Sql-server – Loop INSERT EXEC AT through linked servers

dynamic-sqlsql server 2014temporary-tables

Relating to Inserting EXEC AT linked server into temp table

I am now in the midst of dynamically insert each result of dynamic sql to the temp table for each registered linked server. Below is my query:

SELECT
    @LinkedServer = @LinkedServer + ' INSERT INTO #eapb EXEC(@remotecmd) AT [' + name + '];'
FROM sys.servers s
WHERE server_id > 0;

Below is the error: 

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@remotecmd".

Manually, I can achieve this through below script:

insert into #eapb
EXEC (@remotecmd) AT [server_1];
insert into #eapb
EXEC (@remotecmd) AT [server_2];

If I try to get @remotecmd outside like this,

SELECT
    @LinkedServer = @LinkedServer + ' INSERT INTO #eapb EXEC('+@remotecmd+') AT [' + name + '];'
FROM sys.servers s
WHERE server_id > 0;

I get below similar error:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'DECLARE'.
Msg 102, Level 15, State 1, Line 52
Incorrect syntax near ')'.

Best Answer

If I am understanding this correctly (even though the first part of what you are doing is not being shown), I think you just need to place the declaration and setting of @remotecmd into the @LinkedServer variable. For example:

DECLARE @CRLF NCHAR(2) = NCHAR(0x000D) + NCHAR(0x000A);
DECLARE @LinkedServer NVARCHAR(MAX) = N'DECLARE @RemoteCMD NVARCHAR(MAX);' + @CRLF;

SET @LinkedServer += N'SET @RemoteCMD = 
N''some query here'';
';

SELECT @LinkedServer += N' INSERT INTO #eapb EXEC(@RemoteCMD) AT [' + name + N'];' + @CRLF
FROM   sys.servers s
WHERE  server_id > 0;

When doing Dynamic SQL, I find it best to use NVARCHAR(MAX) for the variable, and also prefix string literals / constants with a capital-N.

Also, since you are using SQL Server 2014, you can take advantage of the += operator (introduced in SQL Server 2008, I believe), and save yourself some typing :-)

Related Question