SQLPS.exe is a mini-shell which was made before the SQLPS module was created and is compiled with a limited sub-set of PowerShell version 2.0 cmdlets. Powershell.exe is no longer even being called, instead SQLPS.exe is being called. What you will notice is that SQL Server 2008 R2 has taken all of our system configurations out of our hands. On a SQL Server 2008 R2 instance I have created a job with a single step which is configured as follows. Test 1: SQL Server 2008 R2 using PowerShell job step NOTE: The powershell.exe is being used, we are using PowerShell version 4.0, and the system execution policy is set to Unrestricted.
“Execution Policy: $executionPolicy” | Out-File $filePath -Appendįor setup purposes, this is what my PowerShell environment looks like outside of SQL Server. “Version: $version” | Out-File $filePath -Append We will also test different job step types. We will then run this script through the SQL Agent on a SQL Server 2008 R2, 2012, and 2014 instance.
First we’ll create a simple PowerShell script which will output the executable path and script path, PowerShell version and execution policy to a log file.
