Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    ODBC Timeout (2000 SR1)

    I use Client Access ODBC connections to an AS400 to pull from company databases, but sometimes the query will time out and I get a message saying something like 'query will take xx seconds; timeout is 60 seconds'. I havn't been able to find where the timeout setting is -- registry, ODBC setup, Access setup, or other. Any info on where this setting is hiding? If I could get the timeout pushed up to 120 seconds it would make things so much easier; the usual 'estimated' time to complete the aborting queries runs ~90-100 seconds.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: ODBC Timeout (2000 SR1)

    Queries have a property which is called ODBC Timeout where you can set the timeout value (at in 2002 it's called that - it may be slightly different in 2000). BTW, setting it to zero will make it wait forever.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC Timeout (2000 SR1)

    Hi Wendell:

    I thought so too, but even though my query timeout is set to 0 in the DSN I'm using in my ODBC file data source, the (admittedly very large and complex) query is timing out. Any ideas?

    Thank you, in advance, for your help.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ODBC Timeout (2000 SR1)

    Could there be a time limit on the side of the server database?

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC Timeout (2000 SR1)

    Thanks, Hans; the ODBC settings on my PC (Control Panel, Administrative Tools, Data Sources) were overriding the ODBC settings in my file DSN. I set the Long Query Time to 0 and everything now works fine.

    As always, I appreciate your help!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ODBC Timeout (2000 SR1)

    I'm glad you were able to solve it. Thanks for sharing the solution - it might well help someone else in the future.

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: ODBC Timeout (2000 SR1)

    Hi Kathryn,

    Sorry to not get back to you sooner, and glad you solved the problem, but there's a detail here that I don't understand. Can you provide more details about where you set the Long Query Timeout? We tend not to use File or User DSNs, but System DSNs, and I can't seem to find any where that exists for a System DSN. Thanks.
    Wendell

  8. #8
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC Timeout (2000 SR1)

    Sure.

    From the Windows Start button, select Settings and Control Panel, then double-click on Administrative Tools and then Data Sources (ODBC). Click the File DSN tab and browse to any previously-created .dsn files. (File dsns are created in Notepad or by the Access user interface. I use and store them on the network so my applications can be used by other people on our company's network. The Windows form defaults to a local folder on the C drive, but that is of no use if you need to distribute a client application that will be used on other PCs.) Select the DSN you want and click the Configure button, which opens a wizard. Work through the wizard, setting the options you want. When you get to about the fourth screen, you will see the top option is "Change the language of SQL Server system messages to:" and several checkboxes. The fourth check box label says "Save long running queries to the log file: and shows a grayed-out text box. If you click the check box, the text box becomes enabled. Delete the path string and change the "Long query time (milliseconds) setting from the default 30000 to zero, then uncheck the check box and click Finish. You can then test the connection.

    After I did this, my queries ran fine.

    FYI: If you don't want to set the timeout to zero, there is also a DAO property called ODBCtimeout which can be set for queries using VBA. You could clock your queries and then use a higher setting for each one individually.

Posting Permissions

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