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.