Configure Database Mail - Send Email From SQL Server Database

We can send an email using the SQL Server. Database mail configuration information is maintained in an MSDB database. It is supporting logging and auditing features, using system tables of MSDB. We can send mail as a text message, HTML, query result, file as an attachment. We have to follow some simple steps to achieve this.

Step 1 Go to Object Explorer.

Step 2 Expand the management menu, as shown below:

menu

Step 3

Right click on database mail and select configure database mail, as shown below:

Configure Database Mail

After selecting “Configure Database Mail”, we will get the screenshot as shown below:

Configure Database Mail

Step 4

Click Next button and after clicking next button; we will get a new screenshot, as shown below: 

Next

Step 5

Select Radio button on the first option “Set up Database Mail by performing the following tasks” and click Next button.

We will get new Screen for setting up the account details for configuring the mail.

Step 6

Enter "Profile name" and "Description", as shown below:

Profile

Step 7

Click ADD button and we will get a new prompt where we can add more details related to the mail setup, as shown below:

  • Account name Enter a name of your new account.

  • Description Enter a description for the account. It is optional.

  • E-mail address Enter your e-mail address, which we will use for sending an e mail, here you can specify your domain email id also as email@yourDomain.com.

  • Display name Enter the name which will use for displaying the name of sender and it is optional.

  • Reply e-mail Enter the reply e-mail address, which will use for replies to e-mail messages sent from this account. It is also optional.

  • Server name Enter the IP address of SMTP server for your e-mail account.
    This server requires a secure connection (SSL) - checked or unchecked as per your e-mail Domain.

  • SMTP Authentication We have to choose one Authentication type among three Authentication types.

    Here, I am using my Gmail account credentials to configure the mail setup. In most cases, we are using company account.

    new

Step 8

Click OK. This screen will close and the previous screen is shown below:

new

Step 9

Click Next and we will get the prompt.

Step 10

Check the checkbox on “TestMailProfile” and make it as default profile, as shown below:

TestMailProfile

Step 11 Click Next and we will get a new screen.

Step 12 Keep default setting for the system parameters and click Next button, as shown below:

default setting

Step 13 Click Finish button to compete the configuration, as shown below:

Finish

Step 14 It will do all the configurations and then click close button.

configurations

Now, we are done with the mail configuration. We will test this to send a sample mail, with the help of the following steps.

Step 1 Go to Object Explore

Step 2 Expand the “Management” menu

Step 3 Right Click “Database Mail”

Step 4 Click “send Test E-Mail”, as shown below:

send Test E-Mail

After clicking “send Test E-Mail”, we will get new screen.

Step 5

Database Mail Profile: Select “TestMailProfile”, as we created just now.

  • To Enter an e-mail Id of receiver
  • Subject Enter subject of your e-mail.
  • Body Enter content of your mail.

Click the “Send Test E-mail” button, as shown below:

Send Test E-mail

Email will be sent to recipient successfully.

After successfully configuring the Email in SQL server, we will see how to send Email programmatically, with the help of a system procedure.

We are using system procedure “sp_send_dbmail” to send an E-mail.

We can see the “sp_send_dbmail” system procedure by using “sp_helptext sp_send_dbmail”

Query will be written as shown below:

Query

We will send the parameters to “sp_send_dbmail” system procedure, as per our requirement.

Here, I am using parameters shown below to send an E-mail. 

  1. use msdb  
  2. go  
  3. EXEC msdb.dbo.sp_send_dbmail  
  4. @profile_name = 'TestMailProfile',  
  5. @recipients = 'vivek1273@gmail.com',  
  6. @subject = 'DataBase Mail Test',  
  7. @body = 'This is a test e-mail.';   
  • Profile name We have to write profile name which we created now.
  • Recipients we have to write recipient email. We can write multiple recipients e-mail id by separating with ‘;’
  • Subject  We have to write subject of the e-mail.
  • Body We have to write body of the e-mail

We can also verify our E-mail status, whether it will successfully send or not and get other information using the query given below:

  1. use msdb  
  2. go  
  3. select * from sysmail_allitems  
query

We can also see the database mail log, as shown below:

Mail Log

After clicking “View database Mail Log”, we will get the information about database mail log, as shown below:

View database Mail Log

In this article, I used my Gmail credentials to send an E-mail. You can use your SMTP Server to send an E-mail, using SQL Server.

Below are a few SMTP Server Details for your reference.

Mailing Account SMTP Server Name Port Number
Gmail smtp.gmail.com 587
Hotmail smtp.live.com 587
Yahoo smtp.mail.yahoo.com 25
AOL smtp.aol.com 587