Master Data Services – Database Error has Occured when Publishing Business Rule

View Comments

• Great!!

  I was spinning my head around this problem and this solved it for me! :)
  Although, that account might be different from the one you show.

  Thanks, Koen

• Hi, I have created three types business rule in one column
  1. Must be unique
  2. is required
  3. must be less than 99999999

  when I publish first two MDS will not give me error and published the rules, but when I publish third rule it gives me error "Errors • 156 : A database error has occurred. Contact your system administrator. "

  Can anyone guide me about this error

  1

  • Hi Bilal,
    what does the logs say?

    Koen

• Hi Koen,
  how can I check log?
  but i have started profiler and checked calls of SQL Statement and I saw error in mdm.[udp_SYSTEM_1_14_CHILDATTRIBUTES_ProcessRules] procedure when I click on publish and I can see the syntax error there .below is the query where error occurred,you can see in case statement..there is nothing after md.[AnnualLeaveCredits] <

  SELECT
  md.MemberID
  ,5 AS BusinessRuleID
  ,19 AS BRItemID
  ,N'AnnualLeaveCredits must be less than ' AS RuleItemText
  ,ce.IsConditionTrue
  ,CASE WHEN
  md.[AnnualLeaveCredits] < THEN 0 ELSE 1 END AS IsRuleBroken
  ,CASE WHEN iss.MemberID IS NOT NULL THEN 1 ELSE 0 END AS HasExistingIssue
  FROM #BRMemberData AS md
  INNER JOIN #BRConditionEvaluation AS ce
  ON md.[MemberID] = ce.[MemberID]
  AND ce.[BusinessRuleID] = 5
  LEFT JOIN cteCurrentValidationIssues iss
  ON ce.[MemberID] = iss.[MemberID]
  AND iss.[BusinessRuleID] = ce.[BusinessRuleID]
  AND iss.BRItemID = 19 ),

  • It's described in the article how to find the log:

    The MDS team promised that in SQL Server 2016, troubleshooting would be easier. So I went and checked out their blog post What’s New in Master Data Services – SQL2016 CTP2 (May) Release, where they have a section Improved Troubleshooting and Logging. There it is mentioned the web application logs to the following folder: C:\Program Files\Microsoft SQL Server\130\Master Data Services\WebApplication. In the log file over there, I could find the following – actual – error message

    Regarding the error in the case statement: what if you use a smaller number?

• Koen it says we can find log folder under web application but I am not able to see any log folder under web application folder. Can u help me!!

  • Which version of MDS are you using?

    • Koen I am using master data services 2016 and product version is 13.1.4001.0.

      • Make sure tracing is enabled.

        https://docs.microsoft.com/en-us/sql/master-data-services/tracing-master-data-services

        • Koen I have enabled tracing and found below error when publishing business rules.
          but I am wondering why I am seeing this error while publishing specific one business rule, other business rule are working fine,

          Failed to execute database operation server BILALARSHAD\SQL2016, database MDS_V1. Error:

        • Is there an actual error message after "Failed to execute database operation server BILALARSHAD\SQL2016, database MDS_V1. Error:"?
          You posted a CASE statement earlier that was not finished, which would lead to an error. Have you tried a lower value?

• Koen I have tried lower value I was publishing value 99 and now I am publishing value 5 but error remains same..there is syntax error in query .Value should be present after < sign
  you can see in case statement of query ,CASE WHEN md.[AnnualLeaveCredits] <

  SELECT
  md.MemberID
  ,6 AS BusinessRuleID
  ,22 AS BRItemID
  ,N'AnnualLeaveCredits must be less than ' AS RuleItemText
  ,ce.IsConditionTrue
  ,CASE WHEN md.[AnnualLeaveCredits] < THEN 0 ELSE 1 END AS IsRuleBroken

  • Can you show me a screenshot of the business rule, if possible?

    • Another issue how can I attach screenshot here? :):)

      • You can't. You can host it somewhere else though and link to it.

      • please see below link you can see two answers with two images here.

        http://stackoverflow.com/questions/43390659/code-column-read-only-issue-in-master-data-services-2016/43472829#43472829

  • I'd have to test it, but looking at your screenshots it seems it's a bug.

• Hi Koen,
  Microsoft has confirmed that it's a bug, we have to update our Master data services 2016 to MDS 2016 SP1 Cu2. Thank you so much for your time. :)

• koen.. Can we auto retrieved the column value from other column is it possible in MDS. For Example if we have column A (ID Number Column) value 7407195189089 then Column B (Data of Birth Column) value should be auto retrieved from Column A value , I this case the first 6 digits of column A is the Data of birth of Employee, so Data of Birth should be auto retrieved from ID number In this case the Date of Birth Value (first 6 digits of ID Number) should be 1974-07-19.
  Can we do this?

  • I'd say MDS is not the place for this. This is something your ETL should do, or the process that inserts the master data into MDS.
    MDS should be used for storing master data and validating it.

• Hi Koen,

  I have updated SQL server 2016 to SQL server 2016 SP1 Cu2, after updation my database server is updated to Microsoft SQL Server 2016 (SP1-CU2) (KB4013106) - 13.0.4422.0 (X64) ,Master data services version is changed from 13.1.4001.0 to (Now MDS version is 13.1.4422.0), now I am receiving error on MDS website which I was using before is " The product version and database version are not compatible. " , Can you please guide me.

  • Open the MDS configuration tool. Select your MDS database and run the upgrade wizard.

• Thanks Koen, great :)

• Hi Koen,
  How can we add Business rule or validation on Column which has year input, For example user can only enter year like 2017 or 2016 or 2015 etc etc ...user cannot enter more than 4 digits and 4 digits should be year. Can we achieve this in MDS??

  • Hi Bilal,
    my first reaction would be to use business rule extensions, since you're using MDS 2016.
    Business Rules Extension in Master Data Services 2016

    A rule like this is easy to implement in T-SQL.