Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 589
  • Last Modified:

Edit or remove a custom field in Schema manager

Is there any way to edit or remove a custom field attribute which has already been published in CRM 1.2
We want to remove some obsoleted customizations and alter the type of some established schema field (from string to picklist, etc)
Thanks-
PTM
0
PTMANG
Asked:
PTMANG
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Yes, but NOT with the CRM GUI.
Now, unfortunately, this won't be very "easy" unless you have practice with SQL Server: Profiler & Query Analyser.
In short, the REASON why CRM doesn't allow to remove a column is due to the simple fact that SQL Server doesn't allow to drop a column. You might say: STOP, EM allows to drop columns! Indeed, but if you look at the script that is generated by EM to "drop" the column, it basically renames the table, creates a new one without the "dropped" column, and copies over the data. So in fact you can still blame M$, but not CRM :-)


To know EXACTLY what happens, you should (on a test environment) prepare everything so you are alone on the environment. Don't forget to disable all the SQL jobs for CRM etc...

Then, before adding a new property, start the SQL Profiler to trace all SQL acticity (SQL statements & PROCs).
First, there will be some SELECT's to check if what you entered is valid:
* check if the attribute name is unique for the schema item
* check if the updated underlying schema item table would not become to large (it needs to stay < 8KB size)
* etc

The first insert will look like this:
INSERT INTO Attribute (AttributeId, EntityId, AttributeTypeId, Name, PhysicalName, LogicalName, Description, LocalizedName, Length, IsNullable, ValidForUpdateAPI, ValidForCreateAPI, ValidForReadAPI, VisibleToPlatform, RequiresPlatformAuthorization, ColumnNumber, IsPKAttribute, IsCustomField,IsRequiredForGrid,IsLogical ,ReferencedEntityObjectTypeCode, DisplayMask) VALUES(N'{EFC0BC0E-CEB4-4178-9821-90A2015855F2}', N'{115A1377-9CAC-4267-A169-BB7067F6B6D6}', N'{00000000-0000-0000-00AA-11000000001E}', N'CFSghe2', N'CFSghe2', N'CFSghe2', N'ghe4', N'ghe3', 82, 1,  1, 1, 1, 1, 0, 157, 0, 1,0,0,0, 402653184)
This would be the last DELETE to perform to rollback the new item.

Note that the underlying table for a Schema Item called "XY" will be "XYBase"

Then, the table is altered, adding the column, so you need to remove that column (this will require the table to be recreated)

Now, after that replication part needs to be solved:
exec sp_repladdcolumn  'AccountBase', 'CFSghe2', 'nvarchar (41) NULL'

Also here, there is no function sp_repldelcolumn, you have to recreate the entire article (ie table) for the replication.
Now, if you don't want to replicate among different SQL servers, you could live without this...

Hope this helps
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Grade C ????
0
 
PTMANGAuthor Commented:
Sorry, didn't look before click- meant B...could the mods change this?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Thanks!
it's not for the points, just wanted to understand :-)
Cheers
0
 
PTMANGAuthor Commented:
No prob. It turns out that its just not feasable to do this, unless we rename fields, or change the picklist criteria.
This all happened beacuse someone who was adding in fields was specifying up to 1000 lines per entry within contact lists
(which ate up precious SQL db allocated space)
Thanks for the effort.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now