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.
172.nn.nn.32\sqlexpressactually points to172.nn.nn.11\sqlexpressaddress? Have the any IP addresses been changed recently? Is there a172.nn.nn.11machine on the network? Is it just some random machine or different SQL Server maybe? – Marek Grzenkowicz Dec 22 '10 at 9:36