OPENROWSET による外部データのクエリ

56

第1引数の「SQLNCLI」は、SQL Server Native Client 11.0 を利用するという意味で、第2 引数では、「Server=」に続けて、接続したい SQL Server の名前（画面は BAMBOO）を指定 します。また、「;」（セミコロン）を記述して、「;Trusted_Connection=yes;」と記述すること で、Windows 認証での接続を試みることができます（セミコロンの部分を、カンマにしないよう に注意してください）。第 3引数には、リモートの SQL Server で実行したい SELELCT ステー トメントを記述します。

このように、OPENROWSET 関数を利用すると、リモート（外部データ）を簡単にクエリするこ とができます。また、OPENROWSET で取得した結果に対して、Step2 で説明した SELECT ..

INTO を利用すれば、結果をもとにテーブルを作成することもできます。

Access データベース（.accdb／.mdb）をクエリする場合

OPENROWSET では、前述の「Access データベース エンジン 2010 再頒布コンポーネント」

をインストールしていれば、Access のデータベース（.accdb／.mdb）をクエリすることもでき ます。これは、次のように利用します（Access 2010 データベースへ接続する場合）。

SELECT * FROM

OPENROWSET('Microsoft.ACE.OLEDB.12.0'

, 'C:\temp\ノースウィンド.accdb';'admin';'' , 'SELECT * FROM 社員')

Access データベースへ接続する場合には、第1引数へ「Microsoft.ACE.OLEDB 12.0」を指定

し、第2引数には .accdb または .mdb ファイルへのパスを記述します（ここでは、Access 2010 のノースウィンド サンプル データベースへのパスを記述しています）。パスに続けて、「;」（セミ コロン）を記述し、「;'admin';''」と指定します。これは Access に接続するためのユーザー名と パスワードの指定ですが、デフォルトでは admin／パスワードなしで接続できます。また、セミ コロンの部分は、カンマにしないように注意してください。第3引数には、"SELECT * FROM 社 員" のように Access データベースに対して実行したい任意の SELECT ステートメントを記述 できます。

Note： エラー「アクセスが拒否されました」、「列情報を取得できません」が表示される場合

実行時には、次のように「アクセスが拒否されました」および「列情報を取得できません」エラーが発生する場合があり ます。

このエラーは、次のように「AllowInProcess」と「DynamicParameters」プロパティを有効化（1へ設定）すること で回避できます。

これを実行してもエラーが出る場合は、SQL Server を再起動してみてください。それでもエラーが出る場合は、OS も 再起動してみてください。

なお、上記のプロパティは、オブジェクト エクスプローラーで、次のように［サーバー オブジェクト］の［リンク サー バー］→［プロバイダー］を展開して、「Microsoft.ACE.OLEDB 12.0」のプロパティを開き、「動的パラメーター」と

「InProcess 許可」をチェックしても設定することができます。

1 2

3

58

Note： エラー「データ ソース オブジェクトが初期化できませんでした」が表示される場合

実行時に、次のように「データ ソース オブジェクトが初期化できませんでした」エラーが発生する場合には、SQL Server のサービス アカウントの temp フォルダーに対する NTFS アクセス許可がない場合か、ファイルのパスを間違ってい る場合（存在しないフォルダー名やファイル名を指定している場合）などで発生します。

OPENROWSET 関数は、内部的に、サービス アカウントの temp フォルダー（既定では、C:\Users\<サービス アカ ウント名>\AppData\Local フォルダー） へアクセスするため、このフォルダーに対して、OPENROWSET の実行ユ ーザーがアクセス許可を持っている必要があります。

Note： 32ビット環境の場合は Jet で Access 2003 以前のデータベース（.mdb）を参照可能 32ビット環境では、Jet の OLEDB プロバイダーを利用して、Access 2003 以前のデータベース（.mdb ファイル）を クエリすることができます。これは、次のように利用できます。

SELECT * FROM

OPENROWSET('Microsoft.Jet.OLEDB.4.0'

, 'C:\temp\Northwind.mdb';'admin';'' , 'SELECT * FROM 社員')

この Jet プロバイダーは、SQL Server 2012 のインストール時に自動的にインストールされるので、「Access データ ベース エンジン2010再頒布コンポーネント」をインストールする必要はありません。ただし、この Jet プロバイダー は、32ビットのみの提供なので、X64環境では利用することができません（OPENROWSET は 64ビットで動作するた め）。

実行時に、「アクセスが拒否されました」および「列情報を取得できません」エラーが発生する場合は、次のように Jet プ ロバイダーに対して「AllowInProcess」と「DynamicParameters」プロパティを有効化することで回避できます。

Excel データ（.xlsx／.xls）をクエリ

OPENROWSET 関数では、Excel データ（.xlsx／.xls ファイル）をクエリすることもできます。

これは、次のように利用します（Excel 2010 データの場合）。

SELECT * FROM

OPENROWSET('Microsoft.ACE.OLEDB.12.0'

, 'Excel 12.0;Database=C:\temp\Shohin.xlsx;' , 'SELECT * FROM [Sheet1$]')

Excel 2010 へ接続する場合には、Access 2010 のときと同様、「Access データベース エンジ ン2010再頒布コンポーネント」を利用するので、第1引数には「Microsoft.ACE.OLEDB.12.0」

と指定します。第2引数には、「Excel 12.0;Database=C:\temp\Shohin.xlsx;」のように記 述します。Excel 12.0 と指定することで、Excel データであることを指定し、Database= に続 けて、.xlsx または .xls ファイルへのパスを記述します。第 3 引数には、"SELECT * FROM [Sheet1$]" のように記述し、シート名に「$」マークを付けて、大カッコ [ ] で囲みます。Excel 側で「名前付き範囲」を作成している場合は、＄マークなしで、大カッコ [ ] で囲んで名前付き範 囲を指定することもできます。

このように、OPENROWSET 関数を利用すると、Excel データも簡単にクエリすることができま す。なお、クエリする Excel ファイルが、Excel によって開かれている場合は、エラーになるの で、必ず Excel を終了してから、このクエリを実行するようにします。

Note： 32ビット環境の場合は Jet で Excel 2003 以前のファイル（.xls）を参照可能

32ビット環境では、Jet の OLEDB プロバイダーを利用して、Excel 2003 以前のファイル形式（.xls ファイル）をク エリすることができます。これは、次のように記述します。

SELECT * FROM

OPENROWSET('Microsoft.Jet.OLEDB.4.0'

, 'Excel 8.0;Database=C:\temp\Shohin.xls;' , 'SELECT * FROM [Sheet1$]')

この Jet の OLEDB プロバイダーは、32ビットのみの提供なので、X64環境では利用することができません。

Oracle データベースをクエリ

OPENROWSET 関数では、Oracle データベース（Oracle 9i や 10g、11g など）をクエリする こともできます。これは、次のように利用します（Oracle 11g R2 データベースの場合）。

SELECT * FROM

OPENROWSET('OraOLEDB.Oracle' , 'ORCL';'SCOTT';'tiger' , 'SELECT * FROM EMP')

C:¥temp¥Shohin.xlsx ファイル（Sheet1のデータ）

60

Oracle 11g R2 データベースへ接続する場合は、第1引数へ「OraOLEDB.Oracle」を指定し、

第2引数には、「'ORCL';'SCOTT';'tiger'」のように、ローカル・ネット・サービス名、ユーザー 名、パスワードの順に記述します。第3 引数には、"SELECT * FROM EMP" のように Oracle

Server に対して実行したい任意の SELECT ステートメントを記述できます。なお、Oracle デー

タベースへ接続するには、事前に「Oracle Client」ソフトウェアをインストールして、ローカル・

ネット・サービス名を設定しておく必要があります。

このように、OPENROWSET 関数を利用すると、Oracle データベースも簡単にクエリできるよう になります。

Note： エラー「アクセスが拒否されました」、「列情報を取得できません」が表示される場合

実行時には、次のように「アクセスが拒否されました」および「列情報を取得できません」が発生する場合があります。

このエラーは、次のように「AllowInProcess」と「DynamicParameters」プロパティを有効化（1へ設定）すること で回避できます。

これを実行してもエラーが出る場合は、SQL Server を再起動してみてください。それでもエラーが出る場合は、OS も 再起動してみてください。