sqlserver配置定时任务查询SQL导出excel并以附件发送邮件

一、创建任务

打开：SQL Server Management Studio
1、创建存储过程

--创建存储过程【执行一次】(修改 Alter procedure [dbo].[pr_Employee_Bak]。。。)
Create procedure [dbo].[pr_Employee_Bak]

as

begin

---这里可以增加对数据表的查询条件或更多的数据处理；

---将结果放入一个新的数据表，然后将这个新表导出EXCEL文件；

declare @file_path varchar(200);--导出EXCEl文件的路径；

declare @file_name varchar(200);--导出EXCEl的文件名；

declare @exec_sql  varchar(200);--SQL语句；

---分开定义是为了以后修改路径或文件名更方便。

set @file_path = 'D:\task\'

set @file_name = 'dept' + CONVERT(varchar(100), GETDATE(), 112)+'.xls'

set @exec_sql = 'select id,name from instructions_test.dbo.employee'  ---数据表使用的完整路径；
-- instructions_test:数据库； employee：表名

set @exec_sql = ' bcp "'+@exec_sql+'" queryout "'+@file_path+''+@file_name+'" -c -T -U "sa" -P "SQLpassword"';

----通过bcp将查询结果导出为excel：U "sa" -P "SQLpassword" 这是数据库的sa账号和密码；

exec master..xp_cmdshell @exec_sql

end

2、建立文件夹
以上存储过程pr_Employee_Bak建立，需要导出的数据表是employee，导出的位置是数据库本地计算机的D:\task\文件夹下。需要将这个文件夹建立好。

3、SQL Server启用xp_cmdshell组件

sp_configure 'show advanced options',1
reconfigure
go
sp_configure 'xp_cmdshell',1
reconfigure
go

4、测试存储过程

--测试 执行任务步骤，执行以下SQL
[pr_Person_Bak]

若有问题根据执行结果错误提示进行修正。
可以导出到指定路径即成功。

存储过程问题记录：

1、bcp Copy direction must be either ‘in’ or ‘out’. Syntax Error in ‘queryout’.
原因：
该机器安装了sybase数据库，bcp命令识别成了sybase的命令。
解决方式：
1）找到Microsoft SQL server 的安装路径，
计算机–右键–属性–高级系统设置–环境变量–path里配置了sqlserver的路径，在D:\Microsoft SQL Server文件夹下搜索bcp，找到bcp.exe,复制修改名称为mbcp.exe

存储过程bcp命令改为mbcp命令。

2、bcp 报错 请输入 [char] 字段的文件存储类型
解决方式：
命令添加 -c

二、设置定时任务自动执行导出

1、SQLserver代理需要启动【若已启动跳过此步】
启动方式：
计算机右键–管理–服务和应用程序–服务，搜索sql server 代理–右键启动

2、在SQL Server Management Studio中，SQL Server代理-作业-新建作业

为作业定义名称

新增步骤


新增计划




3、测试任务：
该任务点击右键–作业开始步骤

成功后，到D:\task文件夹下，可以看到导出的EXCEL文件了
若失败，作业右键–查询历史记录–查看失败原因进行修正。

三、配置数据库邮件

（1）邮件服务设置

1、开启POP3、SMTP服务，授权码以最新生成为准，之前的会失效。

2、登录客户端

客户端图标


3、查看配置信息

（2）数据库邮件配置

第一种方式：

--SQLSERVER开启发邮件功能
exec sp_configure 'show advanced options',1
reconfigure with override 
go
 
exec sp_configure 'database mail xps',1
reconfigure with override 
go

--1.创建邮件帐户信息
exec msdb.dbo.sysmail_add_account_sp
  @account_name ='foxmail(wmm_lemon)',                   -- 邮件帐户名称   
  @email_address ='wmm_lemon@foxmail.com',         -- 发件人邮件地址    
  @display_name ='wmm_lemon',                       -- 发件人姓名 
  @MAILSERVER_NAME = 'smtp.qq.com',           -- 邮件服务器地址
  @PORT =465,                                    -- 邮件服务器端口 
  @USERNAME = 'wmm_lemon@foxmail.com',              -- 用户名 
  @PASSWORD = 'aecemvnpoooobedb'                    -- 密码 （授权码） 
GO
--2.数据库配置文件
exec msdb.dbo.sysmail_add_profile_sp
  @profile_name = 'foxmail(wmm_lemon)',                   -- 配置名称 
  @description = '数据库配置-DBCC'              -- 配置描述
go
 
--3.用户和邮件配置文件相关联
exec msdb.dbo.sysmail_add_profileaccount_sp
  @profile_name = 'foxmail(wmm_lemon)',                   -- 配置名称
  @account_name = 'foxmail(wmm_lemon)',                   -- 邮件帐户名称    
  @sequence_number = 1                          -- account 在 profile 中顺序（默认是1）
GO
 
--4.测试邮件是否成功
exec msdb.dbo.sp_send_dbmail
@profile_name = 'foxmail(wmm_lemon)',                     --配置名称
@recipients = 'wmm_lemon@foxmail.com',                --收件名称
@body_format = 'HTML',                          --内容格式
@subject = '我是标题',
@body = '我是内容',
--@file_attachments = 'D:\task\employee20191211.xls' --发送附件【该路径下存在文件】

第二种方式：
打开SQL Server Management Studio





端口默认25，不修改。

数据库邮件配置问题记录：

1、参数或变量中有语法错误。 服务器响应为:mail from address must be same as authorization user原因：
邮件的授权码错误
解决方式：
输入正确的邮箱授权码。

四、定时任务发送邮件

1、创建邮件存储过程

create procedure [dbo].[EmailSend]

as

begin

---这里可以增加对数据表的查询条件或更多的数据处理；

---将结果放入一个新的数据表，然后将这个新表导出EXCEL文件；

declare @file_path varchar(200);--导出EXCEl文件的路径；

declare @file_name varchar(200);--导出EXCEl的文件名；

declare @file_pathname varchar(200);--导出EXCEl的文件名；

declare @exec_sql  varchar(200);--SQL语句；

---分开定义是为了以后修改路径或文件名更方便。

set @file_path = 'D:\task\'

set @file_name = 'employee' + CONVERT(varchar(100), GETDATE(), 112)+'.xls'

set @file_pathname = @file_path+@file_name

--set @exec_sql = 'select * from instructions_test.dbo.employee'  ---数据表使用的完整路径；

--set @exec_sql = ' bcp "'+@exec_sql+'" queryout "'+@file_path+''+@file_name+'" -c -T -U "sa" -P "Ym2019.sqlserver"';

----U "sa" -P "SQLpassword" 这是数据库的sa账号和密码；

--exec master..xp_cmdshell @exec_sql
--4.测试邮件是否成功
exec msdb.dbo.sp_send_dbmail
@profile_name = 'foxmail(wmm_lemon)',                     --配置名称
@recipients = 'wmm_lemon@foxmail.com',                --收件名称
@body_format = 'HTML',                          --内容格式
@subject = '员工表',								--主题
@body = @file_pathname,							--内容
@file_attachments = @file_pathname				--附件

end

2、重复一步骤二步骤创建邮件作业，定时发送邮件。

五、作业执行成功或失败的邮件提醒

SQLserver代理–操作员–新建操作员