HiveBrain v1.2.0
Get Started
← Back to all entries
debugsqlMinor

SQL Agent PowerShell Syntax Error

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
errorsqlpowershellagentsyntax

Problem

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.

Solution

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.

Code Snippets

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

Context

StackExchange Database Administrators Q#176288, answer score: 5

Revisions (0)

No revisions yet.