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

Solarwinds

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)

Solarwinds
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

100 Comments. Leave new

  • I installed the AccessDataBaseEngine_x64 and it worked like a charm! I couldn’t find it anywhere so thank you for linking it!

    Reply
  • I have installed the same and its working fine for me.

    Reply
  • Hi Dave,

    I’d like to say Thank you for all your wonderful posts. I have learned a lot about Sql Server from your blog and always come to you first for my questions. Recently, my office pushed MS Office 16 to my system. When I tried to import an Excel file to my database, I got the infamous The Microsoft.ACE.OLEDB. 16.0 provider is not registered on the local machine. I followed your first step {{locale}}/download/404Error.aspx) — the one that didn’t work for you — just to see and that solved my issue.

    Thanks for the great posts!
    Joni

    Reply
    • This fixed my issue. I’ve got Office 2016 / SSMS 2014 / Windows 7 and after a windows update and reboot, the provider was broken and threw an error when attempting to import excel files. Installing the 2007 Office System Driver: Data Connectivity Components worked for me.

      Reply
    • This fixed my issue too. I have Windows 10 / SQL Server 2012 / Office 2016.

      Thanks.

      Reply
    • Thank You Pinal & Joni. This link worked for me too.
      Have 32 – bit MS Office 16 installed on a 64 bit Windows OS and was receiving the error – Microsoft.ACE.OLEDB. 12.0 provider is not registered on the local machine.

      Reply
  • Hi Pinal

    Thanks for your blog post. After installing the package on one of our servers,I found that I could import data into an SQL Server database table from an Excel 97-2003 file, but not an Excel 2007 (or above) file.

    Hope that helps someone out there!

    Adam

    Reply
  • Pinal,
    I originally attempted this backwards since I am running SQL Management Studio 17 and figured if the first download ( didn’t work for you it wouldn’t work for me. So I skipped right to the second one( It didn’t work, so I went back and decided to start over from the beginning, NOW it worked. SQL Server is 2012 and I was attempting to import an Excel 2016 spreadsheet. Thanks for the help.

    Reply
    • That was exactly what happened to me. I have 32-bit office 2013 and 64-bit SQL 2014 on my 64-bit laptop. I skipped the first download and tried the second download directly. It did not work. Then I read your reply and want to give the first one a try and now it worked. Thanks.

      Reply
  • Sorry for my lack of knowledge, but … where can I set SSIS “Run64BitRuntime = True” as mentioned by Sainey ?
    Thank you all very much !

    Julian L

    Reply
    • Hi. I found an explanation/Solution: choose the correct x64 or x86 EXEcutable :-)
      For example, in my SQL 2014, I have:

      DTExec.exe : command line “launcher”:
      \SQL_Shared\120\DTS\Binn\DTExec.exe
      \SQL_Shared_x86\120\DTS\Binn\DTExec.exe

      DTSWIZARD: GUI useful to create DTSX files in a pure SQL Server installation
      \SQL_Shared\120\DTS\Binn\DTSWizard.exe
      \SQL_Shared_x86\120\DTS\Binn\DTSWizard.exe

      DTExecUI: the GUI useful tu run and give paramenters to DTSX packages already imported in SSIS: it has no x64 version.
      \SQL_Shared_x86\120\Tools\Binn\ManagementStudio\DTExecUI.exe

      So, chose the one that fits your provider version :-)

      Julian L

      Reply
  • Alexander Zaballa
    December 19, 2017 11:08 pm

    If you are right-clicking on the database and going to tasks->import, then here is the issue. Go to your start menu and under sql server, find the x64 bit import export wizard and try that.

    Reply
  • Hector Sanchez-Villeda
    January 5, 2018 12:39 am

    Once you download the provider you should restart SSMS otherwise the cache for some reason contains information resulting the same error. For 64 bits sometimes you also need to download providers for 32 and 64 bits.

    Reply
  • Much appreciation for this blog post. It helped me resolve my issue with exporting Excel data to SQL Server

    Reply
  • As with all your articles, this was the most simply explained with easy to follow steps. This fixed my issue. Thank you :)

    Reply
  • Hi Pinal. I am facing a challenge while creating a SSIS package and inside it I have a script task in which I have written some c# logic to write the SQL queries results to excel file.I am using Provider=Microsoft.ACE.OLEDB.12.0 as the excel connection string,but its asking for me to download Microsoft office access database engine 2007,which is practically not possible for me as I don’t have the permission to download and install it in server.I have searched a lot for the alternative but no luck yet.It would be great if you can advise me the right path how to proceed further.

    Reply
  • Hi Pinal,

    Just to add to expand on previous posts, my configuration: SQL Server 2017 64 bit, SSMS 2017 32 bit, and Excel 2016 64 bit on a 64bit Windows 10 machine.

    I had most of the same error messages. My whole goal was to to use 64bit Excel data import library (Data Source Microsoft Excel, Excel version Microsoft Excel 2016) not earlier version.

    Currently, there are no SSMS 17 64 bit edition, only 32 bit editions. (NOT SURE WHY? Doesn’t everyone have 64 bit these days?)

    That was my problem. Even after installing the proper 64bit Access Database libraries as mentioned above, SSMS 17 still calls the 32bit DTSWizard executable in its installed folders under \Program Files(x86) .

    Since I had the 64 bit Sql Server Developer edition installed, I renamed the 32 bit DTSWizard exe in the SSMS folder and moved the 64bit DTSWizard exe from SQL Server into it.

    Works great and now I can run import data from tasks from SSMS 17 and import using the 64 bit Excel 2016 library

    Reply
  • Can anyone provide MDAC link which work on windows server 2016??

    Reply
  • This worked for me. I tried installing the 32 bit, it didn’t work so I uninstalled and installed the 64 bit. Then it worked.

    Reply
  • I am encountering this and have tried everything I know to do. Currently, I’m relying on one person who is not getting this error (he has 32 bit office and I have 64 bit) but he is going on vacation and I need to be able to do the tasks using import/export wizard.
    I have SSMS 18, (it was working the same in sql 17.9, was thinking upgrading may help), Windows 10, 64 bit office.
    I have installed Access Database Engine x64.exe (multiple times).

    I try to change the excel version in the wizard and I get the same message but it references”…OLEDB.12.0″, “…OLEDB.15.0” and this is for Excel 2016

    I am at a loss and need this to work.

    The ‘Microsoft.ACE.OLEDB.16.0’ provider is not registered on the local machine. (System.Data)

    ——————————
    Program Location:

    at System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper)
    at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
    at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
    at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
    at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
    at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
    at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
    at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
    at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
    at System.Data.OleDb.OleDbConnection.Open()
    at Microsoft.SqlServer.Dts.DtsWizard.DTSWizard.GetOpenedConnection(WizardInputs wizardInputs, String connEntryName)
    at Microsoft.SqlServer.Dts.DtsWizard.Step1.OnLeavePage(LeavePageEventArgs e)

    Reply
  • Thanks Pinal Dave for another simple solution!

    Reply
  • I just open the file and Save As to previous versions Excel 97-2003 .xls and works fine

    Reply

Leave a Reply

Menu