server using the OLEDB for ODBC Driver

I have an ODBC driver to a non-SQL database that contains legacy data that I
need to reference in my application/reports. I had originally hoped to use a
linked server using the OLEDB for ODBC provider to access the data and so be
able to create applications/reports using data stored in SQL Server 2000 and
the legacy database via a single connection.
The problem is that, according to the ODBC drive vendor, when I use the 4
part name to refer to my linked server, SQL Server only passes the driver
the base select statement without any where clause resulting in the entire
table being returned to SQL Server which then applies the filter. This gives
me an average time of 8 minutes to return a single record of a 46,000 row
table.
If I use the OPENQUERY function the same select statement takes about 2
seconds. Unfortunately though, OPENQUERY does not accept a variables as a
parameter and so the select statement must be a hard coded string which
makes it unsuitable for any but a static view.
Any suggestions on a workaround for this?You can build the entire SQL string, e.g. select * from
openquery(server, 'etc...') , and pass the string into an
EXEC(). You can find examples in this article:
HOW TO: Pass a Variable to a Linked Server Query
http://support.microsoft.com/?id=314520
-Sue
On Wed, 11 Aug 2004 12:37:48 -0500, "Charles J Ryan"
<charlesryan1@.msn.com> wrote:

>I have an ODBC driver to a non-SQL database that contains legacy data that
I
>need to reference in my application/reports. I had originally hoped to use
a
>linked server using the OLEDB for ODBC provider to access the data and so b
e
>able to create applications/reports using data stored in SQL Server 2000 an
d
>the legacy database via a single connection.
>The problem is that, according to the ODBC drive vendor, when I use the 4
>part name to refer to my linked server, SQL Server only passes the driver
>the base select statement without any where clause resulting in the entire
>table being returned to SQL Server which then applies the filter. This give
s
>me an average time of 8 minutes to return a single record of a 46,000 row
>table.
>If I use the OPENQUERY function the same select statement takes about 2
>seconds. Unfortunately though, OPENQUERY does not accept a variables as a
>parameter and so the select statement must be a hard coded string which
>makes it unsuitable for any but a static view.
>Any suggestions on a workaround for this?
>