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:
- You are the administrator.
- 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:
- Be lazy, ignore the request or forward it to a colleague.
- Go through each table and look for records that look like the entities content.
- 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.
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.
thanks for the pointers solved my problem with this..
Even the url has enough info
http://myserver:8081/Explorer/AttributeSL.aspx?MID=2&VID=2&EID=19#/ExplorerEntity?MID=2&VID=2&EID=19
MID is the model id
EID is the entity id
so for this example you’d need to look for the rogue entry in [tbl_2_19_EN]
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.