October 6, 2011 at 12:00 pm
Hello,
When I test my stored procedure get this error:
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.
How can I fix it?
This happends when I call sp from another sp and error occurs
Thank you
October 6, 2011 at 12:53 pm
It seems like you're using several transactions but the (logical) number of COMMIT /ROLLBACK commands does not match the number of related OPEN TRANSACTION commands.
You'll need to review the code to check where a previous transaction is closed unintentionally.
You might use @@TRANCOUNT at various points in your sproc to narrow down the problem.
As a side note: if you're using nested transactions, be aware that a COMMIT or ROLLBACK only affect the outermost transaction (see BOL, Nesting Transactions for details).
October 6, 2011 at 1:41 pm
LutzM (10/6/2011)
...As a side note: if you're using nested transactions, be aware that a COMMIT or ROLLBACK only affect the outermost transaction (see BOL, Nesting Transactions for details).
Not quite. Per BOL/MSDN:
ROLLBACK TRANSACTION without a savepoint_name or transaction_name rolls back to the beginning of the transaction. When nesting transactions, this same statement rolls back all inner transactions to the outermost BEGIN TRANSACTION statement. In both cases, ROLLBACK TRANSACTION decrements the @@TRANCOUNT system function to 0. ROLLBACK TRANSACTION savepoint_name does not decrement @@TRANCOUNT.
(emphasis added)
Unless you have a savepoint set up, Rollback goes to the last point where trancount was zero, essentially.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 6, 2011 at 2:06 pm
GSquared (10/6/2011)
LutzM (10/6/2011)
...As a side note: if you're using nested transactions, be aware that a COMMIT or ROLLBACK only affect the outermost transaction (see BOL, Nesting Transactions for details).Not quite. Per BOL/MSDN:
ROLLBACK TRANSACTION without a savepoint_name or transaction_name rolls back to the beginning of the transaction. When nesting transactions, this same statement rolls back all inner transactions to the outermost BEGIN TRANSACTION statement. In both cases, ROLLBACK TRANSACTION decrements the @@TRANCOUNT system function to 0. ROLLBACK TRANSACTION savepoint_name does not decrement @@TRANCOUNT.
(emphasis added)
Unless you have a savepoint set up, Rollback goes to the last point where trancount was zero, essentially.
I guess I used the wrong wording then... Since this is essentially what I meant (hence the BOL reference). The point I tried to make was it's not possible to rollback an inner transaction and still commit the outermost transaction (I'm not sure if there's a scenario where a savepoint at a specific position would allow to do that).
Thanks for clarification.
October 6, 2011 at 2:19 pm
It was unclear because Commit only decrements the trancount by 1 till that hits 0, and then it actually commits stuff. Rollback takes you all the way to the outer transaction, but Commit doesn't.
I've ended some complex DML processes with "While @@Trancount > 0 Commit" just to make sure it gets all the way from deepest inner to the outer transaction without skipping any. Helps avoid the mismatch problem from the first post here.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 7, 2011 at 6:55 am
Is your recomendations to use
"While @@Trancount > 0 Commit"
Thank you
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply