We need some MaxLength Inflation: Going beyond the 100k limit

Archive:  April 19, 2010

Microsoft Dynamics CRM is a metadata driven application built on top of Microsoft SQL Server.  In the current version of Microsoft Dynamics CRM, the data types are abstracted from the underlying SQL types.  This was done for many good reasons, most of them now legacy.  One of those abstracted types is the NTEXT field. 

 
In addition to basic data types, Microsoft CRM is the ability to put both UI and platform enforced limits on fields, including NTEXTs.  Unfortunately, the limit for NTEXT fields is restricted to 100,000 characters. 

 

First some history:

The truth of the matter is I am probably the one to blame for this!  You see, back in the day, when the Application Team had to enforce its own metadata (it’s a long story) via Form Properties we added a feature for MaxLength.  At the time, we were in a world of Internet Explorer 6 and the TextArea control did not support a maximum length property.  We therefore had to enforce it via JavaScript.  The problem was that the JavaScript engine had a very inefficient internal routine for counting characters and such, the validation would get slower and slower, the more text the user put in the TextArea control.  In fact, with a few hundred thousand characters the browser would “freeze” for 5 or more seconds while JavaScript tried to count the characters and the form would become unusable.  The decision was made to just limit customizers to some “reasonable” number.  And so it was.  100k was deemed to be good enough and so it was coded and thus it was shipped.  That limit was later passed from the application layer into the platform layer in version 3.0, the same limit exists in version 4.0 and remains today in betas of CRM 5.0.

So what is an XRM developer to do?

Hack #1 (doesn’t work):

The first and most logical attempt I made to hack around this limit was to simply tell the metadata layer that the field could have a max length of more than 100,000 characters.  I did this by updating the MetadataSchema.Attribute table and setting the MaxLength.  Unfortunately, this did not work as the Metadata cache internally validates that the MaxLength for a NTEXT not be larger than the hard-coded “MemoMaxLength” CONST of 100,000!

 

Since that didn’t work I proceeded to look for another solution.

Hack #2 (works):

SQL Script – Run against your ORG_MSCRM database

BEGIN TRAN -- Convert a field to an email body and thus remove the -- limit check in the platform UPDATE MetadataSchema.Attribute SET MaxLength = 1073741823, AttributeLogicalTypeId = 'emailbody' WHERE [name] in ( 'ac_xmlblob', 'ac_htmlblob') COMMIT TRAN

After running the script, run an IISRESET to flush the MD cache.  Hack #2 converts the field into an “email body” (a feature Microsoft should add as a standard feature anyway) and thus the size restriction is ignored at both the Application and Platform levels.  Your mileage may of course vary.

If you are in the CRM 5.0 CTP program, please take a moment and vote up my request to have this limit raised:

https://connect.microsoft.com/site687/feedback/details/552141/ntext-limited-to-100-000-characters

 

Enjoy,

This posting is provided "AS IS" with no warranties, and confers no rights.

posted at: 9:12 PM by Aaron Elder

Comments

[coach handbags]

In your blog I feel your enthusiasm for life. Thank you for sharing ! I learned a lot! May the joy and happiness around you today and always.By the way，Do you like coach handbags？ It will make your life different!

[Guru]

Hi,

Thanks for the great job...

I have updated the metadata attribute table using this script.

It's working fine in some scenario's.
It's not at all giving error.

But storing blank data in the DB when the size limit exceeds..

It's not storing the exact result.

Thanks & Regards
Guru Prasad