# Tuesday, 08 November 2005

In an interesting turn of events I learned that SQL Server 2000 doesn’t actually run transactions atomically when XACT_ABORT is set to OFF and an error occurs. A colleague of mine sent along a very interesting article describing basic error handling in stored procedures including the description of the XACT_ABORT option. Here’s quote from Books Online:

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.

And here’s the article I mentioned above. A very good read; should be mandatory for everyone coming within 10 meters of SQL Server 2000.

Error handling in stored procedures is a very tedious task, because T-SQL offers no exception mechanism, or any On Error Goto. All you have is the global variable @@error which you need to check after each statement for a non-zero value to be perfectly safe. If you call a stored procedure, you also need to check the return value from the procedure.

[Implementing Error Handling with Stored Procedures]

Saturday, 22 December 2007 13:45:54 (Romance Standard Time, UTC+01:00)
Comments are closed.