Sometimes, we are requested to provide certain information from a database via e-mail. If the request is repeated frequently, it is difficult to repeat this annoying similar process. For this reason, you should regularly send emails with predefined content.

Database Mail, as you would expect from its name, is a solution for sending e-mail messages from the SQL Server Database Engine to users. Using Database Mail, database applications can send e-mail messages that can, for example, contain query results or simply alert a user about an event that occurred in the database. 

The process of Database Mail configuration has three main steps. In order to complete this successfully, we need to:

  • Enable Database Mail
  • Create a Database Mail profile and account,
  • Configure those two to work together

Enabling Database Mail in SQL Server

We’ll use T-SQL to allow Database Mail. To enable Database Mail, run the following code:

If it really works, you’re good to go. In this case, you might hit an error informing you that ‘Database Mail XPs don’t exist. This is going to happen from time to time because this is an advanced feature. To fix this, we need to change the show advanced options default value from 0 to 1.

Configuring Database Mail Profile and Account

Now we have to set up the e-mail profile and add an e-mail account. To do this we will use some stored procedures in msdb database. Here, we create a new Database Mail profile named ‘Notifications’. we will use the sysmail_add_profile_sp stored procedure and the following code:

To grant permission for a database user or role to use this Database Mail profile, we will use the sysmail_add_principalprofile_sp stored procedure and the following code:

To create a new Database Mail account holding information about an SMTP account, we will use the sysmail_add_account_sp stored procedure and the following code:

To add the Database Mail account to the Database Mail profile, we will use the sysmail_add_profileaccount_sp stored procedure and the following code:

Use the Database Mail Configuration Wizard

You can configure Database email very easily by using a wizard. To use wizard, please follow the below steps:

  1. In Object Explorer, expand the node for the instance where you want to configure Database Mail.
  2. Expand the Management
  3. Right-click Database Mail, and then select Configure Database Mail.
    1. Account name: Type the name of the new account.
    2. Description: Type a description of the account. The description is optional.
    3. E-mail address: Type the name of the e-mail address for the account. This is the e-mail address that the e-mail is sent from. For example, an account for SQL Server Agent may send e-mail from the address [email protected].
    4. Display name: Type the name to show on e-mail messages sent from this account. The display name is optional. This is the name displayed on messages sent from this account. For example, an account for SQL Server Agent may display the name “SQL Server Agent Automated Mailer” on e-mail messages.
    5. Reply e-mail: Type the e-mail address that will be used for replies to e-mail messages sent from this account. The reply e-mail is optional. For example, replies to an account for SQL Server Agent may go to the database administrator, [email protected].
    6. Server name: Type the name or IP address of the SMTP server the account uses to send e-mail. Typically this is in a format similar to SMTP.<your_company>.com. For help with this, consult your mail administrator.
    7. Port number: Type the port number of the SMTP server for this account. Most SMTP servers use port 25 or 587, or port 465 for SSL connections.
    8. This server requires a secure connection (SSL): Encrypts communication using Secure Sockets Layer.
    9. Windows Authentication using Database Engine service credentials: Connection is made to the SMTP server using the credentials configured for the SQL Server Database Engine service.
    10. Basic Authentication: Specify the user name and password required by the SMTP server.
    11. User name: Type the user name that Database Mail uses to sign in to the SMTP server. The user name is required if the SMTP server requires basic authentication.
    12. Password: Type the password that Database Mail uses to log in to the SMTP server. The password is required if the SMTP server requires basic authentication.
    13. Confirm password: Type the password again to confirm the password. The password is required if the SMTP server requires basic authentication.
    14. Anonymous authentication: Mail is sent to the SMTP server without login credentials. Use this option when the SMTP server does not require authentication.
  4. Complete the wizard dialogs.

Test Database Mail configuration

Okay, now we’ve set up an email account, so what’s next? Well, let’s send a test e-mail and see if it works. As we mentioned before, we could send an e-mail to alert a user about an event that occurred in the database and this is exactly what we are going to do later on using a simple DML trigger. For now, let’s just send an e-mail to the specified recipient using the sp_send_dbmail stored procedure.

Troubleshooting Database Mail

In this case, the e-mail message was successfully queued, but the message was not delivered. First things first, check if Database Mail is enabled by executing the following code:

In the Results grid, make sure that the ‘run_value’ column for Database Mail XPs is set to 1:

Note that, To send e-mails, the user must also be a member of the DatabaseMailUserRole server role. Members of the sysadmin fixed server role and msdb db_owner role are members automatically. This can be easily checked by going to Security > Logins, right-click the current user and select Properties. In the Login Properties dialog click the ‘Server Roles’ page and make sure that the ‘sysadmin’ server role is checked:

The Database Mail system logs e-mail activity in the ‘msdb’ database. To view the error messages returned by Database Mail, execute the following code:

At this point, let’s do a quick lookup, just to make sure that the e-mail profile is configured the same as it is for Outlook. Execute the code below:

We will use the sysmail_update_account_sp stored procedure to change the port number in the existing Database Mail account. Execute the following code:

Sending query results e-mail message

Another example could be sending an e-mail message that contains query results. Execute the following code:

Sending emails with SQL

We use an extra standard method for sending emails with a system-saved process referred to as sp_send_dbmail. In this manner, you’ll be able to send emails to specified recipients and embody them within the e-mail query, attachments, or each. Please follow the instructions from the previous paragraph.

If you happen to encounter any issues when executing an SQL process, the first issue first, to verify whether or not the SMTP server is ready correctly. Confirm your credentials and take a look at sending a take a look at e-mail with one other account.

Send Email from MS SQL Server

The article was published on July 24, 2022 @ 4:09 PM

Leave a Comment