Nisarg Upadhyay
Database Mail configuration: Email with high priority and confidential

Database Mail configuration in SQL Server Express edition

January 21, 2020 by

In this article, I am going to explain step by step process to perform Database Mail configuration in SQL Server Express edition using the T-SQL script. As we know, SQL Server Express edition does not provide the SQL Server Agent Services; hence we cannot perform Database Mail configuration or SQL Jobs or maintenance plans using SQL Server Management Studio. To configure the Database Mail feature in the SQL Server Express edition, we must use CLR integration or using stored procedures within the MSDB database.

SQL Server Database Mail service and stored procedure

To perform the Database Mail configuration, we are going to use the following stored procedures of the MSDB database of SQL Server Express edition:

msdb.dbo.sysmail_add_profile_sp

This stored procedure adds a database mail profile in SQL Server. Following is the syntax of the stored procedure:

It uses two input parameters:

  1. @Profile_name: The value of this parameter is the name of the database mail profile. It’s a mandatory parameter
  2. @description: The value of this parameter is the description of the database mail profiles. This parameter is optional

The procedure returns the profile ID of Database Mail. The sysadmin fixed server role must be granted to the user to execute this stored procedure.

msdb.dbo.sysmail_add_account_sp

This stored procedure is used to add a database mail account. This account holds the SMTP account information. Below is the syntax of the stored procedure:

The procedure accepts the following input parameters:

  1. @account_name: The name of the account which you want to add. The datatype is sysname, and it’s a mandatory parameter
  2. @email_address: This parameter is from the email address using which you want to send the email. For example, if you want to send an email address from DBA@dclocal.com, then the value of the @email_address is DBA@dclocal.com. The data type is varchar, and it’s mandatory
  3. @display_name: This is the display name of the email address. For example, instead of showing the from the email address you want to show some meaningful name, then you can set the value in the @display_name parameter. The data type is varchar, and it’s an optional parameter
  4. @replyto_address: If you want to respond to the email which has been sent by database account, then you can specify that email ID in @replyto_address. The data type is varchar
  5. @description: It’s the description of the account
  6. @mailserver_name: This parameter holds the name or the IP Address of the SMTP mail server. The data type of this parameter is sysname, and it’s a mandatory parameter
  7. @mailserver_type: This parameter holds the type of mail server. The data type of this parameter is sysname, and it’s a mandatory parameter
  8. @port: This parameter holds the port number of the SMTP server
  9. @username: This parameter holds the username to access the SMTP server. It’s a varchar datatype and its mandatory
  10. @password: This parameter holds the password to access the SMTP server. It’s a varchar datatype and its mandatory
  11. @enable_ssl: This parameter holds the bit value. If you want to use the SSL to encrypt the connection, then you should use 1 or 0. It’s the mandatory parameter, and the default value is 0

If the procedure executes successfully, it returns the account ID. The sysadmin fixed server role must be granted to execute the stored procedure:

msdb.dbo.sysmail_add_profileaccount_sp

This stored procedure is used to add the database mail account to the database mail profile. We must execute it after the database mail account, and database mail profile has been created by executing msdb.dbo.sysmail_add_account_sp and msdb.dbo.sysmail_add_profile_sp stored procedures. Following is the syntax of the stored procedure:

The stored procedure accepts following input parameters:

  1. @profile_id: This parameter is the profile ID in which you want to add the database mail account. The datatype of this parameter is int and it’s mandatory parameter

    OR

    @profile_name: If you are not aware of the profile ID, you can provide the name of the profile in which you want to add the profile. The data type of the parameter is the sysname and its mandatory parameter

  2. @account_id: This parameter is the account ID that you want to add to the profile. The data type of this parameter is int, and it’s a mandatory parameter

    OR

    @account_name: If you are not aware of the account ID, you can provide the name of the database mail profile. The data type of this parameter is sysname, and it’s a mandatory parameter

  3. @Sequence_number: This parameter is the sequence number of the account within the database mail profile. This parameter determines the order in which the database mail account is going to be used. The data type of this parameter is integer, and it’s a mandatory parameter

The return value of the parameter is either 0 (Success) or 1 (failure). The sysadmin fixed server role must be granted to execute this stored procedure.

Now, to send the email using SQL Server Express edition, we will use following store procedure:

msdb.dbo.sp_send_dbmail

This stored procedure is used to send the database mail to one or more than one recipient. The message may include any of the following:

  • Query result
  • File attachment
  • Error message or any plain text email

If the procedure executes successfully, it returns the mailitem_id of the message. Following is the syntax of the procedure:

The procedures accept the following parameters:

  1. @profile_name: This parameter is the name of the profile, which is used to send the email. The data type of this parameter is sysname, and it’s a mandatory parameter
  2. @recipients: This parameter is one or more than one email address where you want to send the email. You can specify one or more than one email address. If you are using multiple email addresses, then differentiate each email by ; character
  3. @copy_recipients: If you want to keep anyone in CC, then you can specify those email IDs in this parameter. You can specify one or more than one email address. If you are using multiple email addresses, then differentiate each email by ; character
  4. @blind_copy_recipients: If you want to keep anyone in BCC, then you can specify those email IDs in this parameter. You can specify one or more than one email address. If you are using multiple email addresses, then differentiate each email by ; character
  5. @from_address: This parameter holds the value of the from email address
  6. @reply_to: If anyone replies to the email, then it will be sent to the email ID hold by the @reply_to parameter
  7. @subject: This parameter is the subject line of the email
  8. @body: This parameter is the email body
  9. @body_format: This parameter is used to determine the format of the email body. It could be any of the following:
    1. HTML body
    2. Plain text body
  10. @importance: This parameter determines the importance of the email. It could be any of the following:
    1. Low
    2. Normal
    3. High
  11. @sensitivity: This parameter determines the sensitivity of the email. The values can be any of the following:
    1. Normal
    2. Personal
    3. Private
    4. Confidential
  12. @file_attachments: If you want to attach one or more then one file in the email, then you can provide the fully qualified name of the file. If you want to send multiple attachments, then you have to differentiate the attachments by using ; character.

Steps to perform Database Mail configuration on SQL Server Express edition

Now, let’s configure Database Mail by executing the above-stored procedures. I have already installed a named instance of the SQL Server Express edition on my work station. First, let’s connect to the SQL Server, to do that, open SQL Server Management Studio and connect to that database engine. See the following image:

connect to db

Once we are connected to the server, open the new query editor window. First, to create the database account, execute the following query in the MSDB database:

Secondly to create the database mail profile, execute following query in the MSDB database:

To assign a database mail account to the database mail profile, execute the following query in the MSDB database:

See the following image:

Once we complete the Database Mail configuration, let us send a test email to the email ID. To do that, execute the following code:

The following is the screenshot of the email:

Database Mail configuration: First text email.

Let’s try to send an attachment using Database Mail, mail importance is high, and sensitivity is confidential:

Following is the screenshot of the email:

Database Mail configuration:  Email with high priority and confidential

Summary

In this article, I have explained the step by step process to perform the Database Mail configuration on the SQL Server Express edition using T-SQL stored procedures of the MSDB database.

Nisarg Upadhyay

Latest posts by Nisarg Upadhyay (see all)

168 Views