Using PowerShell Modules from Agent Job Issues
If you’ve tried to use PowerShell in a SQL Agent job, you know its not a nice experience. adding needing specific modules to load as well gives more headaches! you may be used to seeing errors like:
Executed as user: DOMAIN\me. Import-Module :
The specified module ‘RedGate.InstantClone.PowerShell’ was not loaded because no valid module file was found in any module directory.
At U:\InstantClone\Start-Clones.ps1:2 char:1
+ Import-Module -Name RedGate.InstantClone.PowerShell +
+ CategoryInfo :
ResourceUnavailable: (RedGate.InstantClone.Power Shell:String) [Import-Module], FileNotFoundException
+ FullyQualifiedErrorId :
Modules_ModuleNotFound,Microsoft.PowerShell.Comm ands.ImportModuleCommandRestore-InstantClone :
The term ‘Restore-InstantClone’ 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. At U:\InstantClone\Start-Clones.ps1:3 char:1 + Restore-InstantClone -Verbose + ~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : ObjectNotFound: (Restore-InstantClone:String)  , CommandNotFoundException + FullyQualifiedErrorId : CommandNotFoundException. Process Exit Code 0. The step succeeded.
Using PowerShell in SQL Server Agent jobs you have a couple of options If you use the drop down on Type to use PowerShell (like below) you get a broken down version of PowerShell, depending on the version of SQL Server depends on the version of PowerShell – I wrote about that before here
If you want to run the full version / latest version you have installed. you need to call PowerShell from either xp_cmdshell or running a SQL Agent using the
In the CmdExec type you then need to call Powershell like below:
PowerShell -noprofile "C:\temp\MyPowerShellScript.ps1"
Now you may think, awesome job done.. but sadly no, after doing a little playing around i found that even doing this, doesnt get you everything you might normally have when you open PowerShell ISE and that is modules!
My issue was around loading the RedGate Instance Clone module. If i run the below in PowerShell ISE you get a list all of all the folders that PowerShell will load modules from:
which brings back a bunch of different locations.
We can compare that to SQL Server via the below (Note that you need to enable xp_cmdshell
EXEC xp_cmdshell 'powershell -noprofile "$env:PSModulePath"'
which only returns:
BIG NOTE HERE! Ive trid this on a couple of different machines and sometimes i do get the module paths, sometimes i dont at present i dont know why its different on different machines!
The workaround, where it is happening is of course to add the module path to $env:PSModulePath. which i did via adding the below to the top of my PowerShell script that im calling:
## Load Module path! its not loading by default in calls from powershell $env:PSModulePath=$env:PSModulePath + ";" + "C:\Program Files (x86)\Red Gate\Instant Clone Preview\" ## Load module and run functions now..