Cannot delete a domain based attribute from an entity - Metadata issues

Question

Hi,

I have found a bit of a problem that I hope someone can help with.

A bit of background:

I've built a model in a Dev environment which I've now deployed to Test using the MDS package deployment functionality. This is what I intend to do as part of the deployment to our Production environment.

On Test, I tried to delete an unwanted DBA from an entity and it gave me the error message: "A database error has occurred. Please contact your system administrator". I tried running the mdm.udpAttributeDelete procedure which gave this error msg:

Msg 50000, Level 16, State 1, Procedure udpAttributeDelete, Line 135

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

Delving deeper, this proc runs : mdm.udpUserDefinedMetadataDelete that has the following code on Line 51:

-- Get the member id

 

SET @MemberIdSQL = N'SET @MemberId = (SELECT ID FROM mdm.' + @MemberTableName + N' WHERE Status_ID = ''1'' AND ObjectId = ''' + CONVERT(NVARCHAR(200), @Object_ID) + N''')'

 

EXEC sp_executesql @MemberIdSQL, N'@MemberId INT OUTPUT', @MemberId OUTPUT

This query hits the "Attribute Metadata Definition" table (tbl_1_4_EN in my database) defined in tblEntity which shows two attributes that share the same ObjectId (hence the "subquery returned more than one value" error).

Furthermore a simple query shows 40 similar attributes that share the same objectId:

SELECT

 

name, ObjectId, COUNT(*) FROM mdm.tbl_1_4_EN

 

GROUP BY Name, [OBJECTID]

 

HAVING COUNT(*) > 1

Is this a bug with the model deployment?

Thanks

Ben

 

Answer 1

It looks like this problem could be 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... 

Answer 2

I am having the same issue. When I used MDSModelDeploy.exe with -includedata it would fail as I had an entity which had a file attribute. This failure would case the metadata model to be come corrupted.

To remove the records I modified [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
SET @MemberIdSQL = N'SET @MemberId = (SELECT Top 1 ID FROM mdm.' + @MemberTableName + N' WHERE Status_ID = 1 AND ObjectId = @Object_ID)' 

***
Run

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

until one record remains.

Don't forget to remove the changes to udpUserDefinedMetadataDelete

This may not be the best solution but it worked for me.

Answer 3

Hello Ben and Olof,

I have been able to reproduce the issue and there does not look to be a supported workaround at this time. Adding 'TOP 1' to the subquery will get you by until a fix is available but before you apply a fix, you should remove the 'TOP 1' from the subquery. I am filing a bug on this and it will be considered for a future update (possibly SP1) of SQL Server MDS 2012.

Thanks!

---

Josh Gerszewski, MSFT