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

Olá pessoal,
Boa noite!

Neste post, vou falar um pouco sobre os dois drivers OLEDB mais utilizados no SQL Server para integrações com arquivos, principalmente Excel, que são o Microsoft.ACE.OLEDB.12.0 e Microsoft.Jet.OLEDB.4.0. Uma vez instalados no servidor, eles permitem que, através do banco de dados, você consiga inserir, consultar, atualizar e apagar dados de planilhas do Excel e arquivos de texto utilizando o comando OPENROWSET.

A OLE DB é uma API desenvolvida pela Microsoft com base no COM. Essa API representa uma biblioteca de interface COM que permite o acesso universal a arquivos e também a diversas fontes de dados, como bancos de dados Oracle, SQL Server, Access e outros.

Como verificar quais os providers OLEDB estão instalados

Como verificar quais os providers OLEDB estão instalados

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

SQL Server - Providers sp_MSset_oledb_prop

Ou pelo Management Studio:
SQL Server - Providers List

Diferenças entre ACE OLEDB e o Jet OLEDB

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

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.

SQL Server - Microsoft.JET.OLEDB.4.0

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:
SQL Server - Microsoft ACE OLEDB 12.0 Openrowset Opendatasource Example File

Exemplos de utilização:

SQL Server - Microsoft.JET.OLEDB.4.0 OPENROWSET OPENDATASOURCE

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

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)

SQL Server - Install Microsoft Access database engine 2010 Setup

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:
SQL Server - Microsoft ACE OLEDB 12.0 Openrowset Opendatasource Example File

Utilizando OPENROWSET e OPENDATASOURCE com o driver ACE DB 12.0:

SQL Server - Microsoft ACE OLEDB 12.0 Openrowset Opendatasource

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

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

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)

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

Deste vez, 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”.

É isso aí, pessoal!
Abraço e até o próximo post.

SQL, sql server, sql server 2008, sql server 2008 R2, Oracle, Oracle Database, Oracle 11g, Oracle 10g, Oracle 12c, MySQL, Firebird, Consultoria, Consultor, Programador, Programação. Desenvolvedor, Analista de Sistemas, DBA, Criação de website, Criação de Sistema Web, Vitória, Vila Velha, Guarapari, Espírito Santo, ES, Consultoria SQL em VItória, Treinamento, Curso, Prestação de serviço, prestar serviço, freelancer, freela, banco de dados, consultoria em banco de dados, consultor de banco de dados

Um comentário em “SQL Server – Como instalar os drivers Microsoft.ACE.OLEDB.12.0 e Microsoft.Jet.OLEDB.4.0

Deixe uma resposta