How can I verify the availability of an SQL server

Hello everyone,

I have an ASP application mainly connected to one SQL database that works great but now I am trying to add some functionality that requires to connect to another remote SQL server. Till now all is fine except that the remote SQL server is not always online and of course when this happens my ASP application stops with the following error:

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80004005)
[DBNETLIB][ConnectionOpen (Connect()).]Specified SQL server not found.

in my Global.asa I setup my session variables for DSN connections and in my pages I call my SQL connection as follow:

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open Session("MySQL")

SQL = "my SQL statement here;"
Set RS = Conn.Execute(SQL)

Every time the page hits the Conn.Open line I get the error if the remote SQL is not online!!!

Is there a way I can check a sort of returned error code holding the connection status before getting to the Conn.Open line?

Any help would be greatly appreciated.

Thx in advance

You could put an error handler around it to catch the error if the server is not available

Check this site for some information on error handling: http://www.magictree.com/vbcourse/11design/errors.htm

So you could create a function to see if the server is online using the error handler of asp.

|||

Thank you Mark,

Well of course creating an error handling is what I am trying to do and the link you supplied is great, except it is for client side scriptting. ASP page are server side scrippting. In ASP.NET I could use it but not in pure old fashion ASP, I mean not that I know of!!!

For sure I need more info on how to!!! Please please please anyone!

The only other way I have found is to do it directly from my primary SQL server in a procedure that I can call from my ASP page but there too I need help so I rather follow one lead first and when exhausted the other.

Thank you again Mark

|||

AAAAAAAAAAHHHHHHHHH

Actually I am going to repeat myself but thank you Mark you put me on the right track and I got my solution.

Here it is:

<%
Response.Buffer = True
On Error Resume Next

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open Session("MySQL")

Set ConnRemote = Server.CreateObject("ADODB.Connection")
ConnRemote.Open Session("MySQLRemote")

If Err.Number <> 0 Then
Response.Clear
'set my code without remote SQL here
Else
'set my code with remote SQL here
End If
%>

Thank you so much... I knew I would get an answer in here!