Removing unwanted attributes
First, let me start off by saying that this is a completely unsupported process. There are a number of things that can go wrong and probably will go wrong, and if you break it there's nothing the support team can do to help you.
Second, if you're using the Outlook client and have replication set up, this process gets a lot more complicated, and I'm not going to be able to help out either.
So, let's say you've gone into the schema manager and you've added an attribute, and let's assume you've done something you didn't want to do (like create it with the wrong type, or the wrong size, or the wrong name...). You probably want to remove that attribute. I can imagine there are other scenarios where removing an attribute might be a really helpful thing to do.
Well, removing attributes is a fairly straightforward thing to do. First, you need to make sure you're not using the attribute anywhere. If you are, and you remove it from the metadata, then the product will appear to break when whatever tool is using the attribute tries to access it. Attributes can be consumed in a number of places - the web application uses then obviously; reporting will use them sometimes; integration might use them; and the Outlook client might too.
Make sure you've removed the attribute from the forms first, that way you can test out what you've done. Use the tools supplied by the product, they're really good and can do the right thing.
Here comes the part where things might break. You need to edit the Attribute table in the metadata database. Find the attribute you want to remove (make sure it's the right one and bound to the entity you're expecting it to be bound to). Keep the attribute id handy because you're going to need it. You need to remove all the references to the attribute first - look in AttributeMap for references to the attribute on both sides and remove those references (preferably do this from the mapping tool in schema manager - it knows the right incantations, and if this step breaks you're still in supported territory, I think).
I'm also assuming that you're not doing anything with an attribute used in any relationship because that will flat out break stuff that you can't fix. This means that AttributeOf, SortAttribute, TypeAttribute, and AggregrateOf will all be NULL for the attribute you're removing. It also means that you won't find the attribute used in KeyAttributes anywhere.
Once you've got all that cleaned up, you can delete the row from Attribute. There are some limitations to what you can delete, but since you're only deleting attributes you added, right, you shouldn't have a problem. However, it might be educational to talk about some attribute characteristics that describe attributes that just can't be removed. Any attribute with RequiredForGrid, IsPKAttribute, or RequiresPlatformAuthorization == 1 have to stay. Any attribute with IsNullable == 0 have to stay as well - they are required by the product. By the way, if you find your attribute in KeyAttributes or referenced by name in GeneratedJoins (or JoinAttributes), then don't do this - that attribute is being used in a relationship and removing it will change the shape of the entity graph - and you're going to break the software.
Once you've deleted the attribute reference from the metadata, you're almost done. You just need to ALTER TABLE on the *Base table to drop the column (if you don't know how to do that, you shouldn't be trying any of this stuff...) and then regenerate the views and triggers. If you look at the stored procedures in the metabase you'll find one called p_genSpecificViewAndTrigger. It takes the *Base name of the table that holds the entity as a parameter and generates a script that you can use to recreate the views and triggers. It won't recreate them for you, you need to run it, copy the resulting script, and run that script in the MSCRM database.
Now, if you're using replication you've got all kinds of other issues do deal with. You need to drop the column from the publication (look in SQL BOL for information on sp_repldropcolumn).
One thing you need to do now is reset the cache. In fact, you should probably have the application shut down while you're doing all this stuff. Simply do an IISRESET on all the web servers that host MS-CRM - that'll definitely flush the metadata cache from the platform.
This might look something like:
Like I said though, this is all unsupported stuff and isn't typically something you want to do, but in the off chance that you've done something, like put in an attribute that blows out the 8k row size limit, and you want to fix the problem, then this procedure will probably help out a bit. Most likely I've missed a number of steps in the process, probably the important ones around removing the attribute from all the replication infrastructure (and there's a lot of it).
As a reminder, if you remove any of the attributes that we shipped with the product you're probably going to break during the upgrade process. If V2 suddendly decides that one of those ill-named empty columns from V1 look useful for a feature, it might get used, and if it's not there, upgrade might not work. I haven't seen the final designs for V2 around the 8k limit but I've heard that there's a lot of work in this area - your columns might move around on you during the upgrade too. So, just careful, and be willing to put those columns back if youre upgrade testing fails (you do plan on doing upgrade testing when the product ships, right?).
Second, if you're using the Outlook client and have replication set up, this process gets a lot more complicated, and I'm not going to be able to help out either.
So, let's say you've gone into the schema manager and you've added an attribute, and let's assume you've done something you didn't want to do (like create it with the wrong type, or the wrong size, or the wrong name...). You probably want to remove that attribute. I can imagine there are other scenarios where removing an attribute might be a really helpful thing to do.
Well, removing attributes is a fairly straightforward thing to do. First, you need to make sure you're not using the attribute anywhere. If you are, and you remove it from the metadata, then the product will appear to break when whatever tool is using the attribute tries to access it. Attributes can be consumed in a number of places - the web application uses then obviously; reporting will use them sometimes; integration might use them; and the Outlook client might too.
Make sure you've removed the attribute from the forms first, that way you can test out what you've done. Use the tools supplied by the product, they're really good and can do the right thing.
Here comes the part where things might break. You need to edit the Attribute table in the metadata database. Find the attribute you want to remove (make sure it's the right one and bound to the entity you're expecting it to be bound to). Keep the attribute id handy because you're going to need it. You need to remove all the references to the attribute first - look in AttributeMap for references to the attribute on both sides and remove those references (preferably do this from the mapping tool in schema manager - it knows the right incantations, and if this step breaks you're still in supported territory, I think).
I'm also assuming that you're not doing anything with an attribute used in any relationship because that will flat out break stuff that you can't fix. This means that AttributeOf, SortAttribute, TypeAttribute, and AggregrateOf will all be NULL for the attribute you're removing. It also means that you won't find the attribute used in KeyAttributes anywhere.
Once you've got all that cleaned up, you can delete the row from Attribute. There are some limitations to what you can delete, but since you're only deleting attributes you added, right, you shouldn't have a problem. However, it might be educational to talk about some attribute characteristics that describe attributes that just can't be removed. Any attribute with RequiredForGrid, IsPKAttribute, or RequiresPlatformAuthorization == 1 have to stay. Any attribute with IsNullable == 0 have to stay as well - they are required by the product. By the way, if you find your attribute in KeyAttributes or referenced by name in GeneratedJoins (or JoinAttributes), then don't do this - that attribute is being used in a relationship and removing it will change the shape of the entity graph - and you're going to break the software.
Once you've deleted the attribute reference from the metadata, you're almost done. You just need to ALTER TABLE on the *Base table to drop the column (if you don't know how to do that, you shouldn't be trying any of this stuff...) and then regenerate the views and triggers. If you look at the stored procedures in the metabase you'll find one called p_genSpecificViewAndTrigger. It takes the *Base name of the table that holds the entity as a parameter and generates a script that you can use to recreate the views and triggers. It won't recreate them for you, you need to run it, copy the resulting script, and run that script in the MSCRM database.
Now, if you're using replication you've got all kinds of other issues do deal with. You need to drop the column from the publication (look in SQL BOL for information on sp_repldropcolumn).
One thing you need to do now is reset the cache. In fact, you should probably have the application shut down while you're doing all this stuff. Simply do an IISRESET on all the web servers that host MS-CRM - that'll definitely flush the metadata cache from the platform.
This might look something like:
declare @attributeid uniqueidentifier
select @attributeid = a.attributeid
from attribute a join entity e on a.entityid = e.entityid
where e.name = 'contact'
and a.name = 'yomifullname'
-- check that the attribute isn't used anywhere
if exists (select * from keyattributes where attributeid = ...)
raiserror
-- check that the attribute isn't used anywhere
if exists (select * from keyattributes where referencedattributeid = ...)
raiserror
-- check that the attribute isn't used anywhere
if exists (select * from attributemap where sourceattributeid = ...)
raiserror
-- check that the attribute isn't used anywhere
if exists (select * from attributemap where targetattributeid = ...)
raiserror
if exists (select *
from attribute
where (ispkattribute = 1
or isrequiredforgrid = 1
or requiresplatformauthorization = 1
or isnullable = 0)
and attributeid = @attributeid)
raiserror
delete from attribute
where attributeid = @attributeid
p_genSpecificViewAndTriggers 'ContactBase'
xp_cmdshell 'IISRESET'
Like I said though, this is all unsupported stuff and isn't typically something you want to do, but in the off chance that you've done something, like put in an attribute that blows out the 8k row size limit, and you want to fix the problem, then this procedure will probably help out a bit. Most likely I've missed a number of steps in the process, probably the important ones around removing the attribute from all the replication infrastructure (and there's a lot of it).
As a reminder, if you remove any of the attributes that we shipped with the product you're probably going to break during the upgrade process. If V2 suddendly decides that one of those ill-named empty columns from V1 look useful for a feature, it might get used, and if it's not there, upgrade might not work. I haven't seen the final designs for V2 around the 8k limit but I've heard that there's a lot of work in this area - your columns might move around on you during the upgrade too. So, just careful, and be willing to put those columns back if youre upgrade testing fails (you do plan on doing upgrade testing when the product ships, right?).
posted by Michaeljon Miller at
5/23/2004 10:00:00 PM
