¿Cómo conectarse a una base de datos remota usando sentencias SQL?

La función OpenRowSet se puede utilizar para conectarse a una base de datos remota en una declaración SQL. En el siguiente ejemplo, la base de datos remota Northwind.mdb está conectada a la interfaz Jet4.0 en la declaración y otras bases de datos se pueden modificar consultando los parámetros de su interfaz.

Usar Northwind

Vaya a

Seleccione c.*,o.*

de Northwind.dbo.Clientes como c

p>

INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0',

c:\Program Files\Microsoft Office\Office 11\SAMPLES\northwind.MDB 'admin ' , orden)

Como o

ON c.CustomerID = o.CustomerID

Ir

Lo siguiente es para SQL Server 2005 Libros en línea La información sigue siendo válida para el servidor Sql 2000.

OPENROWSET (Transact-SQL)

Contiene toda la información de conexión necesaria para acceder a datos remotos en una fuente de datos OLE DB. Este método es una alternativa al acceder a tablas en un servidor vinculado y es una forma única y temporal de conectarse y acceder a datos remotos mediante OLE DB. Para hacer referencia a fuentes de datos OLE DB con más frecuencia, utilice en su lugar un servidor vinculado. Para obtener más información, consulte Servidores vinculados. En la cláusula FROM de una consulta, se puede hacer referencia a la función OPENROWSET igual que al nombre de la tabla. Según las funciones de la interfaz de acceso OLE DB, la función OPENROWSET también se puede utilizar como tabla de destino de la instrucción INSERT, UPDATE o DELETE. Aunque una consulta puede devolver varios conjuntos de resultados, OPENROWSET devuelve sólo el primer conjunto de resultados.

OPENROWSET también admite operaciones masivas a través de la interfaz de acceso masivo incorporada, que permite leer datos de un archivo y devolverlos como un conjunto de filas.

Convención de sintaxis de Transact-SQL

Sintaxis

OPENROWSET

({ 'provider_name ', { 'datasourceuser_id'; 'contraseña '

| 'Cadena de proveedor' }

, { [Directorio] [Patrón.

]Objeto

| 'Consulta'

}

|'Archivo de datos' de gran capacidad,

{ formato archivo = ' formato _ archivo _ ruta '[ lt; Bulk _ opciones gt]

| SINGLE _ BLOB | SINGLE _ CLOB }

} )

ltbulk _ opciones gt: :=

[, página de códigos = { ' ACP ' | ' OEM ' | ' RAW ' | ' code_page ' }]

[, ERRORFILE = 'Nombre de archivo ']

[, FIRSTROW = primera_fila]

[, LASTROW = última_fila]

[, MAXERRORS = máximos_errores]

[ , Número de filas por lote = Número de filas por lote]

Parámetros

'Nombre del proveedor'

Una cadena que representa el OLE DB especificado en el registro El amigable nombre (o PROGID) de la interfaz de acceso. Nombre_proveedor no tiene ningún valor predeterminado.

'Fuente de datos'

Una constante de cadena correspondiente a una fuente de datos OLE DB específica. Datasource es la propiedad DBPROP_INIT_DATASOURCE que se pasará a la interfaz IDBProperties del proveedor para inicializar el proveedor. Normalmente, esta cadena contiene el nombre del archivo de la base de datos, el nombre del servidor de la base de datos o un nombre que el proveedor entienda para ubicar la base de datos.

'ID de usuario'

Una constante de cadena, que es el nombre de usuario pasado a la interfaz de acceso OLE DB especificada. User_id especifica el contexto de seguridad de la conexión y se pasa como el atributo DBPROP_AUTH_USERID para inicializar el proveedor. User_id no puede ser un inicio de sesión de Microsoft Windows.

'Contraseña'

Una constante de cadena, que es la contraseña del usuario pasada a la interfaz de acceso OLE DB. Al inicializar el proveedor, la contraseña se pasa como atributo DBPROP_AUTH_PASSWORD. La contraseña no puede ser una contraseña de Microsoft Windows.

'provider_string'

La cadena de conexión específica del proveedor se pasa como propiedad DBPROP_INIT_PROVIDERSTRING para inicializar el proveedor OLE DB. Normalmente, proveedor_cadena encapsula toda la información de conexión necesaria para inicializar el proveedor. Para obtener una lista de palabras clave reconocidas por el proveedor OLE DB de SQL Native Client, consulte Propiedades de inicialización y autorización.

Directorio

Especifica el nombre del directorio o base de datos donde se encuentra el objeto.

Un esquema (de un plan o teoría)

El nombre de la arquitectura o el nombre del propietario del objeto especificado.

Objetivo

Nombre del objeto, identifica de forma única el objeto sobre el que se va a operar.

'Consulta'

Una constante de cadena enviada y ejecutada por el proveedor. La instancia local de SQL Server no procesa consultas, pero procesa los resultados de las consultas devueltas por el proveedor (consultas de paso). Las consultas pasivas son útiles para algunos proveedores que proporcionan sus datos tabulares no a través de nombres de tablas sino a través de un lenguaje de comandos. El servidor remoto admite consultas de paso siempre que el proveedor de consultas admita el objeto de comando OLE DB y sus interfaces obligatorias.

Para obtener más información, consulte la referencia del cliente nativo SQL (ole db).

La mayoría

utiliza el proveedor de conjuntos de filas masivos de OPENROWSET para leer datos del archivo. En SQL Server 2005, OPENROWSET puede leer datos de un archivo de datos sin cargarlos en la tabla de destino. Esto permite utilizar OPENROWSET dentro de una única instrucción SELECT.

Los parámetros de la opción BULK proporcionan un control efectivo sobre cuándo iniciar y finalizar la lectura de datos, cómo manejar 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 detalla en las descripciones de los parámetros a continuación.

Para obtener información sobre cómo utilizar la opción BULK, consulte la sección "Observaciones" más adelante en este tema. Para obtener información sobre los permisos necesarios para las opciones masivas, consulte la sección "Permisos" más adelante en este tema.

Nota:

OPENROWSET(bulk...) no optimiza el registro cuando se utiliza para importar datos en el modelo de recuperación completa.

Para obtener información sobre cómo preparar datos para la importación masiva, consulte Preparación de datos para la exportación masiva o la importación masiva.

'Archivo de datos'

La ruta completa al archivo de datos cuyos datos se copiarán en la tabla de destino.

Formato de archivo = 'Ruta del archivo de formato'

Especifique la ruta completa del archivo de formato. SQL Server 2005 admite dos tipos de archivos de formato: XML y no XML.

El archivo de formato es necesario para definir los tipos de columnas en el conjunto de resultados. La única excepción es cuando se especifica SINGLE_CLOB o SINGLE_NCLOB. En este caso, no se requiere ningún archivo de formato.

Para obtener información sobre archivos de formato, consulte Importación masiva de datos mediante archivos de formato.

ltbulk_options gt

Especifique uno o más parámetros de la opción BULK.

página de códigos = { ' ACP ' | ' OEM ' | ' RAW ' | ' code _ page ' }

Especifique la página de códigos de los datos en este archivo de datos. CODEPAGE solo se aplica si los datos contienen columnas char, varchar o text con valores de caracteres mayores que 127 o menores que 32.

Descripción del valor de la página de códigos

Proteína portadora de acilo (proteína portadora de acilo)

Convierta columnas de tipo de datos char, varchar o texto de la página de códigos ANSI/Microsoft Windows (ISO 1252) convertido a la página de códigos de SQL Server.

OEM (predeterminado)

Convierte columnas de tipo de datos char, varchar o texto de la página de códigos OEM del sistema a la página de códigos de SQL Server.

Sin procesar

No realiza la conversión de una página de códigos a otra. Esta es la opción más rápida.

Página de códigos

Indica la página de códigos fuente donde se encuentran los datos de caracteres en el archivo de datos codificados, por ejemplo, 850. Esta página de códigos es necesaria para que el motor de base de datos SQL Server 2005 interprete correctamente los datos de entrada.

Archivo de error = 'nombre de archivo'

Especifica el archivo utilizado para recopilar filas con formato incorrecto y que no se pueden convertir a un conjunto de filas OLE DB. Las líneas se copian sin cambios desde el archivo de datos a este archivo de error.

Se crea un archivo de error cuando se inicia el comando. Si el archivo ya existe, se generará un error. Además, se crea un archivo de control con la extensión ERROR.txt.

Este archivo hace referencia a cada línea del archivo de errores y proporciona un diagnóstico de errores. Después de corregir los errores, se pueden cargar los datos.

FIRSTROW = first_row

Especifica el número de fila de la primera fila que se cargará. El valor predeterminado es 1, que especifica la primera línea del archivo de datos. Determine el número de línea contando el terminador de línea.

LASTROW =última línea

Especifica el número de línea de la última línea que se cargará. El valor predeterminado es 0, que especifica la última fila del archivo de datos.

MAXERRORS = Maximum_errors

Especifica el número máximo de errores de sintaxis o líneas mal formadas definidas en el archivo de formato antes de que OPENROWSET arroje una excepción. Hasta que se alcanza MAXERRORS, OPENROWSET ignora cada línea de error, no la carga y la trata como un error.

El valor predeterminado de Maximum_errors es 10.

Nota:

MAX_ERRORS no se aplica a restricciones CHECK, ni a conversiones de tipos de datos money y bigint.

ROWS _ PER _ BATCH = filas _ por _ lote

Especifica el número aproximado de filas de datos en el archivo de datos. Este valor debe ser el mismo que el número real de filas.

OPENROWSET siempre importa archivos de datos en un proceso por lotes. Pero si el valor de rows_per_batch se especifica como > 0, el procesador de consultas utilizará el valor de rows_per_batch como sugerencia al asignar recursos en el plan de consulta.

De forma predeterminada, ROWS_PER_BATCH es desconocido. Especificar ROWS_PER_BATCH = 0 equivale a ignorar ROWS_PER_BATCH.

SINGLE_BLOB

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

Importante:

Recomendamos que utilice solo la opción SINGLE_BLOB (en lugar de SINGLE_CLOB y SINGLE_NCLOB) para importar datos XML, porque solo SINGLE_BLOB admite todas las transcodificaciones de Windows.

SINGLE_CLOB

Al leer el archivo de datos en formato ASCII, el contenido se devuelve como un conjunto de filas de una sola fila y una sola columna de tipo varchar(max) utilizando la intercalación de la base de datos actual.

SINGLE_NCLOB

Al leer el archivo de datos en formato UNICODE, el contenido se devuelve como un conjunto de filas de una sola fila y una sola columna de tipo nvarchar(max) utilizando la intercalación de la base de datos actual.

Comentarios

OPENROWSET solo se puede utilizar para acceder a datos OLE DB cuando la opción de registro DisallowAdhocAccess para el proveedor especificado está establecida explícitamente en 0 y la opción de configuración avanzada de consulta distribuida ad hoc está habilitada Datos remotos desde la fuente. Si estas opciones no están configuradas, el comportamiento predeterminado no permite el acceso temporal.

Al acceder a una fuente de datos OLE DB remota, el servidor no delega automáticamente el ID de inicio de sesión de la conexión confiable a través de la cual el cliente puede conectarse al servidor consultado. Se debe configurar la delegación de autenticación. Para obtener más información, consulte Configuración de un servidor vinculado para proxy.

Los nombres de catálogo y esquema son necesarios si el proveedor OLE DB admite varios catálogos y esquemas en la fuente de datos especificada. Si el proveedor OLE DB no admite varios catálogos y esquemas, los valores del catálogo y del esquema se pueden ignorar. Si el proveedor solo admite nombres de esquema, el nombre de dos partes debe especificarse en el formato esquema.objeto.

Si el proveedor solo admite nombres de catálogo, el nombre de tres partes debe especificarse en el formato catálogo.esquema.objeto. Para consultas de paso a través que utilizan el proveedor OLE DB de SQL Native Client, se debe especificar un nombre de tres partes. Para obtener más información, vea Convenciones de sintaxis de Transact-SQL (Transact-SQL).

OPENROWSET no acepta variables de parámetros.

Uso de OPENROWSET con la opción BULK

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

Cláusula FROM usada con SELECT OPENROWSET(BULK) ...) se puede llamar en lugar del nombre de la tabla para implementar la función SELECT completa.

OPENROWSET con la opción BULK requiere un nombre relativo en la cláusula FROM, también conocido como variable de alcance o alias. Puede especificar alias de columna. Si no se especifica ninguna lista de alias de columna, el archivo de formato debe tener nombres de columna. La especificación de un alias de columna anula el nombre de la columna en el archivo formateado, por ejemplo:

De OPENROWSET(Gran capacidad...) como alias de tabla

De OPENROWSET(Gran capacidad...) .) Como table_alias(column_alias,...n)

Destacado... de la declaración OPENROWSET(bulk...) consultará directamente los datos en el archivo sin importar los datos a la tabla. Destacado... de La instrucción OPENROWSET (masivo...) también puede enumerar alias de columnas masivas utilizando un archivo de formato para especificar el nombre de la columna y el tipo de datos.

La instrucción del complemento...select * from openrowset (gran capacidad...) importa los datos del archivo de datos a la tabla de SQL Server en lotes. Para obtener más información, consulte Importación de datos masivos usando BULK INSERT u OPENROWSET(BULK)...).

Cuando la opción OPENROWSET BULK se usa con una instrucción INSERT, la cláusula BULK admite sugerencias de tabla. Además de las sugerencias de tabla habituales, como TABLOCK, la cláusula BULK también puede aceptar las siguientes sugerencias de tabla especiales: IGNORE_CONSTRAINTS (solo se ignoran las restricciones CHECK y FOREIGN KEY), IGNORE_TRIGGERS, KEEPDEFAULTS y KEEPIDENTITY. Para obtener más información, consulte Sugerencias para tablas (Transact-SQL).

Para obtener información sobre cómo utilizar la instrucción insert...select * from openrowset(bulk...), consulte Importación y exportación de datos masivos. Para obtener información sobre cuándo se registran en el registro de transacciones las operaciones de inserción de filas realizadas mediante importación masiva, consulte Requisitos previos para el registro mínimo en la importación masiva.

Nota:

Al utilizar OPENROWSET, es importante comprender cómo SQL Server 2005 maneja la suplantación. Para obtener información sobre consideraciones de seguridad, consulte Importación masiva de datos (masiva...) usando BULK INSERT o OPENROWSET.

Exportación o importación masiva de documentos SQLXML

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

Efectos del tipo de datos

SQLCHAR o SQLVARYCHAR

Envío de datos en la página de códigos del cliente o en la página de códigos implícita en la intercalación.

SQLNCHAR o SQLNVARCHAR

Enviar datos en formato Unicode.

SQLBINARY o SQLVARYBIN

Envía datos sin ninguna conversión.

Permisos

Los permisos OPENROWSET están determinados por los permisos del nombre de usuario pasado a la interfaz de acceso OLE DB. Para utilizar la opción masiva, necesita el permiso "Administrar operaciones masivas".

Ejemplo

A. Utilice OPENROWSET para las interfaces de acceso OLE DB de SELECT y SQL Native Client.

El siguiente ejemplo utiliza la interfaz de acceso OLE DB de SQL Native Client (SQLNCLI) para acceder a la tabla HumanResources. El archivo se encuentra en la base de datos AdventureWorks en el servidor remoto Seattle1. Utilice una instrucción SELECT para definir el conjunto de filas devueltas. La cadena del proveedor contiene las palabras clave Servidor y Conexión_confiable. El proveedor OLE DB de SQL Native Client reconoce estas palabras clave.

Copiar código

Seleccione uno *

FROM OPENROWSET('SQLNCLI ', 'Server = Seattle 1; Trusted_Connection = yes',

Seleccione nombre de grupo, nombre, ID de departamento

De Adventure Factory

ORDENAR POR nombre de grupo, nombre') como;

B. Usando el proveedor OLE DB de Microsoft para Jet

El siguiente ejemplo accede a la tabla Clientes en la base de datos de Microsoft Access Northwind a través del proveedor Microsoft OLE DB para Jet.

Nota:

Este ejemplo supone que Access ya está instalado. Para ejecutar este ejemplo, se debe instalar la base de datos Northwind. Para obtener más información sobre cómo instalar la base de datos Northwind, consulte Descargar la base de datos de muestra de Northwind y pubs.

Copiar código

Seleccione ID de cliente, nombre de la empresa

De OPENROWSET('Microsoft.Jet.OLEDB.4.0',

c :\Archivos de programa\Microsoft Office\Office 11\SAMPLES\northwind.MDB '

admin '', Cliente)

Ir

USANDO OTRA TABLA EN OPENROWSET E INNER JOIN

El siguiente ejemplo selecciona todos los datos almacenados en la tabla Clientes en la instancia local de la base de datos SQL Server Northwind y la tabla Pedidos en la base de datos Access Northwind en la misma computadora.

Nota:

Este ejemplo supone que Access ya está instalado. Para ejecutar este ejemplo, se debe instalar la base de datos Northwind. Para obtener más información sobre cómo instalar la base de datos Northwind, consulte Descargar la base de datos de muestra de Northwind y pubs.

Copiar código

Usar Viento Norte

Ir

Seleccionar c.*,o.*

Desde Northwind.dbo.Clientes como c

Unión interna OPENROWSET('Microsoft.

Jet.OLEDB.4.0 ',

c:\Program Files\Microsoft Office\Office 11\SAMPLES\northwind.MDB 'admin', orden)

as o

ON c.CustomerID = o.CustomerID

Vaya a

d. Utilice OPENROWSET para insertar los datos del archivo en la columna varbinary(max) en gran capacidad.

El siguiente ejemplo crea una pequeña tabla para demostración e inserta datos de un archivo llamado Text1.txt (ubicado en c:) en una columna variable (max).

Copiar código

Utilice AdventureWorks

para

crear la tabla myTable (nombre de archivo nvarchar(60),

Tipo de archivo nvarchar(60), Document varbinary(max))

Ir

Insertar en mi tabla (Nombre de archivo, Tipo de archivo, Documento)

Seleccione 'Texto1 .txt' como nombre del archivo,

. txt ' como tipo de archivo,

* de OPENROWSET(BULK N' c:\text1.txt', SINGLE_BLOB) como documento

Ir

E. el proveedor masivo OPENROWSET para formatear archivos y recuperar líneas de un archivo de texto.

El siguiente ejemplo utiliza un archivo formateado para recuperar filas del archivo de texto delimitado por tabulaciones value.txt, que contiene los siguientes datos:

Copiar código

1 Elemento de datos 1

2 Elemento de datos 2

3 Elemento de datos 3

El archivo de formato valores.fmt describe las columnas en valores.txt:

Copiar código

9.0

2

1 SQL char 0 10 " \ t " 1 ID SQL _ Latin 1 _ General _ CP 437 _ BIN

2 SQLCHAR 0 40 "\r\n" 2 Descripción SQL _ Latin 1 _ General _ CP 437 _ BIN

La siguiente declaración es la consulta para recuperar estos datos:

Copiar código

SELECT a . * FROM OPENROWSET(BULK 'c:\test\values.txt',

FORMATFILE = 'c:\test\values .fmt' )como;