Timeout Issue

[Paco]

Accesss 2003 front end; SQL2000 backend

Recordsource (query) of the form contains 225 records from a single table.
Click a button to enter data to update the displayed record and I get a
timeout error when the update is triggered (3157 ODBC update on a linked
table failed). The underlying table has about 60,000 rows.

The update works fine in SQL2005 (the customer has SQL2000). I have rebuilt
the indexes. Also, running the update query in SQL Management Studio works
instantaneously. If I delete 175 records so only 50 are returned by the
recordsource, it works fine. As soon as the number gets up to around 225
again the error reappears. Management Studio shows a block by the underlying
recordsource query of the underlying form. If I kill the process on the
blocking SPID, the update query completes.

The app and code have have been around awhile and used by hundreds of others
without a problem.

Any ideas?

 

[Alex Dybenko]

Hi,
could be that delete/update trigger causes this timeout, check if you have
any on your table. and second reason - table can be locked by other users,
so you have to check activity monitor for deadlocks

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Stefan Hoffmann]

hi Paco,

Recordsource (query) of the form contains 225 records from a single table.
Click a button to enter data to update the displayed record and I get a
timeout error when the update is triggered (3157 ODBC update on a linked
table failed). The underlying table has about 60,000 rows.

Trigger? What are you actually doing? Can you post a concise example?

The update works fine in SQL2005 (the customer has SQL2000). I have rebuilt
the indexes.

Check the locks. When more records are involved you may have a lock
escalation.
Otherwise check if the existing indices cover your queries.

Management Studio shows a block by the underlying
recordsource query of the underlying form. If I kill the process on the
blocking SPID, the update query completes.

Sounds like a dead lock.

The app and code have have been around awhile and used by hundreds of others
without a problem.

Check the views on the SQL Server 2000. When having an ORDER BY clause
without the appropriate index this may also cause locks.


mfG
--> stefan <--