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!