0

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?

3
  • 5
    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. Jan 8 at 2:12
  • 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.
    – stevanus billy
    Jan 8 at 6:23
  • Can you provide a screenshot of the MSDTC properties on the remote server?
    – J.D.
    Jan 8 at 12:32

1 Answer 1

0

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

2
  • 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? Jan 10 at 3:03
  • 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. Jan 11 at 8:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.