wrapper function for DBI /SQL Server that actually works - via https://stackoverflow.com/a/61073561/5175413

sqlFetchData.R

sqlFetchData <- function(connection, database, schema, table, nobs = 'All') {
  
  #'wrap function to fetch data from SQL Server
  #
  #@ connection: an established odbc connection
  #@ database: database name
  #@ schema: a schema under the main database
  #@ table: the name of the data table to be fetched. 
  #@ nobs: number of observation to be fetched. Either 'All' or an integer number. 
  #        The default value is 'All'. It also supports the input of 'all', 'ALL' and
  #        etc. . 
  
  if (is.character(nobs)) {
    if (toupper(nobs) == 'ALL') {
      obs_text <- 'select'
    } else {
      stop("nobs could either be 'ALL' or a scalar integer number")
    }
  } else {
    if (is.integer(nobs) && length(nobs) == 1) {
      obs_text <- paste('select top ', nobs, sep = '')
    } else {
      stop("nobs could either be 'ALL' or a scalar integer number")
    }
  }
  
  initial_sql <- paste("select * from ", database, '.', schema, ".", table, 
                       sep = '')
  dbquery <- dbSendQuery(connection, initial_sql)
  cols <- dbColumnInfo(dbquery) 
  dbClearResult(dbquery)
  
  #' sort the rows by query type due to error message:
  #' Invalid Descriptor Index 
  
  colInfo <- cols
  colInfo$type <- as.integer(colInfo$type)
  cols_neg <- colInfo[which(colInfo$type < 0), ]
  cols_neg <- cols_neg[order(cols_neg[, 2]), ]
  cols_pos <- colInfo[which(colInfo$type >= 0), ]
  cols_pos <- cols_pos[order(cols_pos[, 2]), ]
  cols <- rbind(cols_pos, cols_neg)
  
  add_comma <- "c(cols$name[1], paste(',', cols$name[-1L], sep = ''))"
  
  sql1 <- paste(c(obs_text, eval(parse(text = add_comma))),
                collapse = ' ', sep = '')
  data_sql <- paste(sql1, ' from ', database, '.', schema, '.', table, 
                    sep = '')
  
  dataFetch <- dbGetQuery(connection, data_sql)[, colInfo$name]
  return(dataFetch)
}

NB - pass in the variables as character, assuming 'con' has been specified before, e.g. sqlFetchData(con,"database","dbo","tablename") and don't forget to pipe to dplyr::filter to ensure you don't return an entire table by default