OLEDB Engine - Throws error

Lance James 366 Reputation points
2022-04-22T13:09:42.78+00:00

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:
195633-image.png

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
Accepted answer
  1. Erland Sommarskog 100.9K Reputation points MVP
    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.

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Naomi 7,361 Reputation points
    2022-04-22T13:24:32.983+00:00

    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 ?

  2. Michael Taylor 47,806 Reputation points
    2022-04-22T15:03:26.687+00:00

    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.

  3. Naomi 7,361 Reputation points
    2022-04-22T16:37:50.003+00:00

    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.

  4. Yitzhak Khabinsky 24,941 Reputation points
    2022-04-24T02:32:21.607+00:00

    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

    195874-ssms-oledb-providers.jpg

    (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
    0 comments