Rajeev Pentyala – Microsoft Power Platform

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

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

🙂