Invoke-SqlCmd generates an exception when used in a loop that contains another Invoke-SqlCmd

Question

I'm having an issue with calling Invoke-SqlCmd when it contains a second Invoke-SqlCmd call:

function Get-Foo
{

  $query=`
@"
WITH data AS 
(
  SELECT  1 ID, 'A' NAME
  UNION ALL
  SELECT  2 ID, 'B' NAME
  UNION ALL
  SELECT  3 ID, 'C' NAME
)
SELECT  *
FROM    data
"@

  Invoke-SqlCmd -ServerInstance "SERVER" -Database "DATABASE" -Query $query

}

function Get-Bar
{
  param
  (
    [int]$ParentId
  )

  $query=`
@"
WITH data AS 
(
  SELECT  1 ID, 'A' NAME, 1 PARENT_ID
  UNION ALL
  SELECT  2 ID, 'B' NAME, 1 PARENT_ID
  UNION ALL
  SELECT  3 ID, 'C' NAME, 2 PARENT_ID
)
SELECT  *
FROM    data
WHERE   parent_id = $ParentId
"@

  Invoke-SqlCmd -ServerInstance "SERVER" -Database "DATABASE" -Query $query

}

Get-Foo | ForEach-Object {

  Get-Bar -ParentId $_.ID

}

The first iteration of the outer loop works fine, but when it attempts the the second iteration, and exception is generated:

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. At Untitled-1.ps1:18 char:3 + Invoke-SqlCmd -ServerInstance "SERVER" -Database "DATABASE ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidResult: (SERVER:PSObject) [Invoke-Sqlcmd], PSInvalidOperationExceptio n + FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

This syntax works, however:

$foo = Get-Foo
$foo | ForEach-Object {

  Get-Bar

}

I'm guessing that I need to close the first Invoke-SqlCmd, but perhaps there is another solution.

Answer 1

This is related to the way the PowerShell pipeline works (see Understanding pipelines), and a limitation in Invoke-SqlCmd where it doesn't like running more than one query in parallel.

To illustrate, let's rewrite your example as follows:

function Invoke-SqlCmd
{
    write-output "aaa";
    write-output "bbb";
    write-output "ccc";
}

function Get-Foo
{
    write-host "Get-Foo started";
    Invoke-SqlCmd;
    write-host "Get-Foo finished";
}

function Get-Bar
{
    param( $value )
    write-host "Get-Bar '$value'";
}

and now run this script:

write-host "using pipelines"
Get-Foo | foreach-object { Get-Bar $_ }

which gives this output:

using pipelines
Get-Foo started
Get-Bar 'aaa'
Get-Bar 'bbb'
Get-Bar 'ccc'
Get-Foo finished

If you look at the output, you can see that the "Get-Bar" commands are being called while Get-Foo is still "active" in the pipeline.

Compare to this:

write-host "using immediate evaluation"
(Get-Foo) | foreach { Get-Bar $_ }

which gives:

using immediate evaluation
Get-Foo started
Get-Foo finished
Get-Bar 'aaa'
Get-Bar 'bbb'
Get-Bar 'ccc'

where Get-Foo is executed to completion before the values are piped into Get-Bar.

Your specific error is due to your script using multiple concurrent pipeline steps but Invoke-SqlCmd not supporting multiple concurrent pipeline steps. Fortunately you can tell PowerShell to use "immediate evaluation" instead in a few different ways, including the approaches mentioned in other answers:

• assigning the expression to a temporary variable first:

$foo = Get-Foo;
$foo | ForEach-Object { Get-Bar $_ }

• using the SubExpression operator in your script (note the $ is optional in some cases as per Is "Dollar-sign" optional in powershell "$()"?)

(Get-Foo) | Foreach-Object { Get-Bar $_ }

• or wrap Invoke-SqlCmd in a subexpression inside your function. (This one is a bit misleading because Get-Foo is still active in the pipeline but Invoke-SqlCmd is evaluated immediately).

function Get-Foo
{
    write-host "Get-Foo started";
    (Invoke-SqlCmd);
    write-host "Get-Foo finished";
}