在具有条件的循环中使用存储过程的结果(Using the results of a stored procedure in a loop with a condition)

在具有条件的循环中使用存储过程的结果(Using the results of a stored procedure in a loop with a condition)

我有以下代码，我希望用它来确定服务器上是否有活动查询，这个逻辑将用于防止或允许其他事情发生。 我在Powershell做这个。

$Active = invoke-sqlcmd "sp_whoisactive" -database $DATABASE -serverinstance $SQLSERVER -verbose Foreach($item in $Active){ If ($item -eq $null) { "There is nothing active on the database server." } Else { "There is something active on the database server." } }

无论是否运行，它都会给我以下警告：

VERBOSE: Warning: Null value is eliminated by an aggregate or other SET operation.

我假设它来自sp_whoisactive的内部。

另一个问题是，如果服务器上没有任何活动，它就不显示消息，所以我不确定逻辑是否实际触发。

为什么要显示该信息？如何在该类测试中使用该SP的结果？

请注意，我愿意以其他方式执行此操作，问题是我只需要一些可以触发的逻辑来查看服务器上是否存在活动事务。 我会使用类似于查看是否存在连接的内容，但始终存在背景连接。 我只关心影响表的实际活动事务。

编辑：所以我只是给了另一个想法一个镜头，它似乎有同样的问题，我认为当除了标题（没有任何运行）没有任何返回时该怎么办窒息。

$Active = invoke-sqlcmd "sp_whoisactive" -database $DATABASE -serverinstance $SQLSERVER -verbose | Out-String select-string -InputObject $Active -Pattern "query" -quiet

如果某些东西正在运行它将返回True （所以我可以在条件检查中使用它）但如果没有运行则不返回False 。

I have the following code that I am hoping to use to determine whether there is an active query on the server, and this logic will be used to prevent or allow something else to happen. I'm doing this in Powershell.

$Active = invoke-sqlcmd "sp_whoisactive" -database $DATABASE -serverinstance $SQLSERVER -verbose Foreach($item in $Active){ If ($item -eq $null) { "There is nothing active on the database server." } Else { "There is something active on the database server." } }

Regardless of whether something runs or not it gives me the following warning:

VERBOSE: Warning: Null value is eliminated by an aggregate or other SET operation.

Which I'm assuming is coming from the internals of sp_whoisactive.

The other problem is that if nothing is active on the server it doesn't display the message, so I'm not sure that logic is actually firing.

Why would it be showing that information and how could I use the results of that SP in that sort of a test?

Note that I'm open to doing this in other ways, the problem is that I just need some logic that could fire to see if there is an active transaction on the server. I'd use something like seeing if there are connections, but there are always background connections. I'm only concerned about actual active transactions that are affecting tables.

EDIT: So I just gave another idea a shot and it seems to have the same problem, I think it's choking on what to do when nothing is returned except for the headers (when nothing is running).

$Active = invoke-sqlcmd "sp_whoisactive" -database $DATABASE -serverinstance $SQLSERVER -verbose | Out-String select-string -InputObject $Active -Pattern "query" -quiet

It will return True if something is running (so I could use that in a conditional check) but doesn't return False if nothing is running.

最满意答案

看起来我需要了解Powershell如何处理空值berrer，我发现使用此链接。

基本上，如果我只是单独留下$Active变量并进行Powershell测试，那就可以了。 所以结束代码如下所示：

$Active = invoke-sqlcmd "sp_whoisactive" -database $DATABASE -serverinstance $SQLSERVER -verbose If ($Active) { "There is something active on the database server. Stopping." } Else { "There is nothing active on the database server." }

它看起来像是有效的！

It looks like I needed to just understand how Powershell handles null values berrer, which I found using this link.

Basically if I just leave the $Active variable alone and have Powershell test that, it works. So the end code looks like this:

$Active = invoke-sqlcmd "sp_whoisactive" -database $DATABASE -serverinstance $SQLSERVER -verbose If ($Active) { "There is something active on the database server. Stopping." } Else { "There is nothing active on the database server." }

And it looks like it works!