OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries
According to this thread,:
Microsoft.Jet.OLEDB.4.0 is not supported for 64-bit OS
Assuming you are running SQL Server 64-bit, you likely need the 64-bit Microsoft Access Database Engine 2010 Redistributable.
And be aware that there is a minor wrinkle when trying to install the software if the other version is already installed. In this case install the second version from the command line using the /passive switch. According to this thread:
Launching the install of a Microsoft ACE OLEDB Provider on a machine with an Office install other than the current one (e.g. 32 on 64) will cause the install to fail. To have it run properly you need to launch it from a command line with the “/passive” argument specified.
That is talking about an existing Office install but the same applies to coexisting database engine installations.
EDIT: Also make sure to use "Microsoft.ACE.OLEDB.12.0" not "Microsoft.Jet.OLEDB.4.0" for the provider string. (Props to @Rumi)
Cannot create an instance of OLE DB provider Microsoft.Jet.OLEDB.4.0 for linked server null
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.
- Configure 'Ad Hoc Distributed Queries' and enable the
Microsoft.ACE.OLEDB
files like this:
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
The OLE DB provider Microsoft.Jet.OLEDB.4.0 for linked server (null) reported an error
Try changing:
SET @ExcelString = 'Excel 8.0;Database=' + @fn
to:
SET @ExcelString = 'Excel 8.0;Data Source=' + @fn +';'
Not 100% sure on the trailing semi-colon so try it with and without
Related Topics
How to Check If an SQL Result Contains a Newline Character
Postgres - Aggregate Two Columns into One Item
Sql: Last_Value() Returns Wrong Result (But First_Value() Works Fine)
How to Rename an Index in MySQL
How to Get All Article Pages Under a Wikipedia Category and Its Sub-Categories
Update with Case and in - Oracle
SQL Server If Not Exists Usage
SQL Update Records with Row_Number()
Example of Three Valued Logic in SQL Server
Calling a Stored Procedure in Oracle with in and Out Parameters
Why Doesn't SQL Server Support Unsigned Datatype
How to Implement a Substring Search in SQL
Will a SQL Server Job Skip a Scheduled Run If It Is Already Running
How to Create a Conditional Where Clause
How to Solve "Either the Parameter @Objname Is Ambiguous or the Claimed @Objtype (Column) Is Wrong."