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.
Problem statement
When trying to use Microsoft.ACE.OLEDB.12.0 provider in a linkedserver or distributed query you’ll get the above error.
Environment
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
T-SQL command
Use the below command to set the properties
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0'
, N'AllowInProcess', 1
GOEXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters', 1GO
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
:WindowsServiceProfilesNetworkServiceAppDataLocalTemp
and for local system account its:WindowsServiceProfilesLocalServiceAppDataLocalTemp
Right click on this folder and give it read write access to the account executing the code.
and for local system account its
Right click on this folder and give it read write access to the account executing the code.
Step 3: Check the MemToLeave memory area allocated
This was the step I took maximum time to understand and resolve.
Had to go through lots of blogs before I came across this helpful article from Nathan
http://nathondalton.wordpress.com/2010/04/01/sql-memory-and-external-data/
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.
Thanks, but no effect.
which is your SQLServer version?
Dear Visakh. Do you have an idea how to extract data from sql to an excel? Been trying to use the openrowdataset but I got the linked server errors. THnkas
Dear Visakh Did you have experience exporting data from sql to an excel? Been trying and I got the linked error servers error.
OPENROWSET doesnt require linked server connection.
What Excel driver have you installed?
ACE driver 32 bit or 64 bit?
See this to understand how you can set driver and use it for reading from Excel or writing to it
https://www.mssqltips.com/sqlservertip/6178/read-excel-file-in-sql-server-with-openrowset-or-opendatasource/