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

Here’s something I don’t do often enough to commit to memory, create your profile.  If you have anything you want to run when you fire up powershell (sql server & sqlpsx modules, etc…) you need first to create your profile.  You can do so with the following command:

New-Item -path $profile -type file -force

You can then edit your profile by typing in:

notepad $PROFILE