255 character limit in custom fields. Is there a workaround? RRS feed

  • Question

  • I have created a custom field that stores categories in a specific way for my clients. It stores the field value as an SPFieldLookupValueCollection which is stored as a "#;" delimited string. It has recently come to my attention that all custom fields are limited to a maximum of 255 characters (a limitation I assume is due to excel or some other microsoft product defaulting the display of custom fields to a 'text' value type). This limitation is unacceptable -- am I missing something?

    Of note:
    *As long as the ParentType attribute in the fldtypes_*.xml file is anything besides 'text' the value will be truncated paste 255 characters. If it is set to 'text' it will throw an error on values longer than 255 characters.

    Monday, November 17, 2008 8:22 PM


  • Okay I've solved the problem.

    Apparently if you create a custom field with 'text' as its SQLType and then wish to change it to another (to avoid the 255 limitation) any item created with the old definition will retain the old sql type... If you were to create a new item after updating the sqltype everything will work properly, i.e., data will not be truncated. 

    Unfortunately it doesn't appear to be possible to update the SQLType for the old lists without modifying the database directly. For those interested in taking that route, run this query against your content database:
    SELECT tp_Fields 
    FROM Lists 
    WHERE tp_ID = 'your_list_guid' 

    This will give you all the FieldRef nodes for that list -- these nodes specify the title, properties and other important stuff for the fields in a list. Among the attributes in these nodes is "ColName"; this attribute defines what column the data is inserted into when the list is updated. Essentially you want this to be one of the unused ntext columns -- there are 32 total ntext columns which are named ntext1 - ntext32. Simply copy and paste the value from the select query into a text editor and swap the ColName attribute from 'nvarchar*' to 'ntext*' in the FieldRef node that you are concerned about. Once you have done that run this update query:
    UPDATE Lists
    SET tp_Fields = 'your modified string'
    WHERE tp_ID = 'your_list_guid' 

    You will then need to move the old columns' data to the new column you specified:
    UPDATE UserData 
    SET ntext* = nvarchar* 
    WHERE tp_ListId = 'your_list_guid' 

    The only potential issue with doing this that I can forsee is the possibility that there could be a collision when provisioning a future ntext column for a field in said list. I believe, however, that the column is decided on the fly by inspecting the current tp_Fields column versus some sequential mechanism.

    I hope I've wasted enough time for a few people out there!

    Note: ensure that you have updated (or removed) the SQLType attribute in your fldtypes_*.xml for the field in question first. Otherwise any new columns created in SharePoint will continue to be incorrectly set up.

    • Marked as answer by Steve Ruiz Monday, November 17, 2008 11:45 PM
    Monday, November 17, 2008 11:45 PM