Trigger Error: The Current Transaction Cannot Be Committed And Cannot Support Operations That Write To The Log File

So I am getting the following error message from SQL Server when sp_SomeProc tries to execute an invalid sql statement. I get the error: The current transaction cannot be committe

Solution 1:

This error occurs when you use a try/catch block inside of a transaction. Let's consider a trivial example:

SET XACT_ABORT ON

IF object_id('tempdb..#t') ISNOTNULLDROPTABLE #t
CREATETABLE #t (i INTNOTNULLPRIMARY KEY)

BEGIN TRAN
    INSERTINTO #t (i) VALUES (1)
    INSERTINTO #t (i) VALUES (2)
    INSERTINTO #t (i) VALUES (3)
    INSERTINTO #t (i) VALUES (1) -- dup key error, XACT_ABORT kills the batchINSERTINTO #t (i) VALUES (4) 

COMMIT  TRAN
SELECT*FROM #t

When the fourth insert causes an error, the batch is terminated and the transaction rolls back. No surprises so far.

Now let's attempt to handle that error with a TRY/CATCH block:

SET XACT_ABORT ON
IF object_id('tempdb..#t') ISNOTNULLDROPTABLE #t
CREATETABLE #t (i INTNOTNULLPRIMARY KEY)

BEGIN TRAN
    INSERTINTO #t (i) VALUES (1)
    INSERTINTO #t (i) VALUES (2)
    BEGIN TRY
        INSERTINTO #t (i) VALUES (3)
        INSERTINTO #t (i) VALUES (1) -- dup key errorEND TRY
    BEGIN CATCH
        SELECT ERROR_MESSAGE()
    END CATCH  
    INSERTINTO #t (i) VALUES (4)
    /* Error the Current Transaction cannot be committed and 
    cannot support operations that write to the log file. Roll back the transaction. */COMMIT TRAN
SELECT*FROM #t

We caught the duplicate key error, but otherwise, we're not better off. Our batch still gets terminated, and our transaction still gets rolled back. The reason is actually very simple:

TRY/CATCH blocks don't affect transactions.

Due to having XACT_ABORT ON, the moment the duplicate key error occurs, the transaction is doomed. It's done for. It's been fatally wounded. It's been shot through the heart...and the error's to blame. TRY/CATCH gives SQL Server...a bad name. (sorry, couldn't resist)

In other words, it will NEVER commit and will ALWAYS be rolled back. All a TRY/CATCH block can do is break the fall of the corpse. We can use the XACT_STATE() function to see if our transaction is committable. If it is not, the only option is to roll back the transaction.

SET XACT_ABORT ON-- Try with it OFF as well.
IF object_id('tempdb..#t') ISNOTNULLDROPTABLE #t
CREATETABLE #t (i INTNOTNULLPRIMARY KEY)

BEGIN TRAN
    INSERTINTO #t (i) VALUES (1)
    INSERTINTO #t (i) VALUES (2)

    SAVE TRANSACTION Save1
    BEGIN TRY
        INSERTINTO #t (i) VALUES (3)
        INSERTINTO #t (i) VALUES (1) -- dup key errorEND TRY
    BEGIN CATCH
        SELECT ERROR_MESSAGE()
        IF XACT_STATE() =-1-- Transaction is doomed, Rollback everything.ROLLBACK TRAN
        IF XACT_STATE() =1--Transaction is commitable, we can rollback to a save pointROLLBACK TRAN Save1
    END CATCH  
    INSERTINTO #t (i) VALUES (4)

IF @@TRANCOUNT>0COMMIT TRAN
SELECT*FROM #t

Triggers always execute within the context of a transaction, so if you can avoid using TRY/CATCH inside them, things are much simpler.

For a solution to your problem, a CLR Stored Proc could connect back to SQL Server in a separate connection to execute the dynamic SQL. You gain the ability to execute the code in a new transaction and the error handling logic is both easy to write and easy to understand in C#.