I helped out a good friend of mine recently when he reported that the All Software Updates node in the SCCM console reported ‘No items found’ after a site upgrade from 1710 to 1802.

SU1802-001.JPG

We attempted a few troubleshooting methods before hitting on the source of the problem and the smsprov.log provided the nugget of information that we could work from.

The following error occured:

Execute WQL =SELECT ci.* FROM SMS_SoftwareUpdate ci WHERE ci.CI_ID NOT IN ( SELECT CI_ID FROM SMS_CIAllCategories WHERE CategoryInstance_UniqueID=’UpdateClassification:3689bdc8-b205-4af4-8d4a-a63924c5e9d5′) AND ci.CI_ID NOT IN (SELECT CI_ID FROM SMS_CIAllCategories WHERE CategoryInstance_UniqueID=’Product:30eb551c-6288-4716-9a78-f300ec36d72b’) ORDER BY DateRevised DESC SMS Provider 23/04/2018 23:14:06 5900 (0x170C)
Execute SQL =select all ci.CI_ID,ci.LocaleID,ci.ApplicabilityCondition,ci.ArticleID,ci.BulletinID,ci.CI_ID,ci.CI_UniqueID,ci.CIType_ID,ci.CIVersion,ci.CMTag,ci.CMTagName,ci.ConfigurationFlags,ci.CreatedBy,ci.CustomSeverity,ci.CustomSeverityName,ci.DateCreated,ci.DateLastModified,ci.DatePosted,ci.DateRevised,ci.EffectiveDate,ci.EULAAccepted,ci.EULAExists,ci.EULASignoffDate,ci.EULASignoffUser,ci.IsUserCI,ci.InUse,ci.IsBroken,ci.IsBundle,ci.IsChild,ci.IsContentProvisioned,ci.IsDeployable,ci.IsDeployed,ci.IsEnabled,ci.IsExpired,ci.IsHidden,ci.IsLatest,ci.IsMetadataOnlyUpdate,ci.IsOfflineServiceable,ci.IsQuarantined,ci.IsSuperseded,ci.IsUserDefined,ci.LastModifiedBy,ci.LastStatusTime,ci.Description,ci.DisplayName,ci.CIInformativeURL,ci.LocaleID,ci.MaxExecutionTime,ci.ModelID,ci.ModelName,ci.NumMissing,ci.NumNotApplicable,ci.NumPresent,ci.NumTotal,ci.NumUnknown,ci.ObjectPath,ci.PercentCompliant,ci.PermittedUses,ci.PlatformType,ci.RequiresExclusiveHandling,ci.RevisionNumber,ci.SDMPackageVersion,ci.SedoObjectVersion,ci.Severity,ci.SeverityName,ci.SourceSite from fn_ListUpdateCIs(2057) AS ci where (ci.ModelName not in (select all Folder##Alias##810314.InstanceKey from vFolderMembers AS Folder##Alias##810314 where Folder##Alias##810314.ObjectTypeName = N’SMS_SoftwareUpdate’) AND (ci.CI_ID not in (select all SMS_CIAllCategories.CI_ID from fn_ListCICategoriesAll(2057) AS SMS_CIAllCategories where SMS_CIAllCategories.CategoryInstance_UniqueID = N’UpdateClassification:3689bdc8-b205-4af4-8d4a-a63924c5e9d5′) AND ci.CI_ID not in (select all SMS_CIAllCategories.CI_ID from fn_ListCICategoriesAll(2057) AS SMS_CIAllCategories where SMS_CIAllCategories.CategoryInstance_UniqueID = N’Product:30eb551c-6288-4716-9a78-f300ec36d72b’))) order by ci.DateRevised desc SMS Provider 23/04/2018 23:14:06 5900 (0x170C)
*** [42S22][207][Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name ‘CMTag’. SMS Provider 23/04/2018 23:14:06 5900 (0x170C)
*~*~[42S22][207][Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name ‘CMTag’. ThreadID : 5900 , DbError: 207 , Sev: 16~*~* SMS Provider 23/04/2018 23:14:06 5900 (0x170C)
*** [42S22][207][Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name ‘CMTagName’. SMS Provider 23/04/2018 23:14:06 5900 (0x170C)
*~*~[42S22][207][Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name ‘CMTagName’. [42S22][207][Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name ‘CMTag’. ThreadID : 5900 , DbError: 207 , Sev: 16~*~* SMS Provider 23/04/2018 23:14:06 5900 (0x170C)
*~*~..\sspobjectquery.cpp(5470) : Error waiting for query to return.~ SQL Error: [42S22][207][Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name ‘CMTagName’.~*~* SMS Provider 23/04/2018 23:14:06 5900 (0x170C)
*~*~Error waiting for query to return. [42S22][207][Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name ‘CMTagName’.~*~* SMS Provider 23/04/2018 23:14:06 5900 (0x170C)
Results returned : 0 of -1 SMS Provider 23/04/2018 23:14:06 5900 (0x170C)

We checked the SQL db, in vSMS_SoftwareUpdate  and all CMTag values were populated with 0 but in a working 1802 site they were recorded as NULL. Something wasn’t quite right.

After some discussion with the Microsoft ConfigMgr product group, they advised to run the following on the DB:

select * from sys.objects where name=N’fn_ListUpdateCIs’

This reported back that the functions definition had not been refreshed after 26/01/18, way before the site upgrade.

The cmupdate.log showed timeout issues when executing the spUpgradeRefreshSqlModules stored procedure and a warning that the SQL object may need to be manually refreshed.

log.png

The fix, therefore, was to run the following command against the ConfigMgr database.

exec spUpgradeRefreshSqlModules

Advertisements