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

YIGEMOGUTOU1105

已于 2023-09-20 15:40:43 修改

阅读量1.2k

点赞数

文章标签： sqlserver sql 数据库

于 2023-02-23 15:34:33 首次发布

本文链接：https://blog.csdn.net/weixin_47053243/article/details/129182881

版权

该文章介绍了如何在SQLServer中创建一个存储过程，该过程用于执行查询，将结果导出为Excel文件，然后使用邮件服务将此Excel作为附件发送。涉及到的关键步骤包括设置数据导出路径，构建bcp命令，配置邮件发送的账户信息，以及使用SP_SEND_DBMAIL发送邮件。

摘要由CSDN通过智能技术生成

参考了站内这篇文章

sqlserver配置定时任务查询SQL导出excel并以附件发送邮件_柠檬--lemon的博客-CSDN博客_sqlserver 查询结果发送邮件

细化邮件配置的内容

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

as

begin

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

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

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

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

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

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

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

set @file_path = 'D:\TEST\'

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

set @file_pathname = @file_path+@file_name

set @exec_sql = 'SELECT SUBINV_CODE FROM scbi.dbo.DW_DIM_SUBINV'  ---数据表使用的完整路径；
-- instructions_test:数据库； employee：表名

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

--PRINT @exec_sql

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

EXEC master..xp_cmdshell @exec_sql


--配置邮件发送程序
DECLARE @ACCOUNT_ID INT,@ACCOUNT_NAME  NVARCHAR(200),@EMAIL_ADDR NVARCHAR(MAX),@ITEM NVARCHAR(200)

--创建邮件帐户信息
SET @ACCOUNT_NAME = 'TEST'
SELECT @ACCOUNT_ID = ACCOUNT_ID FROM MSDB..SYSMAIL_ACCOUNT WHERE NAME=@ACCOUNT_NAME
IF (@ACCOUNT_ID IS NOT NULL)
	BEGIN
		EXEC MSDB.DBO.SYSMAIL_DELETE_ACCOUNT_SP @ACCOUNT_ID, @ACCOUNT_NAME
	END
 
EXEC MSDB.DBO.SYSMAIL_ADD_ACCOUNT_SP 
     @ACCOUNT_NAME = @ACCOUNT_NAME,	            -- 邮件帐户名称    
     @EMAIL_ADDRESS = '',	-- 发件人邮件地址     
     @DISPLAY_NAME = '系统管理员',	            -- 发件人姓名  
     @REPLYTO_ADDRESS = NULL,
     @DESCRIPTION = 'TEST',
     @MAILSERVER_NAME = 'mail.unicloud.com',    -- 邮箱服务器地址
     @PORT = 587,                               -- 邮箱服务器端口 
     @USERNAME = '',                        -- 邮箱用户名 
     @PASSWORD = '',                  -- 邮箱密码  
     @USE_DEFAULT_CREDENTIALS = 0,
     @ENABLE_SSL = 0

--数据库配置文件
IF EXISTS( SELECT 1  FROM  MSDB.DBO.SYSMAIL_PROFILE WHERE  NAME = N'TEST')
	BEGIN	
		EXEC MSDB.DBO.SYSMAIL_DELETE_PROFILE_SP @PROFILE_NAME = 'TEST'
	END
EXEC MSDB.DBO.SYSMAIL_ADD_PROFILE_SP
	 @PROFILE_NAME = 'TEST',	    -- profile 名称  
	 @DESCRIPTION = '数据库邮件配置文件'	-- profile 描述    


--用户和邮件配置文件相关联
EXEC MSDB.DBO.SYSMAIL_ADD_PROFILEACCOUNT_SP 
     @PROFILE_NAME = 'TEST',	     -- PROFILE 名称   
     @ACCOUNT_NAME = 'TEST',	         -- ACCOUNT 名称     
     @SEQUENCE_NUMBER = 1                    -- ACCOUNT 在 PROFILE 中顺序

--配置发送对象
	SELECT @EMAIL_ADDR='xxxxx@xxxxxx.com'

	SET @ITEM='TEST'


EXEC MSDB.DBO.SP_SEND_DBMAIL
			@PROFILE_NAME = 'TEST',                 --配置名称
			@RECIPIENTS =@EMAIL_ADDR,	                      --接收邮件地址列表，可以多个，中间以分号分隔
			@SUBJECT = @ITEM,                               --邮件主题
			@file_attachments = @file_pathname,				--附件
			@body_format = 'HTML'



end