SQL将查询结果生成excel

liulucy2017 2017-11-03 09:24:44
如题,请问如何写一段SQL语句,将查询的数据集直接生成excel并以附件发送出去呢?

重点是前边,附件发送我查到了相关代码。

网上有说用EXEC master..xp_cmdshell ,但我的是windows验证,那U和P如何填写。

另外数据库好像禁用了xp_cmdshell ,这个是有什么风险呢?如果重新启用应该怎么办?

begin

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

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

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

set @file_path = 'C:\Users\luclliu\Desktop\三电'

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

set @exec_sql = 'select * from [NLO_DSC].[dbo].[T_LL_3E]' ---数据表使用的完整路径;

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

exec master..xp_cmdshell @exec_sql

end

GO


...全文
547 19 打赏 收藏 举报
写回复
19 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
liulucy2017 2017-11-08
引用 10 楼 liulucy2017 的回复:
[quote=引用 9 楼 yenange 的回复:] [quote=引用 8 楼 liulucy2017 的回复:] [quote=引用 6 楼 yenange 的回复:] 你的SQL Server版本是多少?
2012 那有没有别的办法可以实现自动导出excel呢[/quote] 用 SQL 作业来实现。 http://blog.csdn.net/yenange/article/details/58135245 步骤: 1. 用导入导出向导, 数据源是SQL Server相关的库( SQL语句 ), 目标是你磁盘上的文件(如:d:\tmpExcel\20171103.xlsx) 2. 发送邮件, 附件是上一步产生的文件。 当然, 如果你的数据量不大( 10万行内 ), 直接用 html 构建表格就可以了, 根本不需要什么附件 http://blog.csdn.net/yenange/article/details/52689781 [/quote] 您好,我按照您所给的导入导出向导的办法, 先在导入导出向导里生成那个包,我选的导出文件地址是我电脑本地的一个文件 然后设置Job运行但报错了(⊙o⊙)… 不知道是不是哪一步操作有问题 [/quote] html构建表格这个,最后生成的html只能用chrome打开嘛 还有我水平有点低,您那个生成html的代码是先用15行后创建 存储过程嘛 15行以前的搁哪儿
  • 打赏
  • 举报
回复
liulucy2017 2017-11-08
引用 15 楼 yenange 的回复:
http://download.csdn.net/download/yenange/9933743 你写批处理执行这个导数据的操作, 保存为bat 文件, 再在sql作业中调用这个 bat 文件。 http://blog.csdn.net/yenange/article/details/78436083 在作业的下一步骤, 执行发邮件的操作就可以了。 这个够简单了, 基本不用担心不兼容什么的。
引用 15 楼 yenange 的回复:
http://download.csdn.net/download/yenange/9933743 你写批处理执行这个导数据的操作, 保存为bat 文件, 再在sql作业中调用这个 bat 文件。 http://blog.csdn.net/yenange/article/details/78436083 在作业的下一步骤, 执行发邮件的操作就可以了。 这个够简单了, 基本不用担心不兼容什么的。
我感觉我之前那样实现不了可能是因为导出的excel地址是本机地址的原因…… 另外windows验证情况下这个批处理的txt怎么写呢,能否给个示范…… 不甚感激
  • 打赏
  • 举报
回复
liulucy2017 2017-11-08
引用 14 楼 ayalicer 的回复:
我开始也用 OPENROWSET 之类 后来自己写了个客户端程序 互导sql 与 excel 感觉也挺快 每秒2000~5000行左右
引用 14 楼 ayalicer 的回复:
我开始也用 OPENROWSET 之类 后来自己写了个客户端程序 互导sql 与 excel 感觉也挺快 每秒2000~5000行左右
客户端程序……感觉我更加没有办法理解了
  • 打赏
  • 举报
回复
吉普赛的歌 2017-11-08
引用 17 楼 liulucy2017 的回复:
我感觉我之前那样实现不了可能是因为导出的excel地址是本机地址的原因…… 另外windows验证情况下这个批处理的txt怎么写呢,能否给个示范…… 不甚感激
比如:ExportExcel.exe 这个文件放在 d:\tools\ 下面 , 1. 先配置好 config 文件, 双击看看是否能导出 excel 文件。确认没问题再进入下一步。 2. 将下面的文本保存为 d:\tools\ExportExcel.bat ;

d:\tools\ExportExcel.exe
3. 在sqlserver的作业中选择 powershell 类型的脚本, 文本框中为:

d:\tools\ExportExcel.bat
4. 设定计划, 多长时间执行一次。 这样就可以了。
  • 打赏
  • 举报
回复
吉普赛的歌 2017-11-07
http://download.csdn.net/download/yenange/9933743 你写批处理执行这个导数据的操作, 保存为bat 文件, 再在sql作业中调用这个 bat 文件。 http://blog.csdn.net/yenange/article/details/78436083 在作业的下一步骤, 执行发邮件的操作就可以了。 这个够简单了, 基本不用担心不兼容什么的。
  • 打赏
  • 举报
回复
我开始也用 OPENROWSET 之类 后来自己写了个客户端程序 互导sql 与 excel 感觉也挺快 每秒2000~5000行左右
  • 打赏
  • 举报
回复
吉普赛的歌 2017-11-06
引用 12 楼 liulucy2017 的回复:
[quote=引用 11 楼 yenange 的回复:] 改成 SQL Server 的, 不保存文件呢? 实在不行就输出 html , 直接作为邮件内容, 再发邮件就是了
没太明白什么意思?您能再看看嘛,我还是觉得导出成excel比较好。您那样操作是可以的是嘛[/quote] 你不是把那个包保存为文件了吗?我的意思是改成保存到SQL Server, 你再试下就知道
  • 打赏
  • 举报
回复
liulucy2017 2017-11-06
引用 11 楼 yenange 的回复:
改成 SQL Server 的, 不保存文件呢? 实在不行就输出 html , 直接作为邮件内容, 再发邮件就是了
没太明白什么意思?您能再看看嘛,我还是觉得导出成excel比较好。您那样操作是可以的是嘛
  • 打赏
  • 举报
回复
吉普赛的歌 2017-11-03
xp_cmdshell 是生产环境中 SQL Server 基线中不允许开启的重要部分, 不要随便开, 出了安全问题也不是你能负责
  • 打赏
  • 举报
回复
吉普赛的歌 2017-11-03
你的SQL Server版本是多少?
  • 打赏
  • 举报
回复
因为这种操作可以直接通过数据库来执行操作系统命令,比如 关机等操作,所以不安全,默认情况下,系统把这个参数给禁用了,你要用,可以通过楼上的命令,开启这个参数
  • 打赏
  • 举报
回复
中国风 2017-11-03
xp_cmdshell --可调用DOS命令,管理员可执行,只要控制好MASTER库权限就行
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
  • 打赏
  • 举报
回复
shadowpj 2017-11-03
1、登录方式没影响应该, -U是你数据库用户名 -P是用户名密码 2、我也是百度的。。 sp_configure 'show advanced options',1 reconfigure go sp_configure 'xp_cmdshell',1 reconfigure go 执行结果:   配置选项 'show advanced options' 已从 0 更改为 1。请运行 RECONFIGURE 语句进行安装。   配置选项 'xp_cmdshell' 已从 0 更改为 1。请运行 RECONFIGURE 语句进行安装。 如需关闭只需将“sp_configure 'xp_cmdshell',1”改为“sp_configure 'xp_cmdshell',0”即可。
  • 打赏
  • 举报
回复
xiaoxiangqing 2017-11-03
用OPENROWSET也可以
  • 打赏
  • 举报
回复
OwenZeng_DBA 2017-11-03
-u就是用户名,-p 就是数据库的密码 开启CMD_SHELL 是从安全角度有风险,因为如果对方通过SQL 注入进入数据库,就可以通过cmd_shell执行系统的命令
  • 打赏
  • 举报
回复
吉普赛的歌 2017-11-03
改成 SQL Server 的, 不保存文件呢? 实在不行就输出 html , 直接作为邮件内容, 再发邮件就是了
  • 打赏
  • 举报
回复
liulucy2017 2017-11-03
引用 9 楼 yenange 的回复:
[quote=引用 8 楼 liulucy2017 的回复:] [quote=引用 6 楼 yenange 的回复:] 你的SQL Server版本是多少?
2012 那有没有别的办法可以实现自动导出excel呢[/quote] 用 SQL 作业来实现。 http://blog.csdn.net/yenange/article/details/58135245 步骤: 1. 用导入导出向导, 数据源是SQL Server相关的库( SQL语句 ), 目标是你磁盘上的文件(如:d:\tmpExcel\20171103.xlsx) 2. 发送邮件, 附件是上一步产生的文件。 当然, 如果你的数据量不大( 10万行内 ), 直接用 html 构建表格就可以了, 根本不需要什么附件 http://blog.csdn.net/yenange/article/details/52689781 [/quote] 您好,我按照您所给的导入导出向导的办法, 先在导入导出向导里生成那个包,我选的导出文件地址是我电脑本地的一个文件 然后设置Job运行但报错了(⊙o⊙)… 不知道是不是哪一步操作有问题
  • 打赏
  • 举报
回复
吉普赛的歌 2017-11-03
引用 8 楼 liulucy2017 的回复:
[quote=引用 6 楼 yenange 的回复:] 你的SQL Server版本是多少?
2012 那有没有别的办法可以实现自动导出excel呢[/quote] 用 SQL 作业来实现。 http://blog.csdn.net/yenange/article/details/58135245 步骤: 1. 用导入导出向导, 数据源是SQL Server相关的库( SQL语句 ), 目标是你磁盘上的文件(如:d:\tmpExcel\20171103.xlsx) 2. 发送邮件, 附件是上一步产生的文件。 当然, 如果你的数据量不大( 10万行内 ), 直接用 html 构建表格就可以了, 根本不需要什么附件 http://blog.csdn.net/yenange/article/details/52689781
  • 打赏
  • 举报
回复
liulucy2017 2017-11-03
引用 6 楼 yenange 的回复:
你的SQL Server版本是多少?
2012 那有没有别的办法可以实现自动导出excel呢
  • 打赏
  • 举报
回复
相关推荐
发帖
疑难问题
加入

2.1w+

社区成员

MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
申请成为版主
帖子事件
创建了帖子
2017-11-03 09:24
社区公告
暂无公告