RAISEERROR from CLR stored procedures

I'm writing:

SqlContext.Pipe.ExecuteAndSend(new SqlCommand("RAISERROR ( '" + e.Message + "', 11, 1)"));

to execute a RAISEERROR from a CLR stored procedure.

It works, but in the SqlError list I get in the client, there are two more errors that say something like '{System.Data.SqlClient.SqlError: A .NET Framework error occurred during execution of user defined routine or aggregate 'Customer__Update':
System.Data.SqlClient.SqlException: noupdate ...' ...

Is there any way to just get the message I'm sending?

Thanks

The issue here is that by ExecuteAndSend a command which executes a RAISERROR, your RAISERROR will be called in the SQL layer, and an error will be raised to the client (which is good and is what you see as the first error). However, this error will be caught by the CLR when ExecuteAndSend returns., and it will be seen as a unhandled error and wrapped by SQL when your method returns as error 6522 (CLR Error).
The way to handle this is to have your Pipe.ExecuteAndSend in a try block and then have an empty catch block which will eat the error coming back from SQL:

try { p.ExecuteAndSend(cmd); } catch { }

Hope this helps!
Niels

|||Thanks Neils.

It got better, now I get to SqlErrors in the Errors collection one with my message and another saying 'The statement has been terminated.'

However, with or without the try/catch, I got a 50000 error, not the 6522.

I got the 6522 when I created the SqlCommand and executed it directly. If I execute using the SqlPipe.ExecuteAndSend, I got 50000

|||

aaguiar wrote:

It got better, now I get to SqlErrors in the Errors collection one with my message and another saying 'The statement has been terminated.'

Hmm, I don't see that here, I only get one message. However if I execute the CLR proc from inside a T-SQL proc I get all kind of weird errors - that's a bug in this build, and will be fixed. Can you post your code, both server side as well as client side?

aaguiar wrote:

However, with or without the try/catch, I got a 50000 error, not the 6522.
I got the 6522 when I created the SqlCommand and executed it directly. If I execute using the SqlPipe.ExecuteAndSend, I got 50000

Sure, but when you do ExecuteAndSend (without the dummy try/catch), I'm willing to bet your second error is 6522. 50000 is expected as that is "user defined error".
Niels|||

I tried this but the error is not caught by a begin try/end try ... begin catch/end catch block in transact sql code. Is there a way to send a custom error which can be caught in TSQL code?

|||I am having this problem too. The RAISERROR call from my CLR Routine is not being caught in the TRY/CATCH T-SQL block. Can anyone tell me why?