Linked server problem on SQL Server 2005

Posted by BradyKelly on Server Fault See other posts from Server Fault or by BradyKelly
Published on 2010-12-22T09:15:53Z Indexed on 2010/12/22 9:55 UTC
Read the original article Hit count: 653

I have a weird issue and I hope someone can steer me in the right direction for resolving this please. When I execute the following query against a linked server, I get the following error. I can connect to the server in SSMS as a separate server, and execute a similar query against its Deposits table.

The nn.nn is my own replacement to avoid broadcasting our server addresses.

The query:

select 
      td.Batch
    , td.DateTimeDeposited
from 
    Deposits cd
    left join [172.nn.nn.32\sqlexpress].Terminal.dbo.Deposits td on cd.DateTimeDeposited = td.DateTimeDeposited

The error:

OLE DB provider "SQLNCLI" for linked server "172.nn.nn.11\sqlexpress" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "172.nn.nn.11\sqlexpress" 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.".
Msg 65535, Level 16, State 1, Line 0
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. 

Notice how the error is about server 172.nn.nn.11 and not 172.nn.nn.32.

SOLVED (STUPID ME): Somebody had added an extra bit to my query that was scrolled off-screen and was querying the 17.nn.nn.11 server.

© Server Fault or respective owner

Related posts about sql-server

Related posts about sql-server-2005