Error in conducting SQL queries using RODBC or ODBC with DBI

0
votes

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.

odbcrstudiodbirodbc