cfquery truncates at 32000 chars

Just had an "interesting" problem with a CMS system. A client had entered a huge chunk of text which had saved in the MS SQL ntext field correctly, however when it was pulled out of the database (using cfquery) and displayed on the website, the text was being truncated. After some detective work I discovered that the text that was being shown on the webpage was exactly 32,000 characters long. I figured that was too much of a coincidence so a quick google search later I had my answer thanks to Joe Rinehart.

The solution is simple, in your CFIDE, find the offending datasource and in the "advanced settings" section, you'll see that you have a "Blob Buffer(bytes)" (as ntext is a blob). Just increase this value and the problem is solved.

Of course a better solution would be to suggest that the client might want to consider editing their text!

• Posted in:
• ColdFusion

10 comments

1. I've been bit by that before! Until you know what the problem is, it's a super pain to debug cause it seems like your data is just randomly being truncated.
   
   Comment by Ben Nadel – March 09, 2009
2. Yeah Ben, total pain to debug! At first I thought it was the data being sent to SQL Server, then SQL Server truncating it (and you can't do a simple Right() on an ntext field to check!), I got there eventually :)
   
   Comment by John Whish – March 09, 2009
3. Any idea if this affect nvarchar(max)?
   
   SQL 2005 has deprecated the NTEXT in favor of NVARCHAR(MAX).
   
   Comment by Henry Ho – March 09, 2009
4. @Henry,
   
   I believe this is truncation is a function of the database driver, not the database itself. I don't think it should be affected by the NVarChar.
   
   Comment by Ben Nadel – March 09, 2009
5. @Henry, there is another box in the CFIDE for Long Text Buffer (chr) which you'll probably need to adjust for an nvarchar data type (currently set at 64000).
   
   Comment by John Whish – March 10, 2009
6. We usually set the long text buffer to 128000 to allow for some bigger and longer texts (like discaimers and stuff). The bad thing about this datasource property is that with retrieval it gets cut off, but when storing a long text it us cut off as well. Bye, bye, hours of HTML-editing... >.<
   
   Comment by Sebastiaan – March 10, 2009
7. Just ran into this problem today and fixed it in about 5 minutes thanks to google and your post. Thank you for sharing your solutions I appreciate it.
   
   Comment by Ryan Knutson – April 30, 2009
8. Thanks for posting this - I thought it would take hours to track down why my images are only half there, but thanks to your posting it's fixed already.
   
   Comment by Simon Harper – October 13, 2009
9. Very usefull tip, thank you so much, this kind of bug is a nightmare to Debug.
   
   Comment by EB – April 21, 2011
10. yes, helpful,
    
    i just checked " -- Enable binary large object retrieval (BLOB)." and now its working
    
    Comment by prashant – September 22, 2011