RAISERROR in CLR Routines

Question

When I use the following code to execute a RAISERROR from within a CLR Routine (Stored Procedure), and I call this CLR stored procedure from T-SQL within a TRY/CATCH block, the error is not caught in the CATCH block. Why is this happening?

try { SqlContext.Pipe.ExecuteAndSend(cmd); } catch { }

Answer 1

When  you say the error is not caught in the catch block - what do you mean with that, or rather how can you say that the error is not caught in the catch block as the catch block is empty?

If you - for example - where to execute a proc from SQL Management Studio (SSMS) whose command was something like so: RAISERROR('Something went wrong', 16, 1), you should see in SSMS something like this: "Error 50000, Something Went Wrong ...", and your catch block in the CLR proc would eat the resulting SQLException that your RAISERROR produced.

The reason you have an empty catch block as in your code is so you can produce as T-SQL like errors as possible from within a CLR proc.

Niels

Answer 2

Hi Niels,

The RAISERROR is being fired from the CLR Routine just fine, but when I call this routine from a T-SQL stored procedure using the following code, control is not transferred to the BEGIN CATCH .. END CATCH block, it just reports the error in my query results window. I expected it to be caught here.

BEGIN TRY
    EXEC @ReturnCode = proc_ClrProcWithError
END TRY
BEGIN CATCH
    -- Code never gets here
    SET @ErrorMsg = ERROR_MESSAGE()
    EXEC proc_LogMyError @ErrorMsg
END CATCH

Sam

Answer 3

I have noticed that if I throw the exception out of the CLR routine, then the T-SQL stored procedure begin catch .. end catch does catch it. But of course, there is a lot of .NET information sent back with it, so the error message is quite messy.

Answer 4

You will always get back the 6522 from the CLR. You will have to parse the information on your own if you want to retrieve information from twithin the assembly.

HTH, Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---

Answer 5

Does this mean that raiserror calls piped from CLR stored procedures will not be caught by T-SQL try/catch blocks?

I will try to better explain what I am trying to do.

I have a CLR Routine (Stored Procedure), which calls a web service and writes any messages returned from the web service to a log table. If any of this causes an exception, I am catching it and creating a SqlCommand object with a raiserror statement. I am then piping that object and ignoring exceptions coming back from that pipe.

try
{
    response = service.ProcessThisInfo(info);
}
catch (Exception ex)
{
      returnCode = Convert.ToInt32(ErrorEnum.SomeError);
      string msg = string.Format("RAISERROR('{0}',16,1)", ex.Message.Replace('\'', ' '));
      SqlCommand cmd = new SqlCommand(msg, connection);
      try
      {
            SqlContext.Pipe.ExecuteAndSend(cmd);
      }
      catch { } // stop duplicate exception throwing...
}
 
The CLR Routine is being called from within a T-SQL stored procedure using a begin try .. end try - begin catch .. end catch construct in order to catch the raiserror statement. All of this takes place within a cursor allowing for web service calls for individual records. 

OPEN MyCursor

FETCH NEXT...

WHILE @@FETCH_STATUS = 0
BEGIN 

    BEGIN TRY
       EXEC @ReturnCode = proc_ClrWithError -- This line causes raiserror!
    END TRY
    BEGIN CATCH
       -- raiserror not caught here!
       SET @ErrorMsg = ERROR_MESSAGE()
       EXEC proc_LogMyError @ErrorMsg
       GOTO CURSOR_ERROR
    END CATCH

    -- Processing is continuing here

FETCH NEXT...
END 

CURSOR_ERROR:
CLOSE MyCursor
DEALLOCATE MyCursor

I am testing this in management studio by calling the T-SQL stored proc directly

EXEC proc_TSqlProcThatCallsClrWithError

The raiserror that is piped from the clr routine is passed right through to the messages window in management studio and processing in my t-sql stored procedure continues.

Answer 6

This issue has caused me to review my exception handling logic for CLR stored procs. I should not have been using raiserror for recoverable exceptions.

I will now only pipe a raiserror (and return an error code indicating a fatal error) if I cannot recover within the CLR. Since I am no longer relying on a begin try .. end try in my calling SQL, letting any fatal errors bubble up is perfectly acceptable.

Recoverable exceptions, such as any exceptions caused by the web service call, should be handled within the CLR routine. I can then return an integer value indicating that the web service call failed. The calling code just has to examine the return code and process accordingly.

I think this is a much cleaner solution in this case. But I still don't see why raiserrors from clrs should not be caught in calling t-sql try catch blocks.

Answer 7

See the information here:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=251376

-Jens.

Answer 8

Hi Sam!

There are two different aspects of RAISEERROR. It does 2 different things:

1.       Sending a message. RAISEERROR writes a message to the “output” (or we can say, “pipe”).
Couple of words about pipe. When code is executed in the server, there is always a current pipe. When the client connects to SQL Server and starts executing T-SQL statements, the current pipe for these statements will be “TDS Pipe”, in other words if a query generates a row, or RAISEERROR generates a message, they will be sent directly to the client.
When we enter nested levels of execution, the current pipe may change, while the previous pipe will be stacked. Say, when you execute INSERT-EXEC statement, the current pipe will be a pipe that will catch the results and insert them into a table.
When you enter a CLR SP, the pipe doesn’t change. However, if you execute SqlCommand.ExecuteNonQuery, the current pipe will be replaced with a special pipe that will just suppress all output.
SqlPipe.ExecuteAndSend is a way of executing a command without changing the current pipe.

2.       Flow control (generating an exception). After writing an error to the current pipe, RAISEERROR raises an exception. Here we start a familiar process of unwinding the stack (that can be a mix of T-SQL or CLR routines). We drop level after level until we find either T-SQL or CLR try-catch statement. Here we stop unwinding the stack and jump to “CATCH” block of the try-catch statement.
Now we are at the mercy of the “CATCH” statement and the code after it. If they decide to throw (or rethrow) an exception, the process of unwinding will resume till the next try-catch statement or till we reach the bottom of the stack.
Note that flow control mechanisms don’t depend on the output control mechanisms and vice versa.

 

In your case, CLR try-catch doesn’t rethrow, which means that the only result of this try-catch statement will be sending an error to the current pipe (most probably, to the client). But CLR SP will return normally, without an exception, and we will not get into “BEGIN CATCH” block.

What you could do (provided that you prefer to log errors outside of SP) is to add an output parameter of type string to the CLR SP. CLR SP would not use RAISERROR at all. Just assign ex.Message to this parameter. Then, in T-SQL, you can look at this parameter, and if it is not empty, log the error.

Answer 9

Thank you all. Those were two very helpful posts with a complete explanation of the issue!

Sam.