Sql-server – The operation could not be performed because OLE DB provider “SQLNCLI11” for linked server was unable to begin a distributed transaction

distributed-transactionsmsdtcsql server

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)
    enter image description here

  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:
    enter image description here

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.

Best Answer

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

Related Topic