Global exception handler in CLR Stored Procedure

Question

I use C# and Sql Server 2008 and I have some try/catch blocks in my CLR Stored Procedure and these are caught. These are logged in a file and they return a code error and message using SqlPipe. These work fines. Now I need to do the same when I have some untrapped errors especially to have a trace in the log file!

I tried to use Application.ThreadException but this class is not available in the CLR Stored Procedure (WinForms). Do I have to use a naughty try/catch block in my entry Sql Procedure method or is there something better to do?

Thanks.

SQL Server does not allow you to specify your own handler for *unhandled* exceptions. If an exception is not handled, SQL Server sends an error message to the user. — Jaroslav Jandek, Feb 10 '11 at 10:43

score 1 · Answer 1 · answered Feb 10 '11 at 10:48

1

As I recall, SQL Server will dump unhandled exceptions to its own log file, in addition to returning them to the user. You might want to look into that.

answered Feb 10 '11 at 10:48

user

6,897
8
43
79

score 1 · Answer 2 · answered Jun 24 '11 at 19:02

The cleanest approach will be to use a try..catch block in the CLR method being invoked. Even better would be to keep the main functionality in a separate method that gets invoked by the CLR entry method:

[SqlProcedure]
public static void GetSomething(string value)
{
    try {
        DoGetSomething(value):
    }
    catch (Exception ex) {
        // error handling
    }
}

private static void DoGetSomething(string value)
{
    // implementation goes here
}

Global exception handler in CLR Stored Procedure

2 Answers2