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

[Philippi, Tom]

memo fields are an issue for MS ODBC and MS Access or SQL Server,
especially with sqlSave() creating or appending to tables.  ODBC reports
them as 1G 2-byte characters per value, VARCHAR with length of 1070M or so.
 As far as I know, this is an all-MS issue, between their ODBC and their
databases, and on most if not all platforms.

sqlColumns(mdb,table) is another way to accomplish what you do with
x <- sqlQuery(con, paste("exec sp_columns", tables[i], sep=" ")  )
x <- sqlColumns(con,tables[i])

I may be brain-dead, but I don't think you need the which() in your
subsetting:
drop.cols <- as.character( x[which(x$LENGTH > 8000),]$COLUMN_NAME )
drop.cols <- as.character( x$COLUMN_NAME[x$LENGTH > 8000] )


Tom 2


On Wed, Jul 22, 2015 at 10:41 PM, Brad P <bpschn01 using gmail.com> wrote:

> Sorry if replying to this is not OK.
>
> I now see that RODBC can not handle cases where length is huge, evidently >
> 8000, see this:
>
>
> http://stackoverflow.com/questions/18477994/rodbc-does-not-save-greater-than-8k-varchar-text-from-a-data-frame
>
> I also noticed that one column in the table previously mentioned had a
> length of  2147483647
>
> Anyway, my goal was to pull all tables in and saving them as .rds files.
> I wrote a quick work around that identifies which tables have these long
> lengths and selects all columns but those columns. This is not ideal, but
> at least I get most of the data.
>
> ##################################
> # get list of all table names
> tables <- sqlQuery(con, "SELECT * FROM information_schema.tables")
> tables <- subset(tables, subset=TABLE_TYPE!="VIEW")
> tables <- as.character(tables$TABLE_NAME)
>
> # for loop to pull in tables 1 at a time
> for(i in 1:length(tables) ){
>   x <- sqlQuery(con, paste("exec sp_columns", tables[i], sep=" ")  )
>   if(length(which(x$LENGTH > 8000))>0){
>      drop.cols <- as.character( x[which(x$LENGTH > 8000),]$COLUMN_NAME )
>      print ( paste(tables[i], "had", paste(drop.cols, collapse=","),
> "column(s) dropped", sep=" ") )
>      keep.cols <- as.character( x[which(x$LENGTH < 8000),]$COLUMN_NAME )
>      dat <- sqlQuery(con, paste( "SELECT", paste(keep.cols, collapse=","),
> "FROM", tables[i], sep=" ") )
>   }else{
>     dat <- try( sqlFetch(con, tables[i]), silent=F)
>   }
>   print(i)
>   print( paste(tables[i]) )
>   print( object.size(dat),  units = "auto" )
>   saveRDS(dat, paste(tables[i], "rds", sep=".") )
> }
> ##################################
>
> ##################################
>
> Cheers!
> Patrick
>
>
> On Wed, Jul 22, 2015 at 8:50 PM, Brad P <bpschn01 using gmail.com> wrote:
>
> > 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]]
>
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB using r-project.org
> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>

	[[alternative HTML version deleted]]