OPENROWSET (Transact-SQL)

  • 發行項

更新： 2006 年 12 月 12 日

包含所有從 OLE DB 資料來源存取遠端資料所需的連接資訊。這個方法是存取連結伺服器資料表的另一個方法，而且是使用 OLE DB 來連接和存取遠端資料的單次特定方法。對於更常用到的 OLE DB 資料來源參考，請改用連結的伺服器。如需詳細資訊，請參閱＜連結伺服器＞。您可以依照資料表名稱的相同方式，在查詢的 FROM 子句中參考 OPENROWSET 函數。根據 OLE DB 提供者的能力而定，OPENROWSET 函數也可以被當做 INSERT、UPDATE 或 DELETE 陳述式的目標資料表加以參考。雖然查詢可以傳回多個結果集，但是 OPENROWSET 只能傳回第一個。

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 是指要傳遞到提供者 IDBProperties 介面，將提供者初始化所用的 DBPROP_INIT_DATASOURCE 屬性。這個字串通常都包含資料庫檔案的名稱、資料庫伺服器的名稱，或是提供者尋找資料庫所用的名稱。
  • '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'
    這是一個提供者特定的連接字串，被當做將 OLE DB 提供者初始化所用的 DBPROP_INIT_PROVIDERSTRING 屬性加以傳遞。provider_string 通常會封裝將提供者初始化所需的所有連接資訊。如需由 SQL Native Client OLE DB 提供者辨識的關鍵字清單，請參閱＜Initialization and Authorization Properties＞。
  • catalog
    這是指定物件所在的目錄或資料庫名稱。
  • schema
    這是指定物件的結構描述或物件擁有者名稱。
  • object
    這是唯一識別所用物件的物件名稱。
  • 'query'
    這是傳給提供者，並且由提供者執行的字串常數。SQL Server 的本機執行個體不會處理這項查詢，但會處理提供者傳回的查詢結果，亦即傳遞查詢。如果提供者不是透過資料表名稱，而只透過命令語言使用其資料表資料，不妨採用傳遞查詢。只要查詢提供者支援 OLE DB Command 物件及其必要介面，遠端伺服器就支援傳遞查詢。如需詳細資訊，請參閱＜SQL Native Client (OLE DB) Reference＞。

  • BULK
    使用 BULK 資料列集提供者，讓 OPENROWSET 讀取檔案資料。在 SQL Server 2005 中，OPENROWSET 可以從資料檔讀取，而不必將資料載入到目標資料表中。此舉可讓您搭配簡單的 SELECT 陳述式來使用 OPENROWSET。

    BULK 選項的引數，可讓您全力控制在哪裡開始和結束資料讀取、如何處理錯誤以及如何解譯資料。例如，您可以指定讓資料檔作為 varbinary、varchar 或 nvarchar 類型的單一資料列、單一資料行資料列集加以讀取。預設行為將在接下來的引數描述中加以描述。

    如需有關如何使用 BULK 選項的詳細資訊，請參閱本主題稍後的＜備註＞一節。如需有關 BULK 選項所需權限的詳細資訊，請參閱本主題稍後的「權限」一節。

    ms190312.note(zh-tw,SQL.90).gif附註：
    當它以完整復原模式匯入資料時，OPENROWSET (BULK ...) 不會將記錄最佳化。

    如需有關準備大量匯入資料的詳細資訊，請參閱＜準備大量匯出或匯入的資料＞。

  • 'data_file'
    這是要將其資料複製到目標資料表之資料檔的完整路徑。

  • FORMATFILE ='format_file_path'
    指定格式檔的完整路徑。SQL Server 2005 支援下列兩種類型的格式檔：XML 和非 XML。

    您必須使用格式檔，才能定義結果集中的資料行類型。不過，當指定 SINGLE_CLOB、SINGLE_BLOB 或 SINGLE_NCLOB 時，就不需要格式檔，這是唯一的例外狀況。

    如需有關格式檔的資訊，請參閱＜使用格式檔案大量匯入資料＞。

  • < bulk_options >
    為 BULK 選項指定一或多個引數。

  • CODEPAGE = { **'**ACP '| **'**OEM '| **'**RAW '| 'code_page' }
    指定資料檔中之資料的字碼頁。只有當資料包含字元值大於 127 或小於 32 的 char、varchar 或 text 資料行時，CODEPAGE 才有相關。

    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 Database Engine 要正確解譯輸入資料，必須採用這個字碼頁。

  • ERRORFILE ='file_name'
    指定用來收集格式錯誤且無法轉換成 OLE DB 資料列集之資料列的檔案。這些資料列會「依照原狀」，從資料檔複製到這個錯誤檔中。

    錯誤檔是在開始執行命令時建立。如果檔案已經存在，就會引發錯誤。另外，還會建立一個副檔名為 .ERROR.txt 的控制檔。這個檔案會參考錯誤檔中的每個資料列，且會提供錯誤診斷。錯誤更正之後，就能夠載入資料。

  • FIRSTROW **=**first_row
    指定要載入的第一個資料列的號碼。預設值是 1，表示指定資料檔中的第一個資料列。資料列號碼是由計算資料列結束字元決定的。
  • LASTROW **=**last_row
    指定要載入的最後一個資料列的號碼。預設值是 0，表示指定資料檔中的最後一個資料列。

  • MAXERRORS **=**maximum_errors
    指定最多出現幾個語法錯誤或不符合的資料列 (如格式檔所定義) 之後，OPENROWSET 便會擲出例外狀況。只要尚未到達 MAXERRORS，OPENROWSET 會忽略所有不正確的資料列，也不載入它，並將這個不正確的資料列計為一個錯誤。

    maximum_errors 的預設值是 10。

    ms190312.note(zh-tw,SQL.90).gif附註：
    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) 類型的單一資料列、單一資料行資料列集加以傳回。

    ms190312.note(zh-tw,SQL.90).gif重要事項：
    建議您只使用 SINGLE_BLOB 選項匯入 XML 資料，而不要使用 SINGLE_CLOB 和 SINGLE_NCLOB，因為只有 SINGLE_BLOB 支援所有的 Windows 編碼轉換。
  • SINGLE_CLOB
    以 ASCII 格式讀取 data_file，並且使用目前資料庫的定序，將內容當做 varchar(max) 類型的單一資料列、單一資料行資料列集加以傳回。
  • SINGLE_NCLOB
    以 UNICODE 格式讀取 data_file，並且使用目前資料庫的定序，將內容當做 nvarchar(max) 類型的單一資料列、單一資料行資料列集加以傳回。

備註

唯有針對指定的提供者將 DisallowAdhocAccess 登錄選項明確設為 0，且已啟用 [特定分散式查詢] 進階組態選項時，才可使用 OPENROWSET 來存取 OLE DB 資料來源的遠端資料。若未設定這些選項，預設行為便不允許特定存取。

存取遠端 OLE DB 資料來源時，用戶端從其中連接到將進行查詢之伺服器的伺服器，不會自動委派信任連接的登入識別。此時必須設定驗證委派。如需詳細資訊，請參閱＜設定委派之連結的伺服器＞。

如果 OLE DB 提供者支援指定資料來源中的多個目錄和結構描述，則需要用到目錄和結構描述名稱。如果 OLE DB 提供者不支援 catalog 和 schema 的值，就可以將它們省略。如果提供者只支援結構描述名稱，就必須指定 schema**.object 格式的兩部分名稱。如果提供者只支援目錄名稱，則必須指定 catalog.schema.**object 格式的三部分名稱。使用 SQL Native Client OLE DB 提供者的傳遞查詢必須指定三部分的名稱。如需詳細資訊，請參閱＜Transact-SQL 語法慣例 (Transact-SQL)＞。

OPENROWSET 不接受其引數的變數。

搭配 BULK 選項使用 OPENROWSET

下列 Transact-SQL 增強功能支援 OPENROWSET(BULK…) 函數：

  • 搭配 SELECT 使用的 FROM 子句，可以利用完整的 SELECT 功能來呼叫 OPENROWSET(BULK…)，而不呼叫資料表名稱。
    附有 BULK 選項的 OPENROWSET，在 FROM 子句中需要一個相互關聯名稱，又稱為範圍變數或別名。您可以指定資料行別名。如果未指定資料行別名清單，格式檔就必須有資料行名稱。指定資料行別名會覆寫格式檔中的資料行名稱，例如：
    FROM OPENROWSET(BULK...) AS table_alias
    FROM OPENROWSET(BULK...) AS table_alias(column_alias,...n)
  • SELECT…FROM OPENROWSET(BULK...) 陳述式會直接查詢檔案中的資料，而不將資料匯入資料表中。SELECT…FROM OPENROWSET(BULK…) 陳述式也可以使用格式檔案來指定資料行名稱和資料類型，以列出大量資料行別名。
  • INSERT...SELECT * FROM OPENROWSET(BULK...) 陳述式會將資料檔案中的資料大量匯入 SQL Server 資料表。如需詳細資訊，請參閱＜使用 BULK INSERT 或 OPENROWSET(BULK...) 匯入大量資料＞。
  • 當搭配 OPENROWSET BULK 選項使用 INSERT 陳述式時，BULK 子句支援資料表提示。除了一般的資料表提示 (例如 TABLOCK) 之外，BULK 子句也接受下列特定的資料表提示：IGNORE_CONSTRAINTS (僅忽略 CHECK 和 FOREIGN KEY 條件約束)、IGNORE_TRIGGERS、KEEPDEFAULTS 和 KEEPIDENTITY。如需詳細資訊，請參閱＜資料表提示 (Transact-SQL)＞。

如需有關如何使用 INSERT...SELECT * FROM OPENROWSET(BULK...) 陳述式的詳細資訊，請參閱＜匯入和匯出大量資料＞。如需有關大量匯入所執行的資料列插入作業於何時記錄到交易記錄的詳細資訊，請參閱＜大量匯入採用最低限度記錄的必要條件＞。

ms190312.note(zh-tw,SQL.90).gif附註：
使用 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. 搭配 SELECT 和 SQL Native Client OLE DB 提供者來使用 OPENROWSET

下列範例會利用 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 資料表。

ms190312.note(zh-tw,SQL.90).gif附註：
這個範例假設您已經安裝了 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 資料表中，選取所有的資料。

ms190312.note(zh-tw,SQL.90).gif附註：
這個範例假設您已經安裝了 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 提供者，從文字檔擷取資料列

下列範例會利用一個格式檔，從 Tab 鍵分隔的文字檔 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...) 的其他範例，請參閱下列主題：

請參閱

參考

DELETE (Transact-SQL)
FROM (Transact-SQL)
INSERT (Transact-SQL)
OPENDATASOURCE (Transact-SQL)
OPENQUERY (Transact-SQL)
資料列集函數 (Transact-SQL)
SELECT (Transact-SQL)
sp_addlinkedserver (Transact-SQL)
sp_serveroption (Transact-SQL)
UPDATE (Transact-SQL)
WHERE (Transact-SQL)

其他資源

分散式查詢
匯入和匯出大量資料
使用者自訂函數 (Database Engine)

說明及資訊

取得 SQL Server 2005 協助

變更歷程記錄

版本 歷程記錄

2006 年 12 月 12 日
新增內容：
  • 新增 IGNORE_CONSTRAINTS 資料表提示會忽略 CHECK 和 FOREIGN KEY 條件約束。

2006 年 7 月 17 日
新增內容：
  • 加入說明對於更常用到的 OLE DB 資料來源參考，應該使用連結的伺服器而不是 OPENROWSET 的內容。
  • ＜備註＞一節內容已作了下列變更：
    • 加入有關在透過雙躍點存取遠端 OLE DB 時需要設定驗證委派的內容。
    • 加入說明使用 SQL Native Client OLE DB 提供者的傳遞查詢必須指定三部分之名稱的內容。
    • 加入有關使用 BULK 選項的詳細資訊。