locked
SQL Server Express - 32 bit Microsoft.Jet.OLEDB.4.0 on 64 bit SQL Server RRS feed

  • Question

  • Hi,

    Some background on my issue:

    1.)

    When using OPENDATASOURCE() or OPENROWSET() I receive the error:

    The 32-bit OLE DB provider "Microsoft.Jet.OLEDB.4.0" cannot be loaded in-process on a 64-bit SQL Server.

    I receive a similar error when trying to use ACE.

    2.)

    I understand that this could be rectified if I installed the 6 bit version of JET or ACE.  To do this however, would require upgrading my Office Suite from 32 to 64 bit.

    3.)

    When using the SQL Server Import and Export Wizard, I can import data from excel just fine.  I can only not import when doing so from a query, i.e. 

    SELECT * FROM OPENROWSET(
    'Microsoft.Jet.OLEDB.4.0',
    'Excel 12.0 Xml;HDR=YES;Database=C:\FileDirectory\Filename.xlsx;',
    'SELECT * FROM [SheetName]')

    Question

    Since it is possible to load excel data into SQL-Express via the wizard, is there a way to replicate that functionality and make a query work without installing 64-bit Office, or uninstalling/re-installing 32-bit SQL Express?


    • Edited by X-Squared Wednesday, June 25, 2014 6:44 PM
    Wednesday, June 25, 2014 6:43 PM

Answers

  • OPENROWSET requires linked server, which requires loading the OleDB provider in the SQL Server process.  Jet and ACE are not supported for this, and anyway would require the same "bitness" as SQL Server.

    The Import/Export Wizard creates an SSIS package which runs outside the SQL Server process and can run in 32bit on a 64bit server.

    So, you need to use SSIS or the Import/Export Wizard to load your data.

    You can perhaps use the SSIS Data Feed Publishing components

    http://www.microsoft.com/en-us/download/details.aspx?id=39931

    to "select" from an SSIS package.  But I'm not sure of what "bitness" the package will run in.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by X-Squared Wednesday, June 25, 2014 9:12 PM
    Wednesday, June 25, 2014 8:44 PM

All replies

  • Bump.
    Wednesday, June 25, 2014 8:21 PM
  • OPENROWSET requires linked server, which requires loading the OleDB provider in the SQL Server process.  Jet and ACE are not supported for this, and anyway would require the same "bitness" as SQL Server.

    The Import/Export Wizard creates an SSIS package which runs outside the SQL Server process and can run in 32bit on a 64bit server.

    So, you need to use SSIS or the Import/Export Wizard to load your data.

    You can perhaps use the SSIS Data Feed Publishing components

    http://www.microsoft.com/en-us/download/details.aspx?id=39931

    to "select" from an SSIS package.  But I'm not sure of what "bitness" the package will run in.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by X-Squared Wednesday, June 25, 2014 9:12 PM
    Wednesday, June 25, 2014 8:44 PM
  • That makes a lot of sense.  Thank you.
    Wednesday, June 25, 2014 9:13 PM