Neil Thomas (5/23/2012)
So that means that when xact_abort is set to off then
does not do as expected.
What's the default setting on a fresh install of SQL?
I guess that depends on the expectation. The code in this QotD contains no error-handling and an unconditional COMMIT statement.
For handling SQL Server errors, it is important to realize that various errors have various effects on running code. Off the top of my head, the potential effects are:
* Compile-time error - the entire batch is not executed; including statements BEFORE the one that causes the error (because the batch is compiled as a whole before execution starts). Execution halts.
* Statement-aborting - running statement is rolled back; transaction is left intact; execution continues.
* Transaction-invalidating - running statement is rolled back; transaction is marked invalid (meaning you'll get an error if you try to commit); execution continues.
* Transaction-aborting - running statement and open transactions (if any) rolled back; execution continues. You'll get an error if you execute a commit or rollback statement after this.
* Batch aborting - running statement is rolled back. I don't know if transaction is rolled back or left open (sorry). Rest of batch is not executed; execution continues at next batch.
* Connection aborting - running statement and open transactions rolled back; connection dropped (so all execution halts). This only happens in the case of some very severe errors (like drive failures or so).
For a very complete discussion on this subject, read http://www.sommarskog.se/error_handling_2005.html
Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis