对数组或列表中的每个元素运行查询 [英] Run query for each element in array or list
问题描述
我正在尝试监控访问我们数据库的所有连接.
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:
很好,ServerName
在那里,DatabaseName
还有,NumberOfConnections
也有,等等...
Very good, the ServerName
is there, DatabaseName
too, the NumberOfConnections
too, etc...
但是如果我想将它运行到远程服务器怎么办?实际上,在我的实验室中,我有一个名为 [TESLABSQL02T]
的远程服务器,让我们看看是否可以远程查询:
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!
这是事情变得棘手的时候:我想在每 10 分钟运行一次并且可以查询大约 30 个服务器的作业中自动执行此操作.
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.
让我们先尝试使用 2 个服务器 [TESLABSQL01T]
和 [TESLABSQL02T]
:
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
不幸的是没有快乐:(
查询在 FROM
和 LEFT JOIN
附近给了我一个 Incorrect syntax
.
The query is giving me an Incorrect syntax
near the FROM
and the LEFT JOIN
.
如何使此查询每 10 分钟在每个远程服务器上运行一次?
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?
我听说了 FETCH NEXT FROM ObjectCursor
,这是一个选项吗?
I hear about FETCH NEXT FROM ObjectCursor
, could that be an option?
动态查询?
...每个建议都是有效的.谢谢.
...every suggestion is valid. Thank you.
推荐答案
要解决语法错误,您必须使用游标创建动态查询:
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
但是对于此查询,您必须确保您在所有服务器中都拥有权限,或者您在主要 SQL Server 引擎中拥有链接服务器
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
这篇关于对数组或列表中的每个元素运行查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!