SQL error when querying from a linked server (SQL Server linked to Oracle)

Question

I am getting an error on some queries in SQL Server that select from a linked Oracle database. The error occurs only for some SELECT statements but not others. I am trying to compare data on the two servers and get records that exist on one but not the other.

I know the Oracle database link works because this query returns the expected results:

SELECT TABLE_ID
FROM   [OraDevLink]..[SCHEMA].[MY_TABLE];

Here is another query that works correctly:

SELECT *
FROM   dbo.MY_TABLE
WHERE  TABLE_ID NOT IN
(
    SELECT TABLE_ID
    FROM   [OraDevLink]..[SCHEMA].[MY_TABLE]
);

This query, which is almost identical to the one above but just tries to filter the results a bit, returns an error message (only the last line is different):

SELECT *
FROM   dbo.MY_TABLE
WHERE  TABLE_ID NOT IN
(
    SELECT TABLE_ID
    FROM   [OraDevLink]..[SCHEMA].[MY_TABLE]
)
AND    TABLE_ID IN(0, 100);

Here's the error message:

OLE DB provider "OraOLEDB.Oracle" for linked server "OraDevLink" returned message "ORA-01403: no data found".
Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "OraDevLink".

What I ultimately want to do is run an INSERT statement that looks for missing records (but with a filter). This works correctly:

INSERT INTO [OraDevLink]..[SCHEMA].[MY_TABLE]
( TABLE_ID )
SELECT TABLE_ID
FROM   dbo.MY_TABLE
WHERE  TABLE_ID NOT IN
(
    SELECT TABLE_ID
    FROM   [OraDevLink]..[SCHEMA].[MY_TABLE]
);

But this returns the same above error message:

INSERT INTO [OraDevLink]..[SCHEMA].[MY_TABLE]
( TABLE_ID )
SELECT TABLE_ID
FROM   dbo.MY_TABLE
WHERE  DEPT_ID = 1 -- this is the only change
AND    TABLE_ID NOT IN
(
    SELECT TABLE_ID
    FROM   [OraDevLink]..[SCHEMA].[MY_TABLE]
);

EDIT

It gets worse. See if you can spot the difference between these two queries:

SELECT *
FROM   dbo.MY_TABLE
WHERE  TABLE_ID NOT IN
(
    SELECT TABLE_ID
    FROM   [OraDevLink]..[SCHEMA].[MY_TABLE]
)
AND    TABLE_ID IN(0);

SELECT *
FROM   dbo.MY_TABLE
WHERE  TABLE_ID NOT IN
(
    SELECT TABLE_ID
    FROM   [OraDevLink]..[SCHEMA].[MY_TABLE]
)
AND    TABLE_ID IN(0, 100); -- <-Hint

The first works; the second throws an error!

Answer 1

I can only assume this is a bug. There is no other explanation for this behavior.

Here was my workaround: Create a temporary table and use that instead.

SELECT TABLE_ID
INTO #temp_table
FROM   [OraDevLink]..[SCHEMA].[MY_TABLE];

SELECT *
FROM   dbo.MY_TABLE
WHERE  TABLE_ID NOT IN
(
    SELECT TABLE_ID
    FROM   #temp_table
)
AND    TABLE_ID IN(0, 100);

There is no reason for this to be necessary, and performance probably takes a hit, but at least it works!