Untitled

DECLARE @email_profile NVARCHAR(256) = 'kreirani operator na sql bazi'
DECLARE @emailrecipients NVARCHAR(500) = 'mail'
declare @clientName nvarchar(64) = 'projekt'
declare @deltaMinutes int = 120
DECLARE @subject NVARCHAR(MAX) = '['+@clientName+'] SQL Server Agent Job Failure Report: ' + @@SERVERNAME

--Dump report data to a temp table to be put into XML formatted HTML table to email out
SELECT sjh.[server]
    ,sj.NAME
    ,sjh.step_id
       ,sjh.step_name
    ,sjh.[message]
    ,sjh.run_date
    ,sjh.run_time
       ,msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime'
    ,   STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(sjh.run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') as run_duration
into #TempJobFailRpt
FROM msdb..sysjobhistory sjh
INNER JOIN msdb..sysjobs sj ON (sj.job_id = sjh.job_id)
WHERE 1=1
    and msdb.dbo.agent_datetime(run_date, run_time) > dateadd(minute, -1*@deltaMinutes, getdate())
    AND run_status != 4 -- Do not show status of 4 meaning in progress steps
    AND run_status != 1 -- Do not show status of 1 meaning success
    and sjh.step_id<>0
ORDER BY sjh.run_time desc, sjh.step_id


IF EXISTS (SELECT top 1 * FROM #TempJobFailRpt)
BEGIN

-----Build report to HTML formatted email using FOR XML PATH
DECLARE @tableHTML NVARCHAR(MAX) = '
<html>
<head>
<style>
</style>
</head>
<body>
<table border="1" style="border-collapse: collapse">
<tr>
    <th style="width:10%; border: 1px solid #dddddd; text-align: left; padding: 8px;  text-decoration: underline">SQL Instance</th>
    <th style="border: 1px solid #dddddd; text-align: left; padding: 8px; text-decoration: underline">Job Name</th>
    <th style="border: 1px solid #dddddd; text-align: center; padding: 8px; text-decoration: underline">Step</th>
    <th style="border: 1px solid #dddddd; text-align: justify; padding: 8px; text-decoration: underline">Error Details</th>
    <th style="width:15%; border: 1px solid #dddddd; text-align: left; padding: 8px; text-decoration: underline">Job Run Date</th>
    <th style="border: 1px solid #dddddd; text-align: left; padding: 8px; text-decoration: underline">Job Run Duration (DD:HH:MM:SS)</th>
</tr>' + CAST((
            SELECT td = [server]
                ,''
                ,td = NAME
                ,''
                ,td = step_id
                ,''
                ,td = [message]
                ,''
                ,td = RunDateTime

                ,''
                ,td = run_duration


            FROM #TempJobFailRpt a
            ORDER BY run_time desc, step_id


            FOR XML PATH('tr')
                ,TYPE
                ,ELEMENTS XSINIL
            ) AS NVARCHAR(MAX)) + '
    </table>
</body>
</html>';

EXEC msdb.dbo.sp_send_dbmail @profile_name = @email_profile
    ,@recipients = @emailrecipients
    ,@subject = @subject
    ,@body = @tableHTML
   ,@body_format = 'HTML'

--Drop Temp table
    DROP TABLE #TempJobFailRpt
END
ELSE
BEGIN
    PRINT '*** No Records Generated ***'
    DROP TABLE #TempJobFailRpt
END