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.
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”.
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
——————————
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
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.
- Uninstall 32-bit version of Microsoft Office.
- 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).
- 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”
- On a 32-bit operating system
- C:\Program Files\Common Files\Microsoft Shared\OFFICE14\ACEOLEDB.DLL
- On a 64-bit operating system
- If 32-bit office is installed then
C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE14\ACEOLEDB.DLL
- 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)
119 Comments. Leave new
This doesn’t seem like a great solution as even Microsoft regularly recommends against using the 64-bit version of Office, for all sorts of reasons. If one has the right file, can one use regsvr 32, regsvr64 or similar to accomplish the driver installation?
I’m actively fighting with this issue (again, I fight with it everytime I want to modify a stored proc that exports to excel). I feel like I tried the above solution in the past to no effect, might try again. I’m with you jermitts, there must be a simpler/better way.
If you find any solution other than above, please share.
There IS a solution to the problem where you have 32-bit Office 2010 installed on 64-bit Windows but need the 64-bit Access DB Engine installed to get your application to see the driver. Furthermore the solution doesn’t involve ripping out your 32-bit Office installation and replacing it with 64-bit Office 2010 (which as others have observed, is not recommended by MS). As you noted Dave, if you run the AccessDatabaseEngine_x64.exe interactively by double-clicking the file name in windows explorer you are told to take a hike because it doesn’t match the ‘bittyness’ of the your 32-bit version of Office 2010. However if you run it from a command prompt with the /passive switch (e.g. \AccessDatabaseEngine_x64.exe /passive) it installs silently and in my case, fixed the missing driver error.
It worked for me! Finally! Thanks a lot!
Sorry I’m going to have to retract my post of 5th Jan. It’s factually correct but I discovered later in the day that although it fixed the ‘missing driver’ problem, it broke my MS Office installation. Excel would still run but ran the repair installer every time it was started and Access tried to run a repair but eventually gave up. I had to remove the 64-bit database engine and run an Office repair to get everything going again. Sorry to get your hopes up.
Thanks for letting us know Robert. Appreciate it!
I have found a work around for when I get this error; I open a new excel workbook, then copy/paste the data from the previous file into the new one. I then save it in a xls (Excel 2003-2007 Workbook) format and that resolves the import problem for me.
I did the same thing, but when your file contains over 64k rows of data, it craps out on you. :/
We were successful with the above solution, although we did this over a year ago. Our only difference is that we have a 64-bit OS and the DLL file is located:
C:\Program Files\Common Files\Microsoft Shared\OFFICE14\ACEOLEDB.DLL
Thanks for sharing.
Hm, I’m getting this error even tho the SQL Provider shows it installed on the instance: I tried per above, uninstalling, then installing 64 bit, still the same error
I also tried installing the 64 bit ACE driver, but still get the same message,Microsoft.ACE.OLEDB.12.0’ provider is not registered. In the above article, you showed the current providers by executing EXECUTE MASTER.dbo.xp_enum_oledb_providers. Are we suppose to see “ACEOLEDB” in the results? Could we some how add this in one of the providers?
Well, I found the cause of this problem. Lot of us are facing this issue, because the ACE.OLDB setup needs to be done on the server and not on the client.
yeah. provider list is picked from server.
you can open your spreadsheet and then Save As Excel 97-2003. Should import.
yeah. that should work if we have provider installed.
Hi Mr Pinal Dave Sir, This Solution worked for me; Installing ACCESS DATABASE ENGINE for OFFICE 2007. I’m using SQLSERVER 2014. Here’s the link
i had same issue and it resolved via installing 007 Office System Driver: Data Connectivity Components.
My Environment is:
OS windows 10 64bit
SQL 2014 Enterprise 64
hope it will help
Haseeb
All I had to do was install the 32-bit version of “Microsoft Access Database Engine 2010 Redistributable”. I’m on Windows 7 Pro 64-bit, running SQL Server 2008 R2 x64 SSMS to connect to a SQL Server 2008 R2 server. I have Office 2013 32-bit.
I received this error when trying to import data from an Excel spreadsheet that had been created in MS Office 2013. I re-saved the file, but saved it as an xls (Excel 97-2003 Workbook), then copied it over to the SQL server and tried the import again. That time, the import was successful.
Thanks Dave, it worked — and I did not need to restart the SQL Server Instance. After downloading and running “AccessDatabaseEngine_x64.exe from second link provided earlier”, I simply refreshed Server Objects and could now find the “ACE” provider I was expecting in the SQL Server list.
Great. Thanks for sharing the outcome.
Thanks Yassir, your solution worked for me by installing this:
I’m using SQL Server 2014, Office 2013, 64-bit, OS: Win 8.1
Thank you, correct driver for the configuration. Works no new instance needed
Thanks @Judina
i already had the 2010 version installed previously. i decided to install 2007 too and it fixed my error.thing is, it was already working before. somehow it decided to stop.
I have no idea about why something would suddenly fail.
I have 64-bit machine and 64-bit MS Office Standard 2013 installed, I am using SQL Server 2014, installed the Microsoft Access Database Engine 2010 redistributed-64 bit but still getting this error
I think SSMS is 32 bit. that’s why?
I have 64 bit version of SQL server 2012 and 64-bit of windows server 2012. I came across this issue and resolved when I installed MS OFFICE 2007 with Microsoft Access Database Engine 2010 redistributed-64 bit. I don’t know why It’s not working with MS OFFICE 2010 version.
Really? I have no idea.
This is a very annoying problem. I use Office Objects because they are always there. No More!
This broke an app that is more valuable to the enterprise than Office 2013.
If you have apps that use the Excel Data Provider, stay with your current version of Office until this is fixed. That’s what we should have done.
I remember back when .NET was supposed to prevent this nonsense.
Thanks for your comment MichaelK
Hi, After installing the Microsoft Access Database Engine 2010 Redistributable, I was able to resolve the issue.
Hi, As you mentioned in the above blog i checked accordingly and in my local machine i have Microsoft.ACE.OLEDB.12.0 provider is available but still i am getting the error message “the ‘microsoft.ace.oledb.12.0’ provider is not registered on the local machine.(system.data) ” while importing the in ssms.
Downloading the Microsoft Access Database Engine 2010 Redistributable worked for me. Thanks for the help!
If you have 32 bit Office 2010 & 64 bit Windows, install the Access Database Engine 2007 or 2010. Then install the Access Database Engine x64 with the passive switch (use cmd line). Just as others have mentioned. But then use registry editor (regedit) and delete (or rename) the mso.dll value in the following key: “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Common\FilesPaths”. If you don’t remove this value it will reconfigure office and try to repair every time you open an Office app.
Thanks it worked for me. I had to use the second link and installed the “AccessDatabaseEngine.exe” setup for fixing that problem.