sql - RODBC sqlQuery() 在应该返回 varchar(MAX) 时返回 varchar(255)

Question

我正在使用 RODBC 包从数据库查询文本列。该数据库基于 Microsoft SQL Server 2008 R2 构建。 SQL 中列的数据类型为 nvarchar(max)。

但是，当我运行时:

# 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)
")

在 SQL 中，我期望(对于顶行):

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

但是，当我这样做时:nchar(as.character(job.text[1,2]))

它返回:255。

所以我的问题是，是什么导致了这种截断以及如何避免它？谢谢!!

Answer 1

好的，看来我已经找到了解决方法。经过更多的谷歌搜索，我发现:

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.

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

所以，就我而言，以下方法达到了目的:

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)
")

这样 nchar(as.character(job.text[1,2])) 现在会返回 2742(正如它应该的那样)。

我在 StackOverflow 上没有看到任何类似的问题，因此我将保留此问题。希望这对某人有帮助!