OPENROWSET (Transact-SQL)

Actualizado: 12 de diciembre de 2006

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 Vincular servidores. 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 sólo 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 ] 

Argumentos

• 'provider_name'
  Es una cadena de caracteres que representa el nombre descriptivo (o PROGID) del proveedor OLE DB 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 Native Client pueda reconocer, vea Initialization and Authorization Properties.

• 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í los resultados de la consulta que devuelve 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 sólo 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 SQL Native Client (OLE DB) Reference.

• BULK
  Utiliza el proveedor de conjuntos de filas BULK para que OPENROWSET lea datos de un archivo. En SQL Server 2005, 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 aparecen a continuación.

  Para obtener información acerca de cómo utilizar la opción BULK, vea "Notas", 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.

• '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 2005 admite dos tipos de archivos de formato: XML y no XML.

  Un archivo de formato es necesario para definir tipos de columna en el conjunto de resultados. La única excepción es cuando se especifica SINGLE_CLOB, SINGLE_BLOB o SINGLE_NCLOB; en este caso, no es necesario 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.

• < bulk_options >
  Especifica uno o más argumentos de 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 sólo es relevante si los datos contienen columnas de tipo char, varchar o text con valores de caracteres mayores que 127 o menores que 32.

  Valor de CODEPAGE	Descripción
  ACP	Convierte columnas de 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 cifran los datos de caracteres incluidos en el archivo de datos; por ejemplo, 850. La página de códigos es necesaria para que el SQL Server 2005 Database Engine (Motor de base de datos de SQL Server 2005) interprete correctamente los datos de entrada.

• ERRORFILE ='file_name'
  Especifica el archivo utilizado para recopilar filas que tienen errores de formato y no se pueden convertir a 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 fila del archivo de errores y proporciona diagnósticos de errores. Tras corregir los errores, se pueden cargar los datos.

• FIRSTROW **=**first_row
  Especifica el número de la primera fila que se va a cargar. El valor predeterminado es 1, que hace referencia a la primera fila del archivo de datos especificado. Los números de fila vienen determinados por el recuento de terminadores de fila.

• LASTROW **=**last_row
  Especifica el número de la última fila que se va a cargar. El valor predeterminado es 0, que 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 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 ignora 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 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, el valor de ROWS_PER_BATCH es desconocido. Especificar ROWS_PER_BATCH = 0 es lo mismo que omitir ROWS_PER_BATCH.

• 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 sólo mediante la opción SINGLE_BLOB, en vez de SINGLE_CLOB y SINGLE_NCLOB, ya que sólo SINGLE_BLOB admite todas las conversiones de codificación de Windows.

• SINGLE_CLOB
  Al leer data_file como ASCII, se devuelve el contenido 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.

Notas

OPENROWSET se puede utilizar para obtener acceso a datos remotos de orígenes de datos OLE DB sólo cuando la opción de registro DisallowAdhocAccess se establece explícitamente en 0 para el proveedor especificado y la opción de configuración avanzada Ad Hoc Distributed Queries se habilita. Cuando estas opciones no se establecen, 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. Para obtener más información, vea Configurar servidores vinculados para la delegación.

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

OPENROWSET no acepta variables como argumentos.

Utilizar OPENROWSET con la opción BULK

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

• Una cláusula FROM que se use con SELECT puede lamar a OPENROWSET(BULK?c) en lugar de a un nombre de tabla, con toda la funcionalidad de SELECT.
  OPENROWSET con la opción BULK requiere un nombre de correlación, también conocido como alias o variable de intervalo, en la cláusula FROM. Se pueden especificar los alias de columna. Si no se especifica una lista de alias de columna, el archivo de formato debe tener 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 de un archivo directamente, sin importar los datos en la tabla. Las instrucciones SELECT…FROM OPENROWSET(BULK…) también pueden mostrar alias de las columnas masivas utilizando un archivo de formato para especificar nombres de columnas y tipos de datos.
• Una instrucción INSERT...SELECT * FROM OPENROWSET(BULK...) realiza una importación masiva de datos de un archivo de datos en una tabla SQL Server. Para obtener más información, vea Importación masiva de datos mediante BULK INSERT u OPENROWSET(BULK...).
• 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 (sólo 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 masivos. 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 comprender el modo en el que SQL Server 2005 trata la suplantación. Para obtener más información sobre las consideraciones de seguridad, vea Importación masiva de datos mediante BULK INSERT u OPENROWSET(BULK...).

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.

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 Native Client

En el ejemplo siguiente se utiliza el proveedor OLE DB de SQL Native Client (SQLNCLI) para tener acceso a la tabla HumanResources.Department de la base de datos AdventureWorks en el servidor remoto Seattle1. 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 Native Client reconoce estas palabras clave.

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. Usar el proveedor Microsoft OLE DB para Jet

En el ejemplo siguiente 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] Este ejemplo asume que Access está instalado. Para ejecutar este ejemplo, debe instalar la base de datos Northwind. Para obtener información acerca de cómo instalar la base de datos Northwind, vea Descargar las bases de datos de ejemplo Northwind y pubs.

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 base de datos Northwind de SQL Server local y de la tabla Orders de la base de datos Northwind de Access almacenada en el mismo equipo.

[!NOTA] Este ejemplo asume que Access está instalado. Para ejecutar este ejemplo, debe instalar la base de datos Northwind. Para obtener información acerca de cómo instalar la base de datos Northwind, vea Descargar las bases de datos de ejemplo Northwind y 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. 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 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. 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
• Mantener valores de identidad al importar datos de forma masiva
• Mantener valores NULL o utilizar valores predeterminados durante la importación masiva
• Usar un archivo de formato para importar datos de forma masiva
• Usar el formato de caracteres para importar o exportar datos
• Usar un archivo de formato para omitir una columna de tabla
• Usar un archivo de formato para omitir un campo de datos
• Usar un archivo de formato para asignar columnas de tabla a campos de un archivo de datos

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)

Otros recursos

Consultas distribuidas
Importar y exportar datos masivos
Funciones definidas por el usuario (motor de base de datos)

Ayuda e información

Obtener ayuda sobre SQL Server 2005

Historial de cambios