0

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 ?
  • I think you should add Oracle to the tags, since this the error is likely specific to Oracle. I tried adding the tag myself, but it said my edit will not be visible until it is peer reviewed. – Matthew Sontum Feb 13 '17 at 12:35
  • @Matthew : as explained in the description, the error is probably not linked to Oracle (data ok, linked server ok). – irimias Feb 13 '17 at 12:39
  • The error message says: "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"." And you don't feel like Oracle is involved? – Matthew Sontum Feb 13 '17 at 12:40
  • @Matthew: no, because when the clustered index of the SQLServer destination table is recreated, the error disappears :) – irimias Feb 13 '17 at 12:44
  • I still wouldn't rule it out. My experience with linked servers is that the database source for the linked server matters. Even different versions of SQL Server can produce different results. Also the error message, in English, means: "An ORA-01403 error occurs when a SQL statement, written within a PL/SQL block, does not fetch any data." That is an Oracle error. – Matthew Sontum Feb 13 '17 at 12:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.