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!

How can I tell which CTP I have?

I received SQL Server 2005 CTP in my June MSDN shipment. But it seems to be missing specific functionality that I would expect in the latest June CTP. Specifically, I don't see the 'Transfer Database' task in the Integration Service project template. A fellow on the NG told me that he has this task in his environment. He reported his SQL version as being 9.00.1187.07, while my version is 9.00.1116.00. So which CTP do I have? Am I using April or June?

Thanks for your help!

Here's my complete SQL Server version info:

Microsoft SQL Server Management Studio (expires in 365 days)
9.00.1116.00
Microsoft Analysis Services Client Tools 2000.090.1116.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00
(xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50215.44
Operating System 5.1.26001187.07 is June.
1116 is April.