Powershell: Why is my variable empty after using ForEach-Object -Parallel?

tl;dr

• Use the $using: scope to refer to the value of variables defined in the caller's scope, as you're partially already doing.

• You cannot directly modify variables in the caller's scope ($using:DBInventory += $result would not work), but you don't need to: let PowerShell collect the output objects in an array for you:

$DBDetails = "SELECT @@VERSION"

$VMs = ("vm1", "vm2", "vm3", "vm4", "vm5", "vm6", "vm7")
 = @()

$scriptBlock = {
    $vm = $_
    $result = Invoke-Sqlcmd -ServerInstance $vm -Query $using:DBDetails
    Write-Host "Outputting $($result.Count) rows from $($vm)"
    $result # Simply output the objects 
}

# Let PowerShell collect all output objects from the ForEach-Object -Parallel call 
# in an array.
# Note: The [array] type constraint ensures that $DBInventory is an array
#       even if there happens to be only *one* output object.
[array] $DBInventory = $VMs | ForEach-Object -Parallel $scriptBlock

Write-Host "Number of elements in DBInventory: $($DBInventory.Count)"

$DBInventory will contain a regular PowerShell array ([object[]]).

Background information:

• Your code already partially shows awareness that you need the $using: scope inside a script block that runs in a different runspace (such as those in the threads that ForEach-Object -Parallel creates) in order to refer to variable values from the caller's scope.

  • This therefore in principle applies to your caller-side $DBInventory variable as well, however:
    • A $using: reference is a reference to a variable value, not to a variable itself, so you cannot assign to $using: references.
    • That is, $using:DBInventory += $result would not work, leaving aside the general point that using += to "grow" arrays is best avoided due to its inefficiency - see this answer.

• While you could initialize $DBInventory to an efficiently extensible list type, you'd have to ensure that it is grown in thread-safe manner, given that you're using thread-based parallelism via ForEach-Object -Parallel:

  • Notably, the commonly used list types [System.Collections.Generic.List[object]] and System.Collections.ArrayList are not thread-safe.

  • You'd either have to:

    • add manual synchronization code to your script block, using .NET APIs, which is nontrivial.
    • pick a different, concurrent (thread-safe) collection type from the System.Collections.Concurrent namespace (there is none built in for lists as of .NET 8; this answer offers a custom implementation)
    • use a thread-safe wrapper, e.g. $DBInventory = [System.Collections.ArrayList]::Synchronized([System.Collections.Generic.List[object]] @()), which returns a non-generic [System.Collections.IList] implementation.
      Note, however, that this is inefficient, due to the whole-list locking mechanism used - see Thread-safe collections; additionally, it doesn't expose an .AddRange() method for efficient appending of multiple elements, and its .Add() method returns a (usually unwanted) value, which you'll have to discard with $null = ($using:DBInventory).Add(...)

  • Note that the reason that growing lists via $using: does work - as opposed to via += - is that you're adding elements via methods (.Add(), .AddRange()) of the object that is the value of the variable being referenced with $using:. That is, you're directly modifying the variable value, not the variable itself (which isn't supported).

• Fortunately, there's a simpler solution: Rely on PowerShell's ability to automatically collect all output objects emitted by a pipeline in an array, which is both more concise and more efficient than manual growth of a list, and also works with ForEach-Object -Parallel, as shown at the top - again, see this answer for background information.