Below query can read data from excel in a shared location.
SELECT * FROM OPENROWSET( 'Microsoft.ACE.OLEDB.12.0' ,'Excel 12.0;Database=\\\\FileServer\\ExcelShare\\HRMSDATA.xlsx;HDR=YES;IMEX=1' ,'SELECT * FROM [EMPMASTER$]')
This was failing with error:
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)’.
Based on my search on the internet, we were not seeing 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
After this, we were able to read data from excel without any error.
If you find some other solution, please share via comments.
Here are a few other blog post related to the same subject:
- SQL SERVER – Quickest Way to Add LoopBack Linked Server for OpenQuery
- SQL SERVER – FIX – Linked Server Error 7399 Invalid authorization specification
- SQL SERVER – Linked Server Error: TCP Provider: No Connection Could be Made Because the Target Machine Actively Refused It
- SQL SERVER – How to Create Linked Server to SQL Azure Database?
Reference: Pinal Dave (https://blog.sqlauthority.com)
am getting an error as The system cannot find the file specified.
Getting same error. The system cannot find the file specified.
You will receive the error RegDeleteValue() returned error 2, ‘The system cannot find the file specified.’
Msg 22001, Level 1, State 1 because that registry entry does not exist, so cannot be deleted by the RegDeleteValue() call. If it was there, the call would succeed and the entry would be deleted, which is what setting it to 0 does.