This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
Troubleshooting Powershell errors from SQL Agent job
Question
I have a powershell script that I am trying to add in to a sql agent job, but the job step is failing with the following error
A job step received an error at line 1 in a PowerShell script. The corresponding line is ‘import-module SQLPS -DisableNameChecking’. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘Could not load file or assembly ‘file:///C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\SQLPS\Microsoft.SqlServer.Management.PSSnapins.dll’ or one of its dependencies. This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded.
I assumed that the error was being caused by differences in the module path, so I explicitly set the path via $env:PSModulePath to match the path of my powershell session where the code runs fine. I am pretty new to powershell, so any help you could provide would be appreciated
I am just doing some testing right now, so everything is running locally on my desktop. I am running SQL Server 2012.
asked 2017-04-19 by Patrick
Answer
When you run a SQL Agent job step with the “PowerShell” type, you aren’t actually running in (full) PowerShell. This job step type actually runs under the sqlps.exe minishell, which is based on PowerShell 1.0 and an extremely limited number of cmdlets.
Practically speaking, the PowerShell job step type is not very useful. Instead, I recommend using an “Operating System/CMDExec” job step, and calling PowerShell.exe from the command line.
Using the cmdexec job step type, your job step would look something like this:
PowerShell.exe "D:\Scripts\MyScript.ps1" -NonInteractive
By calling PowerShell.exe, you get the full/current PowerShell shell (instead of the mini shell), and you get to use the full/current SQL Server PowerShell module. This is particularly important if you are using SQL Server 2016+, where the SQLSERVER module has been enhanced with a much longer list of cmdlets.
answered 2017-04-19 by Andy Mallon