SQL Job not failing when Powershell script runs and Fails

  • schwizla

    SSCrazy

    Points: 2668

    I have a sql job step that runs a powershell script using XP_cmdshell as follows

    EXEC xp_cmdshell 'powershell.exe %Deployment_root%apps\Systems\sql\DR\ScriptSQLServerObjects.ps1 "gssqlbackup"'

    However, if the script fails the job finishes successfully. I want the SQL Job to fail when the powershell script fails and also update the code below so that it outputs the error to a log file

    The code in my script "ScriptSQLServerObjects.ps1" is as follows. The first part loads the SQLPS provider (obtained from MSDN) and the second part scripts every Job on the SQL Server for Backup purposes

    [PowerShell Code BLock Start]

    # Pass the Backup Share Location into the script i.e ScriptsSQLServerObjects "$BackupShare"

    param ([string]$BackupShare)

    # Add the SQL Server Provider.

    $ErrorActionPreference = "Stop"

    $sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"

    if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")

    {

    throw "SQL Server Provider for Windows PowerShell is not installed."

    }

    else

    {

    $item = Get-ItemProperty $sqlpsreg

    $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)

    }

    #

    # Set mandatory variables for the SQL Server provider

    #

    Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0

    Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30

    Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false

    Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000

    #

    # Load the snapins, type data, format data

    #

    Push-Location

    cd $sqlpsPath

    Add-PSSnapin SqlServerCmdletSnapin100

    Add-PSSnapin SqlServerProviderSnapin100

    Update-TypeData -PrependPath SQLProvider.Types.ps1xml

    update-FormatData -prependpath SQLProvider.Format.ps1xml

    Pop-Location

    # Set the Server Name and Default starting location

    $SName = 'L3PSQL71'

    Set-Location C:

    # Load the Dot Net Assembly For SQL Server SMO. Out-Null indicates no output of the GAC and Version

    [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

    # Assign the Scripting Options to a variable

    $so = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions

    # Set the Input and Output Location Variables

    $JobsOutputLocation = '\\' + $BackupShare + '\' + $SName + 'Failover\OtherBits\' + $SName + '_SQLJob - '

    $SQLServerJobsPath = 'SQLSERVER:\sql\' + $SName + '\default\JobServer\Jobs'

    # Script the Jobs

    $so.IncludeDatabaseContext = 1

    gci -path $SQLServerJobsPath | %{$Job = $_.Name; $_.script($so) | out-file -filepath $JobsOutputLocation$Job.sql}

    [PowerShell Code BLock END]

    The $ErrorActionPreference is set to stop. So even though I can get it to fail, this failure does not cause the SQL job to fail. How can I trap the failure and also outut the failure details to a log file?

    Thanks in advance

  • MG-148046

    SSCrazy Eights

    Points: 9945

    You might try this

    EXEC xp_cmdshell 'powershell.exe %Deployment_root%apps\Systems\sql\DR\ScriptSQLServerObjects.ps1 "gssqlbackup"'; exit $LASTEXITCODE

    MG

    "There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
    Tony Hoare

    "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

  • Shawn Melton

    SSC-Insane

    Points: 24675

    dibbydibby (9/9/2010)

    The $ErrorActionPreference is set to stop. So even though I can get it to fail, this failure does not cause the SQL job to fail. How can I trap the failure and also outut the failure details to a log file?

    Caveat: I'm learning this PowerShell stuff as I go 😀

    From what I have read when you use $ErrorActionPreference = "Stop", you can then use the Trap command before your command you think it might be erroring on. Setting it to Stop will allow the cmdlet to report the error to the trap so you can capture it. Read a little further and you can actually capture the message and write it out.

    # Script the Jobs

    $so.IncludeDatabaseContext = 1

    Trap {Write-Host -force Red -back White $_.Exception.Message; Continue};

    gci -path $SQLServerJobsPath | %{$Job = $_.Name; $_.script($so) | out-file -filepath $JobsOutputLocation$Job.sql}

    [PowerShell Code BLock END]

    The Continue is treated like the ErrorAction "SilentlyContinue". Note I got all this info from here:

    http://powershell.com/cs/blogs/ebook/[/url], see Chapter 11.

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

  • MG-148046

    SSCrazy Eights

    Points: 9945

    I second your caveat :-D:-D

    Here is a link to a pretty good explanation of error handling for PoSh:

    http://huddledmasses.org/trap-exception-in-powershell/

    MG

    "There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
    Tony Hoare

    "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

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

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