Convert “virtual” content types to “physical”
08 Sep 2007 22 Comments
What do you do, if you in a fit of madness/desperation/stupidity created the content types used throughout your site, through the web interface and you now want to do the “right” thing and place them in xml files packaged as a feature?
Well this is description on how to convert the existing “virtual” content type to that xml file, while maintaining the integrity of your existing site and content. Warning: I’m modifying the SharePoint content database directly – use at your own risk!
The basic idea:
- Create a content type xml file and package it in a feature (don’t deploy it yet) as you would if you started in a blank environment
- “Steal or copy” the content id for “virtual” content type from the database and use it in your xml files. In other words the existing content id that is used throughout your existing SharePoint database in the inheritance hierarchy, will remain unchanged
- Modify the database so that SharePoint sees your content type as being feature based instead of “database based”
- Deploy your new content type feature. You can now update that content type as if you had started it out xml based to begin with
It seems fairly straightforward doesn’t it? It actually is.
Howto
Information on creating xml based content type can be found here (and on many other sources), it’s really not that hard. Your deployment will be much easier after this.
Right about now would be a good time to do a backup of your content database 😉
Step 1: Steal the Content Type ID
Your content type will need a very specific ID that the SharePoint created for you when you created your new content type in the first place (either through the web frontend or API). It looks like “0x0101……” and will probably be a rather long string. You need to grab this id from the content database:
- Connect to the content database in question, probably named wss_content_XXXX (if you didn’t choose a database name the XXXX will be a guid)
- Execute the following query to find the right content typeselect ResourceDir, ContentTypeId, Definition
from dbo.ContentTypes
where ResourceDir like ‘%Article Page%’
Obviously substitute your own content type name, note that the web interface might have appended some trailing numbers to the name, so you’ll have to do a “like” selection
- Copy the ContentTypeID and insert it into the xml file. You might also want to verifiy that the definition corresponds to your that in your xml file (or just copy it over)
Step 2: Connect the Content Type to the XML File
Now you need to go into the database and modify the ContentType table to make SharePoint see it as a feature based content type as opposed to those solely in the database.
- Connect to the content database again (you might just have kept the window open)
- Execute begin tran once, just to give you an undo option
- Execute the following SQL statementUpdate dbo.ContentTypes
Set Size = 16, Definition = null, IsFromFeature = true
where ContentTypeId = 0x010100C5…..
It should only modify one row
- If the name “ResourceDir” has been mangled by the web interface, you might want to take the opportunity to fix that too now
- If you are satisfied with the update execute commit tran, otherwise rollback tran, do not forget this as you are locking the table for the duration (btw: Isn’t that a neat trick?)
Caveats
I will not take any responsibility if you lose your databases, however I would like to know if you find flaws with the procedure 😉
If you have many environments this technique only works if they have the same content type id for the same type across the farms. They will have if you did a backup/restore or content deployment from one to the other. They won’t if you created them through the web on both servers. Then you either choose which one is the master of the content or you are out of luck.
Note that if you update/change the content type xml files at a later time, the changes will only apply to the site scoped content type, not the actual list content types that the system created for every list where the type is enabled. This is very bad news, but not to worry I’ll post the fix for that in a few days (give me a bit of time).
If you modify the content type through the web interface after deployment it will once again be disconnected from the xml source, and you’ll have to complete Step 2 (only) to reconnect it.
About Søren Nielsen
Thoughts on computing, SharePoint and all the rest 
Pingback: Propagate Site Content Types to List Content Types « Thoughts on computing, SharePoint and all the rest
Pingback: Lock Down Security: My Best Practices (and restoring usability) « Thoughts on computing, SharePoint and all the rest
Hey,
nice article. We had problems updating Content Types deployed by features.(because of stupidity) Contenttypes where changed via the web interface…
It worked for me.
You might mention, that your Update statement will affect more rows if there are several Site Collections.
Thanks for this great article.
Nice article… but… What about if the Content Type was deployed through a Feature as a Content Type within a List Definition… i.e. it does not exist in the ContentTypes table!
Hi,
Very nice article,
Mark even i have the same question, i have content type schema embeded inside the list schema. Once i have upadated the content type through code, i am not able to update the list item properties of those items which were based on previous version(i.e before updating the content type)
Hi Mallikarjun
You’ll want to look at this post then:
if you change the content type throught a redeploy of the changed feature, it should work without any additional changes – the content type is still ghosted.
only when you have changed the content type throught the UI, the content type gets unghosted and stored to the database. new items (pages, lists, ..) will get the new, changed content type. Existing items will still have the old content type. Thats when you have to change the database.
Also a good link for this:
http://stsadm.blogspot.com/2008/05/propagate-content-type-changes.html
All I seem to get is:
Invalid column ‘False’.
Also, I’m not sure about the use of BEGIN TRAN within the query. Can you use this on its own without COMMIT or ROLLBACK TRAN?
And is there any reason why you need the ContentTypeID when you already have the name? Just curious.
(Note: Previous post appears to be spam.)
Instead of modifying the table via T-SQL (although I have used your technique in testing), can’t you simply delete the existing site content type and redeploy the solution, with the feature receiver’s OnActivate calling SPContentType.Update for each content type deployed?
That is the way I’m handling site columns: the feature receiver’s Deactivate deletes all its site columns, and the feature receiver’s Activate does SPField.Update on all its site columns. That Update call by the feature reciever’s Activate immediately unghosts the site column, but it doesn’t matter — all subsequent deployments will be new as the Deactivate will delete the customized site columns.
The existing list column instances of course survive all this, as they are essentially cached copies, and the Activate SPField.Update calls push all changes from the XML feature def down to them.
I’m assuming this same process will work for me with content type deployment.
BTW, I’m using Trent’s smartsolutionupgrade to automate solution redeployment (with full deactivation & reactivation).
And the spam comment is out. Thanks. 🙂
pan:
* This “Invalid column ‘False’.” means that you have a syntactic mistake in your SQL.
* You should be using ROLLBACK or COMMIT if you are using BEGIN TRAN.
* You must use the ContentTypeId because that is the key field in the table — the name is not even a name in that table.
Site columns are also rows in that table, stored under their GUIDs, by the way.
Perry that is probably a very good solution you have there.
I’ve been planning (and development will actually start in a day or two) to implement a feature to do the update through the API. It seems to me that you have achieved this in a much simpler way than I thought would be possible.
My plan were to implement some sort of comparison between an xml based content type and the one found in the database and then use the API to do the update recursively.
Thank you very much for that, we’ll start testing this shortly.
🙂
BTW, although that was succinct for me to describe, actually figuring it out (involving testing and taking notes and some false starts and confusion), took quite a while — and your pages on these subjects were very useful. I was looking in the ContentTypes & ContentTypesUsage tables monitoring what has happening, often. Your page was the only one I found that had the tip to find the underlying data in those tables. So thank you.
Thanks Perry. Good tips! I’m in a bind with site columns that will no longer update on feature reactivation. According to MS, updating site columns is supposed to be supported but I can’t work it out.
If you could provide the feature activate and deactivate code somewhere I would be very grateful.
And, Søren, if you ever manage to create a supported solution for this problem that can handle content types and site columns – please post it here! 😀
I’m not sure that posting code here will work. We’ve made a blog site, but I’ve started putting stuff up there yet.
I’ll try posting one method here — the core of feature activate is to loop through all site columns, and if they’re in use, then delete them.
Let’s see how a code snippet appears (I already condensed the lines some, in case that helps):
///
/// Search to see if there are any list column instances of specified site column (field)
///
private bool IsSiteFieldInUse(SPSite site, Guid fieldId)
{
foreach (SPWeb web in site.AllWebs) { try {
foreach (SPList list in web.Lists) { try {
SPField field = list.Fields[fieldId];
return true;
} catch(Exception exc) { }
}
} finally { web.Dispose(); }
}
return false;
}
Note: I don’t like the fact that that code is going to trip (ignored) exceptions on almost all lists of all webs, but I didn’t want the inefficiency of looping through every field of every list of every web — even nearly empty lists start out with 50+ fields.
My strategy for site columns (delete at deactivate, update at activate) does not work for content types, because the delete call will fail for content types, with an exception that the content type is in use.
I’m starting to think that Microsoft may have left this nearly unworkable.
Thanks for such a great post. I have found there is one trigger “trigger_OnDeleteContentTypes” which call one SP proc_AppendSiteQuota. When we are updating the contenttypes table how to update the siteQuota?
Sorry for late response.
I would opt for not trying to adjust the Site quotas yourself. I believe that you are entering dangerous territory if you mess with these. As they are triggers they are executed when you update the tables, it *might* work correctly.
My gut feeling is that it likely does not update the quota correctly as we are modifying the table in an unintended way.
I believe that the change in quota size must be small/negligable and it does not concern me much. Good find though.
Pingback: Content types can be unghosted too! « Olav Aukan
Interesting solution. But unfortunately not usable, since Microsoft prohibits altering the SharePoint config/content database. You’ll lose all support if you alter the database directly. Unacceptable for my clients.
In that case I suppose you shouldn’t use this in production at least. There is no supported way of resetting content types to site definition, so your options are limited and ultimately depends on the impact of the disconnected content typed for you.
In the real world this may come as a fair trade at times, regardless of the ms mumbo jumbo of loosing support (which is the official line).