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()