PowerShell Function to Check the Status of a SQL Agent Job using the .NET Framework
My previous blog article demonstrated how to start a SQL agent job using the .NET Framework from PowerShell to eliminate the dependency of needing the SQL Server PowerShell module or snap-in on the machine where the command is being run from.
There's not much use of blindly starting a SQL agent job without being able to check the status of it so I decided to write another function to accomplish that task. If the machine where the code is being run from has the SQL Server PowerShell module that installs as part of SQL Server Management Studio 2016, then you could simply use existing cmdlets without having to use this custom function.
1#Requires -Version 3.0
2function Get-MrSqlAgentJobStatus {
3
4<#
5.SYNOPSIS
6 Retrieves the status of one or more SQL Agent Jobs from the specified target instance of SQL Server.
7
8.DESCRIPTION
9 Get-MrSqlAgentJobStatus is a PowerShell function that is designed to retrieve the status of one or more SQL
10 Server Agent jobs from 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 one or more Job objects that this cmdlet gets. The names 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 Get-MrSqlAgentJobStatus -ServerInstance SQLServer01 -Name syspolicy_purge_history, test
26
27.EXAMPLE
28 Get-MrSqlAgentJobStatus -ServerInstance SQLServer01 -Name syspolicy_purge_history -Credential (Get-Credential)
29
30.EXAMPLE
31 'syspolicy_purge_history', 'test' | Get-MrSqlAgentJobStatus -ServerInstance SQLServer01
32
33.INPUTS
34 String
35
36.OUTPUTS
37 PSCustomObject
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 $Params = @{
60 ServerInstance = $ServerInstance
61 Database = 'msdb'
62 }
63
64 if ($PSBoundParameters.Credential) {
65 $Params.Credential = $Credential
66 }
67
68 }
69
70 PROCESS {
71 foreach ($n in $Name) {
72
73 $Params.Query = "EXEC dbo.sp_help_job @JOB_NAME = '$n', @job_aspect= 'JOB'"
74 $Results = Invoke-MrSqlDataReader @Params |
75 Select-Object -Property originating_server, name, last_run_outcome, current_execution_status
76
77 [pscustomobject]@{
78 ComputerName = $Results.originating_server
79 Name = $Results.name
80 Outcome = switch ($Results.current_execution_status) {
81 1 {'Executing';break}
82 2 {'Waiting for thread';break}
83 3 {'Between retries';break}
84 4 {'Idle';break}
85 5 {'Suspended';break}
86 7 {'Performing completion actions';break}
87 default {'An unknown error has occurred'}
88 }
89 Status = switch ($Results.last_run_outcome) {
90 0 {'Failed';break}
91 1 {'Succeeded';break}
92 3 {'Canceled';break}
93 5 {'Unknown';break}
94 default {'An unknown error has occurred'}
95 }
96 }
97
98 }
99
100 }
101
102}
Specify the SQL instance name and job name to return the status. There's also a credential parameter to specify SQL authentication if needed.
1Get-MrSqlAgentJobStatus -ServerInstance SQL011 -Name syspolicy_purge_history
Multiple jobs can also be retrieved via parameter or pipeline input:
1Get-MrSqlAgentJobStatus -ServerInstance SQL011 -Name syspolicy_purge_history, test
2syspolicy_purge_history', 'test' | Get-MrSqlAgentJobStatus -ServerInstance SQL011
The Get-MrSqlAgentJobStatus
function shown in this blog article can be downloaded from
my SQL repository on GitHub. This function depends on my
Invoke-MrSqlDataReader
function which is also part of my MrSQL PowerShell module which can be
found in the same repo.
µ