Error when attempting to set Sub-Data Type to HTML

Version 7

    Verified Product Versions

    LANDESK Service Desk 7.7.xLANDESK Service Desk 7.8.xLANDESK Service Desk 2016.xLANDESK Asset Central 2016.x

    Problem:

    Error when attempting to set Sub-Data Type to HTML

     

    Description:

    When attempting to set an attribute Sub-Data Type to HTML an Error is displayed  - Cannot create a record with name 'usr_description_html'. This is already in use by another record. (For this example, we are using the Description attribute.)

     

    Explanation:

    When a Sub-Data type is selected, a column in the database is created to allow for the use of HTML as a Sub-Data type. When this is switched back to NONE, the table retains the column instead of removing it, thus causing an issue if you try to enable the Sub-Data type again.

     

    Solution:

     

    BACK UP YOUR DATABASE BEFORE RUNNING THESE SQL STATEMENTS.
    ***IF YOU ARE USING 2017.1***

    This script corrects missing HTML metadata that is removed by the MDM when an attribute that has previously been enabled for HTML has it's sub data type set to none; preventing the user from re-enabling HTML on the attribute.

     

    --=================================
    -- *** Author : MSR ***
    -- *** Created : 2017/08/11 ***
    --=================================
    
    
    -- This script corrects missing HTML metadata that is removed by the MDM when an attribute 
    -- that has previously been enabled for HTML has it's sub data type set to none; preventing 
    -- the user from re-enabling HTML on the attribute.
    /*****************************************************************************************/
    
    
    /*
    select * from md_database_column dc
    join md_database_table dt on dc.md_database_table_guid=dt.md_guid
    join md_attribute_type at on at.md_database_table_guid=dt.md_guid and at.md_name=substring(dc.md_name, 5, len(dc.md_name)-9)
    where (dc.md_name like 'ubr_%_html' or dc.md_name like 'usr_%_html')
    and (at.md_data_type = 2 and at.md_sub_data_type=0 and at.md_database_column2_guid is null)
    */
    SET NOCOUNT ON
    
    
    update at
    set at.md_database_column2_guid=dc.md_guid
    from md_database_column dc
    join md_database_table dt on dc.md_database_table_guid=dt.md_guid
    join md_attribute_type at on at.md_database_table_guid=dt.md_guid and at.md_name=substring(dc.md_name, 5, len(dc.md_name)-9)
    where (dc.md_name like 'ubr_%_html' or dc.md_name like 'usr_%_html')
    and (at.md_data_type = 2 and at.md_sub_data_type = 0 and at.md_database_column2_guid is null)
    

    If you are experiencing this problem, please log this with your Support Provider referencing problem 6779

     

     

    ***IF YOU ARE USING LDSD 7.7.x - 2016.x***
    You will need to find the tables that contain the column causing errors and remove the columns from the affected tables.

    It is suggested that you contact support before attempting this within your environment as the scripts below will need to be modified.

    The scripts below can be used to identify the tables that contain the column.

     

    1. Remove the linking rows from within md_database_column where the md_name is usr_description_html:

     

    select md_name from md_database_table where md_guid in (select md_database_table_guid from md_database_column where md_name like 'usr_description_html')

     

    2. Once the linking rows are removed, clear the columns on the tables that had usr_description_html column within them using the following script to find them:

     

    delete from md_database_column where md_name like 'usr_description_html'

     

    See results below - Listed Results are the tables with the columns present:

    SubDataTypeHTML.JPG

     

    3. Once identified, delete the columns from our results:

     

              alter table pm_process 
              drop column usr_description_html 
    
              alter table km_article 
              drop column usr_description_html
     

     

    4. Once this was successful, run an MDM upgrade via Configuration center -> Framework-> Upgrade. After it completes successfully, validate that you can then set the SubData_Type to HTML on the Process Management -> Description attribute.

     

     

    We do have a generic script that will run through the database and correct any occurrences of this issue. Please see below:

     

    -- script to clean out old HTML metadata and table data from previously HTML enabled attributes
    
    
    SET NOCOUNT ON
    
    
    declare @attGuid uniqueidentifier
    declare @colGuid uniqueidentifier
    declare @attName nvarchar(200)
    declare @tableName nvarchar(200)
    declare @colName nvarchar(200)
    declare @sql nvarchar(500)
    
    
    declare attCursor cursor for
      select t1.md_guid, t3.md_title + ' -> ' + t2.md_title + ' -> ' + t1.md_title,
      t4.md_name, t1.md_database_column2_guid, t5.md_name from md_attribute_type t1
      join md_class_type t2 on t1.md_class_type_guid = t2.md_guid
      join md_module t3 on t2.md_module_guid = t3.md_guid
      join md_database_table t4 on t1.md_database_table_guid = t4.md_guid
      join md_database_column t5 on t1.md_database_column2_guid = t5.md_guid
      where t1.md_data_type = 2 and t1.md_sub_data_type = 0
      and t1.md_database_column2_guid is not null
    
    
    open attCursor
    fetch next from attCursor into @attGuid, @attName, @tableName, @colGuid, @colName
    while (@@FETCH_STATUS = 0)
    begin
      print 'Fixing ' + @attName + ' attribute...'
      update md_attribute_type set md_database_column2_guid = null
      where md_guid = @attGuid
      delete from md_database_column where md_guid = @colGuid
    
    
      set @sql = 'alter table ' + @tableName + ' drop column ' + @colName
      exec(@sql)
    
    
      fetch next from attCursor into @attGuid, @attName, @tablename, @colGuid, @colName
    end
    
    
    close attCursor
    deallocate attCursor
    
    
    print char(13) + 'done.'
     

    If you are experiencing this problem, please log this with your Support Provider referencing problem 4636