Social comments are part of Sharepoint 2010 social computing features. They allow users to comment content on the site built on Sharepoint. There is one problem with using social comments – internally they are bind to the page using absolute URL of this page. It means that if you e.g. move page from one site to another or change URL of the whole site – comments will be lost.
About a month ago Microsoft released Service pack 1 for Sharepoint 2010. It contains new method MergeSocialNotes() in SocialDataManager class. There is also new PowerShell cmdlet Move-SPSocialComments – wrapper for mentioned class. The following post contains other improvements in social features: Changes to Social Computing features in SharePoint Server 2010 Service Pack 1. This is useful enhancement, but what if you don’t have SP1 installed yet, but still need to change URL for social comments? E.g. if customers didn’t approve SP1 installation yet. This is possible and in this post I will show how to do it.
First of all you need to know that social comments are stored in social database of User profile service application – in SocialComments table:
As you can see it has UrlID – foreign key to another table Urls which contains absolute URLs:
Now let’s see how SocialDataManager.MergeSocialNotes() method is implemented in SP1:
Implementation is quite simple – it just calls stored procedure dbo.proc_SocialData_MergeSocialNotes with specified parameters. This procedure is defined in SQL scripts socialsrp.sql and socialup.sql which are located in 14\Template\SQL\SPS folder (scripts are updated with SP1, previous version didn’t have this stored procedure). Both files contain the same code for dbo.proc_SocialData_MergeSocialNotes (I’m not sure why it is duplicated in 2 files):
It uses 2 other stored procedures: proc_Social_GetUrlID and proc_Social_EnsureUrlID which existed before SP1. The code of the procedure is quite simple: it ensures that newUrl exists in Urls table and after updates foreign keys UrlID in SocialComments table to relate comments with new URL. In order to use it in environment without SP1 first of all you need to create this stored procedure in the social database of User profile service application (just execute above script). After that the only thing which should be done – is to write SQL script which will update URLs based on requirements.
E.g. imaging that we need to move news from http://example.com/Departments/IT to the parent site http://example.com/Departments. During content migration we need to preserve social comments. It can be done with the following SQL script:
(This particular example can be implemented using simple Update query, but I decided to use cursor because in real life requirements may be more complicated and Update’s possibilities won’t be enough).
It gets all comments with their URLs, filter them by particular condition and updates one by one. You may also add transaction here. The last question is where to get @partitionId. The simplest way is just perform the following query on your social database:
and see what partition will be returned for the comments in your web application.
When you will execute this script – social comments will be merged to the news migrated to parent site.
Of course this half-hack: it manipulates social database directly without object model. However as we saw – object model is just a thin wrapper over used stored procedure. Also this approach doesn’t require SP1 installed on production – which can be important for real environments. Anyway I hope that this information will be useful in your work.