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

Question

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

User · Accepted Answer

If you have a TRY CATCH block then the likely cause is that you are catching a transaction abort exception and continue  In the CATCH block you must always check the XACT STATE   and handle appropriate aborted and uncommitable  doomed  transactions  If your caller starts a transaction and the calee hits  say  a deadlock  which aborted the transaction   how is the callee going to communicate to the caller that the transaction was aborted and it should not continue with  business as usual   The only feasible way is to re-raise an exception  forcing the caller to handle the situation  If you silently swallow an aborted transaction and the caller continues assuming is still in the original transaction  only mayhem can ensure  and the error you get is the way the engine tries to protect itself    I recommend you go over Exception handling and nested transactions which shows a pattern that can be used with nested transactions and exceptions   create procedure  usp my procedure name  as begin     set nocount on      declare  trancount int      set  trancount     trancount      begin try         if  trancount   0             begin transaction         else             save transaction usp my procedure name           -- Do the actual work here  lbexit          if  trancount   0             commit      end try     begin catch         declare  error int   message varchar 4000    xstate int          select  error   ERROR NUMBER     message   ERROR MESSAGE     xstate   XACT STATE            if  xstate   -1             rollback          if  xstate   1 and  trancount   0             rollback         if  xstate   1 and  trancount  gt  0             rollback transaction usp my procedure name           raiserror   usp my procedure name   d   s   16  1   error   message        end catch end go

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Avoid using   RETURN   statement when you are using   BEGIN TRY          END TRY  BEGIN CATCH         END CATCH   and  BEGIN  COMMIT  amp  ROLLBACK   statements in SQL stored procedures

User · Answer

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

User · Answer

I encountered this error once after omitting this statement from my transaction   COMMIT TRANSACTION  MyTransactionName

User · Answer

I had this problem too  For me  the reason was that I was doing  return commit   instead of  commit return      in one stored procedure

User · Answer

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  gt  0 rollback transaction      throw  --allows capture of useful info when an exception happens within the transaction end catch

User · Answer

If you are having a code structure of something like   SELECT 151 RETURN -151   Then use   SELECT 151 ROLLBACK RETURN -151

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server-2012

Examples related to sqlexception