SQL 2005 HTML Job Report

Question

SQL 2005 HTML Job Report

Ray Sotkiewicz

Right there with Babe

Points: 796
More actions
July 7, 2008 at 9:29 pm

#193524

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jerry Sommerville SSC Veteran Points: 271 More actions · Answer 1

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

Glen Weldon Maney Say Hey Kid Points: 694 More actions · Answer 2

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

vlad-548036 Hall of Fame Points: 3491 More actions · Answer 3

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

Ray Sotkiewicz Right there with Babe Points: 796 More actions · Answer 4

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 NameReq'd/OptDescription

' -----------------------------------------------------------------------------------

' TARGET_SERVERRequiredSpecifies the SQL Server instance for which the

'job report will be compiled.

' ADDRESSESOptionalContains 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_SECURITYRequiredSpecifies whether to use Windows Integrated

'Security when connecting to the specified SQL

'Server instance. Valid values are "YES" and

'"NO".

' UIDOptionalOnly required if Windows Integrated Security is

'not being used. Specifies a valid SQL Server

'user ID.

' PWDOptionalOnly required if Windows Integrated Security is

'not being used. Specifies the password for the

'SQL Server user ID.

' EMAIL_REPORTRequiredSpecifies if the job report is to be emailed. Valid

'values are "YES" and "NO".

' REPORT_PATHRequiredSpecifies the file path where the job report will

'be written to.

' KEEP_REPORT_FILERequiredSpecifies 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(" **" & 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("

")

' Create summary reports by job status

TempSucc = " <FONT

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

TempSucc = " " & vbCrLf & TempSucc

TempSucc = TempSucc & " " & vbCrLf

TempSucc = TempSucc & "

" & vbCrLf

TempFail = " <FONT

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

TempFail = " " & vbCrLf & TempFail

TempFail = TempFail & " " & vbCrLf

TempFail = TempFail & "

" & vbCrLf

TempCanc = " <TD

WIDTH=150> " & vbCrLf & TempCanc

TempCanc = " " & vbCrLf & TempCanc

TempCanc = TempCanc & " " & vbCrLf

TempCanc = TempCanc & "

" & vbCrLf

TempUnk = " <TD

WIDTH=150> " & vbCrLf & TempUnk

TempUnk = " " & vbCrLf & TempUnk

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(" ")

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(" ")

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(" " & 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.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

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