September 26, 2008 at 1:46 pm
Hello,
I'm having trouble joining results from a linked server; I'm able to pass the variable and get results, but cannot join to local table. Is this possible?
Thank you
DECLARE @TileName nvarchar (10),@TSQL varchar(8000)
select @TSQL = 'select * FROM OPENQUERY(amisole, ''select room_code,asset_tag,description FROM asset where location_code = ''''' + @TileName + ''''''')'
EXEC ( @TSQL )
/*
SELECT ID, ModelID, AssetTag, Project, Requester, SIR, RequestDate, DueDate, CableID, CableID2, CableID3, CableID4, CableID5, CableID6,
Height, Shelf, TileID, Decom, ReqComplete, Reserve, Units, XPosition, Rotate, SerialNumber, HostName, UtlAmps, UtlVirtualAmps FROM
dbo.HardwareRequests
inner JOIN @TSQL ON @TSQL.asset_tag = dbo.HardwareRequests.AssetTag
ORDER BY Units DESC, Height DESC*/
October 1, 2008 at 8:52 am
You aren't storing the data you are selecting from the remote server anywhere.
Instead of
select @TSQL = 'select * FROM OPENQUERY(amisole, ''select room_code,asset_tag,description FROM asset where location_code = ''''' + @TileName + ''''''')'
use:
select @TSQL = 'select * INTO ##TSQL FROM OPENQUERY(amisole, ''select room_code,asset_tag,description FROM asset where location_code = ''''' + @TileName + ''''''')'
EXEC ( @TSQL )
This will create a global temp table on your local system (tempdb) with the data you want in it. You can then join that table to your followup query. Unlike local temp tables, global temp tables do not drop automatically when the connection closes, so make sure you issue a DROP TABLE ##TSQL when you're finished to clean up after yourself.
October 1, 2008 at 9:22 am
Garadin
Thanks so much for the solution....works great!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply