OLEDB Engine - Throws error

Lance James 371

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 10,061 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 accepted by question author

Erland Sommarskog 128.7K Reputation points MVP Volunteer Moderator

2022-04-29T21:01:18.24+00:00

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.
Please sign in to rate this answer.
Lance James 371 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
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

6 additional answers

LiHong-MSFT 10,061 Reputation points

2022-04-25T02:31:45.013+00:00

Hi @Lance James
Please try the following solutions from this article:
If the operating system is 64-bit, download "Microsoft Access Database Engine 2010 Redistributable" 64-bit and 32-bit from here and install them both.
If the operating system is 32-bit, download "Microsoft Access Database Engine 2010 Redistributable" 32-bit from here.(Note：Uninstall 64 bit version if previously installed.)
Hope this helps.

Best regards,
LiHong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Please sign in to rate this answer.
Lance James 371 Reputation points

2022-04-29T14:50:18.663+00:00

Windows will not allow the installation of 32bit & 64bit engines to co-exist for me.

I have installed both the 2010 and 2016 engines yet SSMS still doesn't see them.

Windows sees them:

Powershell sees them:

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}

Dealing with the frustration.

Regards,
Lance

Yitzhak Khabinsky 27,091 Reputation points

2022-04-29T15:48:22.167+00:00

Please check #4 in my answer.

Lance James 371 Reputation points

2022-04-29T16:50:25.827+00:00

Following your #4, I see the following which may represent the installation of 32bit & 64bit coexist.

However, here is what SSMS sees:

Thank you for the continued help.

Regards,
Lance

Yitzhak Khabinsky 27,091 Reputation points

2022-04-29T18:18:56.333+00:00

SSMS shows just 64-bit ACE installations.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Lance James 371 Reputation points

2022-04-29T21:06:51.6+00:00

Really great question.

I am dealing with Ace on the desktop not the actual server box. Monday, I will install the providers on the SQL server box and give report.

Thanks,
Lance
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

OLEDB Engine - Throws error

6 additional answers

Your answer