Friday, March 30, 2012

How to trap EXECUTE failure

We are running SQL 2000 sp3a on Win2003 Server with all patches and SPs applied.

I want to run a stored procedure from a SQL Agent job. I want the stored procedure to run as a TRANSACTION to force a ROLLBACK in case of failure. However, if execute the procedure and an error of severity level 20 (I think) or greater happens I get blown out of the job immediately. Therefore I cannot call RAISEERROR or use @.@.ERROR. So, how do I assure that I can ROLLBACK the TRANSACTION?

I have searched the web and BOL and cannot find anything that addresses this specifically.

Thanks in advance for any help.Severity level 20 indicates a fatal error that the session encountered. The batch terminates and the connection is severed. The fate of your transaction is predetermined, - it will roll back. You (your spid) cannot roll it back with ROLLBACK because the batch execution will not reach that point.

No comments:

Post a Comment