RODBC using EXEC @variable query

Question

I working with a group of databases with several projects of the same structure on each database. My goal is to query the master database on each server and pull the same information from all projects to aggregate and perform analysis. In SQL Server I have put together a working query (example below) which loops through all of the projects returning the same information:

Declare @DBNames Table (RN int, Name varchar(max))
Insert Into @DBNames (RN, Name)
Select Row_Number() Over (Order By Name Desc) as RN, 
        Name 
From sys.databases 
Where  (Name = 'proj1' or
        Name = 'proj2' or
        Name = 'proj3')
    and state_desc = 'Online'

-- sql part 1a
DECLARE @sql1 varchar(max) = 
                                 '
select  ''a.colA
        ,b.colB
        ,c.colC

from '

-- sql2 part 
Declare @SQL2 varchar(max) = '.dbo.tableA as a
    inner join '

-- sql3 part 
Declare @SQL3 varchar(max) = '.dbo.tableB as b on b.x = a.x
    inner join '

-- sql4 part 
Declare @SQL4 varchar(max) = '.dbo.tableC as c on c.y = b.y
where col1 is not null' 

Declare @SQL varchar(max)
Select @SQL = (
                                 Select Stuff((@SQL1 + Name + @SQL2 +  NAME + @SQL3 + Name + @SQL4 + Case When RN = 1 Then '' Else ' Union ' End), 1, 0, '') as SQL
                                 From @DBNames Order By RN Desc
                                 for XML Path(''), TYPE).value('.','varchar(max)')

                               exec(@sql)

When I try to load the data using the above query in RODBC, it doesn't seem to work. Does RODBC support this type of query? If not, what might be a good alternative?

Thank you in advance for your time and patience!

Place all this in a stored procedure and then have R call it. — Parfait
– Parfait, Commented Apr 1, 2020 at 1:19
Some drivers don't like rowcount messages coming before resultsets. So put set nocount on at the top of the batch. — David Browne - Microsoft
– David Browne - Microsoft, Commented Apr 1, 2020 at 2:49

Parfait · Accepted Answer · 2020-04-01 01:39:24Z

Essentially, you are building a dynamic query in TSQL using procedural semantics. But R is a procedural language. Hence, consider looping through databases to build concatenated data frame (counterpart of UNION).

library(RODBC) 

sql <- "select a.colA, b.colB, c.colC 
        from dbo.tableA as a 
        inner join dbo.tableB as b on b.x = a.x 
        inner join dbo.tableC as c on c.y = b.y 
        where col1 is not null"

get_db_data <- function(db) {
     # DYNAMICAL DATABASE CONNECTION
     conn <- odbcDriverConnect(paste0("Driver={SQL Server};",
                                      "Server=myserver;Database=", db,
                                      ";Trusted_connection=Yes"))

     df <- sqlQuery(conn, sql)

     odbcClose(conn)

     return(df)
}

# BUILD LIST OF DFs FROM DIFFERENT DBs
df_list <- lapply(c("proj1", "proj2", "proj3"), get_db_data)

# EQUIVALENT TO UNION QUERY
final_df <- unique(do.call(rbind, df_list))

Collectives™ on Stack Overflow

RODBC using EXEC @variable query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related