none
How to link Excel 2010 .XLSX file to SQL server 2008R2 - a method that actually works?

    Question

  • I have been trying unsuccessfully to link my excel file to SQL server like I used to with SQL 2005.

    In SQL 2008R2 I have installed the 64-bit data access components from Office 2010 to get the  provider 'Microsoft.ACE.OLEDB.12.0' avilable.

    When I try this command:

    SELECT

     

    * FROM OPENDATASOURCE( 'Microsoft.ACE.OLEDB.12.0', 'Data Source="E:\ADUpdates\Employee List1.xlsx"; Extended properties=Excel 12.0')...[Sheet1$]

    I get 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. Access denied.

    Msg 7301, Level 16, State 2, Line 1

    Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

    The Access Denied error seems to be form the worksheet and not the file itself. Evidence is that if I enter a bogus sheet name it tells me the sheet doesn't exist and after this error the file is locked open and can't be saved again from Excel until the query windows is closed. The sheet is not protected in Excel so I don't know how access to it can be denied.

    Does anyone actually have this working in SQL 2008R2?

    • Moved by Tom PhillipsModerator Tuesday, November 13, 2012 4:22 PM TSQL question (From:SQL Server Database Engine)
    Friday, January 28, 2011 1:13 AM

Answers

All replies

  • Some minor progress on this issue by running SQL Server service under a domain account with Administrator priv on the SQL server.

    This query now works in a query window:

    SELECT * FROM OPENDATASOURCE( 'Microsoft.ACE.OLEDB.12.0', 'Data Source="E:\ADUpdates\Employee List.xlsx"; Extended properties=Excel 12.0')...[EmployeeData];

    However, creating a Linked server object for the same spreadsheet still does not work:

    EXEC master.dbo.sp_addlinkedserver @server = N'TEST', @srvproduct=N'Excel 12.0', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'E:\ADUpdates\Employee List.xlsx'

     

     

    The test connection to the linked server failed.

    Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "TEST".
    OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "TEST" returned message "Unrecognized database format 'E:\ADUpdates\Employee List.xlsx'.". (Microsoft SQL Server, Error: 7303)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476

    Naturally, the help link provided only confirms the contempt microsoft holds for their customers by not providing any information at all.

    (appologies for the weird colour and fonts, this is a microsoft product, just have to live with obvious defects).

    Friday, January 28, 2011 3:21 AM
  • Hi Mark,

    Thank you for your question.
     
    I am currently looking into this issue and will give you an update as soon as possible.
     
    Thank you for your understanding and support.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Wednesday, February 02, 2011 7:12 AM
    Moderator
  • Any help will be greatly appreciated.

    Mark.

    Wednesday, February 02, 2011 10:08 PM
  • Updates?
    Tim Webber
    Thursday, March 17, 2011 3:26 PM
  • Mark

     

     

    Try this link, it is a very good one and help me many times! rs...

    http://blog.hoegaerden.be/2010/03/29/retrieving-data-from-excel/


    ------------------------------------------------------------- Oracle OCA11g
    Thursday, March 17, 2011 3:31 PM
  • Hi Mark, 

     

    Add another parameter while creating linked server @provstr=N'Excel 12.0'. 

    This will make it work. Create a new linked server 

     

    EXEC master.dbo.sp_addlinkedserver

    @server = N'TEST', 

    @srvproduct=N'Excel 12.0', 

    @provider=N'Microsoft.ACE.OLEDB.12.0', 

    @datasrc=N'E:\ADUpdates\Employee List.xlsx',

    @provstr=N'Excel 12.0' 

     

    Thanks 

    Harpreet 


     

     

     


    Thanks, Harpreet
    Thursday, March 17, 2011 4:39 PM
  • I know this is a late reply but does number 3 on the following link help?

    http://blogs.msdn.com/b/sqlforum/archive/2011/06/10/sql-server-faqs-ebook-with-database-engine-ssrs-ssis-ssas-.aspx

    Tuesday, November 13, 2012 4:18 PM
  • hi

    View the following link as an example :

    http://akawn.com/blog/2012/01/query-and-update-an-excel-2010-spread-sheet-via-a-linked-server/


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Tuesday, November 13, 2012 4:32 PM