Archive

Posts Tagged ‘Transaction count after EXECUTE’

Transaction count after EXECUTE indicates a mismatching number – SQL Error

December 8, 2014 Leave a comment

I was getting the below exception when I execute a stored procedure from my C# console application.

SQL Transaction Error

SQL Transaction Error

My Stored procedure executes in Transaction mode and wrapped in Try/Catch block (Refer below)

CREATE PROCEDURE [Name]

BEGIN TRY

BEGIN TRANSACTION;

— My SQL Script

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

IF XACT_STATE() <> 0

ROLLBACK TRANSACTION;

END CATCH

Reason

  • We get this error if the transaction started and terminated suddenly without committed or rollback.
  • In my Stored Procedure I had a ‘return’ statement after ‘BEGIN TRANSACTION’ , so the stored procedure terminated neither calling COMMIT nor ROLLBACK.

Fix

  • Put “SET XACT_ABORT, NOCOUNT ON” statement which suppresses the error.
  • So the modified Stored Procedure template is as below

CREATE PROCEDURE [Name]

AS

SET XACT_ABORT, NOCOUNT ON 

DECLARE @starttrancount int

BEGIN TRY

SELECT @starttrancount = @@TRANCOUNT

IF @starttrancount = 0

BEGIN TRANSACTION

      — Your SQL Script

IF @starttrancount = 0

COMMIT TRANSACTION

END TRY

BEGIN CATCH

IF XACT_STATE() <> 0 AND @starttrancount = 0

ROLLBACK TRANSACTION

END CATCH

🙂