none
Business Rule corruption

    Question

  • Has anyone encountered then following:

    Suddenly I cannot create or edit any existing business rule without it becoming corrupted. This occurred after security updates to the server. 

    Running Windows Server 2012 and SQL Server 2016

    Creating/editing a rule that uses a user defined function results in the following: "This business rule contains corrupted rule item(s), as corresponding SQL Script has been changed"

    Creating a simple rule to check that an attribute value equal to some static value:

    "The sequence must be greater than zero"

    I've tried to look at the underlying stored procedures that run and the business rules tables, but have not found anything except the corrupted data.

    Thursday, November 17, 2016 2:50 PM

Answers

  • I had the same issue

    The fix for this issue is included in the following updates:
    •SQL Server 2016 SP1 MDS GDR https://support.microsoft.com/en-us/kb/3210089
    •SQL Server 2016 RTM MDS GDR https://support.microsoft.com/en-us/kb/3210111
    •SQL Server 2016 RTM COD MDS GDR https://support.microsoft.com/en-us/kb/3210110

    It solved my 2 issues with business rules and version validation.

    • Marked as answer by subtonez Tuesday, December 27, 2016 9:51 PM
    Monday, December 19, 2016 4:46 PM
  • MS acknowledged the problem and they are working on the fix.   Please make it quick!

    https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2016-sp1-known-issues/


    Business Intelligence Consultant in Quebec, Canada

    • Marked as answer by subtonez Tuesday, December 13, 2016 5:00 PM
    Thursday, December 1, 2016 3:31 PM

All replies

  • Hi Subtonez

    I experience exactly the same thing, but so far I haven´t found any solution.

    This problem started this weekend when we installed SP1.

    The symptom I see is that my functions are losing their parameters when saving them, and then I get the same error message as you.
    Scripts that already exist and are unchanged work as they should, but functions that I create now after the upgrade to SP1 and functions that exists since before and gets changed is all affected by this problem.

    I get the same error as you do when saving, and when I open the business rule again to correct it, the parameters are lost and an error saying "The operator FunctionName is corrupted, please reassign all values."

    I have tried to reassign the parameters, but the error "This business rule contains corrupted rule item(s), as corresponding SQL Script has been changed" remains when saving...

    Have you found any more info regarding this?


    Tuesday, November 22, 2016 3:18 PM
  • If DBCC CHECKTABLE reports any errors, then, restoring the database from the database backup instead of running REPAIR. If no backup exists, running REPAIR can correct the errors that are reported.
    Wednesday, November 23, 2016 8:25 AM
  • Seems to be a bug in SQL Server 2016 SP1, I found this on Microsoft Connect:

    https://connect.microsoft.com/SQLServer/Feedback/Details/3112582

    Let´s hope it gets fixed ASAP.

    To me it is a showstopper.

    Wednesday, November 23, 2016 12:00 PM
  • Hello. Have the same problem since upgrade to SQL SERVER 2016 SP1 with all Business Rules. In the MDS log files, I also have an error in the udp_SYSTEM_1_1_CHILDATTRIBUTES_ProcessRules:

    number : 102, message : Incorrect syntax near ','., server : *******, proc. : udp_SYSTEM_1_1_CHILDATTRIBUTES_ProcessRules, ligne : 242

     SELECT @AttributeDataType = DataType_ID
     FROM [mdm].[tblAttribute] AT
     JOIN [mdm].[tblEntity] ET
     ON AT.Entity_ID = ET.ID
     WHERE AT.Name = N'SourceTable'  AND ET.Name = @EntityName;
       UPDATE  md
          SET   [SourceTable] = LEFT(, 100)
            OUTPUT inserted.[MemberID], 
                   inserted.[MemberMUID],
                   inserted.[OriginalCode],     
                   N'SourceTable', 
                   inserted.[SourceTable], 
                   CASE WHEN ISNULL( NULLIF(inserted.[SourceTable], 
                   deleted.[SourceTable]),  
                   NULLIF(deleted.[SourceTable], 
                   inserted.[SourceTable])) IS NULL THEN 0 ELSE 1 END
            INTO #BRAssignmentStaging
          FROM    #BRMemberData AS md
          INNER JOIN #BRConditionEvaluation AS ce
            ON  md.[MemberID] = ce.[MemberID]
            AND md.[SourceTable] IS NULL -- defaulting so must be empty
          AND ce.[BusinessRuleID] = 45  AND ce.[IsConditionTrue] = 1  WHERE NOT (@AttributeDataType = 2 AND -- 2 means Number data type.
       [mdq].[IsNumber](LEFT(, 100)) = 0);


    Also need a quick fix


    • Edited by YNGUYEN01 Thursday, November 24, 2016 10:58 AM
    Thursday, November 24, 2016 9:41 AM
  • Hello,

    We had exactly the same problem without any clue for a workaround or solution, just we have the error log with all messages, but this don´t say anything:

    11/24/2016 18:43:44,21ba9b87-8bf3-4ff3-a53e-61519729559a,/api/BusinessRules,Error,"<ArrayOfError xmlns=""http://schemas.microsoft.com/sqlserver/masterdataservices/2009/09"" xmlns:i=""http://www.w3.org/2001/XMLSchema-instance"">
      <Error>
        <Code>400030</Code>
        <Context>
          <FullyQualifiedName />
          <Identifier />
          <Type>BusinessRuleItem</Type>
        </Context>
        <Description>La secuencia debe ser mayor que cero.</Description>
        <Number>400030</Number>
      </Error>
      <Error>
        <Code>400031</Code>
        <Context>
          <FullyQualifiedName />
          <Identifier />
          <Type>BusinessRuleItem</Type>
        </Context>
        <Description>El operador de condición o acción no es válido.</Description>
        <Number>400031</Number>
      </Error>
      <Error>
        <Code>400012</Code>
        <Context>
          <FullyQualifiedName />
          <Identifier />
          <Type>BusinessRuleItem</Type>
        </Context>
        <Description>Debe especificar un argumento de prefijo para la condición o acción.</Description>
        <Number>400012</Number>
      </Error>
    </ArrayOfError>"

    Friday, November 25, 2016 1:07 PM
  • MDS is broken with SP1.   Can MS confirm?  After SSRS printing was broken out of the gate with SP1.  The same thing goes for MDS.   Can we wait for a hotfix or will it be too long and we need to rollback the SP1 install.

    Business Intelligence Consultant in Quebec, Canada

    Tuesday, November 29, 2016 2:16 PM
  • Anyone reading this PLEASE DON'T APPLY SP1

    It will ruin your day, week and or month.


    -Brian-

    Tuesday, November 29, 2016 9:29 PM
  • MS acknowledged the problem and they are working on the fix.   Please make it quick!

    https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2016-sp1-known-issues/


    Business Intelligence Consultant in Quebec, Canada

    • Marked as answer by subtonez Tuesday, December 13, 2016 5:00 PM
    Thursday, December 1, 2016 3:31 PM
  • I had the same issue

    The fix for this issue is included in the following updates:
    •SQL Server 2016 SP1 MDS GDR https://support.microsoft.com/en-us/kb/3210089
    •SQL Server 2016 RTM MDS GDR https://support.microsoft.com/en-us/kb/3210111
    •SQL Server 2016 RTM COD MDS GDR https://support.microsoft.com/en-us/kb/3210110

    It solved my 2 issues with business rules and version validation.

    • Marked as answer by subtonez Tuesday, December 27, 2016 9:51 PM
    Monday, December 19, 2016 4:46 PM