Background Job Hung

[Ben]

I want to use background jobs to manipulate SQL data. I have a PS script
JobName.ps1 as the following:

param([string]$instance, [string]$database)

$destinationConnection = New-Object System.Data.SqlClient.SqlConnection
$destinationConnection.ConnectionString =
"Server=$instance;Database=$database;Integrated Security=True"
$destinationConnection.Open()

$sqlCmd = New-Object System.Data.SqlClient.SqlCommand
$sqlCmd.Connection = $destinationConnection
$sqlCmd.CommandText = "TRUNCATE TABLE MyTable ;"
$sqlCmd.ExecuteNonQuery()

$destinationConnection.Close()

exit

In the main script, I use Start-Job command to call JobName.ps1.

$inst = "MySQLServer"
$db = "MyDB"

Invoke-Expression "Start-Job -ScriptBlock {powershell.exe -File
C:\Scripts\JobName.ps1 $inst $db}"

This background job never completes (always in Running state) even though
every statement inside JobName.ps1 is done. Oddly, if there are only same
simple statements in JobName.ps1, it will complete.

What is wrong? Thanks

[Oisin (x0n) Grehan [MVP]]

Background Jobs in powershell use their own runspace (in fact, it's
nigh-on an isolated process.) You don't have access to variables in
other scopes - the job must be self contained. You are trying to
reference $inst and $db inside the scriptblock ; I say "trying"
because it's not even getting that far - because you have the whole
thing wrapped in a double-quoted string, the variables are expanded
inside the expression before it's getting invoked. Replace invoke-
expression with write-host to see what you're actually passing to
invoke-expression.

-Oisin

[Ben]

Oisin,

Variable values did pass through into JobName.ps1 and the scripts was
successfully executed. The problem is the background powershell.exe job does
not set State = "Completed" even after the scripts is done. If JobName.ps1
only contains write-host $inst, $db, the background job State will set to
"Completed".

Any ideals to fix it? Thanks,

> .
>

[stej]

After a quick look - you probably need to pass in arguments like this
start-job -scripblock {param($db0,$in0) write-host $db0 $in0 } -
argumentList $db,$inst

[Oisin (x0n) Grehan [MVP]]

> > .- Hide quoted text -
>
> - Show quoted text -

Ok, I see what you mean - your variables were strings - this is the
only case where this would work. If they were live objects, you'd be
in trouble.

-Oisin

[Ben]

This must be a bug. The same code runs fine on W2003 server.

Thank you, Oisin and stej.