RODBC: execute a query containing multiple statements
<p>I have a stored procedure that returns a varbinary output parameter. I want to call it from R and capture the return variable. I tried:</p>

<pre><code>qq &lt;- "declare @mm varbinary(max); exec spTrain_df_to_op @mm_outer = @mm output; select @mm as model;"
conStr &lt;- "Driver={SQL Server};Server=.;Database=airline_new;Trusted_Connection=TRUE;"
dbhandle &lt;- odbcDriverConnect(conStr, rows_at_time = 1)
result &lt;- sqlQuery(dbhandle, qq)
</code></pre>

<p>This fails and result comes out as character(0). It may be that RODBC does not want to execute what is essentially a sequence of queries. How would I go around that?</p>

<p>Thank you!</p>

<p>Here is the stored procedure:</p>

<pre><code>ALTER PROCEDURE [dbo].[spTrain_df_to_op]
  @mm_outer varbinary(max) output
AS
  BEGIN TRY
    exec sp_execute_external_script
      @language = N'R',
      @script = N'
        func &lt;- function() {
          in_df[,"DayOfWeek"] &lt;- factor(in_df[,"DayOfWeek"], levels=as.character(1:7))
          # The model formula
          formula &lt;- ArrDelay ~ CRSDepTime + DayOfWeek + CRSDepHour:DayOfWeek
          # Train the model
          rxSetComputeContext("local")
          mm &lt;- rxLinMod(formula, data=in_df, transformFunc=NULL, transformVars=NULL)
          mm &lt;&lt;- serialize(mm, connection=NULL)
        }
        result = func()
      ',
      @input_data_1 = N'select top 10000 ArrDelay,CRSDepTime,DayOfWeek,CRSDepHour from cleanData',
      @input_data_1_name = N'in_df',
      @params = N'@mm varbinary(max) output',
      @mm = @mm_outer output
  END TRY
  BEGIN CATCH
    THROW;
  END CATCH;
</code></pre>

<p>For kicks I tried the following:</p>

<pre><code>qq = "declare @mm varbinary(max); select 2 as hello; select 1 as model"
conStr &lt;- "Driver={SQL Server};Server=.;Database=airline_new;Trusted_Connection=TRUE;"
dbhandle &lt;- odbcDriverConnect(conStr, rows_at_time = 1)
result &lt;- sqlQuery(dbhandle, qq)
</code></pre>

<p>This returns only the result of the 1st query:</p>

<pre><code>  hello
1     2
</code></pre>

<p>I also tried setting the query to</p>

<pre><code>qq = paste0("SET NOCOUNT ON; declare @mm varbinary(max); ",
           "exec spTrain_df_to_op @mm_outer = @mm output; ",
           "SET NOCOUNT OFF; select @mm as model;")
</code></pre>

<p>That still produced character(0).</p>

<p>Creating a stored procedure out of the queries is not an option.</p>