连接到 MS SQL Server 时的 RODBC 临时表问题 [英] RODBC Temporary Table Issue when connecting to MS SQL Server

问题描述

我在 unix 上运行 R，我使用 RODBC 包连接到 MS SQL 服务器.我可以执行一个查询，该查询返回的结果很好，但是如果我在 SQL 查询中的某处使用临时表，则会向我返回一个空字符串.在浏览网页后，我认为问题可能在于 RODBC 包是假设最终用户使用标准 SQL(而不是 MS SQL)编写的.我提供了以下代码作为示例.

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.

有趣的是，如果我使用 RJDBC 包，则不存在临时表问题.然而，RJDBC 包即使导入 80,000 行(10 列)也非常缓慢，并且会经常停顿，所以这也不是一个选项.有没有其他人遇到过这个问题?如果有我没有想到的替代解决方案，我很乐意听取他们的意见.

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.

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

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 示例:

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)

推荐答案

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

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

这允许我使用在 R 中使用临时表的存储过程.

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

这篇关于连接到 MS SQL Server 时的 RODBC 临时表问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！