There are times when we want to send email using SQL server after a job has been accomplished. For instance, in a company every month on a certain date and time updates are to be made via web service and once that is accomplished a notification is sent to Database administrator. In this scenario, leveraging database email would be more manageable then using a third party tool or creating any application for that matter. Most commonly it is utilized for sending email after periodic Database backup has been performed. Database email can be used to send attachments to the receiver. It is important to know that Database Mail used SMTP protocol for communication. It can support more than one SMTP server and SMTP user accounts and profile. It is important to understand as how database email operates, image below gives a high level view of process flow.
So let's get started to send our first database email.
- Database mail is disabled by default so our first step in the process is Configuring the database mail. We will have to open up our SQL server Management studio and under Management right click on Database Mail.
2. On selecting Configure Database Mail a new Welcome wizard window will open as shown below. Click on Next.
3. Next screen presents with four different options. Since, we are setting this up for the first time we will choose first option. Below is a brief summary as what each of these options describes.
a. Set up Database Mail: Here we can set new profile and assign it to a new account either by creating a new account or to an already existing account. We can manage the security of profile as well.
b. Clicking Next will bring below screen. Give a name to your profile, a brief description and click Add as shown below:
c. On adding new account below screen will be displayed. If you already have an account that you want to leverage for this purpose, you can select from the dropdown but if this is the first time creating the account, click on New Account button.
d. Database server will use the account that we will create here to communicate to SMTP server for sending the email. For this purpose we need to have an account created in SMTP server whose credentials we can pass on to DB server. In my example, I have an email account (firstname.lastname@example.org) already created in my SMTP mail server for sending the email from database server.
Account Name: Give a name to the account that we are creating and it can be anything we want.
Description: A brief description for better manageability
E-mail address: Email account in which you want to get reply. This is an optional field.
By default the port where SMTP listens is 25.
If you want email transmission requires a SSL layer then check on the checkbox.
Under SMTP Authentication there are three different options.
Windows Authentication using Database Engine service credentials will use the credentials from the database engine itself.
Basic Authentication: This is most common in use and requires the credentials of the SMTP email account which we are using to send emails.
Anonymous Authentication: This is used when SMTP does not require credentials. This is not safe and is not recommended.
The one we are using in our example is Basic Authentication.
e. Click Next and below will appear where you click Finish
4. Next Screen will have this new account assigned to the profile that we are creating. If you want you can add or remove more account to this by clicking on Add as shown below. It is beneficial to have more than one profile, so in case if the one fails the other can still be used to relay.
5. Click Next and then Finish
Now we are done creating our profile and assigning it to an Account. It is time we send the real email now.
msdb.dbo.sp_send_dbmail is the procedure that does the work. It has several parameters that are helpful in formating our email. For more information and complete list of parameter that can be passed please refer to MSDN online http://msdn.microsoft.com/en-us/library/ms190307.aspx
Place the below code in SQL Server management Studio and run Execute
@email@example.com', --This is not the real email address please use a genuin email to test the same
@body='This is my first email test',
@subject='test email from DevTechie'
If we want to send an email from having the result of a query in the attachment then that can be accomplished as below.
@body='This is my email that contains attachment from query',
@subject='test email from DevTechie',
@query='Select top(10)* from DummyDB.dbo.table_name ',
You can see the log of the email sent by running dbo.sysmail_event_log table
select * from dbo.sysmail_event_log order by last_mod_date desc