本文详细介绍了如何使用T-SQL脚本在SQL Server Express版中配置数据库邮件。由于Express版不支持SQL Server Agent，配置需通过CLR集成或MSDB存储过程。文章详细阐述了`sysmail_add_profile_sp`、`sysmail_add_account_sp`、`sysmail_add_profileaccount_sp`和`sp_send_dbmail`四个存储过程的用法和参数，以及配置数据库邮件的完整步骤。
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.
在本文中，我将逐步说明使用T-SQL脚本在SQL Server Express版中执行数据库邮件配置的过程。 众所周知，SQL Server Express版本不提供SQL Server代理服务。 因此，我们无法使用SQL Server Management Studio执行数据库邮件配置或SQL作业或维护计划。 要在SQL Server Express版中配置数据库邮件功能，我们必须使用CLR集成或在MSDB数据库中使用存储过程。
SQL Server数据库邮件服务和存储过程 (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:
要执行数据库邮件配置，我们将使用SQL Server Express版的MSDB数据库的以下存储过程：
msdb.dbo.sysmail_add_profile_sp (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:
此存储过程在SQL Server中添加数据库邮件配置文件。 以下是存储过程的语法：
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 @Profile_name：此参数的值是数据库邮件配置文件的名称。 这是必填参数
- @description: The value of this parameter is the description of the database mail profiles. This parameter is optional @description：此参数的值是数据库邮件配置文件的描述。 此参数是可选的
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.
该过程返回数据库邮件的配置文件ID。 必须将sysadmin固定服务器角色授予用户才能执行此存储过程。
msdb.dbo.sysmail_add_account_sp (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:
此存储过程用于添加数据库邮件帐户。 此帐户保存SMTP帐户信息。 下面是存储过程的语法：
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 @account_name：您要添加的帐户的名称。 数据类型为sysname，它是必填参数
- @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 @email_address：此参数来自您要用来发送电子邮件的电子邮件地址。 例如，如果要从DBA@dclocal.com, then the value of the DBA@dclocal.com发送电子邮件地址，则@email_address is @email_address的值为DBA@dclocal.com. The data type is varchar, and it’s mandatory DBA@dclocal.com 。 数据类型为varchar，它是强制性的
- @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 @display_name：这是电子邮件地址的显示名称。 例如，您可以在@display_name参数中设置值，而不是显示要显示一些有意义名称的电子邮件地址。 数据类型为varchar，它是一个可选参数
- @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 @replyto_address：如果要回复数据库帐户发送的电子邮件，则可以在@replyto_address中指定该电子邮件ID。 数据类型为varchar
- @description: It’s the description of the account @description：这是帐户的描述
- @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_name：此参数保存SMTP邮件服务器的名称或IP地址。 此参数的数据类型为sysname，它是必需参数
- @mailserver_type: This parameter holds the type of mail server. The data type of this parameter is sysname, and it’s a mandatory parameter @mailserver_type：此参数保存邮件服务器的类型。 此参数的数据类型为sysname，它是必需参数
- @port: This parameter holds the port number of the SMTP server @port：此参数保存SMTP服务器的端口号
- @username: This parameter holds the username to access the SMTP server. It’s a varchar datatype and its mandatory @username：此参数保存用于访问SMTP服务器的用户名。 这是varchar数据类型，它是强制性的
- @password: This parameter holds the password to access the SMTP server. It’s a varchar datatype and its mandatory @password：此参数保存用于访问SMTP服务器的密码。 这是varchar数据类型，它是强制性的
- @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 @enable_ssl：此参数保存位值。 如果要使用SSL加密连接，则应使用1或0。这是必填参数，默认值为0。
If the procedure executes successfully, it returns the account ID. The sysadmin fixed server role must be granted to execute the stored procedure:
如果该过程成功执行，则返回帐户ID。 必须授予sysadmin固定服务器角色才能执行存储过程：
msdb.dbo.sysmail_add_profileaccount_sp (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:
此存储过程用于将数据库邮件帐户添加到数据库邮件配置文件。 我们必须在数据库邮件帐户之后执行它，并通过执行msdb.dbo.sysmail_add_account_sp和msdb.dbo.sysmail_add_profile_sp存储过程来创建数据库邮件配置文件。 以下是存储过程的语法：
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
@profile_id：此参数是您要在其中添加数据库邮件帐户的配置文件ID。 此参数的数据类型为int，它是必需参数
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
@profile_name：如果您不知道配置文件ID，则可以提供要在其中添加配置文件的配置文件的名称。 该参数的数据类型是sysname及其必需参数
@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
@account_id：此参数是您要添加到配置文件的帐户ID。 此参数的数据类型为int，这是强制性参数
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
@account_name：如果您不知道帐户ID，则可以提供数据库邮件配置文件的名称。 此参数的数据类型为sysname，它是必需参数
@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
@Sequence_number：此参数是数据库邮件配置文件中帐户的序列号。 此参数确定数据库邮件帐户的使用顺序。 此参数的数据类型为integer ，它是必需参数
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.
该参数的返回值为0 （成功）或1 （失败）。 必须授予sysadmin固定服务器角色才能执行此存储过程。
Now, to send the email using SQL Server Express edition, we will use following store procedure:
现在，要使用SQL Server Express版发送电子邮件，我们将使用以下存储过程：
msdb.dbo.sp_send_dbmail (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:
如果该过程成功执行，则返回消息的mailitem_id 。 以下是该过程的语法：
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 @profile_name：此参数是配置文件的名称，用于发送电子邮件。 此参数的数据类型为sysname，它是必需参数
- @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 @recipients：此参数是您要向其发送电子邮件的一个或多个电子邮件地址。 您可以指定一个或多个电子邮件地址。 如果您使用多个电子邮件地址，请通过区分每个电子邮件; 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 @copy_recipients：如果要将任何人保留在抄送中，则可以在此参数中指定那些电子邮件ID。 您可以指定一个或多个电子邮件地址。 如果您使用多个电子邮件地址，请通过区分每个电子邮件; 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 @blind_copy_recipients：如果要将任何人保留在密件抄送中，则可以在此参数中指定那些电子邮件ID。 您可以指定一个或多个电子邮件地址。 如果您使用多个电子邮件地址，请通过区分每个电子邮件; character； 字符
- @from_address: This parameter holds the value of the @from_address：此参数保存的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：如果有人回复了电子邮件，那么它将通过@reply_to parameter @reply_to参数发送到保留的电子邮件ID
- @subject: This parameter is the subject line of the email @subject：此参数是电子邮件的主题行
- @body: This parameter is the email body @body：此参数是电子邮件正文
- @body_format: This parameter is used to determine the format of the email body. It could be any of the following: @body_format：此参数用于确定电子邮件正文的格式。 可能是以下任何一种：
- HTML body HTML主体
- Plain text body 纯文本正文
- @importance: This parameter determines the importance of the email. It could be any of the following: @importance：此参数确定电子邮件的重要性。 可能是以下任何一种：
- Low 低
- Normal 正常
- High 高
- @sensitivity: This parameter determines the sensitivity of the email. The values can be any of the following: @敏感性：此参数确定电子邮件的敏感性。 值可以是以下任意值：
- Normal 正常
- Personal 个人
- Private 私人的
- 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 @file_attachments：如果要在电子邮件中附加一个或多个然后一个文件，则可以提供文件的全限定名。 如果要发送多个附件，则必须使用来区分附件; character. 。 字符。
在SQL Server Express版上执行数据库邮件配置的步骤 (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:
现在，让我们通过执行上述存储的过程来配置数据库邮件。 我已经在工作站上安装了SQL Server Express版本的命名实例。 首先，让我们连接到SQL Server，为此，打开SQL Server Management Studio并连接到该数据库引擎。 见下图：
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:
连接到服务器后，打开新的查询编辑器窗口。 首先，要创建数据库帐户，请在MSDB数据库中执行以下查询：
Use MSDB
go
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:
其次，要创建数据库邮件配置文件，请在MSDB数据库中执行以下查询：
Use MSDB
go
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:
要将数据库邮件帐户分配给数据库邮件概要文件，请在MSDB数据库中执行以下查询：
Use MSDB
go
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:
完成数据库邮件配置后，让我们将测试电子邮件发送到电子邮件ID。 为此，执行以下代码：
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.
在本文中，我已解释了使用MSDB数据库的T-SQL存储过程在SQL Server Express版本上执行数据库邮件配置的分步过程。
翻译自: https://www.sqlshack.com/database-mail-configuration-in-sql-server-express-edition/
扫一扫
2555
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
