Connection Timeout Problems

Hi,
We want to connect to a remote SQL server to retrieve stock levels in real
time. If the SQL server is unavailable (it is not on a totally reliable
network connection) we want to display stock levels from a local backup
database. So, if we can't connect to the remote server in say 5 seconds, we
want the connection attempt to time out and return control to our
application (a web application on windows 2000 written in ASP). I cannot get
the connection attempt to timeout if the remote server is unavailable. I
have tried:
- Setting the ConnectionTimeout on the ADO connection object. As far as I
can see, if the server is totally unavailable, setting the Timeout property
has no effect at all
and
- Writing a VB component which opens the connection asynchronously and polls
to see whether it has opened successfully. This sort of works, except that
if I try to cancel the attempt to open the connection, this hangs for about
30 seconds before returning control to my component.
The connection string I am using is: "provider=sqloledb;Server=<Server IP
Address>;Initial Catalog=<DBName>;user Id=<userid>;Password=<password>
Is there any reliable way of setting a connection timeout for this
situation?
Thanks,
Peter
Hi Pete,
The loginTimeout issue is due to the underlying networking components.
So, before we send the login packet for SQL, we first have to establish an
underlying tcp session. (3 way handshake). If this doesn't complete, the
LoginTimeout is never even used. You may be able to reduce the timeout by
adjusting the tcp settings.
See the following kb;
176257 PRB: Client Login Does Not Time Out If the Server Is Offline
http://support.microsoft.com/?id=176257
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.