SQL Server - Cómo instalar los drivers Microsoft.ACE.OLEDB.12.0 y Microsoft.Jet.OLEDB.4.0

Cómo comprobar qué proveedores OLEDB están instalados

Para verificar qué proveedores ha instalado, puede ejecutar esta consulta:

EXEC master.dbo.sp_MSset_oledb_prop

O a través de Management Studio:

Diferencias entre ACE OLEDB y Jet OLEDB

Muy parecidos, pero a la vez diferentes. Para operaciones básicas, como INSERTAR, ACTUALIZAR, ELIMINAR y SELECCIONAR, apenas notará ninguna diferencia entre estos dos controladores OLEDB, ya que tienen los mismos parámetros y funcionan exactamente de la misma manera. ¿Pero qué cambia entre ellos? ¿Por qué dos conductores?

Lanzado en 1992, el controlador JET ha satisfecho durante mucho tiempo las necesidades de muchos desarrolladores, proporcionando integraciones entre diferentes fuentes de datos de una manera fácil y práctica, abstrayendo problemas técnicos. Con la aparición de Windows en la plataforma x64 (64 bits), JET comenzó a dejar de servir a los desarrolladores, ya que el controlador solo tenía soporte nativo en la plataforma x86 (32 bits) y para poder acceder a bancos MDB y otras fuentes de datos, era necesario utilizar un software de 32 bits que actuaba como proxy.

Consciente de este escenario, Microsoft lanzó Office 2007, y con él, una nueva versión de JET, ahora llamada Office Access Connectivity Engine (ACE), que permitía compatibilidad con JET 4.0 y sus versiones anteriores y soportaba el nuevo formato de Access (.accdb), que traía varias características nuevas a Access, como campos multivalor, mejoras de seguridad y cifrado. A pesar de esto, ACE no mantuvo algunas funciones importantes de JET versión 4.0, como la seguridad a nivel de usuario y las características de replicación.

Con Access 2010, el controlador ACE recibió soporte para la plataforma de 64 bits, considerándose, de hecho, una versión de 64 bits del controlador JET.

Al analizar los 2 controladores, vemos que son muy similares para operaciones simples, pero cuando involucramos unión, unión, consultas anidadas y otras, existe una buena posibilidad de que los resultados no sean los mismos. El controlador ACE no tiene tanto soporte para archivos antiguos como JET, tanto es así que si abre estos archivos en versiones antiguas de Access, por ejemplo, cuando realiza una UNION en campos de tipo TEXTO, donde JET devuelve TEXTO(255), ACE devuelve MEMO.

Por lo tanto, si utiliza un sistema operativo de 32 bits (que actualmente no se recomienda), puede elegir entre ACE y JET. Si está utilizando una versión de 64 bits, sólo podrá utilizar ACE. ¿Mi recomendación? Utilice ACE.

Instalación del controlador Microsoft Jet OLEDB

Como ya mencioné, el controlador JET OLEDB no funciona en entornos de 64 bits. Por lo tanto, tuve que crear otra VM de 32 bits para realizar la instalación y mostrársela.

Como puede ver, estoy usando Windows Server 2008 R2 x86 y SQL Server 2012 y el proveedor JET ahora está disponible para usar sin tener que instalar nada. Acabo de instalar las actualizaciones del sistema operativo (recién instaladas) y luego instalé SQL Server.

Archivo de ejemplo:

Ejemplos de uso:

-- Utilizando OPENROWSET
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Temp\Pasta1.xls', [Planilha1$])

-- Utilizando OPENDATASOURCE
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\Temp\Pasta1.xls;Extended Properties=Excel 8.0')...[Planilha1$]

Tenga en cuenta que para usar el controlador JET, tuve que convertir mi hoja de cálculo XLSX de Office 2016 al formato XLS de Office 2003 y cambiar la versión de Excel a 8.0 en mi consulta.

Si intento importar XLSX, veremos este mensaje de error:

Mensaje 7399, Nivel 16, Estado 1, Línea 2
El proveedor OLE DB “Microsoft.Jet.OLEDB.4.0” para el servidor vinculado “(nulo)” informó un error. El proveedor no dio ninguna información sobre el error.
Mensaje 7303, Nivel 16, Estado 1, Línea 2
No se puede inicializar el objeto de origen de datos del proveedor OLE DB “Microsoft.Jet.OLEDB.4.0” para el servidor vinculado “(nulo)”.

Si intento importar XLS, pero no cambio la versión de Excel a 8.0 en mi consulta, veremos este mensaje de error:

El proveedor OLE DB “Microsoft.Jet.OLEDB.4.0” para el servidor vinculado “(nulo)” devolvió el mensaje “No se puede encontrar ISAM instalable”.
Mensaje 7303, Nivel 16, Estado 1, Línea 2
No se puede inicializar el objeto de origen de datos del proveedor OLE DB “Microsoft.Jet.OLEDB.4.0” para el servidor vinculado “(nulo)”.

El controlador Microsoft.Jet.OLEDB.4.0 debe usarse en sistemas operativos de 32 bits y admite archivos de Excel hasta la versión 2003. Por encima de eso, debe usar ACE DB.

Instalación del controlador OLEDB de Microsoft ACE

Ampliamente utilizado, principalmente debido a su soporte para sistemas operativos de 64 bits, el controlador ACE debe instalarse mediante uno de los enlaces siguientes:
– Controlador de Office System 2007: componentes de conectividad de datos (32 bits)
– Microsoft Access Database Engine 2010 redistribuible (32 bits y 64 bits)

Después de completar la instalación, los proveedores y controladores estarán disponibles para su uso en SQL Server (no es necesario reiniciar).

Archivo de ejemplo:

Usando OPENROWSET y OPENDATASOURCE con el controlador ACE DB 12.0:

-- Utilizando OPENROWSET
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Temp\Pasta1.xlsx', [Planilha1$])

-- Utilizando OPENDATASOURCE
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=C:\Temp\Pasta1.xlsx;Extended Properties=Excel 12.0')...[Planilha1$]

El controlador Microsoft.ACE.OLEDB.12.0 se puede utilizar en sistemas operativos de 32 bits para abrir archivos de Excel hasta la versión 2007 y se puede utilizar en sistemas operativos de 64 bits y, en esta edición, puede abrir archivos de Excel de cualquier versión.

Vale la pena señalar que no es posible instalar el controlador ACE OLEDB de 64 bits si está instalado Microsoft Office 2007-2016 x86 (32 bits). En otras palabras, si está utilizando SQL Server de 64 bits y ha instalado Microsoft Office de 32 bits, no podrá utilizar las funciones OPENROWSET/OPENDATASOURCE para abrir archivos de Excel y puede encontrar este mensaje de error:

Mensaje 7403, Nivel 16, Estado 1, Línea 1
El proveedor OLE DB “Microsoft.ACE.OLEDB.12.0” no se ha registrado.

Cómo habilitar transacciones distribuidas

Uno de los errores más comunes al utilizar proveedores OLE DB es no habilitar la función Consultas distribuidas ad hoc. Cuando esto ocurra, encontrará este mensaje de error:

Mensaje 15281, Nivel 16, Estado 1, Línea 1
SQL Server bloqueó el acceso a la DECLARACIÓN 'OpenRowset/OpenDatasource' del componente
'Consultas distribuidas ad hoc' porque este componente está desactivado como parte de
la configuración de seguridad para este servidor.
Un administrador del sistema puede habilitar el uso de "Consultas distribuidas ad hoc"
mediante el uso de sp_configure.
Para obtener más información sobre cómo habilitar "Consultas distribuidas ad hoc",
consulte “Configuración del área de superficie” en los Libros en pantalla de SQL Server.

Para solucionar este problema es muy sencillo:

sp_configure 'Show Advanced Options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

Configuración de propiedades ACE OLEDB

Otro problema que puede ocurrir al intentar utilizar el controlador Microsoft ACE OLEDB es no habilitar las funciones AllowInProcess y DynamicParameters y aparecer el siguiente mensaje de error:

Mensaje 7399, Nivel 16, Estado 1, Línea 1
El proveedor OLE DB “Microsoft.ACE.OLEDB.12.0” para el servidor vinculado “(nulo)” informó un error. El proveedor no dio ninguna información sobre el error.
Mensaje 7330, Nivel 16, Estado 2, Línea 1
No se puede recuperar una fila del proveedor OLE DB "Microsoft.ACE.OLEDB.12.0" para el servidor vinculado "(nulo)".

Si esto le sucede, simplemente ejecute los siguientes comandos para habilitar estas funciones:

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

Conceder permisos al directorio TEMP (32 bits)

Este tipo de problema sólo ocurre con SQL Server x86 (32 bits) y por lo tanto no debería molestar a tanta gente. Esto ocurre porque SQL Server crea archivos temporales durante la ejecución de consultas que utilizan el proveedor, utilizando las credenciales del usuario que ejecuta la consulta. El mensaje de error generado es algo como este:

El proveedor OLE DB “Microsoft.Jet.OLEDB.4.0” para el servidor vinculado “(nulo)” devolvió el mensaje “Error no especificado”.
Mensaje 7303, Nivel 16, Estado 1, Línea 1
No se puede inicializar el objeto de origen de datos del proveedor OLE DB “Microsoft.Jet.OLEDB.4.0” para el servidor vinculado “(nulo)”.

Si SQL Server se ejecuta utilizando la cuenta de servicio de red, el directorio temporal debería ser algo así como: C:\Windows\ServiceProfiles\Servicio de red\AppData\Local\Temp

Si SQL Server se ejecuta utilizando la cuenta de servicio local, el directorio temporal debería ser algo así como: C:\Windows\ServiceProfiles\Servicio local\AppData\Local\Temp

En este caso debemos otorgar permisos de lectura y escritura a todos los usuarios de ese directorio o solo a los usuarios que ejecutan este tipo de consultas. Esto se puede hacer con un comando similar a este:

icacls C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp /grant vs:(R,W)

En el ejemplo anterior, creé el comando para definir permisos en caso de que SQL Server se esté ejecutando usando la cuenta NetworkService y el usuario usado para ejecutar las consultas sea "vs".