Error in stored procedure : current transaction cannot be commited and cannot support operations that write to the log file

Question

The error message is:

The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

This part right here causes an error (once I comment the SELECT clause out everything runs smoothly).

    DECLARE @TSV_Target_Counter INT
    DECLARE @TargetTable nvarchar(255)
    DECLARE @TargetColumn nvarchar(255)
    DECLARE @Value nvarchar(4000)
    DECLARE @SQLSTR nvarchar(4000)

    SET @TSV_Target_Counter = ( SELECT MIN(Transition_Set_Variable_ID) 
                                FROM @TSV_WithTarget )
    SET @TargetTable = ( SELECT TargetTable 
                            FROM @TSV_WithTarget
                            WHERE Transition_Set_Variable_ID = @TSV_Target_Counter )
    SET @TargetColumn = ( SELECT TargetColumn 
                            FROM @TSV_WithTarget
                            WHERE Transition_Set_Variable_ID = @TSV_Target_Counter )
    SET @Value = ( SELECT Value
                    FROM @TSV_WithTarget
                    WHERE Transition_Set_Variable_ID = @TSV_Target_Counter )

-- problem starts here

SELECT @SQLSTR = 'UPDATE Business_Partner AS BP 
                    INNER JOIN BP_Contact AS BPC ON BP.Business_Partner_ID = BPC.Business_Partner_ID 
                    INNER JOIN Due_Diligence AS DD ON BPC.BP_Contact_ID = DD.BP_Contact_ID 
                    SET' + @TargetColumn + ' = ' + @Value + ' 
                    WHERE DD.Process_Instance_ID = ' + @Process_Instance_ID

-- ends here

EXEC(@SQLSTR);

Am I doing something wrong? I am trying to test this SP with this transaction :

BEGIN TRANSACTION T1
    EXEC Process_Instance_Value_AddAlter -- the name of the SP
    REVERT
ROLLBACK TRANSACTION T1

Solution

You are operating in the context of an uncommitable (aka. 'doomed') transaction. Which implies there is more code that you did not show and probably the call occurs from a CATCH block. See Uncommittable Transactions and XACT_STATE:

If an error generated in a TRY block causes the state of the current transaction to be invalidated, the transaction is classified as an uncommittable transaction. An error that ordinarily ends a transaction outside a TRY block causes a transaction to enter an uncommittable state when the error occurs inside a TRY block. An uncommittable transaction can only perform read operations or a ROLLBACK TRANSACTION. The transaction cannot execute any Transact-SQL statements that would generate a write operation or a COMMIT TRANSACTION. The XACT_STATE function returns a value of -1 if a transaction has been classified as an uncommittable transaction. When a batch finishes, the Database Engine rolls back any active uncommittable transactions. If no error message was sent when the transaction entered an uncommittable state, when the batch finishes, an error message will be sent to the client application. This indicates that an uncommittable transaction was detected and rolled back.

The fix is quite simple: do not call the procedure from an uncommitable transaction context. Always check the XACT_STATE() in a CATCH block.