在 SQL Server 中查询EXCEL 表中的数据遇到的各种问题

[sql]  view plain  copy
  1. SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="D:\KK.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]    
问题:
消息 15281,级别 16,状态 1,第 1 行
SQL Server 阻止了对组件 'Ad Hoc Distributed Queries' 的 STATEMENT 'OpenRowset/OpenDatasource' 的访问,因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 'Ad Hoc Distributed Queries'。有关启用 'Ad Hoc Distributed Queries' 的详细信息,请参阅 SQL Server 联机丛书中的 "外围应用配置器"。

解决:

[sql]  view plain  copy
  在CODE上查看代码片 派生到我的代码片
  1. exec sp_configure 'show advanced options',1   
  2. reconfigure  
  3. GO  
  4. exec sp_configure 'Ad Hoc Distributed Queries',1  
  5. reconfigure  
  6. GO  
  7.    
  8.   
  9. SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="D:\KK.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]    
  10.   
  11.   
  12. --exec sp_configure 'Ad Hoc Distributed Queries',0  
  13. --reconfigure  
  14. --GO  
  15. --exec sp_configure 'show advanced options',0  
  16. --reconfigure   
  17. --GO  

问题:

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

解决:

对象资源管理器——>服务器对象——>连接服务器——>访问接口——>Microsoft.Jet.OLEDB.4.0——>(右键属性,接口选项全不启用)


问题:

消息 7302,级别 16,状态 1,第 1 行
无法创建链接服务器 "(null)" 的 OLE DB 访问接口 "MSDASC" 的实例。


解决:

将SQL Server(MSSQLSERVER)和SQL Full-Filter Deamon Launcher(MSSQLSERVER)两个服务的登录身份改为LocalSystem



问题:

链接服务器"(null)"的 OLE DB 访问接口 "Microsoft.Jet.OLEDB.4.0" 返回了消息 "外部表不是预期的格式。"。
消息 7303,级别 16,状态 1,第 1 行
无法初始化链接服务器 "(null)" 的 OLE DB 访问接口 "Microsoft.Jet.OLEDB.4.0" 的数据源对象。

解决:

Microsoft.Jet.OLEDB.4.0是Microsoft Jet引擎,这适用于2003版本。在2007中,微软对其旗下 Access 与 Excel 的主要文件格式进行修改,并且重命名为 .accdb(Access 2007 数据库文件)与 .xlsx(Excel 2007 文件),因此未被 Microsoft Jet 引擎所支持,不过微软也很快的提出了Microsoft Office 2007 Desktop Drivers: Data Connectivity Components 来支持,目前的解决方法就是把连接字符串中的数据提供者改为 Microsoft.ACE.OLEDB.12.0。

下载安装: Microsoft Office 2007 Desktop Drivers: Data Connectivity Components   http://www.microsoft.com/zh-cn/download/details.aspx?id=23734

SELECT * FROM OpenDataSource( 'Microsoft.ACE.OLEDB.12.0','Data Source="D:\KK.KK.xlsx";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]  


问题:

消息 7314,级别 16,状态 1,第 1 行
链接服务器 "(null)" 的 OLE DB 访问接口 "Microsoft.ACE.OLEDB.12.0" 不包含表 "Sheet1$"。该表不存在,或者当前用户没有访问该表的权限。


最终解决:

在网上搜索一番,终于找到原因了!

原来我的excel列表名字不是Sheet1,而是 KK! 所以上面的所有  Sheet1$ 都改为 KK$!!


完整代码如下(两中写法):

[sql]  view plain  copy
  在CODE上查看代码片 派生到我的代码片
  1. --  
  2.   
  3.   
  4. exec sp_configure 'show advanced options',1   
  5. reconfigure  
  6. GO  
  7. exec sp_configure 'Ad Hoc Distributed Queries',1  
  8. reconfigure  
  9. GO  
  10.    
  11.   
  12. SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="D:\KK.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[KK$]    
  13. SELECT * FROM OpenDataSource( 'Microsoft.ACE.OLEDB.12.0','Data Source="D:\KK.xlsx";User ID=Admin;Password=;Extended properties=Excel 12.0')...[KK$]    
  14.   
  15. SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0''Excel 8.0;Database=D:\KK.xls','SELECT * FROM [KK$]')  
  16. select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=D:\KK.xlsx;HDR=YES','SELECT * FROM [KK$]')    
  17.   
  18.   
  19. exec sp_configure 'Ad Hoc Distributed Queries',0  
  20. reconfigure  
  21. GO  
  22. exec sp_configure 'show advanced options',0  
  23. reconfigure   
  24. GO  
  25.   
  26.   
  27. --  

------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------------------------

成功之后,试回原来的操作:


1. 以下这个对Microsoft.Jet.OLEDB.4.0没有影响,对 Microsoft.ACE.OLEDB.12.0 有影响(接口选项要全启用):

对象资源管理器——>服务器对象——>连接服务器——>访问接口——>Microsoft.Jet.OLEDB.4.0——>(右键属性,接口选项全不启用)



2. 以下两个服务身份由LocalSystem个改回 NetWord Service ,对Microsoft.Jet.OLEDB.4.0没有影响,对 Microsoft.ACE.OLEDB.12.0 有影响

将SQL Server(MSSQLSERVER)和SQL Full-Filter Deamon Launcher(MSSQLSERVER)两个服务的登录身份改为LocalSystem


SELECT * FROM OpenDataSource( 'Microsoft.ACE.OLEDB.12.0','Data Source="D:\KK.xlsx";User ID=Admin;Password=;Extended properties=Excel 12.0')...[KK$]  

错误:

消息 7302,级别 16,状态 1,第 1 行
无法创建链接服务器 "(null)" 的 OLE DB 访问接口 "MSDASC" 的实例。


select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=D:\KK.xlsx;HDR=YES','SELECT * FROM [KK$]') 

错误:

消息 7302,级别 16,状态 1,第 1 行
无法创建链接服务器 "(null)" 的 OLE DB 访问接口 "Microsoft.ACE.OLEDB.12.0" 的实例。




Questions About Using TSQL to Import Excel Data You Were Too Shy to Ask


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值