Master Data Services (MDS): Bug deleting an attribute

UPDATE on 11/28/2012:  I had this same error when trying to delete a model.  Fortunately, SQL Server 2012 SP1 has fixed this bug!

In Master Data Services (MDS), when trying to delete an attribute in an entity, I received the following error:

“Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.”

Note I also received the same error when using the MDS add-in in Excel and tried to change the maximum length of an attribute.

From reading Cannot delete a domain based attribute from an entity – Metadata issues, it seems this happens because the “Attribute Metadata Definition” table that is defined in mdm.tblEntity (in which is shows that it points to tbl_1_4_EN in my database) contains two attributes that share the same ObjectId (hence the “subquery returned more than one value” error).  To find out if you have duplicate ObjectId’s, execute this code:

SELECT name, ObjectId, COUNT(*) FROM mdm.tbl_1_4_EN
GROUP BY Name, [OBJECTID]
HAVING COUNT(*) > 1

It looks like this problem is caused by deleting a Model using the user interface. The attribute metadata and attribute group metadata seems to persist even when the model is deleted.  When the model is deployed again in the same DB, you get duplicate records.

This is a confirmed bug that hopefully will be fixed in SP1 of SQL Server 2012.  An unsupported workaround:

To remove the records modify [mdm].[udpUserDefinedMetadataDelete]:

***Warning only works if the last record is valid. Perform a backup before doing this!!!

– Get the member id
SET @MemberIdSQL = N’SET @MemberId = (SELECT ID FROM mdm.’ + @MemberTableName + N’ WHERE Status_ID = 1 AND ObjectId = @Object_ID)’

to

– Get the member id
SET @MemberIdSQL = N’SET @MemberId = (SELECT Top 1 ID FROM mdm.’ + @MemberTableName + N’ WHERE Status_ID = 1 AND ObjectId = @Object_ID)’

Then run:

DECLARE @Object_ID UNIQUEIDENTIFIER = ’9E36F7C6-2DA5-4786-B6E0-35E7CEDB8AAE’
EXEC [mdm].[udpUserDefinedMetadataDelete] ‘Attribute’, @Object_ID

multiple times until one record remains.  Don’t forget to remove the changes to udpUserDefinedMetadataDelete when completed.

About James Serra

James currently works for Microsoft specializing in big data and data warehousing using the Analytics Platform System (APS), a Massively Parallel Processing (MPP) architecture. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence/MDM architect and developer, specializing in the Microsoft BI stack. He is a SQL Server MVP with over 25 years of IT experience.
This entry was posted in MDS/MDM, SQLServerPedia Syndication. Bookmark the permalink.

2 Responses to Master Data Services (MDS): Bug deleting an attribute

  1. Eric Hyland says:

    James,

    Thanks for posting this. It would appear I’ve got this situation. Do you think if I apply the service pack to my development machine, delete the model, and then use MDSModelDeploy to deploy a clone back to development from my production box, the resultant model will be free of the duplicate meta data entries?

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>