RODBC游标查询

Question

我是R的新手-但我希望跨SQL server上的多个数据库执行SQL Server游标查询，并将结果存储在数据帧中-我已连接到服务器，并且我有一个包含SQL的单独文件-

SQL (cursor_query.sql)

CREATE TABLE #tempRPT
(date DATE, 
 number  INT,
 type nvarchar(255)
);
DECLARE @sql NVARCHAR(4000);
DECLARE @DBNAME NVARCHAR(255);
DECLARE dbcursor CURSOR STATIC
FOR SELECT [name]
    FROM sysdatabases; 
OPEN dbcursor;
FETCH NEXT FROM dbcursor INTO @DBNAME;
WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = 'USE  ['+@DBNAME+']; 
        select
cast(startdatetime as date),
count(*),
''bookings''
from bookings
group by cast(startdatetime as date);'
        INSERT INTO #tempRPT
        EXEC (@sql);
        FETCH NEXT FROM dbcursor INTO @DBNAME;
    END;
CLOSE dbcursor;
DEALLOCATE dbcursor;
SELECT *
FROM #tempRPT;
DROP TABLE #tempRPT;

我试着用下面的代码把它读到R中-

Results <-
  sqlQuery(
    odbcConnect(dsn = "Database", uid = "***", pwd = "***"),
    query = readLines("cursor_query.sql", warn=FALSE)
  )

但我没有得到任何结果-有什么想法或建议如何做到这一点？

Answer 1

RODBC queries在每个sqlQuery调用中只支持一条SQL语句。您尝试的过程查询由多条语句组成。此外，您正在运行带有循环的动态SQL，R可以在没有临时表的情况下为您做两件事。

考虑检索所有数据库名称，然后针对聚合查询遍历每个数据库名称。注意使用句点限定符从同一台服务器上的其他数据库进行查询。最后，将所有对象连接到最终的单个数据帧对象。

conn <- odbcConnect(dsn = "Database", uid = "***", pwd = "***")

db_names <- sqlQuery(conn, "SELECT [name] FROM sysdatabases")

df_list <- lapply(db_names$name, function(db) 
         sqlQuery(conn, paste0("select
                                  cast(startdatetime as date) as start_date,
                                  count(*) as [count],
                                  'bookings' as [indicator]
                                from [", db, "]..bookings
                                group by cast(startdatetime as date)")
         )
)

final_df <- do.call(rbind.data.frame, df_list)

odbcClose(conn)

Answer 2

批处理开始处的SET NOCOUNT ON。在DML语句之后发送的行数消息必须由客户端在读取结果集之前进行处理。SET NOCOUNT ON禁止显示这些消息，因此客户端通常无法区分单语句SELECT批处理和单语句SELECT作为其中一条语句的多语句批处理。