The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server was unable to begin a distributed transaction [duplicate]

Question

I'm trying to run a distributed transaction from my machine (SQL Server 2012) to a client server (SQL Server 2008).

I'm trying to run:

begin distributed transaction
select * from [172.01.01.01].master.dbo.sysprocesses
Commit Transaction

and I get:

OLE DB provider "SQLNCLI11" for linked server "172.01.01.01" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "172.01.01.01" was unable to begin a distributed transaction.

I can run a SELECT to that server with data coming back, so at least I know the servers can see each other, and the Linked Server exists and is operating

Now, there are multiple posts on the web for this, but I can't get it to work. This is what I have tried so far:

1. Set DTC properties to the following (on both server)

2. Restarted the Distributed Transaction Coordinator (MSDTC) from Control Panel -> Services (on both servers).

3. Uninstalled and installed DTC (on both servers).

4. Restarted the remote server.

5. Turned off the firewall on both servers.

6. Enabled sp_configure 'Ad Hoc Distributed Queries', 1 (on both servers).

7. I ran DTCPing and it pinged successful.

8. Linked server properties changed to the following:

What else are there to try?

UPDATE: Running the transaction from another server to 172.01.01.01 works. Therefore the issue is not on the destination server, but on my machine which is the source.

Yoheaveho

Do you found any solution? Please put the answer here if you do :) – Mojica

I have the same problem, if you found a solution please share it – Atalya

Answer 1

If after configuring your MS Distributed Transaction Coordinator (MSDTC) on the two SQL server's according to the OP's original post, you still get "no transaction active", you should check that each host is reachable via the IP (assuming that's what you've used) registered in the linked server.

For example; on a recent setup, two SQL servers were reachable through a network in the 192.168.200.x range (same subnet), but each server was also indirectly connected through an IP in the 10.x.x.x range. On one SQL Server, the DNS server it used kept resolving the target SQL server to it's 10.x.x.x IP (which was firewalled) even though the linked server entry used the IP in the 192.168.200.x of the target server.

It appears that MSDTC uses the hostname of the server, while SQL server connects over any linked connection using the IP or hostname defined in the linked server entry, leading to this confusing behaviour of apparent connectivity when checking the target linked server within SQL Management Studio, but inability to execute remote procedures on the target.

The solution was to add entries in the host file's (%windir%\system32\drivers\etc\hosts) to explicitly force each SQL server to resolve the other to the IP address on the 192.168.200.x network.

On host 1 (IP 192.168.200.15):

# TARGET SERVER
192.168.200.20    targetserverhostname.and.any.domain.suffix  targetserverhostname

On host 2 (IP 192.168.200.20)

# SOURCE SERVER
192.168.200.15    sourceserverhostname.and.any.domain.suffix sourceserverhostname

Don't forget to ensure MSDTC has been configured according to the OP's screenshot above allowing network access and (if required) No Authentication.

Banker

Answer 2

Setting "Enable promotion of distributed transaction" flag to false (in Linked Server Properties Window) solved my similar problem.

Fumigant

Exactly what fixed mine ! – Areta

Your answer is still useful in 2021 Cheers – Verticillate

Answer 3

I faced a similar problem and I resolved it as follows. There is a node in tree structure of object explorer in SQL Server. There you will find Serverobjects → LinkedServers → below that there is a list of IP addresses of distributed servers.

Right click on it, select properties, a window will pop up. Select server options in the left pane; you will get list of properties. Set the flag value false to the property "Enable promotion of distributed transaction".

Pedi

This is what resolved the issue for me! – Reedbuck

Answer 4

try this SQL above your begin distributed transaction

set XACT_ABORT on

Frock

msdn.microsoft.com/en-us/library/ms188792.aspx. Doesn't look like a good solution. – Virgo

Answer 5

I had a similar problem. Went through all the things listed to no avail. We have a cluster environment and it turns out our DBA was doing the services work on the cluster and not the main server. Worth keeping that in mind if all else fails :-)

Maryleemarylin

Answer 6

Condition like this may cause same error:

1- Run a transactional script like this and read from linked server:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Begin Tran
... (select command from linked server)...
Commit

2- Open a new connection in code and run a non-transactional script

Result: Error will raised!

Solution: Remove SET TRANSACTION ISOLATION LEVEL SERIALIZABLE.

Reconsider

Answer 7

Fix: Make sure the “Enable Promotion of Distributed Transaction” is set to “False” on the linked server property.

EXEC master.dbo.sp_serveroption @server=N'yourlinkedservername', @optname=N'remote proc transaction promotion', @optvalue=N'true'

“remote proc transaction promotion” is a new option on SQL Server 2008, which allows you to control whether or not you want to enlist remote stored procedure call in a distributed transaction. When this option is off (FALSE), the local transaction will not be promoted to distributed transaction. This is how we are able to separate outer and inner transactions in a “autonomous transaction” fashion.

Source: https://www.eugenechiang.com/2020/09/17/linked-server-error-no-transaction-is-active/

Baton