Linked Server error - ODBC timeout

My MS Access app links to an SQL Server 2000 db which includes a linked
server.

A pass through query like "select * from linkedtable" works fine. But
"select * from linkedtable where linkedtablekey=1" fails - ODBC timeout
error.

The same thing happens when I try the queries in Query Analyzer.

This error only occurs on the production computer - the queries work fine in
the test environment.

Thanks, in advance, for suggestions.

DT

Maybe a bad query plan or the execution take to much time because there are
no index on the linkedtablekey column. (If you have upsized this table from
Access, make sure that this column has been declared as the primary key for
the table. The Upsizing Wizard often forget to do this.)

After checking for the possibility of a missing primary key (or index), the
second thing to do would be to update the statistics using the
sp_updatestats stored procedure (or use the command UPDATE STATISTICS is you
want to work on a more detailed level) and clean
the caches after that:

DBCC FLUSHPROCINDB
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

After that, you can think about reindexing and - very important -, you can
take some precautions about the use of atypical parameters and
recompilations in order to eliminate the reuse of bad query plans. See:
http://sqlknowledge.com/index.php?op...d=65&Itemid=41

Reindexing:
http://blog.sqlauthority.com/2007/01...ics-on-tables/

Good article on the use of the With Recompile option and of intermediary
variables in
order to deactivate the parameters sniffing from SQL-Server (the use of
intermediary variables is probably better than to use the With Recompile
option because recompiling big procedures take time):
http://blogs.msdn.com/khen1234/archi...02/424228.aspx . (It's
useless to use both at the same time).

If you are with SQL-Server 2005, you can also play with the OPTIMIZE FOR
option instead of deactivating the parameters sniffing or using the With
Recompile option; see:
http://www.sql-server-performance.co...guides_p1.aspx
and
http://www.sql-server-performance.co..._hints_p1.aspx

Finally, a good reference on recompilation:
http://www.microsoft.com/technet/pro...05/recomp.mspx

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"David Taylor" wrote in message

> My MS Access app links to an SQL Server 2000 db which includes a linked
> server.
>
> A pass through query like "select * from linkedtable" works fine. But
> "select * from linkedtable where linkedtablekey=1" fails - ODBC timeout
> error.
>
> The same thing happens when I try the queries in Query Analyzer.
>
> This error only occurs on the production computer - the queries work fine
> in
> the test environment.
>
> Thanks, in advance, for suggestions.
>
> DT
>
>
>