My linked server was fine until now and i was selecting data using some join statement for filtering the but suddenly its returning following error. i cannot execute any query against my linked server.
Linked server is still available as i tried a TEST CONNECTION from server object and it was successful
Msg 65535, Level 16,State 1, Line 0
Sql Server Interface:Error Loading Server/Instance Specified (xfffffff)
oledb provider sqlnc111 for linked server "MSNAC/MSDB5" returned an error " A login time out expired
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.
How to solve this issue ?
If nobody has made any configuration changes, your queries may be timing out because of a big data change. Try to, at least temporarily, set the following option:
USE Yourdatabase;
GO
EXEC sp_configure 'remote query timeout', 0;
GO
RECONFIGURE;
GO
If it goes fine with these settings, your queries were simply timing out because of the set limit. Experiment a bit with your queries, see what is an appropriate limit to put in this properties (leaving it to infinity isn't a good idea) and reconfigure once again.
NOTE: the number is in seconds.
To read up more on this issue, check the documentation for remote query timeout.