3

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.

Improve this question

3 Answers 3

Reset to default
4

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.

Improve this answer
1
  • This is what I ended up doing. There was some added complexity because of having to use multiple AD accounts, one for the remote servers the poweshell script was querying, another for the local machine access (path, profile, etc). Windows credential manager came through for that though.
    – Patrick
    May 10, 2017 at 20:34
2

Check out this blog post as well. http://www.sqlhammer.com/running-powershell-in-a-sql-agent-job/

Powershell and SQL Agent Jobs can have some issues upfront. This blog post explains them and how to get around them. Essentially, the module loaded by SQL is different than the module loaded by your script.

Improve this answer
1
  • I changed job and set it up as an Operating system step, and the error went away, but now I am getting different errors. Looks like the job doesn't like something buried in the inside script. Add-Type:(0):Source file 'C:\Windows\TEMP\a2gpsrih.0.cs' could not be found (1) :using System; At C:\Users\usapxm20\Documents\WindowsPowerShell\Modules\NetShell\NetShell.psm1:5 char:1 + Add-Type @" + CategoryInfo: InvalidData: (error CS2001: S...ld not be found: CompilerError) [Add-Type], Exception + FullyQualifiedErrorId : SOURCE_CODE_ERROR,Microsoft.PowerShell.Commands. AddTypeCommand
    – Patrick
    Apr 19, 2017 at 14:26
-1

Remove the import-module from the job, SQL Server already starts up with SQLPS loaded, in 2012 it loads the 2.0 version of powershell so your scripts might need more adjustments.

Improve this answer
2
  • ok. I don't really want to get into making changes to the script that is loading the SQLPS module, looks like I will just have to find a different way to do that I want. The script I wrote is a to automatically run some other powershell scripts, and manipulated the output. Thanks for the quick reply though
    – Patrick
    Apr 19, 2017 at 14:01
  • Another option would be to run it as a CmdExec job step. See the section on running it as CmdExec here (learn.microsoft.com/en-us/sql/relational-databases/scripting/…)
    – MrTCS
    Apr 19, 2017 at 14:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.