4

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?

Paul Draper
  • 64,883
  • 37
  • 172
  • 246
defect833
  • 206
  • 5
  • 19

2 Answers2

7

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

Chad Miller
  • 32,809
  • 3
  • 24
  • 33
  • 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. – defect833 Jan 23 '14 at 02:50
  • 1
    Seems odd that you are call powershell.exe in sqlps job step? – Chad Miller Jan 24 '14 at 00:09
  • 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. – defect833 Jan 28 '14 at 01:12
  • 1
    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. – Chad Miller Jan 29 '14 at 00:02
  • 1
    @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. – Arluin Sep 11 '14 at 17:19
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

Community
  • 1
  • 1
Nick.McDermaid
  • 15,079
  • 5
  • 40
  • 67