宇宙仮面の
C# Programming

 
SQL 2008

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

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

1.目次

1.目次
2.他のデータベースとの接続
3.リンクサーバー
 3.1 ACCESSとの接続
 3.2 EXCELとの接続
4.アドホッククエリ
 4.1 OPENROWSET
 4.2 OPENDATASOURCE
5.パススルークエリ
 5.1 OpenQueryを使ったパススルークエリ
 5.2 OpenRowSetを使ったパススルークエリ

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

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

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

3.リンクサーバー

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

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

Image

sp_addlinkedserver 構文

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

.1 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ファイルをリンクサーバとして設定し、クエリした様子

Image

.2 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ファイルをリンクサーバとして設定し、クエリした様子

Image

.アドホッククエリ

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

.1 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
 

Image

.2 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')...社員

Image

5.パススルークエリ

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

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

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

構文

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

OpenQueryを仕様した例

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


Image

.2 OpenRowSetを使ったパススルークエリ

OpenRowSetを使用した例

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

Image