SQL Agent Job Wrapper Part 3 - Handing the Errors in the Wrapper Script

This is the 3rd installment in a small series of blog posts on how to create a PowerShell wrapper for running SQL Server Agent Jobs. Here are the links to the 2 previous posts and I recommend reading them because all of the posts build on the previous one.

Creating a SQL Agent Job Wrapper with PowerShell and SMO - Part 1 SQL Agent Job Wrapper Part 2 – Adding Error Generation to the Cmdlet

function Start-SQLAgentJob
     NAME: Start-SQLAgentJob
     AUTHOR: Chris Sommer
     Version: 1.0
     CREATED: 2015-05-09

     Start a SQL Server Agent job.

     Start a SQL Agent job and wait for its completion. This function relies on the SQL Agent to be up and running.

     .Parameter SQLServer
     SQL Server Name

     .Parameter JobName
     SQL Agent job name

     Start-SQLAgentJob -SQLServer "localhost" -JobName "TestJob"
    param (
        [Parameter(Mandatory=$true)][string]$SQLServer ,
    # Load the SQLPS module
    Push-Location; Import-Module SQLPS -DisableNameChecking; Pop-Location

    # ConnectionContext.Connect tests the connection to the SQLServer. This will throw an error if connection fails.
    $ServerObj = New-Object Microsoft.SqlServer.Management.Smo.Server($SQLServer)

    # New check to ensure the JobName exists on this SQL Server
    if ( ($ServerObj.JobServer.Jobs | Where-Object {$_.Name -eq $JobName} | Select-Object -ExpandProperty Name) -ne $JobName ) {
        throw ("Job $JobName does not exist")

    $JobObj = $ServerObj.JobServer.Jobs | Where-Object {$_.Name -eq $JobName}

    # If the job is and enabled and not currently executing start it
    if ($JobObj.IsEnabled -and $JobObj.CurrentRunStatus -ne "Executing") {

    # Wait until the job completes. Check every second.
    do {
        Start-Sleep -Seconds 1
        # You have to run the refresh method to reread the status
    } While ($JobObj.CurrentRunStatus -eq "Executing")

    # Get the run duration by adding all of the step durations
    $RunDuration = 0
    foreach($JobStep in $JobObj.JobSteps)     {
        $RunDuration += $JobStep.LastRunDuration

    # If the job succeeded return the job object, otherwise throw an error.
    if ($JobObj.LastRunOutcome -eq "Succeeded") {
        $JobObj | select Name,CurrentRunStatus,LastRunOutcome,LastRunDate,@{Name="LastRunDurationSeconds";Expression={$RunDuration}}
    } else {
        $JobResult = $JobObj.LastRunOutcome
        throw ("Job '$JobName' LastRunOutcome = '$JobResult'")
Set-StrictMode -Version 2.0
$ErrorActionPreference = "Stop"

try {
    # Setup pathing and environment based on the script location
    $Invocation = (Get-Variable MyInvocation -Scope 0).Value
    $ScriptLocation = Split-Path $Invocation.MyCommand.Path
    # Load the Start-SQLAgentJob cmdlet
    . "$ScriptLocation\Start-SQLAgentJob.ps1"
    # Set a couple variables for testing and call the cmdlet
    $SQLServer = "localhost\inst1"
    $JobName = "TestJob"
    Start-SQLAgentJob -SQLServer $SQLServer -JobName $JobName
} catch {
    throw $error[0]
    # Exit with error code of 1 on any failure

This version is quite a bit different than the simple Test-Wrapper script I have used in the previous 2 posts. I’ll try to highlight the main differences here.

Below are the results of a successful SQL Agent job, and a failed SQL Agent job. In the failed job you will see that the error message describes exactly what went wrong and because of the SetShouldExit it can be trapped by an external scheduler to indicate a failure.

See also