How to Remove Custom Discovery Architecture

A while back a customer had added some custom discovery architecture to their sites but needed to change a column name due to a conflict in SQL.  We searched the web but didn’t find much on how to remove the added information.  I did some testing and came up with a solution but didn’t think much about it.  Last week a coworker asked the same question so I thought maybe I need to add this information to my blog.  Here it is:

Make a backup before you try this to be on the safe side.  All my statements below reference a 7 in the name, that’s because once I figured out how to clean it out I added it all back in again to verify my tests so it changed the table name.  Just replace the 7 with a 6 or whatever your number is and you should be good to go.

1. Stop the SMS_Executive and SMS_Site_Component Services on the site with the Database that has the issue.

2.  Delete the entry out of the wbem repository.  Use wbemtest and connect to root\sms\site_sitecode.  Once connected click on the Enum Classes button and then find the class that you need to remove (in this case it was called SMS_R_STRMetrics) and highlight like this screen shot:

Next hit the Delete button and that should remove it

3.  Verify the name of the table that the custom architecture is in.  For me it is called Cust_Arch_7_Disc.  For you it is most likely Cust_Arch_6_Disc.  Once you have the table name then you need to drop the table using this SQL command:  Drop table Cust_Arch_7_Disc

4. Next we need to do the same thing with the view.  Mine was called v_R_Cust_Arch_7.  Once you get that view name then drop it using this SQL command:  Drop view v_R_Cust_Arch_7

5. Now is where the fun comes in.  In my tables I had a reference to a table and it would not let me delete the entries related to the Custom Architecture until I cleaned that out.  Here is more information.  I was trying to run this SQL command:  delete from DiscPropertyDefs where DiscArchKey = '7'.  When I run this command I get this error: 

Msg 99951, Level 16, State 1, Procedure DiscPropertyDefs_del, Line 1

Cannot delete row because referenced table exists in database.

Msg 3609, Level 16, State 1, Line 1

The transaction ended in the trigger. The batch has been aborted.

If you run a Select * from DiscPropertyDefs where DiscArchKey = '7' then you will see this in the results:

It is those entries in the ArrayTableName that is causing the issue.  Run this SQL command to fix that:  update DiscPropertyDefs set ArrayTableName = '' where DiscArchKey = '7' Once this complete do another Select * from DiscPropertyDefs where DiscArchKey = '7' to verify that all rows in the ArrayTableName column are blank.  Once that is the case then you are ready for the next step.

6.  Run this SQL query to clean up the DiscPropertyDefs table:  delete from DiscPropertyDefs where ArrayTableName = '' and DiscArchKey = '7’

7. Run this SQL query to clean up the DiscoveryArchitectures table:  delete from DiscoveryArchitectures where BaseTableName='CUST_ARCH_7_DISC'

8. Run this SQL query to clean up the DiscItemAgents table:  delete from DiscItemAgents where DiscArchKey = '7’.  You may get a 0 rows affected on this statement as there is a trigger when a delete statement is issued on the DiscoveryArchitectures table that will delete entries in this table as well.  Go ahead and run the delete statement on this table just in case.

9. Restart the SMS Services on the box

10. In the console delete any collections and queries that referenced this custom architecture.