locked
De-activating member not working in MDS RRS feed

  • Question

  • Hi,
    I try to de-activate a member using mdm.tblStgMemberAttribute table. I use the below code to de-activate member. It is not working. When I do processing, the processing does not do anything with respect to this staged row. The Membercode is not getting deactivated. Please let me know whether I am doing something wrong? I am doing as specified in the link http://technet.microsoft.com/en-us/library/ee633772.aspx
    --Deactivating member
     
     
    insert mdm.tblStgMemberAttribute(ModelName,Membertype_ID,EntityName,MemberCode,AttributeName,AttributeValue)
    
    Values
    
    ('ModelName',1,'EntityName','MemberCode','MDMMemberStatus','De-Activated')
    
    • Edited by Venkataraman R Tuesday, May 24, 2011 6:43 PM Content did not get pasted earlier properly
    Tuesday, May 24, 2011 6:39 PM

Answers

  • Venkataraman,

     

    Having the same issue with members who are domain referances to other entities. Problem I discovered is that if the member in question (M1 for clarity) is referanced by another member (M2) who is deactivated, then the M1 referance on M2 is retained and unavailable to the GUI, But the M1 referance on the deactivated M2 will prevent deactivation of M1.

    The solution (that I use) is to NULL the M1 referance(s) on the M2 Record directly on M2's table using SSIS.

    Key is to seek out all the M1 referances in Deactivated records, clear the referances and then deactivate M1.

     

    Tony

     

     


    Richard A. "Tony" Eckel Rochester, NY
    Wednesday, October 19, 2011 1:38 AM
  • Hi Tony,

    In my case, I wanted to deactive leaf members, which are not being referenced by any other entities. But, they still failed. They work fine from UI. But, from the staging tables, they did not work.

    Finally, I went on to utilize background stored procedure udpMemberStatusSet to update the member status.

    I think it is a bug.

    thanks,

    Venkat


    Venkataraman R
    Thursday, October 20, 2011 3:17 AM
  • Hi APJVenkat

    please activate the MDS Trace in order to check the detailed information about your staging :

    http://sqlblog.com/blogs/mds_team/archive/2010/01/05/trace-logging-in-sql-server-2008-r2-master-data-services.aspx

     

    Regards,



    Xavier Averbouch
    Microsoft Community Contributor
    Avanade , FRANCE
    If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".
    Tuesday, June 7, 2011 4:03 PM
  • It seems your issue is related to either SQL Broker not enabled on the MDS database or the issue fixed in MDS June 2010 Cumulative Update. If you are running RTM version I suggest you apply latest Service Pack. If you used that entity as domain-based attribute for another entity, even if you removed the referencing attribute it will not allow you to delete any member of that entity. In the June 2010 CU under import and export the following fix is listed: 

    "The staging process supports the deletion of members from an entity that supplies domain-based attribute values for another entity."

    You can download the June 2010 CU at: http://support.microsoft.com/kb/2143880 or for a list of more recent updates refer to: http://sqlblog.com/blogs/mds_team/

    I hope this helps.

    Regards,

    Jose Chinchilla aka SQLJoe
    http://www.sqljoe.com

    Tuesday, October 11, 2011 4:56 AM

All replies

  • Hi,

    There's nothing wrong with the SQL code that you've posted, assuming that you've replaced the strings of 'ModelName, 'EntityName and 'MemberCode' above with the correct values. E.g. If I was deleting a member with a code of 2 from the sample Product model, then I would do:

    insert

    mdm.tblStgMemberAttribute(ModelName,Membertype_ID,EntityName,MemberCode,AttributeName,AttributeValue

     

    Values

    ('Product',1,'Product',2,'MDMMemberStatus','De-Activated')

    I'm guessing you probably have filled these in correctly. Other things to check are:

    -Are you loading data from the staging tables into MDS itself? If you are then how are you doing it? Via the front end or by running mdm.udpStagingSweep?

    -Is service broker enabled?

    -Are there any error codes in the mdm.tblStgMemberAttribute table for the member that you're trying to de-activate?

    The link below also may help as it covers the whole staging process:

    http://sqlblog.com/blogs/mds_team/archive/2010/02/10/Importing-Data-by-Using-the-Staging-Process.aspx

    Hope that helps

    Jeremy


    http://blogs.adatis.co.uk/blogs/jeremykashel/default.aspx

     

    )


    Tuesday, May 24, 2011 8:39 PM
  • I tried through both Master Data Manager and also through udpStagingSweep. Both of them not doing any processing for this deactivation of a staging row.

    Service broker is enabled. Processing is working fine, except for this deactivation of a member.

    No error codes are present in the mdm.tblStgMemberAttribute table. It is blank. It is present as if no processing was performed on the row.

    Can you please clarify why this weird error is occuring ?


    Venkataraman. Please Vote As Helpful or Mark as Answer, if the answer had helped you.

    Wednesday, May 25, 2011 4:38 PM
  • A couple of other things to try:

    -If you say you've tried it via Master Data Manager, do you get any error messages returned when you load the data in? I'm refering to the part in the UI that will give you the staging batches grid, where you get the status of the batch, the count of records processed, and the count of errors. Is there anything there, either in the grid itself, or when you click the button that's marked 'View Details for Selected Batch'?

    -Is the member used in any derived hierarchies? E.g. does it have any active members associated with it?

    -Can you attempt to delete it in the front end to see if you get the same behaviour?

    Best Regards

    Jeremy


    http://blogs.adatis.co.uk/blogs/jeremykashel/default.aspx
    Wednesday, May 25, 2011 4:57 PM
  • Hi APJVenkat,

    did you check that post ?

    http://sqlblog.com/blogs/mds_team/archive/2010/02/10/staging-examples.aspx

    You can also use this table to deactivate a member. Deactivating a member changes the MemberCode to a GUID and changes the AttributeValue to De-Activated so the member is no longer displayed in the UI.

    INSERT INTO mdm.tblStgMemberAttribute (ModelName, EntityName, MemberType_ID, MemberCode, AttributeName, AttributeValue) VALUES
    
    (N'Product', N'Product', 1, N'BK-M101', N'MDMMemberStatus', N'De-Activated') 
    
    
    

    To reactivate a member, use the following example.

    INSERT INTO mdm.tblStgMemberAttribute (ModelName, EntityName, MemberType_ID, MemberCode, AttributeName, AttributeValue) VALUES
    
    (N'Product', N'Product', 1, N'B12DDC7B-DD25-4623-AAC6-BBE51A017D2F', N'MDMMemberStatus', N'Active')
    
    
     

    To determine the GUID that is the MemberCode, open the view: mds.viw_SYSTEM_SCHEMA_ENTITY. Find the name of the entity that contains the deleted member or collection. Note the value in the EntityTable column. Then open the table that was listed in the EntityTable field. Find the name of the member or collection and note the value in the Code column. That value is the GUID.

    Regards,



    Xavier Averbouch
    Microsoft Community Contributor
    Avanade , FRANCE
    If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".
    Tuesday, June 7, 2011 2:10 PM
  • Hi APJVenkat

    please activate the MDS Trace in order to check the detailed information about your staging :

    http://sqlblog.com/blogs/mds_team/archive/2010/01/05/trace-logging-in-sql-server-2008-r2-master-data-services.aspx

     

    Regards,



    Xavier Averbouch
    Microsoft Community Contributor
    Avanade , FRANCE
    If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".
    Tuesday, June 7, 2011 4:03 PM
  • It seems your issue is related to either SQL Broker not enabled on the MDS database or the issue fixed in MDS June 2010 Cumulative Update. If you are running RTM version I suggest you apply latest Service Pack. If you used that entity as domain-based attribute for another entity, even if you removed the referencing attribute it will not allow you to delete any member of that entity. In the June 2010 CU under import and export the following fix is listed: 

    "The staging process supports the deletion of members from an entity that supplies domain-based attribute values for another entity."

    You can download the June 2010 CU at: http://support.microsoft.com/kb/2143880 or for a list of more recent updates refer to: http://sqlblog.com/blogs/mds_team/

    I hope this helps.

    Regards,

    Jose Chinchilla aka SQLJoe
    http://www.sqljoe.com

    Tuesday, October 11, 2011 4:56 AM
  • Venkataraman,

     

    Having the same issue with members who are domain referances to other entities. Problem I discovered is that if the member in question (M1 for clarity) is referanced by another member (M2) who is deactivated, then the M1 referance on M2 is retained and unavailable to the GUI, But the M1 referance on the deactivated M2 will prevent deactivation of M1.

    The solution (that I use) is to NULL the M1 referance(s) on the M2 Record directly on M2's table using SSIS.

    Key is to seek out all the M1 referances in Deactivated records, clear the referances and then deactivate M1.

     

    Tony

     

     


    Richard A. "Tony" Eckel Rochester, NY
    Wednesday, October 19, 2011 1:38 AM
  • Hi Tony,

    In my case, I wanted to deactive leaf members, which are not being referenced by any other entities. But, they still failed. They work fine from UI. But, from the staging tables, they did not work.

    Finally, I went on to utilize background stored procedure udpMemberStatusSet to update the member status.

    I think it is a bug.

    thanks,

    Venkat


    Venkataraman R
    Thursday, October 20, 2011 3:17 AM