Sending email is a routine task needed in any application. Emails used for sending an alert, message, query result, attachment or any other notification to users. SQL Server uses the Database Mail component to configure and send emails. Using this back end component rather than a frontend application for sending email is hassle-free. Giving more control to manage security and logging.
Database Mail
Database mail component provides functionality to create different profiles & accounts for sending emails. In addition to this, profile includes SMTP server account and configurations needed for sending emails. Configurations can be from SSMS GUI Object explorer. Available under Management > Database Mail.
Configuring Database Mail In SQL Server
In brief, database Mail configuration in SQL server includes the SMTP account setup. Configured email id will be used to send emails. Besides, the easiest option to create database mail profile using SSMS GUI. Another handy option is to run system stored procedures to configure database email.
Detail Guide
1.Navigate to Object Explorer and open Management folder and choose Database Mail.
2.Right click on Database Mail, select Configure Database Mail.
3.On the Configuration Task page, choose “Set up Database Mail by performing the following tasks”. To create a new database email profile.
4.On New Profile page, give the Profile name and Description and click on Add…
5.Select New Account and mention the configuration details for your SMTP server.
6.On the next page, select the profile name for security configurations.
7.On configure system parameters page, change the values as per requirement. You may keep the default security parameters.
8.Click on finish, and new database email profile is added successfully.
Let’s use the Test Email Profile created using the above instructions to send email in SQL Server. Assuming we’ve configured [email protected] in the profile name.
1.
Send Simple Email to Single Recipient
Using sp_send_dbmail system stored procedure to send a simple email to [email protected]. Using Test Emal Send profile.
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Test Email Send' ,@recipients = '[email protected]' ,@subject = 'Regarding Sales Team Meeting' ,@body = 'Hi There, Decision has been made. Regards, Vish';
2.
Send Email To Multiple Recipients
In this example, we’re sending email to multiple email ids. The @recipients parameter accepts semi-column delimited list of email ids.
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Test Email Send' ,@recipients = '[email protected];[email protected]' ,@subject = 'Regarding Sales Team Meeting' ,@body = 'Hi There, Decision has been made. Regards, Vish';
3.
Send Email With an Attachment
Here using @file_attachments parameter to pass the absolute path of attachment file. Multiple attachments can be specified as semicolon delimited list.
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Test Email Send' ,@recipients = '[email protected];[email protected]' ,@subject = 'Regarding Sales Team Meeting' ,@body = 'Hi There, Decision has been made. Regards, Vish' ,@file_attachments = 'D:\PolicyDetails\PolicyWordings.pdf';
4.
Send Email With Query Result as Attachment
In the following example, using @attach_query_result_as_file to send the query result as an attachment. @attach_query_result_as_file parameter accepts 1 or 0 value as input.
If @attach_query_result_as_file = 0 then query result will be appended to the email body. In case @attach_query_result_as_file = 1 then query result will be sent as an attachment.
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Test Email Send' ,@recipients = '[email protected];[email protected]' ,@subject = 'Regarding Policy Count - Yesterday' ,@body = 'Refer Policy Count ' ,@query = 'SELECT COUNT(*) Policy WHERE CreatedDate >= DATEADD(day,DATEDIFF(day,1,GETDATE()),0) AND CreatedDate < DATEADD(day,DATEDIFF(day,0,GETDATE()),0)' ,@attach_query_result_as_file = 1;
5.
Send HTML Email In SQL Server
In the following example, using HTML to format email message. Using a @HTML_Body variable to set up the body first in HTML format using tags. @body_format = ‘HTML’ command SQL Server to parse this email and send it in HTML format.
DECLARE @HTML_Body as NVARCHAR(500) = 'Hi There, <h4> Heading </h4> </br> Refer report. <b> Regards </b>'; EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Test Email Send' ,@recipients = '[email protected];[email protected]' ,@subject = 'Regarding Policy Count - Yesterday' ,@body = @HTML_Body ,@body_format = 'HTML';
Summary
In this article, we have leared to configure Databaes mail in SQL Server. Using system stored procedures to send email to single & multiple recipients. With query result as attachment along with HTML emails. If you like this article you may want to explorer Tech-Recipes Database archieve.
