none
OLE DB provider "SQLNCLI10" for linked server "" returned message "No transaction is active.".

    Question

  • Hi,

    So I went through all the help I could google but my problem persists.

    I have server A SQL 2008 R2 and server B SQL 2005. they are on different networks to be clear. So i have this linked server. all set up. But when I try DT like f.e.

    set

    xact_abort on

    BEGIN

    DISTRIBUTED TRANSACTION

    select

    top 10 * from serverB.database.schema.table

    commit

    transactionI got

    OLE DB provider "SQLNCLI10" for linked server "B" 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 "B" was unable to begin a distributed transaction.

    I set another SQL 2005 which is on the same network and the same statement works. To confuse even more I got Oracle on the other network and the DT to the oracle works as well.  At the same time Admin in the other company did similar tests and he can confir that within the network DT works.  The firewall- hmm honestly it doesn;t exist between the 2 networks anymore as we put one big hole for all the traffics between the 2 networks.

    By saying the servers are on different networks I mean there is company A and company B with their own domains.

    Is there a tool like dtcping which will work form machines on different networks/domains?

    any suggestions?

    thank you

    Anna

    Tuesday, October 09, 2012 12:59 PM

Answers

  • Hi,

    First, please make sure the linked server (SQL Server 2005 on different network) connection works:

    1. Find the linked server under “Linked Server” object.
    2. Right click the linked server name and click “Test Connection”.

    If the issue persists when linked server connection works, let’s refer to the steps on the following documents to troubleshoot the issue:

    FIX: Error message when you run a distributed query in SQL Server 2005 or SQL Server 2008: "OLE DB provider 'SQLNCLI' for linked server '<Linked Server>' returned message 'No transaction is active'":
    http://support.microsoft.com/kb/954950

    How to troubleshoot error 7391 that occurs when you use a linked server in SQL Server:
    http://support.microsoft.com/kb/306212


    Allen Li

    TechNet Community Support

    Wednesday, October 10, 2012 5:55 AM