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
If Office 2007 is Installed then the system will not allow you to install 64bit version driver. The solution to install the 64Bit Driver is installing it in passive mode. i.e. install the driver using Start -> Run… select the 64bit version file and add /passive flag at the end like below …
/AccessDatabaseEngine_x64.exe /passive
Hi,
The fix given worked well. Thanks Pinal
thanks alot… you genuis .. i am looking for this soluaiton for one month.. but now its resloved from you gudieline.
Thanks Pinal, installing the 2007 Office System Driver: Data Connectivity Components worked for me :)
Windows 10 with Office 2016 64 bit installed. I installed MS Access Runtime 2016 64bit first followed by MS Access Runtime 2013 32bit using the passive key (depicted above). This seemed to work for me.
The 64-bit version of the Access Database Redistributable did not work for me on my Server, running SQL 2012 (it’s a Server, so not running Office). I had to uninstall it and install the 32-bit version
Thank you very much . This solved my problem, i have spent more than 2 hours for fixing this problem.
and i find this article, i try it then my problem is solved.
Thank you very much
I recently got a new laptop at work, and the IMPORT from Excel to SQL stopped working with the error mentioned in the post. I have 64-bit Office Installed on a 64-bit machine (Windows 7).
I installed the AccessDatabaseEngine_X64 but the issue still exists. Did anyone face a similar situation and want to share how to fix this?
Thanks
Go to -> C:\Program Files\Common Files\microsoft shared\OFFICE14 copy ACEOLEDB.dll & paste it into ur MS OFFICE installed folder.
This worked for me
Thanks for sharing.
Thanks it worked for me
Open the 64-bit SQL Server import wizard from the startmenu. SSMS will standard use the 32-bit version of it.
It worked for me. Maybe also for you.
I had this problem, tried this solution with x64 system and didn’t work, so I downloaded 2007 Office System Driver: Data Connectivity Components in x86 and worked like a dream.
We tried installing the AccessDatabaseEngine_x64.exe package onto our 64 bit sever but no joy. Same error afterwards (we did not reboot by the way).
When we run EXECUTE MASTER.dbo.xp_enum_oledb_providers, we can see the new driver (Microsoft.ACE.OLEDB.12.00) in the result set. There is another driver in the list though (SQLOLEDB).
Do we need to take one more step to instruct sql server to specifically use the new driver for imports/ETL etc?
If your SQL Server and MS Office are 64 bit then below link works. It worked for me.
Thanks a lot! That helped me, my environment is: Win 10 64 bit, Office 64 bit, SQL 2016 Developer 64
adek1304
great adek1304
SQL SERVER – FIX – Export Error – ‘Microsoft.ACE.OLEDB.16.0’ provider is not registered on the local machine
I am running a 64 bit environment with Office 2016 64bit. The import /export is failing for the following:
1. SQL Server 2016 import/ export 64bit does not work for Excel
2. SSIS 2016 source /destination for does not work for Excel 64 bit
I have tried many corrections that have not worked. Anything would be great.
Someone mentioned this in an earlier post. The 2 easy fixes are to 1. Save it as an 97-2003 .xls file or 2. Save as a .CSV file and import as .CSV
If it works then it a nice workaround.
Yes, I saved as a .CSV file and that worked for me. Thanks!
I was facing the same issue. I installed the ‘2007 Office System Driver: Data Connectivity Components’ from the URL mentioned below:
{{locale}}/download/404Error.aspx
Resolved the issue. Thanks alot.
Thanks for sharing it Anik.
Try install 2007 Office System Driver: Data Connectivity Components {{locale}}/download/404Error.aspx
This is same as what Anik shared earlier?
Folks,
Server: Windows Server 2008 R2 – no Office installed
SSIS Package: Built using SQL Server Data Tools
Database: SQL Server 2012 SP3, 64-bit
I’m trying to run an SSIS package on a Windows server to no avail. I’ve tried installing ACE 2007 32-bit, 2010 32-bit Redist and 2010 64-bit Redist, but I still get “The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine”. In Programs and Features, I can see ACE 2010 32-bit Redist (currently installed) but not when I run EXEC master..xp_enum_oledb_providers in SSMS.
Is there a registry setting that I need to change for this to work because I’ve been trying all kinds of suggestions to no avail. Sometimes Microsoft can be extremely frustrating!
TIA!
Silly really… I uninstalled ACE 2010 32-bit Redist and reinstalled ACE 2010 64-bit Redist, then changed SSIS to Run64BitRuntime = True and now it works!
And they wonder why I get confused ;-)! MS needs to make it mind between 32-bit and 64-bit.
Thanks for sharing this Sainey.
Hi Sainey. I swear your saolution is great. Unfortunately I don’t know where to set Run64BitRuntime = True. I use SQL 2014
Thank you :-)
Julian