Running Powershell SQL Agent Jobs

Running a PowerShell script from a SQL Agent job is a perfectly fine way of scheduling it. However, you can run into a few issues. One of the potentially most serious is that failing jobs can report themselves as succeeding.

Lets create two very simple PowerShell scripts as an example. If you save the snippet below as good_date.ps1.

$Date = Get-Date
$Date.toString("yyyyMMdd")

Now you save this snippet as bad_date.ps1.

$Date = Get-Date
$Date.toBling("yyyyMMdd")

Once you save them, you can run via a command prompt.

powershell.exe good_date.ps1
powershell.exe bad_date.ps1

What you will see is something like this:

c:\Stuff>powershell.exe c:\Stuff\good_date.ps1
20191112

c:\Stuff>powershell.exe c:\Stuff\bad_date.ps1
Method invocation failed because [System.DateTime] does not contain a method
named 'toBling'.
At C:\Stuff\bad_date.ps1:2 char:1
+ $Date.toBling("yyyyMMdd")
+ ~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : MethodNotFound

That is what we expected to see – there is an obvious syntax error in the second script, and PowerShell.exe let’s me know in it’s usual clear concise error message.

Now I will run the second script from a SQL Agent job. It will be a simple one step job, using the “Operating System (CmdExec)” job type, running the same exact command that failed above.

When we run the SQL Agent job, it actually reports that it succeeded. Why would it say that, when we know that it failed? Even if we look at the job history, it has the same ugly error message.

The reason it shows as failed makes sense if you think about it – we asked the SQL Agent service to run powershell – it did. That is “success”. The SQL Agent job works within the scope of cmd.exe.

So how do we get that scope to realize that the job failed? I need the SQL Agent job to receive an error level code other than the default of 0. I do this by wrapping the PowerShell code in a try..catch block. In the catch I return an exit code that is different than 0. The last part we need is to return that error level to the SQL Agent service. For that, the actual command run by the SQL Agent job needs a second statement to return the error, %ERRORLEVEL%.

In order to return an error level code, we wrap the prior script in a try\catch block:

param($code=99)

try {
$Date = Get-Date
$Date.toBling("yyyyMMdd")
}
catch {
    $ScriptName = $MyInvocation.ScriptName
    $msg = $_.Exception.Message
    Write-Error "Error executing powershell script $ScriptName '"
    exit $code
}

Next, we modify the SQL Agent job to return the error level:

Now when we run the job, it returns a failed status as we would have hoped.