Access table in linked server using a query stored in a variable.

[DraperD7772]

All,

I am trying to store a query in a variable and then execute the variable. SQL is giving me errors because the table is on a remote server. I have already setup the linked server and tested the code in the Query Analyzer.

The query itself works fine, but when I load the query into the variable and try to execute it, SQL chokes. I would try using OPENQUERY, but it does not accept variables for its arguments. Any suggestions?

SET @vchQuery =
'
SELECT
FNAME, LNAME, EMAIL, PHONE
FROM
remoteserver.database.owner.table AS table
WHERE
table.id = 1885
'
EXECUTE
@vchQuery

Thanks in advance for your help.

-DJ

 

[tlbroadbent]

SQL chokes? Does it give you a more precise error message?

I suspect that the error may be...

Server: Msg 7202, Level 11, State 2, Line 3
Could not find server ....

Enclose the Execute variable in parenteheses.

EXECUTE (@vchQuery)
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.