CODEWITHSUNDEEP
sql-serverpowershell
craig
craig

Reputation: 26262

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

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.

Upvotes: 3

Views: 2891

Answers (3)

mclayton
mclayton

Reputation: 9975

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 $_ }


(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";
}

Upvotes: 3

craig
craig

Reputation: 26262

Based on Defending Invoke-SqlCmd, I decided to implement my own Invoke-SqlCmd cmdlet:

function Invoke-SqlCmd
{

  [cmdletbinding()]
  param
  (
    [string]$ServerInstance,
    [string]$Database,
    [string]$Query
  )

  Write-Debug $MyInvocation.MyCommand.Name
  Write-Debug "$ServerInstance.$Database"

  try {
    $Connection = New-Object System.Data.SQLClient.SQLConnection

    $Connection.ConnectionString = "server=$ServerInstance;database=$Database;trusted_connection=true;"
    $Connection.Open()

    $Command = New-Object System.Data.SQLClient.SQLCommand
    $Command.Connection = $Connection
    $Command.CommandText = $Query

    $DataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $Command
    $Dataset = New-Object System.Data.Dataset

    $DataAdapter.Fill($Dataset) | Out-Null # suppress record count

    $Dataset.Tables[0]
  }
  catch 
  {
    Write-Error $_
  }
  finally
  {
    $Connection.Close()
  }

}

This implementation will be used instead of the 'regular' Invoke-SqlCmd cmdlet in my functions.

The loop works as expected.

Upvotes: 0

Joost
Joost

Reputation: 1216

I cannot explain why this isn't working (although it looks like the threads are being mixed and the .NET provider cannot handle it properly).

What does work (but this is basically the same as your proposal of assigning it to a variable first) is to surround Get-Foo by parentheses:

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

You can also experiment with the -Begin, -Process and -End parameters of Foreach-Object:

Get-Foo | ForEach-Object -Process {$_} -End { Get-Bar }

Upvotes: 2

Related Questions