Data Cogs Information Technology

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