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?

Chad Miller · Accepted Answer · 2014-01-22 11:57:58Z

8

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

answered Jan 22, 2014 at 11:57

Chad Miller

42.4k4 gold badges33 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

defect833 Over a year ago

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.

Chad Miller Over a year ago

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

defect833 Over a year ago

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.

Chad Miller Over a year ago

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.

Arluin Over a year ago

@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.

Community · Accepted Answer · 2017-05-23 12:08:28Z

0

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

edited May 23, 2017 at 12:08

CommunityBot

11 silver badge

answered Jan 22, 2014 at 6:43

Nick.Mc

19.4k6 gold badges67 silver badges102 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related