(转)链接服务器——获取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 设置即可。
参考文档:
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 驱动程序:数据连接组件
最新文章
- DEDECMS标签调用汇总啊
- PHP操作数据库
- C#多线程线程
- [Java面试六]SpringMVC总结以及在面试中的一些问题.
- Spring Boot项目使用Flyway
- Android -- Adapter
- linux su和sudo命令的区别(转)
- C#获取机器码(转)
- js实现未知宽高的元素在指定元素中垂直水平居中
- 线程间操作无效: 从不是创建控件“”的线程访问它~~~的解决方法~ 线程间操作无效: 从不是创建控件“Control Name'”的线程访问它问题的解决方案及原理分析
- (转)android屏幕适配
- VS2010与QT的集成开发环境
- bzoj 1034 (田忌赛马++)
- Android开发手记(13) 几种Alertdialog的使用
- Fibonacci(数论 输出前四位Fibonacci)
- c++ primer plus 习题答案(7)
- VUE依赖webpack分别给开发环境和生产环境配置不同的常量值并在项目中动态引用
- 201521123073 《Java程序设计》第9周学习总结
- EBS获取并发程序Trace File
- BZOJ.1013.[JSOI2008]球形空间产生器(高斯消元)
热门文章
- laravel mapSpread 例子
- 1044 Shopping in Mars
- UVAL 7902 2016ECfinal F - Mr. Panda and Fantastic Beasts
- UDP ------ UDP 详解
- bcdiv bcmul
- python中高阶函数与装饰器
- Java基础-程序流程控制第二弹(循环结构)
- [理论篇]一.JavaScript中的死连接`javascript:void(0)`和空连接`javascript:;`
- mongo查询日期格式数据
- [整理]C语言中的a,&;a和&;a[0]