• Register
0 votes
395 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

Please log in or register to answer this question.

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
2 answers 14 views
14 views
Problem: I am trying to call OPENROWSET command in SQL server 2012 to read data from xls excel file below is my code SET @sql = 'SELECT * FROM [' + @Query + '$]' Exec( 'select * FROM OPENROWSET( ''Microsoft.Jet.OLEDB.4.0'' ,''Excel 16.0;Database=' ... EXEC sp_configure 'Ad Hoc Distributed Queries', 1 GO RECONFIGURE; GO but also not working . Also both sql and excel are 64 bit any other solution??
asked Apr 24 Yeamin 22k points
0 votes
1 answer 5 views
5 views
Problem: I am trying to access an excel sheet by creating a linked server in SQL Server 2012. Below is my error: Msg 7403, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered. How do I solve this issue?
asked Mar 20 ummesalma 29.2k points
0 votes
1 answer 11 views
11 views
Problem: Why I got the error > Cannot fetch a row from ole db provider "bulk" for linked server "(null)".
asked May 1 ummeshani 10.8k points
0 votes
1 answer 18 views
18 views
Problem: I need to import multiple files with a varying number of columns into SQL Server through a script. I am trying to use OPENROWSET for this. I have done the following. Installed driver on the client X64 PC. Execute below commands. EXEC sp_configure 'show advanced ... Level 16, State 1, Line 17 Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".
asked Apr 24 sumaiya simi 43.9k points
0 votes
1 answer 9 views
9 views
Problem: Please assist me in resolving this issue in The microsoft ace oledb 12.0 provider is not registered on the local machine 64 bit?
asked Apr 1 rakib1 51.5k points
0 votes
1 answer 5 views
5 views
Problem: Do you have any suggestions about how I can resolve this &ldquo;The microsoft.ace.oledb.12.0 provider is not registered on the local machine windows 8&rdquo;?
asked Mar 30 tuhin1 48.9k points
0 votes
1 answer 7 views
7 views
Problem: Please help me fix this dilemma &rdquo;The microsoft.ace.oledb.12.0 provider is not registered on the local machine windows 7&rdquo;?
asked Mar 30 tuhin1 48.9k points
0 votes
1 answer 8 views
8 views
Problem: Cannot fetch a row from OLE DB provider &ldquo;BULK&rdquo; for linked server (null).How to resolve the error?
asked Apr 17 sumaiya simi 43.9k points
0 votes
1 answer 3 views
3 views
Problem: Please assist me in resolving this issue: additional information: Provider 'microsoft.ace.oledb.12.0' local machine not registered.
asked Apr 7 rakib1 51.5k points
1 vote
1 answer 12 views
12 views
Problem: Please help me. How can I solve it? microsoft ace oledb 12.0 provider is not registered on the local machine
asked Mar 23 MUHAMMAD MUNEEB 83k points