Random connection failures when executing remote queries

JLankford

I am getting random connection failures when I run different types of queries:

"Msg 2, Level 20, State 0, Line 0
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. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"

We are using SQL Server 2000, not 2005. Also, I can open Enterprise Manager or Query Analyzer and connect to these servers just fine.

The connection failures are not consistent either. One day a the tool may fail to connect; the next day it may connect fine.

What's going on here?

James

Ben Pinchen

Does this affect multiple servers or just one in particular? Does it seem to affect long running queries or all types of query? This is generally a basic connectivity error meaning the client could not connect to the target SQL Server. The fact that it occurs randomly makes it fairly tricky to troubleshoot.

If there's constantly an issue connecting it can be down to the protocols that you have enabled, firewalls etc. However in your case this doesn't seem to be the cause as sometimes it connects and sometimes not. Do you have TCP/IP enabled on the SQL Server as well as Named Pipes? You can check this by opening the Server Network Utility. If so could you try connecting to the server using TCP/IP to see if it causes the same issues? If you're using a port other than the default 1433 then you'll have to force this in the connection string. When adding the server to your distribution list, choose 'Add a SQL Server not listed' and add the server name with the port number appended afterwards e.g. SERVERNAME\INSTANCENAME,1234

Also, is the network that you're working across fairly solid? Have you checked to see whether there were any network issues at the times that the errors were thrown?

JLankford

The issue is random; not consistent. I may very well run a simple or a more complex query one time and it run fine, immediately execute it again and it may fail the 2nd time. I administer ~100 SQL Servers globally, and they all have a standard configuration - which is pretty much the default when it comes to network settings - yes, they are using port 1433 over TCP/IP and Named Pipes.

Also, I will emphasize that even while I am having these issues with Multi-Script - I do not have them at all when using SQL Query Analyzer or Enterprise manager to connect to the servers.

Thanks,
James