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