Inconsistent PowerShell Script results

Question

I have a powershell script that runs an invokesqlcmd statement and counts the number of lines in the return and creates a variable based on that number. It should only close out if there are one or two rows, depending on if there are errors returned (some errors are expected) but a couple of times it has closed while there were still three rows. I want it to close, and it normally does if the only values are "Successful" and "Failure" but it has closed out with a 3rd value of "Processing".

Here is the relevant part of the script.

$var = invoke-sqlcmd -ServerInstance "$server" -database "$db" -inputfile "C:\updated-selectstatus.sql" |measure-object -line|select-object -expandproperty lines

while ($var -ge 1)
{
        $var = invoke-sqlcmd -ServerInstance "$server" -database "$db" inputfile "$updated-selectstatus.sql" |measure-object -line|select-object -expandproperty lines
                clear-host
                invoke-sqlcmd -ServerInstance "$server" -database "$db" -inputfile "$updated-selectstatus.sql"
                Start-Sleep -Seconds 5
        if ($var -eq 2){
            if ((invoke-sqlcmd -ServerInstance "$server" -database "$db" -inputfile "$updated-selectstatus.sql"|select-object -expandproperty statusname|select-string -simplematch successful) -and (invoke-sqlcmd -ServerInstance "$server" -database "$db" -inputfile "$updated-selectstatus.sql"|select-object -expandproperty statusname|select-string -simplematch failure)) {invoke-sqlcmd -ServerInstance "$server" -database "$db" -inputfile "$updated-selectstatus.sql"|out-gridview; break}
                }
            if ($var -eq 1){
            if ((invoke-sqlcmd -ServerInstance "$server" -database "$db" -inputfile "$updated-selectstatus.sql"|select-object -expandproperty statusname|select-string -simplematch successful) -or (invoke-sqlcmd -ServerInstance "$server" -database "$db" -inputfile "$updated-selectstatus.sql"|select-object -expandproperty statusname|select-string -simplematch failure)) {invoke-sqlcmd -ServerInstance "$server" -database "$db" -inputfile "$updated-selectstatus.sql"|out-gridview; break}
            }

}

Have I somehow jumbled up my test logic conditions?

TheMadTechnician · Accepted Answer · 2017-07-14 18:28:26Z

2

You are invoking that sql command way too many times. Invoke once, capture results in a variable, run tests against that variable.

$var = invoke-sqlcmd -ServerInstance "$server" -database "$db" -inputfile "$updated-selectstatus.sql"

while ($var.count -gt 2) {
    Start-Sleep -Second 5
    $var = invoke-sqlcmd -ServerInstance "$server" -database "$db" -inputfile "$updated-selectstatus.sql"
}

That runs your sql query, and then if it is more than 2 results it waits 5 seconds and tries again until there is no more than 2 results. Past that it is a little confusing what you are trying to do. Your code says:

If there are 2 results, and the 'statusname' property for one is 'Success', and the other is 'Failure', then output to GridView. If there is only one result, and the 'statusname' property is either 'Success' or 'Failure', then output to GridView.

This seems flawed to me. What if there are 2 results, and they are both success, or are both failure? If there is only 1 result, is it possible for it to be anything other than success or failure? Do you need to add more clauses to the While test maybe? Does it need to wait until nothing is processing? Maybe this would be better:

$var = invoke-sqlcmd -ServerInstance "$server" -database "$db" -inputfile "C:\updated-selectstatus.sql" 
While($var.count -gt 2 -or $var.statusname -contains 'Processing'){
    Start-Sleep -Seconds 5
    $var = invoke-sqlcmd -ServerInstance "$server" -database "$db" -inputfile "C:\updated-selectstatus.sql" 
}
$var | Out-GridView

Update: One way to loop until all responses are either SUCCESSFUL or FAILURE is to filter out all objects that are successful or failure, and see if you have anything left. See if this suites your needs better:

$var = invoke-sqlcmd -ServerInstance "$server" -database "$db" -inputfile "C:\updated-selectstatus.sql" 
While(($var|Where{$_.statusname -notmatch 'SUCCESSFUL|FAILURE'}).count -gt 0){
    Start-Sleep -Seconds 5
    $var = invoke-sqlcmd -ServerInstance "$server" -database "$db" -inputfile "C:\updated-selectstatus.sql" 
}
$var | Out-GridView

edited Jul 14, 2017 at 18:28

answered Jul 13, 2017 at 0:14

TheMadTechnician

36.5k3 gold badges48 silver badges63 bronze badges

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

3 Comments

ShellGames Over a year ago

Thanks for the feedback. I'll use the .count to clean up the script. I had the second invoke command because the loop never ended since there was no evaluation once execution began. For the rest of the issue I probably wasn't the clearest in my original post. This script monitors a data load job running on the sql server there are about 2000+/- transactions which normally ends with about 5 or 6 errors and the rest are successful. Both of the IF statements at least in my mind were written to terminate only if sql command only returns SUCCESSFUL and/or FAILURE results.

TheMadTechnician Over a year ago

I've updated my answer, see if that works better for you.

ShellGames Over a year ago

I've implemented your update and it looks like it did the trick. Thank you so much.

Collectives™ on Stack Overflow

Inconsistent PowerShell Script results

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related