PowerShell is an incredibly powerful scripting tool for managing Windows environments and it has become especially popular in the SQL Server community thanks to a rich library of cmdlets that support SQL Server operations.
The native Microsoft PowerShell SQL Server module contains cmdlets can be used to run queries and export data to files, among other simple tasks. There are 3rd party modules that can be installed to add more power to a PowerShell environment. The most popular of these is known as DBATools and is completely free! The details of the module including download instructions and cmdlet documentation can be found at https://dbatools.io/.
Way back in SQL Server 2008 Microsoft added a feature allowing SQL Server Agent jobs to call PowerShell scripts directly. This is a really great feature, but comes with a caveat. Most of the PowerShell cmdlets used by modern scripts will require PowerShell version 5, yet often the SQL Server Agent will attempt to execute them using PowerShell version 4 – leading to some ugly errors and a script that won’t execute. Nothing is more frustrating than writing a really useful PowerShell script, testing it, then attempting to execute it as part of a scheduled task and getting a series of unexpected errors.
This problem can be avoided by checking the version used by the Agent and using a workaround if the version being used is 4 or less.
To confirm the version called by the Agent natively, create a test job with one step of Type PowerShell and include this text, $psversiontable.psversion.major as the command. It does not need a schedule and should look something like the screenshot below. To make it easier to read the version go to the Advanced pane and check the “Include step output in history” checkbox.
Execute the job and check the job history. There will be a single digit number in the job step output. If it is a 5 then the Agent can be used natively. If the number is not 5 then the workaround should be tested. The workaround is to change the step type to Operating system and explicitly call the PowerShell engine from there. The job step should look like the screenshot below. Make the change and run the job again.
If the output is a 5 then this is the way that PowerShell scripts can be called from the Agent. Simply save a PowerShell script as a ps1 file on the target machine and execute it via an operating system command step by putting the complete path to the ps1 file in the double quotes after the word “powershell”.
On one instance I was working with I still got a 4 as my output even using the workaround. In this scenario I simply had to install the PowerShell 5 binaries in Windows and rerun the second version of the test job. The results of the 3 executions are below. The native command and workaround both returned a 4. After installing PowerShell 5 (which required a system reboot) the operating system method returned a 5 and I was able to schedule my PowerShell tasks.
