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.

Specify the SQL instance name and job name to return the status. There’s also a credential parameter to specify SQL authentication if needed.

agentjobstatus1a

Multiple jobs can also be retrieved via parameter or pipeline input:

agentjobstatus2a

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.

µ

1 Comment

  1. jxhyao

    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.

    Reply

Leave a Reply

%d bloggers like this: