Results 1 to 14 of 14

Thread: SQL Connectivity Test?

  1. #1
    Join Date
    May 2006
    Posts
    6

    Question SQL Connectivity Test?

    Hi -

    I'm having a problem with a VB6 program I'm writing that posts data to a SQL server. The problem occurs when the SQL server is unavailable. When this happens it causes the program to hang for at least 30 seconds before releasing. The hang happens at the code that connects to the SQL server, because the server isn't there. I've added code found on this site to test the connectivity prior to making my actual data connection, but this causes the same hang. Is there any way to verify connectivity to an SQL server without actually trying to make the connection? I'm really trying to eliminate the hang that occurs when the server is unavailable.

    Thanks

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Tried with odbcping?

  3. #3
    Join Date
    May 2006
    Posts
    6
    Nope, never heard of that, but will now look it up.

    Thanks very much!

  4. #4
    Join Date
    May 2006
    Posts
    6
    Unfortunately I don't have odbcping. Also I'm wondering if there's a programmatic way to do this rather than shelling out to dos.

    Thanks

  5. #5
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254

  6. #6
    Join Date
    May 2006
    Posts
    6
    That's definitely what I'm looking for, so thanks. I still think I'm out of luck though, because even with this script and with ODBCPing, if the server isn't available there's a pause of about 30 seconds. That's what I'm trying to avoid. I'm rolling out this app to all 50,000 computers here, and they all post to a single SQL server. If that server goes down then 50,000 users are gonna have a 30 second pause when they log in. Not a big deal to me, but typical non-technical users will pick up the phone and call the help desk to complain, and that's what I'm trying to avoid. I guess it's not possible, but I'd love something that just checks connectivity and responds very quickly - it's either live or it's not live.

    Thanks again for all your help, but I think I'm S.O.L. on this one.

    Greg.

  7. #7
    Join Date
    May 2006
    Posts
    1

    this is what i'd do -

    This may sound a bit simplistic, but would it not be simpler to check the sql server's availability by another method? For example if there's a shared folder on the sql server, you could check for visibility of that?

  8. #8
    Join Date
    Sep 2002
    Posts
    5,938
    But that doesn't mean sql service is running.

  9. #9
    Join Date
    May 2006
    Posts
    6
    SQL Service availability is what I really need to know, but I can't have every client taking so long to determine that. It's weird that once I get connected to SQL it's the fastest process in the world, but knowing whether or not I CAN connect is the big challenge. I don't expect the service to be unavailable often, but the minute it is we're gonna have a customer support problem. Whatever ODBCPING is doing to figure out availability is what I need to be able to do from VB. I know it's possible, because ODBCPING is doing it, but I have no idea how to make it happen.

    I thought this would be a pretty common task, but it looks like it's really not. I do greatly appreciate all of your help though!

    Greg.

  10. #10
    Join Date
    May 2006
    Posts
    8

    SQL Connectivity Test

    You're saying that the biggest proplem with this 30 second wait will be the users complaining to the help desk. You're also saying that you don't expect this issue to happen very often, and when it does, there won't be any mission critical consequenses.
    I'm new to SQL, but it seems to me that the easiest fix would be to display a "Please Wait" screen at this point. If the connectivity is as fast as you expect, no one will see the screen until you get that rare 30 second pause.

  11. #11
    Join Date
    May 2006
    Posts
    6
    The application has no front end - users don't even know it's running. That's why the 30 second hang is a problem, because their machine suddenly pauses for about 30 seconds and they have no idea why. That generates calls and with so many users, a panic.

    Greg

  12. #12
    Join Date
    Apr 2006
    Posts
    30
    why is the server unavailable so often? that doesn't sound good.

    if you are doing this in vb, have an asynchronous process poll the service periodically and set a flag that can be read by the app.

  13. #13
    Join Date
    May 2006
    Posts
    1
    Let me guess, the test takes forever only when it can't connect to the server right?
    The reason it's taking so long is because your connection timeout is set to the default 30 seconds.


    Reduce the connection timeout to 5 or 10 seconds.

  14. #14
    Join Date
    May 2006
    Posts
    1

    Connection error trapping/handling code

    You may wish to try the following connection error trapping/handling code. It works with a web (ASP "classic" vbscript) page and Oracle (but can easily be adapted to MSSQL by changing the provider string to SQLOLEDB, etc.)

    ' Partial MSSQL conn string syntax
    e.g.; "Provider=SQLOLEDB; Data Source=svrSource; Initial Catalog=myDBName;"

    <%
    '************************************************* *******************************************
    ' Global Routines
    '************************************************* *******************************************
    Sub ConnectDB(oConn_Local)

    On Error Resume Next

    'Establish Connection to Oracle database.
    Set oConn_Local = Server.CreateObject("ADODB.Connection")

    oConn_Local.Open "Provider=MSDAORA;Password=myPWD;User ID=myUID;Data Source=myDS;Persist Security Info=True;"

    ' If connection error occurs, send user to "Under Construction" page
    '
    If Err.Number <> 0 Then

    ' Send mail to admin.
    Dim objCDO
    Set objCDO = Server.CreateObject("CDONTS.NewMail")
    objCDO.TO = "test@acme.com"
    'objCDO.CC = "test@acme.com"

    objCDO.FROM = "server@acme.com"
    objCDO.SUBJECT = "Possible connection problem with myApp - SOURCE: myServer"
    objCDO.BODY = "SOURCE: myServer. Possible db/network connection problem with the myApp. Please check and repair if necessary."

    objCDO.MailFormat = 1 ' CdoMailFormatText
    objCDO.BodyFormat = 1 ' CdoBodyFormatText

    objCDO.Send
    Set objCDO = Nothing

    Response.Redirect("http://www.someothersite/under_construction.htm")

    End If

    On Error GoTo 0

    End Sub

    Sub DisconnectDB(oConn_Local)
    'Close and Destroy connection object.
    If IsObject(oConn_Local) Then
    If oConn_Local.State = adStateOpen Then
    oConn_Local.Close
    End If

    Set oConn_Local = Nothing
    End If
    End Sub
    %>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •