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

时间:2022-05-11 09:20:08
我在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被管理员删除

#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被管理员删除