Using the results of a stored procedure in a loop with a condition

Question

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.

If you validated your procedure is returning the correct results, you can turn off the warning message. `SET ANSI_WARNINGS OFF`. — jdl, Jan 24 '14 at 20:39

score 0 · Accepted Answer · edited May 23 '17 at 12:03

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!

Using the results of a stored procedure in a loop with a condition

1 Answers1