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:

 

Message

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

PowerShellinSQLAgent

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

PowerShellinSQLAgent02

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:

$env:PSModulePath

which brings back a bunch of different locations.

PowerShellinSQLAgent03

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:

PowerShellinSQLAgent04

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.. 

 

Advertisements