Pass the parameters from current database to linked database server using linked database server

Question

I want to write an select query using OPENQUERY in SQL server where i have to select the records from linked server but in where clause i have to compare the dates but the date should be from a table present in current DB not in linked server DB. For example: Select * from OPENQUERY(Linked_Server,'select * from db.table_name where Edate>(select edate from CurrentDB)')

so please help me how in can pass the parameters from current Database server to linked Database server

Possible duplicate of [including parameters in OPENQUERY](https://stackoverflow.com/questions/3378496/including-parameters-in-openquery) — Nick.Mc, Jul 18 '17 at 04:44

score 0 · Answer 1 · answered Jul 18 '17 at 04:52

0

Hiii its not necessary to use OPENQUERY you can query in this way

select * from "linkservername"."DB NAME"."Tablename" where Edate > (select edate from CurrentDB)

try this.

answered Jul 18 '17 at 04:52

Santhosh Raja

39
1
9

hello Thanks for your reply! but it is taking too much time AFTER removing OPENQUERY – user2398338 Jul 18 '17 at 04:56
select * from "linkservername"."DB NAME"."Tablename" WITH(NOLOCK) where Edate > (select edate from CurrentDB) – Santhosh Raja Jul 18 '17 at 05:03
try this hope it will help – Santhosh Raja Jul 18 '17 at 05:03

score 0 · Answer 2 · answered Jul 18 '17 at 07:02

Here you can find different ways to do it: https://support.microsoft.com/en-us/help/314520/how-to-pass-a-variable-to-a-linked-server-query

So, for example, in your case it can be smth like this:

    declare @dt char(8) = (select convert(char(8), edate, 112) from CurrentDB)  -- this should return 1 value only of date type

    declare @sql varchar(4000) =
    'select *
    from openquery(MyLinkedServer, ''select * from db.table_name where Edate>''''' + @dt + ''''''')';
    exec(@sql);

Pass the parameters from current database to linked database server using linked database server

2 Answers2