OLEDB Engine - Throws error

Question

OLEDB Engine - Throws error

Lance James 346

Trying to convert Excel Import actions in a C# program using DataTables, to using T-SQL so all processing is on the server side.

Setup:

Query:

SELECT * INTO Data_dq  
FROM OPENROWSET('Microsoft.ACE.OLEDB.16.0',  
    'Excel 12.0; Database=\\Server1\ExcelFileToImport.xlsx', [Sheet1$]);  
GO

I have tried both OLEDB.16.0 & OLEDB.12.0 but can't get past these errors.

Msg 7403, Level 16, State 1, Line 1  
The OLE DB provider "Microsoft.ACE.OLEDB.16.0" has not been registered.  
Msg 7403, Level 16, State 1, Line 6  
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.

Engine16.0 is installed and 12.0 cannot be installed concurrently. The attempt to do so throws and installation error.

There is a lot of chatter on the internet for this error but it always comes down to installing the 12.0. This is not an option so am I out of luck?

I also found this and applied:

sp_configure 'show advanced options', 1;  
RECONFIGURE;  
GO  
sp_configure 'Ad Hoc Distributed Queries', 1;  
RECONFIGURE;  
GO

Regards,
Lance

LiHong-MSFT 9,996 Reputation points

2022-04-29T06:03:11.263+00:00

Hi @Lance James
Is there any update about this issue?
If all of the answers are not working or helpful, please share with us your confusion or more details about this issue.
And don't forget to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thanks for your understanding!

Best regards,
LiHong

LiHong-MSFT 9,996 Reputation points

2022-04-29T06:03:11.263+00:00

Hi @Lance James
Is there any update about this issue?
If all of the answers are not working or helpful, please share with us your confusion or more details about this issue.
And don't forget to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thanks for your understanding!

Best regards,
LiHong

Answer 1

Erland Sommarskog 78,826 MVP

I just want to check one thing out: You have installed ACE on the same machine that SQL Server is running on, haven't you?

I mean, if SQL Server runs on another machine, and you install ACE on your desktop, it is not going to work out.

Lance James 346 Reputation points

2022-05-12T16:03:37.29+00:00

So many assisted on this issue that I want to make clear that though I am marking this as the answer, all other input shared was absolutely necessary to get to this point.

This answer is selected due to the fact that when I installed the OLEDB providers on the SQL Server box I no longer receive the timeout error. In fact, the processing is almost instantaneous.

I still cannot see the OLEDB providers listed in the SSMS. Not sure why but I do think the location of the OLEDB provider installations made the difference.

Regards,
Lance

Answer 2

Naomi 7,096

Did you try to follow steps from this blog post
abbeycode.com/blog/2019/10/17/read-excel-file-in-sql-server-with-openrowset-or-opendatasource ?

Lance James 346 Reputation points

2022-04-22T13:54:36.773+00:00

Yes, I read that article previously.

1) I am not looking to use the import wizard. I need to perform this via code.
2) Just for testing purposes I confirmed that launching the import wizard as described is not an option from my Start Menu. It doesn't exist. I can use the import wizard from within SSMS but it needs the 32bit installation. This is not an option.

Thanks,
Lance

Answer 3

Michael Taylor 41,636

The error is most likely a bitness issue. The provider you're trying to use is x86 and your app is running as x64. The quick and dirty solution is to change your app's project properties to run as x86. However you shouldn't really be using that driver anyway in my opinion if you can avoid it.

Consider using the OpenXML SDK instead. It doesn't require that Office be installed at all. The downside is that you cannot directly move the data to SQL because it isn't an ADO.NET provider.

Lance James 346 Reputation points

2022-04-22T16:13:49.967+00:00

Thanks.

The SQL query I provided is being executed within SSMS. There are no layers or outside software involved other than trying to read the Excel file.

The error is thrown by SSMS and never even gets to a point it tries to read the Excel file

.

Regards,
Lance

Answer 4

Naomi 7,096

Do you have 32bit version of this installed? https://stackoverflow.com/questions/49860479/sql-server-import-export-wizard If you don't have 32bit version, you would not be able to run this command from SSMS.

Lance James 346 Reputation points

2022-04-22T16:49:00.797+00:00

Regards,
Lance
Michael Taylor 41,636 Reputation points

2022-04-22T17:01:45.523+00:00

You have a bitness issue I believe. Refer to this article. Specifically your SQL Server instance (which is likely x64) doesn't have the x64 driver installed on it so it won't work. You need to install the x64 version of the driver on the SQL Server to resolve this I believe.

Lance James 346

It seems my issue continues to get more complex.

Using a query from your suggestion, I see both 16.0 and 12.0 engines.

PS C:\WINDOWS\system32> foreach ($provider in [System.Data.OleDb.OleDbEnumerator]::GetRootEnumerator()){  for ($i = 0; $i -lt $provider.FieldCount; $i++){  
    >>         Write-Host $provider.GetName($i), $provider.GetValue($i) |Format-List  
    >>     }  
    >> Write-Host  
    >>  
    >> }  
        
SOURCES_NAME Microsoft.ACE.OLEDB.12.0  
SOURCES_PARSENAME {3BE786A0-0366-4F5C-9434-25CF162E475E}  
SOURCES_DESCRIPTION Microsoft Office 12.0 Access Database Engine OLE DB Provider  
SOURCES_TYPE 1  
SOURCES_ISPARENT False  
SOURCES_CLSID {3BE786A0-0366-4F5C-9434-25CF162E475E}  
  
SOURCES_NAME Microsoft.ACE.OLEDB.16.0  
SOURCES_PARSENAME {3BE786A2-0366-4F5C-9434-25CF162E475E}  
SOURCES_DESCRIPTION Microsoft Office 16.0 Access Database Engine OLE DB Provider  
SOURCES_TYPE 1  
SOURCES_ISPARENT False  
SOURCES_CLSID {3BE786A2-0366-4F5C-9434-25CF162E475E}

However, if I run this query from the same article, I don't see the 16.0 and 12.0 engines

Regards,
Lance

Answer 5

Hi @Lance James ,

(1) Please try the following query in SSMS:

-- 0. Check what 64-bit OLEDB providers are installed  
-- 0a. In Object Explorer: Server Objects/Linked Servers/Providers  
EXEC master.sys.sp_MSset_oledb_prop;

It should show what 64-bit OLEDB providers installed, and their properties, like on a screen shot below

(2) If you don't see at least one 64-bit ACE provider, you need to install just one of them:
Microsoft.ACE.OLEDB.16.0
Microsoft.ACE.OLEDB.12.0

(3) You would need to adjust your T-SQL statement along the following:

--INSERT INTO ... -- uncomment this line when you are ready  
SELECT *  
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',  
 'Excel 12.0 Xml; HDR=NO;  
 Database=\\Server1\ExcelFileToImport.xlsx',  
[Sheet1$]);

(4) It is possible to install both 32-bit and 64-bit on the same machine in a quiet mode:

AccessDatabaseEngine_X64.exe /quiet

OLEDB Engine - Throws error

6 additional answers

Activity