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?

codeulike · Accepted Answer · 2024-05-30 10:33:02Z

3

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

edited May 30, 2024 at 10:33

answered Mar 29, 2022 at 9:28

codeulike

23.2k29 gold badges128 silver badges172 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Duane Theriot · Accepted Answer · 2020-03-26 17:49:55Z

2

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
}

answered Mar 26, 2020 at 17:49

Duane Theriot

2,1851 gold badge13 silver badges16 bronze badges

Comments

Magnus Smith · Accepted Answer · 2024-04-19 14:00:04Z

I had a similar issue, and found Write-Error and exit did not return the message/code to SQL Agent and were not detected as failures!

According to https://stuart-moore.com/failing-a-powershell-sql-agent-step/ the answer is Throw, and here's my test Powershell code as part of a SQL Agent job step:

$numfiles = 5

#we hope there are no old files, so if some were found, alert the calling code
 if ($numfiles -gt 0) {
   Throw ($numfiles.ToString() + " old files found")
}

The job history isn't perfect but does include the information eventually:

Message A job step received an error at line 9 in a PowerShell script. The corresponding line is ' Throw ($numfiles.ToString() + " old files found") '. Correct the script and reschedule the job. The error information returned by PowerShell is: '5 old files found '

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related