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

Question

I am using the RODBC package to query a text column from a database. The database is built on Microsoft SQL Server 2008 R2. The data type of the column in SQL is nvarchar(max).

However, when I run:

# Set up ODBC connection to CCWEB5 production server
# Note: default database is set to "CCSalary"
ccweb5.prod <- odbcConnect("ccweb5")

# Read in some job ad text
job.text <- sqlQuery(ccweb5.prod,"
  SELECT TOP 100
    ja.JobTitle,
    ja.JobText as 'JobText',
    LEN(ja.JobText) as 'JobTextLength'
  FROM JobStore.dbo.JobAd as ja (NOLOCK)
")

Within SQL, I am expecting (for the top row):

JobTitle                     JobText              JobTextLength
IT Field Service Technician  <text goes here...>  2742

However, when I do: nchar(as.character(job.text[1,2]))

It returns: 255.

So my question is, what is causing this truncation and how do I avoid it? Thanks!!

Brassware

@logworthy Yes, the question you linked is a duplicate of this one; I asked this 4+ years ago while the one you linked is only ~1 year old. – Brassware

Answer 1

OK, so it seems that I have found a work-around to this. After some more Google'ing, I found that:

One thing to consider with the SQL Native Client ODBC driver is that VARCHAR(MAX) has does not have fixed size and the ODBC driver represents this by returning a max column size of 0. This can confuse your application if it doesn't check for 0 as a special case. See the bottom section of this article: http://msdn.microsoft.com/en-us/library/ms130896.aspx But in general I have not seen this happen with any of my .NET applications as it is handled properly in ADO.NET.

Source: http://bytes.com/topic/sql-server/answers/808461-cannot-read-varchar-max

So, in my case, the following did the trick:

job.text <- sqlQuery(ccweb5.prod,"
  SELECT DISTINCT TOP 100
    ja.JobTitle,
    [JobText] = CAST(ja.JobText AS varchar(8000)), -- note the data-type re-cast
    [JobTextLength] = LEN(ja.JobText)
  FROM JobStore.dbo.JobAd as ja (NOLOCK)
")

Such that nchar(as.character(job.text[1,2])) now returns 2742 (as it should).

I didn't see any similar questions on StackOverflow so I'll leave this up. Hope this helps somebody!

Brassware

Answer 2

A solution would be to cast the nvarchar(max) field to ntext

job.text <- sqlQuery(ccweb5.prod,"
  SELECT TOP 100
    ja.JobTitle,
    CAST(ja.JobText AS ntext) as 'JobText',
    LEN(ja.JobText) as 'JobTextLength'
  FROM JobStore.dbo.JobAd as ja (NOLOCK)
")

Maite