Nesting Invoke-SqlCmd in the Pipeline

If you need to collect data from an list of SQL Servers stored in a table, you would write a PowerShell script that uses Invoke-SqlCmd to query the list from the table and then loop through each instance to execute another query. However, PowerShell 5 does not support nesting Invoke-SqlCmd in the pipeline. The workaround is to use an intermediate variable or use PowerShell 7. 

Scenario 

For example you have a table with the SQL instance names stored in SQL1

create table instances(name varchar(255))
insert instances values ('SQL1')
insert instances values ('SQL2')
insert instances values ('SQL3')

To query the list, you use Invoke-SqlCmd in PowerShell 5

$query = "select name from instances"
Invoke-Sqlcmd -ServerInstance 'SQL1' -Query $query

To loop through each instance, you pipe the results and use Invoke-SqlCmd to execute another query to let's say get the version

Invoke-Sqlcmd -ServerInstance 'SQL1' -Query $query |
ForEach-Object {
    $query2 = "select instance = serverproperty('InstanceName'), version = serverproperty('ProductVersion')"
    Invoke-Sqlcmd -ServerInstance $_.name -Query $query2
}

The script will fail with this error

# Invoke-Sqlcmd : The WriteObject and WriteError methods cannot be 
# called from outside the overrides of the BeginProcessing, ProcessRecord,     
# and EndProcessing methods, and they can only be called from within the same thread.
# Validate that the cmdlet makes these calls correctly, or contact Microsoft Customer 
# Support Services.

Workaround 

Use a variable $instances to hold the results of the first Invoke-SqlCmd and then pipe it

$instances = Invoke-Sqlcmd -ServerInstance 'SQL1' -Query $query 
$instances |
ForEach-Object {
    $query2 = "select instance = serverproperty('InstanceName'), version = serverproperty('ProductVersion')"
    Invoke-Sqlcmd -ServerInstance $_.name -Query $query2
}

or use PowerShell 7

Invoke-Sqlcmd -ServerInstance 'SQL1' -Query $query |
ForEach-Object {
    $query2 = "select instance = serverproperty('InstanceName'), version = serverproperty('ProductVersion')"
    Invoke-Sqlcmd -ServerInstance $_.name -Query $query2
}

# instance     version
# --------     -------
# SQL1         13.0.5865.1
# SQL2         13.0.5865.1
# SQL3         13.0.5492.2