'Copyright (c) Microsoft Corporation. All rights reserved.
Option Explicit
SetLocale("en-us")
Dim SCRIPT_SQL
SCRIPT_SQL = "SET NOCOUNT ON" & VbCrLf &_
"DECLARE @job_activity TABLE (" & VbCrLf &_
"[session_id] [int] NOT NULL," & VbCrLf &_
"[job_id] [uniqueidentifier] NOT NULL," & VbCrLf &_
"[job_name] [sysname] COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL," & VbCrLf &_
"[run_requested_date] [datetime] NULL," & VbCrLf &_
"[run_requested_source] [sysname] COLLATE SQL_Latin1_General_CP1_CS_AS NULL," & VbCrLf &_
"[queued_date] [datetime] NULL," & VbCrLf &_
"[start_execution_date] [datetime] NULL," & VbCrLf &_
"[last_executed_step_id] [int] NULL," & VbCrLf &_
"[last_exectued_step_date] [datetime] NULL," & VbCrLf &_
"[stop_execution_date] [datetime] NULL," & VbCrLf &_
"[next_scheduled_run_date] [datetime] NULL," & VbCrLf &_
"[job_history_id] [int] NULL," & VbCrLf &_
"[message] [nvarchar](1024) COLLATE SQL_Latin1_General_CP1_CS_AS NULL," & VbCrLf &_
"[run_status] [int] NULL," & VbCrLf &_
"[operator_id_emailed] [int] NULL," & VbCrLf &_
"[operator_id_netsent] [int] NULL," & VbCrLf &_
"[operator_id_paged] [int] NULL," & VbCrLf &_
"[execution_time_minutes] [int] NULL )" & VbCrLf &_
"INSERT INTO @job_activity" & VbCrLf &_
"([session_id]" & VbCrLf &_
",[job_id]" & VbCrLf &_
",[job_name]" & VbCrLf &_
",[run_requested_date]" & VbCrLf &_
",[run_requested_source]" & VbCrLf &_
",[queued_date]" & VbCrLf &_
",[start_execution_date]" & VbCrLf &_
",[last_executed_step_id]" & VbCrLf &_
",[last_exectued_step_date]" & VbCrLf &_
",[stop_execution_date]" & VbCrLf &_
",[next_scheduled_run_date]" & VbCrLf &_
",[job_history_id]" & VbCrLf &_
",[message]" & VbCrLf &_
",[run_status]" & VbCrLf &_
",[operator_id_emailed]" & VbCrLf &_
",[operator_id_netsent]" & VbCrLf &_
",[operator_id_paged])" & VbCrLf &_
"EXECUTE [msdb].[dbo].[sp_help_jobactivity]" & VbCrLf &_
"SELECT" & VbCrLf &_
"[ja].[job_id]" & VbCrLf &_
",[ja].[job_name]" & VbCrLf &_
",[originating_server]" & VbCrLf &_
",[message]" & VbCrLf &_
",[run_status]" & VbCrLf &_
", [execution_time_minutes] = case" & VbCrLf &_
" -- Has not finished a run" & VbCrLf &_
" When [start_execution_date] is not null And [stop_execution_date] is null" & VbCrLf &_
" Then DATEDIFF(minute, [start_execution_date], GETDATE())" & VbCrLf &_
" -- Has finished that run, but we still need to time for monitor state" & VbCrLf &_
" When [start_execution_date] is not null and [stop_execution_date] is not null" & VbCrLf &_
" Then DATEDIFF(minute, [start_execution_date], [stop_execution_date])" & VbCrLf &_
" else null" & VbCrLf &_
"End" & VbCrLf &_
" " & VbCrLf &_
"FROM @job_activity [ja]" & VbCrLf &_
"JOIN [msdb].[dbo].[sysjobs_view] [sjv] ON [sjv].[job_id] = [ja].[job_id]"
'Start of Main
'-----------------------------------------------------------------------------------------------
call Main()
Sub Main()
Dim objParameters, sConnectionString
Dim oAPI, oBag
Set objParameters = WScript.Arguments
If objParameters.Count <> 1 Then
Quit()
End If
sConnectionString= objParameters(0)
Set objParameters = Nothing
Set oAPI = MOMCreateObject("MOM.ScriptAPI")
Set oBag = oAPI.CreatePropertyBag()
If GetJobStatus(oBag, sConnectionString) = 0 Then
Call oAPI.Return(oBag)
Else
Quit()
End If
End Sub
'End of Main
'-----------------------------------------------------------------------------------------------
Function Quit()
WScript.Quit()
End Function
Class Error
Private m_lNumber
Private m_sSource
Private m_sDescription
Private m_sHelpContext
Private m_sHelpFile
Public Sub Save()
m_lNumber = Err.number
m_sSource = Err.Source
m_sDescription = Err.Description
m_sHelpContext = Err.HelpContext
m_sHelpFile = Err.helpfile
End Sub
Public Sub Raise()
Err.Raise m_lNumber, m_sSource, m_sDescription, m_sHelpFile, m_sHelpContext
End Sub
Public Sub Clear()
m_lNumber = 0
m_sSource = ""
m_sDescription = ""
m_sHelpContext = ""
m_sHelpFile = ""
End Sub
Public Default Property Get Number()
Number = m_lNumber
End Property
Public Property Get Source()
Source = m_sSource
End Property
Public Property Get Description()
Description = m_sDescription
End Property
Public Property Get HelpContext()
HelpContext = m_sHelpContext
End Property
Public Property Get HelpFile()
HelpFile = m_sHelpFile
End Property
End Class
Function MomCreateObject(ByVal sProgramId)
Dim oError
Set oError = New Error
On Error Resume Next
Set MomCreateObject = CreateObject(sProgramId)
oError.Save
On Error Goto 0
If oError.Number <> 0 Then ThrowScriptError "Unable to create automation object '" & sProgramId & "'", oError
End Function
Function ThrowScriptError(Byval sMessage, ByVal oErr)
On Error Resume Next
Dim oAPITemp
Set oAPITemp = MOMCreateObject("MOM.ScriptAPI")
oAPITemp.LogScriptEvent "GetSQL2005AgentJobStatus.vbs", 4000, 1, sMessage & ". " & oErr.m_sDescription
Quit()
End Function
Function GetJobStatus (ByRef oBag, ByVal sConnectionString)
Dim cnADOConnection
Dim strProv
Dim rsSQLJobs
Dim bFail
Dim nDuration
Dim jobID
Dim lastStatus
Dim lastMessage
'on error resume next
Set cnADOConnection = MomCreateObject("ADODB.Connection")
cnADOConnection.Provider = "sqloledb"
cnADOConnection.ConnectionTimeout = 30
strProv = "Server=" & sConnectionString & ";Database=msdb;Trusted_Connection=yes"
Err.Clear
cnADOConnection.Open strProv
if 0 <> Err.number then
' Throw
Exit Function
end if
Err.Clear
Set rsSQLJobs = cnADOConnection.Execute(SCRIPT_SQL)
If 0 <> Err.number Then
' Throw
Exit Function
End If
If 0 = rsSQLJobs.State Then
' No records returned, recordset is likely closed.
Exit Function
End If
Do
bFail = True
Err.Clear
if rsSQLJobs.EOF then
if 0 <> Err.number then Exit Do
bFail = False
Exit Do
End If
If 0 <> Err.number Then Exit Do
jobID = rsSQLJobs("job_id").Value
lastStatus = rsSQLJobs("run_status").Value
lastMessage = rsSQLJobs("message").Value
nDuration = Int(rsSQLJobs("execution_time_minutes").Value)
If (IsNull(nDuration)) Then nDuration = -1
If (IsNull(lastStatus)) Then lastStatus = -1
If (IsNull(lastMessage)) Then lastMessage = ""
Call oBag.AddValue(jobID & "-Duration",CDbl(nDuration))
Call oBag.AddValue(jobID & "-LastStatus", CInt(lastStatus))
Call oBag.AddValue(jobID & "-LastMessage", CStr(lastMessage))
Err.Clear
rsSQLJobs.MoveNext
If 0 <> Err.number then Exit Do
Loop
If bFail Then
' Throw
End if
Set cnADOConnection = Nothing
Set rsSQLJobs = Nothing
Home
Show References: