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.
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
Install it on your server.
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.
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;
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 had use
'DynamicParameters' instead of
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
Make sure you register msexcl40.dll like this:
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.
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
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.
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.