RODBC Cursor Queries

Question

Im new to R - but im looking to execute an SQL server cursor query across multiple databases on an SQL Server and store the results in a dataframe - I have connected to the server and I have a separate file with the 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;

im trying to read this into R with the following -

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

But I dont get any results - any ideas or a suggestion of how to do this?

Answer 1

RODBC queries supports only one SQL statement per sqlQuery call. Your attempted procedural query consists of multiple statements. Additionally, you are running dynamic SQL with loops, two things R can do for you without temp tables.

Consider retrieving all database names then iterate across each for your aggregate query. Notice the use of period qualifiers to query from other databases on same server. Finally, concatenate all to a final, single data frame object.

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)