[R-sig-DB] RODBC Error when fetching tables: 'Calloc' could not allocate memory

[Brad P]

Hello,

I have used R for a while in Linux, but am trying to become familiar with
using R in Windows as well as using MS SQL and the RODBC package. I have a
problem described below when trying to fetch SOME tables.

I am using:

OS:
Windows 7 64-bit
Intel quad i5 650 using 320GHz
12GB RAM

R:
Rstudio Version 0.99.467
Revolution R Open 3.2.0
Using CRAN snapshot taken on 2015-05-01

> sessionInfo()
R version 3.2.0 (2015-04-16)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 7 x64 (build 7601) Service Pack 1

locale:
[1] LC_COLLATE=English_United States.1252  LC_CTYPE=English_United
States.1252
[3] LC_MONETARY=English_United States.1252 LC_NUMERIC=C

[5] LC_TIME=English_United States.1252

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base

other attached packages:
[1] SOAR_0.99-11 pryr_0.1.2   RODBC_1.3-12

loaded via a namespace (and not attached):
[1] magrittr_1.5     tools_3.2.0      Rcpp_0.11.6      stringi_0.5-5
[5] codetools_0.2-11 stringr_1.0.0

> Sys.getlocale()
[1] "LC_COLLATE=English_United States.1252;LC_CTYPE=English_United
States.1252;LC_MONETARY=English_United
States.1252;LC_NUMERIC=C;LC_TIME=English_United States.1252"

SQL:
MS SQL server 2012
Example database: AdventureWorksDW2012


# Here is some code and the problem I am having:

library(RODBC)
con <- odbcDriverConnect('driver={SQL Server}; server=SQLEXPRESS;
Database=AdventureWorksDW2012; uid=BPS; trusted_connection=true')


# The problem is that some tables are fetched OK, while some tables lead to
the following error:

# increasing memory
memory.limit(size=10000000000)
round(memory.limit()/2^30, 2)
[1] 9.31 # GB


dat <- sqlFetch(con, DimEmployee)
Error in odbcQuery(channel, query, rows_at_time) :
  'Calloc' could not allocate memory (214748364800 of 1 bytes)

traceback()
4: .Call(C_RODBCQuery, attr(channel, "handle_ptr"), as.character(query),
       as.integer(rows_at_time))
3: odbcQuery(channel, query, rows_at_time)
2: sqlQuery(channel, paste("SELECT * FROM", dbname), ...)
1: sqlFetch(con, DimEmployee)


# Here are 2 examples of databases that can be fetched and can not:

# DimAccount does work, here is some info regarding its size:
sqlQuery(con, "exec sp_spaceused DimCustomer")
         name  rows reserved     data index_size unused
1 DimCustomer 18484 13608 KB 12552 KB     920 KB 136 KB

# DimEmployee (example given above) does not work, here is some info
regarding its size:
sqlQuery(con, "exec sp_spaceused DimEmployee")
         name rows reserved     data index_size unused
1 DimEmployee  296 18992 KB 18856 KB      48 KB  88 KB


# 4 of 31 tables in this SQL database give this exact error when attempting
to fetch them as shown above, the rest are fetched without error.

# Also, please know that when I run this in regular 64-bit R (not
Revolution R) it does the same thing, and when I do it in 32-bit R, it
literally crashes

Please let me know if you have any suggestions.
-Patrick

	[[alternative HTML version deleted]]