Unanswered: Trouble with OPENQUERY, cause dynamic parameters not allowed
Hi, i have and ODBC linked server to an ASA6 DB. I read that the only way to make a query against that server is using Openquery (i dont know how to use four-part names, it always gives me an error when i try to use it).
I have to write a FUNCTION which determines if a query (with 4 inner joins) in a table on the ASA6 DB, has results or not (the possible results are 0 or 1 records). The problem is that OPENQUERY does not allow me to use dynamic parameters.
If i make the query without dynamic conditions, and I filter the results AFTER the Openquery, like this,
SELECT count(*) FROM OPENQUERY(ASA_CONN, 'SELECT field1,.. from t1 inner join t2 inner join t3 on...............') WHERE cond1=val1 and cond2=val2 and .....
the result is a very poor performance, because the openquery returns a very big quantity of records (remember, it has 4 inner joins, and no dynamic conditions), and after the WHERE in the first select, the records are reduced to 1 or 0 (depends on the conditions). I only need to
determine the EXISTENCE of a record.
So, what can I do to write this function?
Thanks for your time!