概要
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 で、[オブジェクト エクスプローラ] の [サーバー オブジェクト] を展開します。
-
[リンク サーバー] を右クリックし、[新しいリンク サーバー] をクリックします。
-
左側のウィンドウの [全般] ページで、次の手順を実行します。
-
最初のテキスト ボックスにリンク サーバーの名前を入力します。
-
[その他のデータ ソース] オプションをクリックします。
-
[プロバイダー] ボックスの一覧の [Microsoft Jet 4.0 OLE DB Provider] をクリックします。
-
[製品名] ボックスに OLE DB データ ソース名として「Excel」と入力します。
-
[データ ソース] ボックスに Excel ファイルの絶対パスとファイル名を入力します。
-
[プロバイダー文字列] ボックスに、Excel 2002、Excel 2000、または Excel 97 のブック用に「Excel 8.0」と入力します。
-
[OK] をクリックして、新しいリンク サーバーを作成します。
-
注: SQL Server Management Studio では、新しいリンク サーバー名を展開して、サーバーに格納されているオブジェクトの一覧を表示することはできません。
Enterprise Manager (SQL Server 2000)
-
Enterprise Manager で、[セキュリティ] フォルダーを展開します。
-
[リンク サーバー] を右クリックし、[新しいリンク サーバー] をクリックします。
-
[全般] タブで、次の手順を実行します。
-
最初のテキスト ボックスにリンク サーバーの名前を入力します。
-
[サーバーの種類] の下で、[その他のデータ ソース] をクリックします。
-
[プロバイダー名] ボックスの一覧の [Microsoft Jet 4.0 OLE DB Provider] をクリックします。
-
[データ ソース] ボックスに Excel ファイルの絶対パスとファイル名を入力します。
-
[プロバイダー文字列] ボックスに、Excel 2002、Excel 2000、または Excel 97 のブック用に「Excel 8.0」と入力します。
-
[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 では、SQL Server 管理オブジェクト (SMO) を使用して、プログラムで 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 Surface Area 構成を使用して 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 を使用する場合の一般的なガイドラインおよび注意事項が適用されます。
詳細については、以下のサポート技術情報番号をクリックしてください。
257819 Visual Basic または VBA から ADO を Excel データで使用する方法
SQL Server 管理オブジェクトの詳細については、次の MSDN (Microsoft Developer Network) Web サイトを参照してください。
http://msdn2.microsoft.com/ja-jp/library/ms162169(ide).aspx Ad Hoc Distributed Queries オプションを有効にする方法の詳細については、次の MSDN Web サイトを参照してください。
