ad-hoc distributed query – errors

Types of microsoft.ace.oledb Driver Errors:

SELECT * FROM OPENDATASOURCE(‘Microsoft.ACE.OLEDB.12.0’, ‘Data Source=D:\tmp\a2.xlsx;Extended Properties=EXCEL 12.0’)…[Sheet1$];
OR
SELECT * FROM openrowset(‘Microsoft.ACE.OLEDB.12.0’, ‘EXCEL 12.0 Xml;Database=D:\tmp\a2.xlsx’,’select * from [Sheet1$]’);

Error -1:

The OLE DB provider “Microsoft.ACE.OLEDB.12.0” has not been registered.

Msg 7403, Level 16, State 1, Line 21

The OLE DB provider “Microsoft.ACE.OLEDB.12.0” has not been registered.

Solution :

Need to install Driver

Go to any search engine and Search “microsoft.ace.oledb.12.0 driver download”

Or

Go to  https://www.microsoft.com/en-in/download/details.aspx?id=13255

And download and install driver.

Error-2:

Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”.

OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” returned message “Unspecified error”.

Msg 7303, Level 16, State 1, Line 21

Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”.

Solution :

run following script:

EXECUTE master.dbo.sp_configure ‘show advanced options’,1;
GO
reconfigure ;
GO
EXECUTE master.dbo.sp_configure ‘Ad Hoc Distributed Queries’,1;
GO
reconfigure;
go
EXECUTE master.dbo.sp_configure ‘xp_cmdshell’,1;
GO
reconfigure;
GO
EXEC sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′, N’AllowInProcess’, 1
GO
EXEC sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′, N’DynamicParameters’, 1

Error-3 :

OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” returned message “Unspecified error”.

Msg 7303, Level 16, State 1, Line 21

Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”.

Solution :

If you have already installed microsoft.ace.oledb Driver but returning “Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0″ for linked server (null)”. when fire query then you need to check following steps :

1. Make sure you close the excel spreadsheet and run SSMS as admin.

2. Verify path At Registry Editor:

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

Below mention both entry must require at same path as you see above

1. AllowInProcess : value = 1
2. DynamicParameters : value =1

If not exists then need to run following script:

Exec sp_configure ‘show advanced options’, 1;
RECONFIGURE WITH OverRide;
GO
Exec sp_configure ‘Ad Hoc Distributed Queries’, 1;
RECONFIGURE WITH OverRide;
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