Update Excel sheets from SQL Server 2005 linked servers

0
votes

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.

I use:

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

Can someone help? thanks in advance.

1
sql-server-2005excellinked-server

1 Answers

0
votes

Read this: http://support.microsoft.com/kb/257819

Excerpt: ... However, you cannot:

  1. Delete an entire record at once ... You can only delete a record by blanking out the contents of each individual field

  2. Delete the value in a cell containing an Excel formula

  3. You cannot delete the empty spreadsheet row(s) in which the deleted data was located