title: How do you use RODBC with a Query containing DDL Commands?
body: <p>I am attempting to use R to clean the output data from the following SQL command however when it is done loading it results in an empty data-set. The primary purpose of the query is to create multiple time series for every individual in a group. Temp tables were used due to the size of the datasets. </p>
  
  <p>this is what I have thus far:</p>
  
  <pre><code>conn&lt;-odbcConnect("ks208")
  Query&lt;-"
  
  
  ---temp table for daily balances
  Create Table #dmhi_hist
  (rowno_dmddmhi_dmd int,
  balance float, 
  amount float,
  effective date,
  )
  
  Insert Into #dmhi_hist
  
  SELECT data_.rowno_dmddmhi_dmd,
         dmd.balance,
         Sum(data_.amount) AS amount,
         data_.effective
  FROM   (
         --Top False Cap 
         SELECT dmhi.rowno,
                dmhi.rowno_dmddmhi_dmd,
                0                               AS amount,
                CONVERT(Date, Getdate()) AS effective
         FROM   ks208.dbo.dmd
                INNER JOIN dmon208.dbo.dmhi
                        ON dmd.rowno = dmhi.rowno_dmddmhi_dmd
         UNION
         SELECT dmhi.rowno,
                dmhi.rowno_dmddmhi_dmd,
                dmhi.amount,
                CONVERT(Date, dmhi.effective) AS effective
         FROM   ks208.dbo.dmd
                INNER JOIN ks208.dbo.dmhi
                        ON dmd.rowno = dmhi.rowno_dmddmhi_dmd
         UNION
         SELECT dmhi.rowno,
                dmhi.rowno_dmddmhi_dmd,
                dmhi.amount,
                CONVERT(Date, dmhi.effective) AS effective
         FROM   dmon208.dbo.dmd
                INNER JOIN dmon208.dbo.dmhi
                        ON dmd.rowno = dmhi.rowno_dmddmhi_dmd
          -- False Bottom Cap
          UNION
          SELECT dmhi.rowno,
                 dmhi.rowno_dmddmhi_dmd,
                 0                                                  AS amount,
                 CONVERT(Date, Dateadd(year, -4, Getdate())) AS effective
          FROM   ks208.dbo.dmd
                 INNER JOIN dmon208.dbo.dmhi
                         ON dmd.rowno = dmhi.rowno_dmddmhi_dmd) AS data_
         INNER JOIN ks208.dbo.dmd
                 ON dmd.rowno = data_.rowno_dmddmhi_dmd
  WHERE  Year(data_.effective) &gt;= Year(Getdate()) - 4
  
  
  GROUP  BY data_.rowno_dmddmhi_dmd,
            data_.effective,
            dmd.balance 
  
  --- Joining the balances and transations to the calandar
  DECLARE @start_date DATE = Dateadd(year, -4, Getdate());
  DECLARE @end_date DATE = getdate();
  
  
      WITH    AllDays
            AS ( SELECT   @start_date AS [Date]
                 UNION ALL
                 SELECT   DATEADD(DAY, 1, [Date])
                 FROM     AllDays
                 WHERE    [Date] &lt; @end_date )
  Insert Into #dmhi_hist(effective,rowno_dmddmhi_dmd,amount,balance)
       SELECT [Date] as effective,dmd.rowno as rowno_dmddmhi_dmd,isnull(#dmhi_hist.amount,0) as amount,dmd.balance
       FROM   AllDays 
       cross join KS208.dbo.dmd
       left outer join #dmhi_hist on AllDays.Date = #dmhi_hist.effective and dmd.rowno = #dmhi_hist.rowno_dmddmhi_dmd
       OPTION (MAXRECURSION 0)
  
       select * from #dmhi_hist
  
  
  "
  conn&lt;-odbcConnect("ks208")
  dataset &lt;-sqlQuery(conn,Query)
  </code></pre>
  
  <p>is it that RODBC is simply limited to queries or is it the structure of my command?   </p>