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)".
Solution:
There are several solutions on this, but for us the below solution worked great.
Step 1:
sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
Step 2:
USE [master]
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
Step 3:
Full permissions on C:\Users\MSSQLSERVER\AppData\Local\Temp and C:Windows\ServiceProfiles\NetworkService\AppData\Local for windows nt groups
Thanks to:
http://stackoverflow.com/questions/26267224/the-ole-db-provider-microsoft-ace-oledb-12-0-for-linked-server-null