SQL 2005 HTML Job Report

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

  • 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

  • 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

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

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

    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

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

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