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 All,
I am facing the same issue after we have migrated to SQL 2008 R2 64 bit. Can you let me know if there are any other solution other than uninstall? I am worried as I am not sure how many places it will affect.
Is there any command or way so that same command can work ? There should be some way to handle below 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.
Regards,
Jatin Soni
Getting Same error ,after execution of the following statement
Error:MS Jet OLEDB 4.0 cannot be used for distributed queries because the provider is used to run in apartment mode.
sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Ad Hoc Distributed Queries’, 1;
GO
RECONFIGURE;
GO
Regards
Vamsik
Are you using 64 bit server? It wont work in 64 bit server
Thank you! Useful information!
I still get the same error after running the script.
The problem could be I am I am on 64bit Windows 7. OLE DB 4.0 is not compatible with 64 bit.
Check Whether “Microsoft.Jet.OLEDB.8.0” is reigtered in sql server machine?
Hi…I have SQL server 2008…when i try to export to excel I’m getting “MS Jet OLEDB 4.0 cannot be used for distributed queries because the provider is used to run in apartment mode.” error…As mentioned above i ran the below statements also…but still I’m getting the same error…can you pls help me to proceed…
sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Ad Hoc Distributed Queries’, 1;
GO
RECONFIGURE;
GO
Hi, i have installed AccessDatabaseEngine_x64.exe on 64bit machine and it worked fine.
After 120 days it will show the error like ‘Microsoft.ACE.OLEDB.12.0” provider is not registered on the local machine.
Please kindly help me.
i solved the problem. Thank you
how did u solved the problem?pls elobrate.
I am still having problem
I Run This Syntac
select * from
OPENROWSET
(‘Microsoft.Jet.OLEDB.4.0’, ‘Excel 8.0;
Database=D:Book1.xls;HDR=YES’,
‘select * from [Sheet1$]’)
and there is error
OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” returned message “Unspecified error”.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.
Thx
hi i am getting the below error after run this query
Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”.
sp_configure ‘show advanced options’, 1;RECONFIGURE;
go
sp_configure ‘Ad Hoc Distributed Queries’, 1;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
select * from OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’, ‘Excel 12.0;Database=D:\test\test.xls’, ‘SELECT * FROM [Sheet1$]’)
i am using SQL Server 2008R2 and excel 2007 for this operation.
can any one help me in this….
thanks in advance…
Hi Pinal,
I have followed your instruction but I am still facing same error:
sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Ad Hoc Distributed Queries’, 1;
GO
RECONFIGURE;
GO
SELECT * FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;Database=D:\book1.xls’, [Sheet1$])
Please provide ant alternate solution.
Thanks,
Jeetesh Garg
A few of our servers are running instances of 2005 and 2008. The 2005 runs fine using OPENROWSET in a VBS file to export a SQL table to a DBF file but the 2008 had the issue indicated.
I did not uninstall or reinstall anything. All I did was (from what I saw in the other posts):
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
VBS:
SELECT * FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’ …..
AND IT WORKS NOW….
I did this but I also had to install the AccessDatabaseEngine_x64.exe /passive in a cmd line with admin rights. However, it failed numerous times and I found a few posts where people had to download the exe twice before it would work (don’t ask). I had to download another copy of the exe before it would work. I sat in amazement as it installed after hours of tinkering. This was on Windows 7 x64 with 32bit office 2010 installed. Still haven’t got it running on the server. Got it to install (the second download) but I get the linked server null error now. More tinkering.
Got it to work on the server by starting SSMS as Administrator.
Should mention that I was using this query in SSMS, not VBS.
SELECT * FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’,
‘Excel 12.0 Xml;HDR=YES;Database=c:\test\excelfile.xlsx’,
‘SELECT * FROM [Sheet1$]’);
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “Microsoft.Jet.OLEDB.4.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.Jet.OLEDB.4.0” for linked server “(null)”.
Hi Pinal,
i got this error.
Msg 7308, Level 16, State 1, Line 3
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 the solution for this one.
i have used excel 2007 data is imported into sql server 2008.
Hi, I am also facing the same issue.
Can you please give me a better suggestion.
Msg 7403, Level 16, State 1, Line 5
The OLE DB provider “Microsoft.ACE.OLEDB.12.0” has not been registered.
Hi
I have a 64 bit server, but my MS Office version is 32 bit..therefore I get this 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.
I can not uninstall the 32 bit MS OFfice as suggested earlier, is there any other work around?
Thanks
Marlene
I am trying to import excel (.xls) file in to sql server 2008 through import Data wizard. However I am getting the follwing error –
0 down vote favorite
I am importind Data from excel sheet. I am struggling with the following problems –
Executing (Error) Messages Error 0xc020901c: Data Flow Task 1: There was an error with output column “Intelligence” (21) on output “Excel Source Output” (9). The column status returned was: “Text was truncated or one or more characters had no match in the target code page.”. (SQL Server Import and Export Wizard)
Error 0xc020902a: Data Flow Task 1: The “output column “Intelligence” (21)” failed because truncation occurred, and the truncation row disposition on “output column “Intelligence” (21)” specifies failure on truncation. A truncation error occurred on the specified object of the specified component. (SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component “Source – MainSheetData$” (1) returned error code 0xC020902A. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard)
Please help me in this.
This one solved this issue for me:
Grant rigths to TEMP directory
icacls C:\Windows\ServiceProfiles\\AppData\Local\Temp /grant :(R,W)The most commonly used pathes:
C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp
C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp
is a sql server restart required after chaning this configuration setting to take effect?
Restart is not required