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?
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.
I installed the driver and it still gave me the same error. After so many days of hair pulling 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.
and searching far and wide, I finally found this, which made it work: