SQL Agent PowerShell Syntax Error

Question

I am attempting to setup a PowerShell script within a SQL Agent job (SQL Server 2016). I'm using a third party module, DBATOOLS.psd1 which appears to be where I'm getting my syntax error. Currently my SQL Job has one line of code which is failing:

Import-Module e:\dbatools-master\dbatools-master\dbatools.psd1

Why is the job failing with the following error? This command works just fine when I run it through the PowerShell ISE.

Error Message: Unable to start execution of step 1 (reason: line(5): Syntax error). The step failed.

• SqlWorldWide: Are you sure that you want to import .psd1 and NOT .psm1?
• Geoff Dawdy: I believe you are correct, however changing it to .psm1 still gives me the syntax error.
• Scott Hodgin - Retired: no - I do not believe you ever want to import the psm1 module - see Properly importing the module at this link regarding the dangers of importing the psm1 module - dbatools.io/install

Answer 1

If you are running this as a SQL Server Agent job step you will need to use the Operating system (CmdExec) step type, not a PowerShell type.

Within the CmdExec it would be best to create a file on the machine with all of the steps that you require and then call that.

For example create a file C:\PowerShellScripts\MyScript.ps1 which contains...

Import-Module e:\dbatools-master\dbatools-master\dbatools.psd1
Copy-SqlLogin -Source ServerA -Destination ServerB

And then call that script using powershell.exe "& 'C:\PowerShellScripts\MyScript.ps1'" in the CmdExec job step.

• SQLDBAWithABeard: In addition you can follow the steps at dbatools.io/agent to help you to set up the jobs correctly
• SQLDBAWithABeard: You also need to ensure that the module is available to the SQL Agent (or proxy) user. You can achieve this by saving the module using Save-Module dbatools -Path PATHTOFOLDER and copying it to Program Files\WindowsPowerShell\Modules This will enable it to automatically import when required

Answer 2

To expand the reason you can't generally use the PowerShell step with another module is the SQL Server provider. It does not play nice and there is a connect item out to get MS to fix this.

If you add $erroractionpreference = 'stop' to the your job step you will see the full output of why the command is erring:

Executed as user: NT Service\SQLSERVERAGENT. A job step received an error at line 92 in a PowerShell script. The corresponding line is 'if (Get-Command TabExpansionPlusPlus\Register-ArgumentCompleter -ErrorAction Ignore) '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'SQL Server PowerShell provider error: Path SQLSERVER:\TabExpansionPlusPlus does not exist. Please specify a valid path. '. Process Exit Code -1. The step failed.

The provider is the issue in most cases as it does not know how to interpret some characters/commands. If the PowerShell step type put you within the context of the PowerShell.exe host, our life would be so much easier and awesome things could happen.

Answer 3

Interesting, Doesn't work for me in my lab, but I get a different error. I've been using Powershell in our SQL2014 environment, but I ran into problems, I think related to the PS provider in 2014, and I've been using the cmdexec command to fire up powershell scripts.

My SQL Agent step looks like this in 2016, and it worked: enter image description here