October 29, 2008 at 11:39 am
I'm looking for a solution to a problem that I'm having with OpenQuery. I need a way to parametrize the server value. In the past I've accomplished this by creating a string then using the exec(@string) to do selects, but this will now work for my insert statements. Does anyone have an insight or direction that may help?
Declare @servername nvarchar(50)
Set @servername = 'somevalue'
SELECT * from OPENQUERY(@Servername,
SELECT * FROM Other.Server)
Thanks!
October 31, 2008 at 12:26 pm
Declare @servername nvarchar(50)
Set @servername = 'somevalue'
SELECT * from OPENQUERY(@Servername,
SELECT * FROM Other.Server)
I think what you are looking for is a dynamic query.
Try..
[Code]
Declare @servername nvarchar(50), @SQL varchar(max)
Set @servername = 'somevalue'
select @SQL = 'SELECT * from OPENQUERY( ' + @Servername + ' , SELECT * FROM Other.Server) ' [/code]
DAB
October 31, 2008 at 12:54 pm
Well, this is a bit out of the box and I've not done this before, but it may be an option for you to try. You may be able to write your OPENQUERY statement with a static linked server name and make the connection dynamic by creating the linked server prior to running the statement. See below:
Declare @servername nvarchar(50)
Set @servername = 'somevalue'
sp_addlinkedserver --configure linked server here w/ your @servername value
SELECT * from OPENQUERY(MyServer,
SELECT * FROM Other.Server)
sp_dropserver --remove server
Again, I have not done this before, but it may work and get you around your no-dynamic requirement. You could even add some logic to only add/create the server if the servername is different than the existing linked server name.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply