Problem:
How to send an email from Database Mail.?
If we need to send alerts, notifications,
Job status of a SQL Server agent from SQL Server Database. You may have to
configure Database Mail to send e-mail message to concerned persons as it keeps
notifying when there is a fatal error or failure of a SQL job. So, it keeps you
aware of the process even though you are not on the computer.
After the configuration we can
send an email using sp_send_dbmail.
This system procedure has few
parameters that needs to passed in order to send an email to a particular
person.
You can pass file attachments,
query results and etc.
Example:
Example 1: In this example I would
like to send a test email to a specified recipient using a profile that already
exists.
EXEC msdb.dbo.sp_send_dbmail
@recipients='mahesh.sambu@gmail.com',
@profile_name='DB_Mail Public Profile',
@subject
= 'email sent from
database engine’,
@body
= 'This email is sent
in order to test the email server' ;
Example 2:
In this article I am going to show you we can send an email to send a
status update of a SQL job as a table format in HTML.
DECLARE @tableHTML NVARCHAR(MAX) ;
DECLARE @job_id UNIQUEIDENTIFIER,
@jobname SYSNAME = 'syspolicy_purge_history'
SELECT @job_id = job_id
FROM msdb.dbo.sysjobs
WHERE [name] = @JobName
SET @tableHTML =
N'<H1>Job
History Report</H1>' +
N'<table
border="1">' +
N'<tr><th>Job_name</th><th>Step_name</th>'
+
N'<th>Jobfailedatstep</th><th>rundate</th><th>runtime</th>'
+
N'<th>runduration</th><th>logoutput</th></tr>'
+
CAST ( (
SELECT td=sj.name
, '',
td = JS.step_name,'',
td='Step ' + CAST(JH.step_id AS CHAR(3)) + ' of ' + CAST(MAX(js.step_id) OVER (ORDER BY (SELECT 0)) as char(3)) ,'',
td =CONVERT(date, left(jh.run_date, 8)), '',
td =CAST(dateadd(hour, (jh.run_time / 10000) % 100, dateadd(minute, (jh.run_time / 100) % 100, dateadd(second, (jh.run_time / 1) % 100, cast('00:00:00' AS TIME(2)))))AS char(8)), '',
td =JH.run_duration ,'',
td =CASE WHEN JSL.[log] IS NULL THEN JH.[Message]
ELSE JSL.[log] END,''
FROM msdb.dbo.sysjobs
sj
INNER JOIN msdb.dbo.sysjobsteps JS
ON js.job_id=sj.job_id
INNER JOIN msdb.dbo.sysjobhistory JH
ON
JS.job_id = JH.job_id
AND JS.step_id = JH.step_id
LEFT JOIN msdb.dbo.sysjobstepslogs JSL
ON
JS.step_uid =
JSL.step_uid
WHERE
JS.step_id
<> 0
AND
JH.job_id =
@job_id
--AND
JH.run_status = 0
ORDER BY Jh.run_date
desc,Jh.run_time desc
FOR
XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>'
;
EXEC msdb.dbo.sp_send_dbmail
@recipients='mahesh.sambu@gmail.com',
@profile_name='DB_Mail Public Profile',
@subject = 'job history',
@body =
@tableHTML,
@body_format =
'HTML' ;
0 comments:
Post a Comment