none
Upgrade from CB 1802 to 1906 RRS feed

  • Question

  •  

    Hello

    Post Upgrade there is a critical issue with the SMS_STATE_SYSTEM Component

    The error being logged

    *** [01003][8153][Microsoft][SQL Server Native Client 11.0][SQL Server]Warning: A NULL value is deleted by an aggregate or other SET operation. : spOfficeReadinessCalculation

    *** exec spOfficeReadinessCalculationTask NULL, 0x0000000000000000

    Task 'Office Readiness Calculation Task' failed after running for 15 seconds.

    Looking into this there is a SP on the local SQL Server 2016 / spOfficeReadinessCalculationTask

    (33564 line(s) affected)

    Warning: A NULL value is deleted by an aggregate or other SET process.

    Message 547, level 16, status 0, procedure spOfficeReadinessCalculation, line 130 [batch start line 0]

    The MERGE statement conflicts with the FOREIGN KEY restriction "OfficeReadiness_MachineIdGroupXRef_FK". The conflict occurred in the CM_RPS database, table "dbo.MachineIdGroupXRef", column 'MachineID'.

    The statement was terminated.

    Warning: A NULL value is deleted by an aggregate or other SET operation.

     

    (1 row(s) affected)

     

    (No column name)

    (No column name)

    0

    NULL

     

    Anyone seen this, i suspect its to do with this new feature post 1802 but im not sure how this can be resolved without some SQL Wizardry.

    Chris

     

    Wednesday, January 8, 2020 10:51 AM

All replies

  • Where exactly was this error logged?

    Jason | https://home.configmgrftw.com | @jasonsandys

    Wednesday, January 8, 2020 9:01 PM
  • Hi,

    Sorry, same doubt as Jason, where is the error message from?
    What's the error description for the component SMS_STATE_SYSTEM?

    Regards,
    Allen

    Please remember to mark the replies as answers if they help.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Thursday, January 9, 2020 3:12 AM
  • Thanks for asking back

    In Monitoring / Component Status / SMS_STATE_SYSTEM

    Opening the message

    Error Code 620

    Microsoft SQL Server returned SQL message 547, severity level 16: 23000][547][Microsoft][SQL Server Native Client 11.0][SQL Server]The MERGE statement conflicts with the FOREIGN KEY restriction "OfficeReadiness_MachineIdGroupXRef_FK". The conflict occurred in the CM_RPS database, table "dbo.MachineIdGroupXRef".

    For more information about troubleshooting, see the Configuration Manager documentation, SQL Server documentation, or the Microsoft Knowledge Base.

    Based on that Info i looked in the statesys.log file on the site in question.

    I seen these lines

    *** [01003][8153][Microsoft][SQL Server Native Client 11.0][SQL Server]Warning: A NULL value is deleted by an aggregate or other SET operation. : spOfficeReadinessCalculation

    *** exec spOfficeReadinessCalculationTask NULL, 0x0000000000000000

    I then logged into teh SQL server and ran spOfficeReadinessCalculationTask

    Output is this

    (33564 line(s) affected)

    Warning: A NULL value is deleted by an aggregate or other SET process.

    Message 547, level 16, status 0, procedure spOfficeReadinessCalculation, line 130 [batch start line 0]

    The MERGE statement conflicts with the FOREIGN KEY restriction "OfficeReadiness_MachineIdGroupXRef_FK". The conflict occurred in the CM_RPS database, table "dbo.MachineIdGroupXRef", column 'MachineID'.

    The statement was terminated.

    Warning: A NULL value is deleted by an aggregate or other SET operation.

     

    (1 row(s) affected)

     

     

    (No column name)

    (No column name)

    0

    NULL

    Hope that now clarifies the trail of breadcrumbs im following

    Friday, January 10, 2020 8:46 AM
  • Hi,

    It seems to be a deeper issue related to SQL Server, then it is recommended to contact Microsoft Customer Support Services (CSS) for a dedicated Support. 

    Regards,
    Allen

    Please remember to mark the replies as answers if they help.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Tuesday, January 14, 2020 9:33 AM
  • I'm seeing the exact same failures since upgrading from 1810 to 1910.  Just curious if Microsoft able to resolve the issue for you? (or if you were able to resolve it yourself).  If it's resolved, can you share the resolution?
    Wednesday, January 22, 2020 2:47 PM
  • Hello, i have not contacted Microsoft yet, this issue is for a customer and we are supporting them i dont know if they have any microsoft support available.  If you get further please post any results, i am continuing to look into the issue, its not service affecting but the system state is critical so doesnt look good, interesting that your upgrade is not the same but 1810-1910 where ours was 1802-1906, its something to do with teh sccm office 365 upgarde readiness, we are seeing some results being populated in the dashboard for this component.
    Thursday, January 23, 2020 7:37 AM
  • and we are supporting them

    That doesn't mean you have the ability to solve every issue for them or even should try necessarily. Any other expectation by the customer or your org is naive and not reality. There simply are some things that only the actual creator of something can help with and this is very true with software and software as complex as ConfigMgr is.


    Jason | https://home.configmgrftw.com | @jasonsandys

    Thursday, January 23, 2020 2:12 PM
  • my guess is that it has to do with something introduced in 1906.  At some point, we'll be opening a ticket with microsoft, but, just like in your environment, it doesn't really seem to be impacting any other operations other than the O365 upgrade readiness dashboard.
    Thursday, January 23, 2020 5:28 PM
  • Hello

    If you do open a ticket please update this thread so that we can see if his is something that can be fixed or if we still need to go via MS support to be covered.

    Thanks

    Chris

    Monday, February 3, 2020 1:24 PM
  • ok here is the resolution email from MS

    Its a known bug and will be fixed in SCCM 2020

    Well lets hope so

    many thanks for the log files.

     

    I reviewed them and as already mentioned during our last phone call the problem is caused by a known bug in SCCM. Actually you mentioned that you do not observe any functionality restriction in your environment.

     

    The problem occurs when a state message arrives on the site server and the SQL stored procedure spOfficeReadinessCalculationTask is being executed to calculate office upgrade readiness.

     

    Based on the bug it was found there are clients in System_DISC that were not in MachineIdGroupXRef that was causing the foreign key constraint OfficeReadiness_MachineIdGroupXRef_FK failure during merge into OfficeReadiness. As a result those particular state message won’t be processed and its data won’t get inserted into the SCCM DB.

     

    The bug is actually scheduled to be fixed in version SCCM 2002. Based on that I recommend to upgrade to SCCM 2002 as soon as it is made available and to check if the problem is resolved after the upgrade.

     

    If you have any questions please feel free to contact me.

     

    Regards,


    Support Escalation Engineer                        

    System Center Support - Central Europe   

    Customer Services and Support                    

    Wednesday, February 19, 2020 7:56 PM