question

27873953 avatar image
0 Votes"
27873953 asked Cathyji-msft commented

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

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-generaldotnet-sqlclient
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @HassanZaidi,

We have not received a response from you. Did the reply(s) could help you? If the response helped, do "Accept Answer". If it is not work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

1 Vote 1 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered 27873953 commented

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

Olaf, we can tell that there was an asterisk originally, by the fact that the text switches to italic. Later it switches back to regular - this is were the second asterisk originally.

The posting window is not WYSIWIG, but uses Markdown, a very lightweight markup, but nevertheless it can affect what is posted when posters are not aware of it.

0 Votes 0 ·

Dear OlafHelper,
steric sign was there but it is not showing so I updated the description again. Can you please look it again.

0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.