DECLARE
@tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<html><body><h1>Mail Header</h1>' +
N'<table border="1" width="100%">' +
N'<tr bgcolor="gray"><td>name</td><td>last_run_outcome</td><td>last_run_date</td><td>last_run_time</td><td>last_run_duration</td></tr>' +
CAST((
SELECT
td = name, '',
td = last_run_outcome, '',
td = last_run_date, '',
td = last_run_time, '',
td = last_run_duration, '',
'td/@bgcolor'='Green'
, ''
FROM msdb.dbo.sysjobs_view sjv
INNER JOIN msdb.dbo.sysjobsteps sjs ON sjs.job_id = sjv.job_id
WHERE name in ('JobName')
FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX)) + N'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail @recipients= 'ramachandran.ponnusary@abc.org',
@subject = 'SQL Errors Report',
@body = @tableHTML,
@body_format = 'HTML',
@profile_name = 'ECProfile';
If you have more information use this link http://technet.microsoft.com/en-us/library/ms186358.aspx#SSMSProcedure
SET @tableHTML =
N'<html><body><h1>Mail Header</h1>' +
N'<table border="1" width="100%">' +
N'<tr bgcolor="gray"><td>name</td><td>last_run_outcome</td><td>last_run_date</td><td>last_run_time</td><td>last_run_duration</td></tr>' +
CAST((
SELECT
td = name, '',
td = last_run_outcome, '',
td = last_run_date, '',
td = last_run_time, '',
td = last_run_duration, '',
'td/@bgcolor'='Green'
, ''
FROM msdb.dbo.sysjobs_view sjv
INNER JOIN msdb.dbo.sysjobsteps sjs ON sjs.job_id = sjv.job_id
WHERE name in ('JobName')
FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX)) + N'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail @recipients= 'ramachandran.ponnusary@abc.org',
@subject = 'SQL Errors Report',
@body = @tableHTML,
@body_format = 'HTML',
@profile_name = 'ECProfile';
If you have more information use this link http://technet.microsoft.com/en-us/library/ms186358.aspx#SSMSProcedure
