sql server connect: Hi,

We have a requirement to test connectivity of clients to
the SQL 2000 database. I am very new to this :-( ... well,
what I need to accomplish is when the database is running
all the clients should be able to connect to it also when
the SQL system goes down for some maintenance the clients
should not abort the connection but should keep on trying
to reestablish the connection. Once the SQL server comes
back online the clients must be able to reconnect to the
database with out any intervention.

My questions are,

1.) What is the client software required to accomplish
this?? I also need to know if it is possible to set a
timeout period for the reconnection to the database.

2.) Does the default Client tools that come with SQL2000
support this kind of re-connection?

3.) What happens to the transaction that might be
happening when the server goes down? Can we recall/recover
these transactions?

Please help.

Thanks,

Answers inline:

1.) What is the client software required to accomplish
this?? I also need to know if it is possible to set a
timeout period for the reconnection to the database.
A: You would need to write you code to trap connection failed and query
cancelled type messages and to retry after X seconds for Y number of times.

2.) Does the default Client tools that come with SQL2000
support this kind of re-connection?
A: Sort of. If you have a query up and running in Query Analyzer and the
SQL Server is shutdown, then the results window in Query Analyzer will
report an error. If you leave the window up and wait for SQL Server to
start again, you can just execute the query again and it will start over
without needing to go through the connection dialog. But it will not
automatically retry the query for you, you have to execute it again.

3.) What happens to the transaction that might be
happening when the server goes down? Can we recall/recover
these transactions?
A: If the transaction was not complete then it will be rolled back. Your
application has to be written to recognize that the transaction did not
complete and take whatever action is appropriate for your application.

Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.