Red Gate forums :: View topic - Random connection failures when executing remote queries
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Multi Script 1
SQL Multi Script 1 forum

Random connection failures when executing remote queries

Search in SQL Multi Script 1 forum
Post new topic   Reply to topic
Jump to:  
Author Message
JLankford



Joined: 27 Aug 2008
Posts: 2

PostPosted: Wed Aug 27, 2008 10:26 pm    Post subject: Random connection failures when executing remote queries Reply with quote

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
Back to top
View user's profile Send private message Yahoo Messenger MSN Messenger
Ben Pinchen



Joined: 28 Dec 2006
Posts: 157
Location: Red Gate

PostPosted: Mon Sep 01, 2008 8:09 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
JLankford



Joined: 27 Aug 2008
Posts: 2

PostPosted: Wed Sep 03, 2008 4:55 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Yahoo Messenger MSN Messenger
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group