Test SQL Connection

[Steve R.]

Is there a quick test to verify the status of your SQL connection? We had a network outage today.

I have a "start" form that does not connect to the server until the user selects what they want to do. What I would like is to have the "start" form test the SQL connection before the user makes any selection; otherwise the user will be surprised with an error message.

I just did this for another Access database that is not connected to the SQL server.

    Rem Quick check to verify if the data file can be accessed.
    If Dir("P:\FEDERAL CONSISTENCY\DATABASES\BackEnd\consistency_be.mdb") = "" Then            
            MsgBox "Data File NOT Found." & Chr(13) & "Check network connection and file location." & Chr(13) & "P:\FEDERAL CONSISTENCY\DATABASES\BackEnd\consistency_be.mdb"
        Else
            Rem Nothing, Everything OK.
        End If

I suppose that I could try to open a recordset as a test and if that fails and issue a message based on that. But I am hoping for a less brutish solution.

 

[SQL_Hell]

Hi,

I dont really see how checking the sql server connectivity beforehand would solve anything, what happens if the network goes down after the test has been performed? the user will still be surprised as you put it.

You could perhaps put some better error handling into your form, and open the connection earlier to stop the user filling out a form before getting an error message. In which case you only need to open the connection and handle the error if the connection fails, no operation is needed as such.

Isn't the best solution to ensure you have a reliable network?

How often do you have network outages?

 

[Steve R.]

Hi,

I dont really see how checking the sql server connectivity beforehand would solve anything, what happens if the network goes down after the test has been performed? the user will still be surprised as you put it.

You could perhaps put some better error handling into your form, and open the connection earlier to stop the user filling out a form before getting an error message. In which case you only need to open the connection and handle the error if the connection fails, no operation is needed as such.

Isn't the best solution to ensure you have a reliable network?

How often do you have network outages?

Not very often. Simply a congruence of a network failure and the availability of sufficient free time to generate thoughts on improvement. Got to think of some make-work project to get paid!

 

[Steve R.]

DCrake posted the following in response to another question "dont run if network offline":

"Here is a function that you can use to see if there is a valid connection between the client and the internet."

Private Declare Function InternetGetConnectedState Lib "wininet" _
(ByRef dwFlags As Long, _
ByVal dwReserved As Long) As Long

Private Function AmICOnnected() As Long
Dim lReturn As Long
AmICOnnected = InternetGetConnectedState(lReturn, 0)
End Function

Private Sub cmdGo_Click()
If AmICOnnected = 1 Then
MsgBox "You are Connected"
Else
MsgBox "you are not connected"
End If

End Sub

I have not tested this code. It is obviously a step in the right direction. The residual question that I would have is that MS SQL is a network service, so you could still have an internet connection even if the service is not functioning. In that situation, the code (I assume) would give a false indication that the MS SQL server would be available.

Is there a more specific test?

 

[DCrake]

Herre is a snippet of code taken from a Visual Basic App of mine that checks for connectivity. See it this suits your needs

Private Sub CmdTestConnection_Click()
Dim TmpDBProvider
Dim TmpDatabase
Dim TmpConn             As New ADODB.Connection
On Error GoTo ErrConn

    Set TmpConn = New ADODB.Connection
        With TmpConn
            '.Provider = TmpDBProvider
            .Mode = adModeShareDenyNone
            .Open "dsn=ICATS" 'TmpDatabase
        End With
    Dim Tbl As New ADODB.Recordset
    Set Tbl = New ADODB.Recordset
    With Tbl
        .Open "MPI", TempConn, adOpenKeyset, adLockOptimistic, adCmdTable
    End With
        Tbl.Close

    MsgBox "Test Connection Sucessful"
    
    TmpConn.Close
    Set TmpConn = Nothing
    
    Exit Sub

ErrConn:
    Select Case Err.Number
        Case -2147217843
            MsgBox "Connection failed due to unknown user name '" & Me.TxtUserId.Text & "'"
        Case -2147467259
            MsgBox "Sql Server does not exist or access denied"
        Case Else
            MsgBox Err.Number & " " & Err.Description
    End Select
    Exit Sub

End Sub

This code uses the system DNS for its checking however you can change that again to suit your needs.

David

 

[Steve R.]

Thanks very much.

 

[Steve R.]

Here is a snippet of code taken from a Visual Basic App of mine that checks for connectivity. See it this suits your needs

.....

This code uses the system DNS for its checking however you can change that again to suit your needs.

David

I experimented with it a bit, and I finally got it work after originally posting. Additionally, I seem to have stumbled upon a search term that gave me some positive hits for verifying connectivity to SQL Server.

How to use Odbcping.exe to verify ODBC connectivity to SQL Server Unfortunately, I don't have access to Odbcping.exe. Microsoft notes that the utility has been deprecated for SQL Server 2005. Guess which version of SQL Server we have!

I found a similar (free) utility here SQLPing3. There is a command line version, but I was unable to figure out how to make it function. I left a message on their forum.

There is also this paper: Lab 18.1 --- Detecting SQL-Server

That's it for now. Since we seldom have an issue accessing the server, this really isn't much of a concern. Mostly it is a learning exercise. I will plug away as time permits.