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

Question

I am using the RODBC package to query a column of text from a database. The database is built on Microsoft SQL Server 2008 R2. The column data type 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) ") 

In SQL, I expect (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 causes this truncation and how to avoid it? Thanks!!

Answer 1

Good, so it seems that I have found a workaround for this. After a few more steps by Google, I found that:

One thing to keep in mind with the ODBC driver for the native SQL client is that VARCHAR (MAX) is not fixed in size and the ODBC driver represents this by returning a maximum column size of 0. This may confuse your application if it does not check 0 as a special case. See the bottom of this article: http://msdn.microsoft.com/en-us/library/ms130896.aspx But in general, I have not seen how this happens with any of my .NET applications, since it is correctly processed in ADO .NET

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

So, in my case, the following performed 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) ") 

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

I have not seen such questions in StackOverflow, so I will leave this out. Hope this helps someone!

Answer 2

The solution is 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) ")