SQL Server: DELETE FROM OPENDATASOURCE

Question

This works:

SELECT * FROM OPENDATASOURCE( 
       'Microsoft.ACE.OLEDB.12.0', 
       'Data Source=d:\JobFiles\MyFile.xlsx; 
        Extended properties=Excel 8.0')...MySheet$

So does this:

INSERT INTO OPENDATASOURCE( 
       'Microsoft.ACE.OLEDB.12.0', 
       'Data Source=d:\JobFiles\MyFile.xlsx; 
        Extended properties=Excel 8.0')...MySheet$
SELECT * FROM blahblahblah

So why not?

DELETE FROM OPENDATASOURCE( 
        'Microsoft.ACE.OLEDB.12.0', 
        'Data Source=d:\JobFiles\MyFile.xlsx;
         Extended properties=Excel 8.0')...MySheet$

I've done some searching with no real luck. At the end of the day, all I need is the excel rows deleted before I can insert fresh data and I want to achieve this with SQL only.

Answer 1

You cannot delete entire rows via OPENDATASOURCE. According to http://support.microsoft.com/kb/257819:

Delete

You are more restricted in deleting Excel data than data from a relational data source. In a relational database, "row" has no meaning or existence apart from "record"; in an Excel worksheet, this is not true. You can delete values in fields (cells). However, you cannot:

• Delete an entire record at once or you receive the following error message:

Deleting data in a linked table is not supported by this ISAM.

You can only delete a record by blanking out the contents of each individual field.

• Delete the value in a cell containing an Excel formula or you receive the following error message:

Operation is not allowed in this context.

• You cannot delete the empty spreadsheet row(s) in which the deleted data was located, and your recordset will continue to display empty records corresponding to these empty rows.