SQL Agent job not failing when Powershell script errors

First time using Powershell. And I'm trying to execute via SQL Agent job. So I don't know if I have something wrong in my Powershell script or my SQL Agent job or Both.

SQL Server 2008 R2
PowerShell V2.0

Have tried in Powershell the Trap and Try catch with a Throw and Exit 1. But for some reason the SQL Agent job doesn't fail.

In Powershell I have:
$ErrorActionPreference = "Stop"

-ea stop

In Sql agent job I have:
Command:
powershell.exe -File "\\mypowershell\PSscript.ps1" -param1 "\myfilelocation" -param2 "\mydestination" -ErrorAction Stop
Advanced:
On failure action: Quit the job reporting failure


Now when the Powershell script fails I want the sql agent job to fail.

What am I missing?

Thanks,
Sqlraider

I may be wrong (I am on the mobile trying to catch up before the week starts) but I think you would be better not handling the error including removing the error stop.

Is the job type CmdExec? If so wouldn't a failed PowerShell job still show as success since the CmdExec is just calling it and the call itself is succeeding?

What happens if you change the job type to PowerShell and call it?

Joie,
The type is:
Type: PowerShell
in the SQL Agent job and the command is powershell.exe


Gaz,

I removed all references to the Stop and error catching within the powershell script but the SQL job still reports a successful execution of the Step.


Maybe if I tell you what I'm trying to accomplish you'll be able to help me out.

I want to use PowerShell to Copy a File from one location to another using params ($source, $destination).
I want the SQL Agent job to Execute the PowerShell script with the parameters.

To test for error's I'm passing an incorrect $source and/or incorrect $destination.
I want the SQL job Step to "Quit the job reporting failure".

Please let me know if you need more information.

Thanks,
Sqlraider

i use powershell + robocopy to copy files, and the format i use will return an error when it occurs; this ight help:

$SourceLocation = "D:\SQLBackups\";
$DestinationLocation = "\\gdc-bak-p01\SHARE\CRITICALBACKUPS\HOL\SQL-BACKUPS\HOL-SQL-CL01" ;
#/E copy directories even if empty
#/NFL no file logging in $CaptureOutput
#/NDL no directory logging in $CaptureOutput
#/NJH : No Job Header. 
#/NJS : No Job Summary.
#/R:0 retry 0 times on failure
#getting robocopy to work and double quoting the paths was a bitch. hardcoding is easier.

$CaptureOutput = robocopy  "D:\SQLBackups" "*.sqb" "\\gdc-bak-p01\SHARE\CRITICALBACKUPS\HOL\SQL-BACKUPS\HOL-SQL-CL01" /NFL /NDL /NJH /NJS /E /R:0 ;
#write-verbose -Message "param1 $CaptureOutput"  -verbose;
#check if there was an error
$Isfailure = $CaptureOutput -match "ERROR ";
if ($Isfailure)
{
throw("Failure in copying $SourceLocation files to $DestinationLocation. Details: $CaptureOutput");
}

Make sure you use a 'throw' command in your error traps.

I finally got this working and thought I'd post the solution.

In the SQL Agent job Step I changed the type to:
Type: Operating sytem (CmdExec)

And the Powershell script looks like this:
Param(
$source,
$destination
)

try {

Copy-Item $source -Destination $destination -ea stop
} catch {

Write-Error $_

[System.Environment]::Exit(1)
}

The Write-Error $_ is not necessary but it gives a more descriptive error message for someone not familiar with powershell (like me). The Exit(1) passes back to the SQL Agent job which fails if not 0.

Thanks everyone for the help it pushed me in the correct direction.

Thanks,
Sqlraider