SQL Linked server error

  • Hi ALl,

    I am getting the following error for linked server.

    Plz help. Thanks in advance.

    OLE DB provider "SQLNCLI10" for linked server "X" returned message "Login timeout expired".

    OLE DB provider "SQLNCLI10" for linked server "X" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".

    Msg 53, Level 16, State 1, Line 0

    Named Pipes Provider: Could not open a connection to SQL Server [53].

    OLE DB provider "X" for linked server "CPSERVER" returned message "Invalid connection string attribute".

  • Hello,

    Has this Linked Server ever worked?

    Can you post a screen-shot of the Linked Server General Properties page? Failing a screen-shot please detail each property-value pair.

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • Thanks John.

    the issue was resolved now:)

  • I'm getting this same problem. Can you tell me how the issue was resolved?

    Thanks!

  • Hello Steve,

    I would ask the same two questions of you i.e. has this Linked Server ever worked, and can you post a screen-shot of the Linked Server General Properties page?

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • THe linked server works sometimes. If I haven't used it in a while, the first time usually fails, but then succeeds everytime after that. Here's the exact error message

    OLE DB provider "SQLNCLI10" for linked server "nychosxsqlvir1" returned message "Login timeout expired".

    OLE DB provider "SQLNCLI10" for linked server "nychosxsqlvir1" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".

    Msg 5, Level 16, State 1, Line 0

    Named Pipes Provider: Could not open a connection to SQL Server [5].

    Collation Compatible - False

    Data Access - True

    RPC - True

    RPC Out - True

    Use Remote Collation - TRue

    Collation Name - (Blank)

    Connection Timeout - 0

    Query Timeout - 0

    Distributor - False

    Publisher - False

    Subscriber - False

    lazy Schema Validation - False

    Enable Promotion of ... - True

    By the way, I'm using SQL 2008 on Windows 2008

    Thanks for your help!

  • Hello Steve,

    As it is an intermittent problem, I would think it is as the message suggests a Network issue.

    When you get the connection failure message in SQL can you ping the Linked Server from the SQL Server?

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • Can anyone provide the solution to this issue? Some one did get the resolution but didn;t put what steps he followed to fix this issue


    Kindest Regards,
    Jaiprakash M Bankolli
    My Blog
    Suggestions for me

  • This is what it was in my case. When I pinged any server, it would reply back once with my cluster Private IP and say destination unreachable and then it would reply back successfully every time afterward. If I hadn't used it in a while, the same problem would happen. When i checked the IP addresses for my public NIC and my Private NIC, here's what I had

    10.1.26.40/255.255.255.0

    10.0.0.1/255.0.0.0

    My Public IP fell into the realm of the Private IP address. Once I changed the Private IP, it was fine.

  • Hi All

    I got the same problem too,

    Here are one(DB3) SQL server 2008 SP1 with CU1 + windows 2008 ( all 64BIT)

    three(DB2,DB4,DB5) SQL Server 2008 SP1 with CU1 + windows 2003 (All 64 BIT)

    The SQL Server start (account + password) all the same

    I can use the linked server in local computer on windows 2008

    I use DB4's SSMS logon to DB3 with db4\administrator and using db3's linked server and do a query form DB4's SSMS it will got the error message

    Msg 5, Level 16, State 1, Line 0

    Named Pipes Provider: Could not open a connection to SQL Server [5].

    IF i use SQL Server ID login to DB4 using DB3's linked it work fine,

    It is work fine on Windows 2003 + SQL Server 2008 using windows authentication

    anyone can help ?

  • Hi,

    I created a linked server on sql server 2005 express edition and tried to access linked server which is sql server 2000 enterprise in my case. But, Infortunately I am not able to connect..I am getting following error...

    Server: Msg 65535, Level 16, State 1, Line 0

    SQL Network Interfaces: Server doesn't support requested protocol [xFFFFFFFF].

    OLE DB provider "SQLNCLI" for linked server "CentralDB" returned message "Login timeout expired".

    OLE DB provider "SQLNCLI" for linked server "CentralDB" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".

    I have checked...Browser is working...remote connection is enabled and their is not problem from firewall. Still I am getting this error. Though If I create a link server vice versa I am able to do so. I mean from sql server2000 to sql server 2005 express edition.

    Let me know if you need any additional information.

    Please help.

    Thank you,

    Mithalesh Gupta

    mithalesh.gupta@gmail.com

    Mithalesh Gupta
    mithaleshgupta@aol.com
    8802955929

  • Hi to All,

    Its a very generous problem, which may have many solutions. But as we have witnessed so many user saying that they have tried everything.

    What solved my problem is..

    I upgraded sql server 2000 from sp2 to SP4 and if you already have sp4 on sql server 2000 then run Instcat.sql. As per my experience I can assure you this will work for sure, if you are exhausted with all the other workarounds.

    Thanks,

    Mithalesh

    mithalesh.gupta@gmail.com

    Mithalesh Gupta
    mithaleshgupta@aol.com
    8802955929

  • Was there a resolution for this? I am having the same issue. I have a linked server setup from sql server 2008 sp2 to sql server 2000 sp4. It will work for 4 or 5 tries then I get the following:

    System.Data.SqlClient.SqlException: Named Pipes Provider: Could not open a connection to SQL Server [5].

    OLE DB provider "SQLNCLI10" for linked server "mscs2" returned message "Login timeout expired".

    OLE DB provider "SQLNCLI10" for linked server "mscs2" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".

    MSCS2 is my clustered datasource for sql 2000.

    If I drop and recreate the linked server. It will start working again.

    This started after the hardware was moved to a new data center and re-ip'd to a new scheme.

    I have been through all of the networking posts and have tried reconfiguring the linked server to no avail.

    Any help would be greatly appreciated.

    Keith

  • I am receiving the same error...

    OLE DB provider "SQLNCLI10" for linked server "servername" returned message "Login timeout expired".

    OLE DB provider "SQLNCLI10" for linked server "servername" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".

    Msg 53, Level 16, State 1, Line 0

    Named Pipes Provider: Could not open a connection to SQL Server [53].

    Has anyone resolved this issue? An answer will be greatly appreciated.

    Thank you,

    Judie

  • Hi,

    I was having exactly the same issue. I noticed that another existing linked server was created utilizing an ALIAS in the Product name and Data source settings. This one seems to be working fine so I decide to create a new alias in Sql Server Configuration Manager for my problematic linked server. Then I created a new linked server utilizing this alias as Product name and Data source.

    I don't know the reason but this seems to solve the problem.

Viewing 15 posts - 1 through 15 (of 16 total)

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