Master Data Services Excel Add-in Query updater


This small tool allows you to Load, Edit, and save MDS queries with operators that are not available in current mds add-in
you can download the source code (.Net C# 4.0 Client Profile, VS2010) of my tool here (FREE) HERE


binary only:

very easy to use:

Load your already generated query

then choose a new Operator and edit the criteria

and then save the updated query

you can use all these operators:
[Description(“”)] NotSpecified,
[Description(“=”)] IsEqual,
[Description(“<>”)] IsNotEqual,
[Description(“LIKE”)] Like,
[Description(“NOT LIKE”)] NotLike,
[Description(“>”)] GreaterThan,
[Description(“< “)] LessThan, [Description(“>=”)] GreaterThanOrEqual,
[Description(“< =”)] LessThanOrEqual,
[Description(“MATCH”)] Matches,
[Description(“NOT MATCH”)] NotMatches,
[Description(“REGEX”)] ContainsPattern,
[Description(“NOT REGEX”)] NotContainsPattern,
[Description(“IS NULL”)] IsNull,
[Description(“IS NOT NULL”)] IsNotNull,
[Description(“IN”)] In,
[Description(“NOT IN”)] NotIn,
[Description(“DIRECT DECENDANTS”)] DirectDecendants,
[Description(“ALL DECENDANTS”)] AllDecendants,

More info on sql mds 2012 MSDN blog

Master Data Services : Display entity members with good sort order when code is numeric

a MDS user recently asked me this question:

When looking at entity contents via web UI in the column code, 10 and each code that starts with 1 (10,11..19) always goes before 2 and so on. Is there any way to fix it? I don’t believe that’s the right order.



so I’ve started to investigate..

first (easy solution)

the easiest way is to add at least one zero before code “1” and code  “2” and so on

but if your code size is huge, you will have to add a lot of “0” behind your code

so I’ve tried to find a custom hack in MDS stored procedure: and finally I’ve found it 🙂

second (hard way) solution:

after a few search on google, I’ve found this post :

but I had to find where to place this code hack

after a few search on mds (by sorting entity members in Web UI and looking at SQL profiler)

i’ve found that a call was made to this stored procedure:


after looking into udpEntityMembersGet, I’ve found that another stored procedure was called : mdm.udpMembersGet, a huge one!

so I’ve tried to update this SP code and finally it works!

be carefull, this hack could slow down the entitymembers get request, and it is only for testing purpose, and if you update MDS with cumulative update, it could be erased!

you just have to replace every instance of (there should be 3 of them) :






 right click here and ‘save as’ to download the customized stored procedure


Master Data Services : Business Rules Copy / Import / Export (Cross Model)

updated article (Oct 2011)

(code refactored)

Hi All,

One other missing feature of MDS is Import / Export / Copy Business rules from a model to another (or copy business rules to the same model)

my code is still in beta, but it is already (partially) working.

you can find it on my “MDS Manager” tool on codeplex:

Get business rules from modelId, quite easy with service client method “business rules get”, returns a business rule set

                //new type needed to avoir error because serialization of an inherited class                 XmlSerializer xs = new XmlSerializer(typeof(BusinessRules), new Type[] { typeof(Common.ServiceReference1.BRAttributeValueArgument) });
//update oct 2011!
//new type needed to avoir error because serialization of an inherited class

update oct 2011 code for BRActionsInstanciate, BRConditionsInstanciate,BRConditionTreeNodesInstanciate

Master Data Services : Cross Model entity Copy

Hi all,

One of the missing things in MDS is the cross-model entity copy

here is a API usage example for copying an entity from a model /version to another (or even copy an entity into a new model with a new entity name).

you can download the complete source code of my codeplex tool including this sample on :

note: if source entity contains domain based attributes, and if the target model does not contain the domain based attribute parent entity, the attribute type will automatically converted to “Freeform” instead of “Domain based attribute”.


SQL2008 R2 Master Data Services Cumulative updates history and details


Here is a link to a great blog post from Jason Howell (Senior Escalation Engineer in the SQL Server Support team specializing in MDS)


here is a history of MDS cumulative updates / fixes

note : the latest cumulative update always contains all previous updates.
note 2:
here is another link for all SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 was released :



This update fixes a number of issues related to the following areas in Microsoft SQL Server 2008 R2 Master Data Services. The following sections describe the fixes and functionality available after you apply this update.

Hierarchy Management

Collapse this tableExpand this table

New members added to an entity in MDS while within a derived hierarchy will default the associated domain based attribute based on the user’s current location within the hierarchy.
In Explorer, the more option is available in the hierarchy tree for derived hierarchies.
Referential integrity is enforced for derived hierarchies with explicit caps.

Import and Export

Collapse this tableExpand this table

The staging process supports the deletion of members from an entity that supplies domain-based attribute values for another entity.
In Integration Management, under Unbatched Staging Records, the Model and Version drop-down menus enable successful selection of a different model and version.
The staging process is enabled for the Metadata model.
System member records from the Metadata model cannot be deactivated by using the staging process.

Business Rules

Collapse this tableExpand this table

Business rules can now be deployed by using the Model Deployment wizard or API even if there are staging errors for file attributes.
Users can add multiple Actions to a business rule without causing the error, “Failed to enable constraints.”
On the Edit Business Rule page, the contains the pattern condition and must contain the pattern action support up to 200 characters to allow for complex regular expressions.
On the Edit Business Rule page, users can use Delete from the right-click menu to delete a condition or an action without encountering the error, “An unknown error occurred.”
The Internet Information Services (IIS) application pool identity no longer needs sysadmin privileges for the SQL Server Database Engine instance or db_owner privileges to the Master Data Services database to successfully publish business rules. Previously, insufficient privileges for the application pool identity resulted in the error, “A database error has occurred. Contact your system administrator.”
Users can delete business rules that contain nested operators without specified conditions.

Attributes and Attribute Groups

Collapse this tableExpand this table

In Explorer, when entering a value for a numeric attribute, users can specify very long numbers without encountering the conversion overflow error, “A general error occurred. System.OverflowException: Conversion overflows.”
In Explorer, when users edit numeric attribute values on the Member Information page, numbers following a decimal point are no longer rounded to two decimal places.
In Explorer, domain-based attribute values are retrieved from the database instead of from the cache. This enables new domain-based attributes values to be immediately available for an entity.
An attribute that belongs to more than one attribute group with different permissions now resolves as expected so that the least restrictive permission applies.
In System Administration, users can now reorder attribute groups without causing the attributes to disappear.

Versioning and Validation

Collapse this tableExpand this table

When a user copies a version, the member ID is preserved so that records can be compared across versions even if the member’s code changes.
A new system setting, NotificationsPerEmail, has been added to specify the number of validation issues to include per e-mail when notifications are sent. This provides the ability to combine a number of validation issues in one e-mail rather than sending a separate e-mail for each validation issue.
Validation and copying versions is supported when two entities have references to one another (known as a recursive relationship).
In Version Management, on the Validate Version page, users can use the Model and Version drop-down menus to successfully select a different model and version for validation.
Validation issues are now properly removed after the issues have been fixed on the item edit page.
Validation issues have been improved to give more information about the issues found.

Users and Groups

Collapse this tableExpand this table

In User and Group Permissions, on the Add groups page, users can add groups that contain a dash (-) in the group name.

User Interface Usability

Collapse this tableExpand this table

Drag and drop functionality has been fixed throughout the application so that it works when Master Data Manager has been added as a Trusted Site in Internet Explorer.
The hierarchy pane has been fixed to work properly with the minimum supported resolution of 1024×768.


Collapse this tableExpand this table

Deletion of a metadata type is limited to one type in a single API call.



CU8 : (10.50.1797.0)
no fix for master data services in SQL 2008 R2 cumulative update 8  🙁


CU7 : (10.50.1777.0)
2516439 FIX: “The script ‘/MDM/ScriptResource.axd’ contains multiple calls to Sys.Application.notifyScriptLoaded(). Only one is allowed” error on a SQL Server 2008 R2 MDS website


CU6 : (10.50.1765.0)

2486113 : A drop-down list is always displayed instead of a search box when you double-click the value of the filter criteria box for an attribute in Entities Explorer of the SQL Server 2008 R2 MDS website

2497301 FIX: “Validate Version” page takes a long time to open or times out after you click “Validate version” on the SQL Server 2008 R2 MDS website


no MDS update in it (as far I know)


CU 4 : (10.50.1746.0)

FIX: You encounter two issues when you use Microsoft SQL Server 2008 R2 Master Data Services (MDS)

FIX: You receive an exception when you try to create an MDS database in SQL Server 2008 R2 MDS if a non-MDS database table is named “tblSystem”

FIX: The Entities Explorer grid displays few rows in a SQL Server 2008 R2 MDS website if you use a low screen resolution


CU3 :
no MDS update in it (as far I know)


CU2 : (10.50.1720.0)

Bugs that are fixed in Microsoft SQL Server 2008 R2 Master Data Services June Update


no MDS update in it (as far I know)

Master Data Services : Getting Errors out of a ModelMembersBulkUpdate/Merge with API

In fact, you can get the error codes on MDS website : in “Integration Management/Staging Batches” clicking on the batch you want and clicking on button “view details for selected batch”
in order to do the same thing with API (get error codes), you’ll need to get info from the batch after it has been inserted. 


so, complete process would be:

note: StagingDataStatus= StagingDataStatus.All –> get all including Errors

SQL 2008 R2 Master Data Services : Bulk Data Insert with API

In this article, I will talk about Master Data Services.
You can find the source code of this example on my MDS project on Codeplex:  

Special thanks to Suzanne Selhorn at Microsoft for help and support and her new great book about Master Data Services that you can find on Amazon :

Update 30 april 2011
a part of a answer message from Tyler Graham about synchronous /asynchronous operations:
 I wanted to ensure you understood that EntityMembersCreate and its partners calls are synchronous calls that will return any and all errors founds in the MDS load. (More so in Denali where some problems have been fixed)  ModelMembersBulkMerge and any calls with the ‘Bulk’ keyword leverage staging and will return success no matter what the disposition of the records as the use of staging is asynchronous.”

thanks to Tyler for this useful remark!

Note : 
EntityMembersCreate : only new member records with unique codes can be created with this operation. If a member code already exists, an error will be thrown in the returned error collection and the member will not be created or updated.

EntityMembersMerge : Many users have been confused by this operation and believe it has something to do with record survivorship or match merge functionality. This operation does nothing to look for possible matches or provide any survivorship functionality; it only allows users to create and update records simultaneously within MDS.
Information source : 
book: Master Data Services “Implementation and Administration” by Tyler Graham and Suzanne Selhorn
So, what’s the point:

you want to insert a big number of members in a MDS entity, with API (MDS WebService)
first solution :

Second Solution :

So let’s see the 2 solutions in details :

First solution  : (slow, not recommanded)
inserting each member with API method EntityMembersCreate


Second Solution : with API method ModelMembersBulkMerge (fast, recommanded)