SQL server agent not reporting failure from uncaught exception in PowerShell script

Question

I have set up an SQL server agent job with a few steps that needs to be processed each week. One of which is a PowerShell script that will throw the following (uncaught) custom exception:

$endfile = Test-Path "C:\Temp\1_${loc}_${lastpayweek}.csv"
IF ($endfile -eq $true) 
{
   throw "target file already exists"
}

Now when this occurs, I can see in the server agent history that an exception was thrown but the step is still reported as a success. Without a failed, the server agent will not send out a notification email to let me know of the failure.

I am calling the PowerShell script like this:

powershell C:\scripts\rename_timesheet_export.ps1 -loc 3

Why is it reported as a success?

Answer 1

You need to set you $ErrorActionPreference to stop:

$ErrorActionPreference = "Stop"

By default this is set to Continue, so when Powershell is called from SQL Agent the job will continue on error unless you change the setting.

I have a few more tips on calling Powershell from SQL Agent here: http://blogs.technet.com/b/heyscriptingguy/archive/2013/05/06/10-tips-for-the-sql-server-powershell-scripter.aspx