February 24, 2010 at 5:12 am
I am having an issue with a Linked Server running queries against that connection. When I first run the query for example:
select top 1 *
from [SQL-02].idl_reporting.dbo.stoprenewalentries
I get the following error:
OLE DB provider "SQLNCLI10" for linked server "SQL-02" 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.".
OLE DB provider "SQLNCLI10" for linked server "SQL-02" returned message "Communication link failure".
Msg 233, Level 16, State 1, Line 0
Named Pipes Provider: No process is on the other end of the pipe.
However, if I persist in trying the command, after about 5 attempts, I get back the result I am expecting.
PolnumPdidHist IDReasonDateStamp
ERA105921IDL46683C98DAD0430A84EF00E459D829661manual premium amendment2008-10-07 07:05:39.290
From then I can keep running any query against this linked connection, but if I then don't run anything for 10 to 15 minutes, the connection brings back the error message in the first instance.
I am getting this from 2 different servers connecting to this one server, and have no problems with the linking of the servers the other way round, and as far as I can tell, their setup is identical (though obviously they can't be)
Has anyone come across this before?
February 24, 2010 at 6:33 am
Check the connection SQL Server Configuration on both servers and check named pipes is enabled and the other connection protocols are ok. Below link might help you as well http://technet.microsoft.com/en-us/library/ms175496.aspx
February 24, 2010 at 8:59 am
I've done as suggested, and all settings are okay, but I still get this error on the first 3-5 attempts, but then it is okay for a while before reverting back to getting the error again after inactivity.
It's really puzzling, I've had a look at the services running on the server to see if any involved are on manual startup, but everything is running as it should be.
February 24, 2010 at 10:37 am
Do you get the same behavior using the OPENQUERY command?
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply