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

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?

Answer 1

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

Answer 2

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" 
....

Answer 3

I didn't see these responses until now. Both approaches look good, I'll post here with any issues. Thanks to both of you!

Answer 4

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"

Answer 5

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

Answer 6

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.

Answer 7

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

Answer 8

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.