学步园

 1、在SQL SERVER里往Excel插入数据

 2、将SQL SERVER中查询到的数据导成一个Excel文件

 

  

1、在SQL SERVER里往Excel插入数据

INSERT INTO OPENDATASOURCE('MICROSOFT.JET.OLEDB.4.0',
	'Excel 5.0;DATABASE=c:\book1.xls' )...[Sheet1$] 
	SELECT [BlogID],[Title]   FROM   [库名].[dbo].[Blog]

可能会遇到的问题：

1、"SQL Server 阻止了对组件 'Ad Hoc Distributed Queries' 的 STATEMENT 'OpenRowset/OpenDatasource' 的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 'Ad Hoc Distributed Queries'。有关启用 'Ad Hoc Distributed Queries' 的详细信息，请参阅 SQL Server 联机丛书中的 "外围应用配置器"。"

解决：

--启用Ad Hoc Distributed Queries：
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
    
--使用完成后，关闭Ad Hoc Distributed Queries：
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

 

2、"列名或所提供值的数目与表定义不匹配。"

解决：在book1.xls的Sheet1中必须有和excel表相对应的字段，否则会出错。可在插入前查询EXCEL表结构。

/*查看 xls表结构*/ 
SELECT * 
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[SHEET1$]

 

3、"消息 7399，级别 16，状态 1，第 2 行
链接服务器 "(null)" 的 OLE DB 访问接口 "Microsoft.Jet.OLEDB.4.0" 报错。提供程序未给出有关错误的任何信息。
消息 7303，级别 16，状态 1，第 2 行
无法初始化链接服务器 "(null)" 的 OLE DB 访问接口 "Microsoft.Jet.OLEDB.4.0" 的数据源对象。"

解决：确保已经该xls文件已经关闭。

 

 

2、将SQL SERVER中查询到的数据导成一个Excel文件

 EXEC master..xp_cmdshell 	
    'bcp "SELECT BlogID ,Title FROM [库名].[dbo].[Blog]"  queryout   c:\temp1.xls -c -q -S"6350BFDFB9DD413" -U"sa" -P"sa_123"'

/*
output
-----------------------------------------------------------------------------------------------------------
NULL
开始复制...
NULL
已复制 8 行。
网络数据包大小(字节): 4096
总时钟时间(毫秒)     : 1      平均值: (每秒 8000.00 行。)
NULL

(7 行受影响)
*/

可能会遇到的问题：

1、"SQL Server 阻止了对组件 'xp_cmdshell' 的 过程 'sys.xp_cmdshell' 的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 'xp_cmdshell'。有关启用 'xp_cmdshell' 的详细信息，请参阅 SQL Server 联机丛书中的 "外围应用配置器"。"

解决：

/*允许*/
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

/*关闭*/
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;

 

2、"复制方向必须是 'in'、'out' 或 'format'。
　　用法: bcp {dbtable | query} {in | out | queryout | format} 数据文件"

解决：都是双引号，还有所有代码都放在一行，不允许回车或者换行

 

 

引用自：

SQL SERVER 与ACCESS、EXCEL的数据转换  http://www.microsoft.com/china/community/column/31.mspx

SQL Server 阻止了对组件 ''Ad Hoc Distributed Queries'' 的 STATEMENT''OpenRowset/OpenDatasource'' 的访问

导入EXCEL数据到SQL  http://topic.csdn.net/u/20091213/19/72280C51-B7D8-43E0-9964-A7142604F0B9.html

bcp 执行老是 提示 复制方向必须是 'in'、'out' 或 'format'。那位高手指教一下