Timeout Expired but worked 3 months ago

[Drew]

Access XP
MDAC 2.8 SP1
SQL Server 2000

I have an ADP that I use for reporting that I have used for about a year. I
only use it every 3 months, and it has worked up until now. This morning I
tried using it, but after trying to open the report, it hourglassed and then
showed a "Timeout expired" error. I searched this message and found out how
to set the timeout limit, and set it to 300 seconds. Still the same
message.

Since I am not in the sysadmin role, I cannot run Profiler to see what is
happening. Instead I must use sp_who2 active to see what is going on (not
the best, but what else could I do?).

I did notice something very strange when I ran the sp_who2 active command.
The HOSTNAME displayed for the ADP file is my old computer hostname, not my
new one. Why would this be happening?

Also, the CPUTime when I try running the report goes up into the 200,000's,
which doesn't look very good.

What can I do to find out what is going wrong?

Thanks,
Drew

 

[Sylvain Lafontaine]

Make sure that you have set the right timeout.

There are three timeouts in ADP; the first two are located in the advanced
tab on the connection window: Connection menu: File --> Connection -->
Advanced tab for the « Connection TimeOut » and the All tab for the «
General Timeout ».
The third is the « OLE/DDE TimeOut » in the Options dialog window, you can
try to set it to 0 and close/reopen Access when changing its value; however
I don't expect it to be usefull in your situation.

There is also the « Command time-out » but this one is only used when you
are creating your own recordsets by using the OLEDB collection of ADO
objects. You should not be concerned with this one unless you are creating
your own recordsets 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 tried this myself.

In you case, you should try setting the General Timeout to 0. However,
redesigning your schema and making sure that SQL statements and the stored
procedures used as the source of your forms and reports are optimized and
that you have all the required indexes. Simply opening Views is not a
proper way of doing things with SQL-Server.

You should try these record sources 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.

 

[Drew]

Thanks for the reply...

To make matters worse, this morning everything worked completely fine... no
problems at all! It seems that there was a DNS (or WINS) problem that was
keeping the ADP from seeing the SQL Server. So now that is fixed,
everything is working properly...

Drew

 

[aaron.kempf]

i disagree.. 'simply opening views' IS an acceptable way of doing
things.

i use views ALLLLLLLL the time; im forced to because that's all that
olap will read off of.