• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2041
  • Last Modified:

How to use OPENQUERY() with a local variable?

I want to make query to multiple servers and the query is run on remote server. So that, I tired that the following code.

--(The @svr_name will change during program runtime.)
DECLARE @svr_name char(20)
SET @svr_name = 'SERVER1'
SELECT * FROM OPENQUERY(@svr_name, 'SELECT * FROM LIBRARY_DB.DBO.MEMBER')

It does not work!
What can I do?
Thanks
Nelson
0
nelsonyuen
Asked:
nelsonyuen
  • 4
  • 3
1 Solution
 
nelsonyuenAuthor Commented:
Adjusted points to 50
0
 
Jon_RaymondCommented:
Did you check OPENQUERY(T-SQL) in Books OnLine?  You need to use sp_addlinkedserver to set the server name.  Here is an example:

This example assumes that an Oracle database alias called ORCLDB has been created.

EXEC sp_addlinkedserver 'OracleSvr',
    'Oracle 7.3’,
    'MSDAORA',
    'ORCLDB'
GO

SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
GO
0
 
SunnybazCommented:
can you do this with sql server, instead of oracle ? if you have a sql server registered in enterprise manager, can you use open query the same way ?

0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
Jon_RaymondCommented:
Yes, exactly. It doesn't matter.
0
 
Jon_RaymondCommented:
Try this:

DECLARE @svr_name char(20)
SET @svr_name = 'SERVER1'

EXEC sp_addlinkedserver @svr_name,
    'SQL Server',
    'SQLOLEDB',
    'Library_DB'

SELECT * FROM OPENQUERY(@svr_name, 'SELECT * FROM LIBRARY_DB.DBO.MEMBER')
0
 
nelsonyuenAuthor Commented:
Hi, Raymond
I know what you mean, but actually my posted code and yours also have syntax error in Query Analyzer.
Also I tell everyone that the remote server is already linked and tested with the following code.

SELECT * FROM OPENQUERY(SERVER1, 'SELECT * FROM LIBRARY_DB.DBO.MEMBER')

This works, but the problem is that OPENQUERY() cannot accept the server name, that is a variable as my question coded.

0
 
Jon_RaymondCommented:
Sorry, I wasn'table to test it.  How about putting the SQL statement into a variable and then executing it like this.  I tested this one ok:

DECLARE @svr_name char(20)
DECLARE @sSQL varchar(200)

SET @svr_name = 'SERVER1'
SET @sSQL = 'SELECT * FROM OPENQUERY (' + @svr_name + ',"SELECT * FROM DBO.MEMBER"' + ')'
EXEC (@ssql)
0
 
nelsonyuenAuthor Commented:
Thank you very much!
It works now
:-)

Nelson
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now