Sending an email from Database engine using database mail profile.

Problem: How to send an email from Database Mail.?

Solution: sp_send_dbmail

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' ; 

OUTPUT:

Please go through this article Configure Mail profile, on how to configure a mail account, profile.