Read the status of SQL Server Agent jobs


This article helps about information on title how to Read the status of SQL Server Agent jobs

The sample code can be used to read the status of SQL Server Agent jobs without using any log files.


The Store procedure exec sp_help_job is a system SP for the MSDB Database which reads the status of all SQL Server Agent jobs stored under Management. Hence the connection string for executing this SP should have the DB name as MSDB.

For example:

Dim conn_job As New SqlConnection ("data source=servername; initial catalog= msdb; persist security info=False; user id=id; password=pwd; packet size=4096")



Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Try

Dim con As New SqlConnection("--- Specify Connection String------")

Dim da As New SqlDataAdapter("exec sp_help_job", con)

Dim ds As New DataSet

da.Fill(ds, "SQL_Agent_Jobs")

Dim drow As DataRow

Dim originatingserver As String

Dim name As String

Dim lastrundate As String

Dim lastdate As String

Dim lastruntime As String

Dim lasttime As String

Dim lastrunoutcome As Integer

Dim laststatus As String

Dim nextrundate As String

Dim nextdate As String

Dim nextruntime As String

Dim nexttime As String

Dim Currentststus As Integer

Dim status As String

For Each drow In ds.Tables(0).Rows

originatingserver = drow.Item(1)

name = drow.Item(2)

lastrundate = drow.Item(19)

lastrundate = drow.Item(19)

If lastrundate <> "0" Then

lastdate = lastrundate.Substring(6, 2) & "-" & lastrundate.Substring(4, 2) & "-" & lastrundate.Substring(0, 4)

Else

lastdate = ""

End If

lastruntime = drow.Item(20)

If lastruntime <> "0" Then

lasttime = lastruntime.Substring(0, 2) & ":" & lastruntime.Substring(2, 2) & ":" & lastruntime.Substring(4, 2)

Else

lasttime = ""

End If

lastrunoutcome = drow.Item(21)

If lastrunoutcome = 0 Then

laststatus = "Failed"

ElseIf lastrunoutcome = 1 Then

laststatus = "Succeeded"

ElseIf lastrunoutcome = 3 Then

laststatus = "Cancelled"

Else

laststatus = "Unknown"

End If

nextrundate = drow.Item(22)

If nextrundate <> "0" Then

nextdate = nextrundate.Substring(6, 2) & "-" & nextrundate.Substring(4, 2) & "-" & nextrundate.Substring(0, 4)

Else

nextdate = ""

End If

nextruntime = drow.Item(22)

If nextruntime <> "0" Then

nexttime = nextruntime.Substring(0, 2) & ":" & nextruntime.Substring(2, 2) & ":" & nextruntime.Substring(4, 2)

Else

nexttime = ""

End If

Currentststus = drow.Item(25)

If Currentststus = 1 Then

status = "Executing"

ElseIf Currentststus = 2 Then

status = "Waiting For Thread"

ElseIf Currentststus = 3 Then

status = "Between Retries"

ElseIf Currentststus = 4 Then

status = "Idle"

ElseIf Currentststus = 5 Then

status = "Suspended"

ElseIf Currentststus = 7 Then

status = "Performing Completion Action"

Else

status = "Unknown"

End If

MsgBox("Job Name --" & name & "" & "Next Run Date --" & nextdate & "Next Run Time -- " & nexttime & "Last Run Date--" & lastdate &"Last Run Time --" & lasttime & "Current Status--" & status & "Last Status --" & laststatus)

Next

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try
End Sub





Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: