How to generate a HTML in chart in SQL and send via db mail ?

  • Hello All,

    I'm trying to find guidance on how to create a HTML generated chart off a very simple query that I can then schedule through db mail. I found a video on youtube but its of bad quality and the speaker does not speak English. I know it sounds silly, but i'd like to see how to do it, its kind of one of those things thats not necessary but i'd like to learn how anyway. It would be rendered via outlook 2010.

    (My database mail is completely set up. Just need help on this chart style message)

    Any help appreciated !!!

    SELECT

    j.name AS [Job Name],

    s.step_id AS [Step Number],

    s.step_name AS [Step Name],

    DATENAME(MONTH,msdb.dbo.agent_datetime(run_date, run_time)) AS [Run Month],

    CAST(msdb.dbo.agent_datetime(run_date, run_time) AS DATE) AS [Run Date],

    msdb.dbo.agent_datetime(run_date, run_time) AS [Run Date Time],

    ((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) AS [Run Duration Minutes]

    From msdb.dbo.sysjobs j

    INNER JOIN msdb.dbo.sysjobsteps s

    ON j.job_id = s.job_id

    INNER JOIN msdb.dbo.sysjobhistory h

    ON s.job_id = h.job_id

    AND s.step_id = h.step_id

    AND h.step_id <> 0

    WHERE j.ENABLED = 1

    ORDER BY [Job Name], [Run Date Time] ASC

    ***SQL born on date Spring 2013:-)

Viewing 0 posts

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