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

81 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  29. 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?

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

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

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

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

  34. 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]

    • Done,

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

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

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

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

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

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

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

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

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

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

Leave a Reply

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

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 )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s