Post reply

Strange Link Server Issue

  • June 22, 2019 at 8:54 am

    #3656430

    Hello,

    I have an application A that connects to  different servers to get the data during its startup.

    Application A whose DB is on Server 1. It connects to DB2 on Server  2 and DB 3 On  Server 3 to get the data through  a stored procedure. We have created linked  server for the connection. Please note Server 1 is SQL 2016, Server 2 is SQL 2016 and Server 3 is SQL 2008 R2. We have given all the security rights for distributed transaction. Enabled the distributed transaction in linked server property window. Please note All the server instances are defined using ip and port and the same exists in sys.servers table.

    Here is the scenario

    On Application Startup  -- Stored procedure stp_Startup executes.

    1. stp_Startup calls another stp Stp_DownloadMaster. This stp connects to DB2 on Server 2 and executes a stored procedure which returns the data which in turn is stored into a temp table.
    2. After above stp_Startup calls another procedure stp_DownloadDetails. This stp connects to DB3 on Server 3 and executes a stored procedure which returns the data which in turn is stored into a temp table.
    3. After step 2 stp_Startup calls another procedure stp_DownloadMarketDetails. This stp connects to DB2 on Server 2 and tries to execute the stored procedure but while executing an error of distributed transaction occurs "The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server was unable to begin a distributed transaction"

    Strange part is that both in step 1 and  step 2 the execution happened successfully but yet in step 3 this issue came.

    Kindly help as to what else can be done. Also i need to know the internal working of linked server.

    Thanks

    Saumik Vora

     

     

  • June 22, 2019 at 5:01 pm

    #3656498

    Have you tried using client provider 10 to connect to your 2008 sql server?

    That could be the problem.

     

    Cheers,
    John Esraelo

  • June 23, 2019 at 9:01 am

    #3656648

    Hi,

    Just some time back i came to know that in a single machine they are using two network cards. The setup runs properly in one card, but gives error when changing the network card.

    Regards,

    Saumik Vora

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply