How to use transaction in different server SQL Server

Question

I have more than one server with SQL Server database. I want to do a begin transaction on both databases so that when there is an error it will automatically roll back. I have tried linked servers which have been tested to connect to each other using IP and can select between servers including updating and deleting, but when I try to do begin transaction an error occurs.

OLE DB provider "SQLNCLI11" for linked server "name" returned message "No transaction is active.".
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "name" was unable to begin a distributed transaction.

I have searched for a solution and have done several things such as changing enable promotion of distribution transactions to false, trying to make sure the servers are connected to each other, reinstalling the linked server driver. but it still doesn't work, does anyone know the cause of this connection failure? I'm using SQL Server 2012 with Windows NT 6.2 OS and SQL Server 2019 with Redhat 8.4.

Is there another way to carry out transactions on different servers without a linked server?

• Mitch Wheat: while using distributed transactions can be valid, they are to be avoided wherever possible. Perhaps you have an XY problem? Why don't you tell us the actual problem you are trying to solve. A distributed transaction requires that a DTC be installed and active.
• stevanus billy: i already have DTC installed on both server and already active, i want to use stored procedure to manage data between two server, but i cant do it and get the error.
• J.D.: Can you provide a screenshot of the MSDTC properties on the remote server?

Answer 1

Distributed transactions require the Microsoft Distributed Transaction Coordinator which I didn't think was available for Linux, however a quick bingle shows that it is indeed available and produces the following official documentation on how to configure it and what is supported. Note not all drivers are supported and also that you need to be above a certain minimum version of OS & SQL

https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-configure-msdtc?view=sql-server-ver16

• stevanus billy: thank you for the answer, after reading the documentation I realized that my windows server OS does not meet the minimum requirements, but I still want to try doing something like this transaction. Is there any other way to do this besides using distributed transactions?
• Stephen Morris - Mo64: easiest will be to upgrade the Windows OS. I dare say it is possible to handle the simple cases programmatically but distributed transaction handling is pretty complicated computer science & the number of hours you would have to put in would easily pay for an OS upgrade.