VB6/Ado on Sql server- checking connection


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: VB6/Ado on Sql server- checking connection

  1. #1
    Join Date
    Dec 2005
    Posts
    6

    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 ?

  2. #2
    Join Date
    Dec 2003
    Posts
    2,750
    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)

  3. #3
    Join Date
    Dec 2005
    Posts
    6
    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

  4. #4
    Join Date
    Dec 2003
    Posts
    2,750
    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)

  5. #5
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    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!

  6. #6
    Join Date
    Dec 2005
    Posts
    6
    Thanks, that works great for the local m/c.
    Anyway of running that for remote servers ?

  7. #7
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    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!

  8. #8
    Join Date
    Dec 2005
    Posts
    6
    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'

  9. #9
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    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!

  10. #10
    Join Date
    Dec 2005
    Posts
    6
    Yes, that works a treat. Thank you very much indeed.

  11. #11
    Join Date
    Jan 2006
    Posts
    3
    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,

  12. #12
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    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!

  13. #13
    Join Date
    Jan 2006
    Posts
    3
    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.

  14. #14
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    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!

  15. #15
    Join Date
    Jan 2006
    Posts
    3
    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

  1. Connection String to SQL Server
    By Don in forum Database
    Replies: 5
    Last Post: 08-23-2006, 11:46 AM
  2. query tuning
    By dhaya in forum Database
    Replies: 11
    Last Post: 08-25-2003, 05:24 PM
  3. SQL Server connection
    By shuhada&mona in forum VB Classic
    Replies: 0
    Last Post: 05-14-2001, 03:54 AM
  4. Access to SQL server
    By Nate in forum Database
    Replies: 29
    Last Post: 05-09-2001, 10:04 AM
  5. myLittleAdmin SQL Server version
    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
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center