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

Question

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

Nisarg Upadhyay

SSC-Addicted

Points: 460
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

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

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

MMartin1 One Orange Chip Points: 27795 More actions · Answer 1

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: 2727 More actions · Answer 2

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 · Answer 3

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: 450 More actions · Answer 4

Hi Nisarg

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