How to deal with SQL connection error in ASP code

kwilliams · February 15th, 2010, 11:03 AM

We have an application that pulls data from several servers, and one of the external servers that we don't have control of went down recently. When our code attempted to open the connection string to that server, it pulled our entire application down.

So I need to know how to check that a connection is working first *before* attempting to open it, so that it won't bring everything else down with it. here is what the connection string looks like:

Code:

Dim strConn As String
strConn = "Provider=SQLOLEDB; Data Source=SERVERNAME; Initial Catalog=DATABASENAME; User ID=USERID; Password=PASSWORD"

And here is the line of code that had the error:

Code:

objConn = Server.CreateObject("ADODB.Connection")
        objConn.Open (strConn) '<<<----- ERROR OCCURRED HERE!!!

And and all help is appreciated.

Old Pedant · February 15th, 2010, 02:54 PM

Code:

<%
...
objConn = Server.CreateObject("ADODB.Connection")
On Error Resume Next
    objConn.Open (strConn)
On Error GoTo 0
If objConn.State <> 1 Then
    ... unable to connect to that db ...
    ... so you get to figure out how to handle that ...
    ... note that objConn is now useless, so don't attempt to use it ...
 
Else
    ... should be okay 
 
End If
...

kwilliams · February 15th, 2010, 05:02 PM

Thanks for the quick response. I'll take your suggested code and give it a try. thanks.

Old Pedant · February 15th, 2010, 05:18 PM

Fair warning: The attempt to open *could* take some time if you are trying to open across a network and the network is down. Because the driver will need to wait for the timeout on the network connection. You can control that by specifying a shorter time for the connectiontimeout:

Code:

Set conn = ...
conn.ConnectionTimeout = 10 
On Error Resume Next
    conn.Open ...

Just don't set the time too short so that even normal connection opens fail.

mat41 · February 15th, 2010, 05:27 PM

Nice! This has happened once in a blue moon for me....

kwilliams · February 15th, 2010, 05:59 PM

Thanks for that information. Everything seems to be working. Hopefully it won't happen anytime soon though:)

kwilliams · February 16th, 2010, 04:13 PM

Old Pendant,

I've added the code without a problem, but I'm not sure how to check that what I've added works because that connection is now working. Do you have any suggestions on how I could test it?

Old Pedant · February 16th, 2010, 04:22 PM

Sure. Turn off the SQL Server engine that you are trying to connect to.

Hopefully, you can do this at some point in time then the sytem isn't in use.

Alternative: Install SQL Server (could be Express version, so free) on some other machine and connect to it only for this purpose. Then shut it off.

kwilliams · February 16th, 2010, 04:25 PM

Thanks for that information. I wouldn't be able to do the first, but I already have SQL Server Express installed on my machine, so the second option should work. Thanks again for your help!