October 2, 2007 at 8:05 am
How do i pass the linked Server name in as a parameter when using openquery
eg i want to pass in the name of the linked server as a parameter
/******************************************
DECLARE @LINKED_SERVER_NAME AS VARCHAR(50)
SELECT *
OPENQUERY(@LINKED_SERVER_NAME,'SELECT "OFFICER-CODE", "FORENAME", "SURNAME", "TELEPHONE" FROM PUB."IH_OFFICER" ')LIVE_OFFICER
/*************************************************
i can not use execute as this forms part of a larger piece of SQL which will be joined to SQL Server tables.
The linked Server Name changes depending on the customers sites (Database are the same)
October 2, 2007 at 8:29 am
You're trying to sneak in dynamic SQL without actually treat it as dynamic SQL.
The only way to do something like that is to wrap the entire statement into something dynamic, e.g.
DECLARE @LINKED_SERVER_NAME AS VARCHAR(50)
declare @sql varchar(4000)
set @sql='SELECT * from OPENQUERY('+@LINKED_SERVER_NAME+',''SELECT "OFFICER-CODE", "FORENAME", "SURNAME", "TELEPHONE" FROM PUB."IH_OFFICER" '')LIVE_OFFICER'
exec(@sql)
...
Sorry - not fun - but the openquery won't accept any form of dynamic SQL.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply