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)

Firth

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.

Renascent

This is incredible. Thank you so much for this ChrisGheen. – Athodyd

This helped me a lot - I can use long running scripts with temp tables, inserts, deletes etc. until I get the last select statement run. I am using this for DBI/ODBC with R. – Caresse

It does not help for me?! – Fogbow

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.

Tiruchirapalli

First off, thanks for the help, your method works for me as well. As far as putting my query into one string, I am not sure separating the statements with the go command works. Could this be a function of me using Transact SQL? – Firth

@Bob - I don't know enough about the various flavors of SQL or how the R interface to SQL is fundamentally different than a "normal" interface to know why it would behave differently. I assume if you highlight all of your query above and run it through management studio, it works fine? Assuming you need separate queries in R to make it work, you could turn all of them into a list and use lapply(). I'll update the answer to reflect this method. – Tiruchirapalli

@Bob BTW I think that drop table is not needed cause you create temporary table so it will gone when you close connection. – Preparator

On another note, I think this explains why stored procedures that create and manipulate temporary tables don't work with RODBC. – Firth