How to solve Microsoft.ACE.OLEDB.12.0 error "Unspecified error"
The reason for writing this blog is for the last 2 hours I've been battling with the below error message
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)".
And finally managed to solve it!
Thought of sharing the steps I went through for others benefit.
When trying to use Microsoft.ACE.OLEDB.12.0 provider in a linkedserver or distributed query you'll get the above error.
Win 7/vista 32 bit, SQL Server 2008 R2 SP!
Steps to resolve
Check the following steps
Step 1: Check the In Process and Dynamic Provider options for the ACE provider
This can be done through T-SQL statement or through GUI
Connect to server instance through SQL Management Studio.
Expand Server -> Server Objects -> Linked Servers -> Providers -> Microsoft.ACE.OLEDB.12.0.
Right click choose properties.Select the Dynamic Provider and Allow inProcess properties as shown below
Step 2: Check the permissions on the Temp folder
This is needed because the provider uses the temp folder while retrieving the data. The folder can be one of the below based on whether you use a local system account or network domain account.
For network accounts, folder is :\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp and for local system account its :\Windows\ServiceProfiles\LocalService\AppData\Local\Temp
Right click on this folder and give it read write access to the account executing the code.
The setting can be applied as follows
Open SQL Server Configuration Manager -> Services -> SQLServer service.
Right click and choose properties.
Go to advanced tab and append -g512; to startup parameters property and it will resolve the issue.
This brought end to my 2 hour long struggle with the issue and i was able to get data from the file as shown below.