Getting the bloody Sql Server provider to work with Start-Job

I was trying to use the Invoke-Sqlcmd in a script-block in Powershell to kick off a job, but I kept running into the following error:

“The term ‘Invoke-Sqlcmd’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if  a path was included, verify that the path is correct and try again.”

When you use the Start-Job cmd to start a job, you have to re-initialize the session with the snap-ins or modules that said script block references.  I had assumed it would more or less inherit the current profile that the script was run from, which is apparently not the case.  Took me a bit to get it all figured, but you have to pass in what you want loaded to the Start-Job in the –InitializationScript parameter.  Here’s an example:

$scriptBlock = {
	Invoke-Sqlcmd -Database master -Query "SELECT * FROM dbo.TestTable"
}

$init = {
	Add-PSSnapin SqlServerCmdletSnapin100
	Add-PSSnapin SqlServerProviderSnapin100
}

cls
sl SQLSERVER:\SQL\localhost\DEFAULT\Databases\master
$job = Start-Job -InitializationScript $init -ScriptBlock $scriptBlock

 receive-job -Job $job