Linked Server behaving strange

  • Bhuvnesh

    SSC Guru

    Points: 59344

    hi

    i have two servers D2\dev and IN\dev ..both servers were behaving as Linked server for each other for a long time (say from last 30 days) but yesterday we receive error

    when we try do execute a remote query at IN\dev server

    ------Queries ------------------------------

    select top 1 * from [d2\dev].CVENT_DBA.dbo.SYNC_OBJECT_RECEIVE

    go

    select top 1 * from [d2\DEV].cvent_prod.dbo.contact

    ------------------------------------------------------------------------

    -----ERROR_-------------------------------

    OLE DB provider "SQLNCLI" for linked server "d2\dev" returned message "Unspecified error".

    OLE DB provider "SQLNCLI" for linked server "d2\dev" returned message "Communication link failure".

    OLE DB provider "SQLNCLI" for linked server "d2\dev" returned message "Communication link failure".

    Msg 64, Level 16, State 1, Line 0

    TCP Provider: The specified network name is no longer available.

    Query was cancelled by user.

    -----ERROR END-------------------------------

    but the strange thing is that first query is working fine but Second query giving above ERROR

    we are using two diff databases in two queries

    if i m not wrong linked server setting cannot be database specific ?

    now below are the linked server settings ;

    ---At D2\dev server

    /****** Object: LinkedServer [in\DEV] Script Date: 12/02/2008 18:22:17 ******/

    EXEC master.dbo.sp_addlinkedserver @server = N'in\DEV', @provider=N'SQLNCLI', @datasrc=N'in\DEV'

    GO

    EXEC master.dbo.sp_serveroption @server=N'in\DEV', @optname=N'collation compatible', @optvalue=N'true'

    GO

    EXEC master.dbo.sp_serveroption @server=N'in\DEV', @optname=N'data access', @optvalue=N'true'

    GO

    EXEC master.dbo.sp_serveroption @server=N'in\DEV', @optname=N'dist', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'in\DEV', @optname=N'pub', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'in\DEV', @optname=N'rpc', @optvalue=N'true'

    GO

    EXEC master.dbo.sp_serveroption @server=N'in\DEV', @optname=N'rpc out', @optvalue=N'true'

    GO

    EXEC master.dbo.sp_serveroption @server=N'in\DEV', @optname=N'sub', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'in\DEV', @optname=N'connect timeout', @optvalue=N'0'

    GO

    EXEC master.dbo.sp_serveroption @server=N'in\DEV', @optname=N'collation name', @optvalue=null

    GO

    EXEC master.dbo.sp_serveroption @server=N'in\DEV', @optname=N'lazy schema validation', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'in\DEV', @optname=N'query timeout', @optvalue=N'0'

    GO

    EXEC master.dbo.sp_serveroption @server=N'in\DEV', @optname=N'use remote collation', @optvalue=N'true'

    ------At IN\dev server

    /****** Object: LinkedServer [D2\DEV] Script Date: 12/02/2008 18:23:38 ******/

    EXEC master.dbo.sp_addlinkedserver @server = N'D2\DEV', @srvproduct=N'SQL Server'

    GO

    EXEC master.dbo.sp_serveroption @server=N'D2\DEV', @optname=N'collation compatible', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'D2\DEV', @optname=N'data access', @optvalue=N'true'

    GO

    EXEC master.dbo.sp_serveroption @server=N'D2\DEV', @optname=N'dist', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'D2\DEV', @optname=N'pub', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'D2\DEV', @optname=N'rpc', @optvalue=N'true'

    GO

    EXEC master.dbo.sp_serveroption @server=N'D2\DEV', @optname=N'rpc out', @optvalue=N'true'

    GO

    EXEC master.dbo.sp_serveroption @server=N'D2\DEV', @optname=N'sub', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'D2\DEV', @optname=N'connect timeout', @optvalue=N'0'

    GO

    EXEC master.dbo.sp_serveroption @server=N'D2\DEV', @optname=N'collation name', @optvalue=null

    GO

    EXEC master.dbo.sp_serveroption @server=N'D2\DEV', @optname=N'lazy schema validation', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'D2\DEV', @optname=N'query timeout', @optvalue=N'0'

    GO

    EXEC master.dbo.sp_serveroption @server=N'D2\DEV', @optname=N'use remote collation', @optvalue=N'true'

    ----------------------------------------

    can any one help me in this area ???????????????

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Lowell

    SSC Guru

    Points: 323331

    if it has been working without fail for a long time, and suddenly doesn't work, I'd check the basics:

    is there basic network connectivity to the DF2\dev server? can you ping it? is the service running?

    did someone on the D2\dev server change the password you were using?

    did the database you were connecting to get renamed?

    did the database you were connecting to get restored? maybe it's an orphaned user login problem

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Bhuvnesh

    SSC Guru

    Points: 59344

    no buddy ......Nothing like that !!!

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • ace.spades

    SSC-Addicted

    Points: 408

    Did you find out the solution for your problem? I have the same problem as yours. same scenario.

    I hope you could post the solution here.

    Thanks.

  • Bhuvnesh

    SSC Guru

    Points: 59344

    you saw 2 earlier years back.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • ace.spades

    SSC-Addicted

    Points: 408

    Hehehe but have you found the solution for this? 😀

  • Bhuvnesh

    SSC Guru

    Points: 59344

    i dont really remember the exact cause but i think it happened because of network connection failure.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • ace.spades

    SSC-Addicted

    Points: 408

    I cant find a solution for this.

    My case is, I have many linkservers from Server A. Almost all are functioning. Only some are encountering the errors.

    OLE DB provider "SQLNCLI" for linked server "RemoteServer" returned message "Unspecified error".

    OLE DB provider "SQLNCLI" for linked server "RemoteServer" returned message "Communication link failure".

    OLE DB provider "SQLNCLI" for linked server "RemoteServer" returned message "Communication link failure".

    Msg 64, Level 16, State 1, Line 0

    TCP Provider: The specified network name is no longer available.

    The wierd thing is, I can directly connect to the RemoteServer from the Server A. The error only appears when I use the linkserver connection.

    One of the Sysad of our company told me that it might be network related, but how come I am able to directly connect to the remote server?? Using the PCName, not even the IP address., By the way the datasource of my linkserver is the ipaddress of the remoteserver, so it should function properly and besides, this has been functional eversince and is used everyday. The error just appears this past few days.

    Whew, hope someone could enlighted me.

  • Bhuvnesh

    SSC Guru

    Points: 59344

    What login credentials you are using to connect with linked server ?

    and During linked server setup in securty tab which options you are using ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • ace.spades

    SSC-Addicted

    Points: 408

    HAHA! Finally Got the solution!

    I've added the Ip address PC Name on the host file of the SQL Server where I created the link server as advised by our SYS AD!

    GREAT! haha

    THanks by the way!

  • ace.spades

    SSC-Addicted

    Points: 408

    You and our SysAd were right all along, it was network problem. nothing to do with the linkserver setup.

    Thanks for your response! appreciate it! 😀

  • Bhuvnesh

    SSC Guru

    Points: 59344

    acebalajadia (4/20/2010)


    I've added the Ip address PC Name on the host file of the SQL Server

    Are you sure :w00t: ? Sql server doesnt have any HOST file , where you have made changes , can you share with us ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Neal.Appell

    Grasshopper

    Points: 17

    We were receiving a similar error message. 

    OLE DB provider "SQLNCLI11" for linked server "<server>" returned message
    "Communication link failure".
    Msg 64, Level 16, State 1, Line 0
    TCP Provider: The specified network name is no longer available.

    We also applied the recommended changes to turn off Offloading. This did not resolve our problem.

    We were running an Insert Select statement from server A to Server B. The statement include two varchar(max) fields. When both varchar(max) fields were removed from the insert statement, the query succeeded. When the fields were added back the query, the query failed with the above noted error. We were able to trace the issue to the inclusion of {Tab} Ascii Char(9) values embedded in varchar(max) field values. When the Tab chars were replaced with a {Space} char the query ran successfully. Characters such as Line Feed and Carriage Return did not cause the same problem.

Viewing 13 posts - 1 through 13 (of 13 total)

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