Post reply

Import from excel; Why can I import using wizard and not using sproc?

  • June 28, 2012 at 5:21 pm

    #258610

    So I've tried to run the following code and get the error below:

    SELECT * FROM

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

    'Excel 12.0;Database=\\C\Test.xlsx',

    'SELECT * FROM [Sheet1$]')

    The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.

    When I try to then import the worksheet using the SQL Server import/export wizard it shows:

    Source Provider : Microsoft.ACE.OLEDB.12.0

    Destination Location : ....

    Destination Provider : SQLNCLI10

    And this works just fine.

    So why do I get the error saying that Microsoft.ACE.OLEDB.12.0 hasn't been registered when trying to use OPENROWSET?

  • June 28, 2012 at 5:32 pm

    #1506897

    Maybe this will help:

    http://www.mytechmantra.com/LearnSQLServer/Troubleshoot_OLE_DB_Provider_Error_P1.html

    And then again, I might be wrong ...
    David Webb

  • June 29, 2012 at 10:26 am

    #1507210

    Thanks David, but I do have Microsoft office 2010 and SSIS on my machine. And I'm still wondering why I can perform basically the same task using the wizard as I'm unable to do using T-SQL.

    I can't use:

    SELECT * FROM

    OPENROWSET('Microsoft.Jet.OLEDB.4.0',

    'Excel 12.0;Database=\\C\Test.xlsx',

    'SELECT * FROM [Sheet1$]')

    because I have the 32bit version of Excel and the SQL Server is 64bit.

    Here is the error generated with this provider:

    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.

    Anyone know if there is any way to run the import wizard functionality through code?

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply