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.