6

I have a SQL Server instance 2019 64 bit and Microsoft Office 2019 64 bit.

When I want to "import data" from Excel files with SSMS, I have the following errors depending the Excel version :

  • SQL Server 2019 : 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. (System.Data)
  • SQL Server 2019 : 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data)

I tried to install :

  • Microsoft Access Database Engine 2010 Redistributable
  • Microsoft Access Database Engine 2016 Redistributable

Though, in SSMS, I can see 2 providers : Microsoft.ACE.OLEDB.12.0 and Microsoft.ACE.OLEDB.16.0 But nothing works.

I have searched a solution for a long time but I found nothing. The solution on this topic does not work : 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data)

Windows 10 64 bits version : 20H2

SQL Server instance version : Microsoft SQL Server 2019 (RTM-GDR) (KB4583458) - 15.0.2080.9 (X64) Nov 6 2020 16:50:01 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 19042: )

Thom A
  • 88,727
  • 11
  • 45
  • 75
David Carlier
  • 61
  • 1
  • 1
  • 3
  • Any reason you're not using SSIS for this? – Charlieface Jan 21 '21 at 14:18
  • 1
    Did you install the **32 bit** version of the ACE drivers? SSMS can't use the 64 bit drivers, only the 32bit ones, as it's a 32 bit application. Considering you state you're using the 64 bit version of office, I suspect you've installed the 64 bit drivers. – Thom A Jan 21 '21 at 14:52
  • your answer here https://answers.microsoft.com/en-us/msoffice/forum/all/the-microsoftaceoledb160-provider-is-not/40945bb6-4121-4c09-80b1-01c578c6c6be – Amirhossein Jul 22 '22 at 11:22

3 Answers3

3

For those struggling like me about 32 and 64 version of Mssql and Office. I found it easier to save xls as csv and then Tasks > Import Flat File > Select .csv

import flat file

The only issue I had was about the column types, which I had to set manually. Still easier though...

Christopher Freyburg
  • 65
  • 5
  • You can detect column types based on the data if you choose export data option, it can be the same if you choose your SQL Server database as a destination – Dordi Dec 20 '22 at 13:21
1

if you use office version 64 bit

search in start about "SQL Server 2019 Import and Export Data (64-bit)"

and us it

khaleel.zourob
  • 19
  • 2
  • 1
    Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Oct 22 '21 at 15:30
0

SSMS importing option is 32 bit execution option, means it runs as as 32 bit app. The suggested solutions for your case are:

1- use the 64 bit edition of Import/export data wizard out of SSMS.

2- remove the 64 bit office and install the 32bit edition (if available) if not then do not install office and install the Access Database Engine x86 versions. Then you can use the import wizard inside SSMS.

3- use a virtual machine with windows 32 bit edition but at then you will have to use much older version of ssms (as far as I have tested none works even version 16.x.x, so I hade to install the sql server management set from this link) it's name is SQLManagementStudio_x86_ENU.exe .

Those are the 3 available solutions my experience guided me to.

Mohammed Mahmoud
  • 59
  • 1
  • 3
  • 10