OPENROWSET (Transact-SQL)

[アーティクル]
12/16/2008

更新 : 2006 年 12 月 12 日

OLE DB データソースからリモートデータへのアクセスに必要な、すべての接続情報をインクルードします。これは、リンクサーバー内のテーブルにアクセスする代わりに、OLE DB を使用してリモートデータに接続しアクセスする特別な方法です。OLE DB データソースをより頻繁に参照する場合は、代わりにリンクサーバーを使用してください。詳細については、「サーバーのリンク」を参照してください。OPENROWSET 関数は、クエリの FROM 句の中でテーブル名として参照できます。OPENROWSET 関数は、INSERT、UPDATE、または DELETE ステートメントの対象テーブルとしても参照できます。ただしこれは OLE DB プロバイダの制限を受けます。クエリでは複数の結果セットが返される場合がありますが、OPENROWSET では最初の 1 つだけが返されます。

OPENROWSET では、組み込みの BULK プロバイダによる一括操作もサポートされ、ファイルのデータを行セットとして読み取り、返すことができます。

トピックリンクアイコン Transact-SQL 構文表記規則

構文

OPENROWSET 
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password' 
   | 'provider_string' } 
      , {   [ catalog. ] [ schema. ] object 
       | 'query' 
     } 
   | BULK 'data_file' , 
       { FORMATFILE = 'format_file_path' [ <bulk_options> ]
       | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} ) 

<bulk_options> ::=
   [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] 
   [ , ERRORFILE = 'file_name' ]
   [ , FIRSTROW = first_row ] 
   [ , LASTROW = last_row ] 
   [ , MAXERRORS = maximum_errors ] 
   [ , ROWS_PER_BATCH = rows_per_batch ]

引数

'provider_name'
レジストリの OLE DB プロバイダの表示名 (または PROGID) を表す文字列を指定します。provider_name に既定値はありません。

'datasource'
特定の OLE DB データソースに対応する文字列定数を指定します。datasource は DBPROP_INIT_DATASOURCE のプロパティで、プロバイダの IDBProperties インターフェイスに渡され、プロバイダの初期化に使用されます。一般的に、この文字列にはデータベースファイルの名前、データサーバーの名前、プロバイダがデータベースを検索する際に認識する名前のいずれかを指定します。

'user_id'
指定した OLE DB プロバイダに渡されるユーザー名を表す文字列定数を指定します。user_id は接続のセキュリティコンテキストを示し、DBPROP_AUTH_USERID プロパティとして渡され、プロバイダの初期化に使用されます。user_id に Microsoft Windows のログイン名は指定できません。

'password'
OLE DB プロバイダに渡されるユーザーパスワードを表す文字列定数を指定します。password は DBPROP_AUTH_PASSWORD プロパティとして引き渡され、プロバイダの初期化に使用されます。password に Microsoft Windows のパスワードは指定できません。

'provider_string'
プロバイダ固有の接続文字列を指定します。DBPROP_INIT_PROVIDERSTRING プロパティとして渡され、プロバイダの初期化に使用されます。provider_string には、通常、プロバイダを初期化するために必要なすべての情報がカプセル化されます。SQL Native Client OLE DB プロバイダで認識されるキーワードの一覧については、「初期化プロパティと承認プロパティ」を参照してください。

catalog
指定したオブジェクトが存在するカタログまたはデータベースの名前を指定します。

schema
指定したオブジェクトのスキーマまたはオブジェクト所有者の名前を指定します。

object
操作するオブジェクトを一意に識別するオブジェクト名を指定します。

'query'
プロバイダに送られ、プロバイダによって実行される文字列定数を指定します。SQL Server のローカルインスタンスでは、このクエリは処理されず、プロバイダから返されたクエリ結果が処理されます (パススルークエリ)。パススルークエリは、テーブル形式のデータをテーブル名ではなくコマンド言語のみで公開するプロバイダで使用すると便利です。クエリプロバイダで OLE DB Command オブジェクトと必要なインターフェイスがサポートされている限り、パススルークエリはリモートサーバー上でサポートされます。詳細については、「SQL Native Client (OLE DB) リファレンス」を参照してください。

BULK
ファイルからのデータ読み取りに OPENROWSET の BULK 行セットプロバイダを使用します。SQL Server 2005 では、OPENROWSET を使用すると、データを対象テーブルに読み込むことなくデータファイルからの読み取りを行えます。このため、OPENROWSET は簡単な SELECT ステートメントで使用できます。

BULK オプションの引数を使用すると、データの読み取りを開始および終了する場所や、エラーの取り扱い、データの解釈方法について、細かく制御することができます。たとえば、データファイルを varbinary、varchar、nvarchar 型の単一行、単一列の行セットとして読み取るように指定できます。既定の動作については、後の引数の説明を参照してください。

BULK オプションの使用方法については、後の「解説」を参照してください。BULK 操作に必要な権限については、後の「権限」を参照してください。

メモ :

OPENROWSET (BULK ...) を完全復旧モデルでデータのインポートに使用する場合、ログ記録は最適化されません。

一括インポートのデータの準備については、「一括エクスポートまたは一括インポートのデータの準備」を参照してください。

メモ :
OPENROWSET (BULK ...) を完全復旧モデルでデータのインポートに使用する場合、ログ記録は最適化されません。

'data_file'
データを対象テーブルにコピーするデータファイルの完全なパスを指定します。

FORMATFILE ='format_file_path'
フォーマットファイルの完全なパスを指定します。SQL Server 2005 では XML と非 XML の 2 種類のフォーマットファイルがサポートされます。

フォーマットファイルは、結果セットの列の型を定義する場合に必要となります。ただし SINGLE_CLOB、SINGLE_BLOB、または SINGLE_NCLOB を指定した場合は例外で、この場合はフォーマットファイルは必要ありません。

フォーマットファイルの詳細については、「データの一括インポートでのフォーマットファイルの使用」を参照してください。

< bulk_options >
BULK オプションの引数を 1 つ以上指定します。

CODEPAGE = { **'**ACP '| **'**OEM '| **'**RAW '| 'code_page' }
データファイル内のデータのコードページを指定します。CODEPAGE は、127 より大きいか、32 より小さい文字値の char、varchar、または text 型の列がデータに含まれる場合にのみ適用されます。

CODEPAGE の値	説明
ACP	char、varchar、または text 型の列を、ANSI/Microsoft Windows コードページ (ISO 1252) から SQL Server コードページに変換します。
OEM (既定値)	char、varchar、または text 型の列を、システムの OEM コードページから SQL Server コードページに変換します。
RAW	コードページの変換は行われません。これは最も高速なオプションです。
code_page	データファイルの文字データをエンコードするソースコードページを示します (例 : 850)。コードページは、SQL Server 2005 データベースエンジンが入力データを正しく解釈するために必要です。

ERRORFILE ='file_name'
形式エラーがあり、OLE DB 行セットに変換できない行を収集するときに使用するファイルを指定します。該当する行は、データファイルからこのエラーファイルに "そのまま" コピーされます。

エラーファイルはコマンドの実行開始時に作成されます。ファイルが存在する場合にはエラーが発生し、拡張子 .ERROR.txt の制御ファイルが作成されます。このファイルにはエラーファイルの各行と、エラーの診断が含まれています。エラーが修正されると、データは読み込み可能になります。

FIRSTROW **=**first_row
読み込み開始行の行番号を指定します。既定値は 1 です。指定したデータファイルの最初の行を示します。行番号は行ターミネータの数をカウントして決定されます。

LASTROW **=**last_row
読み込み終了行の行番号を指定します。既定値は 0 です。指定したデータファイルの最後の行を示します。

MAXERRORS **=**maximum_errors
フォーマットファイルで定義されている、構文エラーまたは無効な行の最大数を指定します。この数を超えると、OPENROWSET で例外が発生します。OPENROWSET では、無効な行は MAXERRORS に達するまで読み込まれずに無視され、無効な行はエラーとしてカウントされます。

maximum_errors の既定値は 10 です。

メモ :

MAX_ERRORS は、CHECK 制約または money および bigint 型の変換には適用されません。

メモ :
MAX_ERRORS は、CHECK 制約または money および bigint 型の変換には適用されません。

ROWS_PER_BATCH **=**rows_per_batch
データファイル内にあるデータ行の概算数を指定します。この値は実際の行数と同じ順序にする必要があります。

OPENROWSET では、常にデータファイルが単一のバッチとしてインポートされますが、rows_per_batch に値 > 0 を指定した場合、クエリプロセッサでは、クエリプランのリソース割り当てのヒントとして rows_per_batch の値が使用されます。

ROWS_PER_BATCH の既定値はありません。ROWS_PER_BATCH = 0 は、ROWS_PER_BATCH を省略した場合と同じになります。

SINGLE_BLOB
data_file の内容を、varbinary(max) 型の単一行、単一列の行セットとして返します。

重要 :
すべての Windows エンコード変換がサポートされるのは SINGLE_BLOB オプションだけなので、SINGLE_CLOB オプションや SINGLE_NCLOB オプションではなく、SINGLE_BLOB オプションだけを使用して XML データをインポートすることをお勧めします。

SINGLE_CLOB
data_file を ASCII として読み取り、現在のデータベースの照合順序に従い、内容を varchar(max) 型の単一行、単一列の行セットとして返します。

SINGLE_NCLOB
data_file を UNICODE として読み取り、現在のデータベースの照合順序に従い、内容を nvarchar(max) 型の単一行、単一列の行セットとして返します。

解説

OPENROWSET は、OLE DB データソースからリモートデータにアクセスするときに使用できます。ただしこの場合、指定したプロバイダに対して DisallowAdhocAccess レジストリオプションが明示的に 0 に設定されており、Ad Hoc Distributed Queries 詳細構成オプションが有効になっている必要があります。これらのオプションが設定されていない場合は、既定でアドホック接続は許可されません。

リモートの OLE DB データソースにアクセスするとき、信頼関係接続のログイン ID は、クライアントの接続先サーバーからクエリの対象サーバーに自動的に委任されるわけではありません。したがって、認証の委任を構成する必要があります。詳細については、「リンクサーバーの委任用の構成」を参照してください。

指定したデータソースにおいて、OLE DB プロバイダで複数のカタログとスキーマがサポートされる場合は、カタログ名とスキーマ名を指定する必要があります。OLE DB プロバイダでカタログとスキーマがサポートされない場合は、catalog と schema の値を省略してもかまいません。プロバイダでスキーマ名だけがサポートされる場合は、schema**.object という形式の 2 部構成の名前を指定する必要があります。プロバイダでカタログ名だけがサポートされる場合は、catalog.schema.**object という形式の 3 部構成の名前を指定する必要があります。SQL Native Client OLE DB プロバイダを使用するパススルークエリには、3 つの部分で構成される名前を指定する必要があります。詳細については、「Transact-SQL 構文表記規則 (Transact-SQL)」を参照してください。

OPENROWSET の引数に変数は指定できません。

OPENROWSET を BULK オプションと共に使用する

次に示す Transact-SQL の機能拡張では、OPENROWSET(BULK…) 関数がサポートされます。

SELECT 句と共に使用される FROM 句では、テーブル名の代わりに OPENROWSET(BULK…) を呼び出すことができます。このとき SELECT の機能に制限はありません。
OPENROWSET で BULK オプションを使用するには、FROM 句に相関名を指定する必要があります。これは範囲変数または別名とも呼ばれます。列には別名を指定できます。列の別名のリストを指定しない場合は、フォーマットファイルに列名が必要です。次のように、列の別名を指定した場合は、フォーマットファイルの列名よりも優先して使用されます。
FROM OPENROWSET(BULK...) AS table_alias
FROM OPENROWSET(BULK...) AS table_alias(column_alias,...n)
A SELECT…FROM OPENROWSET(BULK...) ステートメントでは、データをテーブルにインポートせずに、ファイル内のデータを直接クエリします。また、フォーマットファイルを使用して列名を指定することにより一括列の別名を列挙したり、データ型を列挙することもできます。
INSERT...SELECT * FROM OPENROWSET(BULK...) ステートメントは、データファイルから SQL Server テーブルへデータを一括インポートします。詳細については、「BULK INSERT または OPENROWSET(BULK...) を使用した一括データのインポート」を参照してください。
INSERT ステートメントと共に使用する OPENROWSET BULK オプションでは、BULK 句でテーブルヒントがサポートされます。BULK 句では、TABLOCK などの通常のテーブルヒント以外に、特殊なテーブルヒント IGNORE_CONSTRAINTS (CHECK 制約と FOREIGN KEY 制約だけを無視)、IGNORE_TRIGGERS、KEEPDEFAULTS、KEEPIDENTITY を使用できます。詳細については、「テーブルヒント (Transact-SQL)」を参照してください。

INSERT...SELECT * FROM OPENROWSET(BULK...) ステートメントの使用方法については、「一括データのインポートおよびエクスポート」を参照してください。一括インポート中に実行される行挿入操作がトランザクションログに記録されるタイミングの詳細については、「一括インポートで最小ログ記録を行うための前提条件」を参照してください。

メモ :
OPENROWSET を使用するにあたっては、SQL Server 2005 で権限借用がどのように処理されるかを理解しておくことが重要です。セキィリティ上の考慮事項については、「BULK INSERT または OPENROWSET(BULK...) を使用した一括データのインポート」を参照してください。

SQLXML ドキュメントの一括エクスポートまたは一括インポート

SQLXML データを一括エクスポートまたは一括インポートする場合、フォーマットファイルのデータ型には次のいずれかを使用します。

データ型	結果
SQLCHAR または SQLVARYCHAR	データは、クライアントコードページまたは照合順序で暗黙的に指定されるコードページで送られます。
SQLNCHAR または SQLNVARCHAR	データは Unicode として送られます。
SQLBINARY または SQLVARYBIN	データは変換なしで送られます。

権限

OPENROWSET の権限は、OLE DB プロバイダに渡されるユーザー名の権限によって決まります。BULK オプションを使用するには ADMINISTER BULK OPERATIONS 権限が必要です。

例

A. OPENROWSET を SELECT および SQL Native Client OLE DB プロバイダと共に使用する

次の例では、SQL Native Client OLE DB プロバイダ (SQLNCLI) を使用して、リモートサーバー Seattle1 のデータベース AdventureWorks のテーブル HumanResources.Department にアクセスします。SELECT ステートメントは、返す行セットの定義に使用します。プロバイダの文字列には、Server と Trusted_Connection キーワードが含まれます。これらのキーワードは、SQL Native Client OLE DB プロバイダによって認識されます。

SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
     'SELECT GroupName, Name, DepartmentID
      FROM AdventureWorks.HumanResources.Department
      ORDER BY GroupName, Name') AS a;

B. Microsoft OLE DB Provider for Jet を使用する

次の例では、Microsoft OLE DB Provider for Jet を介して、Microsoft Access Northwind データベース内のテーブル Customers にアクセスします。

メモ :
この例では、Access がインストールされていることが前提です。この例を実行するには、Northwind データベースをインストールする必要があります。Northwind データベースのインストール方法については、「Northwind サンプルデータベースと pubs サンプルデータベースのダウンロード」を参照してください。

この例では、Access がインストールされていることが前提です。この例を実行するには、Northwind データベースをインストールする必要があります。Northwind データベースのインストール方法については、「Northwind サンプルデータベースと pubs サンプルデータベースのダウンロード」を参照してください。

SELECT CustomerID, CompanyName
   FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
      'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
      'admin';'',Customers)
GO

C. OPENROWSET と INNER JOIN 内の別のテーブルを使用する

次の例では、ローカルの SQL Server Northwind データベース内のテーブル Customers、および同じコンピュータ上に格納されている Access Northwind データベースのテーブル Orders から、すべてのデータを選択します。

メモ :
この例では、Access がインストールされていることが前提です。この例を実行するには、Northwind データベースをインストールする必要があります。Northwind データベースのインストール方法については、「Northwind サンプルデータベースと pubs サンプルデータベースのダウンロード」を参照してください。

USE Northwind  
GO
SELECT c.*, o.*
FROM Northwind.dbo.Customers AS c 
   INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';'admin';'', Orders)    
   AS o 
   ON c.CustomerID = o.CustomerID 
GO

D. OPENROWSET を使用して、ファイルデータを varbinary(max) 列に一括挿入する

次の例では、小さなテーブルを作成し、ルートディレクトリ C: にあるファイル Text1.txt から varbinary(max) 列にファイルデータを挿入します。

USE AdventureWorks
GO
CREATE TABLE myTable(FileName nvarchar(60), 
  FileType nvarchar(60), Document varbinary(max))
GO

INSERT INTO myTable(FileName, FileType, Document) 
   SELECT 'Text1.txt' AS FileName, 
      '.txt' AS FileType, 
      * FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB) AS Document
GO

E. OPENROWSET BULK プロバイダをフォーマットファイルと共に使用して、テキストファイルから行を取得する

次の例では、フォーマットファイルを使用して、タブ区切りのテキストファイル values.txt から行を取得します。このテキストファイルには次のデータが含まれます。

1     Data Item 1
2     Data Item 2
3     Data Item 3

フォーマットファイル values.fmt では、values.txt の列が次のように表されています。

9.0
2
1  SQLCHAR  0  10 "\t"        1  ID                SQL_Latin1_General_Cp437_BIN
2  SQLCHAR  0  40 "\r\n"      2  Description        SQL_Latin1_General_Cp437_BIN

データを取得するクエリは次のようになります。

SELECT a.* FROM OPENROWSET( BULK 'c:\test\values.txt', 
   FORMATFILE = 'c:\test\values.fmt') AS a;

その他の例

INSERT...SELECT * FROM OPENROWSET(BULK...) のその他の使用例については、次のトピックを参照してください。

参照

その他の技術情報

分散クエリ
 一括データのインポートおよびエクスポート
 ユーザー定義関数 (データベースエンジン)

ヘルプおよび情報

SQL Server 2005 の参考資料の入手

変更履歴

リリース	履歴
2006 年 12 月 12 日	新しい内容 : IGNORE_CONSTRAINTS テーブルヒントでは、CHECK 制約と FOREIGN KEY 制約の両方が無視されることを追加。
2006 年 7 月 17 日	新しい内容 : OLE DB データソースをより頻繁に参照する場合は、OPENROWSET ではなくリンクサーバーを使用する必要があるという説明を追加。「解説」に以下の変更を追加。リモートの OLE DB データソースからダブルホップを介してアクセスするときに、認証委任を構成する必要があるという段落を追加。 SQL Native Client OLE DB プロバイダを使用するパススルークエリには、3 つの部分で構成される名前を指定する必要があるという説明を追加。 BULK オプションの使用に関して情報を追加。

2006 年 12 月 12 日

新しい内容 :

IGNORE_CONSTRAINTS テーブルヒントでは、CHECK 制約と FOREIGN KEY 制約の両方が無視されることを追加。

2006 年 7 月 17 日

新しい内容 :

OLE DB データソースをより頻繁に参照する場合は、OPENROWSET ではなくリンクサーバーを使用する必要があるという説明を追加。
「解説」に以下の変更を追加。
- リモートの OLE DB データソースからダブルホップを介してアクセスするときに、認証委任を構成する必要があるという段落を追加。
- SQL Native Client OLE DB プロバイダを使用するパススルークエリには、3 つの部分で構成される名前を指定する必要があるという説明を追加。
- BULK オプションの使用に関して情報を追加。

OPENROWSET (Transact-SQL)

構文

引数

解説

OPENROWSET を BULK オプションと共に使用する

SQLXML ドキュメントの一括エクスポートまたは一括インポート

権限

例

A. OPENROWSET を SELECT および SQL Native Client OLE DB プロバイダと共に使用する

B. Microsoft OLE DB Provider for Jet を使用する

C. OPENROWSET と INNER JOIN 内の別のテーブルを使用する

D. OPENROWSET を使用して、ファイルデータを varbinary(max) 列に一括挿入する

E. OPENROWSET BULK プロバイダをフォーマットファイルと共に使用して、テキストファイルから行を取得する

その他の例

参照

関連項目

その他の技術情報

ヘルプおよび情報

変更履歴

その他のリソース

OPENROWSET (Transact-SQL)

構文

引数

解説

OPENROWSET を BULK オプションと共に使用する

SQLXML ドキュメントの一括エクスポートまたは一括インポート

権限

例

A. OPENROWSET を SELECT および SQL Native Client OLE DB プロバイダと共に使用する

B. Microsoft OLE DB Provider for Jet を使用する

C. OPENROWSET と INNER JOIN 内の別のテーブルを使用する

D. OPENROWSET を使用して、ファイル データを varbinary(max) 列に一括挿入する

E. OPENROWSET BULK プロバイダをフォーマット ファイルと共に使用して、テキスト ファイルから行を取得する

その他の例

参照

関連項目

その他の技術情報

ヘルプおよび情報

変更履歴

その他のリソース

D. OPENROWSET を使用して、ファイルデータを varbinary(max) 列に一括挿入する

E. OPENROWSET BULK プロバイダをフォーマットファイルと共に使用して、テキストファイルから行を取得する