0

I'm trying to run two Invoke-Sqlcmd in parallel and then wait all the results.

$server1 = '...'
$server2 = '...'

workflow work {
  parallel {
    $r1 = Invoke-Sqlcmd -ServerInstance $server1 'select 1 a'
    $r2 = Invoke-Sqlcmd -ServerInstance $server2 'select 2 a'
  }
}
work
# do something with $r1 and $r2. How to access the variables here?
  1. How to pass $server1 and $server2 to the code block?
  2. How to get $r1 and $r2 from the parallel block?
mklement0
  • 382,024
  • 64
  • 607
  • 775
ca9163d9
  • 27,283
  • 64
  • 210
  • 413
  • Given that workflows are obsolescent (they're no longer supported in PowerShell _Core_), I suggest looking into `Start-ThreadJob` - see [this answer](https://stackoverflow.com/a/56612574/45375). – mklement0 Oct 28 '19 at 23:00
  • You can pass in servers with a `param ()`. Not sure how to get the results except `$result = work $server1 $server2`. Maybe jobs is easier. – js2010 Oct 29 '19 at 02:17
  • 1
    @mklement0, I tried ThreadJob. But it doesn't work with Invoke-SqlCmd https://stackoverflow.com/questions/58609740/threadjob-the-writeobject-and-writeerror-cannot-be-called-from-outside-the-over – ca9163d9 Oct 29 '19 at 14:47
  • Interesting, @ca9163d9: I think the problem is a bug in `Invoke-SqlCmd`, as detailed in my comment on your linked question. You can work around the problem with regular child-process-based background jobs (`Start-Job`), but note that they have a lot more overhead than thread jobs - for long-running SQL queries that won't matter, however. – mklement0 Oct 29 '19 at 19:48

1 Answers1

1

So pass in servers with param, and return a hashtable of both results.

$server1 = '...'
$server2 = '...'

workflow work {
  param ($server1, $server2)
  parallel {
    @{r1 = Invoke-Sqlcmd -ServerInstance $server1 'select 1 a'}
    @{r2 = Invoke-Sqlcmd -ServerInstance $server2 'select 2 a'}
  }
}
$r = work $server1 $server2
$r.r1
$r.r2
js2010
  • 23,033
  • 6
  • 64
  • 66