Powered by Blogger.

Saturday, February 15, 2014

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements




Recently while working in an Asp.Net project, while communicating with data base using Microsoft Enterprise Library 6. I found one error. And the full error message was:

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

When before looking at the solution you can check out some of my articles on:

- How to give Tooltips in WPF in Asp.Net?

- Steps to create wcf service and host wcf service as windows service in C#.Net

- C#.Net basic interview questions and answers

Actually i was using a stored procedure and with in the stored procedure TRANSACTION statements were there like below:

USE [TestDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

 CREATE PROCEDURE [dbo].[Employees]
 @Name nvarchar (256) ,
 @Age Int

 AS
 BEGIN
 BEGIN TRY
 BEGIN TRANSACTION

--*************************************
 INSERT INTO Employees  (Name,Age) VALUES ( @Name, @Age )
--*************************************
 COMMIT TRANSACTION
      RETURN 1
-- Sucessful
 END TRY
 BEGIN CATCH
RETURN -111
 END CATCH
END

GO

Solution:
I found few things like you can remove transction statements from stored procedure if it is not necessary. But if you want to keep the transction statements then you can try writting the below statements above CREATE PROCEDURE [dbo].[Employees] line.

SET XACT_ABORT ON
GO

So your stored procedure should look like below now:

USE [TestDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET XACT_ABORT ON
GO

 CREATE PROCEDURE [dbo].[Employees]
 @Name nvarchar (256) ,
 @Age Int

 AS
 BEGIN
 BEGIN TRY
 BEGIN TRANSACTION

--*************************************
 INSERT INTO Employees  (Name,Age) VALUES ( @Name, @Age )
--*************************************
 COMMIT TRANSACTION
      RETURN 1
-- Sucessful
 END TRY
 BEGIN CATCH
RETURN -111

END CATCH
END
GO



0 comments

Post a Comment