一. 如何配置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
...