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.

As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K
– Dale K, Commented Jun 10, 2021 at 21:00
@DaleK, I removed the images. The problem is that creating a table with the code in StackOverflow is a pain. Is there an easy way? — Francesco Mantovani
– Francesco Mantovani, Commented Jun 11, 2021 at 6:48
Hi @DaleK, I found a website that could help me format the tables: tablesgenerator.com/markdown_tables# . I will use that from now on. I believe StackOverflow should sponsor this or copy the same same tool and make it available in the editor — Francesco Mantovani
– Francesco Mantovani, Commented Mar 29, 2022 at 7:22
Stackoverflow does support tables meta.stackoverflow.com/questions/302471/… — Dale K
– Dale K, Commented Mar 29, 2022 at 7:25

Dale K · Accepted Answer · 2021-06-11 07:16:59Z

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

edited Jun 11, 2021 at 7:16

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Jun 10, 2021 at 22:19

Isaac Sánchez

361 bronze badge

Sign up to request clarification or add additional context in comments.

3 Comments

Francesco Mantovani Over a year ago

You teach me FETCH NEXT, thank you @Isaac

Francesco Mantovani Over a year ago

If I run sp_who2 on the remote server I see my user connected with SSMS in a DORMANT state. Is there a way to disconnect my user? Thank you again

Francesco Mantovani Over a year ago

but it says Cannot use KILL to kill your own process.

Collectives™ on Stack Overflow

Run query for each element in array or list

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related