Hi, everyone,
I got an error when I was using RODBC (or ODBC and DBI) to conduct SQL queries.
The error message is "Error in odbcQuery(channel, query, rows_at_time) :
'Calloc' could not allocate memory (214748364800 of 1 bytes)".
The funny thing is there is no issues when I query to 80% of my tables. The error only happens when I conduct queries in several table (small one). And also I will not get error if I conduct queries by joining the tables, though I will get an error if I just want to query data from the single table. Please have a look at my example
My code is listed below
rm(list=ls())
library("RODBC")
RODBC_connection <- odbcDriverConnect(paste('Driver={SQL Server};server=CA649028;database=CDNSW_Blank;trusted_connection=true', sep = ""))
Load data from SQL query
dt1 <- sqlQuery(channel=RODBC_connection, query = "SELECT * FROM country ")
dt2 <- sqlQuery(channel=RODBC_connection, query = "SELECT * FROM state")
Above two lines will give the error
Error in odbcQuery(channel, query, rows_at_time) :
'Calloc' could not allocate memory (214748364800 of 1 bytes)
dt2 <- sqlQuery(channel=RODBC_connection, query = "SELECT CountryName, StateName
From country c
LEFT JOIN state s ON s.CountryId = c.CountryId")
This query will success without any issue.
##########################################################
Query using odbc and DPI
library(dplyr)
library(dbplyr)
library(odbc)
library(DBI)
DBI_Connection <- dbConnect(odbc(),
driver = "SQL Server",
server = "CA649028",
database = "CDNSW_Blank"
)
dt1<- DBI::dbGetQuery(DBI_Connection, "SELECT * from country")
dt2<- DBI::dbGetQuery(DBI_Connection, "SELECT * from state")
*# Above two lines will give the error *
*Error in odbcQuery(channel, query, rows_at_time) : *
- 'Calloc' could not allocate memory (214748364800 of 1 bytes)*
dt3<- DBI::dbGetQuery(DBI_Connection, "SELECT CountryName, StateName
From country c
LEFT JOIN state s ON s.CountryId = c.CountryId")
This query will success without any issue.
Please help.
Thanks.