CODEWITHSUNDEEP
powershell
Jason
Jason

Reputation: 305

Display results of a SQL query with PowerShell

I have been provided a PS script which calls a simple SQL query, displays the results and plays a voice... supposedly. This is designed to go into a BI gadget which just displays a number from the PS script.

The correct count from the query is not displaying and is for some reason stuck on 2.

$sql = "<simple COUNT query>"

$items = invoke-sql -server "<server name>" -database "<db name>" -user "<uname>" -password "<password>" -sql $sql -timeout 180

$day = (get-date).DayOfWeek.Value__

# $items[0] = 10
if ($day -ge 1 -and $day -le 5)
{

    $date = Get-Date

    if ($date.Hour -ge 8 -and $date.Hour -lt 17)
    {
        if ($items[0] -gt 0)
        {
            $voice = New-Object -Com sapi.spvoice
            $voice.Rate = 1
            $voice.volume = 100

            Do {
                Start-Sleep -s 1
                $Counter = Get-Date
            } 
            Until ($Counter.Second/15 -is [int])

            if ($items[0] -gt 1)
            {
                [void]$voice.Speak("New Ticket.")
            }
            else
            {
                [void]$voice.Speak("New Ticket.")
            }

        }
    }
}
Write-Output $items.Count

The SQL component I have no issues with and definitely works correctly when run, but I have no experience with PowerShell scripting so can someone please assist?

Upvotes: 1

Views: 5258

Answers (1)

JNK
JNK

Reputation: 65147

$Items.count is going to give you the number of records in your result set. Technically these are DataRow objects and $Items is a DataTable. If you are getting multiple rows back, you need to determine WHICH row you want to show.

Basically the way to would query the returned DataTable is:

($Items[x]).Column

Where x is the 0-indexed row you want to return and Column is the name of the column you want to return. So if you want the ItemCount column from the second row you would use:

($Items[1]).ItemCount

Upvotes: 1

Related Questions