CODEDIGEST
Home Articles CodeDigest Tutorials FAQs
Skip Navigation LinksHome » FAQs » "Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2."   You are not logged in.
Search
 

Sponsors
 

Technologies
 

"Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2."
Submitted By Lara Kroft
On 7/20/2009 8:35:05 AM
Tags: Interview Questions,SQL  

FAQ

I keep receiving the following warning during bulk updates through stored procedures called from BizTalk. Please help resolve this issue.

The warning reads as "Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2." And "Uncommittable transaction is detected at the end of the batch. The transaction is rolled back."

Description

The error occurs most often when stored procedures are executed in chained mode. In chained mode, if there is no explicit begin transaction statement, an implicit begin transaction is issued. A begin transaction statement, whether implicit or explicit, increments the session transaction count by 1.

In chained mode, the following statements cause an implicit begin transaction to be issued: select, update, delete, insert, and fetch.

Each begin transaction statement in a stored procedure, whether implicit or explicit, must be balanced by a commit transaction statement, which decrements the session transaction count by 1. Otherwise, the transaction count remains higher when the stored procedure exits than it was at the beginning of execution.

In addition, Error occurs when you are using nested procedures, and procedures at each level of nesting include begin, commit, and rollback transaction statements. If a procedure at a lower nest level opens a transaction and one of the called procedures issues a rollback transaction, Error occurs when you exit the nested procedure.

The possible solution could be a slight modification in the SP that takes care of transaction count as provided in the following code

BEGIN TRANSACTION;

BEGIN TRY
    --Statements

END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

Recent FAQs
  • View All FAQs..