他のデータベースとの接続

SQL サーバと他のデータベース(Oracle, Access, DB2, Excel, Textなど)との接続が必要な場合には、リンクサーバー、アドホッククエリ、パススルークエリから適切な方法で接続します。

１．目次

１．目次
２．他のデータベースとの接続
３．リンクサーバー
　３．１　ACCESSとの接続
　３．２　EXCELとの接続
４．アドホッククエリ
　４．１　OPENROWSET
　４．２　OPENDATASOURCE
５．パススルークエリ
　５．1　OpenQueryを使ったパススルークエリ
　５．２　OpenRowSetを使ったパススルークエリ

２．他のデータベースとの接続

SQL サーバと他のデータベース(Oracle, Access, DB2, Excel, Textなど)との接続には、次の方法があります。

接続方法	コマンド	どのような場合に使用するか
リンクサーバ	sp_addlinkserver	頻繁に使用する場合。
アドホッククエリ	OpenRowSet	あまり頻繁に使用しないので、リンクサーバを作るほどではない場合。ただし、接続の詳細設定(セキュリティ、タイムアウトなど)はできなくなる。
	OpenDataSource
パススルークエリ	OpenQuery	接続先のデータソースのクエリ文字列を使用する場合。
	OpenRowSet

３．リンクサーバー

リンクサーバーにより、簡単に Oracle や DB2, Excelなどにアクセスすることができるようになります。 リンクサーバーを設定するには、ストアドのsp_addlinkedserver を使用する方法と、Enterprise Managerから設定する方法があります。

Enterprise Managerからは、サーバ→セキュリティ→リンクサーバーで追加します。

sp_addlinkedserver 構文

sp_addlinkedserver [ @server = ] 'server'
    [ , [ @srvproduct = ] 'product_name' ]
    [ , [ @provider = ] 'provider_name' ]
    [ , [ @datasrc = ] 'data_source' ]
    [ , [ @location = ] 'location' ]
    [ , [ @provstr = ] 'provider_string' ]
    [ , [ @catalog = ] 'catalog' ]

３．１　ACCESSとの接続

ACCESSとの接続例

IF EXISTS(SELECT * from MyAccess...社員)
BEGIN
    print 'EXIST'
    EXEC sp_dropserver MyAccess
END
GO
EXEC sp_addlinkedserver 
    @server = 'MyAccess', 
    @provider = 'Microsoft.Jet.OLEDB.4.0', 
    @srvproduct = 'OLE DB Provider for Jet',
    @datasrc = 'C:\Documents and Settings\uchukamen\デスクトップ\Northwind.mdb'
GO
SELECT * from MyAccess...社員

ここで、リンクサーバへのアクセスには、リンクサーバ名.カタログ.スキーマ.オブジェクト名と指定する必要がありますが、Accessの場合はカタログ、スキーマは 無いので、『MyAccess...社員』という形でアクセスします。

以下では、実験のためにAccessのノースウィンドというサンプルデータベースを使用します。
ノースウィンドというサンプルデータベースは、Access 2003→ヘルプ→"サンプル　データベース"→"ノースウィンド サンプル　データベース"により、インストールすることができ、Northwind.mdbというファイルが作成されます。

ACCESSファイルをリンクサーバとして設定し、クエリした様子

３．２　EXCELとの接続

Accessとの接続例

IF EXISTS(SELECT * from MyExcel...社員) 
BEGIN 
　　print 'EXIST' 
　　EXEC sp_dropserver MyExcel 
END 
GO 
EXEC sp_addlinkedserver 
　　@server = 'MyExcel', 
　　@srvproduct = 'OLE DB Provider for Jet', 
　　@provider = 'Microsoft.Jet.OLEDB.4.0', 
　　@datasrc = 'C:\Documents and Settings\uchukamen\デスクトップ\社員.xls',
　　@provstr = 'Excel 8.0' 
GO SELECT * from MyExcel...社員

ここで、リンクサーバへのアクセスには、リンクサーバ名.カタログ.スキーマ.オブジェクト名と指定する必要がありますが、EXCELの場合もカタログ、スキーマは 無いので、『MyExcel...社員』という形でアクセスします。

以下では、実験のためにAccessのノースウィンドデータベースより、社員テーブルをEXCELにエクスポートしたファイルを使用します。。

EXCELファイルをリンクサーバとして設定し、クエリした様子

４．アドホッククエリ

リンクサーバほど頻繁に使用しない場合であれば、OpenRowSet, OpenDataSourceによるアドホッククエリがお気軽です。ただし、ユーザ名、パスワードを直書きするので、セキュリティ上注意が必要です。

４．１　OPENROWSET

構文

OPENROWSET ( 'provider_name'
    , { 'datasource' ; 'user_id' ; 'password'
        | 'provider_string' }
    , { [ catalog.] [ schema.] object
        | 'query' }
    )

ACCESSをOPENROWSETする例

SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Documents and Settings\uchukamen\デスクトップ\Northwind.mdb';'Admin';'', 社員)
GO
　

４．２　OPENDATASOURCE　

構文

OPENDATASOURCE ( provider_name, init_string )

EXCELをOPENDATASOURCEする場合

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="C:\Documents and Settings\uchukamen\デスクトップ\社員.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...社員

５．パススルークエリ

クエリ文字列をOLE DBデータソースに直接渡して処理をします。
OpenQuery とOpenRowSetの２つが使えます。

５．1　OpenQueryを使ったパススルークエリ

リンクサーバに対して、SQLサーバ上では処理せずに、クエリ文字列をOLE DBデータソースに渡します。

構文

OPENQUERY ( linked_server , 'クエリ文字列' )

OpenQueryを仕様した例

SELECT *
FROM OPENQUERY(MyAccess, 'SELECT * FROM 受注')

５．２　OpenRowSetを使ったパススルークエリ

OpenRowSetを使用した例

SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Documents and Settings\uchukamen\デスクトップ\Northwind.mdb';'Admin';'',
'SELECT * FROM 社員')
GO