SQL Agent job not failing when Powershell script errors

Question

SQL Agent job not failing when Powershell script errors

Sqlraider

SSCertifiable

Points: 5441
More actions
February 20, 2015 at 3:04 pm

#294797

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gary Varga SSC Guru Points: 82166 More actions · Answer 1

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.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 2

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 Andrew
"Since 1982"

Sqlraider SSCertifiable Points: 5441 More actions · Answer 3

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

Lowell SSC Guru Points: 323518 More actions · Answer 4

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");

}

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ManicStar SSCoach Points: 17992 More actions · Answer 5

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

Sqlraider SSCertifiable Points: 5441 More actions · Answer 6

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

mihaipetcu84 44501 Newbie Points: 9 More actions · Answer 7

mihaipetcu84 44501

Newbie

Points: 9

October 19, 2018 at 6:02 am

#2010397

Thank you very much!