現在オフラインです。再接続するためにインターネットの接続を待っています

SQL Server のリンク サーバーおよび分散クエリで Excel を使用する方法

概要
Microsoft SQL Server では、他の OLE DB データ ソースへの永続的またはアドホックな接続をサポートしています。永続的な接続はリンク サーバーと呼ばれ、単独のクエリのために作成されるアドホックな接続は分散クエリと呼ばれます。

Microsoft Excel ブックは、この方法で SQL Server を通じてクエリを実行できる OLE DB データ ソースの一種です。この資料では、Excel データ ソースをリンク サーバーとして構成するために必要な構文、および Excel データ ソースへのクエリを実行する分散クエリを使用するために必要な構文について説明します。
詳細

リンク サーバー上の Excel データ ソースへのクエリを実行する

SQL Server Management Studio または Enterprise Manager、システム ストアド プロシージャ、SQL-DMO (分散管理オブジェクト) または SMO (SQL Server 管理オブジェクト) を使用して、SQL Server のリンク サーバーとして Excel データ ソースを構成できます (SMO は Microsoft SQL Server 2005 の場合のみ使用できます)。いずれの場合も、必ず次の 4 つのプロパティを設定する必要があります。
  • リンク サーバーに使用する名前。
  • 接続に使用される OLE DB プロバイダ。
  • データ ソース、または Excel ブックの絶対パスとファイル名。
  • Excel ブックをターゲットとして指定するプロバイダ文字列。デフォルトでは、Jet プロバイダでは Access データベースが想定されています。
システム ストアド プロシージャ sp_addlinkedserver にも @srvproduct プロパティが必要です。このプロパティには、任意の文字列値を設定できます。

: SQL Server 2005 を使用して Excel データ ソースを構成する場合、SQL Server Management Studio で "製品名" プロパティの値を指定するか、ストアド プロシージャで @srvproduct プロパティの値を指定する必要があります。

SQL Server Management Studio または Enterprise Manager を使用して Excel データ ソースをリンク サーバーとして構成する

SQL Server Management Studio (SQL Server 2005)
  1. SQL Server Management Studio で、[オブジェクト エクスプローラ] の [サーバー オブジェクト] を展開します。
  2. [リンク サーバー] を右クリックし、[新しいリンク サーバー] をクリックします。
  3. 左側のウィンドウの [全般] ページで、次の手順を実行します。
    1. 最初のテキスト ボックスにリンク サーバーの名前を入力します。
    2. [その他のデータ ソース] をクリックします。
    3. [プロバイダ] ボックスの一覧の [Microsoft Jet 4.0 OLE DB Provider] をクリックします。
    4. [製品名] ボックスに OLE DB データ ソース名として Excel と入力します。
    5. [データ ソース] ボックスに Excel ファイルの絶対パスとファイル名を入力します。
    6. [プロバイダ文字列] ボックスに、Excel 2002、Excel 2000、または Excel 97 のブック用に Excel 8.0 と入力します。
    7. [OK] をクリックして、新しいリンク サーバーを作成します。
: SQL Server Management Studio では、新しいリンク サーバー名を展開して、サーバーに格納されているオブジェクトの一覧を表示することはできません。
Enterprise Manager (SQL Server 2000)
  1. Enterprise Manager で、[セキュリティ] フォルダを展開します。
  2. [リンク サーバー] を右クリックし、[新規リンク サーバー] をクリックします。
  3. [全般] タブで、次の手順を実行します。
    1. 最初のテキスト ボックスにリンク サーバーの名前を入力します。
    2. [サーバーの種類] の下で、[その他のデータ ソース] をクリックします。
    3. [プロバイダ名] ボックスの一覧の [Microsoft Jet 4.0 OLE DB Provider] をクリックします。
    4. [データ ソース] ボックスに Excel ファイルの絶対パスとファイル名を入力します。
    5. [プロバイダ文字列] ボックスに、Excel 2002、Excel 2000、または Excel 97 のブック用に Excel 8.0 と入力します。
    6. [OK] をクリックして、新しいリンク サーバーを作成します。
  4. 新規リンク サーバー名をクリックして展開し、格納されているオブジェクトの一覧を展開します。
  5. 新規リンク サーバー名の下にある [テーブル] をクリックします。右側のウィンドウに表示されるワークシートと名前の範囲を確認します。

ストアド プロシージャを使用して Excel データ ソースをリンク サーバーとして構成する

次のシステム ストアド プロシージャ sp_addlinkedserver を使用して、Excel データ ソースをリンク サーバーとして構成することもできます。
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 parameter valuesSET @server = 'XLTEST_SP'SET @srvproduct = 'Excel'SET @provider = 'Microsoft.Jet.OLEDB.4.0'SET @datasrc = 'c:\book1.xls'SET @provstr = 'Excel 8.0'EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider, @datasrc, @location, @provstr, @catalog				
上記のように、このストアド プロシージャでは、@srvproduct 引数に任意の文字列値を追加する必要があります。これは、Enterprise Manager および SQL Server Management Studio での構成では [製品名] として表示されます。@location 引数と @catalog 引数は使用されません。

SQL-DMO を使用して Excel データ ソースをリンク サーバーとして構成する

SQL 分散管理オブジェクトを使用して、Microsoft Visual Basic または他のプログラム言語を使用して、プログラムで Excel データ ソースをリンク サーバーとして構成することができます。Enterprise Manager および SQL Server Management Studio で構成する場合に必要な引数と同じ 4 つの引数を指定する必要があります。
Private Sub Command1_Click()    Dim s As SQLDMO.SQLServer    Dim ls As SQLDMO.LinkedServer    Set s = New SQLDMO.SQLServer    s.Connect "(local)", "sa", "password"    Set ls = New SQLDMO.LinkedServer    With ls        .Name = "XLTEST_DMO"        .ProviderName = "Microsoft.Jet.OLEDB.4.0"        .DataSource = "c:\book1.xls"        .ProviderString = "Excel 8.0"    End With    s.LinkedServers.Add ls    s.CloseEnd Sub				

SMO を使用して Excel データ ソースをリンク サーバーとして構成する

SQL Server 2005 では、SMO (SQL Server 管理オブジェクト) を使用して、プログラムで Excel データ ソースをリンク サーバーとして構成することができます。プログラミングには、Microsoft Visual Basic .NET を使用することも、他のプログラム言語を使用することもできます。SQL Server Management Studio で構成する場合に必要な引数を指定する必要があります。SMO オブジェクト モデルは、SQL-DMO (分散管理オブジェクト) オブジェクト モデルが拡張され、置き換えられたものです。SMO は SQL Server 7.0、SQL Server 2000 および SQL Server 2005 と互換性があるため、SMO を使用して SQL Server 2000 を構成することもできます。
Imports Microsoft.SqlServer.Management.SmoImports Microsoft.SqlServer.Management.CommonPublic Class Form1    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click        Dim s As Server        Dim conn As ServerConnection        Dim ls As LinkedServer        conn = New ServerConnection("ServerName\InstanceName", "YourUesrName", "YourPassword")        s = New Server(conn)        Try            ls = New LinkedServer(s, "XLTEST_DMO")            With ls                .ProviderName = "Microsoft.Jet.OLEDB.4.0"                .ProductName = "Excel"                .DataSource = "c:\book1.xls"                .ProviderString = "Excel 8.0"            End With            ls.Create()            MessageBox.Show("New linked Server has been created.")        Catch ex As SmoException            MessageBox.Show(ex.Message)        Finally            ls = Nothing            If s.ConnectionContext.IsOpen = True Then                s.ConnectionContext.Disconnect()            End If        End Try    End SubEnd Class

リンク サーバー上の Excel データ ソースへのクエリを実行する

Excel データ ソースをリンク サーバーとして構成した後、クエリ アナライザまたは他のクライアント アプリケーションから簡単に Excel データのクエリを実行できます。たとえば、Excel ファイルの Sheet1 に格納されているデータの行を取得するために、次のコードでは、SQL-DMO を使用して構成したリンク サーバーを使用します。
SELECT * FROM XLTEST_DMO...Sheet1$				
OPENQUERY を使用して、次に示すようにパススルー方式で Excel リンク サーバーへのクエリを実行することもできます。
SELECT * FROM OPENQUERY(XLTEST_DMO, 'SELECT * FROM [Sheet1$]')				
OPENQUERY に必要な最初の引数は、リンク サーバー名です。上記のように、複数のワークシート名の間には区切り記号が必要です。

次のクエリを使用して、Excel リンク サーバーで使用できるすべてのテーブルの一覧を取得することもできます。
EXECUTE SP_TABLES_EX 'XLTEST_DMO'				

分散クエリを使用して Excel データ ソースへのクエリを実行する

SQL Server の分散クエリと OPENDATASOURCE 関数または OPENROWSET 関数を使用して、アクセス頻度の低い Excel データ ソースに対してアドホックにクエリを実行することができます。

: SQL Server 2005 を使用している場合は、SQL Server セキュリティ構成を使用して Ad Hoc Distributed Queries オプションを有効にする必要があります。次に例を示します。
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',   'Data Source=c:\book1.xls;Extended Properties=Excel 8.0')...Sheet1$				
OPENROWSET の 2 番目の (プロバイダ文字列) 引数には、通常とは異なる構文を使用していることに注意してください。
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',    'Excel 8.0;Database=c:\book1.xls', Sheet1$)				
ADO (ActiveX Data Objects) 開発者は、OPENROWSET の 2 番目 (プロバイダ文字列) の引数に次の構文を使用することがあります。
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',    'Data Source=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$)				
この構文では、Jet プロバイダで次のエラーが発生します。
インストール可能な ISAM ドライバが見つかりませんでした。
: このエラーは、Data Source ではなく DataSource と入力した場合にも発生します。たとえば、次の引数は正しくありません。
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'DataSource=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$) 				
関連情報
SQL Server リンク サーバーと分散クエリは OLE DB プロバイダを使用するため、この場合も Excel で ADO を使用する場合の一般的なガイドラインおよび注意事項が適用されます。関連情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
257819 [HOWTO] Visual Basic または VBA から ADO を Excel データで使用する
SQL Server 管理オブジェクトの詳細については、次の MSDN (Microsoft Developer Network) Web サイトを参照してください。Ad Hoc Distributed Queries オプションを有効にする方法の詳細については、次の MSDN Web サイトを参照してください。
プロパティ

文書番号:306397 - 最終更新日: 05/10/2011 00:45:00 - リビジョン: 7.0

  • kbhowto kbdatabase kbjet kbsqlmanagementtools KB306397
フィードバック
html>ml>