结合链接服务器与分布式查询获取EXCEL中的数据测试解决方法

结合链接服务器与分布式查询获取EXCEL中的数据测试
基于之前发的一个帖子http://topic.csdn.net/u/20100913/14/af852c48-7f6d-4c8c-82ae-2c47f88d75ad.html

测试目的：验证利用链接服务器、分布式查询获取EXCEL中的数据
测试环境：
Microsoft SQL Server 2005 - 9.00.3080.00 (X64) 
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

基础知识及相关准备：
1、基于OLE DB访问的相关基础知识
2、创建链接服务器：sp_addlinkedserver
3、Openrowset

基于OLE DB访问的相关基础知识，参看下图
 
本次测试相关接口为：Microsoft OLE DB Provider for Jet
针对Excel不同版本请参考下表使用不同的OLE DB接口参数
Office Version Provider Provider_String
Office 97 ~2005 Microsoft.Jet.OLEDB.4.0Excel 5.0
Office 2007 Microsoft.ACE.OLEDB.12.0Excel 12.0[需安装组件或打ServicePack 1.0]

创建链接服务器

SQL code

DECLARE @RC intDECLARE @server nvarchar(128)DECLARE @srvproduct nvarchar(128)DECLARE @provider nvarchar(128)DECLARE @datasrc nvarchar(4000)DECLARE @location nvarchar(4000)DECLARE @provstr nvarchar(4000)DECLARE @catalog nvarchar(128)SET @server = 'XLTEST_SP'SET @srvproduct = 'Excel'SET @datasrc = 'c:\book1.xls'--注意因excel的版本不同，选择不同的接口参数SET @provider = 'Microsoft.Jet.OLEDB.4.0'  SET @provstr = 'Excel 8.0'EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider, @datasrc, @location, @provstr, @catalog--获取链接服务器对应sheet1范围内的数据SELECT * FROM [Excel]...[Sheet1$]--针对Microsoft.ACE.OLEDB.12.0，还需告知SQL Server如果处理USE [master]GOEXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0',N'AllowInProcess',1GOEXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0',N'DynamicParameters',1GO

OpenRowSet

SQL code

EXEC sp_configure 'show advanced options', 1GORECONFIGUREGOEXEC sp_configure 'Ad Hoc Distributed Queries', 1GORECONFIGUREGOselect  * into NewTableFROM OPENROWSET (  'Microsoft.ACE.OLEDB.12.0' ,  'Excel 12.0;Database=c:\book1.xlsx;HDR=YES' ,  'SELECT * FROM [sheet1$]' )END

经测试可获得对应excel工作表中的数据。

测试中的问题点及部分解决办法：
1、对应office97～2005创建链接服务器时，无对应的OLE DB 接口组件。
解决方法：
  安装office2005内版本即可获取到Microsoft.Jet.OLEDB.4.0接口组件。安装在数据源中可能无法获取到对应安装的文件。 还请在运行中使用regsvr32 msjetoledb40.dll 对其进行模块注册即可。

问题：
  Microsoft.Jet.OLEDB.4.0 至sp8后无更新的版本，且对应64位没有对应的更新。（据坛子里的朋友说有过64位的补丁，据现有查证没有找到）
建议：因Microsoft.Jet.OLEDB.4.0不再进行更新支持，对应于Office相关链接组件还请根据最新的office版本进行下载更新。

2、SQL Server 2005中对应Office2007类型文件，无对应OLE DB接口组件。
解决办法：
安装2007 Office system 驱动程序：数据连接组件即可。

下载地址：
http://www.microsoft.com/downloads/details.aspx?FamilyID=7554f536-8c28-4598-9b72-ef94e038c891&displayLang=zh-cn#QuickInfoContainer

3、对应于查找范围的一个小BUG
[Sheet1$]类似于此的查找范围，如果首字符为非英文字符时，分布式查询及链接服务器引用方式均无法显示的获得对应范围的数据。
不知是我的配置问题导致，还是都有的BUG还请查证。

4、针对提示RPC的问题
解决办法：

SQL code

USE master;EXEC sp_serveroption '服务器名称', 'rpc', 'true';  --启用RPC

参考文档：
如何将数据从 Excel 导入到 SQL Server
http://support.microsoft.com/kb/321686/zh-cn

How to use Excel with SQL Server linked servers and distributed queries
http://support.microsoft.com/kb/306397/EN-US/

PRB：当运行使用 OLE DB Provider for Microsoft Jet 的链接服务器查询时，出现错误 7399
http://support.microsoft.com/kb/814398/zh-cn

关于 Jet 4.0 Service Pack 8 的信息
http://support.microsoft.com/?kbid=829558#top

2007 Office system 驱动程序：数据连接组件
http://www.microsoft.com/downloads/details.aspx?FamilyID=7554f536-8c28-4598-9b72-ef94e038c891&displayLang=zh-cn


[解决办法]

学习。
[解决办法]

探讨

另外图没显示出来。怎么回事情...????

[解决办法]
奇怪 我是记得有64位oledb的补丁的,但是我这边没环境一直没有测试过,刚刚google了一下是这个
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=000364db-5e8b-44a8-b9be-ca44d18b059b&displaylang=en
可能记错了是ado的oledb for odbc驱动,不是office的jet的oledb驱动 抱歉
[解决办法]
刚刚顺便搜索了一下 可能微软认为office的环境不太会在64位(至少现在)的机器上运行,可能不会出oledb的64位补丁了,也就是说彻底抛弃jet oledb4了.查了一下ace.oledb是office2007中包含的,
另外问下楼主你的excel不知道是什么版本,不知道ace的oledb对老版本的excel兼容怎么样

查看更多 下一篇