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.
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.
The fix, therefore, was to run the following command against the ConfigMgr database.
exec spUpgradeRefreshSqlModules
SCCMentor - Paul Winstanley 
Hi Paul,
When I executed select * from sys.objects where name=N’fn_ListUpdateCIs’ in my SCCM DB it returned the same date modified 26-01-2018. So is it mandatory to perform the exec spUpgradeRefreshSqlModules before the 1802 upgrade. Our SCCM is 1706 currently.
No. This is performed for you as part of the upgrade. No intervention required……unless it breaks lol
Thanks Paul!! 🙂