リンクサーバ:EXCELデータの取得

テスト目的:リンクサーバー、分散型クエリーを利用してEXCELのデータを取得するテスト環境を検証する:Microsoft SQL Server 2005-9.00.3080.00(X 64)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_StringOffice 97 ~2005 Microsoft.Jet.OLEDB.4.0 Excel 5.0Office 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、対応office 97～2005リンクサーバ作成時、対応するOLE DBインタフェースコンポーネントがない.
【解決策】:
office 2005のバージョンをインストールするとMicrosoftが入手できます.Jet.OLEDB.4.0インタフェースコンポーネント.データ・ソースにインストールすると、インストールに対応するファイルが取得できない場合があります.また、regsvr 32 msjetoledb 40を実行中に使用してください.dllはモジュール登録すればよい.
【問題】:
  Microsoft.Jet.OLEDB.4.0～sp 8以降は更新されていないバージョンであり、対応する64ビットには対応する更新がない.(かめの中の友人によると64ビットのパッチがあったそうで、既存の検証では見つからなかったそうです)
提案:Microsoft.Jet.OLEDB.4.0更新サポートは行われません.Office関連リンクコンポーネントに対応して、最新のofficeバージョンに従ってダウンロード更新を行ってください.
2、SQL Server 2005ではOffice 2007タイプのファイルに対応し、OLE DBインタフェースコンポーネントに対応していない.
【解決策】:
2007 Officeシステムドライバのインストール:データ接続コンポーネントでOK.
ダウンロード先:クリックしてリンクを開く
3、検索範囲に対応する小さなBUG[Sheet 1$]は、この検索範囲と同様であり、最初の文字が英語以外の文字である場合、分散クエリーおよびリンクサーバリファレンス方式では表示できない対応範囲のデータを取得する.私の配置の問題が原因なのか、それともすべてのバグがあるのか、まだ確認してください.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システムドライバ:データ接続コンポーネント