SQL如何配置Database Mail并应用sp_send_dbmail？

一. 如何配置Database Mail

1. Management → SQL Server Logs → 右击 Database Mail，选择Set up新建一个配置

2. 在弹出框中输入一个Profile name,然后点击Add

3. 输入发送方的信息

注意：要确保使用的邮箱POP3/SMTP/IMAP服务开启

4. 设置完成之后可以右击Database Mail，点击Send Test Email发送测试邮件

二. 如何应用sp_send_dbmail

2.1 参数

参数	释义
@profile_name	要从其发送消息的配置文件的名称
@recipients	要向其发送消息的电子邮件地址的列表，以分号分隔
@copy_recipients	用分号分隔的电子邮件地址列表，将消息复制到该列表中
@blind_copy_recipients	用分号分隔的电子邮件地址列表，将消息复制到该列表中
@from_address	电子邮件的“发送人地址”的值
@reply_to	电子邮件的“回复地址”的值
@subject	电子邮件的主题
@body	电子邮件的正文
@body_format	消息正文的格式，包含TEXT和HTML，默认为TEXT
@importance	消息的重要性，包含低，普通，高，默认是Normal
@sensitivity	邮件的敏感度，包含普通、个人、专用、机密，默认为Normal
@file_attachments	要附加到电子邮件的文件名称的列表，以分号分隔。必须使用绝对路径指定列表中的文件
@query	要执行的查询
@execute_query_database	是存储过程在其中运行查询的数据库上下文
@attach_query_result_as_file	指定是否以附加的文件形式返回查询的结果集，类型为bit,默认值为0，如果为0则查询结果包含在正文之后，如果值为1，则结果作为附件返回
@query_attachment_filename	指定用于查询附件的结果集的文件名

2.2 应用

发送简单的邮件

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'wangyi2',
@recipients = 'xxxxxxxxx@163.com;',
@body = 'hello world', 
@subject = 'Test 123',
@body_format = 'HTML' 
go 

邮件发送查询结果，以附件的形式发送

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'wangyi',  
    @recipients = 'xxxxxxxxxx@163.com',  
    @query = 'select * from BHGS..T' ,  
    @subject = 'Table T',  
    @attach_query_result_as_file = 1 ;

DECLARE @html_seamus NVARCHAR(max)
         DECLARE @sql_seamus NVARCHAR(max)
         set @sql_seamus ='select * from craw_monitor_forcrawler 
             where platform_name_en in(''yhd'',''suning'') 
             and check_time='''+convert(varchar(23),getdate(),25)+''''
         
         --用XML制作表格
         --制作表头
         SET @html_seamus= 'SQL: '+@sql_seamus+'<table border="1">' + 
     N'<tr><th>cust_account_id</th><th>platform_name_en</th><th>cust_keyword_id</th><th>goodsid</th><th>egoodsid</th><th>SKUid</th><th>channel</th><th>original_price_new</th><th>original_price_last</th><th>current_price_new</th><th>current_price_last</th><th>promotion_new</th><th>promotion_last</th><th>batch_time_new</th><th>batch_time_last</th><th>check_time</th>' +  
                     CAST ( (
                     --表格内容换行排格式
                     select  cust_account_id as 'td','',platform_name_en as 'td',''
                     ,cust_keyword_id as 'td','',goodsid AS 'td',''
                     ,egoodsid AS 'td',''
                     ,isnull(SKUid,'') as 'td','',channel AS 'td',''
                     ,isnull(original_price_new,'') as 'td','',isnull(original_price_last,'') AS 'td',''
                     ,isnull(current_price_new,'') as 'td','',isnull(current_price_last,'') AS 'td',''
                     ,isnull(promotion_new,'') as 'td','',isnull(promotion_last,'') AS 'td',''
                     ,isnull(convert(varchar(100),batch_time_new,25),'') as 'td',''
     ,isnull(convert(varchar(100),batch_time_last,25),'') AS 'td',''
                     ,convert(varchar(100),check_time,25) as 'td'
                      from (
   --表格内容
                             select 1 cust_account_id,2 platform_name_en,3 cust_keyword_id,4 goodsid
         ,5 egoodsid,6 SKUid,7 channel,8 original_price_new,9 original_price_last
         ,10 current_price_new,11 current_price_last,12 promotion_new
         ,13 promotion_last,14 batch_time_new,15 batch_time_last
         ,16 check_time
                                 )Z ORDER BY goodsid ASC 
                       FOR XML PATH('tr'), ELEMENTS-- TYPE   
                      ) AS NVARCHAR(MAX) ) + N'</table>'
         --发送邮件
         EXEC msdb.dbo.sp_send_dbmail 
         --数据库配置的发邮件的账号
          @profile_name = 'wangyi'
         --目标收件人
         ,@recipients = 'lyguemail@163.com'
         --邮件主体
         ,@body = @html_seamus 
         ,@body_format = 'HTML'
         --邮件主题
         ,@subject = '原价、现价、促销 抓取差异报告'

三. 数据库邮件常见的错误

3.1 找不到存储过程sp_send_dbmail

sp_send_dbmail存储过程安装在msdb数据库中，所以可以
① 从 msdb 数据库运行sp_send_dbmail

USE msdb
GO
EXEC dbo.sp_send_dbmail 
……

② 或者为存储过程指定一个由三部分构成的名称

EXEC msdb.dbo.sp_send_dbmail
 ...