Incurable ODBC timeout
-
Hi.
I have an Access 2000 database with a linked table pointing to SQL Server 2000. When running a query in Access, it times out after 60 seconds with the standard ODBC "Timeout expired" error.
I've googled like crazy and tried several things. Setting the Access query's "OLE/DDE timeout" to zero doesn't work. Setting to zero the "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC\QueryTimeout" registry key and restarting Access doesn't work. In fact, even if I set this to 5 seconds the query runs to the full 60 then times out. So it's being ignored apparently. Ideas??
This has just got to be a common problem yet I'm unable to find a solution. Please advise.
-
I believe the entry on the registry is supposed to be GREATER that 60
at least that's what I got from MS or you could try it anyways.
Do you have avery long running query ?
* Noel -
From what I've read, setting it to 0 means it will never timeout, but regardless, I've tried setting it to 600, 6000, etc. But it seems to be ignoring it because if I set QueryTimeout to 10 -- which you'd expect to cause the query to timeout after 10 seconds -- it still runs to 60.
It's like there's another setting somewhere, but I've set the Access query itself's timeout and that doesn't help either.
Setting the registry's DisableAsync to 0 causes "server processing" and my harddrive to get busy (so I know changing the registry values does something) but it still times out at 60.
-
>> But it seems to be ignoring it because if I set QueryTimeout to 10 -- which you'd expect to cause the query to timeout after 10 seconds -- it still runs to 60.
It's like there's another setting somewhere, but I've set the Access query itself's timeout and that doesn't help either. <<
DID YOU READ THE LINK I POSTED?
NOTE: When you set the QueryTimeout value within the registry, any new and existing queries within Microsoft Access continue to display a value of 60 for the QueryTimeout property. If the value you set within the registry is greater than the value defined within the individual query, Microsoft Access uses the value within the registry to determine how long to wait before a QueryTimeout occurs.
Apparently works the other way aorund as what you just said!!!
if the value is smaller it takes the registry!!!
On the other end I would reboot the computer just in case... ACCESS is stuck somehow
* Noel -
Yes, thank you, noeld, I did read the link you posted.
Zero is less than 60 but I figured since zero is supposed to represent indefinite (i.e., no timeout) that it would be considered greater. This point is moot however.
The solution is to open up the individual Access query in Design View, and right-click in the gray area where the tables are and say Properties (NOT Tools | Options | Advanced)... this is where the ODBC timeout setting is and it was set to 60 there. Set this to 0 and it runs fine.
Just wasted half my morning on this. The other half was a fire alarm drill.
Thanks for your assistance.
*kicks Access*
-
Thank you very much for posting the solution It will help me (and others) in case we com accross long running queries scenarios
* Noel -
Of course! If not for people doing the same, I'd never have found half the solutions I needed.
-
Obviously not the problem in your case, but remember that ADO has timeouts onits connection and command objects and SQL Server has a timeout on connections. Any one of those can give you problems.
-
I am using an Access Project app, and there is an ADODB Connection parameter called CommandTimeout, which works for extending the query time out. You might search for that.
Shalom!,Michael Lee
-
Yeah, I've done the pass-through thing with ADO and am aware of CommandTimeout and ConnectionTimeout.
I'm really quite irked that my problem was something as simple as a Property of the query.
-
Hi,
I am new to this group.
Hope i am in the rt forum.ODBC Time out parameter gets a default of 60.Can we change this 60 to 0 so that "EVERY" query when opens have the default of 0 but not 60.
I don't want to change from 60 to 0 for each and every new query that is created instead i want it to have default value of 0.
Hope i am making sense.
Thanks,
PK
-
Not that I'm aware of. Maybe if you make a new class (Querydef2) of it.
-
query_squidier
Thanks a million for the relevant registry key!
I'm ending now a 4-hour investigation, trying to solve this annoying timeout error, and this is the only place I've found this simple solution.
Thanks again,
Offer.
-
prabhu19_2k4 (8/29/2008)
Hi,I am new to this group.
Hope i am in the rt forum.ODBC Time out parameter gets a default of 60.Can we change this 60 to 0 so that "EVERY" query when opens have the default of 0 but not 60.
I don't want to change from 60 to 0 for each and every new query that is created instead i want it to have default value of 0.
Hope i am making sense.
Thanks,
PK
IMHO, setting infinite timeout is an awful practice. It sets you up for a locked application that can't be recovered or the problem logged. If you a timeout you catch that and report/handle it. The can be plenty of things that can cause this to lock (Deadlocks? anyone?).
Access linked tables have plenty of issues, and should be used sparingly, and best with tables that cater to the datatype mismatches.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply