I am reading records from a linked server table, and trying to use the output in the next update statement, not on the linked server. I am assigning it to a declared variable. The file is being updated with NULL, instead of the correct check number. Any help???

Example code:

DECLARE
@Check   VARCHAR(8)
@TSQL    varchar(8000)

SELECT @TSQL = 'SELECT keyfield FROM OPENQUERY(linkedserver, 
      ''SET '+@Check+'= select keyfield from library.file WHERE document=12345)'

EXEC (@TSQL)

Update mainlibrary.file SET Keyfield2=@Check where document=12345

END

Finally it worked...

SET @SQL =N'SELECT @CHECK=keyfield FROM OPENQUERY(linkedserver, 
	''select keyfield from library.table where document=' + @NEXT +''')N'
EXEC sp_executesql @sql, N'@CHECK varchar(8) OUTPUT', @CHECK OUTPUT
SELECT @CHECK
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.