Loop INSERT EXEC AT through linked servers

Question

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 ')'.

Answer 1

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 :-)

• Edgar Allan Bayron: On the last part of my script I have EXEC sp_executesql @LinkedServer and Im getting errors inside my script like Msg 102, Level 15, State 1, Line 29 Incorrect syntax near ' + QUOTENAME(@db) + N'. Maybe due to the apostrophe? Thats the conflict with your script?
• Edgar Allan Bayron: btw, here is the link where I got my code dba.stackexchange.com/questions/44953/…