OLE DB provider "Microsoft.ACE.OLEDB.12.0" "Unspecified error".

HassanZaidi 21 Reputation points
2021-08-25T11:24:25.65+00:00

We are facing the following error while reading the excel file on our SQL instances using following query:

Declare @filePath varchar(max)='E:\New folder\ZCWTest.xlsx'
declare @qry varchar(max)

set @qry = 'Select col1,col2 FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0; Database=' + @filePath + ''',''select * from [sheet1$] '')'

exec (@qry)

and we are getting following error while executing the query:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
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)".

"

Please let us know what is the solution for this error.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,637 questions
Accepted answer
  1. CathyJi-MSFT 21,081 Reputation points Microsoft Vendor
    2021-08-27T10:06:57.873+00:00

    Hi @HassanZaidi,

    Please check below.

    1. Which account that SQL server service is running under? Suggest you using local system account, a admin account or an account that has the read and write permissions on this excel file.
    2. Please check that whether you have DisallowAdHocAccess registry key under.

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\Providers\Microsoft.ACE.OLEDB.12.0

    We then executed below command and key got created automatically. 

    USE [master]
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DisallowAdHocAccess', 0
GO

    If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.

    1 person found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 40,656 Reputation points
    2021-08-25T11:49:57.173+00:00

    set @qry = 'Select FROM OPENROWSET('

    SELECT and then empty, no column names, nothing; what should be selected? At least you should an asteriks.

    Is ACE data provider installed on the machine where SQL Server is running and that in the same 32/64 bitness as SQL Server?
    Do the SQL Server service account do have access permissions on the specified Excel file?

  2. Yitzhak Khabinsky 24,926 Reputation points
    2021-08-25T22:20:04.033+00:00

    Hi @HassanZaidi,

    The usual resolution(s) for the error, Msg 7303, Level 16, State 1, you are getting:

    1. Run SSMS as administrator
    2. Give full permissions on this folder:
      C:\Users\<SQL Server Service account name>\AppData\Local\Temp
      So the folder is c:\Users\MSSQLSERVER\AppData\Local\Temp C:\Windows\ServiceProfiles\<SQL Server Service account name>\AppData\Local\Temp
      On my machine SQL Server Service is running as NetworkService
      So the folder is C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp
    0 comments