WCF SQL Server Exception FIXED

When executing a stored procedure from a WCF send port (generated by the BizTalk Consume Adapter Service function in Visual Studio) I would receive the following exception:

The adapter failed to transmit message going to send port “WcfSendPort_SqlAdapterBinding_TypedProcedures_dbo_Custom” with URL “mssql://server/database?”. It will be retransmitted after the retry interval specified for this Send Port. Details:”System.Data.SqlClient.SqlException (0x80131904): The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

The stored procedure was already in place; written by a client and contains a good bit of logic. So, I had only a little leeway to make changes. I discovered the issue had to do with transactions between BizTalk and SQL Server, and a TRY/CATCH block within the stored procedure. Apparently, they don’t work well together unless you also use an explicit BEGIN/COMMIT/ROLLBACK transaction within the stored procedure.

Sure enough, when I added a BEGIN TRANSACTION before the TRY, a ROLLBACK within the CATCH, and a COMMIT at the end of the procedure, things worked correctly. I used the syntax given at this link (scroll down to Error 16).

USE Adventure Works;
GO
-- This was added to fix the error
BEGIN TRANSACTION;

BEGIN TRY
    -- The logic is here
END TRY
BEGIN CATCH
    -- The error handling is here
    SELECT 1
    
    -- This was added to fix the error
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

END CATCH;

-- This was added to fix the error
IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO