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

I was preparing a demo for a session about the new features of Master Data Services in SQL Server 2016. In that demo, I created a business rule to test out the extension with custom scripts. When the business rule was finished, I tried to publish it. But, I was greeted with the following error: “300 – A database error has occurred. Contact your system administrator”.

error when publishing business rule

Not the most helpful error message, especially when you are the admin yourself. 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:

VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’

Apparently during the publishing of a business rule, MDS wants to run a stored procedure called udpValidationIsRunning. This procedure wants to check the system view sys.dm_broker_activated_tasks and apparently the necessary permissions are lacking.

The fix is luckily quite easy: just run the following statement to grant the permission to the MDSAppPool security group:

USE MASTER;
GRANT VIEW SERVER STATE TO [yourmachine\MDSAppPool];

Now the business rule can be published without an issue.

UPDATE:

it’s possible to logs are located at the folder C:\Program Files\Microsoft SQL Server\130\Master Data Services\WebApplication\MDSTempDir. This might have been changed in a later CU.


------------------------------------------------
Do you like this blog post? You can thank me by buying me a beer 🙂

Koen Verbeeck

Koen Verbeeck is a Microsoft Business Intelligence consultant at AE, helping clients to get insight in their data. Koen has a comprehensive knowledge of the SQL Server BI stack, with a particular love for Integration Services. He's also a speaker at various conferences.

58 thoughts to “Master Data Services – Database Error has Occured when Publishing Business Rule”

  1. 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

  2. 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

  3. 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 ),

    1. 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?

  4. 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!!

          1. 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:

          2. 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?

  5. 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

  6. 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. 🙂

  7. 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?

    1. 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.

  8. 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.

  9. 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??

  10. Koen,i am facing one issue in User Permission.
    when I gave permission ALL to user on Model and give permission of Read Only on Lookup Enities then In our main entity MDS is not allowing to insert new row, Using isinserting new record via excel. Can you please advice?
    Thanks

  11. Hi koen, Don’t know how to post new question that’s why posting here.

    Can you please answer me the below question,

    I am facing error while login to SQL server, below is the error,

    “A connection was successfully established with the server,but then an error occurred during the login process ( provider:SSL provider, error:0 – The specified data could not be decrypted.)”

    NOTE: I have already set encryption connection to false
    Thanks

    1. Seems like an issue with certificates. For a question like this, you’re better off at StackOverflow or SQLServerCentral.com. Maybe even the MSDN forums.

  12. Koen, In domain based attribute user can select value from drop down, but user can also enter his own value in domain based attribute value, how can we force user to only select value from drop down, or in other words we have to make sure that user can only select value from drop down we have to make domain based attribute non-editbale filed .

  13. Hi Koen,
    How can I query to show all the transactions on specific entity? I don’t want to see via web interface. I want to see in backend.

  14. koen I have completed my Model development in local environment, I have applied business rules and also created views of entities, now I want to deploy my model on production, Can you please tell me that what will be the best way to deploy model? so that my business rules and views should also go in production when I deploy model

    1. How to Deploy

      Google is your friend 😉
      Make sure you create a version flag in MDS. Views only deploy when they belong to a flag.
      When deploying using the command line tool (as admin), deploy as a clone, not as a new model. Otherwise, your GUIDS are messed up and it will be difficult to deploy an updated version of your model later on.

  15. Hi Koen,
    My Model is working fine on local,
    but when I deploy model by using MDSModelDeploy, I am using DeployClone Option but I am facing error while deploying , below is the error, Can u please help.

    “The attribute value cannot be saved. The attribute participates in a filter relationship and an incompatible value was found in another filter-related attribute within the same member.
    The attribute value is not compatible with the attribute filter.”

  16. Koen i have enabled log and checked in file the issue is happening in derived hierarchy attributes, Model is working fine but i don’t know why issue is happening when i deploy model.

    1. Is it a brand new deployment? Or are you updating an existing model?
      Maybe the order in which entities are deployed causes an error in the attribute filtering as some data might not be present yet.

      Did you try the MSDN forums?

  17. its a brand new deployment.. what can I do? I have to deploy Model but not able to get solution yet 🙁

  18. Hi Koen, I hope u ll be fine…thanks for helping me about the above question,..now I have one question below.can you please answer …

    It is possible to hide(to some users) an entity within a model without hiding the domain-based attributes in all the other entities that refers to the first one?
    The problem seems to be that when we try to disable this entity to be showed in the drop list, the domain-based attributes in the other entity’s are blocked to. Is there a way to do this?

  19. Hi koen, Can we apply business rule on attribute for accepting only 8 digits ? i.e not less than 8 and not greater than 8 , it should be equal to 8 in length,

  20. There is no default rule to check exact length, however we can use maximum length rule but in this case we have to check on fixed length…..as far as TSQL is concerned, I have created user defined function but now the issue is how can I show custom message in validation status column in excel if the function returns 0..

  21. My function (usr.PersalNumberLength) returns 0 if the length of Value 8 …..I have used function in IF Statement of Business Rule,
    Now I want to show custom message in ELSE condition.
    My condition looks like below

    IF usr.PersalNumberLength (@Value:PersalNumber) is True
    Then
    NONE
    ELSE
    “Here I want to show custom message that Persal Number should be equal to 8 in length

  22. Hi Koen,
    I added a comment on your post related to many to many derived hierarchy in Master Data services on MySQL tips..Can you please respond on that if you know the solution, I am facing issue when I apply many to many derived hierarchy on attribute to filter records on the basis of derived hierarchy..below is the link of that post.

    https://www.mssqltips.com/sqlservertip/4702/create-a-manytomany-derived-hierarchy-in-sql-server-master-data-services/

  23. Hi Koen!

    A bit of topic, but do you know if it is possible to configure a rule where
    Condition – If one attribute among several is blank
    Action – At least one is needed/required

    Also, thank you for your really useful posts 🙂

    Cumps

Leave a Reply to Koen Verbeeck Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.