Thread: How to trap EXECUTE failure
08-16-04, 17:14 #1Registered User
- Join Date
- Aug 2004
Unanswered: 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.
08-16-04, 17:26 #2Registered User
- Join Date
- Jul 2003
- San Antonio, TX
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."The data in a record depends on the Key to the record, the Whole Key, and
nothing but the Key, so help me Codd."