I want to synchronize a SQLServer table (version 2008) with data coming from Oracle (11g). The query is quite simple, uses openquery and looks like:
update t set field2 = tt.field2 from my_sqlserver_table t join openquery(XXXXXX, 'select field1, field2 from my_oracle_table') tt ON t.field1 = tt.field1
It works well, fast, everybody is happy. But sometimes (very rare), the query fails with this kind of error:
OLE DB provider "OraOLEDB.Oracle" for linked server "XXXXXX" returned message "ORA-01403: aucune donnée trouvée". Msg 7346, Level 16, State 2, Line 9 Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "XXXXXX".
If I make a copy of the table an apply the same update query, it works.
If I make a select instead of an update, it works and retrieves all the rows to be updated.
So that's not an issue with the data, nor with the linked server. It's an issue with the destination table itself (I assume).
Since there is an index on
field2, I tried to recreate it. But the error was still there.
And when I recreated the clustered index of the table (on another column, let's call it
field0), the error disappeared...
OK, good, but well...
- What happened ?
- How could we prevent that error to appear again ?