SQL SERVER – FIX – Export Error – ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine

Working with Excel files can always be of great interest. I have not seen a reporting solution in the world that doesn’t give an option to export to excel. If you are talking about two separate solutions and you want to export and import, then such options can be of great use. In this blog, I was trying to import data from Excel sheet to SQL Instance by using Import Wizard available in SQL Server Management Studio. I am sure all of us have had this requirement once in a while and would have surely tried to get this working.

SQL SERVER - FIX - Export Error - 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine ace-error-01

After selecting the option, I selected Data Source as “Microsoft Excel” and selected file name which I wanted to import. Excel file was created with latest version of excel so I selected “Microsoft Excel 2007”.

SQL SERVER - FIX - Export Error - 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine ace-error-02

As soon as I hit next, I got below error

TITLE: SQL Server Import and Export Wizard
——————————
The operation could not be completed.
——————————
ADDITIONAL INFORMATION:
The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine. (System.Data)
——————————
BUTTONS:
OK
——————————

SQL SERVER - FIX - Export Error - 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine ace-error-03

I checked management studio and as the message says, I didn’t see the provider as we can see below. We can also use below query to find the details of providers installed on the machine

EXECUTE MASTER.dbo.xp_enum_oledb_providers

SQL SERVER - FIX - Export Error - 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine ace-error-04

Few blogs have mentioned to download (2007 Office System Driver: Data Connectivity Components) but that didn’t work. So I tried installing http://www.microsoft.com/en-us/download/details.aspx?id=13255 (Microsoft Access Database Engine 2010 Redistributable) but it also failed because I am using 32 bit version of office on my 64 bit PC.

Note: If you are running 32-bit version of SQL then your issue will be solved by installing above. In my PC, I have to take below approach.

  1. Uninstall 32-bit version of Microsoft Office.
  2. Install the 64-bit version of MS Office (Make sure setup is run from “x64” folder and run setup.exe in order to install the 64 bit of MS Office).
  3. Install the 64-bit version “Microsoft.ACE.OLEDB.12.0” (download AccessDatabaseEngine_x64.exe from second link provided earlier).

Here is the location of DLL “ACEOLEDB.DLL”

  1. On a 32-bit operating system
    1. C:\Program Files\Common Files\Microsoft Shared\OFFICE14\ACEOLEDB.DLL
  2. On a 64-bit operating system
    1. If 32-bit office is installed then

C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE14\ACEOLEDB.DLL

  1. If 64-bit office is installed then

C:\Program Files\Common Files\Microsoft Shared\OFFICE14\ACEOLEDB.DLL

Production Server would not have complete office installed so it would not be a big deal to install correct version of driver.

Have you ever encountered this error in your environments? What did you do to solve them? Let me know.

Reference : Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – PowerShell to Count Number of VLFs in SQL Server
Next Post
SQL SERVER – How Do We Find Deadlocks? – Notes from the Field #086

Related Posts

116 Comments. Leave new

  • Hi, I have a SQL 2012 64bit version that is causing this error. Will this exe help ?

    Reply
  • Hi, I am facing the same issue and then Install the 64 bit ACEOLEDB.DLL as well. But still not able to import the data from excel. Post installation of ACEOLEDB.DLL, xp_enum_oledb_providers does not list the new provider entry

    Reply
  • it also worked for me thank you so much.

    Reply
  • Hi I installed Access Database Engine 2010 64bit. Microsoft.ACE.OLEDB.12.0 shown up in provider, but this error is still around. Any idea why this may base the case? Thank you.

    Reply
  • Hi

    I installed the 64 bit one because my office version is 64bit.

    However, then the right click menu from SSMS ([DataBase]->Tasks->Import Data) was still giving the error. If I manually ran the SQL program to import data that was 64 bit (WindowsMenu -> Microsoft SQL Server 2019 -> SQL Sever 2019 Import and Export Data(64bit) ), then no error. It seems my version of SQL Server has both a 32bit version and 64bit version of the program to Import and Export Data installed.

    To get the right click menu to work I uninstalled the 64bit Microsoft.ACE.OLEDB.12.0 and installed the 32bit version (since SSMS is 32bit I assumed it was launching that version). Problem solved on the right click menu (which is the only place I ever use Import and Export data from).

    Reply
  • thanks Alva that really helped. just going to start –all programs–sql–import and export was the deal

    Reply
  • wajeeha tayyabwajeha
    January 17, 2021 2:14 am

    Hi I have Access Database2019 64bit. and am using visual studio 2019 .Microsoft.ACE.OLEDB.12.0 shown up in provider,

    Reply
  • Thanks so far for the analysis, but you’re not solving the problem?

    Reply
  • install the 32bit version of access it works

    Reply

Leave a Reply

Menu