I have a try catch block in my SP with just a insert statement in the try. The catch checks the error code to see if it is PK violation, if it is then do an update. But sometimes I get and error:
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.
So I added xact_abort on, but then I keep getting:
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements.
And I found this Set-XACT_ABORT-ON-and-TryCatch-block-in-Sql-Server-2005, which if true, will my catch code not run if there is a error in my try block with xact_abort on?
It is not true, at least with SQL Server 2008, that SET XACT_ABORT ON will cause an error to skip the CATCH block:
Here is the code I tried using the Northwind database
SET XACT_ABORT OFF
BEGIN TRY
SELECT 1, @@TRANCOUNT
BEGIN TRAN
UPDATE [dbo].[Categories]
SET Description='BLAH'
WHERE [CategoryID]=2
SELECT 2, @@TRANCOUNT
SELECT 1/0 as whoops
COMMIT
SELECT 3, @@TRANCOUNT
END TRY
BEGIN CATCH
SELECT 'In Catch. Error occured', 4, @@TRANCOUNT
IF (XACT_STATE()) = 0
BEGIN
SELECT
N'There is no transaction'
END;
IF (XACT_STATE()) = -1
BEGIN
SELECT
N'The transaction is in an uncommittable state.' +
'Rolling back transaction.'
ROLLBACK TRANSACTION;
END;
-- Test whether the transaction is committable.
IF (XACT_STATE()) = 1
BEGIN
SELECT
N'The transaction is committable.' +
'Committing transaction.'
COMMIT TRANSACTION;
END;
END CATCH
This will, obviously, force an error when it hits the SELECT 1/0 statement. With SET XACT_ABORT OFF, when the CATCH block is reached, the value returned by the XACT_STATE() function is 1, causing the code to run which COMMITs the transaction. When SET XACT_ABORT is on, the value returned, in the CATCH block is -1 so the code which ROLLs back the transaction is executed.
This is based on: