SQL SERVER – OLE DB Provider ‘Microsoft.ACE.OLEDB.12.0’ for Linked Server ‘(null)’ Returned Message ‘Unspecified Error’

SQL
2 Comments

SQL SERVER - OLE DB Provider 'Microsoft.ACE.OLEDB.12.0' for Linked Server '(null)' Returned Message 'Unspecified Error' oledberror There are many situations where you have to create a linked server to Microsoft Excel and read data from there. While doing that, I encountered an error and in this blog, we would discuss how to fix OLE DB provider ‘Microsoft.ACE.OLEDB.12.0’ for linked server ‘(null)’ returned message ‘Unspecified error’

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)’.

Solarwinds

SOLUTION/WORKAROUND

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:

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , , ,
Previous Post
SQL SERVER – Get List of the Logical and Physical Name of the Files in the Entire Database
Next Post
Data Privacy – Are Your Database Processes Fully Compliant?

Related Posts

2 Comments. Leave new

Leave a Reply

Menu