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
I need to run Microsoft.Jet.OLEDB.4.0 on 64 bit sql server. Please provide the solution for this.
When i run this i get 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.”
SELECT * INTO TmpSaleData_For_DSR_Approved FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;Database=c:\DSR_Data\DSRSaleData-12-17-14.xls’, [DSRSaleData-12-17-14$])
after execute error is
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, Thank you very much for the useful info.
I have tried those steps in our local database server and it is working fine. I wanted to perform the same on one of our live database. But I wanted to turn off once I complete my comparison. Could you please let me know what commands to be executed to turn off this setting.
Thanks and Regards
Raju
sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Ad Hoc Distributed Queries’, 0;
GO
RECONFIGURE;
GO
Msg 7308, Level 16, State 1, Line 1
OLE DB provider ‘Microsoft.ACE.OLEDB.12.0’ cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
This is the error i m getting i tried everything you suggested but i am not able to rectify the root cause. Please help me
Hi raju ,
I am still facing this issue , kindly let me know the Steps that you followed to resolve this issue.
thanks in advance
Dinesh
Hi, Thank you for the information.
I daily read your blog which I received on my email and its make my interest more in SQL Server.
I have Windows Server 2012 64bit OS and SQL Server 2014.
Earlier it was giving me error about linked server so I installed Microsoft Access DB Engine 2010 64 bit and run through procedure and it was run smoothly. Even I am getting the excel file on define location. But the same thing when I am running through job then I am getting an error which I am mentioning below.
“NT SERVICE\SQLSERVERAGENT. Configuration option ‘show advanced options’ changed from 1 to 1. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457) Configuration option ‘Ad Hoc Distributed Queries’ changed from 1 to 1. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457) Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”. [SQLSTATE 42000] (Error 7303) OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” returned message “Unspecified error”. [SQLSTATE 01000] (Error 7412). The step failed.”
Please advice and help me to sort out the problem.
Thanks & Regards
Can anybody help on above post???
Hi, after brain storming and searching on net I got one of the below link and my problem get resolved.
I just altered SQLSERVERAGENT from Network Service to Local user and its work fine.
Thanks & Regards
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
Hi,
I have a requirement to copy the 6 months data from production server to development server for testing purpose. Records are so huge it approximately 6 lakhs. So what is the best way to proceed for the data copying? Is it the best way to use Import/Export wizard in SSMS?
Thank you and Mitch!
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.
not work :(
“Configuration option ‘show advanced options’ changed from 1 to 1. Run the RECONFIGURE statement to install.
Msg 5808, Level 16, State 1, Line 1
Ad hoc update to system catalogs is not supported.”
error
When i am run SQL query
if exists (select * from tempdb..sysobjects where name=’##monthTarget’)
drop table ##monthTarget
select * into ##monthTarget from OpenRowset(‘Microsoft.Jet.OLEDB.4.0′,’Excel 4.0;Database=C:\MonthTarget\AprTarget.xls’,[Sheet1$])
go
then error Occur
(2722 row(s) affected)
OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” returned message “Unspecified error”.
Msg 7303, Level 16, State 1, Line 6
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.
Msg 208, Level 16, State 0, Line 2
Invalid object name ‘##monthTarget’
Please advice and help me to sort out the problem.
Microsoft.Jet.OLEDB.4.0 is not supported for 64-bit OS :
https://stackoverflow.com/questions/22032222/ole-db-provider-microsoft-jet-oledb-4-0-cannot-be-used-for-distributed-queries
Here is my error message I get when trying to access Excel from the stored procedure running sql 2008.
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 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option ‘Ad Hoc Distributed Quearies’ does not exist, or it may be an advanced option.
Msg 5812, Level 14, State 1, Line 1
You do not have permission to run the RECONFIGURE statement.
Msg 15003, Level 16, State 1, Procedure sp_MSset_oledb_prop, Line 11
Only members of the setupadmin role can execute this stored procedure.
Msg 15003, Level 16, State 1, Procedure sp_MSset_oledb_prop, Line 11
Only members of the setupadmin role can execute this stored procedure.
Msg 7415, Level 16, State 1, Line 2
Ad hoc access to OLE DB provider ‘Microsoft.ACE.OLEDB.12.0’ has been denied. You must access this provider through a linked server.
I got this when i run the command :
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Configuration option ‘show advanced options’ changed from 1 to 1. Run the RECONFIGURE statement to install.
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78
The configuration option ‘Ad Hoc Distributed Queries’ does not exist, or it may be an advanced option.
Valid configuration options are:
I have already run follow your solution. but error still show “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.”
Please advice me to resolve it.