SEO

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" <dreddog@gmail.com> wrote in message
news:%23bvnYVRoIHA.672@TK2MSFTNGP02.phx.gbl...
> 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
>
>
>

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

I am no professional in Sql-Server, however, I had no problems when testing,
too. Using the productive linked servers in my case caused many, many errors
when running queries against it so I had to give up using the linked servers
directly. Instead, I use a stored procedure in SQL-Server that makes copies
of the tables from the linked server regularly (like: INSERT INTO
CopyOfTABLE SELECT * FROM LINKEDSERVER...TABLE, or with the openquery-syntax
if it does not work otherwise). Then I query the copies instead of the
linked originals. This may cause other problems, it works for me.

My linked database is a Firebird DB.

There are various errors comming from drivers or from the database that I
could not even solve with commercial driver software for firebird or the
vendor or my Firebird DB or developers from firebird. I spent many hours to
find out that sometimes different DB's do not work together (MS or anyone
else would never tell you immediately). I did not have any problems running
queries from Access to the database directly, no problems to run queries on
copies of the tables in SQL-Server (testing) - but almost nothing worked
when using the linked tables (productive).

Using linked servers, sometimes you have to convert columns, sometimes you
need the openquery-syntax (if you do not need to many apostrophes...) or
sometimes the queries just fail (because some fields "appeared" during
runtime, etc. etc.).

It's just a pain, so be prepared to face trouble.

arno