RODBC Cannot allocate memory

Question

Simple R script

library(RODBC)
odbChannel <- odbcConnect(dsn = "CTPRD03", uid = "BD_RPT_RO", pwd = "****")
df.test <- sqlQuery(channel = odbChannel, query = "select * from DUAL;")
df.test
close(odbChannel)

Produces the following error

shiny@narc07shiny1dev:~/software> Rscript ./RODBC_SIMPLE_TEST.r
Error in odbcQuery(channel, query, rows_at_time) :
'Calloc' could not allocate memory (18446744073709551616 of 22816 bytes)
Calls: sqlQuery -> odbcQuery -> .Call
Execution halted
Warning message:
closing unused RODBC handle 1

Tested odbc outside of R and was able to get results. I'm not sure where the problem is at this point. I'm thinking it is RODBC.

Already uninstalled RODBC and reinstalled the RODBC package and there were no errors during that process, but still have the same results.

Found one case out there with the same problem but there wasn't any resolution. What would be next steps in isolating the problem, any suggestions?

Answer 1

I had a similar problem and fixed by adding rows_at_time = 1 to the connection.

odbChannel <- odbcConnect(dsn = "CTPRD03", uid = "BD_RPT_RO", pwd = "****", rows_at_time = 1)

I also found this blurb in the documentation:

https://cran.r-project.org/web/packages/RODBC/RODBC.pdf

Several errors which have been reported as bugs in RODBC 1.3-0 which were in fact ODBC driver errors that can be circumvented by setting rows_at_time = 1 (and the warning under that argument has always been there). The drivers involved have been third-party Oracle drivers and old SQL Server drivers.

Answer 2

I kept having this problem for large tables, worked perfectly for smaller tables but not more than 10,000 rows. I was using: con <- odbcDriverConnect ("driver={SQL Server}; server=SERVERName; database=dBName; uid=Name; pwd=password")

I simply changed the driver from 'SQL Server' to 'ODBC Driver 17 for SQL Server' and now I can work with table of any size.