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.

正如您所希望的那样，数据库邮件是一种从SQL Server数据库引擎向用户发送电子邮件的解决方案。 使用数据库邮件，数据库应用程序可以发送电子邮件，例如，可以包含查询结果或仅向用户警告数据库中发生的事件。

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

数据库邮件配置过程包括三个主要步骤。 为了成功完成此操作，我们需要：

• create a Database Mail account,
  创建一个数据库邮件帐户，
• create a Database Mail profile,
  创建一个数据库邮件配置文件，
• and configure those two to work together
  并配置这两个一起工作

配置数据库邮件 (Configuring Database Mail)

To create a Database Mail profile, we can use either the Database Mail Configuration Wizard or T-SQL code. Using Configuration Wizard is easier, but bear in mind that Database Mail is turned off in editions.

要创建数据库邮件配置文件，我们可以使用数据库邮件配置向导或T-SQL代码。 使用配置向导更容易，但是请记住， 版本中的数据库邮件已关闭。

Usually, all we need to do is go in Object Explorer, connect to the SQL Server instance we want to configure Database Mail on and expand the server tree. Then expand the Management node and double-click Database Mail or right-click and choose Configure Database Mail to open the Database Mail Configuration Wizard:

通常，我们所需要做的就是进入对象资源管理器，连接到我们要在其上配置数据库邮件SQL Server实例，并展开服务器树。 然后展开“管理”节点，双击“数据库邮件”，或右键单击并选择“ 配置数据库邮件”以打开“数据库邮件配置向导”：

Since edition is used in this article as an example, the Management node does not have Database Mail:

由于本文以版本为例，因此“管理”节点没有数据库邮件：

This doesn’t mean we cannot use it because it’s only not available as an interface, but it’s still available in the SQL Server Database Engine itself. We just need to enable it using T-SQL.

这并不意味着我们不能使用它，因为它不仅不能用作接口，而且在SQL Server数据库引擎本身中仍然可用。 我们只需要使用T-SQL启用它即可。

To enable Database Mail, run the following code:

要启用数据库邮件，请运行以下代码：

 sp_configure 'Database Mail XPs', 1;GORECONFIGUREGO 

In this case, running the code triggered an error:

在这种情况下，运行代码会触发错误：

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 62 [Batch Start Line 0] The configuration option ‘Database Mail XPs’ does not exist, or it may be an advanced option.

消息15123，级别16，状态1，过程sp_configure，第62行[批处理开始第0行]配置选项'Database Mail XPs'不存在，或者它可能是高级选项。

This is going to happen from time to time because this is an advanced option. To fix this, we need to change the show advanced options default value from 0 to 1.

由于这是高级选项，因此会不时发生。 要解决此问题，我们需要将显示高级选项的默认值从0更改为1。

To do this run the following code:

为此，请运行以下代码：

sp_configure 'show advanced options', 1;GORECONFIGURE;GO sp_configure 'Database Mail XPs', 1;GORECONFIGUREGO

This time, the query is executed successfully. Once these two options are changed from ‘0’ to ‘1’, Database Mail is turned on:

这次，查询成功执行。 将这两个选项从“ 0”更改为“ 1”后，数据库邮件将打开：

Now we can get back to setting up the e-mail profile and adding an e-mail account. To do this we will use some stored procedures in database.

现在，我们可以回到设置电子邮件配置文件并添加电子邮件帐户的方式。 为此，我们将在数据库中使用一些存储过程。

To create a new Database Mail profile named ‘Notifications’ we will use the stored procedure and the following code:

要创建名为“ Notifications”的新数据库邮件配置文件，我们将使用存储过程和以下代码：

-- Create a Database Mail profile  EXECUTE msdb.dbo.sysmail_add_profile_sp      @profile_name = 'Notifications',      @description = 'Profile used for sending outgoing notifications using Gmail.' ;  GO

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

要授予数据库用户或角色使用此数据库邮件配置文件的权限，我们将使用存储过程和以下代码：

-- Grant access to the profile to the DBMailUsers role  EXECUTE msdb.dbo.sysmail_add_principalprofile_sp      @profile_name = 'Notifications',      @principal_name = 'public',      @is_default = 1 ;GO

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

要创建一个新的数据库邮件帐户，其中包含有关SMTP帐户的信息，我们将使用存储过程和以下代码：

-- Create a Database Mail account  EXECUTE msdb.dbo.sysmail_add_account_sp      @account_name = 'Gmail',      @description = 'Mail account for sending outgoing notifications.',      @email_address = 'Use a valid e-mail address',      @display_name = 'Automated Mailer',      @mailserver_name = 'smtp.gmail.com',    @port = 465,    @enable_ssl = 1,    @username = 'Use a valid e-mail address',    @password = 'Use the password for the e-mail account above' ;  GO

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

要将数据库邮件帐户添加到数据库邮件配置文件，我们将使用存储过程和以下代码：

-- Add the account to the profile  EXECUTE msdb.dbo.sysmail_add_profileaccount_sp      @profile_name = 'Notifications',      @account_name = 'Gmail',      @sequence_number =1 ;  GO

Execute the code from all stored procedures, and you should get the message that the whole code is executed successfully:

从所有存储过程中执行代码，您应该得到一条消息，说明整个代码已成功执行：

If for some reason, execution of the code above returns an error, use the following code to roll back the changes:

如果由于某种原因，执行上面的代码返回错误，请使用以下代码回滚更改：

EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = 'Notifications'EXECUTE msdb.dbo.sysmail_delete_principalprofile_sp @profile_name = 'Notifications'EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = 'Gmail'EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = 'Notifications'

If anything goes wrong, executing the stored procedures individually could help in troubleshooting the issue. Just make sure to execute the ‘sysmail_add_profileaccount_sp’ stored procedure after the Database Account, and a Database Profile are created.

如果有任何问题，单独执行存储过程可以帮助解决问题。 只要确保在创建数据库帐户和数据库配置文件之后执行“ sysmail_add_profileaccount_sp”存储过程即可。

测试数据库邮件配置 (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 stored procedure.

如前所述，我们可以发送电子邮件向用户发出有关数据库中发生的事件的警报，而这正是我们以后使用简单DML触发器进行的操作。 现在，让我们仅使用存储过程将电子邮件发送给指定的收件人。

EXEC msdb.dbo.sp_send_dbmail     @profile_name = 'Notifications',     @recipients = 'Use a valid e-mail address',     @body = 'The database mail configuration was completed successfully.',     @subject = 'Automated Success Message';GO

The Results grid will show the message that the e-mail is queued and ID number:

结果网格将显示电子邮件已排队的消息和ID号：

The code above should send an e-mail using the profile that was previously created to a recipient specified @recipients argument. The subject and body are specified @body and @subject arguments.

上面的代码应使用以前创建的配置文件向指定的@recipients参数的收件人发送电子邮件。 主题和主体指定为@body和@subject参数。

Pretty simple, right? Well, not exactly. In this case and in most situations in the real world the mail will not be sent successfully even though every step during the configuration was successful.

很简单，对吧？ 好吧，不完全是。 在这种情况下，在大多数情况下，即使配置过程中的每个步骤都成功，邮件也不会成功发送。

对数据库邮件进行故障排除 (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:

首先，请通过执行以下代码检查是否启用了数据库邮件：

sp_configure 'show advanced', 1; GORECONFIGURE;GOsp_configure;GO

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

在“结果”网格中，确保将Database Mail XP的“ run_value”列设置为1：

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:

要发送电子邮件，用户还必须是DatabaseMailUserRole服务器角色的成员。 sysadmin固定服务器角色和msdb db_owner角色的成员自动成为成员。 可以通过转到“ 安全性” >“ 登录名” ，右键单击当前用户，然后选择“ 属性”来轻松检查。 在“登录属性”对话框中，单击“服务器角色”页面，并确保已选中“ sysadmin”服务器角色：

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:

数据库邮件系统将电子邮件活动记录在“ msdb”数据库中。 要查看数据库邮件返回的错误消息，请执行以下代码：

 SELECT * FROM msdb.dbo.sysmail_event_log; 

The statement will return logs and in a case like ours when e-mails are not delivered look for the errors under the ‘event_type’ column:

该语句将返回日志，在像我们这样的情况下，当电子邮件未发送时，请在“ event_type”列下查找错误：

These logs have all sorts of useful information that could help in troubleshooting, and we are in particular interested in the ‘description’ column as it holds detailed information about the error and what went wrong.

这些日志包含各种有用的信息，可以帮助您进行故障排除，并且我们特别对'description'列感兴趣，因为它包含有关错误和出了什么问题的详细信息。

The very first error logged says:

记录的第一个错误说：

2 error 2017-11-13 00:18:27.800 The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 5 (2017-11-13T00:18:27). Exception Message: Cannot send mails to mail server. (The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.5.1 Authentication Required. Learn more at). ) 12092 1 NULL 2017-11-13 00:18:27.800 sa

2错误2017-11-13 00：18：27.800由于邮件服务器故障，邮件无法发送给收件人。 （使用帐户5发送邮件（2017-11-13T00：18：27）。异常消息：无法将邮件发送到邮件服务器。（SMTP服务器需要安全连接或客户端未通过身份验证。服务器响应为：5.5。 1要求身份验证。有关更多信息，请访问）12092 1 NULL 2017-11-13 00：18：27.800 sa

However, this information won’t be useful unless there is some documentation e.g. in the TechNet library. There is an article about that provides information for users to quickly isolate and correct common problems with Database Mail, but going through the documentation was not helpful in this case and it took a long time without success.

但是，除非在TechNet库中有一些文档，否则此信息将无用。 有一篇有关的文章，为用户提供信息以快速隔离和更正数据库邮件的常见问题，但是在这种情况下浏览文档无济于事，并且花费了很长时间才获得成功。

One other message that was logged of this type was:

记录此类型的另一条消息是：

28 error 2017-11-14 16:20:01.107 The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 6 (2017-11-14T16:20:01). Exception Message: Cannot send mails to mail server. (Failure sending mail.). ) 14452 8 NULL 2017-11-14 16:20:01.107 sa

28错误2017-11-14 16：20：01.107由于邮件服务器故障，邮件无法发送给收件人。 （使用帐户6（2017-11-14T16：20：01发送邮件。异常消息：无法将邮件发送到邮件服务器。（发送邮件失败。）。）14452 8 NULL 2017-11-14 16：20：01.107 sa

The problem, in general, is not with the Database Mail itself and its functionality. The error description messages are not predefined in the SQL Server Engine. This explains the missing Microsoft documentation. Those messages are usually authentication responses from SMTP servers that are required before allowing access to mail services.

通常，问题不在于数据库邮件本身及其功能。 错误描述消息未在SQL Server引擎中预定义。 这解释了缺少的Microsoft文档。 这些消息通常是来自SMTP服务器的身份验证响应，在允许访问邮件服务之前，这些响应是必需的。

To troubleshoot this further, we need to approach the problem from another perspective.

为了进一步解决此问题，我们需要从另一个角度解决这个问题。

So far in troubleshooting, we have verified the following general steps:

到目前为止，在故障排除中，我们已经验证了以下常规步骤：

• Database Mail is enabled
  数据库邮件已启用
• The user is properly configured to send Database Mail
  用户已正确配置为发送数据库邮件
• Sending a test e-mail doesn’t work
  发送测试电子邮件不起作用
• No TechNet library documentation could be found for the error messages logged
  找不到针对记录的错误消息的TechNet库文档

Another approach could be to setup an e-mail account used for sending outgoing notifications on another e-mail client like . This should be fairly simple as both Gmail and MS Outlook are well-known services that should work together and there’s online documentation available on official websites.

另一种方法可能是设置一个电子邮件帐户，该帐户用于在另一个电子邮件客户端（如上发送传出通知。 这应该相当简单，因为Gmail和MS Outlook都是可以一起使用的知名服务，并且官方网站上提供了在线文档。

To use the Gmail account with MS Outlook, we have to configure it to work with or .

要将Gmail帐户与MS Outlook一起使用，我们必须对其进行配置以使其与或 。

Log onto your Gmail account at and click the Settings in the top right corner:

登录上的Gmail帐户，然后单击右上角的“设置” ：

Choose the Forwarding and POP/IMAP tab at the top of the page:

选择页面顶部的转发和POP / IMAP标签：

In the IMAP Access section, make sure that Enable IMAP option is selected and click the link at the bottom:

在“ IMAP访问”部分中，确保已选中“ 启用IMAP”选项，然后单击底部的“ 链接：

This will open an instruction webpage on how to set up IMAP. Skip the 1st step as we already checked and verified that IMAP is turned on.

这将打开一个有关如何设置IMAP的说明网页。 我们已经检查并确认IMAP已打开，因此跳过第一步。

Fire up Outlook client to add the Gmail email account to it. Once Outlook is opened, click the File tab, and select Add Account:

启动Outlook客户端以向其添加Gmail电子邮件帐户。 打开Outlook后，单击“ 文件”选项卡，然后选择“ 添加帐户” ：

In the Add Account dialog, select the Manual setup or additional server types option and click the Next button:

在“添加帐户”对话框中，选择“ 手动设置或其他服务器类型”选项，然后单击“ 下一步”按钮：

In the next step, choose the POP or IMAP option and click the Next button:

在下一步中，选择POP或IMAP选项，然后单击“ 下一步”按钮：

Use the information from the table below to set up Outlook client with the correct settings:

使用下表中的信息以正确的设置设置Outlook客户端：

Incoming Mail (IMAP) Server	imap.gmail.com Requires SSL: Yes Port: 993
Outgoing Mail (SMTP) Server	smtp.gmail.com Requires SSL: Yes Requires TLS: Yes (if available) Requires Authentication: Yes Port for SSL: 465 Port for TLS/STARTTLS: 587

传入邮件（IMAP）服务器	imap.gmail.com 需要SSL：是 端口：993
外寄邮件（SMTP）服务器	smtp.gmail.com 需要SSL：是 需要TLS：是（如果可用） 需要验证：是 SSL连接埠：465 TLS / STARTTLS的端口：587

Under User Information section, make sure to enter a valid e-mail address. The e-mail address entered here will be automatically set as User Name for the Logon Information once the Account Type is selected from the drop-down box. Once finished, click the More Settings button on the right to configure additional settings before continuing further:

在“ 用户信息”部分下，确保输入有效的电子邮件地址。 从下拉框中选择“帐户类型”后，在此处输入的电子邮件地址将自动设置为登录信息的“ 用户名 ”。 完成后，请单击右侧的“其他设置”按钮以配置其他设置，然后再继续操作：

This will open the Internet e-mail settings dialog. Leave everything as it is under the General tab. Next, click the Outgoing Server tab, check the My outgoing server (SMTP) requires authentication check-box and select the Use same settings as my incoming mail server option:

这将打开“ Internet电子邮件设置”对话框。 保留“ 常规”选项卡下的所有内容。 接下来，单击“发件服务器”选项卡，选中“ 我的发件服务器（SMTP）需要身份验证”复选框，然后选择“ 使用与我的传入邮件服务器相同的设置”选项：

Move on to the Advanced tab. Once again, use the information from the table above to set server port numbers. Set the incoming server (IMAP) to 993, and the outgoing server (SMTP) to 465. From the drop-down boxes select the SSL/TLS type of encrypted connection for both IMAP and SMTP servers. Leave the rest as it is and click the OK button to confirm settings:

转到“ 高级”选项卡。 再次使用上表中的信息来设置服务器端口号。 将传入服务器（IMAP）设置为993 ，将传出服务器（SMTP）设置为465 。 从下拉框中为IMAP和SMTP服务器选择加密连接的SSL / TLS类型。 其余部分保持不变，然后单击确定按钮以确认设置：

Now it’s the time to test e-mail account settings to ensure that the entries are correct. Click the Next button and this will pop-up two additional dialogs. In the first dialog, Outlook will perform two tasks:

现在是时候测试电子邮件帐户设置，以确保输入正确。 单击下一步按钮，这将弹出两个附加对话框。 在第一个对话框中，Outlook将执行两项任务：

1. Log onto incoming mail server (IMAP)
   登录到传入邮件服务器（IMAP）
2. Send test e-mail message
   发送测试电子邮件

This step is crucial as this task previously failed for Database Mail. In the second dialog, Outlook will ask for the username and password. This was already configured, optionally check the Save this password in your password lint option if your Windows user account is password-protected, and no other person has access to it:

此步骤至关重要，因为此任务以前因数据库邮件而失败。 在第二个对话框中，Outlook将询问用户名和密码。 这已经配置好了，如果您的Windows用户帐户受密码保护，并且没有其他人可以访问它，则可以选择选中“ 将此密码保存在密码中”选项：

Note: The credential dialog can pop-up more than one time. Just click the OK button each time, until the server stops asking for login information. This usually happens when something is not configured right e.g. username or password, port numbers, etc.

注意 ：凭据对话框可以弹出多次。 每次只需单击确定按钮，直到服务器停止询问登录信息。 这通常发生在未正确配置某些内容（例如用户名或密码，端口号等）时。

In this case, the first task failed and sending test e-mail message failed as well right afterward:

在这种情况下，第一个任务失败，随后发送测试电子邮件也失败：

Some applications and devices use less secure sign-in technology, which makes their respective accounts more vulnerable and Gmail will block these sign-in requests. By default, access for these applications is turned off, but we can also turn access on to use them despite the risks. In this case, we also have to.

某些应用程序和设备使用不太安全的登录技术，这会使它们各自的帐户更容易受到攻击，Gmail会阻止这些登录请求。 默认情况下，这些应用程序的访问权限是关闭的，但是尽管有风险，我们也可以打开访问权限以使用它们。 在这种情况下，我们也必须这样做。

This can be solved by logging into Gmail account and making sure that option is enabled for the desired account. Go to the section of the Gmail account and enable access for less secure applications.

可以通过登录Gmail帐户并确保为所需帐户启用了“ 选项来解决此问题。 转到Gmail帐户的“ 部分，然后启用对安全性较低的应用程序的访问。

Note: This setting is not available for accounts with enabled. Such accounts require an application-specific password for less secure apps access.

注意 ：此设置不适用于启用了帐户。 此类帐户需要特定于应用程序的密码，以降低安全性。

Since this account has 2-Step Verification enabled, we have to turn it off, so we can continue further.

由于此帐户已启用“两步验证”，因此我们必须将其关闭，然后才能继续进行操作。

Go to ’Sign in & security’ section of , select 2-Step Verification, sign in using your credentials, and enter verification code if asked received via text message with a 6-digit verification code or phone call, and click the Turn off button:

转到“ ”的“登录和安全性”部分，选择“两步验证”，使用您的凭据登录，然后输入验证码（如果要求通过短信收到带有6位数字的验证码或致电的验证码），然后单击“ 打开”关闭按钮：

A pop-up dialog will appear informing the user about security and vulnerability. We are aware of this, so just click the Turn off button to continue:

将出现一个弹出对话框，通知用户安全性和漏洞。 我们知道这一点，因此只需单击“关闭”按钮即可继续：

Now we can go back to the section and allow access for less secure applications:

现在，我们可以返回“ 部分，并允许访问安全性较低的应用程序：

Back to Outlook, click the Next button once again to test e-mail account settings and finally a message without errors. Both tasks completed successfully:

返回到Outlook，再次单击“下一步”按钮以测试电子邮件帐户设置，最后是没有错误的消息。 两项任务均成功完成：

The test e-mail message is also delivered successfully which means that sending another test e-mail from Database Mail should work this time:

测试电子邮件也已成功传递，这意味着这次从数据库邮件发送另一封测试电子邮件应该可以正常工作：

There is only one way to find out. Go back to SSMS and execute the code for sending the test e-mail:

只有一种找出方法。 返回SSMS并执行发送测试电子邮件的代码：

EXEC msdb.dbo.sp_send_dbmail     @profile_name = 'Notifications',     @recipients = 'Use a valid e-mail address',     @body = 'The database mail configuration was completed successfully.',     @subject = 'Automated Success Message';GO

Unfortunately, even after all this trouble, the test e-mail is not getting through. A quick look at the log files shows another error message of another type:

不幸的是，即使遇到了所有这些麻烦，测试电子邮件也无法通过。 快速浏览日志文件会显示另一种错误消息：

51 error 2017-11-18 16:21:22.803 The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 10 (2017-11-18T16:21:22). Exception Message: Cannot send mails to the mail server. (Failure sending mail.). ) 3564 14 NULL 2017-11-18 16:21:22.803 sa

51错误2017-11-18 16：21：22.803由于邮件服务器故障，邮件无法发送给收件人。 （使用帐户10（2017-11-18T16：21：22发送邮件。异常消息：无法将邮件发送到邮件服务器。（发送邮件失败。）。）3564 14空2017-11-18 16：21：22.803 SA

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

此时，让我们快速查找一下，以确保将电子邮件配置文件配置为与Outlook相同。 从下面执行代码：

SELECT [sysmail_server].[account_id],       [sysmail_account].[name] AS [Account Name],       [servertype],       [servername] AS [SMTP Server Address],       [Port]FROM [msdb].[dbo].[sysmail_server]     INNER JOIN [msdb].[dbo].[sysmail_account] ON [sysmail_server].[account_id] = [sysmail_account].[account_id];

This will return settings for account name, server type, server address, and port number:

这将返回帐户名称，服务器类型，服务器地址和端口号的设置：

Everything looks good, but the e-mail is still not getting out. A quick online lookup of the error message leads to a thread in which the user has the exact same error message. In the comment section, another user says that changing the port number to 587 instead of 465, even though information from Gmail says otherwise, fixed the issue for him. As we are running out of options here, let’s try that and see what happens.

一切看起来不错，但是电子邮件仍然没有发送出去。 快速在线查找错误消息会导致线程，其中用户具有完全相同的错误消息。 在评论部分，另一位用户说，即使来自Gmail的信息另有说明，将端口号更改为587而不是465也可以解决该问题。 当我们用完这里的所有选项时，让我们尝试一下，看看会发生什么。

We will use the stored procedure to change the port number in the existing Database Mail account.

我们将使用存储过程来更改现有数据库邮件帐户中的端口号。

Execute the following code:

执行以下代码：

EXECUTE msdb.dbo.sysmail_update_account_sp        @account_name = 'Gmail',        @port = 587;  GO

Execute the code from the previous step to verify that the change is applied. The port number will change leaving all other account information intact:

执行上一步中的代码以验证是否已应用更改。 端口号将更改，保留所有其他帐户信息不变：

For some reason configuration instructions for SSL provided by Google does not work on port 465 for Database Mail system, but port 587 that is specified for TLS/STARTTLS worked like a charm.

出于某种原因，由Google提供的SSL配置说明在数据库邮件系统的端口465上不起作用，但为TLS / STARTTLS指定的端口587就像一个超级按钮一样起作用。

Finally, after giving it another try there is a new e-mail in the inbox sent from the Database Mail system:

最后，在再次尝试之后，从数据库邮件系统发送的收件箱中有一封新电子邮件：

从触发器发送电子邮件 (Send e-mail from a trigger)

To demonstrate how to send an e-mail notification to a user when a specific event occurred in the database, we can create a simple trigger.

为了演示在数据库中发生特定事件时如何向用户发送电子邮件通知，我们可以创建一个简单的触发器。

Use the code below to create a trigger named iProductNotification on the Product table which is contained in the Production schema:

使用以下代码在生产模式中包含的产品表上创建名为iProductNotification的触发器：

USE AdventureWorks2014GO IF OBJECT_ID ('Production.iProductNotification', 'TR') IS NOT NULL	DROP TRIGGER Purchasing.iProductNotificationGO CREATE TRIGGER iProductNotification ON Production.Product    FOR INSERTAS     DECLARE @ProductInformation nvarchar(255);    SELECT	   @ProductInformation = 'A new product, ' + Name + ', is now available for $' + CAST(StandardCost AS nvarchar(20)) + '!'	   FROM 		  INSERTED i;    EXEC msdb.dbo.sp_send_dbmail	   @profile_name = 'Notifications',	   @recipients = 'Use a valid e-mail address',	   @body = @ProductInformation,	   @subject = 'New product notification'GO

This trigger will fire when an Insert statement is run against the Product table. The idea behind it is to collect basic information about a new product that is available like name and price and to wrap that information up in a simple e-mail message that will be sent to desired addresses using the Database Mail profile previously configured:

当对Product表运行Insert语句时，将触发此触发器。 其背后的想法是收集有关新产品的基本信息，如名称和价格，并将这些信息包装在简单的电子邮件中，该电子邮件将使用先前配置的数据库邮件配置文件发送到所需的地址：

Once the trigger is created, refresh the ‘Triggers’ node under the ‘Product’ table just to make sure that the trigger is created on the right table:

创建触发器后，请刷新“产品”表下的“触发器”节点，以确保在正确的表上创建了触发器：

Rather than typing the code for the Insert statement and because it’s easier, use the ‘Edit Top 200 Rows’ option from the right-click context menu on the table with the trigger. Fill in some information like working with Excel sheets. Once done, hit the Enter key to continue:

而不是为Insert语句键入代码，因为它更容易，所以可以使用带有触发器的表的右键单击上下文菜单中的“编辑前200行”选项。 填写一些信息，例如使用Excel工作表。 完成后，按Enter键继续：

This will issue an Insert statement in the background and fire the trigger. As a result, the trigger will collect some information about the Insert statement, invoke the Database Mail profile, and use it send e-mail to the desired recipient:

这将在后台发出一个Insert语句并触发触发器。 结果，触发器将收集有关Insert语句的一些信息，调用Database Mail配置文件，并使用它发送电子邮件给所需的收件人：

发送查询结果电子邮件 (Sending query results e-mail message)

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

另一个示例可能是发送包含查询结果的电子邮件。 执行以下代码：

EXEC msdb.dbo.sp_send_dbmail     @profile_name = 'Notifications',     @recipients = 'Use a valid e-mail address,     @query =  'USE AdventureWorks2014;			 GO			 SELECT Name, ProductNumber, ListPrice AS Price			 FROM Production.Product 			 WHERE ProductLine = ''R''			 AND DaysToManufacture < 4			 ORDER BY Name ASC;			 GO',     @subject = 'Product list',     @attach_query_result_as_file = 1;

This SELECT statement returns a list of products with a specific condition in mind:

此SELECT语句返回考虑了特定条件的产品列表：

But more importantly, it sends an e-mail with results of that Select statement as an attachment to the e-mail recipients:

但更重要的是，它发送带有该Select语句结果的电子邮件作为电子邮件收件人的附件：

And if we open the attachment, there it is, the result of our query:

如果打开附件，则为查询结果：

I hope you found this article helpful. Happy emailing!

希望本文对您有所帮助。 祝您发送电子邮件愉快！

翻译自: