r - 从 R 执行时 SQL Server 查询失败

Question

我有以下简单的 SQL 服务器代码:

set nocount on;
if OBJECT_ID('tempdb..#A') IS NOT NULL DROP TABLE #A;

set nocount on;
create table #A
( obj_id int,
  obj_name varchar(50),
  obj_dt   datetime);

  insert into #A (
  obj_id,
  obj_name,
  obj_dt)
  values
  ( 1
   ,'name'
   ,'2019-01-01 00:00:00'
  ),
  ( 2
   ,NULL
   ,NULL
  ),
  ( 2
   ,'alias'
   ,'2019-02-01 00:00:00'
  );

set nocount on;
if OBJECT_ID('tempdb..#B') IS NOT NULL DROP TABLE #B;

set nocount on;
select 
 #A.obj_id
,subq.obj_name
,subq.obj_dt
into #B 
from #A
join (select
        obj_id,
        max(obj_name) as obj_name,
        max(obj_dt) as obj_dt
      from #A
      group by obj_id) as subq
on #A.obj_id = subq.obj_id;

set nocount on;
select * from #B;

正如预期的那样，它在 Microsoft SQL Server Management Studio 中执行时返回以下数据:

obj_id  obj_name    obj_dt
1       name        2019-01-01 00:00:00.000
2       alias       2019-02-01 00:00:00.000
2       alias       2019-02-01 00:00:00.000

到现在为止还挺好。现在我希望从 R 运行此代码并将相同的输出返回给 R。我将上面的查询存储在字符串 query 中。和我的 RODBC连接存储在变量 connection 中，并尝试检索数据

sqlQuery(connection,query)

结果是 character(0) .但是，如果我通过注释掉 subq.obj_name 来修改上面的查询和 subq.obj_dt #B 定义中的字段然后代码成功返回预期的数据集

obj_id
1      1
2      2
3      2

来自 R。

那么这里发生了什么？两个 sql 查询都有效并且在 Microsoft SQL 服务器环境中成功运行，但只有一个在通过 R 管道传输时有效。我无法弄清楚是什么原因导致 RODBC 代码无法处理第二个查询。

Answer 1

这是关于本地临时表( #mytable 而不是 ##mytable )的一个已知问题，不仅在 R 中，而且在临时表的所有外部调用中，甚至在 SSMS 等 Microsoft 工具中也是如此(请参阅下面第一个链接的评论)。

看看这些链接:

https://github.com/r-dbi/odbc/issues/127特别是 https://github.com/r-dbi/odbc/issues/127#issuecomment-396343426

RODBC Temporary Table Issue when connecting to MS SQL Server

阅读这些链接后，奇怪的是它在没有 subq.obj_name 的情况下也能工作。和 subq.obj_dt : 也许它有效，因为查询是在一个独特的调用中。