Code Samples to Test If a SQL Server is Available

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Andy - 05 Jan 2006 12:56 GMT

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.

Reply to this Message

Rob Foster - 05 Jan 2006 16:31 GMT

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

> 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
[quoted text clipped - 5 lines]
>
> Thanks for any help on this.

Reply to this Message

William (Bill) Vaughn - 05 Jan 2006 18:18 GMT

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.

Signature

____________________________________
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 am trying to find a way to verify if a SQL server is available before I
>try
[quoted text clipped - 9 lines]
>
> Thanks for any help on this.

Reply to this Message

Fox - 14 Jan 2006 12:21 GMT

Just put
conn.Open()

in try - catch block

and redirect to another SQL Server when catch fires

good luck

>I am trying to find a way to verify if a SQL server is available before I
>try
[quoted text clipped - 9 lines]
>
> Thanks for any help on this.

Reply to this Message

W.G. Ryan eMVP - 15 Jan 2006 03:20 GMT

You can also use SqlDmo for instance too see if it's available -
http://www.codeproject.com/cs/database/LocatingSql.asp . I'm not
disagreeing with your solution, just throwing in my two cents. Your
approach is definitely valid and a decent way to accomplish the task at
hand.

> Just put
> conn.Open()
[quoted text clipped - 19 lines]
>>
>> Thanks for any help on this.

Reply to this Message

Code Samples to Test If a SQL Server is Available

Free Magazines