Los servidores vinculados nos permiten el acceso de SQL Server a todos los datos de un origen de datos remoto. Es una fuente de datos remota que puede ser otra instancia de SQL Server u otras fuentes de datos como MySQL, bases de datos de Access, Oracle, libros de Excel, archivos de texto, etc.
SQL Server se conecta al origen de datos remoto a través de un proveedor OLE DB. Hay variaciones de los proveedores OLE DB según la fuente de datos con la uno quiera establecer conexión. Existen Proveedores de Microsoft OLE DB para Oracle, Proveedores de Microsoft OLE DB para SQL Server, Proveedores de OLE DB para Jet etc.:
Microsoft SQL Server puede admitir conexiones a otras fuentes de datos OLE DB de forma persistente o ad hoc. La conexión persistente se la conoce como un servidor vinculado. Una conexión ad hoc, que se realiza para una sola consulta, se conoce como una consulta distribuida.
Una consulta distribuida se puede también ejecutar sin crear primero un servidor vinculado mediante el uso de las funciones OPENROWSET y OPENDATASOURCE de Transact-SQL.
Al tratar de ejecutar una declaración T-SQL contra un servidor vinculado, el proveedor OLE DB (por ejemplo, el Proveedor OLE DB para Jet) para el servidor vinculado se carga en el espacio de direcciones de la memoria del servidor SQL, luego SQL Server toma la declaración T-SQL y llama al Interfaz OLE DB. Luego, la interfaz OLE DB proporciona una conexión al origen de datos remoto (por ejemplo, un archivo de Excel). El proveedor OLE DB luego procesa el conjunto de resultados y devuelve los resultados al cliente de SQL Server que realizó la llamada inicial.
En este artículo, vamos a mostrar cómo poder crear un servidor vinculado para poder realizar consultas de datos de Excel desde el archivo de Excel (* .xlsx) usando el proveedor Microsoft.ACE.OLEDB.12.0 OLE DB y también cómo se puede hacer usando funciones OPENROWSET y OPENDATASOURCE.
Creación de un servidor vinculado con el proveedor OLE DB Microsoft.ACE.OLEDB.12.0
Para poder importar datos de un archivo de Excel (Excel 2007 (xlsx) y superior) a SQL Server, se tiene que instalar el controlador Microsoft.ACE.OLEDB.12.0 OLE DB.
El controlador Microsoft.ACE.OLEDB.12.0 OLE DB se puede utilizar en las ediciones de SQL Server de 32 bits para archivos de Excel 2007, o posteriores, o en las ediciones de SQL Server de 64 bits para cualquier archivo de Excel.
Hay un proveedor más; Microsoft. Jet. OLEDB.4.0 que se puede utilizar en ediciones de 32 bits de SQL Server para archivos de Excel 2003 (o anteriores).
Puede ver una lista de todos los proveedores disponibles actualmente en SQL Server en la carpeta Proveedores:
Desde este enlace puede usted descargar e instalar el controlador Microsoft.ACE.OLEDB.12.0 OLE DB . Dependiendo de la versión de SQL Server (32 bits o 64 bits) que se esté usando, hay dos versiones del controlador Microsoft.ACE.OLEDB.12.0 OLE DB que se pueden instalar:
- AccessDatabaseEngine.exe es para la versión de 32 bits de SQL Server
- AccessDatabaseEngine_X64.exe es para la versión de 64 bits de SQL Server
Tiene que tener en cuenta que si se instaló AccessDatabaseEngine_X64.exe exe y se usa la versión de SQL Server 32-bit. El error que aparece a continuación puede aparecer al crear un servidor vinculado para consultar datos de Excel:
Error al recuperar los datos para esta solicitud. (Microsoft.SqlServer.Management.Sdk.Sfc)
– – – – – – – – – – – – – – – –
INFORMACIÓN ADICIONAL:
Se produjo una excepción al ejecutar una instrucción o lote Transact-SQL. (Microsoft.SqlServer.ConnectionInfo)
– – – – – – – – – – – – – – – –
El proveedor OLE DB “Microsoft.ACE.OLEDB.12.0” no se ha registrado. (Microsoft SQL Server, Error: 7403)
Cuando se vaya a instalar el archivo ejecutable AccessDatabaseEngine apropiado, el controlador OLE DB de Microsoft.ACE.OLEDB.12.0aparecerá en la lista de controladores disponibles en la carpeta Proveedores:
Después de instalar AccessDatabaseEngine apropiado, ya se puede crear un servidor vinculado. Para poder hacerlo, tiene que hacer clic con el botón derecho en la carpeta Servidores vinculadosy tiene que elegir el comando Nuevo servidor vinculado:
Esto abrirá el cuadro de diálogo Nuevo servidor vinculado:
En este cuadro de diálogo, se debe identificar el nombre de un servidor vinculadoy el tipo de servidor. En la casilla Servidor vinculado, se puede ingresar cualquier tipo de nombre para el servidor vinculado (por ejemplo, ExcelData).
Si se comprueba SQL Server, entonces un servidor vinculado será una instancia de SQL Server. Puede encontrar mucha más información sobre la creación de un servidor vinculado a SQL Server en la siguiente página: Cómo crear y configurar un servidor vinculado en SQL Server Management Studio. Dado que este artículo trata sobre el uso del origen de datos de Excel, en la sección Tipo de servidor, elija el botón de opción Otro origen de datos y en el cuadro combinado Proveedores, tiene que elegir el elemento Proveedor de OLE DB de Microsoft Office 12.0 Access Database Engine:
En el cuadro Nombre del producto, tiene que colocar el nombre del origen de datos OLE DB para poder agregar como un servidor vinculado (por ejemplo, Excel). En el cuadro Fuente de datos escriba la ruta completa y el nombre del archivo de Excel (por ejemplo, C:\Test \ Excel_Data.xlsx).
Para el campo de la cadena del Proveedor ingrese Excel 12.0:
Tiene que Tener en cuenta que, si está utilizando los archivos de Excel 97-2003 (.xls), en el campo Cadena de proveedor debe ingresar “Excel 8.0”.
En el caso de que se haya ingresado un nombre incorrecto en el campo Cadena del proveedor, aparecerá el siguiente error:
El servidor vinculado ha sido creado, pero falló una prueba de conexión. Quieres mantener el servidor vinculado?
– – – – – – – – – – – – – – – –
INFORMACIÓN ADICIONAL:
Se produjo una excepción al ejecutar una instrucción o lote Transact-SQL. (Microsoft.SqlServer.ConnectionInfo)
– – – – – – – – – – – – – – – –
No se puede inicializar el objeto de origen de datos del proveedor OLE DB “Microsoft.ACE.OLEDB.12.0” para el servidor vinculado “EXCELDATA”.
El proveedor OLE DB “Microsoft.ACE.OLEDB.12.0” para el servidor vinculado “EXCELDATA” devolvió el mensaje “No se pudo encontrar ISAM instalable”. (Microsoft SQL Server, Error: 7303)
Después de ingresar el nombre correcto en el campo Cadena de proveedor, tiene que presionar el botón Aceptar en el cuadro de diálogo Nuevo servidor vinculado para así poder crear el nuevo servidor vinculado. Durante este proceso de creación de un servidor vinculado, puede aparecer el siguiente mensaje de error:
Tiene que presionar Sí para crear el servidor vinculado EXCELDATA. El servidor vinculado EXCELDATA aparecerá en la carpeta Servidor vinculado:
Pero cuando busque datos en la carpeta Tablas de la base de datos predeterminada, aparecerá el siguiente error:
Error al recuperar los datos para esta solicitud. (Microsoft.SqlServer.Management.Sdk.Sfc)
– – – – – – – – – – – – – – – –
INFORMACIÓN ADICIONAL:
Se produjo una excepción al ejecutar una instrucción o lote Transact-SQL. (Microsoft.SqlServer.ConnectionInfo)
– – – – – – – – – – – – – – – –
No se puede inicializar el objeto de origen de datos del proveedor OLE DB “Microsoft.ACE.OLEDB.12.0” para el servidor vinculado “EXCELDATA”. (Microsoft SQL Server, Error: 7303)
Para poder resolver este error y el error anterior, tiene que cerrar el SQL Server Management Studio (SSMS) y ejecútelo nuevamente, pero esta vez como administrador:
Ahora, para poder expandir la carpeta Tablas, aparecerán las hojas de Excel. Tiene que tener en cuenta que en cada hoja se muestra como una tabla de SQL Server con un signo de dólar ($) al final:
Para crear un servidor vinculado mediante el uso de T-SQL, simplemente use el procedimiento almacenado sp_addlinkedserver y ejecute el siguiente código en un editor de consultas:
|
1 2 3 4 5 6 7 8 |
EXEC sp_addlinkedserver @server = N'EXCELDATA', @srvproduct = N'Excel', @provider = N'Microsoft.ACE.OLEDB.12.0', @datasrc = N'C:\Test\Excel_Data.xlsx', @provstr = N'Excel 12.0'; |
Puede encontrar mucha más información sobre la creación de un servidor vinculado mediante T-SQL en el siguiente artículo Cómo crear, configurar y eliminar un servidor vinculado de SQL Server mediante la página Transact-SQL.
Consultar datos de Excel utilizando el servidor vinculado.
Ahora que se creó el servidor vinculado, ¡consultemos algunos datos de Excel!
El siguiente código SQL mostrará una lista de todos los datos de la Hoja1 en el archivo Excel_Data.xlsx para el cual se creó el servidor vinculado:
|
1 2 3 |
SELECT * FROM EXCELDATA...[Sheet1$] |
Pero, cuando ejecute este código, el siguiente mensaje de error puede aparecer:
Msg 7399, Nivel 16, Estado 1, Línea 2
El proveedor de OLE DB “Microsoft.ACE.OLEDB.12.0” para el servidor vinculado “EXCELDATA” informó un error. Acceso denegado.
Mensaje 7301, Nivel 16, Estado 2, Línea 2
No se puede obtener la interfaz requerida (“IID_IDBCreateCommand”) del proveedor OLE DB “Microsoft.ACE.OLEDB.12.0” para el servidor vinculado “EXCELDATA”.
Esto suele suceder debido a los permisos inadecuados.
Una forma de resolver esto es, yendo al Administrador de configuración de SQL Server:
Desde el cuadro de diálogo Administrador de configuración de SQL Server, tiene que seleccionar SQL Server para el cual ha creado un servidor vinculado:
Haga clic derecho y, desde el menú contextual, haga clic en la opción de Propiedades:
En el cuadro de diálogo Propiedades de SQL Server debajo del carrito de Iniciar sesión, elija el botón de opción Cuenta incorporaday, en el cuadro combinado, seleccione el elemento Sistema local:
Desde el cuadro de diálogo Propiedades de SQL Server, tiene que hacer clic en el botón Aplicar y debe presionar el botón Sí en el cuadro de mensaje de advertencia Confirmar cambio de cuenta:
Ahora, abra SQL Server Manage Studio como administrador y en un editor de consultas, ejecute el código SQL:
|
1 2 3 |
SELECT * FROM EXCELDATA...Sheet1$ |
The following result will appear:
Esto mostrará una lista de todos los datos de la Hoja 1 del archivo Excel_Data.xlsx.
Después de todas estas configuraciones, sigue teniendo el mismo error, entonces quizás los componentes de acceso a datos de Microsoft (MDAC) no funcionen correctamente. Para tener más información, vea este artículo de Microsoft.
El siguiente código SQL insertará datos de la hoja 1 (Sheet1) en la tabla de SQL Server ‘SQLTable’:
|
1 2 3 4 |
INSERT INTO dbo.SQLTable(ID,Name) SELECT ID, Name FROM EXCELDATA...Sheet1$ |
Al tratar de consultar la tabla SQLTable, aparecerá el siguiente resultado:
Consultando datos de Excel utilizando las consultas distribuidas
Para poder acceder a los datos de un archivo de Excel a través de SSMS sin tener que crear un servidor vinculado, primero use las funciones OPENROWSET y OPENDATASOURCE de Transact-SQL.
Para poder establecer la conexión y los datos de consulta desde el origen de datos de Excel utilizando la función OPENROWSET , debe escribir el siguiente código SQL en el editor de consultas:
|
1 2 3 |
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Test\Excel_Data.xlsx;', 'SELECT * FROM [Sheet1$]') |
Pero, al ejecutar el código anterior, puede ocurrir el siguiente error:
Msg 15281, Nivel 16, Estado 1, Línea 8
SQL Server bloqueó el acceso a STATEMENT ‘OpenRowset / OpenDatasource’ del componente ‘Ad Hoc Distributed Queries ‘porque este componente está desactivado como parte de la configuración de seguridad de este servidor. Un administrador del sistema puede habilitar el uso de “Consultas distribuidas ad hoc” usando sp_configure. Para obtener más información sobre cómo habilitar “Consultas distribuidas ad hoc”, busque “Consultas distribuidas ad hoc” en los Libros en línea de SQL Server.
Para poder resolver este error, la opción Consultas distribuidas ad hoc debe de estar habilitada para abrir una conexión a un servidor remoto mediante OPENROWSET o OPENDATASOURCE. Esto se puede lograr usando el procedimiento sp_configure y ejecutando el siguiente código SQL en un editor de consultas:
|
1 2 3 4 5 |
EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure 'Ad Hoc Distributed Queries', 1 |
En el caso de que la consulta ejecutada arriba muestre el siguiente error:
La opción de configuración “Mostrar opciones avanzadas” cambió de 0 a 1. Ejecute la instrucción RECONFIGURE para instalar.
Msg 5808, Nivel 16, Estado 1, Línea 2
No se admite la actualización ad hoc de los catálogos del sistema.
Utilice el RECONFIGURE WITH OVERRIDE en vez de RECONFIGURE:
|
1 2 3 4 5 6 |
EXEC sp_configure 'show advanced options', 1 RECONFIGURE WITH OVERRIDE EXEC sp_configure 'Ad Hoc Distributed Queries', 1 RECONFIGURE WITH OVERRIDE |
Ahora al ejecutar el código SQL:
|
1 2 3 |
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Test\Excel_Data.xlsx;', 'SELECT * FROM [Sheet1$]') |
Se va a mostrar el siguiente resultado en la cuadrícula de resultados:
Se puede obtener el mismo resultado, utilizando la función OPENDATASOURCE. Tiene que escribir el siguiente código::
|
1 2 3 4 |
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=C:\Test\Excel_Data.xlsx;Extended Properties=Excel 12.0')...[Sheet1$] |
Al igual que todos los servidores vinculados, OPENROWSET y OPENDATASOURCE utilizan OLE DB para poder conectarse a la fuente de datos remota; pero, estas funciones no abarcan todas las características incluidas con los servidores vinculados, pero por lo general, la razón es porque los servidores vinculados son la opción preferida.
Otros artículos que pueden ser de su interés de esta serie:
- Cómo crear y configurar un servidor vinculado en SQL Server Management Studio
- Cómo crear, configurar y eliminar un servidor vinculado a SQL Server usando Transact-SQL
- Cómo configurar un servidor vinculado utilizando el controlador ODBC
- Cómo crear un servidor vinculado a una base de datos SQL de Azure
See more
To boost your SQL Server development productivity, check out Free SQL Server Management Studio add-ins.
Useful links
- Querying remote data sources in SQL Server
- How to use Excel with SQL Server linked servers and distributed queries
- OPENDATASOURCE (Transact-SQL)
- OPENROWSET (Transact-SQL)
Actualmente trabajando para ApexSQL LLC como un Ingeniero de Ventas de Software, él está ayudando a los clientes con problemas técnicos y hace aseguramiento de calidad para los complementos ApexSQL Complete, ApexSQL Refactor y ApexSQL Search.
Ver todas las entradas de Marko Zivkovic