SQL Server – Como instalar os drivers Microsoft.ACE.OLEDB.12.0 e Microsoft.Jet.OLEDB.4.0

Como verificar quais os providers OLEDB estão instalados

Para verificar quais providers você tem instalado, você pode executar essa query:

Ou pelo Management Studio:

Diferenças entre ACE OLEDB e o Jet OLEDB

Muito parecidos, mas ao mesmo tempo, diferentes. Para operações básicas, como INSERT, UPDATE, DELETE e SELECT, dificilmente você irá notar alguma diferença entre esses dois drivers OLEDB, pois possuem os mesmos parâmetros e funcionam exatamente da mesma forma. Mas o que muda entre eles? Porque dois drivers ?

Lançado em 1992, o driver JET por muito tempo atendeu a necessidade de muitos desenvolvedores, provendo integrações entre diferentes fontes de dados de modo fácil e prático, abstraindo questões técnicas. Com o surgimento do Windows na plataforma x64 (64 bits), o JET começou a não atender mais os desenvolvedores, uma vez que o driver possuía suporte nativo apenas na plataforma x86 (32 bits) e para conseguir acessar bancos MDB e outras fontes de dados, era necessário utilizar um software 32 bits que atuava como um proxy.

Ciente desse cenário, a Microsoft lançou o Office 2007, e com ele, uma nova versão do JET, agora chamada de Office Access Connectivity Engine (ACE), e permitia compatibilidade com o JET 4.0 e suas versões anteriores e suportava o novo formato do Access (.accdb), que trouxe vários novos recursos ao Access, como campos multivalorados, melhorias de segurança e criptografia. Apesar disso, O ACE não manteve algumas funções importantes da versão 4.0 do JET, como recursos de replicação e segurança a nível de usuário.

Com o Access 2010, o driver ACE recebeu suporte à plataforma 64 bits, sendo considerado em sua essência, uma versão 64 bits do driver JET.

Analisando os 2 drivers, vemos que são muito parecidos para operações simples, mas quando envolvemos union, join, nested queries e outros, há boa probabilidade dos resultados não serem os mesmos. O driver ACE não possui um suporte tão grande à arquivos antigos como o JET, tanto que se você abrir esses arquivos em versões antiga do Access, como por exemplo, quando você está realizando um UNION em campos do tipo TEXT, onde o JET retorna TEXT(255), o ACE retorna MEMO.

Sendo assim, caso você esteja utilizando um sistema operacional 32 bits (o que não é recomendável atualmente), você pode escolher entre o ACE e o JET. Caso esteja utilizando uma versão 64 bits, você só poderá utilizar o ACE. Minha recomendação? Utilize o ACE.

Instalando o driver Microsoft Jet OLEDB

Como eu já havia mencionado, o driver JET OLEDB não funciona em ambientes 64 bits. Por isso, tive que criar uma outra VM 32 bits para realizar a instalação e mostrar para vocês.

Como vocês podem ver, estou utilizando o Windows Server 2008 R2 x86 e o SQL Server 2012 e o provider JET já está disponível para utilização sem precisar instalar nada. Apenas instalei as atualizações do sistema operacional (recém instalado) e depois instalei o SQL Server.

Arquivo exemplo:

Exemplos de utilização:

Reparem que para utilizar o driver JET, eu tive que converter minha planilha XLSX do Office 2016 para o formato XLS do Office 2003, e mudar na minha query a versão do Excel para a 8.0.

Caso eu tente importar o XLSX, vamos ver essa mensagem de erro:

Msg 7399, Level 16, State 1, Line 2
The OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.

Caso eu tente importar o XLS, mas não altere a versão do Excel para a 8.0 na minha query, vamos ver essa mensagem de erro:

OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” returned message “Não foi possível encontrar ISAM instalável.”.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.

O driver Microsoft.Jet.OLEDB.4.0 deve ser utilizado em sistemas operacionais 32 bits, e suporta arquivos do Excel até a versão 2003. Acima disso, você deve utilizar o ACE DB.

Instalando o driver Microsoft ACE OLEDB

Muito utilizado, principalmente pelo suporte a sistemas operacionais 64 bits, o driver ACE deve ser instalado utilizando um dos links abaixo:
– 2007 Office System Driver: Data Connectivity Components (32 bits)
– Microsoft Access Database Engine 2010 Redistributable (32 e 64 bits)

Após concluir a instalação, os providers e drivers já estarão disponíveis para uso no SQL Server (não precisa reiniciar).

Arquivo exemplo:

Utilizando OPENROWSET e OPENDATASOURCE com o driver ACE DB 12.0:

O driver Microsoft.ACE.OLEDB.12.0 pode ser utilizado em sistemas operacionais 32 bits para abrir arquivos do Excel até a versão 2007 e pode ser utilizado em sistemas operacionais 64 bits e nessa edição, pode abrir arquivos do Excel de qualquer versão.

Vale ressaltar que não é possível instalar o driver 64 bits do ACE OLEDB se o Microsoft Office 2007-2016 x86 (32 bits) estiver instalado. Ou seja, se você estiver utilizando o SQL Server 64 bits e tiver instalado o Microsoft Office 32 bits, você não irá conseguir utilizar as funções OPENROWSET/OPENDATASOURCE para abrir arquivos do Excel, e pode se deparar com essa mensagem de erro:

Msg 7403, Level 16, State 1, Line 1
The OLE DB provider “Microsoft.ACE.OLEDB.12.0” has not been registered.

Como habilitar as transações distribuídas

Um dos erros mais comuns ao utilizar providers OLE DB, é não habilitar o recurso Ad Hoc Distributed Queries. Quando isso ocorre, você irá se deparar com essa mensagem de erro:

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component
‘Ad Hoc Distributed Queries’ because this component is turned off as part of
the security configuration for this server.
A system administrator can enable the use of ‘Ad Hoc Distributed Queries’
by using sp_configure.
For more information about enabling ‘Ad Hoc Distributed Queries’,
see “Surface Area Configuration” in SQL Server Books Online.

Para resolver esse problema, é muito simples:

Configurando as propriedades do ACE OLEDB

Outro problema que pode ocorrer ao tentar utilizar o driver Microsoft ACE OLEDB, é não habilitar as features AllowInProcess e DynamicParameters e se deparar com a mensagem de erro abaixo:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”.

Caso isso ocorre com você, basta executar os comandos abaixo para habilitar esses recursos:

Concedendo permissões ao diretório TEMP (32 bits)

Esse tipo de problema só ocorre com o SQL Server x86 (32 bits) e por isso, não deve atrapalhar tanta gente. Isso ocorre porque o SQL Server cria arquivos temporários durante a execução das queries que utilizem o provider, utilizando as credenciais do usuário que está executando a query. A mensagem de erro gerada é algo assim:

OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” returned message “Unspecified error”.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.

Se o SQL Server está executando utilizando a conta Network Service, o diretório temporário deve ser algo como: C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp

Se o SQL Server está executando utilizando a conta Local Service, o diretório temporário deve ser algo como: C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp

Nesse caso, devemos conceder permissão de leitura e escrita para todos os usuários nesse diretório ou apenas para os usuários que executam esse tipo de query. Isso pode ser feito com um comando parecido com esse:

No exemplo acima, criei o comando para definição das permissões para o caso do SQL Server estar sendo executado utilizando a conta NetworkService e o usuário utilizado para executar as queries seja “vs”.