OLEDB Engine - Throws error

Question

question

LanceJames-3930 asked Apr 22, '22 LanceJames-3930 commented May 12, '22

OLEDB Engine - Throws error

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

sql-server-transact-sql

image.png (5.2 KiB)

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LiHongMSFT-3908 · Apr 29 at 06:03 AM

Hi @LanceJames-3930
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

0 ·

Answer 1 · 2022-04-29T21:01:18.24Z

ErlandSommarskog answered Apr 29, '22 LanceJames-3930 commented May 12, '22

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.

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LanceJames-3930 · May 12 at 04:03 PM

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

0 ·

Answer 2 · 2022-04-22T13:24:32.983Z

NaomiNNN answered Apr 22, '22 LanceJames-3930 commented Apr 22, '22

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 ?

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LanceJames-3930 · Apr 22 at 01:54 PM

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

0 ·

Answer 3 · 2022-04-22T15:03:26.687Z

cooldadtx answered Apr 22, '22 LanceJames-3930 commented Apr 22, '22

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.

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LanceJames-3930 · Apr 22 at 04:13 PM

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

0 ·

image.png (27.1 KiB)

Answer 4 · 2022-04-22T16:37:50.003Z

NaomiNNN answered Apr 22, '22 LanceJames-3930 commented Apr 22, '22

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.

· 3

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LanceJames-3930 · Apr 22 at 04:49 PM

![195644-image.png][1]

Regards,
Lance
[1]: /answers/storage/attachments/195644-image.png

0 ·

image.png (31.0 KiB)

cooldadtx LanceJames-3930 · Apr 22 at 05:01 PM

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.

0 ·

LanceJames-3930 cooldadtx · Apr 22 at 05:20 PM

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

0 ·

image.png (175.5 KiB)

Answer 5 · 2022-04-24T02:32:21.607Z

YitzhakKhabinsky-0887 answered Apr 24, '22 YitzhakKhabinsky-0887 edited Apr 29, '22

Hi @LanceJames-3930,

(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

ssms-oledb-providers.jpg (27.0 KiB)

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 6 · 2022-04-25T02:31:45.013Z

LiHongMSFT-3908 answered Apr 25, '22 YitzhakKhabinsky-0887 commented Apr 29, '22

Hi @LanceJames-3930
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.

· 4

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LanceJames-3930 · Apr 29 at 02:50 PM

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

0 ·

image.png (10.1 KiB)

YitzhakKhabinsky-0887 LanceJames-3930 · Apr 29 at 03:48 PM

Please check #4 in my answer.

0 ·

LanceJames-3930 YitzhakKhabinsky-0887 · Apr 29 at 04:50 PM

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

0 ·

image.png (14.6 KiB)

image.png (16.0 KiB)

Show more comments

Answer 7 · 2022-04-29T21:06:51.6Z

LanceJames-3930 answered Apr 29, '22

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

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question