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?

Uptotheminute

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

Olein

I changed my execution to try { powershell C:\scripts\export_timesheet_csv.ps1 -loc 3 -locid 102 -ErrorAction 'Stop' } catch { throw $_ } and inside the script, immediately after receiving the command line params, I am throwing an exception. I get the same scenario where the job reports a success but in the history notes that an exception happened. – Uptotheminute

Seems odd that you are call powershell.exe in sqlps job step? – Olein

I removed the powershell.exe call from the front of the execution statement and it started reporting errors correctly. It seems like it was making a powershell call within a powershell (SQL job) call and the exceptions were not being passed all the way through. – Uptotheminute

Error handling with Powershell at first takes a little getting used to. The try/catch blocks requires you to set error actions and also doesn't work with legacy (exe's) type commands. Traditional command-line apps set exit codes. – Olein

@ChadMiller I found I had to call powershell.exe in sqlps job step because the powershell version in SQL is 2.0 and I wanted to use invoke-command which is a 3.0 cmdlet. – Sane

Answer 2

Is this your answer:

Why are my powershell exit codes always "0"?

a lot of things say using -file is the issue but it looks like you aren't

Start