链接服务器——获取EXCEL数据

测试目的：验证利用链接服务器、分布式查询获取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.0 Excel 5.0
Office 2007 Microsoft.ACE.OLEDB.12.0 Excel 12.0[需安装组件或打ServicePack 1.0]

创建链接服务器

DECLARE @RC int
DECLARE @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]
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0',N'AllowInProcess',1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0',N'DynamicParameters',1
GO

OpenRowSet
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO
select  * into NewTable
FROM 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 驱动程序：数据连接组件即可。

下载地址：点击打开链接

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

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

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

5、OLE DB 提供程序 'SQLOLEDB' 指出该对象中没有任何列

【解决方法】：

set fmtonly off  设置即可。

参考文档：

1、如何将数据从 Excel 导入到 SQL Server

2、How to use Excel with SQL Server linked servers and distributed queries

3、PRB：当运行使用 OLE DB Provider for Microsoft Jet 的链接服务器查询时，出现错误 7399

4、关于 Jet 4.0 Service Pack 8 的信息

5、2007 Office system 驱动程序：数据连接组件