• Register
0 votes
132 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.9k 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
36.1k 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.

17.7k points
edited by

Related questions

0 votes
1 answer 179 views
179 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 128 views
128 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
0 votes
1 answer 64 views
64 views
Problem : I am facing following error with the below query in SQL Server 2012. An expression of non-boolean type specified in a context where a condition is expected, near 'RETURN'. SQL Query: CREATE FUNCTION [dbo].[GetMYPMResources](@UserResourceNo ... JOIN JobTask t ON t.JobTaskNo = m.JobTaskNo WHERE t.TaskManagerNo = @UserResourceNo OR t.AlternateTaskManagerNo = @UserResourceNo ) RETURN END
asked Dec 4, 2019 alecxe 7.5k points
0 votes
1 answer 35 views
35 views
Problem: I am little bit confused, which statement is true? In sql server, execute the sp_columns command to list all the columns in a table? True False
asked Feb 19 maddi86 5.4k points