Is there a simple way to detect if a transaction for a given name is open? For example in this code, I need to know whether to rollback the transaction in the error handler.
asked Mar 03, 2010 at 11:03 PM in Default
A ROLLBACK will always roll back the outermost transaction. SQL Server doesnt yet support nested transactions (in the sense we are talking about here). If you are already in the context of a transaction, and you BEGIN TRANSACTION, you dont truly open a new transaction scope, you really just increment that @@trancount counter. Any subsequent COMMIT will decrement it, but only the final COMMIT actually commits it. ref link
Im not positive what you are trying to do here, but you may want to look into SAVE TRAN which will let you rollback to a predefined save point. BOL link
answered Mar 04, 2010 at 09:32 PM