Tuesday, 3 May 2011

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.

11 comments:

sharepointsmygame said...

We came up with a PowerShell script to remove/replace content types in our farm. I'm thinking you can do the same with an errant site column.

What I got today from an MS Support Tech is that once you have the column removed from the sites, you go to PowerShell and run disable-spfeature fields and then enable-spfeature fields. This should reset the columns to their defaults.

Paul Beck said...

Hi sharepointsmygame,

I can't get a handle on the site column using PS, the best I could do is remove the content type using PowerShell or as described in the post manaual delete the field in the content db (big no-no).

I'm not following how you correct the issue can you post the PowerShell?

thanks - paul

K_Rock said...

I am having the same the same exact issue that you descibe, kind of, been through all of your steps.

I just want to remove the site column from the site and remove it from the Content Types, not delete the existing Content Types.

It looks as though you are deleting both the Column and Content Type - From the database; which is not where I want to be deleting data from!!!

I too would be curious how 'sharepointsmygame' would solve this!

K_Rock said...

Second thought - still modifing the database, but what if you just change the 'FieldType' on the column from 'bool' to 'boolean'; rather than deleting it all together.

Would changing it then allow the web pages to load to delete the field through the system, rather than through the database?

Paul Beck said...

Hi K_Rock,

This is a pretty nasty situation to get into and highlights why companies should use test and pre-production environments.

I personally have only deleted the erronous data out of the database on a development machine. As you highlight manually deleting or modifying SP databases is not appropriate in SharePoint.

Lastly, editing the erroneous record in the database - I don't know if this will fix the isssue, be good to post this if it does. Personally I wanted to clean the error and install the site column from scratch.

Anonymous said...

It actually highlights how poorly SharePoint is coded/designed.

SharePoint Recovery SOftware said...

Yes, you are right!! The columns cannot be deleted. It gets inaccessible due to corrupt site column. In this case, you first made a blank site & after that recover all content of the site with the help of SharePoint Recovery Software. This software also has features to upload all recovered content to the new site. After that you can easily perform any action including delete site column on new recovered site. The procedure will help you to get back all your corrupt site data.

Scheevel said...

Awesome! Thank you so much! I had this exact problem, followed your clear instructions and fixed my "unfixable" issue. In researching this I've seen lot's of folks in the blogosphere experiencing this same issue, your's is the only solution that works. Thanks again!

Joel De Leon said...

Try unsealing the fields and try deleting it again. You can either use SPD to delete the fields or create a feature to delete it.

Paul Beck said...

Hi Joel, I don't follow what "Unsealing the fields" means? paul

santhosh said...

Thanks for the post, helped me :)

Post a Comment