Search code examples
sqlssrs-2008

Why we Put INSERT IN DECLARE TABLE in Linked Server report

DECLARE @ReturnTable TABLE
(
    test
)

SET @SqlQuery = 'SELECT something'

INSERT INTO @ReturnTable EXEC(@SqlQuery) AT LinkedServer

SELECT * FROM @ReturnTable
Solution

  • write the Select from the LinkedServer like this

    INSERT INTO @ReturnTable
(test)
SELECT test from [AliasOfLinkserver].Database.Schema.Table

Select * from @ReturnTable

    When you execute a Select _statment from a @variable use the sp_executesql stored procedur which is provided by the SQL Server:

    DECLARE @SQL as VARCHAR(500)
SET @SQL ='Select * from YourTable'
EXECUTE sp_executesql @SQL