当连接到MS SQL服务器时，会出现RODBC临时表问题

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.

我在unix上运行R，我正在使用RODBC包连接到MS SQL server。我可以执行一个查询，该查询使用包返回正确的结果，但是如果我在SQL查询的某个地方使用一个临时表，则会返回一个空字符串给我。在浏览了web之后，我认为问题可能是，如果最终用户是用标准SQL(而不是SQL)编写的，那么就编写了RODBC包。我提供了下面的代码作为示例。

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.

有趣的是，如果我使用RJDBC包，临时表问题并不存在。然而，rrrjdbc非常慢，甚至导入了80000行(10列)，并且会经常中断，所以这也不是一个选项。有人遇到过这个问题吗?如果有别的解决办法，我还没想过，我很想听听。

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

看来我不是唯一有这个问题的人，也许这是R-Bug?http://r.789695.n4.nabble.com/RODBC-results-from-stored-procedure-td897462.html

Thanks

谢谢

Here is the R example:

这里有一个R的例子:

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)

2 个解决方案

#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.

RODBC驱动程序似乎认为，当SQL Server返回任何行数时，整个语句就完成了。因此，需要在调用的语句或存储过程的开头设置nocount。

set nocount on

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

这允许我使用一个存储过程，它使用R中的临时表。

#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:

问题出现在您的SQL语法中，而不是R或RODBC包固有的任何东西。我相当确定，您需要将SQL语句与go命令分开，以确保第一个语句在第二个、第三个等等之前完成执行。或者，您可以将它们分解为四个不同的语句，如下所示。这在我的机器上运行:

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:

将所有查询转换为list对象并对其进行迭代可以在将来节省一些代码。例如:

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.

这将生成一些您可能不关心的无关输出，但是您感兴趣的结果可以通过sqlOutput[[3]]提取，其中3表示感兴趣的查询。

#1