If you’ve ever tried to use OPENQUERY to perform a passthrough query in SQL Server to a linked server, you have probably come across the limitations of this function. It is VERY clunky if you are trying to pass variables or to get return parameters, etc:
Have a look at this article for some examples:
http://support.microsoft.com/kb/314520
A better method is described at the bottom of the article, which is to use sp_executesql. This is really important if you need to assign values to a variable and then use that variable in the local server context. Since there are no built-in global variables in T-SQL you would need to create a temp table and really hack a solution. sp_executesql gives a much tidier method:
DECLARE @RoomDesc varchar(100)
EXEC linkedserver.master.dbo.sp_executesql
N’SELECT @RoomDesc = eqnum
FROM maxprod.dbo.equipmentspec
WHERE orgid = ”XXX”
AND siteid = ”YYY”
AND alnvalue = ”Y”
AND eqnum = @RoomKey’,
N’@RoomDesc VARCHAR(100) OUTPUT, @RoomKey INT’,
@RoomDesc OUTPUT,
@RoomKey
UPDATE t
SET RoomID = @RoomKey,
RoomName = @RoomDesc
FROM mylocaltable t
WHERE t.EmployeeNo = @EmployeeNo