将查询语句导出excel并发送SQLserver 邮件

最新推荐文章于 2022-12-06 14:45:14 发布

煮吧姐

最新推荐文章于 2022-12-06 14:45:14 发布

阅读量2.5k

点赞数 1

分类专栏： Sqlserver

本文链接：https://blog.csdn.net/sunwork888/article/details/44194101

版权

Sqlserver 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

exec master.dbo.xp_cmdshell 'del D:\kaoshi.xls';
go

--;EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 1;RECONFIGURE;--
--go

EXEC master..xp_cmdshell 'bcp "select hrid as 姓名,开始时间 as 日期,分数 from [126.917.312.17].dcwj.dbo.项目_考生 where 开始时间 >(cast(year(getdate()-1) as nvarchar(20))+''-''+cast(month(getdate()-1) as nvarchar(20))+''-''+cast(day(getdate()-1) as nvarchar(20))+'' 00:00:00'') and 开始时间 <(cast(year(getdate()-1) as nvarchar(20))+''-''+cast(month(getdate()-1) as nvarchar(20))+''-''+cast(day(getdate()-1) as nvarchar(20))+'' 23:59:59'') ORDER BY 开始时间 desc" queryout d:kaoshi.xls -c -q -S"26.87.35.212,15136" -U"sa" -P"sa"'
--
--;EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 0;RECONFIGURE;--
--go

exec msdb..sp_send_dbmail
@profile_name= 'bdpicc',
@recipients = 'wei@qq.com;sun@bdpicc.com;',
@subject = '查询结果',
@body = '结果在附件中',
@file_attachments ='d:\kaoshi.xls'