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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 | #Requires -Version 3.0 function Get-MrSqlAgentJobStatus { <# .SYNOPSIS Retrieves the status of one or more SQL Agent Jobs from the specified target instance of SQL Server. .DESCRIPTION Get-MrSqlAgentJobStatus is a PowerShell function that is designed to retrieve the status of one or more SQL Server Agent jobs from the specified target instance of SQL Server without requiring the SQL Server PowerShell module or snap-in to be installed. .PARAMETER ServerInstance The name of an instance of SQL Server where the SQL Agent is running. For default instances, only specify the computer name: MyComputer. For named instances, use the format ComputerName\InstanceName. .PARAMETER Name Specifies the name of one or more Job objects that this cmdlet gets. The names may or may not be case-sensitive, depending on the collation of the SQL Server where the SQL Agent is running. .PARAMETER Credential SQL Authentication userid and password in the form of a credential object. .EXAMPLE Get-MrSqlAgentJobStatus -ServerInstance SQLServer01 -Name syspolicy_purge_history, test .EXAMPLE Get-MrSqlAgentJobStatus -ServerInstance SQLServer01 -Name syspolicy_purge_history -Credential (Get-Credential) .EXAMPLE 'syspolicy_purge_history', 'test' | Get-MrSqlAgentJobStatus -ServerInstance SQLServer01 .INPUTS String .OUTPUTS PSCustomObject .NOTES Author: Mike F Robbins Website: http://mikefrobbins.com Twitter: @mikefrobbins #> [CmdletBinding()] param ( [Parameter(Mandatory)] [string]$ServerInstance, [Parameter(Mandatory, ValueFromPipeLine)] [string[]]$Name, [System.Management.Automation.Credential()]$Credential = [System.Management.Automation.PSCredential]::Empty ) BEGIN { $Params = @{ ServerInstance = $ServerInstance Database = 'msdb' } if ($PSBoundParameters.Credential) { $Params.Credential = $Credential } } PROCESS { foreach ($n in $Name) { $Params.Query = "EXEC dbo.sp_help_job @JOB_NAME = '$n', @job_aspect= 'JOB'" $Results = Invoke-MrSqlDataReader @Params | Select-Object -Property originating_server, name, last_run_outcome, current_execution_status [pscustomobject]@{ ComputerName = $Results.originating_server Name = $Results.name Outcome = switch ($Results.current_execution_status) { 1 {'Executing';break} 2 {'Waiting for thread';break} 3 {'Between retries';break} 4 {'Idle';break} 5 {'Suspended';break} 7 {'Performing completion actions';break} default {'An unknown error has occurred'} } Status = switch ($Results.last_run_outcome) { 0 {'Failed';break} 1 {'Succeeded';break} 3 {'Canceled';break} 5 {'Unknown';break} default {'An unknown error has occurred'} } } } } } |
Specify the SQL instance name and job name to return the status. There’s also a credential parameter to specify SQL authentication if needed.
1 | Get-MrSqlAgentJobStatus -ServerInstance SQL011 -Name syspolicy_purge_history |
Multiple jobs can also be retrieved via parameter or pipeline input:
1 2 | Get-MrSqlAgentJobStatus -ServerInstance SQL011 -Name syspolicy_purge_history, test syspolicy_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.
µ
IMHO, this is really an over-stretch to do a simple work if all you do is to use “sp_help_job” to get the job status.
I would use
invoke-sqlcmd -Server . -Database msdb -Query “sp_help_job xxxx”
to get all I need.
If you really want to use pure SMO via jobserver, that may be another story.