Sql – Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing – SQL server 2005

sqlsql serversql-server-2005tsql

I am getting the error from the application as following with SQL server 2005

"Transaction count after EXECUTE
indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing.
Previous count = 1, current count =
0"

How can I find the stage where this error is raised?

How can I find the missing transaction or the stored procedure where it is not committed or getting rolled back?

Best Answer

I don't think anything is missing. It's probably an inner stored procedure that gets called from inside a transaction (TRANCOUNT = 1), starts its own transaction (TRANCOUNT = 2) and then rolls it back. Well, it means to roll it back, but rollback affects all transactions and not only the innermost one, so the procedure screws up the execution flow.

A way to find the place depends on available tools/skills. A better way is to use SQL Profiler that shows all commands executed by an application against the server. Find out the outermost stored procedure and go through its code looking for any other procedure calls.

Related Solutions

Sql – Should I commit or rollback a read transaction

You commit. Period. There's no other sensible alternative. If you started a transaction, you should close it. Committing releases any locks you may have had, and is equally sensible with ReadUncommitted or Serializable isolation levels. Relying on implicit rollback - while perhaps technically equivalent - is just poor form.

If that hasn't convinced you, just imagine the next guy who inserts an update statement in the middle of your code, and has to track down the implicit rollback that occurs and removes his data.

Sql – Transaction count after EXECUTE error

you can check XACT_STATE() in your code and then commit or rollback, check it out here: Use XACT_STATE() To Check For Doomed Transactions

basically something like this will blow up

BEGIN TRANSACTION TranA
    BEGIN TRY
     DECLARE  @cond INT;
     SET @cond =  'A';
    END TRY
    BEGIN CATCH
     PRINT 'a'
    END CATCH;
    COMMIT TRAN TranA

and when you check xact_state you can control it

BEGIN TRANSACTION TranA
    BEGIN TRY
     DECLARE  @cond INT;
     SET @cond = 'A';
    END TRY
    BEGIN CATCH
     PRINT ERROR_MESSAGE();
    END CATCH;
    IF XACT_STATE() =0
    BEGIN
     COMMIT TRAN TranA
    END
    ELSE
    BEGIN
     ROLLBACK TRAN TranA
    END

Also take a look at these two must read links Implementing Error Handling with Stored Procedures and Error Handling in SQL Server – a Background.

Best Answer

Related Solutions

Sql – Should I commit or rollback a read transaction

Sql – Transaction count after EXECUTE error

Related Topic