SQL Server catch error from extended stored procedure
- by haxelit
Hello I have an extended stored procedure that sends an error message.
srv_sendmsg(pSrvProc, SRV_MSG_ERROR, errorNum, SRV_FATAL_SERVER, 1,
NULL, 0, (DBUSMALLINT) __LINE__,
buff,
SRV_NULLTERM);
I've set the severity to SVR_FATAL_SERVER just as a test to see if I can cause the message to throw an exception in the sql.
In my SQL i'm doing:
BEGIN TRY
EXEC dbo.xp_somethingCool
SET @Error = @@ERROR
END TRY
BEGIN CATCH
PRINT 'AN Error occoured!'
SELECT ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
I would think that when my xp sends the error message the tsql would catch the error and select the error_number and error_message. Instead what ends up happening is that the xp sends the message and the T-SQL continues on its way like nothing happened. The @@Error variable doesn't get set either.
So I was wondering if there was any trick to getting SQL to catch an error from an XP ?
Thanks,
Raul
