• steven.bingham (3/28/2013)


    Team,

    ...

    Often times you can run stored procedures from many processes, like other stored

    procedures, where there are different levels of transactions. The problem is that if you

    write to your log table and a rollback occurs, you lose that error record. Even if you write

    the log record after a rollback statement, you could lose your record if that stored proc.

    itself is running with in inner transaction context and the out transaction rolled back.

    ...

    This is a common misconception with TSQL. A COMMIT statement commits the innermost transaction, but a ROLLBACK statement rolls back to the outermost transaction. So if you do a rollback, you're no longer inside any nested transactions. (You can explicitly rollback to a savepoint, but when using ROLLBACK by itself or even ROLLBACK @transaction_name, you're always rolling back ALL active transactions.)

    Another technique I've seen used is taking advantage of table variables to store errors and relevant details inside of transactions. If you insert data into a table variable and then rollback the transaction, the table variable isn't affected and will still contain the same data as before the rollback. This can be useful for inline debugging, but for logging you still need to make sure the transaction has committed or rolled back before logging from the table var back to a table.