Create an HTML Report on the Status of SQL Server Agent Jobs

• Nisarg Upadhyay

  SSC-Addicted

  Points: 495

  More actions

  May 3, 2023 at 12:00 am

  #4180615

  Comments posted to this topic are about the item Create an HTML Report on the Status of SQL Server Agent Jobs

• MMartin1

  One Orange Chip

  Points: 27840

  More actions

  May 3, 2023 at 6:35 am

  #4183325

  Thank you for breaking things down so well. This is well put. I do recall coming across the ability to email HTML reports from the SQL Server agent in the past, for I have used the sp send mail stored procedure. (There are some security considerations here as I recall).

  Nowadays we use a third party tool that is constantly reading logs and part of which is Agent Job outcomes, which then alerts the DBA on call in real time should a step fail or any problem be encountered.

  ----------------------------------------------------

• Lee Linares

  SSCrazy

  Points: 2730

  More actions

  May 3, 2023 at 2:21 pm

  #4183600

  Thank you for posting this solution. However, I ran into an issue and received. this error:

  Msg 241, Level 16, State 1, Line 1

  Conversion failed when converting date and/or time from character string

  The line that caused it was:  ,msdb.dbo.agent_datetime(sch.next_run_date, sch.next_run_time) NextRunDate

  I replaced it with:

  ,CASE sch.next_run_date WHEN 0 THEN NULL ELSE msdb.dbo.agent_datetime(sch.next_run_date, sch.next_run_time) END AS NextRunDate

  That seemed to fix it. Thanks again.

   

• dawaller

  SSC Veteran

  Points: 213

  More actions

  May 3, 2023 at 3:28 pm

  #4183614

  Hi,

  I looked through the article and don't see any link to the script? Is it included somewhere or do we put together ourselves? I like the idea and want to try it out.

• artilugio

  SSC-Addicted

  Points: 451

  More actions

  May 3, 2023 at 6:18 pm

  #4183686

  Hi Nisarg

  This is a nice all SQL way to create a very useful report, thanks for posting it