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

最新推荐文章于 2021-08-04 08:52:19 发布

smahorse

最新推荐文章于 2021-08-04 08:52:19 发布

阅读量1.2k

点赞数 1

测试目的：验证利用链接服务器、分布式查询获取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 驱动程序：数据连接组件

最近更换了新服务器，操作系统Windows Server 2008 X64，数据库SQL Server 2008 X64，Office 2007（好像只有32位），在存储过程执行OpenDatasource导入Access数据的时候遇到问题了，Oledb 4.0已经不被支持，以下是遇到的若干错误提示：

因为 OLE DB 访问接口 'Microsoft.Jet.OLEDB.4.0' 配置为在单线程单元模式下运行，所以该访问接口无法用于分布式查询。

无法创建链接服务器 "(null)" 的 OLE DB 访问接口 "Microsoft.Ace.OLEDB.12.0" 的实例。

因为 OLE DB 访问接口 'Microsoft.Ace.OLEDB.12.0' 配置为在单线程单元模式下运行，所以该访问接口无法用于分布式查询。

解决方案是安装Office 2010 64位版，网上有很多下载，然后下载Microsoft Access Database Engine 2010 的X64版本，网上也有下载，两者缺一不可。

然后改一下OpenDatasource参数，将 Microsoft.Jet.OLEDB.4.0 改为 Microsoft.ACE.OLEDB.12.0，大功告成。

贴上存储过程部分代码：

--开启导入功能

exec sp_configure 'show advanced options',1

reconfigure

exec sp_configure 'Ad Hoc Distributed Queries',1

reconfigure

--允许在进程中使用ACE.OLEDB.12

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1

--允许动态参数

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

--导入临时表

exec ('insert into jihua(id,[批次号],Right('''+ @filepath +''',charindex(''\'',REVERSE('''+ @filepath +'''))-1),getdate() FROM OPENDATASOURCE (''Microsoft.ACE.OLEDB.12.0'', ''Data Source='+@filepath+';User ID=Admin;Password='' )...计划汇总表')

--注意这里，要先关闭外围的设置，然后再关闭高级选项

exec sp_configure'Ad Hoc Distributed Queries',0

reconfigure

exec sp_configure'show advanced options',0

reconfigure

--关闭ACE.OLEDB.12的选项

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 0

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 0