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

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!

Tags:

Sql

Sql Server

R