ODBC Call Failed - Queries timeout sometimes but not others... Help

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

Hello everyone,

I have an Access database that uses linked tables to a SQL Server
(Microsoft SQL Server 2000).
From these linked tables, I have a few different Access queries that
Click to expand...
"massage" the data to get the exact information I'm looking for, which
is then displayed neatly on a report.

Everything seemed to work fine. However, once and awhile and at random
the queries will not function at all. I get the error message:

ODBC -- Call Failed
[Microsoft][ODBC SQL Server Driver] Timeout Expired (#0).

This was startling to me because normally my queries would run under 5
seconds (they are not too complex). So, just for the sake of testing I
set the ODBC Timeout to 0 (never timeout).
This would only cause Access to completely freeze up and never thaw.

I then assumed that maybe I was trying to retrieve information from the
server while it was under heavy load.
However, this is not the case. I tried running this query at 6:00pm
when there was only 1 other person left in the building only to have
that exact error message appear again!

This only happens at random times, for unknown reasons. One minute I
will be able to view everything fine and the next I will continually
get that error message only to have it magically start working again
about a 1/2 hour later.

Can anyone help me with this?
 
Joe,

The first time this happened to me I was stumped. What you need to do is go
into the design view of your query, click on view and click on properties.
In properties you will have a line that says ODBC time out. Mine defaults in
as 60. Boost this number up (I use 9999). I use a diffrent ODBC connection,
but your problem just like the one I encountered.

Robert

Joe said:
Hello everyone,

I have an Access database that uses linked tables to a SQL Server
(Microsoft SQL Server 2000).
From these linked tables, I have a few different Access queries that
Click to expand...
"massage" the data to get the exact information I'm looking for, which
is then displayed neatly on a report.

Everything seemed to work fine. However, once and awhile and at random
the queries will not function at all. I get the error message:

ODBC -- Call Failed
[Microsoft][ODBC SQL Server Driver] Timeout Expired (#0).

This was startling to me because normally my queries would run under 5
seconds (they are not too complex). So, just for the sake of testing I
set the ODBC Timeout to 0 (never timeout).
This would only cause Access to completely freeze up and never thaw.

I then assumed that maybe I was trying to retrieve information from the
server while it was under heavy load.
However, this is not the case. I tried running this query at 6:00pm
when there was only 1 other person left in the building only to have
that exact error message appear again!

This only happens at random times, for unknown reasons. One minute I
will be able to view everything fine and the next I will continually
get that error message only to have it magically start working again
about a 1/2 hour later.

Can anyone help me with this?
Click to expand...
 
Does this cause your query to take a long period of time to generate
results or is this just a bug?
 
This is due to a large query or alot of processing on the server. Many
records or complicated criteria causes this issue. Did "bumping up" the ODBC
timeout stop your error?

RW
 
After trying your suggestion, this did not solve the problem.

I am still receiving this error at random.

Does anyone else have any ideas?
 
Back
Top