0

PowerShell Errors with SQL Server Agent Jobs

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 TypeStep CommandScript ContentsOutcome
1PowerShellthrow ‘test’N/AError
2PowerShellGet-ChildItem -Path ‘C:\DoesNotExist’N/A Error
3PowerShell& test.ps1throw ‘testError
4PowerShell& test.ps1 Get-ChildItem -Path ‘C:\DoesNotExist’ Error
5CmdExecpowershell.exe -File test.ps1throw ‘test’Error
6CmdExecpowershell.exe -File test.ps1Get-ChildItem -Path ‘C:\DoesNotExist’Success, but error in output
7CmdExecpowershell.exe -File test.ps1Get-ChildItem -Path’C:\DoesNotExist’; if ( $? -eq $false ) { exit 1 } Error
8CmdExecpowershell.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.

Andrew Wickham

?‍?‍?‍? Dad ?‍? PowerSheller ? SQL Server DBA ?‍♂️ Marathoner ?‍♂️ Not Drew

Leave a Reply

Your email address will not be published. Required fields are marked *