OPENROWSET (Transact-SQL)

Contiene toda la información de conexión necesaria para tener acceso a datos remotos desde un origen de datos OLE DB. Es un método alternativo para tener acceso a las tablas de un servidor vinculado y, al mismo tiempo, es un método ad hoc para conectarse y tener acceso a datos remotos utilizando OLE DB. Para obtener referencias más frecuentes a orígenes de datos OLE DB, use, en su lugar, servidores vinculados. Para obtener más información, vea Servidores vinculados (motor de base de datos). Se puede hacer referencia a la función OPENROWSET en la cláusula FROM de una consulta, como si fuera el nombre de una tabla. También se puede hacer referencia a la función OPENROWSET como tabla de destino de una instrucción INSERT, UPDATE o DELETE, sujeta a la funcionalidad del proveedor OLE DB. Aunque la consulta puede devolver varios conjuntos de resultados, OPENROWSET solo devuelve el primero.

OPENROWSET también admite operaciones masivas a través de un proveedor integrado BULK que permite que los datos se lean y se devuelvan como un conjunto de filas.

Convenciones de sintaxis de Transact-SQL

Sintaxis

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 ]
   [ , ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) [ UNIQUE ]

Argumentos

• 'provider_name'
  Es una cadena de caracteres que representa el nombre descriptivo (o PROGID) del proveedor OLE DB según se especifica en el Registro. provider_name no tiene ningún valor predeterminado.

• 'datasource'
  Es una constante de cadena que corresponde a un origen de datos OLE DB determinado. datasource es la propiedad DBPROP_INIT_DATASOURCE que se pasará a la interfaz IDBProperties del proveedor para inicializarlo. Normalmente, esta cadena incluye el nombre del archivo de la base de datos, el nombre del servidor de bases de datos o un nombre comprensible para que el proveedor encuentre las bases de datos.

• 'user_id'
  Es una constante de cadena que contiene el nombre de usuario que se pasa al proveedor OLE DB especificado. user_id indica el contexto de seguridad para la conexión y se pasa como la propiedad DBPROP_AUTH_USERID para inicializar el proveedor. user_id no puede ser un nombre de inicio de sesión de Microsoft Windows.

• 'password'
  Es una constante de cadena que contiene la contraseña de usuario que se debe pasar al proveedor OLE DB. password se pasa como la propiedad DBPROP_AUTH_PASSWORD cuando se inicializa el proveedor. password no puede ser una contraseña de Microsoft Windows.

• 'provider_string'
  Es una cadena de conexión específica del proveedor que se pasa como la propiedad DBPROP_INIT_PROVIDERSTRING para inicializar el proveedor OLE DB. Normalmente, provider_string encapsula toda la información de conexión necesaria para inicializar el proveedor. Para obtener una lista de palabras clave que el proveedor OLE DB de SQL Server Native Client pueda reconocer, vea Propiedades de inicialización y autorización.

• catalog
  Es el nombre del catálogo o de la base de datos donde reside el objeto especificado.

• schema
  Es el nombre del esquema o propietario del objeto para el objeto especificado.

• object
  Es el nombre del objeto que identifica unívocamente el objeto con el que se va a trabajar.

• 'query'
  Es una constante de cadena que se envía al proveedor, quien la ejecuta. La instancia local de SQL Server no procesa esta consulta, pero sí que procesa los resultados de la consulta devuelta por el proveedor (una consulta de paso a través). Las consultas de paso a través resultan útiles cuando se utilizan en proveedores que no muestran sus datos tabulares a través de nombres de tablas, sino solamente a través de un lenguaje de comandos. El servidor remoto admite las consultas de paso a través siempre y cuando el proveedor de consultas admita el objeto Command de OLE DB y sus interfaces obligatorias. Para obtener más información, vea Referencia de SQL Server Native Client (OLE DB).

• BULK
  Utiliza el proveedor de conjuntos de filas BULK para que OPENROWSET lea datos de un archivo. En SQL Server, OPENROWSET puede leer datos de un archivo sin necesidad de cargarlos en una tabla de destino. Esto le permite utilizar OPENROWSET con una instrucción SELECT simple.

  Los argumentos de la opción BULK le permiten elegir dónde empezar y acabar la lectura de datos, cómo abordar los errores y cómo interpretar los datos. Por ejemplo, puede especificar que el archivo de datos se lea como un conjunto de filas de una sola fila y una sola columna de tipo varbinary, varchar o nvarchar. El comportamiento predeterminado se describe en las descripciones de los argumentos que se muestran a continuación.

  Para obtener información acerca del uso de la opción BULK, vea la sección "Comentarios" más adelante en este tema. Para obtener información acerca de los permisos que necesita la opción BULK, vea la sección "Permisos", más adelante en este tema.

  [!NOTA]

  Cuando se utiliza para importar datos con el modelo de recuperación completa, OPENROWSET (BULK ...) no optimiza el registro.

  Para obtener información sobre cómo preparar los datos para la importación masiva, vea Preparar los datos para exportar o importar de forma masiva (SQL Server).

• 'data_file'
  Es la ruta de acceso completa del archivo de datos cuyos datos se copian en la tabla de destino.

• FORMATFILE ='format_file_path'
  Especifica la ruta de acceso completa de un archivo de formato. SQL Server admite dos tipos de archivos de formato: XML y no XML.

  Es necesario usar un archivo de formato para definir los tipos de columna del conjunto de resultados. La única excepción es cuando se especifica SINGLE_CLOB, SINGLE_BLOB o SINGLE_NCLOB; en este caso, no es necesario usar el archivo de formato.

  Para obtener información acerca de archivos de formato, vea Usar un archivo de formato para importar datos de forma masiva (SQL Server).

• < bulk_options >
  Especifica uno o más argumentos para la opción BULK.

• CODEPAGE = { 'ACP'| 'OEM'| 'RAW'| 'code_page' }
  Especifica la página de códigos de los datos incluidos en el archivo de datos. CODEPAGE solo es pertinente si los datos contienen columnas de tipo char, varchar o text con valores de caracteres mayores que 127 o menores que 32.

  [!NOTA]

  Se recomienda especificar un nombre de intercalación para cada columna de un archivo de formato.

  Valor de CODEPAGE	Descripción
  ACP	Convierte columnas de los tipos de datos char, varchar o text de la página de códigos ANSI/Microsoft Windows (ISO 1252) a la página de códigos de SQL Server.
  OEM (valor predeterminado)	Convierte columnas de tipos de datos char, varchar o text de la página de códigos OEM del sistema a la página de códigos de SQL Server.
  RAW	No se realiza ninguna conversión entre páginas de códigos. Ésta es la opción más rápida.
  code_page	Indica la página de códigos original en la que se codifican los datos de caracteres incluidos en el archivo de datos; por ejemplo, 850. Importante SQL Server no admite la página de códigos 65001 (codificación UTF-8).

• ERRORFILE ='file_name'
  Especifica el archivo utilizado para recopilar filas que tienen errores de formato y no pueden convertirse en un conjunto de filas OLE DB. Estas filas se copian en este archivo de errores desde el archivo de datos "tal cual".

  El archivo de errores se crea cuando se inicia la ejecución del comando. Se producirá un error si el archivo ya existe. Además, se crea un archivo de control con la extensión .ERROR.txt. Este archivo hace referencia a cada una de las filas del archivo de errores y proporciona diagnósticos de errores. Tras corregir los errores, pueden cargarse los datos.

• FIRSTROW =first_row
  Especifica el número de la primera fila que se va a cargar. El valor predeterminado es 1. Indica la primera fila del archivo de datos especificado. Los números de fila vienen determinados por el recuento de terminadores de fila. FIRSTROW está en base 1.

• LASTROW =last_row
  Especifica el número de la última fila que va a cargarse. El valor predeterminado es 0. Indica la última fila del archivo de datos especificado.

• MAXERRORS =maximum_errors
  Especifica el número máximo de errores de sintaxis o filas no compatibles, tal y como se define en el archivo de formato, que pueden tener lugar antes de que OPENROWSET produzca una excepción. Hasta que se alcance el valor de MAXERRORS, OPENROWSET omite todas las filas erróneas, sin cargarlas, y cuenta cada fila errónea como un error.

  El valor predeterminado para maximum_errors es 10.

  [!NOTA]

  MAX_ERRORS no se aplica en las restricciones CHECK o para convertir tipos de datos money y bigint.

• ROWS_PER_BATCH =rows_per_batch
  Especifica el número aproximado de filas de datos del archivo de datos. Este valor debe ser del mismo tipo que el número de filas real.

  OPENROWSET siempre importa un archivo de datos como un solo lote. Sin embargo, si especifica un valor rows_per_batch >0, el procesador de consultas utiliza el valor de rows_per_batch como sugerencia para asignar recursos en el plan de consulta.

  De forma predeterminada, se desconoce el valor de ROWS_PER_BATCH. Especificar ROWS_PER_BATCH = 0 es lo mismo que omitir ROWS_PER_BATCH.

• ORDER ( { column [ ASC | DESC ] } [ ,... n ] [ UNIQUE ] )
  Sugerencia opcional que especifica la forma en que están ordenados los datos en el archivo. De forma predeterminada, la operación masiva presupone que los datos del archivo no están ordenados. El rendimiento podría mejorar si el optimizador de consultas puede aprovechar el orden especificado para generar un plan de consulta más eficaz. A continuación se citan algunos ejemplos en los que especificar una ordenación puede ser beneficioso:

  • La inserción de filas en una tabla que tiene un índice clúster, donde los datos del conjunto de filas están ordenados en la clave del índice clúster.

  • La combinación del conjunto de filas con otra tabla, donde las columnas de ordenación y combinación coinciden.

  • La agregación de los datos del conjunto de filas por las columnas de ordenación.

  • El uso del conjunto de filas como una tabla de origen en la cláusula FROM de una consulta, donde las columnas de ordenación y combinación coinciden.

  UNIQUE especifica que el archivo de datos no tiene entradas duplicadas.

  Si las filas del archivo de datos no están ordenadas según el orden especificado, o si se ha especificado la sugerencia UNIQUE y hay claves duplicadas, se devuelve un error.

  Se requieren alias de columna cuando se utiliza ORDER. La lista de alias de columna debe hacer referencia a la tabla derivada a la que la cláusula BULK está obteniendo acceso. Los nombres de columna que se especifican en la cláusula ORDER hacen referencia a esta lista de alias de columna. No pueden especificarse columnas de tipos de datos de gran tamaño: varchar(max), nvarchar(max), varbinary(max) y xml; tampoco pueden especificarse columnas de tipos de objetos grandes (LOB): text, ntext y image.

• SINGLE_BLOB
  Devuelve el contenido de data_file como un conjunto de filas de tipo varbinary(max) de una sola fila y una sola columna.

  Importante
  Recomendamos que importe los datos XML solo mediante la opción SINGLE_BLOB, en vez de SINGLE_CLOB y SINGLE_NCLOB, ya que solo SINGLE_BLOB admite todas las conversiones de codificación de Windows.

• SINGLE_CLOB
  Al leer data_file como ASCII, el contenido se devuelve como un conjunto de filas de tipo varchar(max) de una sola fila y una sola columna, mediante la intercalación de la base de datos actual.

• SINGLE_NCLOB
  Al leer data_file como UNICODE, se devuelve el contenido como un conjunto de filas de tipo nvarchar(max) de una sola fila y una sola columna, mediante la intercalación de la base de datos actual.

Comentarios

OPENROWSET se puede utilizar para tener acceso a datos remotos desde orígenes de datos de OLE DB solo cuando la opción de Registro DisallowAdhocAccess está establecida explícitamente en 0 para el proveedor especificado y la opción de configuración avanzada Ad Hoc Distributed Queries está habilitada. Cuando no se establecen estas opciones, el comportamiento predeterminado no permite el acceso ad hoc.

Al tener acceso remoto a orígenes de datos OLE DB, la identidad de inicio de sesión de las conexiones de confianza no se delegan automáticamente del servidor en el que el cliente se conecta al servidor que se consulta. Debe configurarse la delegación de autenticación.

Los nombres de catálogo y esquema son necesarios si el proveedor OLE DB admite varios catálogos y esquemas en el origen de datos especificado. Los valores de catalog y schema pueden omitirse si el proveedor OLE DB no los admite. Si el proveedor solamente admite nombres de esquema, debe especificarse un nombre de dos partes con el formato schema**.object . Si el proveedor solamente admite nombres de catálogo, debe especificarse un nombre de tres partes con el formato catalog.schema.**object. Es necesario especificar nombres de tres partes para las consultas de paso a través que usen el proveedor OLE DB de SQL Server Native Client. Para obtener más información, vea Convenciones de sintaxis de Transact-SQL (Transact-SQL).

OPENROWSET no acepta variables como argumentos.

Las llamadas a OPENDATASOURCE, OPENQUERY u OPENROWSET en la cláusula FROM se evalúan por separado y de forma independiente de otras llamadas a estas funciones utilizadas como destino de la actualización, incluso si se han suministrado argumentos idénticos a las dos llamadas. En particular, las condiciones de filtro o combinación aplicadas en el resultado de una de esas llamadas no tienen ningún efecto en los resultados de la otra llamada.

Utilizar OPENROWSET con la opción BULK

Las siguientes mejoras de Transact-SQL admiten la función OPENROWSET(BULK…):

• Las cláusulas FROM que se usan con SELECT pueden llamar a OPENROWSET(BULK…) en lugar de indicar un nombre de tabla, con toda la funcionalidad de SELECT.

  OPENROWSET con la opción BULK requiere un nombre de correlación en la cláusula FROM, que también recibe el nombre de alias o variable de intervalo. Pueden especificarse alias de columna. Si no se especifica una lista de alias de columna, el archivo de formato debe incluir nombres de columna. Al especificar alias de columnas se anulan los nombres de columnas en el archivo de formato:

  FROM OPENROWSET(BULK...) AS table_alias

  FROM OPENROWSET(BULK...) AS table_alias(column_alias,... n)

• Una instrucción SELECT…FROM OPENROWSET(BULK...) consulta los datos directamente en el archivo, sin importar los datos en una tabla. Las instrucciones SELECT…FROM OPENROWSET(BULK…) también pueden mostrar los alias de las columnas masivas utilizando un archivo de formato para especificar los nombres de las columnas y también los tipos de datos.

• El uso de OPENROWSET(BULK...) como tabla de origen en una instrucción INSERT o MERGE permite realizar una importación masiva desde un archivo de datos en una tabla de SQL Server. Para obtener más información, vea Importar de forma masiva datos mediante BULK INSERT u OPENROWSET(BULK...) (SQL Server).

• Cuando se utiliza la opción OPENROWSET BULK con una instrucción INSERT, la cláusula BULK admite sugerencias de tabla. Además de las sugerencias de tabla normales, como TABLOCK, la cláusula BULK puede aceptar las sugerencias de tablas especializadas siguientes: IGNORE_CONSTRAINTS (solo pasa por alto las restricciones CHECK y FOREIGN KEY), IGNORE_TRIGGERS, KEEPDEFAULTS y KEEPIDENTITY. Para obtener más información, vea Sugerencias de tabla (Transact-SQL).

Para obtener más información sobre cómo utilizar las instrucciones INSERT...SELECT * FROM OPENROWSET(BULK...), vea Importar y exportar datos de forma masiva (SQL Server). Para obtener información acerca de cuándo se registran en el registro de transacciones las operaciones de inserción de filas que se efectúan durante la importación masiva, vea Requisitos previos para el registro mínimo durante la importación masiva.

[!NOTA]

Cuando utilice OPENROWSET, es importante que entienda el modo en el que SQL Server controla la suplantación. Para obtener más información acerca de las consideraciones de seguridad, vea Importar de forma masiva datos mediante BULK INSERT u OPENROWSET(BULK...) (SQL Server).

Importar de forma masiva datos SQLCHAR, SQLNCHAR o SQLBINARY

OPENROWSET(BULK...) presupone que, si no se especifica, la longitud máxima de los datos SQLCHAR, SQLNCHAR o SQLBINARY no supera los 8000 bytes. Si los datos importados están en un campo de datos LOB que incluye cualquier objeto varchar(max), nvarchar(max) o varbinary(max) que supera los 8000 bytes, debe usar un archivo de formato XML que defina la longitud máxima para el campo de datos. Para especificar la longitud máxima, edite el archivo de formato y declare el atributo MAX_LENGTH.

[!NOTA]

Un archivo de formato generado automáticamente no especifica la longitud o la longitud máxima de un campo LOB. Sin embargo, es posible editar un archivo de formato y especificar la longitud o la longitud máxima manualmente.

Importar o exportar masivamente documentos SQLXML

Para importar o exportar de forma masiva datos SQLXML, utilice uno de los tipos de datos siguientes en el archivo de formato.

Tipo de datos	Efecto
SQLCHAR o SQLVARYCHAR	Los datos se envían a la página de códigos cliente o a la página de códigos que implica la intercalación.
SQLNCHAR o SQLNVARCHAR	Los datos se envían como Unicode.
SQLBINARY o SQLVARYBIN	Los datos se envían sin ninguna conversión.

Permisos

Los permisos de OPENROWSET vienen determinados por los permisos del nombre de usuario que se pasa al proveedor OLE DB. El uso de la opción BULK requiere el permiso ADMINISTER BULK OPERATIONS.

Ejemplos

A.Usar OPENROWSET con SELECT y el proveedor OLE DB de SQL Server Native Client

En el ejemplo siguiente se utiliza el proveedor OLE DB de SQL Server Native Client para tener acceso a la tabla HumanResources.Department de la base de datos AdventureWorks2012 en el servidor remoto Seattle1. (El uso de SQLNCLI y SQL Server redirigirá a la última versión del proveedor OLE DB de SQL Server Native Client). Se utiliza una instrucción SELECT para definir el conjunto de filas devuelto. La cadena de proveedor contiene las palabras clave Server y Trusted_Connection. El proveedor OLE DB de SQL Server Native Client reconoce estas palabras clave.

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

B.Usar el proveedor Microsoft OLE DB para Jet

En el siguiente ejemplo se obtiene acceso a la tabla Customers de la base de datos Northwind de Microsoft Access a través del proveedor Microsoft OLE DB para Jet.

[!NOTA]

En este ejemplo se supone que está instalado Access. Para ejecutar este ejemplo, debe instalar la base de datos Northwind.

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

C.Usar OPENROWSET y otra tabla en INNER JOIN

En el ejemplo siguiente, se seleccionan todos los datos de la tabla Customers de la instancia local de la base de datos Northwind de SQL Server y de la tabla Orders de la base de datos Northwind de Access que se encuentra en el mismo equipo.

[!NOTA]

En este ejemplo se supone que está instalado Access. Para ejecutar este ejemplo, debe instalar la base de datos Northwind.

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.Usar OPENROWSET para insertar de forma masiva datos de archivo en una columna varbinary(max)

En el ejemplo siguiente se crea una tabla pequeña como ejemplo y se insertan datos de archivo desde un archivo llamado Text1.txt ubicado en el directorio raíz C: en una columna varbinary(max).

USE AdventureWorks2012;
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.Usar el proveedor OPENROWSET BULK con un archivo de formato para recuperar filas de un archivo de texto

En el ejemplo siguiente se utiliza un archivo de formato para recuperar filas de un archivo de texto delimitado por tabuladores, values.txt, que contiene los datos siguientes:

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

El archivo de formato, values.fmt, describe las columnas en 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

Ésta es la consulta que recupera los datos:

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

Otros ejemplos

Para obtener más ejemplos del uso de INSERT...SELECT * FROM OPENROWSET(BULK...), vea los temas siguientes:

• Ejemplos de importación y exportación de forma masiva documentos XML (SQL Server)

• Mantener valores de identidad al importar datos de forma masiva (SQL Server)

• Mantener valores NULL o usar valores predeterminados durante la importación masiva (SQL Server)

• Usar un archivo de formato para importar datos de forma masiva (SQL Server)

• Usar el formato de caracteres para importar o exportar datos (SQL Server)

• Usar un archivo de formato para omitir una columna de tabla (SQL Server)

• Usar un archivo de formato para omitir un campo de datos (SQL Server)

• Usar un archivo de formato para asignar columnas de tabla a campos de un archivo de datos (SQL Server)

Vea también

Referencia

DELETE (Transact-SQL)

FROM (Transact-SQL)

INSERT (Transact-SQL)

OPENDATASOURCE (Transact-SQL)

OPENQUERY (Transact-SQL)

Funciones de conjuntos de filas (Transact-SQL)

SELECT (Transact-SQL)

sp_addlinkedserver (Transact-SQL)

sp_serveroption (Transact-SQL)

UPDATE (Transact-SQL)

WHERE (Transact-SQL)

Conceptos

Importar y exportar datos de forma masiva (SQL Server)