Home > SQL Scripts > Send Jobs Status Report using DB Mail as HTML

Send Jobs Status Report using DB Mail as HTML

 

It is really a herculean task to have a consolidated report for all the jobs status for one or more server. My much time is saved by this report

Steps to Create Report

Configure Database Mail Profile, which will be used to send mail.

  1. Create Table for Job History

    USE [msdb]

    GO

    /****** Object: Table [dbo].[JobHistory] ******/

    SET
    ANSI_NULLS
    ON

    GO

    SET
    QUOTED_IDENTIFIER
    ON

    GO

    SET
    ANSI_PADDING
    ON

    GO

    CREATE
    TABLE [dbo].[ServerJobHistory](

    [Job_ID] [uniqueidentifier] NOT
    NULL,

    [Job_Name] [sysname] NOT
    NULL,

    [Run_Date] [int] NOT
    NULL,

    [Run_Time] [int] NOT
    NULL,

    [Run_Duration] [int] NOT
    NULL,

    [Job_Outcome] [varchar](100)
    NOT
    NULL,

    [Step_Outcome] [varchar](250)
    NOT
    NULL

    )
    ON [PRIMARY]

    GO

    SET
    ANSI_PADDING
    OFF

    GO

  2. Create Stored Proc to pull Job Information from msdb Job Table into above created Table

     

USE [msdb]

GO

/****** Object: StoredProcedure [dbo].[sp_LoadJobHistory] Script Date: 03/07/2011 17:48:23 ******/

SET
ANSI_NULLS
ON

GO

SET
QUOTED_IDENTIFIER
ON

GO

ALTER
PROCEDURE [dbo].[sp_LoadJobHistory]

AS

    BEGIN

        SET
NOCOUNT
ON

        DECLARE @JobID UNIQUEIDENTIFIER

        DECLARE @JobName SYSNAME

        DECLARE @RunDate INT

        DECLARE @RunTime INT

        DECLARE @RunDuration INT

        DECLARE @RunStatus INT

        DECLARE @FailedSteps INT

        DECLARE @JobOutcome VARCHAR(100)

        DECLARE @StepOutcome VARCHAR(250)

        DECLARE JobHistoryCursor CURSOR
LOCAL
READ_ONLY
FOR

        SELECT J.job_id,

            J.[name],

            JH.[run_date],

            JH.[run_time],

            ((JH.[run_duration]/10000)*3600)
+
(((JH.[run_duration]/100)



(JH.[run_duration]/10000)
* 100)
* 60)
+


(JH.[run_duration]%100) [run_duration (sec)],

            JH.[run_status]

        FROM dbo.sysjobhistory JH

            JOIN dbo.sysjobs J

            ON J.job_id = JH.job_id

        WHERE JH.[job_id] IN
(

            SELECT [job_id] FROM dbo.sysjobs

        )

        AND
CONVERT(VARCHAR(10), JH.[run_date])
+
CONVERT(VARCHAR(10), JH.[run_time])
NOT
IN

        (

            SELECT
CONVERT(VARCHAR(10), IJH.[run_date])
+
CONVERT(VARCHAR(10), IJH.[run_time])

            FROM dbo.ServerJobHistory IJH

            WHERE IJH.Job_ID = JH.[job_id]

        )

        AND JH.[step_id] = 0

        OPEN JobHistoryCursor

        FETCH
NEXT
FROM JobHistoryCursor INTO @JobID, @JobName, @RunDate, @RunTime, @RunDuration, @RunStatus

        WHILE
@@FETCH_STATUS
= 0

        BEGIN

            SELECT @FailedSteps =
COUNT(*)

            FROM dbo.sysjobhistory H

            WHERE H.job_id = @JobID

            AND H.run_date = @RunDate

            AND H.run_time/100 = @RunTime/100

            AND H.step_id > 0

            AND H.run_status = 0

            IF (@RunStatus = 0)

                SELECT @JobOutcome =
‘Failed’, @StepOutcome = ‘One or more steps failed’

            ELSE
IF (@RunStatus = 1 AND @FailedSteps > 0)

                SELECT @JobOutcome =
‘Succeeded (with errors)’, @StepOutcome = ‘One or more steps failed’

            ELSE
IF (@RunStatus = 1 AND @FailedSteps = 0)

                SELECT @JobOutcome =
‘Succeeded’, @StepOutcome = ‘All steps succeeded’

            ELSE
IF (@RunStatus = 3)

                SELECT @JobOutcome =
‘Cancelled by user’, @StepOutcome =

            ELSE

                SELECT @JobOutcome =
‘Unknown or Retrying’, @StepOutcome = ‘Please check job history’

            INSERT
INTO dbo.ServerJobHistory
(Job_ID, Job_Name, Run_Date, Run_Time, Run_Duration, Job_Outcome, St
ep_Outcome)

                VALUES (@JobID, @JobName, @RunDate, @RunTime, @RunDuration, @JobOutcome, @StepOutcome)

            FETCH
NEXT
FROM JobHistoryCursor INTO @JobID, @JobName, @RunDate, @RunTime, @RunDuration, @RunStatus

        END

        CLOSE JobHistoryCursor

        DEALLOCATE JobHistoryCursor

    END

 

 

 

  1. Create Procedure to send Job Status in Mail using DB Mail
USE [msdb]

GO

SET
ANSI_NULLS
ON

GO

SET
QUOTED_IDENTIFIER
ON

GO

ALTER
PROCEDURE [dbo].[DBA_sp_JobHistoryMailer]

AS

BEGIN

    DECLARE @tableHTML NVARCHAR(MAX)

    SET @tableHTML =

        N'<html><body><Font Face=”Verdana” Size=4><b>Server Job History Report</b></Font><p>’
+

        N'<Font Face=”Verdana” Size=2>Report generated on: ‘
+
CONVERT(VARCHAR(11),
GETDATE(), 113)
+
‘</Font><br>’
+

        N'<Font Face=”Verdana” Size=2>This report shows yesterday”s history only.</Font><p>’
+

        N'<table border=”1″width=”100%”><tr bgcolor=”Black”>’
+

        N'<td><Font Face=”Verdana” Size=2 Color=”White”><b>Job Name</b></Font></td>’
+

        N'<td><Font Face=”Verdana” Size=2 Color=”White”><b>Last Run Date</b></Font></td>’
+

        N'<td><Font Face=”Verdana” Size=2 Color=”White”><b>Last Run Time</b></Font></td>’
+

        N'<td><Font Face=”Verdana” Size=2 Color=”White”><b>Last Run Duration (sec)</b></Font></td>’
+

        N'<td><Font Face=”Verdana” Size=2 Color=”White”><b>Last Run Status</b></Font></td>’
+

        cast
((SELECT

                td =
‘<Font Face=”Verdana” Size=2 Color=”Black”>’
+
CONVERT(VARCHAR(250), Job_Name)
+
‘</Font>’,,

                td =
‘<Font Face=”Verdana” Size=2 Color=”Black”>’
+
CONVERT(VARCHAR(250), Run_Date)
+
‘</Font>’,,

                td =
‘<Font Face=”Verdana” Size=2 Color=”Black”>’
+
CONVERT(VARCHAR(250), Run_Time)
+
‘</Font>’,,

                td =
‘<Font Face=”Verdana” Size=2 Color=”Black”>’
+
CONVERT(VARCHAR(250), Run_Duration)
+
‘</Font>’,,

                Case Job_Outcome

                    WHEN
‘Succeeded’
THEN
‘<Font Face=”Verdana” Size=2 Color=”Green”>’
+
CONVERT(VARCHAR(250), Job_Outcome)
+
‘</Font>’

                    WHEN
‘Failed’
THEN
‘<Font Face=”Verdana” Size=2 Color=”Red”>’
+
CONVERT(VARCHAR(250), Job_Outcome)
+
‘</Font>’

                    ELSE
‘<Font Face=”Verdana” Size=2 Color=”Orange”>’
+
CONVERT(VARCHAR(250), Job_Outcome)
+
‘</Font>’

                END
as td

                     FROM dbo.ServerJobHistory

         WHERE Run_Date =
REPLACE(CONVERT(VARCHAR(10),
DATEADD(dd, 0, GETDATE()), 121),
‘-‘,)

         ORDER
BY Job_Name

     FOR
XML
PATH(‘tr’),
TYPE
)
AS
NVARCHAR(MAX)
)
+
N'</table></body></html>’

     SET @tableHtml =
REPLACE(REPLACE(@tableHtml,‘&gt;’,‘>’),‘&lt;’,‘<‘)

    SELECT @tableHtml

    IF (ISNULL(@tableHtml,
‘NULL’))
!= ‘NULL’

        BEGIN

            EXEC msdb.dbo.sp_send_dbmail
@profile_name =
‘DBProfileName’,

                @recipients=‘xyz@xyz.com’,

                @subject=‘Job History Report’,

                @body=@tableHTML,

                @body_format=‘HTML’

        END

END

 

 

 

Report Looks as below

 

Categories: SQL Scripts
  1. Anonymous
    January 31, 2013 at 7:13 pm

    Thank you for the scripts. It works beautifully.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: