Sql-server – Troubleshooting Powershell errors from SQL Agent job

powershellsql-server-2012sql-server-agent

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.

Best 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.

Related Question