SQL SERVER – Fix: Error: MS Jet OLEDB 4.0 cannot be used for distributed queries because the provider is used to run in apartment mode.

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)

Solarwinds
,
Previous Post
SQL SERVER – Are you a Database Administrator or a Database Developer?
Next Post
SQL SERVER – Finding Last Backup Time for All Database

Related Posts

105 Comments. Leave new

  • Hi I already run the SPs, but now i’m getting this error
    “Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.”

    Can you help me?

    Reply
  • HI
    I’m 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.

    MY Query:
    SELECT * FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
    ‘Excel 8.0;Database=’,
    ‘SELECT * FROM [Sheet1$]’)

    OS-Windows 7 64 bit
    SQL server 2008 R2(64 bit)
    Excel 2007

    Reply
  • Installing 2010 AccessDatabaseEngine 64bit version worked for me.
    Once installed, you need to enable few provider options (Microsoft.ACE.OLEDB.12.0). You can follow Miss Alexander’s (above) or you can right-click on Microsoft.ACE.OLEDB.12.0 (under Linked Servers > Providers), click on Properties and check the boxes for Dynamic Parameters and Allow In Process.

    Reply
  • Got this to work (SQL 2005 to Access 2007):
    1) Installed AccessDatabaseEngine on dB server http://www.microsoft.com/en-us/download/details.aspx?id=13255
    2) Restarted SQL Server Instance
    3) Set Microsoft.Jet.OLEDB.4.0 Provider Options = 1
    “Allow InProcess” & “Dynamic Parameter”

    4) Created Link (Script Below)
    /****** Object: LinkedServer [AccessdB_Name_Referenced_Here] Script Date: 03/13/2013 10:52:00 ******/
    EXEC master.dbo.sp_addlinkedserver @server = N’AccessdB_Name_Referenced_Here’, @srvproduct=N’AccessDatabase’, @provider=N’Microsoft.Jet.OLEDB.4.0′, @datasrc=N’\\255.255.255.00\MyAccessdbPath\Access.mdb’
    /* For security reasons the linked server remote logins password is changed with ######## */
    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’AccessdB_Name_Referenced_Here’,@useself=N’False’,@locallogin=NULL,@rmtuser=N’UserID’,@rmtpassword=’########’

    GO
    EXEC master.dbo.sp_serveroption @server=N’AccessdB_Name_Referenced_Here’, @optname=N’collation compatible’, @optvalue=N’false’
    GO
    EXEC master.dbo.sp_serveroption @server=N’AccessdB_Name_Referenced_Here’, @optname=N’data access’, @optvalue=N’true’
    GO
    EXEC master.dbo.sp_serveroption @server=N’AccessdB_Name_Referenced_Here’, @optname=N’dist’, @optvalue=N’false’
    GO
    EXEC master.dbo.sp_serveroption @server=N’AccessdB_Name_Referenced_Here’, @optname=N’pub’, @optvalue=N’false’
    GO
    EXEC master.dbo.sp_serveroption @server=N’AccessdB_Name_Referenced_Here’, @optname=N’rpc’, @optvalue=N’true’
    GO
    EXEC master.dbo.sp_serveroption @server=N’AccessdB_Name_Referenced_Here’, @optname=N’rpc out’, @optvalue=N’false’
    GO
    EXEC master.dbo.sp_serveroption @server=N’AccessdB_Name_Referenced_Here’, @optname=N’sub’, @optvalue=N’false’
    GO
    EXEC master.dbo.sp_serveroption @server=N’AccessdB_Name_Referenced_Here’, @optname=N’connect timeout’, @optvalue=N’10’
    GO
    EXEC master.dbo.sp_serveroption @server=N’AccessdB_Name_Referenced_Here’, @optname=N’collation name’, @optvalue=null
    GO
    EXEC master.dbo.sp_serveroption @server=N’AccessdB_Name_Referenced_Here’, @optname=N’lazy schema validation’, @optvalue=N’false’
    GO
    EXEC master.dbo.sp_serveroption @server=N’AccessdB_Name_Referenced_Here’, @optname=N’query timeout’, @optvalue=N’30’
    GO
    EXEC master.dbo.sp_serveroption @server=N’AccessdB_Name_Referenced_Here’, @optname=N’use remote collation’, @optvalue=N’true’

    Reply
  • No need to uninstall the 32bit version, here’s a solution that worked for me:
    download both 32& 64 bit version and then in the command window run these one after the other:
    C:\Downloads> AccessDatabaseEngine.exe /passive
    C:\Downloads> AccessDatabaseEngine_x64.exe /passive

    and follow the rest………

    Reply
  • If you are using 64Bit SQL Server then instead of using “Microsoft.Jet.OLEDB.4.0” use “Microsoft.ACE.OLEDB.12.0” and your problems will be solved as Jet provider does not have 64 bits version, you can check with tlist.exe command (in the windbg bin directory, windbg can be downloaded from to see whether wow mode Jet provider is used or not.

    Reply
  • Cristina Perez Otaiza
    May 4, 2013 6:08 am

    Hi Sagar,
    Could you please provide more details about how to get the tlist.exe?
    I am just looking to install the minimum of that packet but it can allow me to used it..
    Regards,

    Cristina

    Reply
  • i try to connect to oracle 10g from Server 2008 at windows 2008 , 64 bit
    1)- oracle client installed.
    2)- ODBC installed.
    3)-System DNS is working fine and pass testing.
    4)- SP link created .. fail to reterive data

    the errors:-
    OLE DB provider ‘MSDAORA’ cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode. (.Net SqlClient Data Provider)

    br
    Abuzaid
    [email removed]

    Reply
    • Artur Fonseca
      June 7, 2013 5:02 pm

      Done,

      select * from
      OPENROWSET(
      ‘Microsoft.ACE.OLEDB.12.0’,
      ‘Excel 12.0 Xml;Database=E:\Cosec.xlsx’,
      ‘SELECT * FROM [folha1$]’)

      Reply
  • Stanisław Pachacz
    July 8, 2013 3:11 am

    It solved the problem. Thank you

    Reply
  • Setting those options did not solve the problem (the problem is with Microsoft.Jet.OLEDB provider and with import from Excel file).

    Reply
  • Hello

    I’m also facing this problem. I’m using MSSQL SERVER 2008 on win XP 32 bit. I already tried all the tips found here and on other forums. SQLSRVER is running under LOCAL SYTSEM account to avoid issues related with TEMP folder access.

    I’m trying to run querry :

    select top 1 *
    from OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’, ‘Excel 12.0;Database=C:\RAPORTY\php_raporty\all_daily\bedzin\CEP2.xlsx;HDR=NO;IMEX=1’, ‘SELECT * FROM [Arkusz1$]’)

    Error Msg:

    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)”.

    What is strange sometimes after service restart randomly it works fine. I would be rateful for some final solution. I cannot restart service everyday :) What is more i’m not able to locate problem source. I think that this has something to do with sql user rights however it works sometimes (random).

    Thnak you

    Reply
  • Am Facing Still this problem, I followed below step, and at step 5 problem occure. plz help…..
    1 – sp_configure ‘show advanced options’, 1;
    msg : Configuration option ‘show advanced options’ changed from 1 to 1. Run the RECONFIGURE statement to install.

    2- RECONFIGURE;
    Msg : Command(s) completed successfully.

    3- sp_configure ‘Ad Hoc Distributed Queries’, 1;

    Msg: Configuration option ‘Ad Hoc Distributed Queries’ changed from 1 to 1. Run the RECONFIGURE statement to install.

    4- RECONFIGURE;
    Msg : Command(s) completed successfully.

    problem occure here

    5-
    insert into P3RESA08

    select * from OPENROWSET

    (‘MICROSOFT.JET.OLEDB.4.0′,’dBase IV;HDR=NO;IMEX=2;

    DATABASE=Path_of_Folder’,’select * from D:\P3RESA08.DBF’)

    Msg : 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.

    Reply
  • i am getting error like:
    OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” returned message “External table is not in the expected format.”.
    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)”.

    Reply
  • Ravindra Walde
    October 29, 2013 5:07 pm

    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 am faced this after executing following code

    EXEC sp_configure ‘show advanced options’, 1;
    GO
    RECONFIGURE;
    GO
    EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1;
    GO
    RECONFIGURE;
    GO

    EXEC Try.dbo.sp_MSset_oledb_prop N’Microsoft.Jet.OLEDB.4.0′, N’AllowInProcess’,1
    GO
    EXEC Try.dbo.sp_MSset_oledb_prop N’Microsoft.Jet.OLEDB.4.0′, N’DynamicParameters’,1
    GO
    USE [Try];
    GO
    INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0’, ‘Excel 8.0;Database=c:\contact.xls;’,
    ‘SELECT * FROM [Sheet1$]’)
    SELECT TOP 5 FirstName, LastName
    FROM MyName
    GO

    help me

    Reply
  • Debayan Samaddar
    November 11, 2013 11:11 am

    this thread is undoubtedly very much useful. It works fine for excel data. but not working for Access Database. i’ve gone through all the procedure discussed above.
    use master
    Go

    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

    Select *
    FROM OpenDataSource(‘Microsoft.ACE.OLEDB.12.0′,’Data Source=C:\NITGENDBAC.mdb;
    User ID=Admin;Password=nac3000;’)…[NGAC_LOG];

    error :
    OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” returned message “Cannot start your application. The workgroup information file is missing or opened exclusively by another user.”.
    Msg 7399, Level 16, State 1, Line 2
    The OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” reported an error. Authentication failed.
    Msg 7303, Level 16, State 1, Line 2
    Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”.

    Please help me in this regard.

    Reply
  • I am able to get ace oledb 12 to work – as long as the files are on the sql server — but I cannot get to work if files are on a remote server and using an unc such as \\server\share\file.xls , even though I granted the server network service full access to the share, can this be done?

    Reply
  • I ran this on my SQL Server and it appears that it is causing other undesirable issues. How do I put it back the way it was?

    Reply
  • I am having serious problem importing Data from Excel into SQL Server 2008 R2
    the code look like this:

    USE databaseName;
    GO
    sp_configure ‘show advanced options’, 1;
    GO
    RECONFIGURE WITH OverRide;
    GO
    sp_configure ‘Ad Hoc Distributed Queries’, 1;
    GO
    RECONFIGURE WITH OverRide;
    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
    — simple insertion query from Excel OLEDB OLEDB Link server
    Insert INTO dbo.temppartners(……………….)
    SELECT * FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’, ‘Excel 12.0;Database=C:\math\Book1.xls’,’SELECT * FROM [Sheet1$]’)

    ——————————————————————————————————————————–

    here is the message

    Configuration option ‘show advanced options’ changed from 1 to 1. Run the RECONFIGURE statement to install.
    Configuration option ‘Ad Hoc Distributed Queries’ changed from 1 to 1. Run the RECONFIGURE statement to install.
    OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” returned message “The Microsoft Access database engine could not find the object ‘Sheet1$’. Make sure the object exists and that you spell its name and the path name correctly. If ‘Sheet1$’ is not a local object, check your network connection or contact the server administrator.”.

    Msg 7350, Level 16, State 2, Line 3
    Cannot get the column information from OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”.

    Reply
  • Hi Pinal,

    I tried the workaround given below but still getting error on executing below query.
    i am trying to put records from table to .DBF (Foxbase) database file.

    INSERT OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,’Text;Database=D:’,NIKEPR#dbf)
    SELECT *
    FROM [HumanResources].[Employee];

    Error received:
    Msg 7302, Level 16, State 1, Line 1
    Cannot create an instance of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.

    Appreciate your inputs on it. Thanks.

    Reply
  • I have sql server 2008 64bit Developer’s Edition Installed on my machine.

    And Microsoft Office 2013 Professional 64bit.

    I have been trying to import and export some Excel data from an Excel sheet into a sql server database. I have used the following query to do this:

    Query

    sp_configure ‘show advanced options’, 1;
    GO
    RECONFIGURE;
    GO
    sp_configure ‘Ad Hoc Distributed Queries’, 1;
    GO
    RECONFIGURE;
    GO

    USE [ERPInventorySystem];
    GO
    SELECT *FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’, ‘Excel 12.0;Database=C:\Users\pratiks\Downloads\contact.xls;’,
    ‘SELECT * FROM [Sheet1$]’)

    GO

    Yet every time I execute the select statement to get data from Excel sheet I get the following error :-
    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.

    Reply

Leave a Reply

Menu