Code Samples to Test If a SQL Server is Available

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to find a way to verify if a SQL server is available before I try
to make a connection to it. My process is pulling data from SQL into a
spreadsheet, but when the primary SQL server is unavailable, the sheet locks
up for long periods of time while it is waiting to time out before returning
an error. I have a backup SQL server that I can connect to in the event of a
failure on the primary, but I don't know how to test availability first to
avoid the "lock up" of my app.

Thanks for any help on this.
 
Andy,

You can probably setup your timeout to something less than the default in
your connection string like the example below.

string cnString = "Data Source=dbServer;Initial
Catalog=AdventureWorks;Integrated Security=SSPI;Connect Timeout=2"

The connection will timeout after 2 seconds, instead of the default (which
is 30 seconds, I think...).

Rob
 
What Framework are you using? The 2.0 Framework includes several calls that
can enumerate available servers (GetFactory classes) as well as test network
status. I discuss all of these issues in my "Connecting" talk that's
incorporated into my VSLive and Developer Connections conference sessions.
If you're still using the 1.1 Framework, I would suggest using SMO to do the
same. They aren't as easy to use but can do the trick.

--
____________________________________
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.
__________________________________
 
Just put
conn.Open()

in try - catch block

and redirect to another SQL Server when catch fires


good luck
 
Back
Top