2 Replies Latest reply on Jul 24, 2009 9:11 AM by elizabethcombrink

    How to change the size of a NON-OOTB string attribute

    elizabethcombrink Employee

      I have found this works when I've set up an attribute to be an incorrect string sizeI have found this works when I've set up an attribute to be an incorrect string size


      ---See how large the attribute is currently in Object Designer (so use Touchpaper names)
      select md_title,md_max_length from md_attribute_type where md_database_table_guid IN(select md_guid from md_database_table where md_name = 'TABLE_NAME ie MyTable') and md_name = 'ATTRIBUTE_NAME ie Title'

       

      ---Change the attribute size in Object Designer(so use Touchpaper names)
      Update md_attribute_type set md_max_length = 255 where md_database_table_guid IN(select md_guid from md_database_table where md_name = 'TABLE_NAME ie MyTable') and md_name = 'ATTRIBUTE_NAME ie Title'

       

      ---Change the attribute size in SQL (so use the sql names)
      ALTER TABLE [dbo].[SQL_TableName ie usr.xxx] ALTER COLUMN [SQL_Attribute_Name, ie usr.xxx] varchar(255) NOT NULL;


      ---You also need to replace the attribute on all windows for it to use the new size

       

       

      CAVEAT:  Do not use this on an OOTB attribute, only on your custom attributes.  I have not tested it, but I have been told that an MDM will reset an OOTB attribute back to the OOTB value.  I suspect your data will be truncated and possibly corrupted.  I have however used this method successfully on my own custom attributes, and upgraded the database with no issues. – BUT DO YOUR OWN TESTING

        • 1. Re: How to change the size of a NON-OOTB string attribute
          Stu McNeill Employee

          Thanks Elizabeth but a couple of words of warning!

           

          Firstly this is not supported.  We can't help with any problems a customer has later on that turns out to be a result of manual changing of the database.

           

          Saying that though it's pretty safe on a user-generated attribute because the system won't care about changes you've made.

           

          The other issue is that the scripts posted above are missing a step.  The md_database_column table also needs updating to fully change the metadata and make the attribute totally upgrade safe.  On the md_attribute_type table there is an md_database_column_guid column, which links to md_guid on the md_database_column table.  You must find the relevant row for your attribute and change the md_column_size to match the max_length of the attribute table (or double the size for unicode attributes).

           

          Finally - NEVER attempt to change a system attribute's length.  Again its unsupported but it will also stop you being able to make further modifications to the object and upgrades will fail.  The way to tell if an attribute is system or user-generated is that its Name property will start with an underscore if it is user-generated (ie. _Title instead of just Title).

          • 2. Re: How to change the size of a NON-OOTB string attribute
            elizabethcombrink Employee

            Hi Stu - thanks very much.

             

            Can you perhaps raise an enhancement request for this?