Dave Hall (1/25/2013)
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" ...
Well, if you want... Not going to solve half the problems but....
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.
Then you're in for a world of hurt, odd bugs, annoying errors, etc. I would strongly recommend you take a long look at the design, see if you can make it so that transactions are only ever started and committed at one level, not nested.
If you have to go this way, then you need either to check the transaction count before you *begin* a transaction and have different behaviours in the procedure depending on whether there's already a transaction open or not, or you need to add savepoints and rollbacks to those savepoints if there's already a transaction open. Which one depends on what needs to roll back in the case of an error.
Neither option is clean and easy, both are going to result in lots of extra code for the transaction management.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability