GilaMonster (1/25/2013)
Nested transactions are a lie (and a major pain)The rollback in the inner procedure rolls back all the way to the first begin tran
Hence you enter the inner proc with a transaction open (tran count 1), start a second one (tran count now 2) hit rollback, that rolls back all open transactions (tran count 0, different from when you entered the proc), return to the outer procedure and hit commit, but there's no open transaction any longer.
Don't nest transactions (unless you really know what you're doing and how nested transactions work). Either do your transaction management in the outer proc or in the inner proc, not both.
Thanks, GilaMonster. I have indeed learned that they are a pain!
I understand the concept of how nested transactions work and have tried using @@Trancount to see if there are still open transactions that need to commit..... but all to no avail! I was hoping someone might be able to say "ahhh you need to test for @@trancount here before you commit" ...
The problem with only doing the Rollback in 1 or the other is that each procedure can and does get called on its own so I'd really need the transaction in both.
Thanks,
Dave