6

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.
MDCCL
  • 8,530
  • 3
  • 32
  • 63
Geoff Dawdy
  • 1,143
  • 7
  • 23
  • 53

3 Answers3

5

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.

Nic
  • 4,063
  • 1
  • 16
  • 22
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.

1

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

sqlpadawan
  • 205
  • 2
  • 8