-
VB6/Ado on Sql server- checking connection
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 ?
-
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).
Paul
~~~~
Microsoft MVP (Visual Basic)
-
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
-
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.
Paul
~~~~
Microsoft MVP (Visual Basic)
-
itmag: Try this:
Code:
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
Phil Weber
http://www.philweber.com
Please post questions to the forums, where others may benefit.
I do not offer free assistance by e-mail. Thank you!
-
Thanks, that works great for the local m/c.
Anyway of running that for remote servers ?
-
Have you tried changing strMachineName to the name of the server you want to check?
Phil Weber
http://www.philweber.com
Please post questions to the forums, where others may benefit.
I do not offer free assistance by e-mail. Thank you!
-
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'
-
Try it without the leading backslashes:
strMachineName = "servername"
Phil Weber
http://www.philweber.com
Please post questions to the forums, where others may benefit.
I do not offer free assistance by e-mail. Thank you!
-
Yes, that works a treat. Thank you very much indeed.
-
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,
-
Which version of Windows are you running? Not all versions support WMI, which is required by this code.
Phil Weber
http://www.philweber.com
Please post questions to the forums, where others may benefit.
I do not offer free assistance by e-mail. Thank you!
-
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.
-
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.
Phil Weber
http://www.philweber.com
Please post questions to the forums, where others may benefit.
I do not offer free assistance by e-mail. Thank you!
-
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?
Similar Threads
-
Replies: 5
Last Post: 08-23-2006, 11:46 AM
-
By dhaya in forum Database
Replies: 11
Last Post: 08-25-2003, 05:24 PM
-
By shuhada&mona in forum VB Classic
Replies: 0
Last Post: 05-14-2001, 03:54 AM
-
By Nate in forum Database
Replies: 29
Last Post: 05-09-2001, 10:04 AM
-
By myLittleTools.net in forum web.announcements
Replies: 0
Last Post: 03-10-2001, 12:35 PM
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Development Centers
-- Android Development Center
-- Cloud Development Project Center
-- HTML5 Development Center
-- Windows Mobile Development Center
|