Member Code Already Used by a Deleted Member in #MDS of #SQLServer

Sometimes, when users enter a new member to an entity in Master Data Services (either using the Web frontend or MS Excel), they experience the following error:

Code 300034: The member code is already used by a member that was deleted. Pick a different code or ask an administrator to remove the deleted member from the MDS database.

So, now comes the problem: the user wants to use the code and is asking the administrator to remove the deleted member from the MDS database. Not a problem? Well it is, for two reasons:

  1. You are the administrator.
  2. Have you ever looked at the MDS database?

Well, #1 is granted. #2 is this beast:

The entities in MDS are stored in numbered but not named tables. So, now you got three options:

  1. Be lazy, ignore the request or forward it to a colleague.
  2. Go through each table and look for records that look like the entities content.
  3. Be clever, as my colleague Berthold, who came up with this SQL statement to find the table which was edited the last time:
SELECT 
  OBJECT_NAME(OBJECT_ID) AS DatabaseName, 
  last_user_update,
  * 
FROM 
  sys.dm_db_index_usage_stats
WHERE 
  database_id = DB_ID('database-name')  
order by 
  2 desc

There is a good chance that the table that contains the entity values is one of the first ones in the statement’s result set. If not, ask the user to change a record or do it by yourself.

 

4 thoughts on “Member Code Already Used by a Deleted Member in #MDS of #SQLServer

  1. Get a screendump from the user, showing the name of the entity is the problem, then you go into the table mdm.tblEntity, find the name of the entity (column: name) and match it with the coded name of the table (column: EntityTable).

    When you have the name of the table, open the table for editing.
    Pay attention to column statusID and see if statusID 2 is represented on the rows where column “name” represents your problem – change the 2 to 1 and have a check in the MDS GUI.

  2. In 2012, check the EBS update stored procedure. The table for the entity itself, and all the related entities will be referenced in the sproc stg.udp__leaf.

Leave a Reply

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