Code Samples to Test If a SQL Server is Available

5 Jan 2006 12:56 PM

Andy

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.

5 Jan 2006 4:31 PM

Rob Foster

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

Show quote

"Andy" wrote:

> 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.

5 Jan 2006 6:18 PM

William (Bill) Vaughn

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.
__________________________________

Show quote

"Andy" <A***@discussions.microsoft.com> wrote in message
news:EF7543EE-BDF6-49F0-BB85-0D4EBD7F7940@microsoft.com...
>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.

14 Jan 2006 12:21 PM

Fox

Just put
conn.Open()

in try - catch block

and redirect to another SQL Server when catch fires

good luck

Show quote

"Andy" <A***@discussions.microsoft.com> wrote in message
news:EF7543EE-BDF6-49F0-BB85-0D4EBD7F7940@microsoft.com...
>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.

15 Jan 2006 3:20 AM

W.G. Ryan eMVP

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.
Show quote

"Fox" <m***@job.am> wrote in message
news:Ov6ZUUQGGHA.3532@TK2MSFTNGP14.phx.gbl...
> Just put
> conn.Open()
>
> in try - catch block
>
> and redirect to another SQL Server when catch fires
>
>
> good luck
>
> "Andy" <A***@discussions.microsoft.com> wrote in message
> news:EF7543EE-BDF6-49F0-BB85-0D4EBD7F7940@microsoft.com...
>>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.
>
>