Hi all,

I have a VB app that that runs on one PC and accesses SQL server running on another PC using ADO. I am trying to determine the best way to detect when SQL server is no longer available due to being shutdown or a network problem.
The app keeps a connection open all the time. It uses recordsets to get read and write data when needed.
I would like to detect the moment SQL server goes down so that before running a query the state of the server will be known and handled accordingly.
I found the Disconnect event and the State property in the Connection object of little use.

Thanks in advance,

George.

It is bad design having an open connection to the database for a long time.

Open only a connection when you really need to connect to the database. Your machine will never now anyway, if a cable is unplugged or a switch/hub break down in your network.

Peso, isn't that what for instance SQL Management Studio does when you open a query-window against a DB? Will not that connection be held for the duration of the query-window?

best regards
Gert

Yes.
Who said that is good design for business applications?

It is better to close the connection when the call to the database is made and the work is finished.
If you are using connection pooling, the new connection when you want to to additional work, will be very fast.

This way you will now faster when the server cannot be connected. You will now in less than default 15 (I often set this to 5) seconds that the server is down. If the connection is open the whole time, you will have to rely on the CommandTimeout parameter before the error is thrown back at you, and this value can be anything from default 30 (which i often set to 300) seconds to infinity...

The Disconnect event is only thrown when manually disconnecting, not when problem with network arises.