在SQL Server2008 中如何将数据导出到Excel表中

我在SQL Server2008 中，想把数据导出到Excel表中，查询了网上的执行语句如下：
EXEC master..xp_cmdshell 'bcp"SELECT [area],[testtime]FROM [smscenter].[sms].[servicestat] ORDER BY [area],[testtime]" querout D:\table.xlsx -c -S"132.168.0.198" -U"he" -P"123456"'
但是总是出现如下错误：
消息 15281，级别 16，状态 1，过程 xp_cmdshell，第 1 行
SQL Server 阻止了对组件 'xp_cmdshell' 的 过程'sys.xp_cmdshell' 的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 'xp_cmdshell'。有关启用 'xp_cmdshell' 的详细信息，请参阅 SQL Server 联机丛书中的 "外围应用配置器"。 
如果将'xp_cmdshell' 改为sp_configure 就会出现如下错误，
消息 15123，级别 16，状态 1，过程 sp_configure，第 51 行
配置选项 'bcp"SELECT [area],[testtime]FROM [s' 不存在，也可能是高级选项。
请教各位高手大侠们！

16 个解决方案

#1

在外围配置管理器里有个功能设置的，里面就有xp_cmdshell的勾选按钮！

#2

--SQL语句开xp_cmdshell

-- 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
-- To disallow advanced options to be changed.
EXEC sp_configure 'show advanced options', 0
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO

 

--如果要禁用，

-- 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 disable the feature.
EXEC sp_configure 'xp_cmdshell', 0
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

-- To disallow advanced options to be changed.
EXEC sp_configure 'show advanced options', 0
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO

#3

我再弱弱的问一句你说的外围配置管理器在哪里？

#4

SQL SERVER 和EXCEL的数据导入导出
1、在SQL SERVER里查询Excel数据:
-- ======================================================
SELECT * 
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
下面是个查询的示例，它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。
SELECT * 
FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
-------------------------------------------------------------------------------------------------

2、将Excel的数据导入SQL server :
-- ======================================================
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
实例:
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
-------------------------------------------------------------------------------------------------

3、将SQL SERVER中查询到的数据导成一个Excel文件
-- ======================================================
T-SQL代码：
EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'
参数：S 是SQL服务器名；U是用户；P是密码
说明：还可以导出文本文件等多种格式
实例:EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"'
EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword'
在VB6中应用ADO导出EXCEL文件代码： 
Dim cn  As New ADODB.Connection
cn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"
cn.execute "master..xp_cmdshell 'bcp "SELECT col1, col2 FROM 库名.dbo.表名" queryout E:\DT.xls -c -Sservername -Usa -Ppassword'"
------------------------------------------------------------------------------------------------

4、在SQL SERVER里往Excel插入数据:
-- ======================================================
insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...table1 (A1,A2,A3) values (1,2,3)

T-SQL代码：
INSERT INTO  
OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',  
'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$]  
(bestand, produkt) VALUES (20, 'Test')  

#5

在SQL Server2008 中如何把datetime格式的数据转换成字符格式？如下例：
2011-01-24 09:24:00.263->09:24AM

#6

引用 3 楼 wbj2009 的回复:

我再弱弱的问一句你说的外围配置管理器在哪里？

开始→所有程序→SQL配置管理

#7

引用 6 楼 wxf163 的回复:

引用 3 楼 wbj2009 的回复:

我再弱弱的问一句你说的外围配置管理器在哪里？
开始→所有程序→SQL配置管理

可是我找不到1楼所说的xp_cmdshell选项！

#8

那你就用2楼给你的代码执行下，记得要用具有管理权限的账户登入进去执行命令。

或者右击数据库，“任务”-“导入导出”也可以的。

引用 7 楼 wbj2009 的回复:

引用 6 楼 wxf163 的回复:
引用 3 楼 wbj2009 的回复:

我再弱弱的问一句你说的外围配置管理器在哪里？
开始→所有程序→SQL配置管理


可是我找不到1楼所说的xp_cmdshell选项！

#9

xp_cmdshell勾选

#10

学习了，就是刚开始有点看不懂。

#11

我找到外围配置器了，右击你的数据库，单击方面，出来一个查询方面对话框，在方面 选项里有外围配置管理器，

#12

谢谢各位了！

#13

但是又出现了新的问题，导不出数据，不生成Excel 文件，提示一下错误：
output
SQLState = 37000, NativeError = 4060
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]无法打开登录所请求的数据库 "[sms_center]"。登录失败。
SQLState = 28000, NativeError = 18456
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]用户 'sa' 登录失败。
NULL

这是什么原因？

#14

3、将SQL SERVER中查询到的数据导成一个Excel文件
-- ======================================================
T-SQL代码：
EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'
参数：S 是SQL服务器名；U是用户；P是密码

请问用Window身份验证登陆时，用户和密码是否需要填？

#15

一直很好奇，大神们的代码是回帖的时候现写的，还是以前做过一样的东西有备份。。。

#16

该回复于2013-07-26 13:57:38被管理员删除