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 &_
",DATEDIFF(minute, [start_execution_date], GETDATE()) AS [execution_time_minutes]" & VbCrLf &_
"FROM @job_activity [ja]" & VbCrLf &_
"JOIN [msdb].[dbo].[sysjobs_view] [sjv] ON [sjv].[job_id] = [ja].[job_id]" & VbCrLf &_
"WHERE [start_execution_date] IS NOT NULL AND [run_status] IS NULL"
'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 CheckLongRunningJobs(oBag, sConnectionString) = 0 Then
Call oAPI.Return(oBag)
Else
Quit()
End If
End Sub
'End of Main
'-----------------------------------------------------------------------------------------------
Function WMIExecQuery(ByVal sNamespace, ByVal sQuery)
'
' WMIExecQuery :: Executes the WMI query and returns the result set.
'
'
Dim oWMI, oQuery, nInstanceCount
Dim e
Set e = New Error
On Error Resume Next
Set oWMI = GetObject(sNamespace)
e.Save
On Error Goto 0
If IsEmpty(oWMI) Then
ThrowScriptError "Unable to open WMI Namespace '" & sNamespace & "'. Check to see if the WMI service is enabled and running, and ensure this WMI namespace exists.", e
End If
On Error Resume Next
Set oQuery = oWMI.ExecQuery(sQuery)
e.Save
On Error Goto 0
If IsEmpty(oQuery) Or e.Number <> 0 Then
ThrowScriptError "The Query '" & sQuery & "' returned an invalid result set. Please check to see if this is a valid WMI Query.", e
End If
'Determine if we queried a valid WMI class - Count will return 0 or empty
On Error Resume Next
nInstanceCount = oQuery.Count
e.Save
On Error Goto 0
If e.Number <> 0 Then
ThrowScriptError "The Query '" & sQuery & "' did not return any valid instances. Please check to see if this is a valid WMI Query.", e
End If
Set WMIExecQuery = oQuery
End Function
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 "GetSQL2008LongRunningJobs.vbs", 4000, 1, sMessage & ". " & oErr.m_sDescription
Quit()
End Function
Function CheckLongRunningJobs (ByRef oBag, ByVal sConnectionString)
Dim cnADOConnection
Dim strProv
Dim strQuery
Dim rsSQLLongRunningJobs
Dim bFail
Dim nDuration
Dim maxDuration
'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
strQuery = SCRIPT_SQL
Err.Clear
Set rsSQLLongRunningJobs = cnADOConnection.Execute(strQuery)
If 0 <> Err.number Then
' Throw
Exit Function
End If
If 0 = rsSQLLongRunningJobs.State Then
' No records returned, recordset is likely closed.
Exit Function
End If
maxDuration = 0
Do
bFail = True
Err.Clear
if rsSQLLongRunningJobs.EOF then
if 0 <> Err.number then Exit Do
bFail = False
Exit Do
End If
If 0 <> Err.number Then Exit Do
Home
Show References: