Cannot update table in linked server RRS feed

  • Question

  • Hi

    I cannot update tables in a linked server.
    The linked server is to an Excel 2007 workbook.

    Error message:
    Server: Msg 7346, Level 16, State 2, Line 1
    Could not get the data of the row from the OLE DB provider 'Microsoft.ACE.OLEDB.12.0'.
    [OLE/DB provider returned message: Bookmark is invalid.]
    OLE DB error trace [OLE/DB Provider 'Microsoft.ACE.OLEDB.12.0' IRowset::GetData returned 0x80040e0e].

    Update query:
    UPDATE test...KPI
    set [KPI value] = 2
    WHERE ID like 'COC-1'

    I can do a select on the table and get the correct result.

    Select query:
    select * from test...KPI
    WHERE ID like 'COC-1'

    If I use the same code on a linked server to an Excel 2003 version of the same file I can do both a select and update query of the table.

    Server info:
    SQL server 2000 ver. 8.00.2039 (SP4)

    Linked server Excel 2007:
    Provider name: Microsoft Office 12.0 Access Database Engine OLE DB Provider
    Product name: ACE 12.0
    Datasource: c:\cxalimport\KPI_Marine_2010.xlsx
    Provider string: Excel 12.0; HDR=Yes

    Linked server Excel 2003:
    Provider name: Microsoft JET 4.0 OLE DB Provider
    Product name: Excel
    Datasource: c:\cxalimport\KPI_Marine_2010.xls
    Provider string: Excel 8.0

    Both datasources have the same access rights

    Any help would be much appreciated.

    Thanks in advance :-)

    Friday, March 12, 2010 10:10 AM