Powershell ForEach - new loops starting before current loops ending

Question

I have a quick and dirty Powershell script for running sql scripts against many servers sequentially. I have the servers stored in an array, and loop through them with ForEach. The ForEach roughly looks like this:

ForEach ($Server in $ServerList) {
  Write-Host "Executing $Script against $Server..."

  Invoke-SqlCmd ........
}

But the problem I have is my output looks something like this:

Executing script.sql against Server1
Executing script.sql against Server2
Executing script.sql against Server3

<Output from Server1>
<Output from Server2>
<Output from Server3>

Executing script.sql against Server4

<Output from Server4>

Executing script.sql against Server5

<Output from Server5>

...you get the idea. Is there any way to marry up the outputs so that the output appears under the message depicting which server is currently being executed on? It would help with using output for debugging etc. Executing on PS7 by the way.

Seems like `-Parallel` could be messing up. Try using `ForEach -Parallel:$false ...` to see if it's fixed. — AlwaysLearning, Feb 22 '22 at 10:16
Try this: `Invoke-SqlCmd ........ | Out-Host`. This makes sure every `Invoke-SqlCmd` command outputs immediately, in its own table. Otherwise the formatter might collect multiple command outputs before displaying them. — zett42, Feb 22 '22 at 10:54
@zett42 thank you - Can you make this an answer, so I can mark it as the accepted answer? — Cam, Feb 22 '22 at 14:49

score 2 · Answer 1 · answered Feb 22 '22 at 12:21

What you're observing here is not that the next iteration of the foreach loops starts before the last one has ended - foreach (the loop statement, not the cmdlet) only invokes the loop body in series, never concurrently.

That doesn't mean the next iteration won't start before the formatting subsystem in the host application (eg. powershell.exe or powershell_ise.exe or pwsh.exe) has written any output from the loop body to the screen buffer.
The default host applications usually waits a few 100 milliseconds to see if there's more than one output object of the same type in the output stream - which will then inform how to format the output (table vs list view etc.).

Write-Host on the other hand is an instruction to bypass all that and instead write a message straight to the host application.

So this differentiated delay makes it look like the Write-Host statement at the top is being executed before the code from 2 iterations back - but what you're observing is actually an intentional decoupling of output vs rendering/presentation.

As zett42 notes, you can force the host application to synchronously render the output you want displayed in order by piping it to Out-Host:

ForEach ($Server in $ServerList) {
  Write-Host "Executing $Script against $Server..."

  Invoke-SqlCmd ........ |Out-Host
}

Since PowerShell must now fulfill your request to render the output before it can move on with the next statement/iteration, it'll no longer delay it for formatting purposes :)

Powershell ForEach - new loops starting before current loops ending

1 Answers1