サインイン

Select the product you need help with

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)

SQL Server Management Studio で、[オブジェクトエクスプローラ] の [サーバーオブジェクト] を展開します。
[リンクサーバー] を右クリックし、[新しいリンクサーバー] をクリックします。
左側のウィンドウの [全般] ページで、次の手順を実行します。
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)

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

ストアドプロシージャを使用して 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$)

先頭へ戻る | フィードバック

プロパティ

文書番号: 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、その関連会社またはこれらの者の供給者がかかる損害の発生可能性を了知している場合を含みます。) 結果的損害または偶発的損害に対する責任の免除または制限を認めていない地域においては、上記制限が適用されない場合があります。なお、本文書においては、文書の体裁上の都合により製品名の表記において商標登録表示、その他の商標表示を省略している場合がありますので、予めご了解ください。

先頭へ戻る | フィードバック