• Register
0 votes
184 views

Problem :

My Stored Procedure is giving me following error
transaction count after execute indicates a mismatching number of begin and commit statements
6.9k points

Please log in or register to answer this question.

1 Answer

0 votes

Solution :

This usually happens when one transaction is started and either it is not committed or it is not rolledback.

If the error comes in your stored procedure then it will lock the database tables as the transaction is not completed due to some runtime errors occuring in the absence of exception handling. So to avoid getting such errors you can use the Exception handling as shown below. 

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

Further Readings :

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15

38.6k points

Related questions

0 votes
1 answer 19 views
19 views
Problem : Is there any way to rectify this problem: The transaction log for database is full due to active_transaction?
asked Jun 19 Sifat55 108k points
0 votes
1 answer 61 views
61 views
Problem: I'm trying to figure out a solution, need help : The transaction log for database is full due to 'active_transaction'.
asked Jun 18 Rada Gracelynn 104k points
0 votes
1 answer 9 views
9 views
Problem: No suitable driver found for jdbc oracle?
asked Jul 18 Nahil 128k points
0 votes
1 answer 20 views
20 views
Problem: Here's a strange problem I'm running into on a production server. It has happened twice in the last two weeks, and this is a server that gets a lot of traffic. We have some code in a Web Service that executes a BEGIN TRAN, ... Twice now we have gotten the message in the logs: The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.Any help or suggestion appreciated greatly
asked May 2 Moumita Bhowmick 12.9k points
0 votes
1 answer 19 views
19 views
Problem: I have no idea to solve this, somebody help : The conversion of a date data type to a datetime data type resulted in an out-of-range value.
asked Jun 11 asha 121k points
0 votes
1 answer 15 views
15 views
Problem: Give me an answer please: System.data.sqlclient.sqlerror: the backup set holds a backup of a database other than the existing
asked Jun 10 Sawyer Parviz 167k points
0 votes
1 answer 21 views
21 views
Problem: How to select a partial of a table for exporting using SQL? How can I do that? Thanks
asked Mar 9 ummesalma 29.1k points
0 votes
1 answer 23 views
23 views
Problem: I am a beginner to sql. I have the 123MB sql file which I am trying to execute on my local PC. But I am facing the below error: Cannot execute script: Insufficient memory to continue the execution of the program. Whenever I click on Ctlr+E then it ... above error. I have tried to fix the above sql error with the help of online tutorials but they were not useful in fixing the error.
asked Aug 28, 2020 Raphael Pacheco 4.9k points
0 votes
1 answer 372 views
372 views
Problem : I am facing following error with the below query in SQL Server 2012. An expression of non-boolean type specified in a context where a condition is expected, near 'RETURN'. SQL Query: CREATE FUNCTION [dbo].[GetMYPMResources](@UserResourceNo ... JOIN JobTask t ON t.JobTaskNo = m.JobTaskNo WHERE t.TaskManagerNo = @UserResourceNo OR t.AlternateTaskManagerNo = @UserResourceNo ) RETURN END
asked Dec 4, 2019 alecxe 7.5k points