At the Ohio North SQL Server User Group meeting last night, Eric Blinn (@SQL2TheSequel) had a great question about how to properly handle PowerShell errors with SQL Server Agent Jobs. He said he’d seen some of his jobs succeed even though errors were present in the script output. I’ve been victim to error handling issues in the past with enterprise scheduling tools so I figured I would dig in to see what I could figure out. Below is a table of different test cases I ran through and the outcome of each one.
| Step Type | Step Command | Script Contents | Outcome | |
|---|---|---|---|---|
| 1 | PowerShell | throw ‘test’ | N/A | Error |
| 2 | PowerShell | Get-ChildItem -Path ‘C:\DoesNotExist’ | N/A | Error |
| 3 | PowerShell | & test.ps1 | throw ‘test | Error |
| 4 | PowerShell | & test.ps1 | Get-ChildItem -Path ‘C:\DoesNotExist’ | Error |
| 5 | CmdExec | powershell.exe -File test.ps1 | throw ‘test’ | Error |
| 6 | CmdExec | powershell.exe -File test.ps1 | Get-ChildItem -Path ‘C:\DoesNotExist’ | Success, but error in output |
| 7 | CmdExec | powershell.exe -File test.ps1 | Get-ChildItem -Path’C:\DoesNotExist’; if ( $? -eq $false ) { exit 1 } | Error |
| 8 | CmdExec | powershell.exe -Command “& test.ps1” | throw ‘test’ | Error |
| 9 | CmdExec | powershell.exe -Command “& test.ps1” | Get-ChildItem -Path ‘C:\DoesNotExist’ | Success, but error in output |
| 10 | CmdExec | powershell.exe -Command “& test.ps1” | Get-ChildItem -Path’C:\DoesNotExist’; if ( $? -eq $false ) { exit 1 } | Error |
Conclusion
Errors that go to the PowerShell error stream via Write-Error are considered non-terminating errors so no exit code gets set. In order to get a terminating error, you must throw an exception or specify an exit code. This explains why test cases 5, 7, 8, and 10 return errors – because we are using throw or checking for an error and specifying an exit code. Test cases 6 and 9 incorrectly return a 0 exit code because the errors are considered non-terminating.
If I were to be scheduling PowerShell through the SQL Server Agent, I would definitely recommend using a PowerShell step type.
