openquery - linked database

[EBee]

the code below works but I have about 20 tables I wanted to update in the UpdTables. I am hoping to just past variables to the openquery and grab the information. However, this errors out when I replace it to

SELECT @BeeDirectCountTransactions = Count(*)
FROM OPENQUERY(sbdatabase1,'select * from @TableName)
--- any suggestions . . . i heard about the EXECUTE but that seems not to work either

-- Transactions Table Record Count
SELECT @BeeDirectCountTransactions = Count(*)
FROM OPENQUERY(sbdatabase1,'select * from Transactions')

SELECT @CMDCountTransactions = Count(*) from Transactions

UPDATE UpdTables
SET BeeDirectRecordCount = @BeeDirectCountTransactions
WHERE TableName = 'Transactions'
UPDATE UpdTables
SET CMDRecordCount = @CMDCountTransactions
WHERE TableName = 'Transactions'

 

[ClayG]

The only way you get any variables into OpenQuery is to make the whole statement dynamic:

set @stm = 'SELECT @BeeDirectCountTransactions = Count(*)
FROM OPENQUERY(sbdatabase1,''select * from '
+ @TableName +''')'

Seems crazy because the inner statement going to the remote server is really ad-hoc anyway, yet its only allowed to be a fixed string, not an expression; but its true.

and since you want to return a value to a variable, you will need sp_executesql:

exec sp_executesql @stm, N'@BeeDirectCountTransactions integer Output', @BeeDirectCountTransactions Output