title: SQL Server OpenQuery() behaving differently then a direct query from TOAD
body: <p>The following query works efficiently when run directly against Oracle 11 using TOAD (with native Oracle drivers)</p>
  
  <pre><code>select ... from ... where ...
  and srvg_ocd in (
    select ocd
     from rptofc
    where eff_endt = to_date('12/31/9999','mm/dd/yyyy')
      and rgn_nm = 'Boston'
  ) ...
  ;
  </code></pre>
  
  <p>The exact same query "never" returns if passed from SQL Server 2008 to the same Oracle database via <code>openquery()</code>. SQL Server has a link to the Oracle database using an Oracle Provider OLE DB driver.</p>
  
  <pre><code>select * from openquery( servername, '
    select ... from ... where ...
    and srvg_ocd in (
      select ocd
       from rptofc
      where eff_endt = to_date(''12/31/9999'',''mm/dd/yyyy'')
        and rgn_nm = ''Boston''
    ) ...
  ');
  </code></pre>
  
  <p>The query doesn't return in a reasonable amount of time, and the user kills the query. I don't know if it would eventually return with the correct result.</p>
  
  <p>This result where the direct TOAD query works efficiently and the <code>openquery()</code> version "never" returns is reproducible.</p>
  
  <p>A small modification to the <code>openquery()</code> gives the correct efficient result: Change <code>eff_endt</code> to <code>trunc(eff_endt)</code>.</p>
  
  <p>That is well and good, but it doesn't seem like the change should be necessary.</p>
  
  <p><strong><code>openquery()</code> is supposed to be pass through, so how can there be a difference between the TOAD and <code>openquery()</code> behavior?</strong></p>
  
  <p>The reason we care is because we frequently develop complex queries with TOAD directly accessing Oracle. Once we have the query functioning and optimized, we convert it to an <code>openquery()</code> string for use in a SQL Server application. It is extremely aggravating to have a query suddenly fail with <code>openquery()</code> when we know it worked as a direct query. Then we have to search for a work-around through trial and error.</p>
  
  <p>I would like to see the Oracle trace files for the two scenarios, but the Oracle server is within another organization, and we are not getting cooperation from the Oracle DBAs.</p>
  
  <p><strong>Does anyone know of any driver, or TOAD, or ??? issues that could account for the discrepancy? Is there any way to eliminate the problem such that both methods always give the same result?</strong></p>