Time out issues Views/stored procedures

[Guest]

I have a stored procedure that has multiple select statements, each select
statement is giving me a count on a specific recordset and than writes the
results to a temp table.

I am trying to get those results into form in an ADP; however when the form
runs the stored procedure it times out. It runs fine in query analyizer.

Originally, I had just views that gave me these counts, cheated and put all
the views into one view and that was the records source for the form. This
ran fine for about a month than all of the sudden I started getting a time
out error. When I tried to run the view from Enterprise manager it also
times out. It doesn't time out in query analyizer. I have also started
getting time out errors when I try to run the views seperately.

Anyone have a clue what is going on here? I would so greatly appreciate any
help at this point.

 

[Sylvain Lafontaine]

First, you may have a problem with the design of your database, for exemple
with some missing indexes.

Second, there are three timeouts in ADP: two from the connection menu: the
Connect TimeOut and the General Timeout (in the advanced tab) and the third
one is the OLE/DDE timeout in the Advaned tab under the Options menu.

Unless you are using ADO objects to directly make your query, I think that's
the third one that you are looking for: by default it is set to 30 seconds,
you may set it to 0 to get an infinite period of time (that is, no timeout).

If you are using ADO objects, then you must the Command timeout to 0.
(Don't confuse it with the Connection timeout. The connection timeout (15
sec. by default) is only used for opening/login a connection with SQL-Server
and has nothing to do with the length of time taken by executing a query.
The General TimeOut on the connection window is the timeout used internally
by Access for displaying the list of tables and stored
procedures/views/functions along with their properties.)

 

[Guest]

Sylvain,
I am having similar issues: a report written in Access 2000 adp worked fine
for awhile, then we get 'Timeout Expired' error messages.
The report was created based on a view on a SQL Server, permissions are all
ok, ODBC connections tested fine.

Below, when you refer to the 'connection menu', where is that menu? That
is, how would I get to it? And when you refer to the 'command time out', how
do I configure that one?

BTW, I did increase the OLE/DDE timeout, and Access still timed out at 30
seconds, so the timeout we are experiencing is evidently not that one.

Thanks!
Bumfuzzled,
Rick

 

[Sylvain Lafontaine]

Connection menu: File --> Connection --> Advanced tab for the Connection
TimeOut and the All tab for the General Timeout.

For the OLE/DDE TimeOut, try to set it to 0 and close/reopen Access when
changing its value.

The Command time out is only used when you are creating your own recordsets
by using the OLEDB collection of objects. You should not be concerned with
this one unless you are creating your own recordset as the source of your
reports. It may be possible to set this value in the connection string by
using the Extended Properties in the All tab of the connection menu but I
never this myself.

Finally, the best solution for you would be to make sure that SQL statement
or the stored procedure used as the source of your reports are optimized and
that you have all the required indexes. You should try the record source in
SQL Query Analyser to see how much time it takes and, most important, take a
look at the generated query plan. If you query is not already optimized
then it is possible that you will be able to reduce its execution time to a
few seconds. See m.p.sqlserver.server and m.p.sqlserver.programming for
more infos.