locked
Error with linked server after failover RRS feed

  • Question

  • Hi all,

     

    The following query that uses a linked server is giving me the error message below after I initiate a failover (ALTER DATABASE Northwind SET PARTNER FAILOVER).  I have SQL Server 2005 SP2.  I think that without the service pack there is another error too.  

     

    The query is run from a database other than northwind of course.

    select * from DualLink.northwind.dbo.Test1

     

     

    Please note that:

      without a failover it  works perfectly

      it always work if I try to run it a second time - only the first time it fails.

      it fails the first time for each of the open connections.  A new connection that was open after the failover will work fine. 

     

     

    A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

     

    A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

     

    The linked server is defined as

     

    EXEC master.

    dbo.sp_addlinkedserver

    @server = N'DualLink',

    @srvproduct=N'',

    @provider=N'SQLOLEDB',

    @catalog=N'northwind',

    @provstr=N'Server=(local);FailoverPartner=MyIPAddr;'

     

      EXEC master.dbo.sp_addlinkedsrvlogin

            @rmtsrvname = 'DualLink',

            @useself = false,

            @locallogin = 'sa',

            @rmtuser = 'sa',

            @rmtpassword = 'MyPwd'

     

    Thanks,

    Avi

     

    Thursday, February 22, 2007 2:11 PM

All replies

  • I am having the same problem except I don't have a linked server.

    I am running SQL Server 2005 sp1 and Windows 2003 using clustering.

    In addition to your symptoms I have noticed the following:

    1. The error occurs when I make a query through Server Manager and when using a java app that queries a database using an ASP page. However, if I bring up our website as the first request after failover it works fine.

    2. If I failover from Server1 to Server2 and don't make any requests while it is on Server2 and then failover back to Server1 I do not get the error. If I make any requests while it is on Server2 then the first request I make on Server1 after failing over will cause the error.

    If you have solved the problem I would like to know how. If I find a solution I will post it here.

    Blake
    Friday, March 2, 2007 6:32 PM