SQLTeam.com | Weblogs | Forums

Help! Error querying Excel file using OpenRowset

I would like to import data from an Excel file to a table and tried to do the following but it's giving me an error...

It's using this command syntax:

SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;
Database=C:\DataFiles\EmployeeData1.xlsx',
[vEmployee$])

The error I'm getting is:

Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

In my case, the path to the Excel file is a remote server with an IP address and shared folder, for example, \100.70.33.72\ExcelFiles\EmpData.xlsx.

I am remotely connected (RDP) to a database server, 100.70.33.99, for example, which hosts the database where the table I wish to insert to exists and where the above command is being executed. This server is on 64-bit MS Windows Server 2012 R2 Standard OS with SQL Server 2014 Standard Edition 64-bit installed. I have an SSMS 2014 session open on this server in which I am issuing the command.

Does MS Office 2007 or higher need to be installed in the database server (100.70.33.99 in the example) in order to be able to have the Microsoft.ACE.OLEDB.12.0 provider available for use by the OpenRowset function?

What else might I be missing?

Thank you so much!

Yes, it needs to be on the server. since that is where it is running. YOu can try to run it locally if you have a local db to test it with

You can download the drivers separately from Office and install those on the server, but you do need the driver available on the server hosting SQL Server.

Thank you! That's what I thought.

Do you know where I can download the drivers? A link would be very helpful.

Thanks!

You can start here: https://www.microsoft.com/en-us/download/details.aspx?id=54920

This is very helpful. Thank you!
I will have the network guys at the server install it.

I installed the driver and it still gave me the same error. After so many days of hair pulling :slight_smile: and searching far and wide, I finally found this, which made it work:

The following commands are the key:

use master
go
sp_configure 'Show Advanced Options', 1
go
reconfigure
go
sp_configure 'Ad Hoc Distributed Queries', 1
go
reconfigure
go

exec master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
go
exec master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
go

Hope this helps someone else and saves them the long time I had to spend to fix the problem.

Thanks everyone! :slight_smile: