T
TOI
Guest
Hi
This question was also posted in the SQL forum where I was told to move it
here.
I cannot update tables in a linked server.
The linked server is to an Excel 2007 workbook.
MS SQL 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:
MS 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
This question was also posted in the SQL forum where I was told to move it
here.
I cannot update tables in a linked server.
The linked server is to an Excel 2007 workbook.
MS SQL 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:
MS 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