如何使用sp_send_dbmail存储过程将SQL查询结果通过电子邮件发送到智能手机

介绍 (Introduction)

Sometimes there are tasks that require too much time to finish during business hours and we need to leave the office with some still pending like the migration of data, backups or long running queries. In such cases, we would still want to receive an email in our smartphones to make sure that the job was completed successfully.

有时，有些任务需要花费太多时间在工作时间内完成，而我们需要离开办公室，还有一些待处理的任务，例如数据迁移，备份或长期运行的查询。 在这种情况下，我们仍然希望在智能手机上接收电子邮件，以确保作业成功完成。

For example, I want to receive an email at 10 PM from SQL Server with the status of the backup process.

例如，我想在晚上10点收到来自SQL Server的电子邮件，其中包含备份过程的状态。

In this article, we will show how to send SQL Server query results in emails to our Smartphone. It can be an Android, iPhone or any other device that supports email services. That way, we can verify, monitor and administer our SQL Server on-premises remotely.

在本文中，我们将展示如何通过电子邮件将SQL Server查询结果发送到我们的Smartphone。 它可以是Android，iPhone或任何其他支持电子邮件服务的设备。 这样，我们可以远程验证，监视和管理本地SQL Server。

We will divide the article in the following sections:

我们将文章分为以下几部分：

• Configure Database Mail in SQL Server
  在SQL Server中配置数据库邮件
• Testing the mail configuration
  测试邮件配置
• Test the sp_send_dbmail stored procedure
  测试sp_send_dbmail存储过程
• Send queries and check query output
  发送查询并检查查询输出
• Insert the current data and time in the email
  在电子邮件中插入当前数据和时间
• Work with HTML format in emails
  在电子邮件中使用HTML格式

要求 (Requirements)

1. A local SQL Server Management Studio (SSMS)
   本地SQL Server Management Studio（SSMS）
2. SQL Server 2008 or later. In this example, SQL Server 2016 was installed (any edition except the Express edition).
   SQL Server 2008或更高版本。 在此示例中，安装了SQL Server 2016（Express版本除外的任何版本）。
3. A smartphone with internet access and an email service installed.
   安装了具有互联网访问权限和电子邮件服务的智能手机。

开始吧 (Get started)

在SQL Server中配置数据库邮件 (Configure Database Mail in SQL Server)

We will first configure SQL Server to receive emails.

我们将首先配置SQL Server以接收电子邮件。

In SSMS, go to Management>Database Mail right click and select the Configure Database Mail option:

在SSMS中，右键单击管理>数据库邮件，然后选择配置数据库邮件选项：

It will start a Wizard to configure the email. Press the Next button:

它将启动向导来配置电子邮件。 按下一步按钮：

Select the option to Set up mail by performing the following tasks:

选择选项以通过执行以下任务来设置邮件：

You will specify the profile name here. In this example, the profile name is dcalbimonte. Press the Add button to associate an account. You can associate a Profile to multiple SMTP accounts:

您将在此处指定配置文件名称。 在此示例中，配置文件名称为dcalbimonte。 按添加按钮关联一个帐户。 您可以将配置文件关联到多个SMTP帐户：

In this example, I will connect using a Hotmail profile. The Outgoing SMTP server for Hotmail is now smtp.office365.com and the port used is 587. You may need to specify your credentials:

在此示例中，我将使用Hotmail配置文件进行连接。 Hotmail的传出SMTP服务器现在为smtp.office365.com ，使用的端口为587 。 您可能需要指定凭据：

In this example, we are using Hotmail to send emails using the profile associated with the Hotmail account.

在此示例中，我们使用Hotmail通过与Hotmail帐户关联的配置文件发送电子邮件。

Here you have a list of SMTP outgoing services and ports:

在这里，您有SMTP传出服务和端口的列表：

Service	Server Name	Port
Outlook	smtp.office365.com	587 (SSL)
Gmail	smtp.gmail.com	587 (SSL)
Yahoo	smtp.mail.yahoo.com.	587 (SSL)
AOL	smtp.aol.com	587 (SSL)

服务	服务器名称	港口
外表	smtp.office365.com	587（SSL）
邮箱	smtp.gmail.com	587（SSL）
雅虎	smtp.mail.yahoo.com。	587（SSL）
美国在线	smtp.aol.com	587（SSL）

In the next Window, we can convert the profile created to public and to make it the default one:

在下一个窗口中，我们可以将创建的配置文件转换为公共配置文件，并将其设置为默认配置文件：

The system parameters can be used to configure the file size, retry attempts, retry delay and then extensions that are now allowed. You can also configure the Logging Level:

系统参数可用于配置文件大小，重试尝试，重试延迟以及随后允许的扩展名。 您还可以配置日志记录级别：

If everything is OK, press Finish:

如果一切正常，请按“完成”：

The wizard will verify if the account is created, that the profile is created, that the account is added to the profile and that the access is granted to send mails:

该向导将验证是否创建了帐户，是否创建了配置文件，是否将帐户添加到了配置文件以及是否授予了发送邮件的访问权限：

You can verify that the profile was created by using the following stored procedure:

您可以使用以下存储过程来验证是否创建了配置文件：

 
EXECUTE msdb.dbo.sysmail_help_profile_sp;  
 

When you run the procedure, it will show the profile information:

运行该过程时，它将显示配置文件信息：

测试邮件配置 (Testing the mail configuration)

We will test in SSMS if we can send emails. To do that go to Management>Database and right click and select the Send Test E-mail:

如果可以发送电子邮件，我们将在SSMS中进行测试。 为此，请转到管理>数据库，然后右键单击并选择发送测试电子邮件：

Specify the email to send the test:

指定用于发送测试的电子邮件：

If everything is OK, in your Smartphone, the email will be received successfully:

如果一切正常，在您的智能手机中，电子邮件将被成功接收：

测试sp_send_dbmail存储过程 (Test the sp_send_dbmail stored procedure)

In SQL Server 2005, there was an extended stored procedure to send emails. It was the xp_sendmail. For security reasons, the old xp_sendmail was removed because it required a MAPI profile, whereas sp_send_dbmail just requires the SMTP information.

在SQL Server 2005中，有一个扩展的存储过程来发送电子邮件。 是xp_sendmail。 出于安全原因，删除了旧的xp_sendmail，因为它需要MAPI配置文件，而sp_send_dbmail仅需要SMTP信息。

In this example, we will show how to send an email using the sp_send_dbmail stored procedure:

在此示例中，我们将展示如何使用sp_send_dbmail存储过程发送电子邮件：

 
EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'dcalbimonte',  
    @recipients = 'acalbimonte @gmail.com',  
    @body = 'This is a sqlshack test.',  
    @subject = 'This is a test' ;
 

In this example, we are sending an email to a Gmail account. If we check your email in your phone, you will be able to verify that the SSMS is working fine:

在此示例中，我们正在向Gmail帐户发送电子邮件。 如果我们在您的电话中检查您的电子邮件，您将能够验证SSMS是否正常运行：

There are other parameters that may be useful:

还有其他一些有用的参数：

• @file_attachments – is used to send attachments in the email.
  @file_attachments –用于在电子邮件中发送附件。
• @copy_recipients – is used to send Carbon Copies (CC) in the emails.
  @copy_recipients –用于在电子邮件中发送复本（CC）。
• @blind_copy_recipients – is used to Blind Carbon Copies (BCC) in the emails.
  @blind_copy_recipients –用于在电子邮件中进行密件抄送（BCC）。

发送查询并检查查询输出 (Send queries and check query output)

In this example, we will run the sp_who stored procedure to run the current users, sessions, and process and send the results in an attachment by email to my smartphone:

在此示例中，我们将运行sp_who存储过程来运行当前用户，会话和处理，并将结果以附件形式通过电子邮件发送到我的智能手机：

 
EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'dcalbimonte',  
    @recipients = 'acalbimonte @gmail.com',  
    @body = 'List of users',  
    @subject = 'Users on the database' , 	
	@query = 'sp_who',
	    @attach_query_result_as_file = 1
 

The sp_who will display the results as an attachment in the email:

sp_who将结果显示为电子邮件中的附件：

If you open the file attached, you will be able to see the users, processes and sessions:

如果打开附件，您将能够看到用户，进程和会话：

As you can see, sending procedures and receiving emails are straightforward processes.

如您所见，发送过程和接收电子邮件是简单的过程。

在电子邮件中插入当前数据和时间 (Insert the current data and time in the email)

Sometimes we need the current date and time in the email. The following example will show how to display the current date and time in the email subject:

有时我们需要电子邮件中的当前日期和时间。 以下示例将显示如何在电子邮件主题中显示当前日期和时间：

 
		declare @mysubject varchar(100)='Work orders '+cast(FORMAT(getdate(),'MM/dd/yy hh:mm:ss') as varchar(100))
 
		EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'dcalbimonte',  
    @recipients = 'acalbimonte@gmail.com',  
    @body = 'List of workorders',  
    @subject = @mysubject , 	
	@query = 'sp_who',
	    @attach_query_result_as_file = 1 ;
 

The cast is used to convert the dates to strings and the format function is used to provide a specified format to the dates. Finally, in the @mysubject variable, we send the dates and times concatenated with the string.

强制转换用于将日期转换为字符串，格式函数用于为日期提供指定的格式。 最后，在@mysubject变量中，我们发送与字符串连接的日期和时间。

If you check your email in your smartphone, you can verify that the subject now includes the date and time:

如果您在智能手机中检查电子邮件，则可以验证主题现在是否包括日期和时间：

在电子邮件中使用HTML格式 (Working with HTML format in emails)

We can enrich our emails using HTML format in the body.

我们可以在体内使用HTML格式来丰富我们的电子邮件。

The next example, shows how to create a variable named @mybody of time varchar(max) and we are sending a HTML header (H1) and we are using bold fonts for the word report (strong tag):

下一个示例显示如何创建一个名为@mybody的时间varchar（max）的变量，并发送HTML标头（H1），并在单词报告（强标签）中使用粗体：

 
declare @mysubject varchar(100)='Work orders '+cast(FORMAT(getdate(),'MM/dd/yy hh:mm:ss') as varchar(100))
		declare @myquery varchar(100)='sp_who'
 
		declare @mybody varchar(max)=
		'<h1>Work order report</h1>'+
'<p>Please find the <strong>report </strong>attached to this email.</p>'
 
 
 
		EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'dcalbimonte',  
    @recipients = 'acalbimonte@gmail.com',  
    @body = @mybody, 
    @subject = @mysubject , 	
	@query = @myquery,
	    @attach_query_result_as_file = 1,
		@body_format = 'HTML' ;
 

Note that you need to specify that the @body_format is HTML. If you check your email, you will notice that the header has a bigger font and the word report is bold:

请注意，您需要指定@body_format为HTML。 如果检查电子邮件，您会注意到标题具有更大的字体，单词report则为粗体：

You can use a CSS (Cascading Style Sheet) to provide a customized format to your emails. CSS is a language to describe the HTML presentation. For more information, refer to this link:

您可以使用CSS（层叠样式表）为电子邮件提供自定义格式。 CSS是描述HTML表示形式的语言。 有关更多信息，请参考以下链接：

• Format DBMail with HTML and CSS 使用HTML和CSS格式化DBMail

结论 (Conclusion)

In this article, we learned how to configure SQL Server to send emails. We specified an SMTP, port, and credentials to send emails. In this example, we used Outlook.com webmail, however, we can use any SMTP server like Gmail, Yahoo, etc.

在本文中，我们学习了如何配置SQL Server来发送电子邮件。 我们指定了SMTP，端口和凭据来发送电子邮件。 在此示例中，我们使用了Outlook.com网络邮件，但是，我们可以使用任何SMTP服务器，例如Gmail，Yahoo等。

Once that the Profile is configured, we tested that it can send emails and then we tested the sp_send_dbmail stored procedure. We send a message, we send SQL Server queries and we show how to get emails with the query results attached.

配置文件配置完成后，我们测试了它可以发送电子邮件，然后测试了 sp_send_dbmail存储过程。 我们发送一条消息，发送SQL Server查询，并展示如何获取附带查询结果的电子邮件。

Finally, we learned how to have emails with HTML format. If you want an example to send emails using the SQL Server Agent, please refer to the following article:

最后，我们学习了如何使用HTML格式的电子邮件。 如果要使用SQL Server代理发送电子邮件的示例，请参考以下文章：

• Have your database server talk to you: Send emails directly from SQL Agent Jobs 让您的数据库服务器与您对话：直接从SQL Agent Jobs发送电子邮件

翻译自: https://www.sqlshack.com/email-sql-query-results-smartphone-using-sp_send_dbmail-stored-procedure/