2 Issues with PowerShell Steps in SQL Agent

Both of the issues I’m going to discuss have been documented before but for my own benefit I’m going to put them on my blog as they are bound to come up again one day..

Unable to start execution of step 2 (reason: line(50): Syntax error).

The PowerShell Script works fine when run manually but fails when set up in a Agent Step with the above error. How can there be a syntax error?

The line in my code that caused this failure was:

$AgentJob = $($Job[0])

And the chances are your line of code might also be using PowerShell Sub-Expression Operators. The problem is that SQL Server interprets ‘$()’ as a T-SQL User Token so when the Agent gets to this line of code, it thinks it’s T-SQL and Not PowerShell. There’s very little documentation on this but this answer on DBA Stack Exchange is the best explanation I found.

The workaround is simply not to use Sub-expression statements in your script! Or you could save the script as a Powershell file, save it to an accessible share and run the script using a CMD.exe step instead.

With my script, my sub-expression statement was a list of T-SQL query results so what I did was put the results into a PowerShell array and I was able to iterate through the results using this instead and  I didn’t get an error

So replace this:

$Jobs = New-Object System.Data.Datatable
$SqlAdapter.Fill($Jobs)

foreach ($Job in $Jobs.Tables[0].Rows)
{ 
$AgentJob = $($Job[0])
Do-Stuff $AgentJob
}

With This:

$Jobs = New-Object System.Data.Datatable
$SqlAdapter.Fill($Jobs)
$JobArray = @($jobs)
foreach ($Job in $Jobarray.name)
{ 
$AgentJob = $Job
Do-Stuff $AgentJob
}

My PowerShell Agent Step just runs Continuously and Never Finishes

Now this was a strange one. There was nothing in the logs and activity monitor just showed that the step was running, and running, and running. So I checked for blocking/locking the job step in MSDB but nothing was apparently running.

I looked through my script and the only thing that stuck out was that I was using DBATools cmdlets. Maybe the module wasn’t available to my proxy account I was using? So I went and checked on the server that the module was available to all (it should be in C:\Windows\System32\WindowsPowerShell\v1.0\Modules) but the module was there.

In the end I found the answer on another DBA Stack Exchange Question (answered by Shawn Melton who is a major contributor to the DBATools module). The problem is with the PowerShell subsystem within SQL Agent. Basically it struggles running cmdlets from other modules because it is trying to run the script in the context of the SQLPS module.

The workaround I used (and recommended by Shawn) is to save the script as a Powershell file, save it to an accessible share and run the script using a CMD.exe step instead. I use the below command.

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe "& 'E:\PowerShell\PowerShellScript.ps1'"

Be sure to read the  articles below as there’s waaaay more you need to be aware of when running PowerShell scripts within SQL Agent Job steps.

Further Reading:

Scheduling PowerShell Tasks with SQL Agent

Running PowerShell in an SQL Agent Job