Solved

Edit or remove a custom field in Schema manager

Posted on 2004-09-17
7
581 Views
Last Modified: 2013-12-03
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
Comment
Question by:PTMANG
  • 3
  • 2
7 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 12123500
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 12309228
Grade C ????
0
 

Author Comment

by:PTMANG
ID: 12309244
Sorry, didn't look before click- meant B...could the mods change this?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 12311972
Thanks!
it's not for the points, just wanted to understand :-)
Cheers
0
 

Author Comment

by:PTMANG
ID: 12312030
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Supply Chain Event Management Supply chain visibility is the ability of a company to track its products and business processes in supply chain towards building operational excellence. The goal of supply chain visibility is to improve and strength…
Messaging apps are amazing tools with the power to do a lot of good, but the truth is the process of collaborating with coworkers requires relationships established through meaningful communication - the kind of communication that only happens face-…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now