• Register
0 votes
189 views

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)".
6 5 3
6,930 points

2 Answers

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: https://www.microsoft.com/en-us/download/details.aspx?id=13255
  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; 
GO 
RECONFIGURE; 
SP_CONFIGURE 'Ad Hoc Distributed Queries', 1; 
GO 
RECONFIGURE; 
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
9 7 4
38,600 points
0 votes

Solution:

I have MS Sql server 2012, and Office 2013. This seems to be very finicky, so you may have to adjust to your particular versions.

  1. Download the Microsoft.ACE.OLEDB.12.0 for Windows, 64 bit version found here: https://www.microsoft.com/en-us/download/details.aspx?id=13255

  2. Install it on your server.

  3. 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's a local service account or C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp if it's a network service account.

  4.  Now, configure 'Ad Hoc Distributed Queries' and enable the Microsoft.ACE.OLEDB files :

Here's the SP_CONFIGURE commands:

SP_CONFIGURE 'show advanced options', 1; 
GO 
RECONFIGURE; 
SP_CONFIGURE 'Ad Hoc Distributed Queries', 1; 
GO 
RECONFIGURE; 
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 had use 'DynamicParameters' instead of 'DynamicParam'

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

Make sure you register msexcl40.dll like this:

regsvr32 C:\Windows\SysWOW64\msexcl40.dll

This provider is not released for 64 bit and so you require a 32 bit process to host it.  Your continuity appears to be a linked server scenario as well.  For putting the pieces together, you have the following scenario:

1.) 64 bit Windows Operating System
2.) 64 bit SQL Server installed
3.) Linked server to <insert office provider here>

Unfortunately, since there is no 64 bit provider, you cannot make linked servers directly to these data sources through (64 bit) SQL Server. These providers are not available, from any 64 bit process.

Option 1:

 You can use 32 bit SQL Server on the 64 bit machine.  It will  be able to see all the 32 bit providers.  Until provider vendors begin to support 64 bit, this offers the highest level of interactivity with a minimum of labor / maintenance cost.  But, it also comes with a performance penalty as SQL Server will be running in WoW mode on the 64 bit server

Option 2:

Build a bridge out of SQL Express.  Retain your main SQL Server example- 64 bit, however also install SQL Express 32 bit Side by Side.  Then when you reqyire to use 32 bit providers, make the linked server on the SQL Express example and link to that from the main instance.  Your linked server pathway would go SQL Server 64 bit -> SQL Server 32 bit (over 64 bit client) -> (32 bit client) for linked server X.  This has less performance cost, however more maintenance cost. Nevertheless no development costs, though, outside of configuring the linked servers.

Option 3:

Reverse the flow of data from pull to push.  Instead of having SQL Server pull data from the source that only supports 32 bit clients, push data from that source to SQL Server (as it supports 32/64 bit clients).  Example given-  instead of having SQL Server pull data from an access file, add macros in the access file to link to SQL Server and thrust the data up to the server.  Just don't do this one.  It is very expensive and makes it impossible to fully migrate, also often needs human intervention to perform the actual thrust.

10 6 4
31,120 points

Related questions

0 votes
1 answer 10 views
0 votes
1 answer 10 views
10 views
I am facing problem creating a jbdc driver of class. I want to connect it to url null.
asked Sep 22 Daniel Anderson 4k points
0 votes
1 answer 251 views
251 views
Problem : I am facing following error while executing script cannot insert explicit value for identity column in table when identity_insert is set to off.
asked Nov 15, 2019 peterlaw 6.9k points
0 votes
1 answer 155 views
155 views
Problem : My stored procedure giving me following error cannot perform an aggregate function on an expression containing an aggregate or a subquery.
asked Nov 15, 2019 peterlaw 6.9k points