Linked Server - How to write a Query with condtion (string)

Hi all,
I have created a linked server from SQL 2k to Informix which is fine.
I wrote below query which also fine and got results:
select * from OPENQUERY
(myLinkServer, 'select a.emp_number,a.emp_name,a.emp_loc,a.emp_stat,
b.emp_grade,b.emp_title from employee a,title b where a.emp_number =
b.emp_number')
But, I need to add folliwng condition with above query that I tried. But
no luck getting error.
How can I incorporate belwo condition with above linked server query:
and a.join_date =' 04/09/2005' and a.emp_loc = 'LON'
Kindly note, "join_date" value and "emp_loc" values can be passed via a
variables.
Appreciate all your help.
Regards
*** Sent via Developersdex http://www.examnotes.net ***Hi
Try:
SELECT emp_number, emp_name, emp_loc, emp_stat, emp_grade, emp_title
FROM OPENQUERY
(myLinkServer, 'select a.emp_number,a.emp_name,a.emp_loc,a.emp_stat,
b.emp_grade,b.emp_title,a.join_date from employee a,title b where
a.emp_number =
b.emp_number') Q
WHERE join_date =' 04/09/2005'
AND emp_loc = 'LON'
John
"Ahmed Jewahar" <ahmed.jewahar@.dhl.com> wrote in message
news:%23y047zZPFHA.4052@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I have created a linked server from SQL 2k to Informix which is fine.
> I wrote below query which also fine and got results:
> select * from OPENQUERY
> (myLinkServer, 'select a.emp_number,a.emp_name,a.emp_loc,a.emp_stat,
> b.emp_grade,b.emp_title from employee a,title b where a.emp_number =
> b.emp_number')
>
> But, I need to add folliwng condition with above query that I tried. But
> no luck getting error.
> How can I incorporate belwo condition with above linked server query:
>
> and a.join_date =' 04/09/2005' and a.emp_loc = 'LON'
>
> Kindly note, "join_date" value and "emp_loc" values can be passed via a
> variables.
> Appreciate all your help.
> Regards
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Ahmed
I suggest you create a stored procedure that accepts two parameters
DECLARE @.SQLx VARCHAR(500)
DECLARE @.var VARCHAR(20)
SET @.var = 'abcd'
SET @.SQLx = 'SELECT * FROM OPENQUERY(Server,
''EXEC pubs.dbo.sp2 '' + @.var + '')'
EXEC(@.SQLx)
"Ahmed Jewahar" <ahmed.jewahar@.dhl.com> wrote in message
news:%23y047zZPFHA.4052@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I have created a linked server from SQL 2k to Informix which is fine.
> I wrote below query which also fine and got results:
> select * from OPENQUERY
> (myLinkServer, 'select a.emp_number,a.emp_name,a.emp_loc,a.emp_stat,
> b.emp_grade,b.emp_title from employee a,title b where a.emp_number =
> b.emp_number')
>
> But, I need to add folliwng condition with above query that I tried. But
> no luck getting error.
> How can I incorporate belwo condition with above linked server query:
>
> and a.join_date =' 04/09/2005' and a.emp_loc = 'LON'
>
> Kindly note, "join_date" value and "emp_loc" values can be passed via a
> variables.
> Appreciate all your help.
> Regards
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Hi Uri,
Thanks for you kind help..
Regards,
*** Sent via Developersdex http://www.examnotes.net ***|||Hi John,
Thanks for your great help. I tried the same and it has been resolved my
problem,
Regards
*** Sent via Developersdex http://www.examnotes.net ***|||Hi
The only issue may be that the openquery may still return an excessivly
large number of rows before being reduced by the subsequent where clause.
John
"Ahmed Jewahar" <ahmed.jewahar@.dhl.com> wrote in message
news:umzwGEcPFHA.2760@.TK2MSFTNGP10.phx.gbl...
> Hi John,
> Thanks for your great help. I tried the same and it has been resolved my
> problem,
> Regards
>
> *** Sent via Developersdex http://www.examnotes.net ***