RODBC Temporary Table Issue when connecting to MS SQL Server

Question

I am running R on unix and I am using the RODBC package to connect to MS SQL server. I can execute a query that returns results fine with the package, but if I use a temporary table somewhere in my SQL query, an empty string is returned to me. After looking over the web, I think the problem might be that the RODBC package was written assuming an end-user was writing in standard SQL (as opposed to MS SQL). I have provided the below code as an example.

Interestingly enough, the temporary table problem does not exist if I use the RJDBC package. However, the RJDBC package is painfully slow with importing even 80,000 rows (10 columns) and will stall out frequently, so that is not an option either. Has anyone else run into this problem? If there are alternate solutions that I haven't thought of, I'd love to hear them.

It seems I am not the only one with this problem, perhaps this is an R-Bug? http://r.789695.n4.nabble.com/RODBC-results-from-stored-procedure-td897462.html

Thanks

Here is the R example:

library(RODBC)
ch <- odbcConnect(insert your server info here)
qry4 <- "create table #tempTable(
    Test int
)
insert into #tempTable
select 2

select * from #tempTable
drop table #tempTable
"
df4 <- sqlQuery(ch, qry4)

Answer 1

The RODBC driver seems to think that when SQL Server returns any count of rows that the entire statement is complete. So you need to set nocount on at the beginning of your statement or stored procedure that is called.

set nocount on

This allowed me to use a stored procedure that was using temporary table in R.

Answer 2

The problem appears to be in your SQL syntax, not anything inherent with R or the RODBC package. I'm fairly certain you need to separate your SQL statements with the go command to make sure that the first statement finished executing before the second, and the third, and so on. Alternatively, you could break them up into four different statements as I did below. This works on my machine:

library(RODBC)
ch <- odbcConnect("details")

qry1 <- "create table #temptable (test int)"
qry2 <- "insert into #temptable(test) values(2)"
qry3 <- "select * from #temptable"
qry4 <- "drop table #temptable"

sqlQuery(ch, qry1)
sqlQuery(ch, qry2)
doesItWork <- sqlQuery(ch, qry3)
sqlQuery(ch, qry4)

And the output

> doesItWork
  test
1    2

EDIT

Turning all of your queries into a list object and iterating through them could save you some coding in the future. For example:

queryList <- list(qry1, qry2, qry3, qry4)
sqlOutput <- lapply(queryList, function(x) sqlQuery(ch, x))

This will generate some extraneous output that you may not care about, but the results you are interested in can be pulled with sqlOutput[[3]] where 3 represents the query of interest.