Importing data from Excel to SQL Server database using Microsoft.ACE.OLEDB.12.0

Question

I'm developing an application where I need to import data from Excel to an MS SQL Server database. I've installed Microsoft Access Database Engine 2010 Redistributable on the MS SQL Server machine as well as on my machine

Configured the MS SQL Server like this:

USE [master]
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
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

In my application I'm using:

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=PathToMyExcelDoc', [Devices$])

When the application and SQL Server are on the same machine, it works fine, but when I'm running the application on my machine and try to import data from Excel to a remote SQL Server, I get the following error:

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Sunny Tiwari · Accepted Answer · 2013-04-30 09:25:40Z

2

I have been facing same problem, I resolved it by first saving the excel to server and then passing that path in OPENROWSET.

answered Apr 30, 2013 at 9:25

Sunny Tiwari

751 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Stacked Over a year ago

So you mean: when I browse for an Excel file, then I must first upload it to the server where the SQL Server is, then perform importing data. If yes, how do I upload a file to a remote server in this case? I'm working on a WPF application (C#).

Sunny Tiwari Over a year ago

yes ofcourse..... actually i am still learning WPF with this part i might not be very helpful...

Stacked Over a year ago

Thank you, but what about the second part of my comment (how to upload file?).

Sunny Tiwari Over a year ago

This link may help you

Collectives™ on Stack Overflow

Importing data from Excel to SQL Server database using Microsoft.ACE.OLEDB.12.0

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related