SQL Agent job not failing when Powershell script errors

  • Sqlraider

    SSCertifiable

    Points: 5038

    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

  • Gary Varga

    SSC Guru

    Points: 82166

    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: 27271

    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: 5038

    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: 323357

    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

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

  • Sqlraider

    SSCertifiable

    Points: 5038

    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

    Thank you very much!

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

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