Programmers learn & share
0 votes

Problem :

I am trying to export from my Table data into Excel through T-SQL query but getting bellow error
cannot create an instance of ole db provider "microsoft.ace.oledb.12.0" for linked server "(null)".
by (6.9k points)   | 65 views

1 Answer

0 votes

Solution :

I had similar problem and I made it work for me.

To resolve the issue please follow bellow instructions very carefully

  1. Download the Microsoft.ACE.OLEDB.12.0 for Windows, 64 bit version from following link:
  2. Install it on your server.
  3. Please check the user running SQL Server and make sure that user has access to the temp directory C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp if it is a local service account or C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp if it is a network service account.
  4. Configure 'Ad Hoc Distributed Queries' and enable the Microsoft.ACE.OLEDB files as mentioned bellow:

Bellow are the SP_CONFIGURE commands:

SP_CONFIGURE 'show advanced options', 1; 
SP_CONFIGURE 'Ad Hoc Distributed Queries', 1; 
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1   
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParam', 1

On newer SQL Server 2014 You need to use 'DynamicParameters' instead of 'DynamicParam'

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

Please make sure that you register msexcl40.dll as mentioned bellow:

regsvr32 C:\Windows\SysWOW64\msexcl40.dll
by (36.1k points)  
2,183 questions
2,491 answers
241 users