CodePattern.net

Recently, one of my colleague was very upset with this error as he was not using any @@TRANCOUNT but this error was coming. As this error message suggest a wrong transaction count after execute he was poking his nose into finding the line where he is counting transaction. There were no transaction count in his stored procedure.

After a lot troubleshooting, he find the simple mistake that was the actual culprit. In fact he has used XACT_STATE() and not @@Trancount. His procedure inside code was as (just for sample)-

BEGIN TRY
BEGIN TRANSACTION;
DELETE FROM Production.Product
WHERE ProductID = @ProductID;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF (XACT_STATE()) = -1
BEGIN
ROLLBACK TRANSACTION;
END;
IF (XACT_STATE()) = 1
BEGIN
COMMIT TRANSACTION;   
END;
END CATCH;
GO

And in above code you can see there is no transaction count. hmmm....

The error was due to XACT_STATE() usage without setting XACT_ABORT to ON. 

This error just disappeared after putting this line of code on the top.

SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
DELETE FROM Production.Product
WHERE ProductID = @ProductID;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF (XACT_STATE()) = -1
BEGIN
ROLLBACK TRANSACTION;
END;
IF (XACT_STATE()) = 1
BEGIN
COMMIT TRANSACTION;   
END;
END CATCH;
GO

In short: Always set XACT_ABORT to ON on the top if you are using XACT_STATE()