Friday, 8 June 2012

SCCM database column widths and failed HWInv processing.


Recently I had to work on an issue were database column width expansion errors had been noted in both the SMS_INVENTORY_DATA_LOADER site component status messages and the dataldr.log when a primary site server is attempting to process hardware inventory MIF's from some clients. When agents submit new hardware inventory that contains data which is longer than the current database table column width a column width expansion request will be triggered. If the maximum column width value in the database is lower than the expansion value then hardware inventory processing for the MIF will fail. The root cause of this is the site server database was originally created as an SMS 2003 site server which had previously been upgraded to SCCM 2007 and at which time the database maximum column width values were not automatically increased to the 2007 defaults. After getting in contact with Microsoft PSS support it was discovered this is a known issue that the MaxColWidth value in the AttributeMap table is smaller than the actual column width in the corresponding DATA table. This could be due to some failed upgrade / installation of the site, which might have modified either value but not the other.

This issue was manually fixed by correcting the MaxColWidth value, in which case we prevented automatic widening from being triggered incorrectly. There is no need to drop any indexes on the columns after updating the column widths.

In most cases automatic widening will be able to fix the inconsistent AttributeMap, MaxColWidth and CHARACTER_MAXIMUM_LENGTH when the actual width of the attribute in the MIF exceeds MaxColWidth. The only problem of this inconsistency happens to the columns on which the DATA tables (e.g., INSTALLED_SOFTWARE_DATA) have non-clustered index depending on those columns and blocking automatic widening (incorrectly triggered due to the inconsistency).

Error Status Messages:
*** ALTER TABLE dbo.INSTALLED_SOFTWARE_MS_DATA ALTER COLUMN SoftwareCode00 varchar(72)  NULL                SMS_INVENTORY_DATA_LOADER           5/30/2012 7:33:19 PM    1544 (0x0608)
*** [42000][5074][Microsoft][ODBC SQL Server Driver][SQL Server]The index 'sw_ms_data_idx2' is dependent on column 'SoftwareCode00'.           SMS_INVENTORY_DATA_LOADER           5/30/2012 7:33:19 PM    1544 (0x0608)
*** ALTER TABLE dbo.INSTALLED_SOFTWARE_MS_DATA ALTER COLUMN SoftwareCode00 varchar(72)  NULL                SMS_INVENTORY_DATA_LOADER           5/30/2012 7:33:19 PM    1544 (0x0608)
*** [42000][4922][Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE ALTER COLUMN SoftwareCode00 failed because one or more objects access this column.             SMS_INVENTORY_DATA_LOADER           5/30/2012 7:33:19 PM    1544 (0x0608)
CDefinedGroup::WidenDefinedAttribute - could not change data type. SMS_INVENTORY_DATA_LOADER           5/30/2012 7:33:19 PM          1544 (0x0608)
CGroup::DefineAttributes - unable to widen attributes  SMS_INVENTORY_DATA_LOADER           5/30/2012 7:33:19 PM    1544 (0x0608)


 Example Scenario:
1. The MIF processing problem happens when data loader is trying to widen the CM_DSLID attribute.
2. Automatic widening happens when the AttributeMap.MaxColWidth value is less than the actual attribute width in the MIF, and it will attempt to set both MaxColWidth and the actual column width of the DATA table (INSTALLED_SOFTWARE_DATA.CM_DSLID00) to the smallest mod-8 number that is enough to hold the MIF attribute.
3. However, there’s a non-clustered index “sw_data_idx2” on the INSTALLED_SOFTWARE_DATA table that includes the column “CM_DSLID00”, so that automatic column widening failed in the ALTER TABLE attempt.
4. As the INSTALLED_SOFTWARE_DATA.CM_DSLID00 column is already of the correct width (255), we can simply modify the AttributeMap.MaxColWidth value to 255 to prevent this from happening again.
  

Errors as seen in the dataldr.log
Note the difference between an upgraded 2003\2007 site server in column 1 versus the values from a native SCCM 2007 database.


The following are the SQL commands used to update the SCCM database on the site server reporting errors. These commands are to be used at your own risk and you would be taking you life into your own hands without a current backup! 

Table - 'Installed_Software_MS_Data'

Note: Run the first select query in each block to check the current values within the database to check the database even needs updating BEFORE uncommenting and running the proceeding update statements.

Select ColumnName, MaxColWidth, GroupKey From AttributeMap Where GroupKey In ( Select GroupKey From GroupMap Where SpecificTableName = 'Installed_Software_MS_Data' ) order by ColumnName

--Update AttributeMap Set MaxColWidth = 16 Where GroupKey= (Select GroupKey from GroupMap Where SpecificTableName = 'Installed_Software_MS_Data' ) AND ColumnName = 'ChannelCode00'
--Update AttributeMap Set MaxColWidth = 16 Where GroupKey= (Select GroupKey from GroupMap Where SpecificTableName = 'Installed_Software_MS_Data' ) AND ColumnName = 'ChannelID00'
--Update AttributeMap Set MaxColWidth = 16 Where GroupKey= (Select GroupKey from GroupMap Where SpecificTableName = 'Installed_Software_MS_Data' ) AND ColumnName = 'MPC00'
--Update AttributeMap Set MaxColWidth = 255 Where GroupKey= (Select GroupKey from GroupMap Where SpecificTableName = 'Installed_Software_MS_Data' ) AND ColumnName = 'ProductCode00'
--Update AttributeMap Set MaxColWidth = 255 Where GroupKey= (Select GroupKey from GroupMap Where SpecificTableName = 'Installed_Software_MS_Data' ) AND ColumnName = 'SoftwareCode00'
--Select ColumnName, MaxColWidth, GroupKey From AttributeMap Where GroupKey In ( Select GroupKey From GroupMap Where SpecificTableName = 'Installed_Software_MS_Data' ) order by ColumnName

Table - 'Installed_Software_Data'

Select ColumnName, MaxColWidth, GroupKey From AttributeMap Where GroupKey In ( Select GroupKey From GroupMap Where SpecificTableName = 'Installed_Software_Data' ) order by columnname

--Update AttributeMap Set MaxColWidth = 255 Where GroupKey= (Select GroupKey from GroupMap Where SpecificTableName = 'Installed_Software_Data' ) AND ColumnName = 'ARPDisplayName00'
--Update AttributeMap Set MaxColWidth = 255 Where GroupKey= (Select GroupKey from GroupMap Where SpecificTableName = 'Installed_Software_Data' ) AND ColumnName = 'CM_DSLID00'
--Update AttributeMap Set MaxColWidth = 255 Where GroupKey= (Select GroupKey from GroupMap Where SpecificTableName = 'Installed_Software_Data' ) AND ColumnName = 'InstalledLocation00'
--Update AttributeMap Set MaxColWidth = 255 Where GroupKey= (Select GroupKey from GroupMap Where SpecificTableName = 'Installed_Software_Data' ) AND ColumnName = 'LocalPackage00'
--Update AttributeMap Set MaxColWidth = 255 Where GroupKey= (Select GroupKey from GroupMap Where SpecificTableName = 'Installed_Software_Data' ) AND ColumnName = 'ProductName00'
--Update AttributeMap Set MaxColWidth = 255 Where GroupKey= (Select GroupKey from GroupMap Where SpecificTableName = 'Installed_Software_Data' ) AND ColumnName = 'Publisher00'
--Update AttributeMap Set MaxColWidth = 255 Where GroupKey= (Select GroupKey from GroupMap Where SpecificTableName = 'Installed_Software_Data' ) AND ColumnName = 'RegisteredUser00'
--Update AttributeMap Set MaxColWidth = 48 Where GroupKey= (Select GroupKey from GroupMap Where SpecificTableName = 'Installed_Software_Data' ) AND ColumnName = 'SoftwarePropertiesHash00'
--Update AttributeMap Set MaxColWidth = 48 Where GroupKey= (Select GroupKey from GroupMap Where SpecificTableName = 'Installed_Software_Data' ) AND ColumnName = 'SoftwarePropertiesHashEx00'
--Update AttributeMap Set MaxColWidth = 48 Where GroupKey= (Select GroupKey from GroupMap Where SpecificTableName = 'Installed_Software_Data' ) AND ColumnName = 'UpgradeCode00'
--Select ColumnName, MaxColWidth, GroupKey From AttributeMap Where GroupKey In ( Select GroupKey From GroupMap Where SpecificTableName = 'Installed_Software_Data' ) order by columnname

Cheers

Ben

No comments:

Post a Comment