Here we expect no change from test #2 & #4 because both are essentially using cmd.exe and SQL Server versions do not affect cmd.exe at all.Īs expected, this method, in SQL Server 2014, grants you full control of the version and settings at the system level. In this test we will be using the Operating System (CmdExec) method once again, just in SQL Server 2014. Test 6: SQL Server 2014 using Operating System job step
We wouldn’t have to add initialization to our scripts such as Import-Module SQLPS -DisableNameChecking. It is my guess that this was the chosen method because SQLPS.exe guarantees that all of the appropriate assemblies are available for interaction with SQL Server. It appears that the SQLPS.exe mini-shell was updated with SQL Server 2014 to use the newer PowerShell version rather than start using PowerShell.exe. What is interesting here is that SQLPS.exe is still used but the PowerShell Version is 4.0. Once again we will redo test #1 but, this time, it will run on a SQL Server 2014 instance. Test 5: SQL Server 2014 using PowerShell job step Here we expect no change from test #2 because both are essentially using cmd.exe and SQL Server versions do not affect cmd.exe at all.Īs expected, this method, in SQL Server 2012, grants you full control of the version and settings at the system level. In this test we will be using the Operating System (CmdExec) method again, just in SQL Server 2012. Test 4: SQL Server 2012 using Operating System job step This appears to not be true, I wonder if SQL Server 2014 implemented that feature or not. I was having a discussion with Allen White ( b | t) several months ago and our belief was that SQL Server 2012 would use the native PowerShell.exe rather than SQLPS.exe. We expect to see a behavior change because, with version 2012, the SQLPS module was created which removed the need of the mini-shell for assembly packaging. Now we will redo test #1 but, this time, it will run on a SQL Server 2012 instance. Test 3: SQL Server 2012 using PowerShell job step Also, it is the only way for you to use the system defined setting for execution policy. What is important to note with this configuration is that, in 2008 R2, this is the only way that you can use a version of PowerShell other than 2.0.
In this test we will experiment with the Operating System (CmdExec) job step with no other changes except for modifying the script for cmd.exe syntax.Īs you can see below, by using the Operating System job step, we have taken back control over the system settings and ran the script in our currently install version of PowerShell. Test 2: SQL Server 2008 R2 using Operating System job step The mini-shell is also run in RemoteSigned mode, regardless of the system configured value. 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.