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

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

 receive-job -Job $job

One thought on “Getting the bloody Sql Server provider to work with Start-Job

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.