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.

Answer 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.

Answer 2

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
}