Powershell outputs mixed between loops - SQL datasets [duplicate]

Question

I have a PowerShell script that loops through list of 3 servers. A SQL script is run with Invoke-Sqlcmd and the result set is stored to variable $DS. At the end of the loop I return the records with with $DS.Tables.Rows.

But the results sets are getting mixed together. I tried using a Write-Host message to breakup the results. But they are still getting mixed together.

Why are the result getting mixed together in the output?

How can I separate the outputs between each loop?

Thanks

Object type

$DS | gm ............... TypeName: System.Data.DataSet

$DS.Tables | gm ........ TypeName: System.Data.DataTable

$DS.Tables.Rows | gm ... TypeName: System.Data.DataRow

Script

#########################>

# SQL servers
$PCList= @("GCOD139","GCOD039","GCOP039") 
Write-Host ($PCList -join ", ") 

# Query multiple servers
foreach ($PC in $PCList) {

    Write-Host ($PC + "...") -ForegroundColor Yellow

    # SQL parameters
    $Params = @{
        'ServerInstance' = $PC;  
        'Database' = 'master';
    #   'Username' = 'svcBIPOC';
    #   'Password' = 'bipoc2020*';
    #   'InputFile' = "C:\ScriptFolder\TestSqlCmd.sql"
        'Query' = '
            SELECT 
                [Server]= @@SERVERNAME
                --MB to GB
                , REPLACE(name, ''MB'', ''GB'')
                ,[value]= CAST(value as int)/1000
                , [value_in_use]= CAST(value_in_use as int)/1000
                --, value, value_in_use, [description]
            FROM sys.configurations
            WHERE name like ''%server memory%''
            ORDER BY name desc 
            OPTION (RECOMPILE);
        '
    }

    # Capture SQL Dataset
    # (Get-Date).ToSTring('s')  + " SQL query start..."
    $DS = Invoke-Sqlcmd @Params -As DataSet
    #(Get-Date).ToSTring('s')  + " SQL query end..."

    Write-host "-----"
    Write-host "SQL"

    sleep -Seconds 5
    $DS.Tables.Rows
    sleep -Seconds 5

}


#########################

While Mathias' recommendation to use Write-Progress instead bypasses your problem, its root cause is explained in the accepted answer to the linked question. — mklement0
– mklement0, Commented Aug 7, 2020 at 14:40
Using Out-Host as recommended in the linked article did solve the issue. So $DS.Tables.Rows | Out-Host — SherlockSpreadsheets
– SherlockSpreadsheets, Commented Aug 7, 2020 at 19:37

Mathias R. Jessen · Accepted Answer · 2020-08-07 13:46:47Z

1

Stop using Write-Host to convey progress information - use Write-Progress for that instead!

$PCList= @("GCOD139","GCOD039","GCOP039") 
Write-Progress -Activity "Query servers" -Status "About to query: $($PCList -join ", ")"

# Query multiple servers
foreach ($PC in $PCList) {

    Write-Progress -Activity "Query servers" -Status "Querying: $PC"

    # SQL parameters
    $Params = @{
        'ServerInstance' = $PC;  
        'Database' = 'master';
    #   'Username' = 'svcBIPOC';
    #   'Password' = 'bipoc2020*';
    #   'InputFile' = "C:\ScriptFolder\TestSqlCmd.sql"
        'Query' = '
            SELECT 
                [Server]= @@SERVERNAME
                --MB to GB
                , REPLACE(name, ''MB'', ''GB'')
                ,[value]= CAST(value as int)/1000
                , [value_in_use]= CAST(value_in_use as int)/1000
                --, value, value_in_use, [description]
            FROM sys.configurations
            WHERE name like ''%server memory%''
            ORDER BY name desc 
            OPTION (RECOMPILE);
        '
    }

    # Capture SQL Dataset
    $DS = Invoke-Sqlcmd @Params -As DataSet
    $DS.Tables.Rows
}

Write-Progress -Activity "Query servers" -Completed

Now the progress messages won't interfere with the actual output from the function

answered Aug 7, 2020 at 13:46

Mathias R. Jessen

178k13 gold badges175 silver badges234 bronze badges

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

2 Comments

SherlockSpreadsheets Over a year ago

I tried this. While the Write-Progress is interesting (it gives a highlighted status bar while the script runs), it does not solve my problem. The data set results are still blended. I want the outputs to have a divider, like "----- SQL ----" text message, between each result set,

Mathias R. Jessen Over a year ago

@SherlockSpreadsheets .\yourscript.ps1 |Format-List -GroupBy Server <-- probably the best compromise - will group and divide the output (although not with the exact text you want)

Collectives™ on Stack Overflow

Powershell outputs mixed between loops - SQL datasets [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related