locked
SCCM 2012 RTM to SP1 RRS feed

  • Question

  • Hi,

    We are planning to upgrade our SCCM 2012 to the SP1 and I got this error when running the testdbupgrade on a copy of the SCCM database. Can someone have an idea how to fix this? Thanks.

    ERROR: Failed to execute SQL Server command:  ~-- upgrade ADR filters and SUM saved searches ~IF EXISTS (SELECT * FROM dbo.SMSData WHERE SMSBuildNumber < 7750) ~BEGIN ~    update f set Data=NewXML ~    from RuleFilters f ~    cross apply (select LocaleID=f.Data.value('(/UpdateXML/@LocaleID)[1]', 'int')) lc ~    cross apply ( ~        select NewXML=( ~            select [@Name]='SMS_SoftwareUpdate'  ~                 , [@LocaleID]=lc.LocaleID ~                 , [*]=( ~                    select [@PropertyName]=NewProp ~                         , [@UIPropertyName]=NewUIProp ~                         , [*]=( ~                            select [*]=ISNULL(NewValue, OldValue) ~                            from xdi.n.nodes('MatchRules/string') xds(n) ~                            cross apply(select OldValue=xds.n.value('text()[1]', 'nvarchar(255)')) oldv ~                            outer apply(select top 1 NewValue=N''''+CategoryInstance_UniqueID+N'''' from dbo.fn_ListCategoryInstances(lc.LocaleID) c where c.CategoryTypeName=ct.CatType and c.CategoryInstanceName=substring(OldValue, 2, len(OldValue)-2)) newv ~                            for XML path('string'), root('MatchRules'), type) ~                    from f.Data.nodes('/UpdateXML/UpdateXMLDescriptionItems/UpdateXMLDescriptionItem') xdi(n) ~                    cross apply (select OldProp=xdi.n.value('@PropertyName[1]', 'nvarchar(64)'), OldUIProp=xdi.n.value('@UIPropertyName[1]', 'nvarchar(64)')) oldp ~                    cross apply (select NewProp=case when OldUIProp in ('_Company', '_Product') then OldUIProp else OldProp end, NewUIProp=case when OldUIProp in ('_Company', '_Product') then '' else OldUIProp end) newp ~                    cross apply (select CatType=case when NewProp='UpdateLocales' then 'Locale' when NewProp in ('_Company', '_Product', '_UpdateClassification') then substring(NewProp, 2, 255) else null end) ct ~                    for XML path('UpdateXMLDescriptionItem'), root('UpdateXMLDescriptionItems'), type) ~            for XML path('UpdateXML'), type) ~        ) nx ~    where f.Data.exist('/UpdateXML/UpdateXMLDescriptionItems/UpdateXMLDescriptionItem[@UIPropertyName = ("_Company", "_Product") or @PropertyName = ("_UpdateClassification", "UpdateLocales")]')=1 ~ ~    ;with xmlnamespaces('http://www.w3.org/2001/XMLSchema' as xsd, 'http://www.w3.org/2001/XMLSchema-instance' as xsi) ~    update f set SearchString=convert(nvarchar(max), NewXML) ~    from SearchFolders f ~    cross apply (select SearchXML=convert(xml, f.SearchString)) sx ~    outer apply ( ~        select NewXML=( ~            select  ~                [@Name]='SMS_SoftwareUpdate',  ~                [*]=( ~                    select  ~                        [@xsi:type]='SearchFolderDescriptionItem', ~                        [@PropertyName]=Prop, ~                        [*]=( ~                            select [@Condition]=sc.n.value('@Condition', 'nvarchar(255)') ~                                 , [@ConditionType]=sc.n.value('@ConditionType', 'nvarchar(255)') ~                                 , [@RequiresFormat]=sc.n.value('@RequiresFormat', 'nvarchar(255)') ~                                 , [@CanHaveNullValue]=sc.n.value('@CanHaveNullValue', 'nvarchar(255)') ~                                 , [@IsMultiValue]=sc.n.value('@IsMultiValue', 'nvarchar(255)') ~                                 , [@SearchString]=ISNULL(NewValue, OldValue) ~                            from sdi.n.nodes('SearchCriteriaList/SearchCriteria') sc(n) ~                            cross apply(select OldValue=sc.n.value('@SearchString[1]', 'nvarchar(255)')) oldv ~                            outer apply( ~                                select top 1 NewValue=CategoryInstance_UniqueID ~                                from v_Categories c ~                                where c.CategoryTypeName=ct.Type  ~                                  and exists(select 1 from v_LocalizedCategories where CategoryInstanceID=c.CategoryInstanceID and CategoryInstanceName=OldValue) ~                              ) newv ~                            for XML path('SearchCriteria'), root('SearchCriteriaList'), type) ~                    from SearchXML.nodes('/SearchFolderDescription/SearchFolderDescriptionItems/SearchFolderDescriptionItem') sdi(n) ~                    cross apply (select Prop=sdi.n.value('@PropertyName[1]', 'nvarchar(64)')) oldp ~                    cross apply (select Type=case when Prop='UpdateLocales' then 'Locale' when Prop in ('_Company', '_Product', '_UpdateClassification') then substring(Prop, 2, 255) else null end) ct ~                    for XML path('SearchFolderDescriptionItem'), root('SearchFolderDescriptionItems'), type) ~            for XML path('SearchFolderDescription'), type) ~        ) nx ~    where f.ObjectType=1011 and f.SourceSite=dbo.fnGetSiteCode() and f.IsSystem=0 ~      and SearchXML.exist('/SearchFolderDescription/SearchFolderDescriptionItems/SearchFolderDescriptionItem[@PropertyName = ("_Company", "_Product", "_UpdateClassification", "UpdateLocales")]')=1 ~ ~END

    Wednesday, April 24, 2013 2:09 PM

Answers