Import Excel Sheet into SQL Server Getting Error "The OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”"

1
votes

When i implement this code (Excel Sheet to Sql Server) Getting this Error.

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".

I done All the Below things,

  • sp_configure 'show advanced options', 1
  • reconfigure
  • sp_configure 'Ad Hoc Distributed Queries', 1
  • reconfigure

Also Install 2010 Office System Driver: Data Connectivity Components

Then also Same Problem.

1
c#wpfexcelsql-server-2008excel-2010
did you try this: stackoverflow.com/questions/20572563/… ? – Franco Fusaro

1 Answers

1
votes

I had the same issue due to incompatibility between access diver and SQL server. I couldn't even change permission of my system. Following is the solution that worked for me:

Pre-Condition:

  • Office products installed is 32-bit edition.
  • O/S is 64-bit.
  • SQL Server installed is 64-bit edition.

Issue:

  • Cannot execute following query:

SELECT * FROM OPENROWSET( 'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=YES;Database=C:\temp\Book.xlsx', 'select * from [Sheet1$]')

  • Following error will be displayed:
   OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Could not find installable ISAM.".
    Msg 7303, Level 16, State 1, Line 1
    Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
  • This is because 64-bit MS Access driver (Microsoft.ACE.OLEDB.xx.x) cannot read 32-bit Excel file.

Solution:

  1. Install 32-bit MS Access drive in passive mode in command prompt as follows:

access_drive_path>access_dirve_name.exe /passive

E.g.        C:\Users\user_name\Downloads>AccessDatabaseEngine.exe /passive

  1. Install 32-bit SQL Server (EXPRESS edition since it’s free).

  2. Enable Ad Hoc Distributed queries and dynamic parameters etc. by executing the following queries:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

USE [master] 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
  1. Now try to read Excel file in SQL query :)

One more thing.... make sure that the file you are trying read is not opened.