我正在使用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.
所以我的问题是,导致这种截断的原因是什么,我该如何避免呢？谢谢！！
解决方法
好的,所以我似乎找到了解决方法.经过一些谷歌,我发现：
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:
07000 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上看到任何类似的问题所以我会把它留下来.希望这有助于某人！
