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