title: EXEC(query) AT linkedServer With Oracle DB
body: <p>I am using Microsoft SQL server 2005. I need to sync data between SQL server and an Oracle db. First thing I need is to find out if the count of data on Oracle side with certain filters(here I use ID as a simple example).</p>
  
  <pre><code>SELECT COUNT(*) FROM oracleServer..owner.table1 WHERE id = @id;
  </code></pre>
  
  <p>The problem I have is that the table on the lined server or Oracle is very big with 4M rows of data. The above query took about 2minutes to get data back. This code is just a simplied piece. Actually my SP has some other queries to update, insert data from the lined server to my SQL server. The SP took hours or 10+ hours to run with large Oracle db. Therefore T-SQL with lined server is not good for me.</p>
  
  <p>Recently I found OPENQUERY and EXEC (...) AT linedServer. OPENQUERY() is very fast. It took about 0 time to get the same result. However, it does not support variable query or expressions. The query has to be a literal constant string.</p>
  
  <p>EXEC() is in the same way to pass-through query to Oracle. It is fast as well. For example:</p>
  
  <pre><code>EXEC ('SELECT COUNT(*) FROM owner.table1 WHERE id = ' + CAST(@id AS VARCHAR))
    AT oracleServer
  </code></pre>
  
  <p>The problem I have is how to pass the result COUNT(*) back. I tried to google examples in web and msdn. All I can find are SQL or ExpressSQL linedServer examples like:</p>
  
  <pre><code>EXEC ('SELECT ? = COUNT(*) FROM ...', @myCount OUTPUT) AT expressSQL
  </code></pre>
  
  <p>This query does not work for Oracle. It seems in Oracle, you can set value as output in this way:</p>
  
  <pre><code>SELECT COUNT(*) INTO myCount ...
  </code></pre>
  
  <p>I tried this:</p>
  
  <pre><code>EXEC ('SELECT COUNT(*) INTO ? FROM ...', @myCount OUTPUT) AT oracleServer
  EXEC ('SELECT COUNT(*) INTO : FROM ...', @myCount OUTPUT) AT oracleServer
  EXEC ('SELECT : = COUNT(*) FROM ...', @myCount OUTPUT) AT oracleServer
  </code></pre>
  
  <p>None of those working. I got error message saying query not executable on Oracle server. </p>
  
  <p>I could write a .Net SQL Server project to do the job. Before that, I just wonder if there is anyway way to pass value out as oupput parameter so that I put the better performance T-SQL codes in my SP?</p>