CODEWITHSUNDEEP
powershell
ShellGames
ShellGames

Reputation: 87

Inconsistent PowerShell Script results

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?

Upvotes: 0

Views: 245

Answers (1)

TheMadTechnician
TheMadTechnician

Reputation: 36342

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

Upvotes: 2

Related Questions