none
Distributed Transactions fail on Linked server

    Question

  •  

    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)

     

    Thursday, February 12, 2009 12:48 AM

Answers

  • 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.


    ***Xiao Min Tan***Microsoft Online Community***
    Wednesday, February 18, 2009 10:29 AM
    Moderator

All replies

  • You probably need to install and configure MSDTC (Distributed Transaction Coordinator)
    Bodo Michael Danitz - MCITP Database Administrator - free consultant - performance guru - www.sql-server.de
    Thursday, February 12, 2009 10:31 AM
  • 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.


    ***Xiao Min Tan***Microsoft Online Community***
    Wednesday, February 18, 2009 10:29 AM
    Moderator
  • 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.


    DBA\DBD
    Tuesday, April 07, 2009 6:20 PM
  • 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.

    Tuesday, June 09, 2009 4:42 PM
  • 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.


    Thanks, Mohan Kumar www.sqlvillage.com -- Please mark the post as answered if it answers your question.
    Friday, May 28, 2010 3:03 AM
  • 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

    Thursday, June 17, 2010 5:07 PM
  • 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.

    Tuesday, June 22, 2010 7:40 PM
  • 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
    Monday, July 05, 2010 10:43 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?

     


    Tim Carper
    Friday, August 27, 2010 3:42 PM
  • 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

    Monday, October 04, 2010 7:26 PM
  • 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

     

    Wednesday, October 06, 2010 11:25 PM
  • 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.


    Uziel Bueno SQL Server MCTS
    Tuesday, April 12, 2011 1:41 AM
  • 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!

    Wednesday, April 13, 2011 8:39 PM
  • Hi ClynnH i could solve this problem.

     

    Here is the post:

    http://sqlservermemories.blogspot.com/

     

    Hope this solves other people issues.


    Uziel Bueno SQL Server MCTS
    • Proposed as answer by UzielB Monday, April 25, 2011 4:36 PM
    Friday, April 15, 2011 4:12 PM
  • Is this solution worked for any of you?, please leave a comment.
    Uziel Bueno SQL Server MCTS
    Monday, May 02, 2011 4:03 PM
  • 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.
    Wednesday, September 28, 2011 9:24 AM
  • 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
    Wednesday, September 28, 2011 5:20 PM
  • 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

    Thursday, October 20, 2011 6:22 AM
  • good answer helped me solve problem
    Friday, November 18, 2011 7:17 AM
  • Thanks to Lazy_Writer, that solution worked perfectly.
    Monday, November 21, 2011 6:35 AM
  • 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
    Saturday, January 21, 2012 2:59 AM
  • 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!

    :)

    Tuesday, April 17, 2012 10:18 AM
  • The setting changes to DTC worked for me.
    Wednesday, May 23, 2012 6:38 PM
  • It helps a lot to me. Thanks you to posted ur valuable answer in this article.
    Monday, June 25, 2012 3:08 PM
  • 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
    Saturday, November 03, 2012 8:45 PM
  • 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/
    Thursday, March 07, 2013 10:01 AM
  • Thanks a ton Lazy Writer. Your solution worked like a charm.
    Tuesday, March 26, 2013 5:56 AM
  • in Windows 2012 ...please follw the same steps in 2008 as well
    Friday, August 30, 2013 7:29 AM