biztalkdistributed-transactionsmsdtc

The MSDTC transaction manager was unable to push the transaction using AmbientTransaction

I have a BizTalk solution that would call a stored procedure that will query and update table. Apparently I'm encountering this error each time I test my solution in a load balanced servers.

The adapter failed to transmit message going to send port SQLAdapter_Sendport" with URL "mssql://server//StoredProc?". It will be retransmitted after the retry interval specified for this Send Port.

Details: System.Transactions.TransactionManagerCommunicationException: Communication with the underlying transaction manager has failed.

System.Runtime.InteropServices.COMException: The MSDTC transaction manager was unable to push the transaction to the destination transaction manager due to communication problems. Possible causes are: a firewall is present and it doesn't have an exception for the MSDTC process, the two machines cannot find each other by their NetBIOS names, or the support for network transactions is not enabled for one of the two transaction managers.

Exception from HRESULT: 0x8004D02A)
at System.Transactions.Oletx.ITransactionShim.Export(UInt32 whereaboutsSize, Byte[] whereabouts, Int32& cookieIndex, UInt32& cookieSize, CoTaskMemHandle& cookieBuffer)
at System.Transactions.TransactionInterop.GetExportCookie(Transaction transaction, Byte[] whereabouts)
--- End of inner exception stack trace ---

I've read some articles that I should set the DTC properties as below. These I've already set in both BizTalk and SQL Servers.

enter image description here

Also the DTC must be added in the Windows Firewall.

enter image description here

And lastly if the useAmbientTransaction in the Send Port properties is set to True I keep encountering the said error above.

enter image description here

But if I set the useAmbientTransaction to False the message is succeeding in SQL.

I've read in some article that Microsoft doesn't recommend setting the useAmbientTransaction to False especially if there's Update and Delete involve in the stored procedure. But my solution uses delete and update in the stored procedure that's why I need to set it the useAmbientTransaction to True. But if I do that I keep encountering the error "MSDTC transaction manager was unable to push the transaction to the destination..."

Please suggest any possible solution for this.

Solution

  • If you are connecting to a database in a AlwaysOn Availability Group then you need to switch off useAmbientTransaction as the AlwaysOn do not support MSDTC except for SQL Server 2016.

    The same article for SQL Server 2014 Cross-Database Transactions Not Supported For Database Mirroring or AlwaysOn Availability Groups (SQL Server)

    vs

    SQL Server 2016 Cross-Database Transactions and Distributed Transactions for Always On Availability Groups and Database Mirroring (SQL Server)