We get the below error while performing a distributed transaction on linked server. We have several linked servers configured in the source server and all of them succeed with the distributed transaction except on one.

We did all the basic troubleshooting and moreover the distributed transactions work fine if we use a remote server instead.

Need your expert guidance in resolving this issue

Error:

OLE DB provider "SQLNCLI10" for linked server "SERVERNAME.REDMOND.CORP.MICROSOFT.COM" returned message "No transaction is active.".

Msg 7391, Level 16, State 2, Line 3

The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "SERVERNAME.REDMOND.CORP.MICROSOFT.COM" was unable to begin a distributed transaction.

Test code:

begin distributed transaction

select top 10 * from [SERVERNAME.REDMOND.CORP.MICROSOFT.COM].master.sys.objects

ROLLBACK

Source server :

Microsoft SQL Server 2008 (RTM) - 10.0.1779.0 (X64)

Nov 12 2008 12:10:04

Copyright (c) 1988-2008 Microsoft Corporation

Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6001: Service Pack 1) (VM)

Target server :

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)

Jul 9 2008 14:43:34

Copyright (c) 1988-2008 Microsoft Corporation

Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)

You probably need to install and configure MSDTC (Distributed Transaction Coordinator)

Hi,

This is a known issue. You will get error message "OLE DB provider 'SQLNCLI' for linked server '<Linked Server>' returned message 'No transaction is active'" when you run a distributed query in SQL Server 2008. The fix for this issue was release in Cumulative Update 2 for SQL Server 2008. Here is a KB article that supports it.

http://support.microsoft.com/kb/954950

Hope this helps.

Thanks.

I am running 2 W2K8, with SQL Server 2008. The SQL Servers are patched to CU3, which is greater than CU2, and I am still getting the following error. The MSDTC is installed and setup. I am able to perform queries through the linked servers in both directions, but if I try to load data from the results of a remote SP, or I try to run an explicit distributed transaction I recieve the following.
OLE DB provider "SQLNCLI10" for linked server "X" returned message "No transaction is active.". Msg 7391, Level 16, State 2, Line 13 The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "X" was unable to begin a distributed transaction.

Hi,

Has this issue been resolved?

I get the same error as above.

We're using W2K8 Enterprise SP1 64 & SQL Server 2008 SP1 (build 2531).
MSDTC is installed and running.
Allow inbound/outbound checked
No Authentication required checked
Enable XA Transaction NOT checked.

Hi Douglas

Try steps mentioned in http://www.sqlvillage.com/Articles/Distributed%20Transaction%20Issue%20for%20Linked%20Server%20in%20SQL%20Server%202008.asp 

It should be able to help you in resolving issue, I had same issue but was resolved using method provided in above link.

Hi Xiao,

   I have the above issue in SQL Server 2005 Enterprise server with SP3.  Per Microsoft KB article, http://support.microsoft.com/kb/954950,  SP3 should have that issue addressed. But, still I am getting the same error. My both partner servers are installed with SP3.

  Is there any other workaround?

 

Thanks

Ram

Also having this problem with 2008 standard sp1 cu7, all MSDTC settings marked as instructed on other threads.

 

http://technet.microsoft.com/en-us/library/cc753510%28WS.10%29.aspx

 

Had instructions on how to enable settings.  Setting for the cluster worked, even though it is not a cluster.  It is calling a cluster though.

What worked for me is this. I set the DTC properties to check take "No Authentication required" for the Transaction Manager Communication (after Allowing inbound and outbound transactions). 

Along with this, I had to add SET XACT_ABORT ON for the query that starts the distributed transaction.

 

Hope this helps.

 

-- Adi.

• Proposed as answer by Ganeshkumar005 Thursday, December 27, 2012 7:51 AM

I don't understand this recommendation to install CU2.

DTC is enabled (Admin Tools -> Component Services -> Component Services -> Computers ->My Computer -> Distributed Transaction Coordinator ->Local DTC -> Right-Click Properties -> Security Tab -> Network DTC Access)

but the CU2 installer says it only applies to the Connectivity SDK... which is not installed.

So... the question then becomes... Does having a Transaction in a Proc which uses a Linked Server require that the Connectivity SDK be installed?

 

I got the same issue. I have SQL2008 SP1 and SQL2000 sp4. My problems was the server SQL2008 don't have a record in WINS, that why I didn't got a connection.

Also, my server SQL2008 have 2 name in the DNS with the same IP.

Be sure your server is the only one in your network.

_______________

Manuel Pineda - DBA - Senior

Hello:

I had exact same issue & just resolved.

1> Make sure DTC Service is running & doesn't matter running under Local System Account.

2> As query is not working, so i created Store Proc(SP) on Server = B where this SP will be executed from Server = A.

3> configured Linked Server on Server = A

4> executing SP from OPENQUERY(T-SQL) was able to run successfully on Server = A.

For e.g.
select * from openquery([Linked Server Name],'EXEC Databasename..StoreProcedure')
GO

I hope this helps.

Thanks

 

Hi Xiao, i have the same issue, the scenario is:

 

Server 1

Windows Server 2008 R2 Enterprise Edition x64 SP1

SQL Server 2008 Standard x64 SP2 (10.0.4000)

MSDTC Config:

- Enabled Network DTC Access

- Enabled Allow remote clients

- Enabled Allow Inbound

- Enabled Allow Outbound

- Selected No authentication required

 

Server 2

Windows Server 2003 Enterprise Edition SP2 (x86)

SQL Server 2000 Standard SP4 (8.00.2039)

MSDTC Config:

- Enabled Network DTC Access

- Enabled Allow remote clients

- Enabled Allow Inbound

- Enabled Allow Outbound

- Selected No authentication required

 

There is a linked server on Server 1 pointing to Server 2

Linked Server Config:

Data Access: True

RPC: True

RPC Out: True

Enable Promotion of Distributed Transactions: True

 

Even i tried to install the Hotfixes mentioned in http://support.microsoft.com/kb/954950 but it does not let me install because

says that can´t find an instance to apply the patch.

 

The code i execute is next:

SET XACT_ABORT ON

BEGIN DISTRIBUTED TRANSACTION

insert into [192.168.103.157].sao1814.dbo.DTCTest(
    Field1, Field2
)
values(1, 'rgrg')

COMMIT TRANSACTION

 

But get this error:

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

 

If i execute only the insert part of the query it works good, but i have to execute some other statements

for insert data and if one fails i won´t be able to rollback.

 

 

Before migrating the SQL Server intance to a x64 OS worked well.

I´m going crazy for this, please can someone give me a hand?

 

Thanks all.

Hi Uziel,

 

I am running into the same problem and have a very similar setup.  One server is Win 2008 and the other Win 2003 and both running SQL 2005, do you think that the difference in Windows OS has anything to do with it? We have not had this issue before in other configurations.

I will keep you posted on any progress and would love if you could as well.

 

Cheers!

Hi ClynnH i could solve this problem.

 

Here is the post:

http://sqlservermemories.blogspot.com/

 

Hope this solves other people issues.

Is this solution worked for any of you?, please leave a comment.

In addition to changing MSDTC settings to allow inbound and outbound transactions and selecting "No Authentication required", as per previous posts, we had to create an inbound allow firewall rule allowing all ports from the remote/linked server.

After DTC was set to allow inbound and outbound with authentication set to none our process was still not working with the remote server logging:
MS DTC is unable to communicate with MS DTC on a remote system.  MS DTC on the primary system established an RPC binding with MS DTC on the secondary system.  However, the secondary system did not create the reverse RPC binding to the primary MS DTC  system before the timeout period expired.  Please ensure that there is network connectivity between the two systems.

Having enabled the firewall logging on the server initiating the transaction (2008 R2) we could see that the response from the linked server was on a high port and being dropped. After creating an inbound allow rule our transaction worked.

I have got the same issue and I fixed it by changing the Linked server properties 

 

XEC sp_serveroption @server = 'servername',@optname = 'remote proc transaction promotion', @optvalue = 'false' ;

 

hope this helps

 

• Proposed as answer by kwyk Monday, November 21, 2011 6:35 AM

Dear Concern,

 

I have followed your instruction as you wrote in your blog but still i am getting same error. 

Can you please help me out from this problem as i am searching solution on google since last one month but still not get any satisfactory answer. I hope i will get solution from you. 

 

 

Regards,

Sunil Kumar Kaushal

good answer helped me solve problem

Thanks to Lazy_Writer, that solution worked perfectly.

Lazy_Writer's Solution worked for me as a temporary solution. This should work fine if you don't need transactions.

EXEC sp_serveroption @server = 'linked_servername',@optname = 'remote proc transaction promotion', @optvalue = 'false'

Scenario:

Server1: Windows 2K8 R2 64bit / SQL Server 2K8 R2 64bit
Was SQL 2K5 on a W2K3 32bit. It was moved to a new server.

Server 2: Windows 2K3 R2 32bit / SQL Server 2K8 R2 32bit
upgraded the SQL instance from 2K5 to 2K8 R2

All the DTC Settings are correct.

These servers were both recently updated from SQL 2K5 and after upgrading/moving Server1 that's when the problems started. I updated Server2 in the hopes that having the same version would help, it didn't. After I startd to write this response I figured out my problem.

I used DTCPing to track the problem down. It turns out that Server2 could not find Server1 by it's netbios name which is a requirement for DTC over RPC. I was using DNS to make the new server available. Once I resolved that, everything is back to normal. I used the simplest solution, put the IP and NetBIOS name in the hosts file. Server2 is going to be upgraded/moved soon anyway.

Moral of the story, if you have tried the steps above and you need to have transactions, use DTCPing to track down any RPC connectivity problems.

Hope this helps someone.

• Edited by Teknologist Saturday, January 21, 2012 3:00 AM typo
• Proposed as answer by Venu Marella Wednesday, October 10, 2012 3:13 PM

Hope this helps somebody else as it has taken me a day of reading every post about MS DTC on forums in vain today to work this out.

I've got the same set up as everyone else here apart from my servers are clustered.

I was getting the old "The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "servername" was unable to begin a distributed transaction.
OLE DB provider "SQLNCLI10" for linked server "servername" returned message "No transaction is active.".
" errors and had setup DTC security the same as in a non-clustered test environment (that worked).

After a hell of a lot of headscratching i noticed another branch underneath DTC in component services labelled "Clustered DTCs"

Set the security up on these and bang. sorted!

:)

The setting changes to DTC worked for me.

It helps a lot to me. Thanks you to posted ur valuable answer in this article.

Thanks Lazy_Writer for your simple yet easy-to-miss observation!

If you really don't have a need to invoke DTC in the first place, then heck with the "Enable Promotion of Distributed Transactions" under the Server Options of the Linked Server's properties! Just set it to False!! End of the story :)

Too bad I spent several hours trying to benefit from all these other ideas (Enable DTC and/or modify DTC's security options, install CU2, create firewall rules...etc) which seem to have helped others but didn't do zilch in my case.

Thanks!

• Edited by Crosswalk Saturday, November 03, 2012 8:48 PM a small addition to the previous comment

hope this link will help

http://support.microsoft.com/kb/954950

http://www.sqlvillage.com/Articles/Distributed%20Transaction%20Issue%20for%20Linked%20Server%20in%20SQL%20Server%202008.asp

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/523116d4-4c8e-43a8-abf4-e705ffaa68c8/

Thanks a ton Lazy Writer. Your solution worked like a charm.

in Windows 2012 ...please follw the same steps in 2008 as well

I have got the same issue when migrating from SQL Server 2008 R2 on the old server to SQL Server 2012 on a new server. My fix involves two steps:

(1) Change MSDTC settings by following the instructions in the aforementioned article

http://www.sqlvillage.com/Articles/Distributed%20Transaction%20Issue%20for%20Linked%20Server%20in%20SQL%20Server%202008.asp

After this first step, the error still exists.

(2) Turn the Windows Firewall off on your server.

Now the problem is gone.

This instruction Worked For Me.  GREAT!!!!! THANKS