Corrupt site column cannot be deleted

Problem: I have deployed a site column (field) and Content Type declaratively, the site column is not valid and I get the following error when looking at my site columns using SharePoint 2010's UI: "Field type xxx is not installed properly. Go to the list settings page to delete this field."

Initial Hypothesis:  The cause of the issue is simply that I create a site column of type "Bool", Type bool does not exist, boolean is the correct type, as show below:

Resolution: Delete the Site Column, can't do this I tried SharePoint's UI (as shown in the problem image), SharePoint Designer, Solution Explorer with CKSDev extensions.  All tools error as the Site Column object cannot in instantiated.

So now it was time to try Powershell which will fail as it can't instantiate the Site column object either. 
PS> $web = Get-SPweb http://demo1/sites/sponline
PS> $fields = $web.Fields
PS> foreach($field in $fields) {  write-host $field.Id }

This proves the site column exists but it is corrupted.  I tried deleting it using PS.
Obviously the site column won't be removed. 

Summary:  At this point I am in a knot.  The site column is causing errors and it can't be removed, so the option is to delete the site collection or to do the bad stuff and fix the error directly in the database.

                         =================

Problem:  A corrupt site column needs to be deleted using T-SQL.

Initial Hypothesis: Find the corrupt/offending field, as you can't use SharePoint's API to remove the field, it will need to be done directly in the content database using T-SQL.

Resolution:

1. Find the site column causing the issue using Powershell.
2. I opened the field.text file and found:   I did a search for "bool" in the text document, there are a lot of results but all the other found "boolean".  So simply search for the type that is shown in your error message.
3. Find the content database that is storing the offending Site Column using Central Admin.

4.> Open "Microsoft SQL Server Management Studio" and perform a query to find the offending record

T-SQL:  SELECT * FROM [Demo_PortalDB].[dbo].[ContentTypes] WHERE Definition LIKE ('%332B55548E6C%')
5.> Delete the offending record

T-SQL: DELETE FROM [Demo_PortalDB].[dbo].[ContentTypes] WHERE Definition LIKE ('%332B55548E6C%')

Summary:  Editing SharePoint database directly is not supported by Microsoft and should not be done under any circumstances.  I can't find another way to fix this issue - so if anyone has a suggestions I'd love to know it.

Error Message Examples:
Field type Bool is not installed properly. Go to the list settings page to delete this field.
Field type Financials Gross Value Certified is not installed properly. Go to the list settings page to delete this field.
Field type UKTelephone is not installed properly. Go to the list settings page to delete this field.

Error occurred in deployment step 'Activate Feature': A duplicate field name "xx" was found.

Problem: A site column/ field occurs more than once.  I.e. there is a duplicate field name.
Initial Hypothesis:  If you are deploying site columns declaritively (CAML), you will have 2 site columns with the same name and type.  This commonly happens with "Email" so be more specific or reuse the exisitng site column. 
Resolution:  Ensure thre duplicate field name is unique or reuse an exisitng suitable site column.

If you have check that you don't have the issue above and you are deploying using Visual Studio 2010 use the following advice.  This occurs after you have renamed or deleted the site columns as explained above.

Problem: Deploying site columns using VS results in the error "Error occurred in the deployment step 'Activate Features': A duplicate field name "Email" was found. 

Initial Hypothesis: Ensure you don't have duplicate fields as show in the above problem solver.  Activate the feature using ststadm, it will now work.  The problem is that Visual Studio on it's 1st build and deployment caches SharePoint information, this was done by MS to speed up subsequent deployments of code.  However, the cached VS SP info is now wrong so restart VS 2010 and deploy again.

Resolution:

1. Retract the solution that holds the failing feature;
2. Close and reopen the Visual Studio Project; and
3. Deploy the feature using VS 2010. 

Reverse Engineer Lists, Content Types and Site Columns and retrieving the CAML

Problem: You need to create deployable lists, this should be done using a feature.  You can create a lists using the UI and then get the CAML that created the list.  The code will have some hicups but it a good place to start for building your Lists based on List definitions, content types and their corresponding site columns.

Resolution:
Get the CAML and create your feature based on the declaritive CAML
http://%7bwebapplication%7d/%7Bsite%7D/_vti_bin/owssvr.dll?Cmd=ExportList&List={ListGuid}
Thanks to Sahil Malik

Read More:
http://blah.winsmarts.com/2008-2-Dev_Tip__The_SharePoint_University_of_Reverse_Engineering.aspx
http://www.synergyonline.com/blog/blog-moss/Lists/Posts/Post.aspx?ID=24

Download: SharePoint 2010 sandbox project example that creates 2 lists with a lookup field. Updated 24/03/2011

Creating Site Columns / fields declaratively

Problem: Adding site columns decoratively has nuances.  This post adds information for getting fields/site columns correct when building them declarative using a feature to deploy to a Site Collection.

Resolution: You can use the following reverse engineering technique on the list.  This will show you the xml/CAML to create lists.  This works in MOSS & SP2010.  I used the list reverse engineering technique to figure out how to create a Lookup site columns with multiple values. 

Steps to Reverse engineering a SharePoint List:
1.> Create a list, content type and site column for the lookup (or field you want) using the SharePoint's UI
2.Then retrieve the list using a formatted url to see the native CAML used to create the site column.  http://url/_vti_bin/owssvr.dll?Cmd=ExportList&List=listguid
This approach saved me a lot of time today.

Tips:
CAML attributes required for each field element are: ID, Name, DisplayName, Group, and Type.
MSDN field element attributes
Attributes explained
Type="Note" will create a site column in the UI showing "Multiple line of text".  Attribute to get full html imput capabilities are RichText="True" (default is false)  & RichTextMode="FullHtml".
Type="Choice" can have a default value selected as shown below:

Built-in Site Column Append-only Comment can be referenced in a content type to provide comments that are appended and basic html is available.

Lookup columns work in SP2010 without having to create an event receiver to setup the lookup relationship using the list guid as is the case in MOSS.  Multiple lookups are possible also simple set the field attributes Type="Lookup" & Mult="TRUE".

Tip: Updated 18 Oct 2010 - CKSDev has the functionality to import Site Columns, this is useful in that you may of created the SiteColumns via the UI on a prototype site and you can not easily get the xml to create the site columns/fields declaritively. http://blog.mastykarz.nl/cksdev-support-importing-site-columns/

Creating lookup columns and list declaratively in SP2010

Problem: When creating lists via a feature, you may want to link SharePoint 2010 lists.
Initial Hypothesis: In MOSS to link to SharePoint 2007 lists you had to do it via the UI or via a deployable feature.  The list was created declaratively using XML (CAML).   Lastly the lookup column needed to be created using a feature receiver.  As in MOSS, in SP2010 you can create a lookup column using the UI but this is not a reusable deployment method and falls prey to errors.  SP2010 allows you to create lookup lists using declarative CAML.

Resolution: Create the lists, add site columns (fields), then add the site columns to a content type and lastly generate the list definition & list instance.  Steps are detailed below:

• In an elements file declaratively create a lookup column that uses the specified list as shown below: 

• Add the new lookup column to a content type:


• Your existing/create a list definition that derives from your content type.

Note: The Lookup Site column needs to specify the list it is looking up when you declare the XML.
More Info:
Creating lists declaritively in SP2010 - Post outlines creating SharePoint lists, coupled with this post you can create lists that are related using a feature to create your lists.
Available field types

Creating SharePoint Lists declaratively using Visual Studio 2010

Problem: Create a custom list to hold configuration values in a list.
Resolution Overview: Using VS 2010 create a feature that deploys the new list with content.
1.> Create Site Columns/fields;
2.> Create the Content Type using the Site Columns;
3.> Create the List Definition from the Content Type; and
4.> Add items to the list instance elements.xml.

Resolution Steps:

1.> Create Site Columns by adding a new "Empty Element" item in VS.

Declaratively add the site column to the elements.xml file;

Ensure your element file has been added to a feature for deployment;

Deploy the feature and ensure the Site Column has been add using the UI "Site Setting" > "Site Columns";

2.> Create the Content Type using the Site Columns

Using VS 2010 create a new Content Type;

Add caption

Using the existing Site Column create the Content Type declaratively;

Check the Content Type is create after you have deployed; 

3.> Create the List Definition from the Content Type

Follow the wizard and select the newly created Content Type "Configuration";
Check the List definition has created the elements.xml and schema.xml files;

Edit the Schema.xml File to Show the new columns defined int he content type (otherwise you will only see the "Title" column);

4.> Add items to the list instance elements.xml

Ensure the List is created

More Info: Creating a Custom field, Content Type, List def & List instance on MSDN  PB Change:  27 Aug 2010

OOTB available field Types in SP2010

Tip: The fields/site columns & FieldRefs used in the elements files can be added thru the VS2010 tools.  VS2010 > View > Toolbar

Tip: Updated 18 Oct 2010 - CKSDev has the functionality to import Site Columns, this is useful in that you may of created the SiteColumns via the UI on a prototype site and you can not easily get the xml to create the site columns/fields declaritively.  http://blog.mastykarz.nl/cksdev-support-importing-site-columns/

Download: SharePoint 2010 sandbox project example that creates 2 lists with a lookup field.  Updated 24/03/2011

Using PowerShell to retrieve SharePoint site column guids

Problem: I am creating a Content Type that uses 1 of the built in site columns (field column), I need the guid of a OOTB site column.  I am looking to use the OOTB "Append-Only Comments" site column.
Resolution: Open PowerShell and execute the cmd:
$web = Get-SPweb http://demo1
write-host $web.Fields["Append-Only Comments"].Id

Tip: Don't add xml comments into declaritive content type creation, it stops the activation without any warning. 

SharePoint 2010 Error occurred in deployment of step ‘Activate Features’: the field with ID defined in feature was found in the current site collection or in a sub site.

Problem: I can't redeploy Site columns using Visual studio 2010 to an SP2010 site collection.  I get the error "Error occurred in deployment of step ‘Activate Features’: the field with ID defined in feature was found in the current site collection or in a sub site".
Resolution: Retract wsp, restart VS2010, open the project and deploy the solution.  VS2010 is caching data.
Microsoft kb

SharePoint 2010 UI/browser help urls

Site settings url: http://...../_layouts/settings.aspx
Determine CAML to create site columns  http://url/_vti_bin/owssvr.dll?Cmd=ExportList&List=listguid
Remove web parts from a page http://url/_layouts/spcontnt.aspx?&url=page/_layouts/spcontnt.aspx?&url=page