Database Mail configuration in SQL Server Express edition

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:

Execute sysmail_add_profile_sp

@profile_name = 'DBMailprofile_name'

, @description = 'description'

It uses two input parameters:

@Profile_name: The value of this parameter is the name of the database mail profile. It’s a mandatory parameter
@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:

Execute sysmail_add_account_sp

@account_name = 'account name',

@email_address = 'email address' ,

@display_name = 'display name' ,

@replyto_address = 'replyto address' ,

@description = 'description' ,

@mailserver_name = 'server name',

@mailserver_type = 'server type',

@port = port number,

@username = 'user_name',

@password = 'password',

@enable_ssl = enable SSL

The procedure accepts the following input parameters:

@account_name: The name of the account which you want to add. The datatype is sysname, and it’s a mandatory parameter
@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
@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
@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
@description: It’s the description of the account
@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
@mailserver_type: This parameter holds the type of mail server. The data type of this parameter is sysname, and it’s a mandatory parameter
@port: This parameter holds the port number of the SMTP server
@username: This parameter holds the username to access the SMTP server. It’s a varchar datatype and its mandatory
@password: This parameter holds the password to access the SMTP server. It’s a varchar datatype and its mandatory
@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:

Execute sysmail_add_profileaccount_sp

@profile_id = profile_id OR @profile_name = 'profile_name' } ,

@account_id = account_id OR @account_name = 'account_name' } ,

@sequence_number = sequence_number

The stored procedure accepts following input parameters:

@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
@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
@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:

execute sp_send_dbmail

@profile_name = 'profile name'

, @recipients = 'recipients ; '

, @copy_recipients = 'copy recipient;'

, @blind_copy_recipients = 'blind copy recipient;'

, @from_address = 'from address'

, @reply_to = 'reply to'

, @subject = 'subject'

, @body = 'body'

, @body_format = 'body format'

, @importance = 'importance'

, @sensitivity = 'sensitivity'

, @file_attachments = 'attachment;'

The procedures accept the following parameters:

@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
@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
@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
@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
@from_address: This parameter holds the value of the from email address
@reply_to: If anyone replies to the email, then it will be sent to the email ID hold by the @reply_to parameter
@subject: This parameter is the subject line of the email
@body: This parameter is the email body
@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
@importance: This parameter determines the importance of the email. It could be any of the following:
1. Low
2. Normal
3. High
@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
@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:

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:

Use MSDB

IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE name = 'SQLServer Express')

BEGIN

--CREATE Account [SQLServer Express]

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = 'SQLServer Express',

@email_address = 'nisargupadhyay87@outlook.com',

@display_name = 'SQL Server Database Mail',

@replyto_address = '',

@description = '',

@mailserver_name = 'smtp.office365.com',

@mailserver_type = 'SMTP',

@port = '587',

@username = 'nisargupadhyay87@outlook.com',

@password = 'NotTheRealPassword',

@use_default_credentials = 0 ,

@enable_ssl = 1 ;

END --IF EXISTS account

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

Use MSDB

IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE name = 'SQLServer Express Edition')

BEGIN

--CREATE Profile [SQLServer Express Edition]

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = 'SQLServer Express Edition',

@description = 'This db mail account is used by SQL Server Express edition.';

END --IF EXISTS profile

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

Use MSDB

IF NOT EXISTS(SELECT *

FROM msdb.dbo.sysmail_profileaccount pa

INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id

INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id

WHERE p.name = 'SQLServer Express Edition'

AND a.name = 'SQLServer Express')

BEGIN

-- Associate Account [SQLServer Express] to Profile [SQLServer Express Edition]

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = 'SQLServer Express Edition',

@account_name = 'SQLServer Express',

@sequence_number = 1 ;

END

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:

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'SQLServer Express Edition',

@recipients = 'nisargupadhyay87@outlook.com',

@body = 'Voila..!! This email has been sent from SQL Server Express Edition.',

@subject = 'Voila..!! This email has been sent from SQL Server Express Edition.' ;

The following is the screenshot of the email:

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

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'SQLServer Express Edition',

@recipients = 'nisargupadhyay87@outlook.com',

@body = 'Please find attched scripts',

@importance='High',

@sensitivity='Confidential',

@file_attachments='C:\Personal\String_Split_Code.sql;C:\Personal\Updated_String_Split_Code.sql',

@subject = 'T-SQL Scripts' ;

Following is the screenshot of the email:

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.

Author
Recent Posts

Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration.

He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

Latest posts by Nisarg Upadhyay (see all)

Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
Changing the location of FILESTREAM data files in SQL Database - October 14, 2022
Manage SQL Databases in CentOS: Manage filegroups of user databases - October 5, 2022

SQL Server Database Mail service and stored procedure

msdb.dbo.sysmail_add_profile_sp

msdb.dbo.sysmail_add_account_sp

msdb.dbo.sysmail_add_profileaccount_sp

msdb.dbo.sp_send_dbmail

Steps to perform Database Mail configuration on SQL Server Express edition

Summary

Related posts: