I use SQL Server linked servers to connect to Excel 2003 multiple tabs workbook. After connected, I have not problem to use select statement like:
select * FROM OPENQUERY(TEST, 'select * from [fist day$]')
but I can not use normal delete and insert statement. I want to delete data from first row and insert one row of data into the last row to the excel spreadsheet.
delete top(1) FROM OPENQUERY(TEST, 'select * from [first day$]')
and I get
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "TEST" returned message "Deleting data in a linked table is not supported by this ISAM.".
Msg 7345, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "TEST" could not delete from table "select * from [first day$]". There was a recoverable, provider-specific error, such as an RPC failure.