SQL Server cursor - loop through multiple servers and execute query

Question

I have the following code (cursor):

DECLARE @SN VARCHAR(20);
DECLARE @sql NVARCHAR(MAX); 
DECLARE C CURSOR LOCAL FAST_FORWARD
  FOR SELECT DISTINCT(SERVERNAME) FROM INSTALLATION 
    where DATABASETYPE = 'MsSql' AND SERVERNAME IN ('x');

OPEN C;

FETCH NEXT FROM C INTO @SN;
WHILE (@@FETCH_STATUS = 0)
BEGIN 
    PRINT @SN;
    -- you could loop here for each database, if you'd define what that is
   SELECT name 
FROM master.dbo.sysdatabases 
WHERE name not in ('master','model','msdb','tempdb');
    SET @sql = N'SELECT TOP 1 NAME FROM TABLE ';

    EXEC sp_executesql @sql;
    FETCH NEXT FROM C INTO @SN;
END 
CLOSE C;
DEALLOCATE C;

I would like to be able to loop through every server and execute a select statement on some (not all) of their databases.

The query is something like:

SELECT TOP 1 NAME FROM TABLE 

The server from where I am running the cursor has all the others as linked servers.

Answer 1

DECLARE @SN VARCHAR(20);

DECLARE C CURSOR LOCAL FAST_FORWARD
  FOR SELECT DISTINCT(SERVERNAME) FROM TABLE 
  where SERVERNAME NOT IN ('SRV1','SRV2','SRV3');

OPEN C;

FETCH NEXT FROM C INTO @SN;
WHILE (@@FETCH_STATUS = 0)
BEGIN 
    PRINT @SN;
    -- you could loop here for each database, if you'd define what that is
    SET @sql = N'SELECT * FROM ' + @SN + '.master.dbo.TABLE;';
    EXEC sys.sp_executesql @sql;
    FETCH NEXT FROM C INTO @SN;
END 
CLOSE C;
DEALLOCATE C;

Changes:

1. There is no reason to use the default cursor options here - global, updatable, dynamic, scrollable, etc. Background.

2. As a habit / best practice, use sp_executesql and not EXEC(). While it doesn't really matter in this case, it can matter in others, so I'd prefer to always code the same way. Background.

3. Also, please get in the habit of terminating your statements with semi-colons. You'll have to, eventually. Background.

EDIT

Now that we have a little more information about your actual requirements, I suggest this bit of code. Oh, and look, no cursors (well, no explicit cursor declarations and all the scaffolding that comes with them)!

SET NOCOUNT ON;

DECLARE @dbs TABLE(SERVERNAME SYSNAME, DBNAME SYSNAME);

DECLARE @sql NVARCHAR(MAX) = N'';

-- first, let's get the databases on each server:

SELECT @sql += N'SELECT ''' + SERVERNAME + ''', name FROM '
 + QUOTENAME(SERVERNAME) + '.master.sys.databases
   WHERE database_id > 4 
   AND name NOT IN (N''somedb'',N''someotherdb'');' 
 FROM dbo.INSTALLATION 
   WHERE DATABASETYPE = 'MsSql' 
   AND SERVERNAME IN ('x');

INSERT @dbs EXEC sys.sp_executesql @sql;

SELECT @sql = N'';

-- now, build a command to run in each database context:

SELECT @sql += N'
  EXEC ' + QUOTENAME(SERVERNAME) + '.'
  + QUOTENAME(DBNAME) + '.sys.sp_executesql @sql;'
  FROM @dbs;

-- feel free to change the 3rd parameter here:

EXEC sys.sp_executesql @sql, N'@sql NVARCHAR(MAX)', 
  N'SELECT @@SERVERNAME, DB_NAME(), actual_columns FROM dbo.table_name;';

This will fail if table_name doesn't exist, so you may still have some work to do if you want to facilitate error handling. But this should get you started.

Also, please be conscious of, and consistently use, the schema prefix. Background.