Query SQL Server from R with ETLUtils for big tables

Question

Normally to query a sql-server database from R, I'd use:

library(RODBC)
con <- odbcConnect(dsn = "ESTUDIOS", uid = "estudios", pwd = "yyyy")
sql_trx <- "SELECT [Fecha], [IDServicio]
            FROM [ESTUDIOS].[dbo].[TRX] where MONTH(Fecha) = MONTH('2016-08-01') and YEAR(Fecha) = YEAR('2016-08-01');"
    trx.server <- sqlQuery(channel = con, sql_trx)
    odbcClose(con)

But when the table of the database is too big, I could the use the libraries: ff and ETLUtils.

So, the normal thing to do must be:

library(RODBC)
library(ff)
library(ETLUtils)  

sql2_trx <- read.odbc.ffdf(query = sql_trx, odbcConnect.args = list(con))

But this doesn't give me the desired result, instead this returned the following error.

1: In RODBC::odbcDriverConnect("DSN=11") :
  [RODBC] ERROR: state IM002, code 0, message [Microsoft][Administrador de controladores ODBC] No se encuentra el nombre del origen de datos y no se especificó ningún controlador predeterminado
2: In RODBC::odbcDriverConnect("DSN=11") : ODBC connection failed

Can you point out what is wrong with the use of read.odbc.ffdf ?

Answer 1

Currently you are passing what seems to be the previous RODBC connection object, con, into read.odbc.ffdf() but remember the method is attempting to create an ODBC connection and call a query. The R docs mention the proper assignment of odbcConnect.args:

odbcConnect.args a list of arguments to pass to ODBC's odbcConnect (like dsn, uid, pwd)

Consider passing your original DSN and credentials like you did in regular RODBC connection:

sql2_trx <- read.odbc.ffdf(query = sql_trx, odbcConnect.args = list(dsn = "ESTUDIOS", uid = "estudios", pwd = "yyyy"))