Problem :
When trying to use Microsoft.ACE.OLEDB.12.0 provider in a linkedserver you'll get the error.
Cannot initialize the data source object of ole db provider "microsoft.ace.oledb.12.0" for linked server
Solution 1:
Step 1:
Enable below parameter
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
----------------------------------------------------------------------------------------------------
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
To cross check whether above parameter reflected or not
Connect to DB-- > Server objects --> Linked Servers --> Provider
Right click on [Microsoft.ACE.OLEDB.12.0] --> properties
Step 2:- Check whether user has permission on temp folder as provider uses temp folder while retrieving the data.
C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp
How to check permission :-
Right click on temp folder --->properties --- > securities.
Solution 2: This solution helped me to solve problem.
Step 1 : Enable all parameter from solution 1.
Step 2: Install Microsoft access database engine 2010 /2007 .
For 2010 : This does work for office 2012
For 2007 : This does work for office 2010
Step 3: I Kept below settings
Step 4: Run SSMS as Admin
That it :)
No comments:
Post a Comment