1

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

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

  • 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 Dec 21 '17 at 16:52
  • btw, here is the link where I got my code dba.stackexchange.com/questions/44953/… – Edgar Allan Bayron Dec 21 '17 at 16:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.