How to show error in PowerShell step called from SQL Server 2008 R2 job?
Citing Ed Wilson's 10th tip "10. Handle Windows Powershell errors in SQL Server Agent jobs" from "10 Tips for the SQL Server PowerShell Scripter":
10. Handle Windows Powershell errors in SQL Server Agent jobs
By default the ErrorActionPreference is set to Continue, and this has implications on how errors bubble up to the SQL Server Job Server. If you run a Windows PowerShell command as a SQL Server Agent job and there are no syntax errors yet, the command produces an error (for example, attempting to get operating system information from an unavailable server). The SQL Server Agent job will report success. If you want an error condition to halt execution of a SQL Server Agent job or to produce an error, you'll need to add some error handling. You can set up a SQL Server Agent job with Windows PowerShell Job Step as follows:
get-wmiobject Win32_OperatingSystem -ComputerName 'nothere'
The job will run successfully, but if you run it directly in Windows PowerShell, you'll see:
get-wmiobject Win32_OperatingSystem -ComputerName 'nothere'get-wmiobject : The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)At line:1 char:1 + get-wmiobject Win32_OperatingSystem -ComputerName 'nothere'
To bubble up Windows PowerShell errors to SQL Server Agent, you'll need to do one of the following:
A. Set your $ErrorActionPreference = "Stop"
$erroractionpreference = "Stop" get-wmiobject Win32_OperatingSystem -ComputerName 'nothere'
B. Set ErrorAction at the cmdlet-level (more granular)
get-wmiobject Win32_OperatingSystem -ComputerName 'nothere' -ErrorAction 'Stop'
C. Use Try/Catch with ErrorActionPreference or ErrorAction
try { get-wmiobject Win32_OperatingSystem -ComputerName 'nothere' -ErrorAction 'Stop' } catch { throw "Something went wrong" #or rethrow error #throw $_ #or throw an error no message #throw }
D. Continue, and fail the SQL Server Agent job
Let's say you have a collection of computers and you want to continue on the error, but you also want to fail the job. In this case you can make use of the ErrorVariable:
#Note the -ErrorVariable parameter takes a variable name without the $ prefix. get-wmiobject Win32_OperatingSystem -ComputerName 'localhost','nothere','Win7boot' -ErrorVariable myError if ($myError) { throw ("$myError") }
I add a line to Write-Error with ErrorAction set to stop if I want a Powershell job step to return an error. It's just the one line.
Write-Error "Job Failure" -EA Stop
If you are using a "CmdExec" step instead of a "PowerShell" step (which you should, due to PowerShell versioning reasons), the following syntax has worked for me:
powershell.exe -command "try { & 'D:\BadScript.ps1'} catch { throw $_ }"
(from Joel Greijer).