title: SQL error when querying from a linked server (SQL Server linked to Oracle)
body: <p>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.</p>
  
  <p>I know the Oracle database link works because this query returns the expected results:</p>
  
  <pre><code>SELECT TABLE_ID
  FROM   [OraDevLink]..[SCHEMA].[MY_TABLE];
  </code></pre>
  
  <p>Here is another query that works correctly:</p>
  
  <pre><code>SELECT *
  FROM   dbo.MY_TABLE
  WHERE  TABLE_ID NOT IN
  (
      SELECT TABLE_ID
      FROM   [OraDevLink]..[SCHEMA].[MY_TABLE]
  );
  </code></pre>
  
  <p>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):</p>
  
  <pre><code>SELECT *
  FROM   dbo.MY_TABLE
  WHERE  TABLE_ID NOT IN
  (
      SELECT TABLE_ID
      FROM   [OraDevLink]..[SCHEMA].[MY_TABLE]
  )
  AND    TABLE_ID IN(0, 100);
  </code></pre>
  
  <p>Here's the error message:</p>
  
  <blockquote>
    <p>OLE DB provider "OraOLEDB.Oracle" for linked server "OraDevLink" returned message "ORA-01403: no data found".<br>
    Msg 7346, Level 16, State 2, Line 1<br>
    Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "OraDevLink". </p>
  </blockquote>
  
  <p>What I ultimately want to do is run an INSERT statement that looks for missing records (but with a filter). This works correctly:</p>
  
  <pre><code>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]
  );
  </code></pre>
  
  <p>But this returns the same above error message:</p>
  
  <pre><code>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]
  );
  </code></pre>
  
  <p><strong>EDIT</strong></p>
  
  <p>It gets worse. See if you can spot the difference between these two queries:</p>
  
  <pre><code>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); -- &lt;-Hint
  </code></pre>
  
  <p>The first works; the second throws an error!</p>