Friday, March 23, 2012

Passing 'date' parameter to Jet 4.0 linked server - collation problem?

Hello,
I've attached Access MDB as a linked server to SQL 2000 server. Now I
want to run distributed queries. I set 'collation compatible' option,
so when I reference an Access MDB table in WHERE clause, the parameter
is passed to Access data provider. But I can't do the same for
Datetime columns! :-((
Unfortunately I have a large legacy application which uses dynamic SQL
creation, so it is VERY painful for me to rewrite&optimize all SQL
statements in order to use OPENQUERY statements :-((
Example:
Select * From MyMDB...Orders Where OrderDate Between '1/1/5' and
GETDATE()
This statement results in scanning all of the Orders table by SQL
server :-(
Please help me!> Select * From MyMDB...Orders Where OrderDate Between '1/1/5'
What the heck kind of date is that? Try '20050101', assuming that is the
date you meant. The fact that *I* don't know what date you are passing
should be some kind of clue as to why the software doesn't understand it.

No comments:

Post a Comment