Iterate through a list of servers in SQL Server

Question

I have a script similar to below and get:

Could not find server '@CURSERVER' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

I am trying to get SQL Server Agent jobs from all linked servers. I don't think there is a problem with my linked servers but can I not have the server name as a variable?

 -- Declare your array table variable  
DECLARE @SERVERS table (serverName nvarchar(50), ARRAYINDEX int identity(1,1) )  
  
-- Add values to your array table, these are the values which you need to look for in your database  
INSERT INTO @SERVERS (serverName)  
   VALUES  
('server1'), ('server2'), ('server3')
  
DECLARE @INDEXVAR INT = 1
DECLARE @TOTALCOUNT int  
DECLARE @CURSERVER nvarchar (50)  
SELECT @TOTALCOUNT= COUNT(*) FROM @SERVERS 
WHILE @INDEXVAR < @TOTALCOUNT  
BEGIN    
    -- Get value of current indexed server  
    SELECT @CURSERVER = serverName from @SERVERS where ARRAYINDEX = @INDEXVAR  
  
    -- Get details of jobs on the server  
    BEGIN  
        SELECT  
            * FROM [@CURSERVER].[msdb].[dbo].[sysjobs] 
        SET @INDEXVAR += 1
    END  
END  

Answer 1

You cannot use a variable as a identifier (server name, database name, table name, etc.). Instead, construct a dynamic SQL statement and execute.

Below is an example including corrections and improvements to your original script.

DECLARE @SERVERS table (serverName nvarchar(50), ARRAYINDEX int identity(1,1) )  
  
-- Add values to your array table, these are the values which you need to look for in your database  
INSERT INTO @SERVERS (serverName)  
   VALUES  
    ('server1'), ('server2'), ('server3');
DECLARE @TOTALCOUNT int = @@ROWCOUNT;
DECLARE @SQL nvarchar(MAX);
DECLARE @INDEXVAR INT = 1;
DECLARE @CURSERVER sysname 

WHILE @INDEXVAR <= @TOTALCOUNT  
BEGIN    
    -- Get value of current indexed server  
    SELECT @CURSERVER = serverName from @SERVERS where ARRAYINDEX = @INDEXVAR;  
  
    -- Get details of jobs on the server  
    BEGIN  
        SET @SQL = N'SELECT * FROM ' + QUOTENAME(@CURSERVER) + N'.[msdb].[dbo].[sysjobs];';
        --PRINT @SQL;
        EXEC sp_executesql @SQL;
        SET @INDEXVAR += 1;
    END;  
END;