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)
Related videos on Youtube
Pரதீப்
My original name is Thanga Pradeep and I am a C#/Sql Server Developer Best answer I ever wrote in SO(based on complexity not on votes) Calculation in Sql Server Some of my answers I enjoyed writing Simplify MS SQL Statements Generate a sequnce number for every 3 rows in SQL Difference between filtering queries in JOIN and WHERE? How to drop all tables from a database with one SQL query? SQL Server Coalesce data set Also 44th to get Sql Server Gold Badge I learned many things from below Sql Server gurus Aaron Bertrand Marc_S Martin Smith gbn
Updated on August 11, 2020Comments
-
Pரதீப் over 3 years
I want to import data from Excel to SQL Server using queries, not by using a wizard. I tried this query:
Select * INTO g FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 12.0;Database=D:\new.xlsx;HDR=YES', 'SELECT * FROM [newSheet$]');
But, I am getting this error:
Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.So I searched on Google, and I got answers like:
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Ad Hoc Distributed Queries', 1; GO RECONFIGURE; GO
Even after reconfiguring it is showing me the same error...
-
agentnega about 10 yearsThis may not be a solution but you seem to have a mismatch in your connection string. For
.xlsx
files withExcel 12.0
you should be usingMicrosoft.ACE.OLEDB.12.0
.Microsoft.Jet.OLEDB.4.0
would be specified withExcel 8.0
but that only supports.xls
files from Excel 97-2003. -
Pரதீப் about 10 years@agentnega I tried changing excel file to .xls format after that also am getting same error
-
agentnega about 10 yearsOops this is a duplicate of stackoverflow.com/questions/16605371/… which is also a duplicate of stackoverflow.com/questions/9687631/openrowset-with-excel-file. See those questions for possible answers.
-
gvee about 10 years
EXEC sp_MSset_oledb_prop 'Microsoft.ACE.OLEDB.12.0', 'AllowInProcess' , 1; EXEC sp_MSset_oledb_prop 'Microsoft.ACE.OLEDB.12.0', 'DynamicParameters', 1;
-
-
Vackup about 9 yearsYou have full and detailed instructions here excel-sql-server.com/…
-
Rumi over 8 yearsThis works but you have to change 'Microsoft.Jet.OLEDB.4.0' to 'Microsoft.ACE.OLEDB.12.0'. Maybe you should put this inside the answer.