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

[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

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

 

[Guest]

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

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

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

Does this cause your query to take a long period of time to generate
results or is this just a bug?

 

[Guest]

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

 

[Joe]

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

I am still receiving this error at random.

Does anyone else have any ideas?