Check for lost SQL connection

[Glasgow]

Ideally, I'd like to be able to check that a SQL connection is still active 'occasionally'. The ideal solution would involve detecting when the application deactivates or re-activates so that I can then check the status of the connection.

It would not be practical/efficient to change the activate event in every form across the application unless I can trap the fact that the activate has come from outside the application rather than within it.

Thanks in advance.

 

[DrJavaJoe]

The ado connection object can raise events.
see

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdconeventsvb.asp

here's an example:

Option Explicit
Dim WithEvents connEvent As ADODB.Connection

Private Sub Command1_Click()
connEvent.Close
End Sub
Private Sub Form_Load()
Dim strConn As String
strConn = "your connection string"
Set connEvent = New ADODB.Connection
connEvent.Open strConn
End Sub

Private Sub connEvent_Disconnect(adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
If adStatus = adStatusOK Then
MsgBox "Your connection has been disconnected"
End If
End Sub


Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[Glasgow]

Well you learn something every day (they say) - thank you very much. I didn't think of investigating 'WithEvents' on the connection even though I use this with ADO recordsets.

However, unfortunately, this does not trap the scenario I was hoping to handle - i.e. where the user physically stops the SQL Server (sorry I did not explicitly state this). In this case, the event does not fire.

I have a general purpose error handler so, as a workaround for now, after reporting a fatal error, I use the SqlDMO object to determine whether the server is running. If it is not I report to the user that this is the likely cause of the error.

This is not ideal so any other suggestions would be appreciated.

 

[TGandon]

For an "occasional" check you could just send a simple query that has a known result such as "SELECT COUNT(*) FROM MyTable" (i.e. returns a single column, single row)
Then if an error occurs you can explicitly close the connection and try to re-open it.

Trevor

 

[Glasgow]

Thanks Trevor. The problem is when and where to do it. I realise I could my own SQLDmo server check in a timer event but I don't want to do it so often as to affect performance nor so rarely that I defeat the purpose - I'm not sure I can judge that balance! So my ideal would be to check each time the focus returns to my application.