none
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered

    Question

  • Hi All,

    I am getting the below error while executing the opendatasource statement

    SELECT ParamName, ParamDate,ParamNumber,ParamChar

    FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

    'Data Source= c:\testcmd\misc\CSTARIntegrationParameters.xls;Extended properties=Excel 8.0')...[CSTARIntegrationParameters$]

    Error :

    The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered

    Enviromnet :

    Windows 2003 64 bit with SP1 and sql 2005 installed.

    Same code is executing fine in my local machine which is XP 32 bit.

    Can some one help me in resolving this issue.

    Also when I tried to check the providers by expanding the linked servers node in the SQL server management studio . i couldnt found the provider for jet.

     

    Regards,

     

     

     

     

     

    Tuesday, February 13, 2007 7:20 AM

Answers

All replies

  • 64-bit SQL Server cannot use Jet, there is no 64-bit OLEDB provider for Jet.
    Currently there is no plan to write one either, but this could change based on demand.
    Tuesday, February 13, 2007 5:44 PM
  • Hi,

     

    Is there is any work around so that I can still import spreadsheets on my 64 bit server using opendatasource method.

     

    thanks again

    regards

     

    Tuesday, February 13, 2007 8:23 PM
  • As far as I know, there are no workarounds short of running a 32-bit instance of SQL 2005 on the machine, but I'll double check with the DQ folks and let you know shortly.
    Tuesday, February 13, 2007 8:56 PM
  • I found this -> http://en.wikipedia.org/wiki/Microsoft_Jet_Database_Engine

    The Jet Database Engine will remain 32-bit only for the forseeable future. Microsoft has no plans to natively support Jet under 64-bit versions of Windows. This means that native 64-bit applications (such as the 64-bit versions of SQL Server) cannot access data stored in MDB files through ODBC, OLE DB, or any other means, except through 32-bit software Access database from SQL 2005/64).

    Check out this link ->

    http://gorm-braarvig.blogspot.com/2005/11/access-database-from-sql-200564.html

    This is the best solution I have found thus far.

    • Proposed as answer by Gorm Braarvig Wednesday, November 04, 2009 3:11 PM
    Tuesday, February 13, 2007 10:29 PM
  • So, are you saying that if we are running a 64 bit instance of SQL 2005 we cannot import data using Excel files?

    Wednesday, April 16, 2008 1:55 PM
  •  

    So, are you saying that if we are running a 64 bit instance of SQL 2005 we cannot import data using Excel files?

    Tuesday, April 29, 2008 2:26 PM
  • I also read:

     

    You can import it with SSIS if you set the executable to 32 bit in the project properties in BIDS.

     

    So 64-bit SQL Server 2005 you can use SSIS and set the executable to be 32-bit and it will allow import.

     

    Have not tried this but it sounds reasonable to me.

     

    Tuesday, April 29, 2008 4:28 PM
  • Just to reiterate someone else's question...

    If I went out and bought a very expensive server, and put microsoft's very expensive software on it, I cannot import excel files using the MS.Jet.OLEDB.4.0?

    And your best solution is to install another server that i can then link to???  Are you serious.  Granted I'm in a bad mood, but this is why people don't like microsoft.

    How would you go about "import it with SSIS if you set the executable to 32 bit in the project properties in BIDS" 

    I have not found this setting.

    I know I sound pretty bitter, but please respond, this is kinda a big deal to me.

    Thanks in advance.


    Tuesday, May 06, 2008 7:44 PM
  • Oh help, seems like I'm having a similar problem.

     

    Please treat me as a complete muppet - I don't know where to start.

     

    I wrote some code on a computer with 32 bit OS, compiled and ran it on a computer with 32 bit OS, now I need to make some minor alterations to the program, however, in the mean time I've "upgraded" the computer with visual studio on it to a 64 bit OS.  The program is still to be run a computer with 32 bit OS (not my computer).

     

    I've just opened the program and tried to run it, and I get errors because of MS.Jet.OLEDB.4.0 but I think my problem may be more deep seated - if I do compile here (even if I didn't have problems because of the above) my program won't work on a 32 bit OS will it?

     

    Any help greatfully received.  I think I just want to run Visual Studio in 32 bit mode (but don't know if it is possible) any help greatfully received.

     

    Thanks

     

    Tuesday, June 17, 2008 2:33 PM
  •  Matt Neerincx - MSFT wrote:
    64-bit SQL Server cannot use Jet, there is no 64-bit OLEDB provider for Jet.
    Currently there is no plan to write one either, but this could change based on demand.


    Yes, I think there is demand.  It has certainly forced me to use annoying work arounds personally.
    Wednesday, September 24, 2008 12:04 AM
  • I agree.  I'm now running my primary amd64 4 proc and I find things that don't work on 64-bit very irritating.

     

    For example Adobe Flash does not work on 64-bit, this is really annoying.

     

    I've forwarded your feedback to the Office team, they own the driver and I expect they will have more and more demand for 64-bit drivers as more and more people start using 64-bit personal machines. 

    Wednesday, September 24, 2008 12:36 AM
  • Same here.  Is there still no 64bit support??  Incredible!  There is demand!!!
    Wednesday, March 18, 2009 1:29 PM
  • Same problem.

    My applications use "OpenRowSet" statements with "Microsoft.Jet.OLEDB.4.0" to get information from "Microsoft Access" databases, if the SQL Server is 64-bit I get "The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered".

    Microsoft is not really planning a Jet provider for 64-bit?

    Sérgio
    Monday, March 30, 2009 11:56 AM
  • I am absolutely speechless.

    I use this functionality to export to Excel. Just tried installing on 64bit server and came across this when it all went sunny side up.

    Well, this is a real incentive to upgrade to 64 bit and no mistake. You can well imagine what I will be recommending to all my clients.
    Wednesday, May 13, 2009 7:04 PM
  • What if you're getting the same error and you're using 32 bit sql 2005 and an 32 bit xp machine?

    The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered
    Thursday, August 06, 2009 3:06 AM
  • Never had that. Sounds like the install may not be complete. I'd be tempted to reinstall office and make sure absolutely everything is installed. Could be a rights issue though. Need a SQL guru with a few more medals!
    Thursday, August 06, 2009 5:15 PM
  • Have you guys tried to export tables from SQL Server 64 bit database to excel, it works perfectly. So, why can't we export to Excel from query. There must be some work around instead of using 32 bit Microsoft.Jet.OLEDB.4.0.


    Thursday, September 17, 2009 1:51 AM
  • Import / export works fine.

    However, when you export / import from a query you are treating the spreadsheet as another database. Excel isn't a database but it uses the jet engine to appear as one. For some reason the 64bit of SQL server doesn't have the capability to talk to this database software, although the 32bit version does.

    Only fixes I've mangaged to find are:

    Install 32bit SQL Express on same machine and link through that to Excel spreadsheet. Works well.

    SISS packages.

    If you find another way then let me know!
    Friday, September 18, 2009 11:48 AM
  • Hi Guys

    The work around we have done is run all our ssis jobs from a central SSIS server (32 bit) pushing and pull data in and out of the 64 bit servers. 
    Monday, September 21, 2009 6:56 PM
  • Matt, Jet runs in Enterprise Server in 64-bit mode.  Keep forwarding these comments!  Nearly all of the new desktops being built for Windows 7 are built around the x64 edition.  I sympathize with the desire for us all to use SQL Server, but backwards compatibility is mission-critical at times, and omitting an (already existing) 64-bit version of Jet from Windows was a puzzling decision.

    Geez, I hollered REAL loud for it throughout the beta-test periods for X64 in XP, Vista and 7.  A lot of us did!  How many requests do you think it will take?

    Tinker
    Tuesday, December 01, 2009 5:28 PM
  • Don't think it matters how many requests there are. it is one of those 'executive' decisions. I suspect that although the functionality was incredibly useful it was probably stuffed full of security issues / historic bugs and maintaining it was becoming burdensome.

    Who knows?

    I'd still like it on 64bit if anyone is listening!
    Tuesday, December 01, 2009 11:14 PM
  • Dont know if this helps anyone, but you can build a console app, set the platform target to x86 in the build properties, write your jet provider code in the console app (see below) and then put the executable on your 64-bit server. Then just run the .exe from your 64-bit environment to do the excel import.

     If you don't specifically set the platform target it will still throw the jet provider error.

    static void Main(string[] args)
            {
                try
                {
                    using (OleDbConnection conn = new OleDbConnection(excelConnectionString))
                    {
                        conn.Open();
                        using (OleDbCommand oleCmd = new OleDbCommand("select * from [Test$]", conn))
                        {
                            using (OleDbDataReader reader = oleCmd.ExecuteReader())
                            {
                                InsertData("TableName", reader);
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    Console.ReadKey();
                }
    
    
            }
    
            static void InsertData(string tableName, IDataReader reader)
            {
                SqlConnection destination = new SqlConnection(sqlConnectionString);
                SqlCommand cmd = new SqlCommand("DELETE FROM " + tableName, destination);
                destination.Open();
                cmd.ExecuteNonQuery();
                // Create SqlBulkCopy
                SqlBulkCopy bulkData = new SqlBulkCopy(destination);
                // Set destination table name
                bulkData.DestinationTableName = tableName;
                // Write data
                bulkData.WriteToServer(reader);
                // Close objects
                bulkData.Close();
                destination.Close();
    
            }

    Jason
    • Edited by Jason Gerstorff Thursday, December 03, 2009 7:19 PM Clarification
    Thursday, December 03, 2009 7:11 PM
  • Erm, you could be right. However, isn't this pretty much the same as using SQL Express? Effectively we are using SQL express as our app. Or am I missing something?
    Thursday, December 03, 2009 8:46 PM
  • I don't think there's any way to get around using some 32 bit solution. To me a console app is simpler than bringing another server into the picture if it meets your needs. But no, i wouldn't replace what your already doing if you've got something working. Just a different approach for anyone else who has the same problem.
    Jason
    Friday, December 04, 2009 2:37 AM
  • Seriously, this is worth sticking with 32 bit SQL Server.

    Runs slower?  Uses memory more ineficiently? 

    buy a bigger box, throw more hardware at at.

    Loosing out on the benifit of 64 bit is worth it for the interegration with Access and Excel most all small businesses are built on.

    wth were they thinking.


    Wednesday, December 30, 2009 6:05 PM
  • Indeed. Big businesses too when you're want a quick method of sucking data in.

    Only thing to watch out for is that sometimes it suffers from the same issues that the import / export wizard in Management Studio sometimes suffers from. I've imported from spreadsheets using this where the first value has been null in a column and it has switched all values in that column to null. Weird.
    Wednesday, December 30, 2009 10:20 PM
  • Is it true? My server is 64 bit and the following worked for me. Execute the following script and restart the MSSQLSERVER service.

    EXEC

    sp_configure 'show advanced options', 1;

    GO

    RECONFIGURE

    ;

    GO

    EXEC

    sp_configure 'Ad Hoc Distributed Queries', 1

    GO

    RECONFIGURE

    ;

    GO

    Monday, February 08, 2010 8:33 PM
  • Are you using the 32bit SQL Express version on 64bit or 64bit Enterprise edition?

    Have you actually tried using the jet engine via OPENROWSET or similar?
    Wednesday, February 10, 2010 11:31 PM
  • Ok, Now its Clear that 64 bit does not support Linking Acess DB. Is there a alternative way with out installing 32 bit sql express. Please Help

    Wednesday, March 17, 2010 1:05 PM
  • Hi Guys

    The work around we have done is run all our ssis jobs from a central SSIS server (32 bit) pushing and pull data in and out of the 64 bit servers. 

    In Visual Studio (2008 version at least) there is an option somewhere to tie into Excel as 32bit and then it works fine. Cannot remember where it is off the top of my head but there should be no reason to use 32bit SISS.
    Saturday, April 03, 2010 6:46 PM
  • I've not tried this but I'd be interested if anyone has and got it to work:

    http://ellisweb.net/2010/01/connecting-to-excel-and-access-files-using-net-on-a-64-bit-server/

    Anyone?

    Saturday, April 03, 2010 6:48 PM
  • There is an 64-bit ACE provider for Office 2010. It is still under Beta review.

    Please try it out at: http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en

    With this new provider, the 64-bit SQL Server should be able to link with the Access data source easily.

    Thanks,
    Ming.
    WDAC Team, Microsoft.


    Pak-Ming Cheung - MSFT
    Wednesday, April 07, 2010 3:05 PM
    Answerer
  • Fantastic. Could you give an example of the syntax required with OPENROWSET or OPENDATASOURCE?

    Cheers,

    JCEH

    Friday, April 09, 2010 9:07 PM
  • No doubt, importing with SSIS is possible but you must set the executable to 32bit in the project properties in BIDS.  

    So with SQL Server 64-bit 2005 version you can use SSIS and set the executable to be 32-bit and it will allow import.

     

    Have you tried this?  What was the result?

     

    Tim Macking MCSE, MCDBA, MCT

    Zander Technical Consulting


    Tim Macking MCSE, MCSA, MCDBA, CCNA
    Monday, April 12, 2010 1:30 AM
  • Yes, I've tried on some systems (64bit SQL 2008 over Windows Server 2008) and it worked fine.

    To be fair I had plenty of hardware to play with so not sure what a lower spec with a decent amount of data will handle performance-wise but cannot see any problems.

    J

    Monday, April 12, 2010 9:17 PM
  • Please take a look at the page:
    OPENROWSET: http://msdn.microsoft.com/en-us/library/ms190312.aspx
    OPENDATASOURCE: http://msdn.microsoft.com/en-us/library/ms179856.aspx

    They contain both syntax and examples.

    Thanks,
    Ming.
    WDAC Team, Microsoft.
    PS. People are usually ignoring a thread that was marked as "answered". Therefore, it is better to start a new thread for a separate question (even if they are related).


    Pak-Ming Cheung - MSFT
    Thursday, April 15, 2010 11:53 PM
    Answerer
  • Thanks Ming.

    I am aware of the general syntax, what I was really after was the specific nomenclature for the ACE provider. I assume the provider will no longer be 'Microsoft.Jet.OLEDB.4.0'. So what's the new one?

    Cheers,

    J

    PS. Will start a new thread for this as well.

    Friday, April 16, 2010 8:21 PM
  • For syntax see: http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/36f8e8a7-c87d-4660-8092-719f73c26f4a/?prof=required

    Note: installation of driver requires uninstalling all 32bit Office versions and re-installing with the 64bit versions first.

    So, think a solution is on the horison but will take clients a while to catch up.

     

    Tuesday, April 20, 2010 2:24 PM
  • Yes I agree there is demand, but this thread is 3.5 years old, is anyone a MS listening??, doesn't look like it.
    Sunday, October 03, 2010 4:58 PM
  • Thursday, November 11, 2010 12:01 AM
  • This one definitely worked for me for both MSExcel 2003 and 2007.

    SELECT

     

    *

    FROM

     

    OPENROWSET('Microsoft.ACE.OLEDB.12.0',

     

    'Excel 12.0;Database=C:\FileName.xls;HDR=YES',

     

    'SELECT * FROM [Sheet1$]')

     

     

     

    • Proposed as answer by Scott D Duncan Sunday, September 18, 2011 7:00 PM
    Thursday, March 03, 2011 6:17 AM
  • This one works

    SELECT

     

    *

    FROM

     

    OPENROWSET('Microsoft.ACE.OLEDB.12.0',

     

    'Excel 12.0;Database=C:\FileName.xls;HDR=YES',

     

    'SELECT * FROM [Sheet1$]')

    Change the provider name...

    Thursday, March 03, 2011 6:50 AM
  • I believe the whole issue is fixed in SQL Server 2010 and Office 10.

    J

    • Proposed as answer by SHAJ84 Wednesday, June 20, 2012 2:39 PM
    Tuesday, June 07, 2011 3:17 PM
  • All on Windows Server 2008 R2 x64, SQL Server 2008 R2 x64 and import data from a access 2003 mdb-file (x86).

    In BIDS open a new SSIS project.
    Delete the automatic generated dtsx-file under folder SSIS Packages.
    Right-Click on that folder and select Migrate DTS 2000 Package.

    After migrating the old DTS Package
    check the connections in Connection Managers.

    In project properties under Debugging set the Dedug Option Run64BitRuntime to False.
    Start debugging, your dtsx-package runs successfully.

    If Dedug Option Run64BitRuntime is set to True my Package runs into the error
    OLE DB provider MICROSOFT.JET.OLEDB.4.0 is not registered.

    If you create a SQL Agent Job to run that (in BIDS tested) SSIS package
    under Job Step Properties select Type SQL Server Integration Services Package
    and in Execution Options check "Use 32 bit runtime" checkbox.

    Hope that helps, Lutz



    • Edited by sqlLKR Thursday, December 13, 2012 12:00 PM
    Thursday, December 13, 2012 7:22 AM