Prizes & Awards
My Profile
Active Members
TodayLast 7 Days
more...
Birthday Greetings
|
Learn Windows 7: What are the Technology advancements in on mobile gaming? This resource will provide information about the advancements that are available in the mobile gaming.
Resources » Code Snippets » SQL »
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
|
|
Responses to the resource: "Read the status of SQL Server Agent jobs"
|
No responses found. Be the first to respond and make money from revenue sharing program.
|
|