none
ODBC Timeout property doesn't impact how long it takes for a query to timeout RRS feed

  • Question

  • Per docs and postings the ODBC Timeout property is supposed to define the number of seconds before an query's odbc connection attempt is ended. But I've experimented and find that it has no impact whatsoever. If I set the property to 2 seconds it still takes anything from 21 to 40 seconds for the sql server Connection failed dialog appears.

    All I can think of is that my testing methodology is wrong. I am interested in controlling how to control the number of seconds before the query gives up when a sql server database is not available on the network. To test, I take the sql server offline. I'm wondering if the odbc timeout property is only meant to deal with the timeout value for queries that can actually be invoked?

    If that's the case, is there a way to test to see if a sql server is available before attempting to run a query?

    Thursday, December 12, 2019 5:07 AM

All replies

  • You can test the ODBC connection by using a “different” path way. This will prevent a ODBC error, and better yet it times out rather fast.

    The code is thus this:

    Sub Testcon()
    
       Dim strCon     As String
    
       strCon = CurrentDb.TableDefs("dbo_tblHotels").Connect
    
       Debug.Print TestLogin(strCon)
    
    End Sub
    

    In above, I grab an existing “connection” string.

    The function TestLogin() is this:

    Public Function TestLogin(strCon As String) As Boolean
    
       On Error GoTo TestError
    
       Dim dbs          As DAO.Database
       Dim qdf          As DAO.QueryDef
    
       Set dbs = CurrentDb()
       Set qdf = dbs.CreateQueryDef("")
    
        qdf.Connect = strCon
    
        qdf.ReturnsRecords = False
    
        'Any VALID SQL statement that runs on server will work below.
     
        qdf.SQL = "SELECT 1"
        qdf.Execute
        TestLogin = True
    
        Exit Function
    
    TestError:
        TestLogin = False
        Exit Function
    
    End Function
    

    So the above near times out instant if you can’t get to sql server.

    As for the ODBC timeout settings? I don’t believe that there is setting in Access for linked tables. You can however flip a query into sql view mode, then display the property sheet, and there is a default value of 60 seconds.

    You can also (I believe) include the timeout as part of the connection string, but the above approach is likely far better anyway.

    Give the above a try – it works rather well. It returns True or False.

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)

    Edmonton, Alberta Canada


    Friday, December 13, 2019 6:10 AM
  • I had a similar issue and wrote the following functions to deal with the problem:

    Public Function IsSQLServerInstalled() As Boolean
    
    On Error GoTo Err_Handler
    
    'checks registry to see if SQL Server is installed on this computer
    
        IsSQLServerInstalled = False
    
        strText = GetStringValFromRegistry(HKEY_LOCAL_MACHINE, "SOFTWARE\MICROSOFT\Microsoft SQL Server", "IsListenerActive")
        'Debug.Print strText
        If strText <> "" Then IsSQLServerInstalled = True
        
        'Debug.Print IsSQLServerInstalled
        
    Exit_Handler:
        Exit Function
    
    Err_Handler:
        MsgBox "Error " & Err.Number & " in  IsSQLServerInstalled procedure : " & Err.Description
        Resume Exit_Handler
        
    End Function
    
    Public Function CheckSQLServerConnection() As Boolean 
    
    'returns true if SQL Server is running and the listed database is available
    'Otherwise false
    
    On Error GoTo Err_Handler
    
    Dim cnn As ADODB.Connection
    
    CheckSQLServerConnection = False
    
        Set cnn = New ADODB.Connection
        cnn.Open "Provider=SQLOLEDB;Data Source=SERVER;Initial Catalog=DBName;User ID=UserName;Password=PASSWORD"
        
        If cnn.State = adStateOpen Then
            CheckSQLServerConnection = True
            cnn.Close
        End If
    
      '  Debug.Print CheckSQLServerConnection
        
    Exit_Handler:
        Exit Function
        
    Err_Handler:
        If Err = -2147467259 Then Exit Function 'CheckSQLServerConnection=False
            
        MsgBox "Error " & Err.Number & " in CheckSQLServerConnection procedure : " & vbNewLine & _
                "  " & Err.Description & "   ", vbCritical, "SQL Server error"
        
        Resume Exit_Handler
    
    End Function

    Both return boolean values so you can test as follows

     If IsSQLServerInstalled = False Or CheckSQLServerConnection = False Then
    MsgBox "SQL Server is not available, vbCritical, "Connection Error" 
    ....



    • Edited by isladogs52 Friday, December 13, 2019 9:30 PM
    • Proposed as answer by isladogs52 Saturday, December 21, 2019 11:27 PM
    • Unproposed as answer by isladogs52 Saturday, December 21, 2019 11:27 PM
    Friday, December 13, 2019 6:51 PM
  • I didn't see these responses until now. Both approaches look good, I'll post here with any issues. Thanks to both of you!
    Friday, January 3, 2020 7:06 PM
  • I've tested both approaches and it takes them 42 seconds to come back with a message that the server isn't available. Albert said his routine would fail due to timeout almost instantly but it's not in my case. 42 seconds is the same timeout that I get without either routine.

    I think both of you understood what I was after, so I must be doing something wrong. I just don't see what. I tried reducing the connection timeout in the adodb version and it didn't change anything. Here is that revised connection string

        cnn.Open "Provider=SQLNCLI11;Data Source=badservername;Initial Catalog=missingdb;Trusted_Connection=Yes;Timeout=5"


    • Edited by lexxicon Sunday, January 5, 2020 3:32 AM
    Sunday, January 5, 2020 2:44 AM
  • Well, I assume you exhausted most routes here.

    Keep in mind that my example was for DAO. I find that if the database don't exist - it comes back instant.

    If the server don't, then about 11 seconds. But who knows the complexity of your network setup.

    However, the ADO connection object has both a TimeOut, and A ConnectionTime out setting.

    (I assume you attempted the ConnectionTimeout setting as the first thing.

    However, it might have been missed here.

    This works rather nice - and even if the server don't exist at all, it times out in 2 seconds.

     Dim rstD    As ADODB.Recordset
       Dim rstDCon As New ADODB.Connection
       rstDCon.ConnectionString = "Provider=MSOLEDBSQL;Server=ABC-DEF-0006\AXISMIS;Database=TEST3;UID=TEST3;PWD=TEST3"
       rstDCon.ConnectionTimeout = 2
       rstDCon.Open
    

    Note that for some reason, placing the TimeOutconnection as part of the string does not work, but the above does work rather nice for me. And it even works when the server don't exist at all.

    Give the above idea a try. You certainly want to do this before you send/attempt any SQL query. 

    So there is both a Timeout and a TimeoutConnection property for ADO. (I don't believe this exists for DAO).

    Give the above idea a try - it works quite nice for me.

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)
    Edmonton, Alberta Canada

    Sunday, January 5, 2020 4:24 AM
  • Last night after posting I also tried the .ConnectionTimeout setting on a separate line like you have it, in the ado version, and it had no impact.

    This am I've done a lot more testing and found...the server I was testing against yesterday has a dns issue so it hasn't been reachable by name for a while, I'd just forgotten. Hitting it by ip address is viable and it even respects the .ConnectionTimeout  setting. This is good. Other sql servers are responsive as well.

    I was thinking, why would it have been taking so long to fail, since if the issue with reaching the sql server instance by name it would seem to be equivalent to trying to reach a server that is offline. But it must be that the dns issue itself it taking a long time to fail; like it's trying to reach the server but it prevented by some internal tangle.

    Thanks isladog52 and albert.

    Sunday, January 5, 2020 5:56 PM
  • Using my code, it connects instantly if all is OK.

    But if SQL Server is not connected, it does take a while to confirm that

    I can only suppose Access will keep checking the connection before at some point throwing its hands up in defeat. Better that way than giving up immediately if there is a brief interruption in service.

    So I agree there is then a delay - in my case perhaps 10 seconds or so

    Sunday, January 5, 2020 9:43 PM
  • isladogs52 I found that the .ConnectionTimeout setting does have an impact on how long it takes the function to return false. If I set it to 1 sec, it still takes 3-4 sec to fail, but if I set it to 4 sec it takes 10 sec.
    Monday, January 6, 2020 5:30 AM