none
SELECT * FROM OPENROWSET

    Question

  • Hi there,

    I have this query, very basic:

    SELECT * FROM OPENROWSET
    ('Microsoft.ACE.OLEDB.12.0'
    ,'Excel 12.0;HDR=YES;IMEX=1
    ;Database=C:\Documents and Settings\andy\My Documents\Test_Worksheet.xls;'
    ,'Select * From [Sheet1$]');

    and am getting the following error:

    OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error

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

    This following warning is apparent:

    -------------------------------------------------------------------------------------------------------------------------------------

    Warning! x64 driver can not be installed if Microsoft Office 2007/2010 x86 is already installed!
    So there is no way to import Excel data to SQL Server x64 using OPENROWSET/OPENDATASOURCE functions on a machine with Microsoft Office x86

    -------------------------------------------------------------------------------------------------------------------------------------

    My system's configuration are those mentioned above, have Microsoft come up with a patch to make this work?

    Many Thanks

    Andrew


    • Edited by AndyQPR Tuesday, March 05, 2013 2:57 PM
    Tuesday, March 05, 2013 2:54 PM

Answers

  • This is clearly driver problem, install the 32 bit version of the driver which you named above in the server from where you are running the job, Run the job from the DTexec utility in the 32 bit envirnment, the utility would be present at the SSMS server path.

    Wednesday, March 06, 2013 5:33 AM

All replies

  • As the error message says, its version compatibility issue, try to install appropriate 32 bit version drivers for Excel. Is this 64 bit system or 32 bit sytem?

    Thanks, Sohail ~Please mark answered if your question is completely answered; vote it as helpful to help others find a solution quicker. This posting is provided AS IS with no warranties.

    Tuesday, March 05, 2013 2:58 PM
  • The server is 64 bit and the machine with Office is 32 bit
    Tuesday, March 05, 2013 3:03 PM
  • You can only have either the 32 or 64 bit office drivers loaded on a single machine.  Bids is 32 bit and uses the 32 bit drivers.  I typically only install Office 32 bit version on machines which need to use excel/access in ssis and then set up the job to execute 32 bit as well.

    Chuck Pedretti | Magenic – North Region | magenic.com

    Tuesday, March 05, 2013 3:06 PM
  • So let me get this clear, my installation of the 64 bit driver (AccessDatabaseEngine_x64.exe)

    on the 64 bit machine is wrong and should install the 32 bit driver (AccessDatabaseEngine.exe)

    instead? That's what I understand you're driving at.

    Tuesday, March 05, 2013 3:22 PM
  • So let me get this clear, my installation of the 64 bit driver (AccessDatabaseEngine_x64.exe)

    on the 64 bit machine is wrong and should install the 32 bit driver (AccessDatabaseEngine.exe)

    instead? That's what I understand you're driving at.


    If you want to be able to run inside bids then yes. 

    Chuck Pedretti | Magenic – North Region | magenic.com

    Tuesday, March 05, 2013 3:42 PM
  • I can run in BIDS as it is with the 64 bit driver, the real problem is running

    from the SQL Server query window with this driver which throws up the error.

    If I install the 32 bit, will that work or is this something that Microsoft have

    yet to look into?


    • Edited by AndyQPR Tuesday, March 05, 2013 3:50 PM
    Tuesday, March 05, 2013 3:49 PM
  • I can run in BIDS as it is with the 64 bit driver, the real problem is running

    from the SQL Server query window with this driver which throws up the error.

    If I install the 32 bit, will that work or is this something that Microsoft have

    yet to look into?


    OK - so this is not an SSIS question, it is an SSMS question?

    SSMS is 32 bit, so I'd assume that it requires the 32 bit driver - honestly I've never had the occasion to want to query an excel file from ssms


    Chuck Pedretti | Magenic – North Region | magenic.com

    Tuesday, March 05, 2013 3:53 PM
  • The scenario is having a 64 bit SQL Server platform, a 32 bit MS Office installation on another

    machine and then running data imports (from Excel) scheduled for overnight using SSIS packages (i.e. .dtsx files).

    That's where the problem kicks in it seems. SSIS is brilliant for scheduling and especially for data

    automated importing, can it be done in the setup I have?

    Have you never scheduled any data imports overnight?

    Tuesday, March 05, 2013 4:01 PM
  • The scenario is having a 64 bit SQL Server platform, a 32 bit MS Office installation on another

    machine and then running data imports (from Excel) scheduled for overnight using SSIS packages (i.e. .dtsx files).

    That's where the problem kicks in it seems. SSIS is brilliant for scheduling and especially for data

    automated importing, can it be done in the setup I have?

    Have you never scheduled any data imports overnight?


    Sure, I schedule them all the time.  Using a SQL Agent job and SSIS packages.  I never accept Excel as an input source unless it is a last resort, due to the flakey nature of the file format.  I'm still not sure what your issue is - why are you running the query in SSMS?

    Chuck Pedretti | Magenic – North Region | magenic.com

    Tuesday, March 05, 2013 4:19 PM
  • I am using SQL Agent Job and an SSIS package, very simple import from Excel

    to a SQL table, but the job falls over  with the error:

    Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
    03/05/2013 17:37:43,Test_File_Retrieval,Error,0,TESTSERVER\DEV,Test_File_Retrieval,(Job outcome),,The job failed.  The Job was invoked by User UK\User.  The last step to run was step 1 (Test File Retrieval).,00:00:01,0,0,,,,0
    03/05/2013 17:37:43,Test_File_Retrieval,Error,1,TESTSERVER\DEV,Test_File_Retrieval,Test File Retrieval,,Executed as user: AIOI\TESTSERVER-USR. ...00.5000.00 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  17:37:43  Error: 2013-03-05 17:37:44.64     Code: 0xC0202009     Source: ExcelImport Connection manager "SourceConnectionExcel"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040154.  An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".  End Error  Error: 2013-03-05 17:37:44.64     Code: 0xC020801C     Source: Data Flow Task Source - Sheet1$ [1]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "SourceConnectionExcel" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2013-0...  The package execution fa...  The step failed.,00:00:01,0,0,,,,0

    I'm using the SSMS query to ascertain that the connections are there, obviously not.

    By the way what would be the CLASS  in a package?

    Tuesday, March 05, 2013 4:42 PM
  • This is clearly driver problem, install the 32 bit version of the driver which you named above in the server from where you are running the job, Run the job from the DTexec utility in the 32 bit envirnment, the utility would be present at the SSMS server path.

    Wednesday, March 06, 2013 5:33 AM