SQL 2005 HTML Job Report

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Ray Sotkiewicz » SQL 2005 HTML Job Report

SQL 2005 HTML Job Report

Rate Topic

Display Mode

Topic Options

Author

Message

Ray Sotkiewicz

Posted Monday, July 07, 2008 9:29 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:34 AM
Points: 165, Visits: 206

Comments posted to this topic are about the item SQL 2005 HTML Job Report

Post #529735

Jerry Sommerville

Posted Wednesday, August 27, 2008 6:15 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 08, 2013 9:22 AM
Points: 33, Visits: 172

Ray -

Thought the HTML Jobs script a great addition to my collection, but the script posted does not seem to have valid carriage return/line feeds. I tried pasting it into SQL Editor in SS2005, notepad, wordpad, word 2003, VEDIT (with several transaction settings) and finally Visual Studio. Could you check your posting please and perhaps post a reply with a copy that has some valid CR/LFs. Thanks.

... Jerry

Post #559548

Glen Weldon Maney

Posted Wednesday, August 27, 2008 9:18 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 2:56 PM
Points: 377, Visits: 361

I too have opened the file in Visual Studio and I think I have added the cr/lf (returns) in the correct places. My question now is how do you execute the script? I saved the file with a .vbs extension; however, it does not appear to work.

Thanks

Post #559731

vlad-548036

Posted Wednesday, August 27, 2008 2:52 PM

SSCommitted

Group: General Forum Members
Last Login: Monday, May 13, 2013 1:51 PM
Points: 1,825, Visits: 257

Please fix the whitespacein the script, otherwise it's almost useless w00t

Post #559961

Ray Sotkiewicz

Posted Wednesday, August 27, 2008 5:07 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:34 AM
Points: 165, Visits: 206

Let's see if this works....

Please note: I am not the original author. Some talented individual wrote this for SQL 2000. In it's original form it didn't work in 2005 but I loved the functionality it provides so I bit the bullet and re-wrote it for 2005.

To execute it, you'll just need to create an SSIS job step. No need for using external connectors or Linked servers. As long as the account running the job has some sort of admin permissions, you should be fine.

Ray Sotkiewicz
What's a PFD?

===========================================================================
= First, here's the DTS.Config file contents:
= NOTE: You MUST do a Search Replace on "(" & ")" and replace with " " respectively
===========================================================================
(?xml version="1.0" encoding="utf-8"?)
(DTSConfiguration)
(DTSConfigurationHeading)
(DTSConfigurationFileInfo GeneratedBy="DOMAIN\USER" GeneratedFromPackageName="Job Report" GeneratedFromPackageID="{3D74EA27-FC59-4C4D-8E1D-C16B6153CD4D}" GeneratedDate="7/18/2008 2:24:54 PM" /)
(/DTSConfigurationHeading)
(Configuration ConfiguredType="Property" Path="\Package.Variables[User::REPORT_PATH].Properties[Value]" ValueType="String")
(ConfiguredValue)D:\Shares\JobReports\(/ConfiguredValue)
(/Configuration)
(Configuration ConfiguredType="Property" Path="\Package.Variables[User::TARGET_SERVER].Properties[Value]" ValueType="String")
(ConfiguredValue)SERVERNAME(/ConfiguredValue)
(/Configuration)
(Configuration ConfiguredType="Property" Path="\Package.Variables[User::WIN_SECURITY].Properties[Value]" ValueType="String")
(ConfiguredValue)YES(/ConfiguredValue)
(/Configuration)
(/DTSConfiguration)

===========================================================================
And here's the ActiveX script:
===========================================================================

'**********************************************************************************************************
' Name: SQL Server Job Report (For SQL 2005 SSIS)
' Script Language: VBScript
'
' Description:
' This script will generate an HTML report of the last run status for all jobs on a specified
' SQL Server instance. This report can be emailed to specified addresses if required, or
' the generated HTML file may be published to a web site or distributed with any other file
' distribution mechanisms.
' The report is broken down into three sections: a summary report of the number of jobs
' which succeeded, failed, were cancelled or have an UNKNOWN status; a list of jobs by
' status; a detailed report on the last recorded run of each job. Each section in the report
' is hyper-linked to allow easy reference and navigation between sections.
'
' ------------------------------------------------------------------------------------
' ORIGINAL AUTHOR: (If anyone knows who the original author is, please email me at:
' raysot@Comcast.net so this person gets the credit they deserve
'
' MODIFICATIONS:
' Date: Author: Notes:
' 7/3/08 Ray Sotkiewicz Converted package to SQL 2005
' ------------------------------------------------------------------------------------
' Addendum:
' Be sure these variables exist as ReadOnlyVariables in the Script Task Editor:
' UID, PWD,TARGET_SERVER, WIN_SECURITY,EMAIL_REPORT, REPORT_PATH, KEEP_REPORT_FILE
' ------------------------------------------------------------------------------------

' Package Global Variables:
' This script requires a number of Global Variables to be created in any package
' that it is included as part of.
' Variable Name Req'd/Opt Description
' -----------------------------------------------------------------------------------
' TARGET_SERVER Required Specifies the SQL Server instance for which the
' job report will be compiled.
' ADDRESSES Optional Contains a list of email addresses to which the
' report will be sent. Addresses must be separated
' by semi-colons, and the list must be terminated
' with a semi-colon.
' WIN_SECURITY Required Specifies whether to use Windows Integrated
' Security when connecting to the specified SQL
' Server instance. Valid values are "YES" and
' "NO".
' UID Optional Only required if Windows Integrated Security is
' not being used. Specifies a valid SQL Server
' user ID.
' PWD Optional Only required if Windows Integrated Security is
' not being used. Specifies the password for the
' SQL Server user ID.
' EMAIL_REPORT Required Specifies if the job report is to be emailed. Valid
' values are "YES" and "NO".
' REPORT_PATH Required Specifies the file path where the job report will
' be written to.
' KEEP_REPORT_FILE Required Specifies if the Report file is to be retained when
' the report is generated. Valid values are "YES"
' and "NO".
'
'**********************************************************************************************************

Option Strict Off

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Server

Imports System.Data.Sqlclient

Imports Microsoft.Win32

Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()
Dim JobsFail, JobsSucc, JobsCanc, JobsDsbl, JobsTotl, JobsUnk, iChar As Integer
Dim TextFail, TextSucc, TextCanc, TextDsbl, TextUnk As String
Dim TitleFail, TitleSucc, TitleCanc, TitleDsbl, TitleUnk As String
Dim MailSubject, MailText, JobInfo, AddrList, Addr, [Char] As String
Dim ErrMsg, ErrLineNo, LineCount, ErrLine As Integer
Dim JobName As String

' Declare ADO object variables
Dim adoConn, rsJob, rsJobHistory, rsJobStep As Object
Dim strJobSQL, strJobHistorySQL, strJobStepSQL As String

' Declare FileSystemObject variables
Dim oFSO, OutFile, ReadFile As Object
Dim TempFail, TempSucc, TempCanc, TempUnk As String

' Declare Outlook object variables
Dim olApp, olMail, olNameSpace As Object

' Create ADO objects
adoConn = CreateObject("ADODB.Connection")
rsJob = CreateObject("ADODB.RecordSet")
rsJobHistory = CreateObject("ADODB.RecordSet")
rsJobStep = CreateObject("ADODB.Recordset")

' Open ADO connection using windows security
'If Dts.Variables("WIN_SECURITY") Is "YES" Then
adoConn.Open = "Provider=SQLOLEDB;Data Source=" & Dts.Variables("TARGET_SERVER").Value & ";Integrated Security=SSPI;Initial Catalog = MSDB"
'Else
'adoConn.Open = "Provider=SQLOLEDB;UID=" & Dts.Variables("UID").Value & ";PWD=" & Dts.Variables("PWD").Value & ";Data Source=" &

Dts.Variables("TARGET_SERVER").Value & ";Initial Catalog = MSDB"
'End If

' Set query test for jobs
strJobSQL = "EXEC msdb.dbo.sp_help_job"
' Open ADO record set
rsJob.Open(strJobSQL, adoConn)

' Create output file
oFSO = CreateObject("Scripting.FileSystemObject")
OutFile = oFSO.CreateTextFile(Dts.Variables("REPORT_PATH").Value & Dts.Variables("TARGET_SERVER").Value & ".html", True)
OutFile.WriteLine(" ")
OutFile.WriteLine(" ")
OutFile.WriteLine(" ")
OutFile.WriteLine(" ")
OutFile.WriteLine(" ")
OutFile.WriteLine(" **" & Dts.Variables("TARGET_SERVER").Value & "** Job Report (Created: " & Date.Now &

") ")
OutFile.WriteLine("

")

' 'Create Outlook objects
' If Dts.Variables("EMAIL_REPORT").Value = "YES" Then
' olApp = CreateObject("Outlook.Application")
' olNameSpace = olApp.GetNameSpace("MAPI")
' olNameSpace.Logon()
' olMail = olApp.CreateItem(0)
' End If

' Loop through all record for summary report

JobsFail = 0
TempFail = ""
JobsSucc = 0
TempSucc = ""
JobsCanc = 0
TempCanc = ""
JobsTotl = 0
JobsUnk = 0
TempUnk = ""

OutFile.WriteLine(" ")
OutFile.WriteLine("

")
rsJob.MoveFirst()
JobName = ""
While Not rsJob.Eof
JobsTotl = JobsTotl + 1
If rsJob.Fields.Item(21).value = 1 Then
JobsSucc = JobsSucc + 1

'JobDateTime(RTrim(rsJob.Fields.Item(19).ToString), RTrim(rsJob.Fields.Item(20)).ToString).ToString &

TempSucc = TempSucc & " "
If rsJob.Fields.Item(3).value = 0 Then
TempSucc = TempSucc & " "
Else
TempSucc = TempSucc & " "
End If
TempSucc = TempSucc & " " & JobDateTime(RTrim(rsJob.Fields.Item(19).value.ToString), RTrim(rsJob.Fields.Item(20).value.ToString)) &

" " & vbCrLf
ElseIf rsJob.Fields.Item(21).value = 3 Then
JobsCanc = JobsCanc + 1
TempCanc = TempCanc & " "
If rsJob.Fields.Item(3).value = 0 Then
TempCanc = TempCanc & " "
Else
TempCanc = TempCanc & " "
End If
TempCanc = TempCanc & " " & JobDateTime(RTrim(rsJob.Fields.Item(19).value.ToString), RTrim(rsJob.Fields.Item(20).value.ToString)) &

" " & vbCrLf

ElseIf rsJob.Fields.Item(21).value = 5 Then
JobsUnk = JobsUnk + 1
TempUnk = TempUnk & " "
If rsJob.Fields.Item(3).value = 0 Then
TempUnk = TempUnk & " "
Else
TempUnk = TempUnk & " "
End If
TempUnk = TempUnk & " " & JobDateTime(RTrim(rsJob.Fields.Item(19).value.ToString), RTrim(rsJob.Fields.Item(20).value.ToString)) &

" " & vbCrLf

Else
JobsFail = JobsFail + 1
TempFail = TempFail & " "
If rsJob.Fields.Item(3).value = 0 Then
TempFail = TempFail & " "
Else
TempFail = TempFail & " "
End If
TempFail = TempFail & " " & JobDateTime(RTrim(rsJob.Fields.Item(19).value.ToString), RTrim(rsJob.Fields.Item(20).value.ToString)) &

" " & vbCrLf
End If
rsJob.MoveNext()
End While

' Create summary report of job statistics
OutFile.WriteLine(" ")
OutFile.WriteLine(" ")
OutFile.WriteLine(" ")
OutFile.WriteLine(" ")
OutFile.WriteLine(" ")
OutFile.WriteLine(" ")
OutFile.WriteLine(" ")
OutFile.WriteLine(" ")
OutFile.WriteLine("

")

' Create summary reports by job status
TempSucc = " <FONT

COLOR=""WHITE""> " & vbCrLf & TempSucc
TempSucc = " " & vbCrLf & TempSucc
TempSucc = TempSucc & " " & vbCrLf
TempSucc = TempSucc & " " & vbCrLf
TempSucc = TempSucc & "

" & vbCrLf

TempFail = " <FONT

COLOR=""WHITE""> " & vbCrLf & TempFail
TempFail = " " & vbCrLf & TempFail
TempFail = TempFail & " " & vbCrLf
TempFail = TempFail & " " & vbCrLf
TempFail = TempFail & "

" & vbCrLf

TempCanc = " <TD

WIDTH=150> " & vbCrLf & TempCanc
TempCanc = " " & vbCrLf & TempCanc
TempCanc = TempCanc & " " & vbCrLf
TempCanc = TempCanc & " " & vbCrLf
TempCanc = TempCanc & "

" & vbCrLf

TempUnk = " <TD

WIDTH=150> " & vbCrLf & TempUnk
TempUnk = " " & vbCrLf & TempUnk
TempUnk = TempUnk & " " & vbCrLf
TempUnk = TempUnk & " " & vbCrLf
TempUnk = TempUnk & "

" & vbCrLf

' Add reports to mail - Successful job list not currently added
OutFile.Write(TempFail)
OutFile.Write(TempCanc)
OutFile.Write(TempUnk)
OutFile.Write(TempSucc)
OutFile.WriteLine(" ")

' Loop through all records for detailed report
OutFile.WriteLine(" ")
OutFile.WriteLine("

")
rsJob.MoveFirst()
JobName = ""
While Not rsJob.Eof
' Write Heading for current job
OutFile.WriteLine(" ")
OutFile.WriteLine(" " &

rsJob.Fields.Item(2).value.ToString & " ")
OutFile.WriteLine(" ")
OutFile.WriteLine(" ")
OutFile.WriteLine(" ")
If rsJob.Fields.Item(24).value = 0 Then
OutFile.WriteLine(" ")
Else
OutFile.WriteLine(" " & JobDateTime(RTrim(rsJob.Fields.Item(22).value.ToString),

RTrim(rsJob.Fields.Item(23).value.ToString)) & " ")
End If
OutFile.WriteLine(" ")
' Open Job History for Current Job
If rsJobStep.State <> 0 Then rsJobStep.Close()
strJobStepSQL = "EXEC msdb.dbo.sp_help_jobstep @job_id='" & rsJob.Fields.Item(0).value.ToString & "'"
rsJobStep.Open(strJobStepSQL, adoConn)
' Write last run outcome
OutFile.WriteLine(" ")
OutFile.WriteLine(" ")
If rsJob.Fields.Item(19).value <> 0 Then

strJobHistorySQL = "EXEC msdb.dbo.sp_help_jobhistory @job_id='" & rsJob.Fields.Item(0).value.ToString & "', @step_id = 0, @mode='FULL',

@start_run_date=" & rsJob.Fields.Item(19).value.ToString & ", @start_run_time=" & rsJob.Fields.Item(20).value.ToString

rsJobHistory.Open(strJobHistorySQL, adoConn)
If rsJobHistory.EOF And rsJobHistory.BOF Then
OutFile.WriteLine(" ")
Else
OutFile.WriteLine(" ")
End If
rsJobHistory.Close()
Else
OutFile.WriteLine(" ")
End If
OutFile.WriteLine(" ")
' Write Job Step Report
While Not rsJobStep.EOF
OutFile.WriteLine(" ")
OutFile.WriteLine(" Step #" & rsJobStep.Fields.Item(0).value.ToString & " - " & rsJobStep.Fields.Item(1).value.ToString &

" ")
OutFile.WriteLine(" ")
OutFile.WriteLine(" ")
OutFile.WriteLine(" " & JobDateTime(RTrim(rsJobStep.Fields.Item(20).value.ToString),

RTrim(rsJobStep.Fields.Item(21).value.ToString)) & " ")
OutFile.WriteLine(" ")
OutFile.WriteLine(" " & Replace(rsJobStep.Fields.Item(3).value.ToString, vbCrLf, "
") & " ")
If rsJob.Fields.Item(19).value <> 0 Then
strJobHistorySQL = "EXEC msdb.dbo.sp_help_jobhistory @job_id='" & rsJob.Fields.Item(0).value.ToString & "', @step_id = " &

rsJobStep.Fields.Item(0).value.ToString & ", @mode='FULL', @start_run_date=" & rsJob.Fields.Item(19).value.ToString & ", @start_run_time=" &

rsJob.Fields.Item(20).value.ToString
rsJobHistory.Open(strJobHistorySQL, adoConn)
If Not (rsJobHistory.EOF And rsJobHistory.BOF) Then
OutFile.WriteLine(" " & Replace(rsJobHistory.Fields.Item(7).value.ToString, vbCrLf, "
") & " ")
End If
rsJobHistory.Close()
End If
OutFile.WriteLine(" ")
rsJobStep.MoveNext()
End While
rsJob.MoveNext()
' Close off Report
OutFile.WriteLine(" ")
OutFile.WriteLine("

")
End While

If JobsFail = 0 Then
MailSubject = "Job Status : " & Dts.Variables("TARGET_SERVER").Value & " : No Failures"
Else
MailSubject = "Job Status : " & Dts.Variables("TARGET_SERVER").Value & " : " & JobsFail & " Failures"
End If

' Close report file
OutFile.WriteLine(" ")
OutFile.WriteLine(" ")
OutFile.Close()

ReadFile = oFSO.OpenTextFile(Dts.Variables("REPORT_PATH").Value & Dts.Variables("TARGET_SERVER").Value & ".html", 1)

'Determine if report is to be emailed
If Dts.Variables("EMAIL_REPORT").Value = "YES" Then
' Set email properties
AddrList = Dts.Variables("ADDRESSES").Value
Addr = ""
For iChar = 1 To Len(AddrList) - 1
If Mid(AddrList, iChar, 1) = ";" Then
olMail.Recipients.Add(Addr)
Addr = ""
Else
Addr = Addr & Mid(AddrList, iChar, 1)
End If
Next
olMail.Recipients.Add(Addr)
olMail.Recipients.ResolveAll()
olMail.Subject = MailSubject
olMail.HTMLBody = ReadFile.ReadAll
olMail.Send()
End If

' Clean Up
ReadFile.Close()
If Dts.Variables("KEEP_REPORT_FILE").Value = "NO" Then
oFSO.DeleteFile(Dts.Variables("REPORT_PATH").Value, True)
End If
oFSO = Nothing
olMail = Nothing
olApp = Nothing

Dts.TaskResult = Dts.Results.Success

End Sub

'**********************************************************************************************************
' Function: JobDateTime
' Description: Accepts unformatted data and time parameters and returns formatted
' datetime string as "DD Mon YYYY HH:MM:SS".
'**********************************************************************************************************
Private Function JobDateTime(ByVal InDate As String, ByVal InTime As String) As String

Dim strDate As String, strTime As String, dtDateTime As Date

strDate = ""
strTime = ""

If InDate = "0" Then
strDate = "Unknown"
Else
'strDate = Right(InDate, 2) & " " & MonthName(CInt(Mid(InDate, 5, 2)), True) & " " & Left(InDate, 4)
'strDate = Right(InDate, 2) & "/" & Mid(InDate, 5, 2) & "/" & Left(InDate, 4)
strDate = Mid(InDate, 5, 2) & "/" & Right(InDate, 2) & "/" & Left(InDate, 4)

End If

If InTime = "0" Then
strTime = ""
Else
If Len(InTime) = 5 Then InTime = "0" & InTime
strTime = Left(InTime, 2) & ":" & Mid(InTime, 3, 2) & ":" & Right(InTime, 2)
End If

JobDateTime = strDate & " " & strTime

End Function

'**********************************************************************************************************
' Function: JobStatus
' Description: Accepts numeric status code and returns string description.
'**********************************************************************************************************
Private Function JobStatus(ByVal InStatus As Integer) As String

If InStatus = 1 Then
JobStatus = "SUCCESS"
ElseIf InStatus = 3 Then
JobStatus = "CANCELLED"
ElseIf InStatus = 5 Then
JobStatus = "UNKNOWN"
Else
JobStatus = "FAILED"
End If

End Function

End Class

Post #560017

« Prev Topic | Next Topic »

Permissions