Results 1 to 2 of 2
  1. #1
    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.

  2. #2
    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."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts