If Only 3 Rows in Dataset - Why Are More Than Double Returned in SQL PowerShell Query

Question

Trying to understand why the data is returning more than expected of only 3 rows. if I run the content of $QueryText directly it returns only 3 rows of data. I would expect this would return twice the same 3 rows running this code-

$connString = "data source=dbserver,1433;Initial catalog=mvTest;Integrated Security=True;"
$QueryText = "select m.created_date, m.additional_data as ReasonDown from aeroscout.mv_audit m where m.created_date >= '2020-02-18'and m.created_date <= '2020-02-19' and m.additional_data like '%remainingCapacity={`"o`":`"50.0`",`"n`":`"49.5`"}%'"
$SqlConnection = new-object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $connString
$SqlCommand = $SqlConnection.CreateCommand()
$SqlCommand.CommandText = $QueryText
$DataAdapter1 = new-object System.Data.SqlClient.SqlDataAdapter $SqlCommand
$dataset1 = new-object System.Data.Dataset
$DataAdapter1.Fill($dataset1)
$rowCount = $DataAdapter.Fill($dataset1)

if($rowCount -gt 0) {

     $exportObjectA=@() 
     ForEach ($row in $dataset1.Tables[0].Rows) {

        $ExciterObject = New-Object PSObject
        $ExciterObject | Add-Member -MemberType NoteProperty -Name "Date Created" -Value $row.created_date
        $ExciterObject | Add-Member -MemberType NoteProperty -Name "Reason Down" -Value $row.ReasonDown
        $exportObjectA += $ExciterObject
        Write-Host $exportObjectA
    }
}
Start-Sleep -Seconds 120
$DataAdapter2 = new-object System.Data.SqlClient.SqlDataAdapter $SqlCommand
$dataset2 = new-object System.Data.Dataset
$DataAdapter2.Fill($dataset2)
$rowCount = $DataAdapter.Fill($dataset2)
if($rowCount -gt 0) {


     $exportObjectB=@() 
     ForEach ($row in $dataset2.Tables[0].Rows) {

        $ExciterObject = New-Object PSObject
        $ExciterObject | Add-Member -MemberType NoteProperty -Name "Date Created" -Value $row.created_date
        $ExciterObject | Add-Member -MemberType NoteProperty -Name "Reason Down" -Value $row.ReasonDown
        $exportObjectB += $ExciterObject
        Write-Host $exportObjectB
    }
}

RETURNING the following -

3 @{Date Created=2/18/2020 12:06:40 AM; Reason Down=remainingCapacity={"o":"50.0","n":"49.5"};batteryReplacementDate={"o":"2020-10-29 00:13:33.963","n":"Sat Nov 07 00:06:40 PST 2020"}} @{Date Created=2/18/2020 12:06:40 AM; Reason Down=remainingCapacity={"o":"50.0","n":"49.5"};batteryReplacementDate={"o":"2020-10-29 00:13:33.963","n":"Sat Nov 07 00:06:40 PST 2020"}} @{Date Created=2/18/2020 10:02:56 AM; Reason Down=remainingCapacity={"o":"50. 0","n":"49.5"};batteryReplacementDate={"n":"Sun Oct 11 10:02:56 PDT 2020"}} @{Date Created=2/18/2020 12:06:40 AM; Reason Down=remainingCapacity={"o":"50.0","n":"49.5"};batteryReplacementDate={"o":"2020-10-29 00:13:33.963","n":"Sat Nov 07 00:06:40 PST 2020"}} @{Date Created=2/18/2020 10:02:56 AM; Reason Down=remainingCapacity={"o":"50. 0","n":"49.5"};batteryReplacementDate={"n":"Sun Oct 11 10:02:56 PDT 2020"}} @{Date Created=2/18/2020 5:54:57 PM; Reason Down=remainingCapacity={"o":"50.0","n":"49.5"};batteryReplacementDate={"o":"2020-08-27 16:31:40.633","n":"Sat Aug 29 17:54:57 PDT 2020"}} @{Date Created=2/18/2020 12:06:40 AM; Reason Down=remainingCapacity={"o":"50.0","n":"49.5"};batteryReplacementDate={"o":"2020-10-29 00:13:33.963","n":"Sat Nov 07 00:06:40 PST 2020"}} @{Date Created=2/18/2020 10:02:56 AM; Reason Down=remainingCapacity={"o":"50. 0","n":"49.5"};batteryReplacementDate={"n":"Sun Oct 11 10:02:56 PDT 2020"}} @{Date Created=2/18/2020 5:54:57 PM; Reason Down=remainingCapacity={"o":"50.0","n":"49.5"};batteryReplacementDate={"o":"2020-08-27 16:31:40.633","n":"Sat Aug 29 17:54:57 PDT 2020"}} @{ Date Created=2/18/2020 12:06:40 AM; Reason Down=remainingCapacity={"o":"50.0","n":"49.5"};batteryReplacementDate={"o":"2020-10-29 00:13:33.963","n":"Sat Nov 07 00:06:40 PST 2020"}} @{Date Created=2/18/2020 12:06:40 AM; Reason Down=remainingCapacity={"o":"50.0","n":"49.5"};batteryReplacementDate={"o":"2020-10-29 00:13:33.963","n":"Sat Nov 07 00:06:40 PST 2020"}} @{Date Created=2/18/2020 10:02:56 AM; Reason Down=remainingCapacity={"o":"50. 0","n":"49.5"};batteryReplacementDate={"n":"Sun Oct 11 10:02:56 PDT 2020"}} @{Date Created=2/18/2020 5:54:57 PM; Reason Down=remainingCapacity={"o":"50.0","n":"49.5"};batteryReplacementDate={"o":"2020-08-27 16:31:40.633","n":"Sat Aug 29 17:54:57 PDT 2020"}} @{ Date Created=2/18/2020 12:06:40 AM; Reason Down=remainingCapacity={"o":"50.0","n":"49.5"};batteryReplacementDate={"o":"2020-10-29 00:13:33.963","n":"Sat Nov 07 00:06:40 PST 2020"}} @{Date Created=2/18/2020 10:02:56 AM; Reason Down=remainingCapacity={"o":"50.0" ,"n":"49.5"};batteryReplacementDate={"n":"Sun Oct 11 10:02:56 PDT 2020"}} @{Date Created=2/18/2020 12:06:40 AM; Reason Down=remainingCapacity={"o":"50.0","n":"49.5"};batteryReplacementDate={"o":"2020-10-29 00:13:33.963","n":"Sat Nov 07 00:06:40 PST 2020"}} @{Date Created=2/18/2020 10:02:56 AM; Reason Down=remainingCapacity={"o":"50. 0","n":"49.5"};batteryReplacementDate={"n":"Sun Oct 11 10:02:56 PDT 2020"}} @{Date Created=2/18/2020 5:54:57 PM; Reason Down=remainingCapacity={"o":"50.0","n":"49.5"};batteryReplacementDate={"o":"2020-08-27 16:31:40.633","n":"Sat Aug 29 17:54:57 PDT 2020"}} @{ Date Created=2/18/2020 12:06:40 AM; Reason Down=remainingCapacity={"o":"50.0","n":"49.5"};batteryReplacementDate={"o":"2020-10-29 00:13:33.963","n":"Sat Nov 07 00:06:40 PST 2020"}} @{Date Created=2/18/2020 10:02:56 AM; Reason Down=remainingCapacity={"o":"50.0" ,"n":"49.5"};batteryReplacementDate={"n":"Sun Oct 11 10:02:56 PDT 2020"}} @{Date Created=2/18/2020 5:54:57 PM; Reason Down=remainingCapacity={"o":"50.0","n":"49.5"};batteryReplacementDate={"o":"2020-08-27 16:31:40.633","n":"Sat Aug 29 17:54:57 PDT 2020"}}

PS C:\Users\figuerdsa>

You are displaying `$exportObjectA` and `$exportObjectB` after every row loop. Move the `Write-Host $exportObjectA` and `Write-Host $exportObjectB` lines outside of the loops after the closing `}`. Also remove `Write-Host` as it is not needed. — Daniel, Feb 19 '21 at 01:16
As a side note: [try avoid using the increase assignment operator (`+=`) to create a collection](https://stackoverflow.com/a/60708579/1701026) as it is exponentially expensive. — iRon, Feb 19 '21 at 07:45

If Only 3 Rows in Dataset - Why Are More Than Double Returned in SQL PowerShell Query

0 Answers0