RODBC and Microsoft SQL Server: Truncating Long Character Strings

user_2478949 0
Viewed 2623

I am trying to query a variable from a Microsoft SQL Server database using R/RODBC. RODBC is truncating the character string at 8000 characters.

Original code: truncates at 255 characters (as per RODBC documentation) library(RODBC) con_string <- odbcConnect("DSN") query_string <- "SELECT text_var FROM table_name" dat <- sqlQuery(con_string, query_string, stringsAsFactors=FALSE)

Partial solution: modifying query string truncate text after 7999 characters. library(RODBC) con_string <- odbcConnect("DSN") query_string <- "SELECT [text_var]=CAST(text_var AS VARCHAR(8000)) FROM table_name" dat <- sqlQuery(con_string, query_string, stringsAsFactors=FALSE)

The table/variable contains text strings at long as 250,000 characters. I really want to work with all the text in R. Is this possible?

@BrianRipley discusses the problem (but no solution) on page 18 of following document: https://cran.r-project.org/web/packages/RODBC/vignettes/RODBC.pdf

@nutterb dicusses similar issues with RODBCext package on GitHub: https://github.com/zozlak/RODBCext/issues/6

Have seen similar discussion on SO, but no solution using RODBC with VARCHAR>8000.

RODBC sqlQuery() returns varchar(255) when it should return varchar(MAX)

RODBC string getting truncated

Note:

  • R 3.3.2
  • Microsoft SQL Server 2012
  • Linux RHEL 7.1
  • Microsoft ODBC Driver for SQL Server
rodbc rhel truncate r sql-server
2 Answers

Maybe this has changed recently, but with the following query format getting varchar(MAX) fields longer then 8000 character can be queried from SQL Server into R

query_string <- "SELECT CAST(text_var AS text) AS text_var FROM table_name"
user_92714760
Related