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.
