Run query for each element in array or list

Question

I'm trying to monitor all connections that hit our databases.

In order to do so I created this query:

SELECT @@ServerName AS [ServerName]
             ,NAME AS DatabaseName 
             ,COUNT(STATUS) AS [NumberOfConnections]
             ,GETDATE() AS [TimeStamp]
             ,hostname
             ,program_name
             ,loginame
FROM sys.databases sd
LEFT JOIN master.dbo.sysprocesses sp ON sd.database_id = sp.dbid
WHERE database_id NOT BETWEEN 1 AND 4
GROUP BY NAME,hostname ,program_name ,loginame
GO

which returns me exactly what I want on my local computer:

Very good, the ServerName is there, DatabaseName too, the NumberOfConnections too, etc...

But what if I want to run it to a remote server? In my lab in fact I have a remote server which is called [TESLABSQL02T], let's see if I can query it remotely:

SELECT @@ServerName AS [ServerName]
             ,NAME AS DatabaseName 
             ,COUNT(STATUS) AS [NumberOfConnections]
             ,GETDATE() AS [TimeStamp]
             ,hostname
             ,program_name
             ,loginame
FROM [TESLABSQL02T].master.sys.databases sd
LEFT JOIN [TESLABSQL02T].master.sys.sysprocesses sp ON sd.database_id = sp.dbid
WHERE database_id NOT BETWEEN 1 AND 4
GROUP BY NAME,hostname ,program_name ,loginame

That's cool: now I want to monitor all my network!

Here is when things get spicy: I want to automate this in a job that runs every 10 minutes and that can query around 30 servers.

Let's try with 2 servers first [TESLABSQL01T] and [TESLABSQL02T]:

DECLARE @myTableVariable TABLE (id INT, ServerName varchar(50))
insert into @myTableVariable values(1,'[TESLABSQL01T]'),(2,'[TESLABSQL02T]')
select * from @myTableVariable


SELECT @@ServerName AS [ServerName]
             ,NAME AS DatabaseName 
             ,COUNT(STATUS) AS [NumberOfConnections]
             ,GETDATE() AS [TimeStamp]
             ,hostname
             ,program_name
             ,loginame
FROM (select ServerName from @myTableVariable).master.sys.databases sd
LEFT JOIN (select ServerName from @myTableVariable).master.sys.sysprocesses sp ON sd.database_id = sp.dbid
WHERE database_id NOT BETWEEN 1 AND 4
GROUP BY NAME,hostname ,program_name ,loginame

Unfortunately no joy :(

The query is giving me an Incorrect syntax near the FROM and the LEFT JOIN.

How can I make this query to run once on each remote server every 10 minutes?

Should I use a list, an array, a temp table, a normal table?

I hear about FETCH NEXT FROM ObjectCursor , could that be an option?

Dynamic Query?

...every suggestion is valid. Thank you.

Answer 1

To solve the syntax error you have to create an dynamic query using a cursor:

DECLARE @ServerName varchar(50), @DynamicSQL NVARCHAR(MAX)
DECLARE @myTableVariable TABLE (id INT, ServerName varchar(50))
insert into @myTableVariable values(1,'[TESLABSQL01T]'),(2,'[TESLABSQL02T]')
select * from @myTableVariable

Declare VarCursor cursor for
Select ServerName from @myTableVariable
Open VarCursor
FETCH NEXT FROM VarCursor INTO @ServerName
WHILE @@FETCH_STATUS = 0 
BEGIN
    SET @DynamicSQL='SELECT @@ServerName AS [ServerName]
                 ,NAME AS DatabaseName 
                 ,COUNT(STATUS) AS [NumberOfConnections]
                 ,GETDATE() AS [TimeStamp]
                 ,hostname
                 ,program_name
                 ,loginame
    FROM '+@ServerName+'.master.sys.databases sd
    LEFT JOIN '+@ServerName+'.master.sys.sysprocesses sp ON sd.database_id = sp.dbid
    WHERE database_id NOT BETWEEN 1 AND 4
    GROUP BY NAME,hostname ,program_name ,loginame'
    

    EXEC (@DynamicSQL)

    FETCH NEXT FROM VarCursor INTO @ServerName
END
CLOSE VarCursor
DEALLOCATE VarCursor

But for this query you have to ensure that you have privileges in all your servers or that you have linked servers in your principal SQL Server engine