[sql] Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0

I have an Insert stored procedure which will feed data to Table1 and get the Column1 value from Table1 and call the second stored procedure which will feed the Table2.

But when I call The second stored procedure as:

Exec USPStoredProcName

I get the following error:

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

I have read the answers in other such questions and am unable to find where exactly the commit count is getting messed up.

This question is related to sql sql-server-2012 sqlexception

The answer is


I encountered this error once after omitting this statement from my transaction.

COMMIT TRANSACTION [MyTransactionName]

Make sure you don't have multiple transactions in the same procedure/query out of which one or more are left uncommited.

In my case, I accidentally had a BEGIN TRAN statement in the query


In my case, the error was being caused by a RETURN inside the BEGIN TRANSACTION. So I had something like this:

Begin Transaction
 If (@something = 'foo')
 Begin
     --- do some stuff
     Return
 End
commit

and it needs to be:

Begin Transaction
 If (@something = 'foo')
 Begin
     --- do some stuff
     Rollback Transaction ----- THIS WAS MISSING
     Return
 End
commit

If you are having a code structure of something like:

SELECT 151
RETURN -151

Then use:

SELECT 151
ROLLBACK
RETURN -151

For me after extensive debugging the fix was a simple missing throw; statement in the catch after the rollback. Without it this ugly error message is what you end up with.

begin catch
    if @@trancount > 0 rollback transaction;
    throw; --allows capture of useful info when an exception happens within the transaction
end catch

Be aware of that if you use nested transactions, a ROLLBACK operation rolls back all the nested transactions including the outer-most one.

This might, with usage in combination with TRY/CATCH, result in the error you described. See more here.


This normally happens when the transaction is started and either it is not committed or it is not rollback.

In case the error comes in your stored procedure, this can lock the database tables because transaction is not completed due to some runtime errors in the absence of exception handling You can use Exception handling like below. SET XACT_ABORT

SET XACT_ABORT ON
SET NoCount ON
Begin Try 
     BEGIN TRANSACTION 
        //Insert ,update queries    
     COMMIT
End Try 
Begin Catch 
     ROLLBACK
End Catch

Source


I had the same error message, my mistake was that I had a semicolon at the end of COMMIT TRANSACTION line


In my opinion the accepted answer is in most cases an overkill.

The cause of the error is often mismatch of BEGIN and COMMIT as clearly stated by the error. This means using:

Begin
  Begin
    -- your query here
  End
commit

instead of

Begin Transaction
  Begin
    -- your query here
  End
commit

omitting Transaction after Begin causes this error!


I had this problem too. For me, the reason was that I was doing

return
commit

instead of

commit
return   

in one stored procedure.


Avoid using

RETURN

statement when you are using

BEGIN TRY
    ... 
END TRY

BEGIN CATCH
    ...
END CATCH

and

BEGIN, COMMIT & ROLLBACK

statements in SQL stored procedures


This can also occur if your stored procedure encounters a compile failure after opening a transaction (e.g. table not found, invalid column name).

I found i had to use 2 stored procedures a "worker" one and a wrapper one with try/catch both with logic similar to that outlined by Remus Rusanu. The worker catch is used to handle the "normal" failures and the wrapper catch to handle compile failure errors.

https://msdn.microsoft.com/en-us/library/ms175976.aspx

Errors Unaffected by a TRY…CATCH Construct

The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:

  • Compile errors, such as syntax errors, that prevent a batch from running.
  • Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.

Hopefully this helps someone else save a few hours of debugging...


This can also depend on the way you are invoking the SP from your C# code. If the SP returns some table type value then invoke the SP with ExecuteStoreQuery, and if the SP doesn't returns any value invoke the SP with ExecuteStoreCommand


Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server-2012

Count the Number of Tables in a SQL Server Database SQL Server IF EXISTS THEN 1 ELSE 2 Get last 30 day records from today date in SQL Server No process is on the other end of the pipe (SQL Server 2012) How to subtract 30 days from the current date using SQL Server Possible to restore a backup of SQL Server 2014 on SQL Server 2012? SQL Server: Best way to concatenate multiple columns? SQL Server - An expression of non-boolean type specified in a context where a condition is expected, near 'RETURN' SSIS Excel Connection Manager failed to Connect to the Source Sql server - log is full due to ACTIVE_TRANSACTION

Examples related to sqlexception

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0 When does SQLiteOpenHelper onCreate() / onUpgrade() run? Java java.sql.SQLException: Invalid column index on preparing statement java.sql.SQLException: Incorrect string value: '\xF0\x9F\x91\xBD\xF0\x9F...' What does "if (rs.next())" mean? How to catch a specific SqlException error? java.sql.SQLException: No suitable driver found for jdbc:microsoft:sqlserver conversion of a varchar data type to a datetime data type resulted in an out-of-range value