I recently got email from blog reader with following error.
MS Jet OLEDB 4.0 cannot be used for distributed queries because the provider is used to run in apartment mode.
The fix of the same is very easy.
Fix/Workaround/Resolution:
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Ad Hoc Distributed Queries', 1; GO RECONFIGURE; GO
If you are still facing the error after running above statement please leave a comment here and I will do my best to help you out.
Reference: Pinal Dave (https://blog.sqlauthority.com)
106 Comments. Leave new
Hi!
I have tried the above and now receive the following error:
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.
What is single-threaded apartment mode? What other apartment modes are there and what apartment mode must I use in order to run a distributed query? What makes my query distributed?
For background, I have created a linked server to an Excel (xls) file using Jet OLEDB 4.0 provider which has registered fine. When attempting to run a select statement against this, the error above appears.
Any insight would be greatly appreciated.
Joe
Are you using SQL Server version 2008?
I am, at least, and get the same error; how do you change apartment modes?
The issue is with 32 bit Excel running on 64 bit SQL Server. You will either have to install 64 bit Excel drivers or run SQL Server 32 bit.
Hi,
Still I am getting the same error.
Hello,
I am also getting the error mentioned in Joe O’Brien’s post when querying a linked server. The link is to an excel workbook.
I am running 32 bit office and 64 bit windows 7 and MSSQL 2008 R2.
Here is my addlinkserver command.
EXEC master.dbo.sp_addlinkedserver @server = N’TEST’, @srvproduct=N’Excel’, @provider=N’Microsoft.Jet.OLEDB.4.0′, @datasrc=N’C:\Users\test.xlsx’, @provstr=N’Excel 14.0′
From my initial readings online it appears that this might be an issue with the 64 bit version of windows I’m running. If I find more info I’ll post it.
Kind Regards,
Marc
Hi Dave,
I am trying to add a linked server connecting to a local Access database on my SQL Server (should I specfy using the UNC path or local path?). Everything I try results in a 7303 error which appears(?) to be related to authentication(?) As best as I can tell, I am instructing to use ‘Admin’ uid with no password for unsecured MS Access 2003 .mdb file. Any help is really appreciated.
-Jeff
Did you find a solution to …
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.
I have this on sql 2008 R2 dev and ent servers (10.50.1600.1 (X64)) and also 2008 standard (10.0.4000.0 (X64))
All 64 bit
Thanks
Dave
No. I have not had much time to devote to checking this out, but my 32-bit SQL2k5/Win2k3 has this issue…
The jet provider will not work in 64 bit platform
Getting Same error ,after execution of the following statement and also I have Restarted the Database server
please do the need full
sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Ad Hoc Distributed Queries’, 1;
GO
RECONFIGURE;
GO
Regards
Ajit
After many struggles with this issue, I found the following solution:
1. On 64-bit servers and boxes, you need to first UNINSTALL all 32-bit Microsoft Office applications and instances (Access 2007 install, Office 10 32-bit, etc.). If you dont, you cannot install the new 64-bit Microsoft Access Database Engine 2010 Redistributable components. Yes, its a headache but the only way I found to install the new replacements for the JET engine components that need to run on 64-bit machines.
2. Download and install the new component from Microsoft:
http://www.microsoft.com/en-us/download/details.aspx?id=13255
* This will install the access and other engines you need to set up linked servers, OPENROWSET excel files, etc.
3. Open up SQL Server and run the following:
sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Ad Hoc Distributed Queries’, 1;
GO
RECONFIGURE;
GO
EXEC master.dbo.sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′, N’AllowInProcess’, 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′, N’DynamicParameters’, 1
GO
* This sets the parameters needed to access and run queries related to the components. Address ‘null
4. Now, if you are running OPENROWSET calls you need to abandon calls ,made using the old JET parameters and use the new calls as follows:
(*Example, importing an EXCEL file directly into SQL):
DONT DO THIS….
SELECT * FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,’Excel 8.0;HDR=YES;Database=c:PATH_TO_YOUR_EXEXCEL_FILE.xls’,’select * from [sheet1$]’)
USE THIS INSTEAD…
SELECT * FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’, ‘Excel 12.0;Database=c:PATH_TO_YOUR_EXEXCEL_FILE.xls’,’select * from [sheet1$]’)
*At this point resolved two SQL issues and ran perfectly
5. Now for the fun part…..find all your Office Disks and reinstall Office and/or applications needed back onto the machine. You can install the 64- bit version of Office 10 by going onto the disk and going into the 64-bit folder and running it but beware as in some cases some third party apps dont interface yet with that version of Office.
Hope that help!
Mitch Stokely – Texas
Chief Internet Architect
Very good write-up; thank you!
Great post! This did the trick.
Thank you! It’s work!!
Thank you!
hi mitch, tried all step u have mentioned but no luck getting the same error as
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”.
Great!! solved it after struggling with it for a while
Excellent! it worked for me…. Thanks a lot…
Excellenet. Solved my issue.
However, no need to uninstall 32bit Office. we can install 64-bit Microsoft Access Database Engine 2010 Redistributable components using command prompt.
C:\AccessDatabaseEngine_x64.exe /passive
Thanks a lot again
This solved my issue. Thank you. Excellent advice and to the point!
Thank you for the help!
Mitch, I know this an old post but THANK YOU for taking the time to write your solution. I tried it and it worked perfectly for my 64-bit system. Up to this point I was still struggling with the old Jet connection which as you pointed out doesn’t work with 64-bit.
Peter
worked for me …
Awesome
Wow – that solved my problem! – thank you so much
Andy
I have to wonder about a solution that removes 32 bit properties. That presupposes that there is no longer a need for backward compatibility. If you are supporting users with only 64 bit machines, I suppose it is feasible.
Hello,
I’ve got the problem with the installation of the 64 bit driver with the 32 already installed. Because the 32 bit version is installed on the 64 server, my administrators are afraid to uninstal it and then the 64 bit version because the fear that the application relying on the 32 bit will not work anymore…
Is it thus safe to install the 64 bit driver instead of the 32 bit on a system that is using the 32 bit ?
Thanks
Tks a lot!
I resolve my problem!!! I used Sql 2005, but now I use Sql 2008 and my reports in .xls have stoped. Thank you!
Marcondes, Marcio – from Brazil!!!
Tks a lot!
You resolve my problem!!! I used Sql 2005, but now I use Sql 2008 and my reports in .xls have stoped. But now, that’s OK!!! Thank you!!!!
Marcondes, Marcio – from Brazil!!!
PA: Sorry, my english is bad…
Msg 15247, Level 16, State 1, Procedure sp_configure, Line 94
User does not have permission to perform this action.
Msg 5812, Level 14, State 1, Line 1
You do not have permission to run the RECONFIGURE statement.
Msg 15247, Level 16, State 1, Procedure sp_configure, Line 94
User does not have permission to perform this action.
Msg 5812, Level 14, State 1, Line 1
You do not have permission to run the RECONFIGURE statement.
I am getting the above msg
Hi,
this error is driving me crazy! I got a 64bits W7, Office 32bits and SQL Server 2008 R2.
I set the parameters AllowInProcess and DynamicParameters to 1 on Microsoft.ACE.OLEDB.12.0 and Microsfot.Jet providers. I also set ‘Ad Hoc Distributed Queries’ to 1
No matter which provider I use, I´m not able to get information from an excel file!
Of course, if I import the file with the import wizard or with SISS, it works just fine, but that´s not what I need.
Any tips would be useful!
Thanks for your help!
Unfortunately, Mitch Stokely instructions didn’t help me. I am still getting this error
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”.
Hi Naomi, I just stumbled across these issues myself and found that it was because I had the excel file open at the same time, I’m guessing it needed exclusive access.
Give bulkadmin permition to the database user
Hi I got the same problem but for reading DBFs (VFP)
select * from
openrowset(‘VFPOLEDB’,’C:Path’;”;
”,’SELECT * FROM table’)
the error is
OLE DB provider ‘VFPOLEDB’ cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
i did all this
sp_configure ‘show advanced options’, 1
reconfigure
go
sp_configure ‘Ad Hoc Distributed Queries’, 1
reconfigure
go
USE [master]
GO
EXEC master.dbo.sp_MSset_oledb_prop N’VFPOLEDB’, N’AllowInProcess’ , 1
GO
USE [master]
EXEC master.dbo.sp_MSset_oledb_prop N’VFPOLEDB’ , N’DynamicParameters’ , 1
GO
but still the same error.
i have win server 2008 r2 64bit, sql server 2008 64bit, and no office installed
please what else can i do
thanks a lot
To ferac.
Did you download and install http://www.microsoft.com/en-us/download/details.aspx?id=13255 ????
Can we see a Database made on visual foxpro by making a mirror on sql server 2008?