OPENROWSET -OLEDB-Error

While trying to import data from an excel file we encountered error related to OLDEDB provider (Microsoft.ACE.OLEDB.12.0 &  Microsoft.ACE.OLEDB.15.0)

Error encountered on Microsoft SQL Server 2017 (RTM-CU5) (KB4092643) – 14.0.3023.8 (X64)

Edition : Developer Edition

 

OLE DB provider "Microsoft.ACE.OLEDB.15.0" for linked server "(null)" returned message "Could not find installable ISAM.".
Msg 7303, Level 16, State 1, Line 91
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.15.0" for linked server "(null)".

Below are the steps followed to resolve the issue.

1.Enable the below configuration settings 

sp_configure 'Ad Hoc Distributed Queries',1
RECONFIGURE 
go
sp_configure 'xp_cmdshell',1
RECONFIGURE


2. Try accessing the file with xp_cmdshell,this is to make sure SQL Server Service Account has access to the file location and file

exec master..xp_cmdshell 'dir D:\SQLServerQueryProjects\AzureStorageFiles\Files\AccountUploadTemplate_M.xlsx'

3. Execute sp_enum_oledb_providers , does the below show up in the result. If not you likely have the incorrect bit architecture for the provider

Microsoft.ACE.OLEDB.12.0
Microsoft.ACE.OLEDB.15.0

3.Set the OLEDB provider properties as below 

USE [master] 
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 

4. Now try accessing the file with OPENROWSET

select * FROM OPENROWSET
  (
    'Microsoft.ACE.OLEDB.12.0', 
    'Excel 12.0;Database=D:\SQLServerQueryProjects\AzureStorageFiles\Files\AccountUploadTemplate_M.xlsx;HDR=Yes', 
    'SELECT * FROM [final core spreadsheet$]'
  )

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *