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”.
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 🙂
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.
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
Hi Koen, Can we change order of Columns in MDS??
Yes you can. Use the Move Up and Move Down boxes when editing attributes.
Thanks 🙂
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
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.
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 .
You can type in values, but you cannot publish the value. MDS takes care of validating input in dba attributes.
u r right..it works 🙂
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.
https://www.mssqltips.com/sqlservertip/4325/member-revision-history-in-master-data-services-2016–part-2/
Koen it means that MDS has also functionality of CDC(Change Data Capture)..
.am I right?
Yes, sort of. You can also look as if it’s like a slowly changing dimension type 2.
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
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.
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.”
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.
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?
its a brand new deployment.. what can I do? I have to deploy Model but not able to get solution yet 🙁
I haven’t seen the error before. Did you try the MSDN forums?
No…I haven’t tried on MSDN…
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?
I have no idea. Have you tried the MSDN forums or Stack Overflow?
yes .but couldn’t find answer.
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,
I don’t know if there’s a default rule for that. I’m pretty sure you can check lengths of text, but I can’t verify. I’m on a new laptop and I haven’t configured MDS yet.
However, you can always use T-SQL business rules. In T-SQL you can do what you want of course. Business Rules Extension in Master Data Services 2016
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..
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
I’m not sure it’s possible to define a custom message for the validation status.
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/
Yay! Wow this could have been painful. Thank you!!!!
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
Maybe take a look at business rules extensions?
https://www.mssqltips.com/sqlservertip/4671/business-rules-extension-in-master-data-services-2016/