September 1, 2005 at 7:56 pm
Sorry for what might seem to be a simple question, but I couldn't find a straightforward answer in Books Online: Can someone give me a simple explanation of what XACT_ABORT does when set ON and when/how it should be used?
Thanks.
Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC
September 1, 2005 at 9:18 pm
In BOL Index tab, type the key word "SET XACT_ABORT".
From BOL:
"When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. When OFF, only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT"
September 5, 2005 at 8:18 am
SET XACT_ABORT ON will abort your batch (up until the next "GO") if there is an error.
IF you issued a "BEGIN TRANSACTION", it will be rolled back.
Testing for @@ERROR after a statement will not work. because this code is never executed.
SET XACT_ABORT OFF will not abort your btach on error (unless it is something like wrong datatype error)
ROLLBACK must be issued manually
The subsequent statements execute
HTH, Shawn
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply