How to run SQL Server Agent Powershell script with output and exit code

Question

I'm trying to run a PowerShell script as a SQL Server 2016 Agent job.

As my Powershell script runs, I'm generating several lines of output using "Write-Output". I'd like to save this output to the job history, and I only want the job to continue to the next step if the step running the PowerShell script completes with an exit code of 0.

I'm using the "PowerShell" step type in my agent job. The Command text looks like this..

# Does some stuff that eventually sets the $resultState variable...

Write-Output ("Job complete with result '" + $resultState + "'")

if($resultState -eq "SUCCESS") {
    [Environment]::Exit(0);
}
else {
    [Environment]::Exit(1);
}

Under the "Advanced" settings, "Include step output in history" is checked. If I remove the final "if" statement from the PowerShell script, then I can see the output in the history, but the job step is always successful and moves on to the next step. If I include the if/else statements, the job step fails if $resultState does not equal "SUCCESS" (which is what I want), but I don't see my output anymore in the history for the job step.

Any suggestions?

score 2 · Accepted Answer · answered Mar 26 '20 at 17:49

I worked around this by saving all of my output lines to a single variable, and using Write-Error with -ErrorAction Stop if my result wasn't what I wanted. This isn't quite what I was trying to do at first, because this doesn't use the exit codes, but SQL Agent will correctly detect if the job step succeeded or not, and my output can still show up in the job history because it will be included in the error message.

Updated code:

# Does some stuff that sets the $resultState and saves output lines to $output...

$output += "`r`nJob complete with result '$resultState'"

if($resultState -eq "SUCCESS") {
    Write-Output ($output)
}
else {
    Write-Error ("`r`n" + $output) -ErrorAction Stop
}

score 2 · Answer 2 · answered Mar 29 '22 at 09:28

I struggled with this. The logged output from the Sql Job Powershell steps is pretty useless. I found it better to use a CmdExex step instead that calls Powershell.exe.

In a Sql 2019 CmdExec job step you can just call your powershell script like this:

Powershell "F:\Temp\TestScript.ps1"

... and you'll get all the output (and you can log it to a file if you like). And if there's an error, the job stops properly.

In some earlier versions of SQL Server, Powershell errors would get logged but the job would continue to run (see here https://stackoverflow.com/a/53732722/22194 ), so you need to wrap your script in a try/catch to bubble up the error in a way SQL can deal with it:

Powershell.exe -command "try { & 'F:\Temp\TestScript.ps1'} catch { throw $_ }"

Note that if your script path has spaces in it you might get different problems, see here https://stackoverflow.com/a/45762288/22194

How to run SQL Server Agent Powershell script with output and exit code

2 Answers2