Oracle ORA-01403: No Data Found while doing a JOIN with OPENQUERY on SQL Server
<p>Here's a real head scracther (at least for me) </p>

<p>I'm running a query let's say it's: </p>

<pre><code>SELECT number FROM equipment e
left outer join OPENQUERY(ORACLESERVER, 'SELECT num FROM equip_nums WHERE MODEL_NO = ''FOOBAR''') ora
ON e.number = ora.num
WHERE num IS NULL
      AND remove = 'no' 
      AND used = 'yes'
      AND model = 'FOOBAR' 
</code></pre>

<p>This query is run for a number of models, about 50 of them. It works FINE for 47 of those models. But then it fails giving the following error:</p>

<pre><code>    OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLESERVER" returned message "ORA-01403: no data found".
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLESERVER".
</code></pre>

<p>Now, I know what you're thinking.. "The query in the OPENQUERY is coming back empty, you need to handle that!" And while I agree that case should be handled.. The problem is that that query shouldn't be coming back empty! If I run this query from the same server</p>

<pre><code>SELECT * FROM OPENQUERY(ORACLESERVER, 'SELECT num FROM equip_nums WHERE MODEL_NO = ''FOOBAR''')
</code></pre>

<p>I pull back about 1100 rows. So I feel like there should be data there for the join.</p>

<p>Can anyone shed ANY light on this? I'm really baffled here... </p>