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.
Improve this question
3
  • Are you sure that you want to import .psd1 and NOT .psm1?
    – SqlWorldWide
    Commented Jun 14, 2017 at 17:26
  • I believe you are correct, however changing it to .psm1 still gives me the syntax error.
    – Geoff Dawdy
    Commented Jun 14, 2017 at 17:34
  • 1
    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
    – Scott Hodgin - Retired
    Commented Jun 14, 2017 at 17:37

3 Answers 3

Reset to default
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.

Improve this answer
2
  • In addition you can follow the steps at dbatools.io/agent to help you to set up the jobs correctly
    – SQLDBAWithABeard
    Commented Nov 22, 2017 at 17:46
  • 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
    – SQLDBAWithABeard
    Commented Nov 22, 2017 at 17:48
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.

Improve this answer
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

Improve this answer

Your Answer

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

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