VB6/Ado on Sql server- checking connection

[itmag]

I'd like to be able to check that a connection is still live, so I have checked the State property of the connection object. But if I open the connection to a SQL server db and then stop the server, the state still returns the same value. It only seems to change if I close the connection explicitly. Is there a better way to check that a server is still there ?

[pclement]

A better idea would be to avoid using persistent connections. Rely on connection pooling instead, which retains an open connection in a pool after it has been closed (via ADO) for about a minute (the default idle period).

[itmag]

This is for a time and attendance system where people could be logging in/out via a pc several times a day. If the server is not available for any reason I'd like to be able to store transactions locally until it's back. But I don't want really want to be trying to open a connection to a server that isn't there while a user stands and waits. I'm trying to avoid that by being able to quickly react to the server becoming unavailable

[pclement]

As far as the State property is concerned, it only reflects the status based upon the last operation performed with respect to the connection. AFAIK there is no active communication between the connection and the database, in other words the connection isn't polling the database in order to determine whether it is actually available.

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.

[Phil Weber]

itmag: Try this:

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

strMachineName = "localhost"

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

[itmag]

Thanks, that works great for the local m/c.
Anyway of running that for remote servers ?

[Phil Weber]

Have you tried changing strMachineName to the name of the server you want to check?

[itmag]

Yes, I have changed it to the server name ie. '\\servername'.

Set objWMIService = GetObject("winmgmts:\\" & strMachineName & "\root\cimv2")

executes without an error, but

strQuery = "SELECT * FROM Win32_Service WHERE Name = 'MSSQLServer'"
Set colItems = objWMIService.ExecQuery(strQuery, , wbemFlagForwardOnly Or wbemFlagReturnImmediately)

gives 'Object variable or with block variable not set'

[Phil Weber]

Try it without the leading backslashes:

strMachineName = "servername"

[itmag]

Yes, that works a treat. Thank you very much indeed.

[VBgeek]

I wanted to know if this would work for an Oracle 9.2 database on a remote server.

I tried using the code snippet but Getobject fails to create an object. Do I need to do anything different here? If you could specify with an example it would be of great help.

Thanks,

[Phil Weber]

Which version of Windows are you running? Not all versions support WMI, which is required by this code.

[VBgeek]

I am using Windows XP professional with SP3. The Database is on Win2k server. Also I am using the OLE DB to connect to Oracle through Custom application.

I hope this helps.

[Phil Weber]

SP3? I didn't know there was an SP3 for XP. ;-)

Make sure the Windows Management Instrumentation service is started on the XP box. If that doesn't help, please post the error message you're getting.

[VBgeek]

Sorry about the incorrect SP information. I guess its SP2 ,but it does not matter,does it?

I have ensured the ant the WMI service is running. I also tested the code snippet posted in this thread (one which uses "localhost") and it works fine.

The error number is 462 and error message is "The remote server machine does not exist or is unavailable"

Following is the code that I am trying to execute, to check if the Datbase is available or not.

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

strMachineName = "axtsPlmar03"

On Error Resume Next

Set objWMIService = GetObject("winmgmts:\\" & strMachineName)


If Err.Number Then
' Unable to connect to server
Debug.Print Err.Description
End If

where axtsPlmar03 is the name of the machine where the DB hosted. I tried with IP instead but it does not help. When I do a ping on the command prompt for this machine...it goes through...no problem.

Would you be in a position to suggest a better way to check if the DB is available or not?