OPENROWSET (Transact-SQL)

Статья
12/15/2008

Изменения: 12 декабря 2006 г.

Содержит все необходимые сведения о соединении, которые требуются для доступа к удаленным данным источника данных OLE DB. Это альтернативный метод для доступа к таблицам на связанном сервере и является однократным нерегламентированным методом соединения и удаленного доступа к данным с помощью OLE DB. Вместо этого для более частых ссылок на источники данных OLE DB используйте связанные серверы. Дополнительные сведения см. в разделе Связь серверов. Функция OPENROWSET может быть использована в предложении FROM запроса так, как если бы она была именем таблицы. Функция OPENROWSET также может быть использована как целевая таблица в инструкциях INSERT, UPDATE или DELETE. Это зависит от возможностей поставщика OLE DB. Несмотря на то, что запрос может возвратить несколько результирующих наборов, функция 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'
Символьная строка, которая представляет понятное имя (или PROGID) поставщика OLE DB, указанное в реестре. Аргумент 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 для инициализации поставщика OLE DB. Аргумент provider_string обычно включает в себя все сведения о соединении, необходимые для инициализации поставщика. Список ключевых слов, распознаваемых поставщиком OLE DB для собственного клиента SQL, см. в разделе 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 может считывать данные из файла без их загрузки в целевую таблицу. Это позволяет использовать функцию OPENROWSET совместно с обычной инструкцией SELECT.

Аргументы параметра BULK позволяют полностью контролировать начало и конец считывания данных, отладку ошибок и способ представления полученных данных. Например, можно указать, что файл с данными будет считан как однострочный или как набор строк типа varbinary, varchar или nvarchar в один столбец. Поведение по умолчанию описано в следующем далее описании аргументов.

Дополнительные сведения об использовании параметра BULK см. в подразделе «Примечания» далее в этом разделе. Дополнительные сведения о разрешениях, необходимых параметру BULK, см. в подразделе «Разрешения» далее в этом разделе.

Примечание.
Функция 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' }
Указывает кодовую страницу данных в файле данных. Аргумент CODEPAGE имеет смысл только в том случае, если данные содержат столбцы типа char, varchar или text с символами, коды которых больше 127 или меньше 32.

Значение аргумента 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.

Примечание.

Аргумент 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).

Важно!
XML-данные рекомендуется импортировать с помощью параметра SINGLE_BLOB, а не SINGLE_CLOB или SINGLE_NCLOB, потому что только параметр SINGLE_BLOB поддерживает все возможные преобразования кодировок в Windows.

SINGLE_CLOB
Считывает файл data_file как ASCII файл, возвращая содержимое в виде набора строк с одной строкой и одним столбцом типа varchar(max), используя параметры сортировки текущей базы данных.

SINGLE_NCLOB
Считывает файл data_file как Юникод, возвращая содержимое в виде набора строк с одной строкой и одним столбцом типа nvarchar(max), используя параметры сортировки текущей базы данных.

Замечания

Функция OPENROWSET может быть использована для доступа к удаленным данным из источников OLE DB только в том случае, если для заданного поставщика параметр реестра DisallowAdhocAccess явно установлен в 0 и включен параметр Ad Hoc Distributed Queries расширенной настройки. Если эти параметры не установлены, политика по умолчанию запрещает нерегламентированный доступ.

При удаленном доступе к источнику данных OLE DB автоматическое делегирование идентификатора имени входа доверенных соединений с сервера, к которому подключен клиент, на запрашиваемый сервер не выполняется. Делегирование проверки подлинности должно быть настроено. Дополнительные сведения см. в разделе Настройка связанных серверов для делегирования.

Имена каталога или схемы необходимы, если поставщик OLE DB поддерживает несколько каталогов и схем для указанного источника данных. Значения аргументов catalog и schema можно опустить, если поставщик OLE DB их не поддерживает. Если поставщик поддерживает только имена схемы, то необходимо указать имя в формате из двух частей schema**.object . Если поставщик поддерживает только имена каталогов, необходимо указать имя в формате из трех частей catalog.schema.**object. Для передаваемых запросов, использующих поставщика OLE DB для собственного клиента SQL, необходимо указать трехсоставное имя. Дополнительные сведения см. в разделе Синтаксические обозначения в Transact-SQL (Transact-SQL).

Функция OPENROWSET не принимает переменные в качестве своих аргументов.

Применение инструкции OPENROWSET с параметром BULK

Следующие усовершенствования Transact-SQL поддерживают функцию OPENROWSET(BULK…).

Предложение FROM, используемое в инструкции SELECT, может вызывать OPENROWSET(BULK…) вместо имени таблицы с полной функциональностью инструкции SELECT.
Функции OPENROWSET с параметром BULK требуется корреляционное имя, также известное как переменная диапазона или псевдоним в предложении 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...), см. в разделе Массовый импорт и экспорт данных. Сведения о том, когда в журнале транзакций регистрируются операции вставки строк, выполняемые при массовом импорте, см. в разделе Предварительные условия для минимального ведения журнала массового импорта данных.

Примечание.
При использовании функции OPENROWSET важно понимать, как SQL Server 2005 обрабатывает олицетворение пользователя. Дополнительные сведения о вопросах безопасности для см. в разделе Массовый импорт данных при помощи инструкции BULK INSERT или OPENROWSET(BULK...).

При использовании функции OPENROWSET важно понимать, как SQL Server 2005 обрабатывает олицетворение пользователя. Дополнительные сведения о вопросах безопасности для см. в разделе Массовый импорт данных при помощи инструкции BULK INSERT или OPENROWSET(BULK...).

Массовый экспорт или импорт документов SQLXML

Чтобы выполнить массовый экспорт или импорт SQLXML-данных используйте один из следующих типов данных в файле форматирования:

Тип данных	Результат
SQLCHAR или SQLVARYCHAR	Данные отправляются в кодовой странице клиента или кодовой странице, определенной параметрами сортировки.
SQLNCHAR или SQLNVARCHAR	Данные отправляются в Юникоде.
SQLBINARY или SQLVARYBIN	Данные отправляются без преобразования.

Разрешения

Разрешения функции OPENROWSET определяются разрешениями пользователя, переданного поставщику OLE DB. Чтобы использовать параметр BULK, необходимо разрешение ADMINISTER BULK OPERATIONS.

Примеры

А. Использование функции OPENROWSET совместно с инструкцией SELECT и поставщиком OLE DB для собственного клиента SQL

В следующем примере для доступа к таблице HumanResources.Department в базе данных AdventureWorks на удаленном сервере Seattle1 используется поставщик OLE DB для собственного клиента SQL (SQLNCLI). Инструкция SELECT используется для определения возвращаемого набора строк. Строка поставщика содержит ключевые слова Server и Trusted_Connection. Эти ключевые слова распознаются поставщиком OLE DB для собственного клиента SQL.

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

Б. Использование поставщика Microsoft OLE DB для Jet

В следующем примере используется таблица Customers в базе данных Microsoft Access Northwind с помощью поставщика Microsoft OLE DB для Jet.

Примечание.
В этом примере предполагается, что 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

В. Использование функции OPENROWSET и другой таблицы в предложении INNER JOIN

В следующем примере производится выборка всех данных из таблицы Customers базы данных Northwind локального экземпляра SQL Server и из таблицы Orders из базы данных Access Northwind, хранящейся на том же компьютере.

Примечание.
В этом примере предполагается, что 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

Г. Использование функции OPENROWSET для массовой вставки файла данных в столбец varbinary(max)

В следующем примере создается небольшая таблица для демонстрационных целей и вставляются данные из файла с именем Text1.txt, расположенного в корневом каталоге диска C:, в столбец 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

Д. Использование поставщика BULK функции OPENROWSET совместно с файлом форматирования для получения строк из текстового файла

В следующем примере используется файл форматирования для получения строк, разделенных символами табуляции, из файла 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...) см. в следующих разделах:

См. также

Журнал изменений

Версия	Журнал
12 декабря 2006 г.	Новое содержимое Добавлено пояснение, что табличная подсказка IGNORE_CONSTRAINTS не обрабатывает ограничения CHECK и FOREIGN KEY.
17 июля 2006 г.	Новое содержимое Добавлено предложение с указанием на использование связанных серверов вместо OPENROWSET для более частых ссылок на источники данных OLE DB. В раздел «Примечания» внесены следующие изменения. Добавлен абзац о необходимости настройки делегирования проверки подлинности при удаленном доступе к источникам данных OLE DB по двухэтапному соединению. Добавлено уведомление, что для передаваемых запросов, использующих поставщик OLE DB для собственного клиента SQL, необходимо указать трехсоставное имя. Добавлены сведения об использовании параметра BULK.

12 декабря 2006 г.

Новое содержимое

Добавлено пояснение, что табличная подсказка IGNORE_CONSTRAINTS не обрабатывает ограничения CHECK и FOREIGN KEY.

17 июля 2006 г.