SqlServer 使用存储过程 导出为Excel

SqlServer 使用存储过程导出为Excel
一个脱离office组件的可以将语句结果导出到Excel的过程
--1.执行时所连接的服务器决定文件存放在哪个服务器
[sql]view plain copy
print?
1.CREATE PROC ExportFile
2. @QuerySql VARCHAR(max)
3. ,@Server VARCHAR(20)
4. ,@User VARCHAR(20) = 'sa'
5. ,@Password VARCHAR(20)
6. ,@FilePath NVARCHAR(100) = 'c:\ExportFile.csv'
7.AS
8.DECLARE @tmp VARCHAR(50) = '[##Table' + CONVERT(VARCHAR(36),NEWID())+']'
9.BEGIN TRY
10.DECLARE @Sql VARCHAR(max),@DataSource VARCHAR(max)='';
11.--判断是否为远程服务器
12. IF @Server <> '.'AND @Server <> '127.0.0.1'
13.SET @DataSource = 'OPENDATASOURCE(''SQLOLEDB'',''Data Source='+@Server+';User ID='+@Us
er+';Password='+@Password+''').'
14.--将结果集导出到指定的数据库
15.SET @Sql = REPLACE(@QuerySql,' from ',' into '+@tmp+ ' from ' + @DataSource)
16. PRINT @Sql
17.EXEC(@Sql)
18.
19.DECLARE @Columns VARCHAR(max) = '',@Data NVARCHAR(max)=''
20.SELECT @Columns = @Columns + ',''' + name +''''--获取列名（xp_cmdshell导出文件没有列名）
21. ,@Data = @Data + ',Convert(Nvarchar,[' + name +'])'--将结果集所在的字段更新为nvarchar（避
免在列名和数据union的时候类型冲突）
22.FROM tempdb.sys.columns WHERE object_id = OBJECT_ID('tempdb..'+@tmp)
23.SELECT @Data = 'SELECT ' + SUBSTRING(@Data,2,LEN(@Data)) + ' FROM ' + @tmp
24.SELECT @Columns = 'Select ' + SUBSTRING(@Columns,2,LEN(@Columns))
25.--使用xp_cmdshell的bcp命令将数据导出
26.EXEC sp_configure 'xp_cmdshell',1
27. RECONFIGURE
28.DECLARE @cmd NVARCHAR(4000) = 'bcp "' + @Columns+' Union All ' + @Data+'" queryout ' + @Fi
lePath + ' -c' + CASE WHEN RIGHT(@FilePath,4) = '.csv'THEN' -t,'ELSE''END + ' -T'
29. PRINT @cmd
30.exec sys.xp_cmdshell @cmd
31.EXEC sp_configure 'xp_cmdshell',0
32. RECONFIGURE
33.EXEC('DROP TABLE ' + @tmp)
34.END TRY
35.BEGIN CATCH
36.--处理异常
37. IF OBJECT_ID('tempdb..'+@tmp) IS NOT NULL
38.EXEC('DROP TABLE ' + @tmp)
39.EXEC sp_configure 'xp_cmdshell',0
40. RECONFIGURE
41.
42.SELECT ERROR_MESSAGE()
43.END CATCH
44.
45.
[sql]view plain copy
print?
1.--查询分析器连接哪个服务器，文件就在哪个服务器上
2.--本地导出
3.EXEC dbo.ExportFile @QuerySql = 'select * from sys.objects', -- varchar(max)
4. @Server = '.', -- varchar(20)
5. @FilePath = N'c:\1.xls'-- nvarchar(100)
6.
7.--远程导出
8.EXEC dbo.ExportFile @QuerySql = 'select * from master.sys.objects', -- varchar(max)
9. @Server = '******', -- varchar(20)
10. @User = '*****', -- varchar(20)
11. @Password = '******', -- varchar(20)
12. @FilePath = N'c:\2.xls'-- nvarchar(100)
有可能出现这种错误
消息15123，级别16，状态1，过程sp_configure，第51 行
配置选项'xp_cmdshell' 不存在，也可能是高级选项。

使用以下命令解决
[sql]view plain copy
print?
1.-- 允许配置高级选项
2.EXEC sp_configure 'show advanced options', 1
3.GO-- 重新配置
4.RECONFIGURE
5.GO
6.-- 启用xp_cmdshell
7.EXEC sp_configure 'xp_cmdshell', 0
8.GO--重新配置
9.RECONFIGURE
10.GO。