by

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 (http://blog.SQLAuthority.com)

About these ads

69 thoughts on “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.”

  1. 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

    Reply

  3. 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

    Reply

  4. 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

    Reply

  5. 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

    Reply

  6. 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

    Reply

  7. 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/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d&displaylang=en
    * 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

    Reply

  9. 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.

    Reply

  10. 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

    Reply

  11. 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!!!

    Reply

  12. 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!!!

    Reply

  14. 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

    Reply

  15. 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!

    Reply

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

    Reply

    • 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.

      Reply

  18. 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

    Reply

  21. 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

    Reply

  22. 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

    Reply

  26. 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

    Reply

  27. 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.

    Reply

  30. 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

    Reply

  31. 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…

    Reply

  32. 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

    Reply

  33. 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….

    Reply

    • 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.

      Reply

        • 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$]‘);

          Reply

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

    Reply

  35. 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.

    Reply

  36. 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.

    Reply

  37. 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

    Reply

  38. 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.

    Reply

  39. 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

    Reply

  41. 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

  42. 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

  43. 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

  44. 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

  45. 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

  48. 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

    • Done,

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

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out / Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out / Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out / Change )

Cancel

Connecting to %s