SQL connection timeout -- is it pretty useless or what?

  • Thread starter Thread starter Rob R. Ainscough
  • Start date Start date
R

Rob R. Ainscough

I'm trying to code a routine that will test the connection to a server and I
would like to specific how long (aka timeout) the .Open() should wait before
a timeout error is triggered.

I've tried "connection timeout=10;" in the connect string, but that still
doesn't timeout til 30 seconds have passed. I can't set the
Connection.ConnectionTimeout as that is a read only property. So am I out
of luck? Is there no way to quickly test a SQL connection in .NET 2.0?
Operative being quickly as in I can define how long to wait?

Please tell me I don't have to initiate a thread for the SQL connection and
have the thread stop after xx seconds. There must be a more simple approach
no?? VS 2005 is supposed to be RAD (rapid application development) not WAAD
(work around application development).

Thanks, Rob.
 
Can you show us the whole connection string? Perhaps there is a typo?

And, at least according to the MSDN documentation, the default is 15
seconds, not 30.
 
Dim Connection As New SqlConnection("Server=" & ServerName & ";" _
& "uid=" & UserID & ";" _
& "pwd=" & Password & ";" _
& "database=" &
DataBaseName & ";" _
& "Max Pool Size=200;" _
& "connection timeout=15;")


http://msdn2.microsoft.com/en-us/li...t.sqlconnection.connectiontimeout(d=ide).aspx

My MSDN doc says:

"Gets the time to wait while trying to establish a connection before
terminating the attempt and generating an error."

"The time (in seconds) to wait for a connection to open. The default value
is 15 seconds."

When I execute the Connection.Open() it takes 43.9 seconds before an error
is triggered. I check the Connection.ConnectionTimeOut value and it is set
to 15.
 
The issue is usually that the timeout counter does not start until network
connectivity has been established. If the NIC card can't see the wire, it's
programmed to wait for quite some time before complaining. This is by design
and due to the way the TCP protocol works--it permits you to move wires
around or use a switch/router/hub without worrying that it will disturb the
network. We've talked about this issue for decades--there is no easy way to
resolve it.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
I was looking it going thru SQL instances but realized that will only work
with local networks.

It seems to me that building in this timeout to permit "move wires around"
is building a solution (with an large negative impact) for a problem that
might happen .0001% of the time? Just about any shop I know, usually
provides "down time" for this activity, so why introduce a 45 second
timeout. Does that make sense to you?

So what is the point of having a TimeOut property? I set it to 90 seconds
and it timed out in 130 seconds, I set it to 2 seconds and it timed out in
45 seconds, I set it to 60 seconds and it timed out in 120 seconds -- just
seems to be to random and very useless to me?

Any way to quick test for a response (with a timeout) from an IP address and
SQL listening port -- hackers seem to be able to do it so it must be pretty
easy.

Rob.
 
It's more than just "moving wires around"--it's a function of the
fundamental design of the TCP protocol at the hardware layer. Yes, there are
Framework APIs that can test for network availability but they too are
dependent on the NIC to respond--which it won't if there is no wire
attached. Connection timeout was more useful (and more necessary) in the
early days when a server had far less CPU horsepower to run it and the
server or network could be too busy to respond in time.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Bill,

This is VS 2005 -- maybe in the "old days" this was an issue, but again,
this is VS 2005?

What is sounds like your suggesting, it that the OS DDK for network drivers
is very much legacy code and there is not way to "adjust" the timeout
response from a NIC. Regardless, assuming the NIC is fine (which it is) the
timeout interval is still a useless setting in all but the most extreme
cases -- isn't it? If I issue a open on a connection and don't receive a
response within the amount of time specified, it should trigger an exception
and let me deal with it, the framework API is NOT dealing with the issue
correctly, if it did, timeout would work as advertised.

This is just another case of more features in .NET framework 2.0 that work
half ass and not reliable. Who the hell wants to write .NET applications
when much of the functionality just doesn't work as explained in their own
freakin' documentaiton??

Sorry Bill, I've read your books and they are good, but VS 2005 and .NET 2.0
really is the worst pile of crap I've seen from Redmond yet. If this is the
best M$ can do, then they've got some SERIOUS problems. Before you say move
to Linux or some other OS ....

Not sure going from one user intensive OS to another user intensive OS is
going to help solve anything beyond my immediate problems. The real issue:

1. Developers don't think outside their box (hence 80% of the population do
use what we produce)
2. The complexity of the OS is preventing complete QA cycles (more security
holes, more bugs)
3. Security should NOT be IN THE WAY and should NOT be the responsibility
of the user
4. Solid, reliable, well documented dev tools are the key to success

There are solutions to all three of these, yet nobody wants to spend the
time, money and resources on it -- and this is exactly how one dooms ones
fate to a flat 20% market share. Are the light bulbs going on? Not at
Microsoft and certainly not open source Linux or any other flavor. Going
with an alternative just because it is an alternative still doesn't address
the three issues above.

My hope is that a well funded group will see the light (and business/profit
potential) and come out with an OS (and no the Mac OS is no better) that is
truely for everyone.

Rob.
 
Ah, in some respects I agree. We've raised this issue from the earliest
Windows 1.0 days (when I was in the Developer Liaison group at MS). We
raised it at MSU when OS/2 arrived and again as the first NT systems were
built. I expect the problem is still out of MS's control--it's defined by
the way that the Ethernet protocols work which MS did not invent or define
and the protocol that everyone has to deal with.

As far as an OS that everyone would like, I think that's a pipe dream. There
is no universal solution and there should not be. What's right for games is
not right for business clients, what's right for hosting services is not
right for client applications.I think MS has a very hard job trying to
please everyone and the world's governments as well. And no, it's not an
ideal situation but it's what the market has adopted.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Back
Top