ERROR (557): More data was found in the database than was expected

Version 2
    Share This:

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    BMC Atrium Core - CMDB


    COMPONENT:

    BMC Atrium Core


    APPLIES TO:

    Any version of product using MSSQL database to store application data



    PROBLEM:

    You may encounter this data when data in ARSCHEMA dataset is being updated via direct SQL injection. This bypasses CMDB API that uses structured metadata for AtriumCore CMDB.
    Please see KA: AtriumCore CMDB Common Data Model (CDM) break down within the ARSCHEMA database  (https://selfservice.bmc.com/casemgmt/sc_KnowledgeArticle?sfdcid=000123533)
    for reference on CMDB Metadata.


    CAUSE:

    This error is caused when the T tables have columns that can accept more data then what the metadata was configured. It is a sign that data is being inserted directly into the table by SQL entry rather than using the proper API.


    SOLUTION:

    To solve this we first need to find the data that exceeds the size specified by CMDB metadata. For this use case we'll use the BMC.CORE:BMC_OperatingSystem class.

    The first step is to find the schemaid of this class.
    SQL Query: select schemaid from arschema where name = 'BMC.CORE:BMC_OperatingSystem'

    Result here will vary, so I am going to use random number for SchemaId. Say 500.

    Next we need to run a query to find the fields associated with that schema using "ForeignKeyID" in OBJSTR:Attribute_Definitions:

    SQL Query: select Field_ID, ForeignKeyId, Field_Name, Input_Length from objstr_attributedefinition where ForeignKeyID = 'BMC_OPERATINGSYSTEM'

    Result would like this:
     

                                                                                                                                                                                                                                                                                                                                                            
    FieldIDForeignKeyIdNameInputLength
    301064700BMC_OPERATINGSYSTEM CountryCode6
    301052200BMC_OPERATINGSYSTEM MaxNumberOfProcesses255
    301052300BMC_OPERATINGSYSTEM MaxProcessMemorySize 255
    301052400BMC_OPERATINGSYSTEM Organization30
    301052500BMC_OPERATINGSYSTEM OSLanguage255
    301052600BMC_OPERATINGSYSTEM OSProductSuite 255
    301052700BMC_OPERATINGSYSTEM OSType 255
    301052800BMC_OPERATINGSYSTEM PAEEnabled 255
    301052900BMC_OPERATINGSYSTEM ProductType 255
    301053000BMC_OPERATINGSYSTEM SuiteMask 255
    301053100BMC_OPERATINGSYSTEM SystemDirectory 255
      

    Next we need to get the columns associated with the T table. This command will show us the table definition (data structure) of the table that is associated with BMC.CORE:BMC_OperatingSystem 

    SQL Query:   exec sp_columns T500

    User-added image


    What we can see here is that the table itself has length of 12 characters which is different from the 6 in the CMDB metadata. 

    So, now that we know what field has more data than expected we can run a SQL query that shows all data at the table level that exceeds the CMDB metadata length of 6 characters: 

    SQL Query: select DATALENGTH(C301064700), C200000020, C179 from T705 where C200000020 IS NOT NULL and DATALENGTH(C301064700) > 6

    Which ever result that comes back from this query would be the root cause for the error because it exceeds the size of data determined by CMDB metadata. 
    The record would either need to be truncated, set to NULL or deleted. Any other field that still generates the error should be investigated in the same way. 

    In summary, this type of case should only be possible when CDMB API's are bypassed and data is inserted directly into the database via SQL INSERT or UPDATE statement.  
    This is not the Best Practice to manage data in the CMDB.   
      

     


    Article Number:

    000142924


    Article Type:

    Solutions to a Product Problem



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles