Problem running DELETE statement against linked server in SQL Server 2008

Question

I'm having a problem running this query:

DELETE FROM [IRPROD]..[BUDGET_USER].[GL_EXP]
WHERE FISCAL_YEAR = 2010

IRPROD is a linked server to an Oracle 10g database. It's linked using the Oracle OleDB provider. There are ~79000 records that need to be deleted. Running this query it deletes 54. Running it again gives me this error message.

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "IRPROD" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "OraOLEDB.Oracle" for linked server "IRPROD".

Obviously, "catastrophic failure" is something bad. But the weird thing is that I can run SELECT and INSERT statements all day and it works fine. I do have permissions to delete the rows. Also, if I link the table through Access I can delete the records.

Any ideas?

Answer 1

This is OleDB provider and Database Bug 5043675: Setting up a linked server works fine to read data but updating or deleting data fails with the error.

Use Patches 5043675, 6637236

You can download them from Oracle Support.

Answer 2

Ok, this turned out to be an easy fix with a complicated explanation.

First problem was an outdated Oracle driver. We were using the version 9 provider so I updated to the 10g provider provided through our OIT shop. Turns out the one they have has a bug in it. So I uninstalled it and installed the 10g provider from Oracle which is updated to fix the bug.

Second, I changed the 'fiscal_year' field from varchar2 type to a number type. If you send a DELETE statement with a WHERE (or maybe any conditional clause, I only checked WHERE) that compares against a test field, then SQL Server must do a remote scan on the table, which basically retrieves the data and has to one-by-one delete each row. If you send a DELETE statement with a WHERE that is numeric, then SQL Server can pass on the query directly to the remote DB without doing a remote scan.

I hope this helps somebody. I found this by trial and error and bit's and pieces from the internet. Thanks for all that helped on this question.