How to pass parameters into distributed query?

[TomasDill]

Hi!

I have a query:

SELECT * From
OPENROWSET(MyLinkedServer, 'SELECT * FROM tblMyTable WHERE MyField > 01/01/2000')

I run this query for Access databsae. Now I need to replace 01/01/2000 by variable passed to the query, for example:

select @MyVar = '01/01/2000'
SELECT * From
OPENROWSET(MyLinkedServer, 'SELECT * FROM tblMyTable WHERE MyField > ' + @MyVar)

Above does not works, says 'incorrect syntax near '+' '

I cannot use
SELECT * From MyLinkedServer...tblMyTable WHERE MyField > @MyVar

because it queries all rows from linked database table, that is quite large and I want to filter out records just <b>before</b> SQL Server receives result. This have strange performance reasons.

Who knows any workaround?


Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softserve.lviv.ua

The professional level of programmer could be determined by level of stupidity of his/her bugs

 

[TomasDill]

Well, I found answer by myself. Just prepare a long-long string with all commands in it and compose it using stored procedure parameters inside as constants. So now it looks like:

select @MyVar = '01/01/2000'
select @MyQuery = 'SELECT * From
OPENROWSET(MyLinkedServer, ''SELECT * FROM tblMyTable WHERE MyField > ''' + @MyVar + ''') as qryFirst

select * from qryFirst......'

execute (@MyQuery)

It looks bad because all syntax coloring hidden when I put all my code inside qutes, but at least it works...


Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softserve.lviv.ua

The professional level of programmer could be determined by level of stupidity of his/her bugs