自动发带查询结果附件的邮件

自动发带查询结果附件的邮件

一：背景

我们日常工作中，可能每天都要反馈一个固定查询结果集，比如日报。

为了减轻工作量，实现自动化管理，所以想通过作业的方式将查询结果附件自动发给收件人。

例如：这里要把test.dbo.emp表里的数据通过邮件自动发给v-白丹丹 <v-baidd@sinooceanland.com>

二：主要实现步骤

1 配置数据库邮件

1．1 在对象资源管理器中，展开‘管理’，右击‘数据库邮件’，点‘配置数据库邮件’

1.2出现如下界面

1.3点击‘下一步’，出现如下界面：

1.4点击‘下一步’，出现如下界面：

在‘配置文件名’那里自定义文件名，如我新建一个’profile1’的配置文件。

1.5点击‘添加’

在‘账户名’那里指定SMTP账户名称(必须是邮件地址，如yabingshi@163.com，而不是随意命名)，

在‘电子邮件地址’那里输入源邮件地址（如yabingshi@163.com）,

在‘服务器名称’那里输入’smtp.163.com’(貌似是固定值)。

选中‘基本身份验证’，用户名写上面的用户名(yabingshi@163.com),

密码这里输入你邮箱的密码。

如图：

1.6点击‘确定’

1.7点击‘下一步’，勾选’profile1’这个配置文件

1.8点击‘下一步’

1.9：点击‘下一步’

1.10：点击‘完成’

点击‘关闭’。

1.11右击‘数据库邮件’，点‘发送测试电子邮件’，出现如下界面

1.12在收件人这里指定收件人，如我MICROSOFE OUTLOOK邮件地址：v-白丹丹 v-baidd@sinooceanland.com

1.13点击‘发送测试电子邮件’，

点击‘确定’即可。

1.14 右击‘数据库邮件’，点‘查看数据库邮件日志’，如果没显示那个时间点发送的邮件信息，则发送成功，也可以通过看自己收到邮件了没有来判断。

注意：一个账号对应一个PROFILE,否则可能会报错。

2 建操作员

2.1：在‘对象管理器’里，展开‘SQL SERVER代理’，右击‘操作员’，点‘新建操作员’，在‘姓名’，‘电子邮件名称’里输入相应名称。

点击‘确定’。

3 设置‘SQL SERVER代理’

3.1：右击‘SQL SERVER代理’，点‘属性’，点击左边的‘警报系统’，勾选‘启用邮件配置文件’，在邮件配置文件里选择自己上面刚建的配置文件profile1，确保勾选‘在通知信息中包含电子邮件正文‘，点击’确定’.

3.2：重启SQL代理来激活这些设置

右击‘SQL SERVER代理’，点‘重新启动’。

4 建作业

 4.1 作业名称

在‘对象管理器’里，展开‘SQL SERVER代理’，右击‘作业’，‘新建作业’

，在‘名称’处输入作业名，如job1.

4.2 作业步骤

4.2.1 步骤简述

该作业公分两个作业步骤。

² 用bcp将查询结果生成一个本地的EXCEL文件

² 执行发邮件的存储过程msdb.dbo.sp_send_dbmail，将步骤一生成的EXCEL文件当做附件发给相应人。

4.2.2 步骤一

在‘步骤’选项卡点击‘新建’，

，出现如下界面

输入步骤名称，选择相应的类型和数据库，在命令处输入SQL语句。也可以点击‘打开’，打开某个脚本。然后点‘分析’，分析命令是否正确，点‘确定’。

步骤一命令：

--将列名一起输出

EXEC master..xp_cmdshellN'bcp "SELECT ''员工编号'',''员工姓名'',''薪资'',''部门编号'',''ID'' UNION ALL SELECT CAST(empnoAS NVARCHAR(20)), CAST(ename AS NVARCHAR(20)),CAST(sal ASNVARCHAR(20)),CAST(deptno AS NVARCHAR(20)),CAST(id AS NVARCHAR(20)) FROMtest.dbo.emp" queryout d:\temp3.xls -c -S"ZB-BAIDD-PC" -U"sa" -P"baidandan"'

 --注意："SELECT ''员工编号'',''员工姓名''，SELECT前面是双引号，而员工姓名，员工编号那里都是两个单引号。

如图：

4.2.3 步骤二

步骤二命令：

msdb.dbo.sp_send_dbmail

   @profile_name = 'profile1',

   @recipients = 'v-白丹丹 <v-baidd@sinooceanland.com>',

   @query = 'SELECT * from emp'  ,

   @subject = 'emp表信息查询',

         @file_attachments='d:\Temp3.xls'

如图：

 

4.2.4 步骤间过度信息设置

在‘步骤’里的‘高级’选项卡中设置。

在第一步成功时，选择‘转到下一步’，第二步成功时‘退出报告成功的作业’。

4.3 计划

在‘计划’选项卡里，点击‘新建’，

 

进行相应设置即可。

如：每天在 0:00:00 和 16:59:59 之间、每 3 分钟 执行。将在 2014/1/20 到 2014/1/21 之间使用计划。

 

 4.4 通知

在‘通知’(Notifications)选项卡中，选择Email和刚才新建的Operator->选择当Job失败/成功/完成时……

三：收邮件

作业执行3分钟后就会收到邮件。

如图：

 

 

 附件内容：