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

文書翻訳 文書翻訳
文書番号: 306397
すべて展開する | すべて折りたたむ

目次

概要

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 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 parameter values
SET @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.Close
End 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.Smo
Imports Microsoft.SqlServer.Management.Common

Public 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 Sub
End 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 サイトを参照してください。
http://msdn2.microsoft.com/ja-jp/library/ms162169(d=ide).aspx
Ad Hoc Distributed Queries オプションを有効にする方法の詳細については、次の MSDN Web サイトを参照してください。
http://msdn2.microsoft.com/ja-jp/library/ms189978(SQL.90,d=ide).aspx

プロパティ

文書番号: 306397 - 最終更新日: 2011年5月10日 - リビジョン: 7.0
キーワード:?
kbhowto kbdatabase kbjet kbsqlmanagementtools KB306397
Microsoft Knowledge Base の免責: Microsoft Knowledge Baseに含まれている情報は、いかなる保証もない現状ベースで提供されるものです。Microsoft Corporation及びその関連会社は、市場性および特定の目的への適合性を含めて、明示的にも黙示的にも、一切の保証をいたしません。さらに、Microsoft Corporation及びその関連会社は、本文書に含まれている情報の使用及び使用結果につき、正確性、真実性等、いかなる表明・保証も行ないません。Microsoft Corporation、その関連会社及びこれらの権限ある代理人による口頭または書面による一切の情報提供またはアドバイスは、保証を意味するものではなく、かつ上記免責条項の範囲を狭めるものではありません。Microsoft Corporation、その関連会社 及びこれらの者の供給者は、直接的、間接的、偶発的、結果的損害、逸失利益、懲罰的損害、または特別損害を含む全ての損害に対して、状況のいかんを問わず一切責任を負いません。(Microsoft Corporation、その関連会社 またはこれらの者の供給者がかかる損害の発生可能性を了知している場合を含みます。) 結果的損害または偶発的損害に対する責任の免除または制限を認めていない地域においては、上記制限が適用されない場合があります。なお、本文書においては、文書の体裁上の都合により製品名の表記において商標登録表示、その他の商標表示を省略している場合がありますので、予めご了解ください。

フィードバック

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com