Detect connection failure with SQL Server

[fulvio1981]

I want to detect when connectivity with SQL Server fails, so i can fire an alert for the user.

Does anyone know a bit of code that will help me detect when connectivity with the SQL Server fails (SQL Server goes down, network cable is unplugged etc).

I try with:

If Conn1.State = adStateOpen Then

But it returns always true. :( also with cable unplugged!
Any help would be gratefully appreciated.

thanks

[AndreRet]

If I understand your problem correctly, you want to constantly monitor your connection to SQL?

If so try to add your connection code to a timer control and let that check your connection on every trigger event -

IF myconnectiontoSQL = TRUE then
      'Do nothing
            ELSE
       'Warn client to closed connection
END IF

Timer1.Enabled=FALSE
Timer1.Enabled=True

The best way to still monitor your connection is to write error code that will trap a lost connection. In other words, if a client want to access anything in your database on the server and the connection is lost, let the client know by means of a message box or similar, take client to re-connect to database and then continue to his enquiry.

[fulvio1981]

Yes, AndreRet! you understand exactly my problem.

IF myconnectiontoSQL = TRUE then

Could you explain better your code? Because I try to implement your code but I got an error on this line. (Type mismatch):@

[AndreRet]

Firstly, lets do the error trapping.

Private WithEvents cnMyConnection As ADODB.Connection 'Where cnMyConnection is the name you want to give to the connection
Private WithEvents rsMyRecordset As ADODB.Recordset 'Where rsMyRecordset is the name you want to give to the Recordset in your database

'In your control event i.e. Command1_Click etc. the following
On Error GoTo ServerOff

Set cnMyConnection = New ADODB.Connection
cnMyConnection .CursorLocation = adUseClient

cnMyConnection .Open = 'ENTER YOUR CONNECTION STRING HERE. If you do not know how, folow this link -  http://www.connectionstrings.com/sql-server-2008

Set rsMyRecordset = New ADODB.Recordset

rsServer.Open "SELECT * FROM YourDatabaseName", cnMyConnection , adOpenStatic, adLockOptimistic 'Where YourDatabaseName is the name for your database you want access to.

Exit Sub 'Remember to add the Exit Sub call, otherwise the error will not be trapped, but read.

ServerOff:
MsgBox "There was a problem in connecting to the Server. Switch the Server power on, or Confirm that your connection (Network) cable is properly connected and try to re-connect.", vbOKOnly + vbInformation, "Connection Error"

End

This not only inform the client of the problem, but also a possible fix. If you have your connection string in every form you load and there is a connection fault, your application will end and the user has to re-connect to confirm connection. You can use anything else in the END event.

If you however only want to check your connection with a timer on a constant basis then try the following.

Add a timer to your form. Set the Enable property to False. Set the Interval property to 1000 (or whatever time interval you like). This is for 1 second.

Private Sub Form_Load()
Timer1.Enabled = True
End Sub

Private Sub Timer1_Timer()

Set cnMyConnection = New ADODB.Connection
cnMyConnection .CursorLocation = adUseClient

cnMyConnection .Open = 'ENTER YOUR CONNECTION STRING HERE. If you do not know how, folow this link -  http://www.connectionstrings.com/sql-server-2008

Set rsMyRecordset = New ADODB.Recordset

rsServer.Open "SELECT * FROM YourDatabaseName", cnMyConnection , adOpenStatic, adLockOptimistic 'Where YourDatabaseName is the name for your database you want access to.

If cnMyConnection.State = 1 Then
     Timer1.Enabled = False
     Timer1.Enabled = True

     Exit Sub
          Else
     MsgBox "There was a problem in connecting to the Server.           Switch the Server power on, or Confirm that your connection (Network) cable is properly connected and try to re-connect.", vbOKOnly + vbInformation, "Connection Error"

Timer1.Enabled = False
End If
End Sub

I have not tested this code, but it should put you on the correct path.

[fulvio1981]

AndreRet,
I was searching an ADODB function that let me to interrogate server because I want konw if it is up or down. At the moment I find a workaround...but I don't like it. Do you know if there's an event to intercept or a function to call.
thank in advance
Bye bye
Fulvio

[AndreRet]

There is a few other steps to try. The only methods I know of to check the true current availability of the database is to request a connection by opening it, or perform a database operation on an existing connection. You can however try the following code as well.

Dim objWMIService As SWbemServices
Dim colItems As SWbemObjectSet
Dim objItem As SWbemObject
Dim strMachineName As String

strMachineName = "localhost" 'OR add your IP Adrress or Server Name in place of localhost (DO NOT ADD LEADING BACKSLASHES '\\'. This will cause an error

On Error Resume Next
Set objWMIService = GetObject("winmgmts:\\" & strMachineName & "\root\cimv2")
If Err.Number Then
    ' Unable to connect to server
End If

Dim strQuery As String
strQuery = "SELECT * FROM Win32_Service WHERE Name = 'MSSQLServer'"
Set colItems = objWMIService.ExecQuery(strQuery, , wbemFlagForwardOnly Or wbemFlagReturnImmediately)
If Err.Number Then
    ' Unable to get SQL Server status
Else
    If colItems.Count Then
        For Each objItem In colItems
            Debug.Print "Status: " & objItem.Status
            Debug.Print "State: " & objItem.State
        Next
    Else
        ' MSSQLServer service not running on strMachineName
    End If
End If

You can also try the code in the attachment. This will ping your server and confirm any disabled connection to that machine.

Hope this helps.