Logging SQL Errors

Question

SQL Server 2008 has a new try/catch structure. If I encounter an error in a sequence of nested stored procedures, I like to log the call stack in an error table. The problem is that if I have started a transaction (which will be true for operations that update the db), an records written to the error table will be removed when the code in the catch part of the try/catch statement rolls back the transaction.

Any hints as to how I can get around this are welcome.

Answer 1

Some options:

• Test for @@TRANCOUNT = 0 in the logging code
• Use SET XACT_ABORT ON: this always rolls back. This is what I'd do.

SO Questions about SET XACT_ABORT ON (with some error handling)

• Do I really need to use “SET XACT_ABORT ON”?
• What is the benefit of using “SET XACT_ABORT ON” in a stored procedure?