Can I please get a definitive clarification?
If I do a begin transaction and run a query that fails, is that transaction dead? If I try a rollback it tells me there’s no transaction to roll back. If I then do another begin transaction, is that a 2nd level/nested transaction? Or did the original begin tran get disposed?
Thank you in advance.
We cannot tell you because we need to see the code before we can give you a final and correct answer.
You can find your answer here:
Controlling Transactions (Database Engine) | Microsoft Learn
If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in the Database Engine is to roll back only the statement that generated the error. You can change this behavior using the SET XACT_ABORT statement. After SET XACT_ABORT ON is executed, any run-time statement error causes an automatic rollback of the current transaction. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT. For more information, see SET XACT_ABORT (Transact-SQL).