I have a SQL Server job which runs some Powershell code, but although when running the Powershell script in a PS editor on the machine it runs successfully, when running the SQL Agent Job with the same code it fails.
And the reason it fails is because it doesn't manage to add a few snap-ins, like the one below or include some assemblies.
Add-PSSnapin SqlServerCmdletSnapin100
This leads me to believe that SQL Server has a internal version of Powershell installed with which it runs the Powershell jobs. And that specific internal version of Powershell is older and does not have the required assemblies / snap-ins and thus fails.
Now, is there any way I can use the Powershell version installed on my machine and not the one that's built into SQL Server?
I've looked over "Proxies" in SQL Server but I don't think they're what I'm looking for.
Any help is appreciated!
Best Answer
As requested, here is an example of the process. The step should look like this, though
Job Step Command Set the step to be a cmdexec job and then run powershell through that. It lets you use the latest version of powershell as well as configure the powershell environment for that user (if you want). The step command will look like the one below, complete with arguments for the example. Remember to put quotes around strings that contain spaces.
Powershell.exe -noprofile -executionpolicy bypass -file "C:\Scripts\DailyRestoreScript.ps1" -Arguments -SourceSQLInstance 'localhost\jdf2016' -SourceDatabaseName 'AdventureWorks' -TargetSQLInstance 'localhost\jdf2016' -TargetDatabaseName 'AdventureWorks2'
Complete Job