Email results of Failed Jobs

  • Thanks you for your time and any help or questions are appreciated.
     Currently i have approximately 300+ jobs...and trying to get somethings that will alert me in an email when THIS job completes it sends.
    How do i take the results from the following code  and have it emailed in an automated fashion... The results come out perfectly in SSMS and would like the "information" to be made in a readable fashion in the emails that would be sent.

    The following code is below
    USE MSDB

    -- Variable Declarations
    DECLARE @PreviousDate datetime
    DECLARE @Year VARCHAR(4)
    DECLARE @Month VARCHAR(2)
    DECLARE @MonthPre VARCHAR(2)
    DECLARE @Day VARCHAR(2)
    DECLARE @DayPre VARCHAR(2)
    DECLARE @FinalDate INT
    -- Initialize Variables
    SET @PreviousDate = DATEADD(dd, -30, GETDATE()) -- Last 30 days
    SET @Year = DATEPART(yyyy, @PreviousDate)
    SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))
    SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2)
    SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))
    SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2)
    SET @FinalDate = CAST(@Year + @Month + @Day AS INT)
    -- Final Logic pulling data from MSDB
    SELECT  TOP 50
            j.[name],
             s.step_name,
             --h.step_id,
             --h.step_name,
             h.run_date,
             h.run_time,
             --h.sql_severity,
            -- h.message,
             h.server
    FROM     msdb.dbo.sysjobhistory h
             INNER JOIN msdb.dbo.sysjobs j
               ON h.job_id = j.job_id
             INNER JOIN msdb.dbo.sysjobsteps s
               ON j.job_id = s.job_id
               AND h.step_id = s.step_id
    WHERE    h.run_status = 0 -- Failure  *****
             AND h.run_date > @FinalDate
    --ORDER BY h.instance_id
    ORDER BY h.run_date DESC 

    Would like to be able to send the email at this point here so then i can automate this job to run about every 30 minutes.

    All assistance is greatly appreciated...Thanks

    Dheath

    DHeath

  • Hi,
    this is quite simple:
    https://www.brentozar.com/archive/2014/10/send-query-results-sql-server-agent-job/
    Brent Ozar did a good job to explain.
    Kind regards,
    Andreas

  • Thanks for the reply...

    Ok..hmmm maybe i didnt do the best job saying what i needed....
    I understand how to use the  DBMAIL  ASPECTS..this part i get fairly good... I am okay here

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Main DBA profile',
    @recipients = 'SQLDBA@company.com',
    @subject = 'Failed SQL Agent Jobs',
    @query = N'SELECT TOP 50
       j.[name],
       s.step_name,
       --h.step_id,
       --h.step_name,
       h.run_date,
       h.run_time,
       --h.sql_severity,
       -- h.message,
       h.server
    FROM  msdb.dbo.sysjobhistory h
       INNER JOIN msdb.dbo.sysjobs j
        ON h.job_id = j.job_id
       INNER JOIN msdb.dbo.sysjobsteps s
        ON j.job_id = s.job_id
        AND h.step_id = s.step_id
    WHERE  h.run_status = 0 -- Failure *****
       AND h.run_date > @FinalDate
    --ORDER BY h.instance_id
    ORDER BY h.run_date DESC   ---
    CAST(collection_date_time AS Date) = CAST(GETDATE() AS DATE)
    ORDER BY collection_date_time DESC;',
    @attach_query_result_as_file = 1,@query_attachment_filename = 'Memory Values.txt'

    The PROBLEM comes where i try to CAPTURE the results...

    SELECT TOP 50
       j.[name],
       s.step_name,
       --h.step_id,
       --h.step_name,
       h.run_date,
       h.run_time,
       --h.sql_severity,
       -- h.message,
       h.server
    FROM  msdb.dbo.sysjobhistory h
       INNER JOIN msdb.dbo.sysjobs j
        ON h.job_id = j.job_id
       INNER JOIN msdb.dbo.sysjobsteps s
        ON j.job_id = s.job_id
        AND h.step_id = s.step_id
    WHERE  h.run_status = 0 -- Failure *****
       AND h.run_date > @FinalDate

    DHeath

  • Why wouldn't you use an INSERT/SELECT into a table and create the email from those results?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff...

    First off thank you for the post...much appreciated.

    I am not the greatest code writer so i  have some  problems getting to that point of inserting into the table.  I was able to finally get that to happen by using the insert into but then the format of the data was not very good.  Yes i could import to an  excel table then email but my goal is to make it 100% automated.  I did get the notifications to work from "failed" jobs but that doesnt give me all the information i want plus if i have 10 failed jobs then thats 10 emails and trying to make it all one email (IF that makes sense).
    I am sure this can be done..its just a matter of me figuring it out LOL

    DHeath

    DHeath

  • I use a version of the query below every M-F:
    it returns all jobs who's LAST status is failed.
    that means jobs that execute every x minutes,and experienced a hiccup, but ran successfully after wards are ignored.

    In my case, i aggregate multiple servers , so i have a consolidated report, so you would need PowerShell or an SSIS package to do that, or run this in a Central Management Servers Query manually to agregate, but for the email from a single server, for all errors,t his would help, i think.

    hope this helps:
    * edit: modified to now include the email portion.

    DECLARE @dt CHAR(8);
    SET @dt= CASE
            WHEN DATENAME(dw,GETDATE()) = 'Monday'
            THEN CONVERT(CHAR(8), (SELECT DATEADD (DAY,(-3), GETDATE())), 112)
            ELSE CONVERT(CHAR(8), (SELECT DATEADD (DAY,(-1), GETDATE())), 112)
            END

    Declare @HTMLBody varchar(max),
      @TableHead varchar(max),
      @TableTail varchar(max)

    Set NoCount On;

    Set @TableTail = '</table></body></html>';
    Set @TableHead = '<html><head>' +
          '<style>' +
          'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +
          '</style>' +
          '</head>' +
          '<body><table cellpadding=0 cellspacing=0 border=0>' +
          '<tr bgcolor=#FFEFD8><td align=center><b>Servername</b></td>' +
          '<td align=center><b>Step Name</b></td>' +
          '<td align=center><b>SQL Job Name</b></td>' +
          '<td align=center><b>Failure Date</b></td>' +
          '<td align=center><b>RunDateTime</b></td>' +
          '<td align=center><b>StepDuration</b></td>' +
          '<td align=center><b>ExecutionStatus</b></td>' +
          '<td align=center><b>Error Message</b></td></tr>';

    Select @HTMLBody = (Select
            ROW_NUMBER() Over(Order By CONVERT(DATETIME,CAST(run_date AS CHAR(8)),101)) % 2 As [TRRow],
            CONVERT(varchar(128),@@SERVERNAME) As

    ,
            T1.step_name AS

    ,
            SUBSTRING(T2.name,1,140) AS

    ,
            CAST(CONVERT(DATETIME,CAST(run_date AS CHAR(8)),101) AS CHAR(11)) AS

    ,
            msdb.dbo.agent_datetime(T1.run_date, T1.run_time) AS

    ,
            T1.run_duration AS

    ,
            CASE T1.run_status
            WHEN 0 THEN 'Failed'
            WHEN 1 THEN 'Succeeded'
            WHEN 2 THEN 'Retry'
            WHEN 3 THEN 'Cancelled'
            WHEN 4 THEN 'In Progress'
            END AS

    ,
            T1.message AS

            FROM
            msdb..sysjobhistory T1 INNER JOIN msdb..sysjobs T2 ON T1.job_id = T2.job_id
            WHERE
            T1.run_status NOT IN (1,2,4)
            AND T1.step_id != 0
            AND run_date >= @dt
              For XML raw('tr'), Elements)

    -- Replace the entity codes and row numbers
    Set @HTMLBody = Replace(@HTMLBody, '_x0020_', space(1))
    Set @HTMLBody = Replace(@HTMLBody, '_x003D_', '=')
    Set @HTMLBody = Replace(@HTMLBody, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
    Set @HTMLBody = Replace(@HTMLBody, '<TRRow>0</TRRow>', '')

    Select @HTMLBody = @TableHead + @HTMLBody + @TableTail

    -- return output
    Select @HTMLBody
    --email the output:

    EXEC msdb.dbo.sp_send_dbmail
      --@profile_name='Specific Profile name',
      @recipients='lowelle@SomeDomain.com',
      @subject = 'DailyErrors',
      @body = @HTMLBody,
      @body_format = 'HTML'
     

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell,

    Thanks a TON!!!! and the formatting is spot on as well GREATLY APPRECIATED.  I will use a CMS so that it can hit all servers at once.  Thanks again.

    DHeath

    DHeath

Viewing 7 posts - 1 through 6 (of 6 total)

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