Function IsValidObject(ByVal oObject)
IsValidObject = False
If IsObject(oObject) Then
If Not oObject Is Nothing Then
IsValidObject = True
End If
End If
End Function
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
'#Include File:Error.vbs
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 ThrowScriptErrorNoAbort(ByVal sMessage, ByVal oErr)
On Error Resume Next
Dim oAPITemp
Set oAPITemp = MOMCreateObject("MOM.ScriptAPI")
oAPITemp.LogScriptEvent WScript.ScriptName, 4001, 1, sMessage & ". " & oErr.Description
End Function
Function ThrowScriptError(Byval sMessage, ByVal oErr)
On Error Resume Next
ThrowScriptErrorNoAbort sMessage, oErr
Quit()
End Function
Sub HandleError(customMessage)
Dim localLogger
If Not (Err.number = 0) Then
Set localLogger = new ScriptLogger
localLogger.LogFormattedError(customMessage)
Wscript.Quit 0
End If
End Sub
Function HandleErrorContinue(customMessage)
Dim localLogger
HandleErrorContinue = False
If Not (Err.number = 0) Then
Set localLogger = new ScriptLogger
localLogger.LogFormattedError(customMessage)
Err.Clear
HandleErrorContinue = True
End If
End Function
'#Include File:ConnectionString.vbs
Function BuildConnectionString(strServer, strDatabase)
ON ERROR RESUME NEXT
Err.Clear
Dim dataSource
dataSource = BuildServerName(strServer, "")
BuildConnectionString = "Data Source=" & EscapeConnStringValue(dataSource) & ";Initial Catalog=" & EscapeConnStringValue(strDatabase) & ";Integrated Security=SSPI"
End Function
Function BuildConnectionStringWithPort(ByVal strServer, ByVal strDatabase, ByVal tcpPort)
ON ERROR RESUME NEXT
Err.Clear
Dim dataSource
dataSource = strServer
If ((tcpPort <> "0") And (tcpPort <> "")) Then
dataSource = dataSource & "," & tcpPort
End If
BuildConnectionStringWithPort = "Data Source=" & EscapeConnStringValue(dataSource) & ";Initial Catalog=" & EscapeConnStringValue(strDatabase) & ";Integrated Security=SSPI"
End Function
' This function should be used to escape Connection String keywords.
Function EscapeConnStringValue (ByVal strValue)
ON ERROR RESUME NEXT
Err.Clear
EscapeConnStringValue = """" + Replace(strValue, """", """""") + """"
End Function
Function EscapeWQLString (ByVal strValue)
ON ERROR RESUME NEXT
Err.Clear
EscapeWQLString = Replace(strValue, "'", "\'")
End Function
Function GetTcpPort (ByVal strServer)
ON ERROR RESUME NEXT
Err.Clear
Dim tcpPort
tcpPort = ""
Call BuildServerName(strServer, tcpPort)
GetTcpPort = tcpPort
End Function
Function BuildServerName(ByVal strServer, ByRef tcp)
ON ERROR RESUME NEXT
Err.Clear
Dim pathArray, instanceName, computerName, ip, serverName
Dim oWMI, oQuery
ip= ""
pathArray = Split(strServer, "\")
computerName = pathArray(0)
instanceName = "MSSQLSERVER"
if (pathArray.Count > 1) Then
instanceName = pathArray(1)
End If
serverName = strServer
Set oWMI = GetObject("winmgmts:\\" & computerName & "\root\Microsoft\SqlServer\" & SQL_WMI_NAMESPACE)
Set oQuery = oWMI.ExecQuery("SELECT * FROM ServerNetworkProtocolProperty WHERE ProtocolName = 'Tcp' AND InstanceName = '"& EscapeWQLString(instanceName) &"' AND PropertyName = 'ListenOnAllIPs'")
If oQuery.Count >0 Then
Dim isListenAll
Set isListenAll = oQuery.ItemIndex(0)
If(isListenAll.PropertyNumVal = 1) Then
Set oQuery = oWMI.ExecQuery("SELECT * FROM ServerNetworkProtocolProperty WHERE ProtocolName = 'Tcp' AND InstanceName = '"& EscapeWQLString(instanceName) &"' AND IPAddressName = 'IPAll' AND (PropertyName = 'TcpPort' OR PropertyName = 'TcpDynamicPorts') AND PropertyStrVal <> ''")
If (oQuery.Count > 0) Then
tcp = oQuery.ItemIndex(0).PropertyStrVal
If ((tcp <> "0") And (tcp <> "")) Then
serverName = serverName & "," & tcp
Else tcp = ""
End If
End If
Else
Set oQuery = oWMI.ExecQuery("SELECT * FROM ServerNetworkProtocolProperty WHERE ProtocolName = 'Tcp' AND InstanceName = '"& EscapeWQLString(instanceName) &"' AND IPAddressName <> '' AND PropertyName = 'Enabled' AND PropertyNumVal = 1")
If (oQuery.Count > 0) Then
Dim ipAddressName
ipAddressName = oQuery.ItemIndex(0).IPAddressName
Set oQuery = oWMI.ExecQuery("SELECT * FROM ServerNetworkProtocolProperty WHERE ProtocolName = 'Tcp' AND InstanceName = '"& EscapeWQLString(instanceName) &"' AND IPAddressName = '"& EscapeWQLString(ipAddressName) &"' AND (PropertyName = 'TcpPort' OR PropertyName = 'TcpDynamicPorts') AND PropertyStrVal <> ''")
If (oQuery.Count > 0) Then
tcp = oQuery.ItemIndex(0).PropertyStrVal
End If
Set oQuery = oWMI.ExecQuery("SELECT * FROM ServerNetworkProtocolProperty WHERE ProtocolName = 'Tcp' AND InstanceName = '"& EscapeWQLString(instanceName) &"' AND IPAddressName = '"& EscapeWQLString(ipAddressName) &"' AND PropertyName = 'IpAddress' AND PropertyStrVal <> ''")
If (oQuery.Count > 0) Then
ip = oQuery.ItemIndex(0).PropertyStrVal
End If
If ip <> "" Then
serverName = ip
End If
If ((tcp <> "0") And (tcp <> "")) Then
serverName = servername & "," & tcp
Else tcp = ""
End If
End If
End If
End If
On Error Goto 0
BuildServerName = serverName
End Function'#Include File:GetSQL2012AgentJobStatus.vbs
'Copyright (c) Microsoft Corporation. All rights reserved.
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, sTcpPort, Computer, Service
Dim oAPI, oBag
Set objParameters = WScript.Arguments
If objParameters.Count <> 4 Then
Quit()
End If
Set oAPI = MOMCreateObject("MOM.ScriptAPI")
Set oBag = oAPI.CreatePropertyBag()
Dim state
state = GetServiceState(Computer, Service)
if (state <> "Running") And (state <> "Unknown") Then
Call oAPI.Return(oBag)
Quit()
End If
If GetJobStatus(oBag, sConnectionString, sTcpPort) = 0 Then
Call oAPI.Return(oBag)
Else
Call oAPI.Return(oBag)
Quit()
End If
End Sub
'End of Main
'-----------------------------------------------------------------------------------------------
'The function returns service or "Unknown" state
'Input:
' server - compute name
' service - system service name
'Output:
' service state or "Unknown" state
Function GetServiceState( sTargetComputer, sServiceName)
On Error Resume Next
Dim sNamespace, sQuery, oWMI, objClasses, sState
sNamespace = "winmgmts://" & sTargetComputer & "/root/cimv2"
sQuery = "SELECT State FROM Win32_Service where Name = """ & EscapeWQLString(sServiceName) & """"
Set oWMI = GetObject(sNamespace)
Set objClasses = oWMI.ExecQuery(sQuery)
if objClasses.Count >= 1 Then
sState = objClasses.ItemIndex(0).Properties_.Item("State")
End If
If Err.number <> 0 Or objClasses.Count = 0 Then
sState = "Unknown"
End If
Err.Clear
GetServiceState = sState
End Function
'End of Main
'-----------------------------------------------------------------------------------------------
Function GetJobStatus (ByRef oBag, ByVal sConnectionString, ByVal sTcpPort)
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 = BuildConnectionStringWithPort(sConnectionString, "msdb", sTcpPort)
Err.Clear
cnADOConnection.Open strProv
if 0 <> Err.number then
Err.Clear
strProv = BuildConnectionString(sConnectionString, "msdb")
cnADOConnection.Open strProv
if 0 <> Err.number then
GetJobStatus = SQL_MONITORING_CONNECT_FAILURE
Exit Function
End If
end if
Err.Clear
Set rsSQLJobs = cnADOConnection.Execute(SCRIPT_SQL)
If 0 <> Err.number Then
GetJobStatus = SQL_MONITORING_QUERY_FAILURE
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
JPN 
Show References: