17 February 2012

Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRAN is missing. Previous count = %ld, Current count = %ld

Such an error has appeared these days to me in SQL using SPROC in another SPROC where these are using transactions:


Msg 266, Level 16, State 2, Procedure SPROC_NAME2, Line 0
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2.

So what is that???
I will explain.


Let assume that I have 2 Stored Procedures:

CREATE PROCEDURE proc1
AS
BEGIN TRANSACTION 

...
EXEC proc2

COMMIT TRANSACTION

-- and the second Stored Procedure:


CREATE PROCEDURE proc2

AS
BEGIN TRANSACTION 

...
ROLLBACK/COMMIT TRANSACTION -- depending on a condition


So what will be the results?

1) Everything is cool when COMMIT is executed in the second Stored Procedure - proc2
2) ERROR with message: 266: "Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2." when ROLLBACK is executed in the second Stored Procedure.

WHY??


Because the transaction count is incremented every time you execute a "BEGIN TRANSACTION", and it is decremented every time you execute a "COMMIT TRANSACTION" and it doesn't decrement anything when "ROLLBACK TRANSACTION" is executed.

So what is the solution for this case?

You need to add "COMMIT TRANSACTION" after each "ROLLBACK TRANSACTION", so that it decrements the transaction count indicator correctly. I know this is nonsense but this is the only way to get rid of the "Msg 266, Level 16, State 2, Procedure SPROC_NAME2, Line 0
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2." error.

Please share this article.
Thanks.

3 comments:

Anonymous said...

Cool, thanks... except it does not work.

Roman Gherman said...

Hello,

Could you please give me more details, what does not work?

I have read Microsoft's documentation and tested that before publishing this article.

Anonymous said...

begin transaction
select @@trancount
rollback transaction
select @@trancount
commit transaction
select @@trancount

Would seem to suggest otherwise.

Post a Comment

your thoughts are welcome:

Post a Comment

Need more? Leave comments and subscribe to my blog.

.