Start a SQL Agent Job with the .NET Framework from PowerShell

As of this writing, the most recent version of the SQLServer PowerShell module (which installs as part of SQL Server Management Studio) includes cmdlets for retrieving information about SQL agent jobs, but no cmdlets for starting them.

1Get-Command -Module SQLServer -Name *job*

startsqljob1a.png

I recently ran into a situation where I needed to start a SQL agent job from PowerShell. The solution needed to be a tool that others could use who may or may not have the SQLServer module, SQLPS module or older SQL Server snap-in installed.

I decided to write a function to leverage the .NET Framework from PowerShell to start a SQL Server job:

  1#Requires -Version 3.0
  2function Start-MrSqlAgentJob {
  3
  4<#
  5.SYNOPSIS
  6    Starts the specified SQL Agent Job on the specified target instance of SQL Server.
  7
  8.DESCRIPTION
  9    Start-MrSqlAgentJob is a PowerShell function that is designed to start the specified SQL Server
 10    Agent job on the specified target instance of SQL Server without requiring the SQL Server PowerShell
 11    module or snap-in to be installed.
 12
 13.PARAMETER ServerInstance
 14    The name of an instance of SQL Server where the SQL Agent is running. For default instances, only
 15    specify the computer name: MyComputer. For named instances, use the format ComputerName\InstanceName.
 16
 17.PARAMETER Name
 18    Specifies the name of the Job object that this cmdlet gets. The name may or may not be
 19    case-sensitive, depending on the collation of the SQL Server where the SQL Agent is running.
 20
 21.PARAMETER Credential
 22    SQL Authentication userid and password in the form of a credential object.
 23
 24.EXAMPLE
 25     Start-MrSqlAgentJob -ServerInstance SQLServer01 -Name syspolicy_purge_history
 26
 27.EXAMPLE
 28     Start-MrSqlAgentJob -ServerInstance SQLServer01 -Name syspolicy_purge_history -Credential (Get-Credential)
 29
 30.EXAMPLE
 31     'syspolicy_purge_history' | Start-MrSqlAgentJob -ServerInstance SQLServer01
 32
 33.INPUTS
 34    String
 35
 36.OUTPUTS
 37    Boolean
 38
 39.NOTES
 40    Author:  Mike F Robbins
 41    Website: http://mikefrobbins.com
 42    Twitter: @mikefrobbins
 43#>
 44
 45    [CmdletBinding()]
 46    param (
 47        [Parameter(Mandatory)]
 48        [string]$ServerInstance,
 49
 50        [Parameter(Mandatory,
 51                   ValueFromPipeLine)]
 52        [string]$Name,
 53
 54        [System.Management.Automation.Credential()]$Credential = [System.Management.Automation.PSCredential]::Empty
 55    )
 56
 57    BEGIN {
 58
 59        $Database = 'msdb'
 60        $connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
 61
 62        if (-not($PSBoundParameters.Credential)) {
 63            $connectionString = "Server=$ServerInstance;Database=$Database;Integrated Security=True;"
 64        }
 65        else {
 66            $connectionString = "Server=$ServerInstance;Database=$Database;Integrated Security=False;"
 67            $userid = $Credential.UserName -replace '^.*\\|@.*$'
 68            ($password = $credential.Password).MakeReadOnly()
 69            $sqlCred = New-Object -TypeName System.Data.SqlClient.SqlCredential($userid, $password)
 70            $connection.Credential = $sqlCred
 71        }
 72
 73        $connection.ConnectionString = $connectionString
 74        $ErrorActionPreference = 'Stop'
 75
 76        try {
 77            $connection.Open()
 78            Write-Verbose -Message "Connection to the $($connection.Database) database on $($connection.DataSource) has been successfully opened."
 79        }
 80        catch {
 81            Write-Error -Message "An error has occurred. Error details: $($_.Exception.Message)"
 82        }
 83
 84        $ErrorActionPreference = 'Continue'
 85        $command = $connection.CreateCommand()
 86
 87    }
 88
 89    PROCESS {
 90
 91        $Query = "EXEC dbo.sp_start_job N'$Name'"
 92        $command.CommandText = $Query
 93        $ErrorActionPreference = 'Stop'
 94
 95        try {
 96            $result = $command.ExecuteNonQuery()
 97        }
 98        catch {
 99            Write-Error -Message "An error has occured. Error Details: $($_.Exception.Message)"
100        }
101
102        $ErrorActionPreference = 'Continue'
103
104        if ($result -eq -1) {
105            Write-Output $true
106        }
107        else {
108            Write-Output $false
109        }
110
111    }
112
113    END {
114
115        $connection.Close()
116        $connection.Dispose()
117
118    }
119
120}

The job returns a Boolean. True means it started successfully and false means it failed to start:

1Start-MrSqlAgentJob -ServerInstance SQL011 -Name syspolicy_purge_history

startsqljob2a.png

The Start-MrSqlAgentJob function shown in the previous code example can be downloaded from my SQL repository on GitHub.

Update:

Thanks to Rob Sewell for pointing out that Get-SqlAgentJob returns a SMO object which has a start method:

1Get-SqlAgentJob -ServerInstance SQL011 -Name test | Get-Member -MemberType Method

startsqljob4a.png

That means it can be used to start a SQL agent job:

1(Get-SqlAgentJob -ServerInstance SQL011 -Name test).Start()
2Get-SqlAgentJob -ServerInstance SQL011 -Name test

startsqljob3a.png

It does require that SQL Server Management Studio 2016 be installed on the machine it's being run from.

µ