Saturday, July 23, 2011

Change page URL for social comments without SP1 in Sharepoint

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:

   1: internal static bool MergeSocialNotes(UserProfileApplication userProfileApplication,
   2: Guid partitionID, string oldUrl, string newUrl)
   3: {
   4:     if (null == userProfileApplication)
   5:     {
   6:         throw new ArgumentNullException("userProfileApplication");
   7:     }
   8:     if (string.IsNullOrEmpty(oldUrl))
   9:     {
  10:         throw new ArgumentNullException("oldUrl");
  11:     }
  12:     if (string.IsNullOrEmpty(newUrl))
  13:     {
  14:         throw new ArgumentNullException("newUrl");
  15:     }
  16:     if (!userProfileApplication.CheckAdministrationAccess(
  17: UserProfileApplicationAdminRights.ManageSocialData))
  18:     {
  19:         throw new UnauthorizedAccessException();
  20:     }
  21:     using (SqlCommand command = new SqlCommand("dbo.proc_SocialData_MergeSocialNotes"))
  22:     {
  23:         command.CommandType = CommandType.StoredProcedure;
  24:         command.Parameters.Add("@partitionID", SqlDbType.UniqueIdentifier).Value = partitionID;
  25:         command.Parameters.Add("@correlationId", SqlDbType.UniqueIdentifier).Value = ULS.CorrelationGet();
  26:         command.Parameters.Add("@oldUrl", SqlDbType.VarChar).Value = oldUrl;
  27:         command.Parameters.Add("@newUrl", SqlDbType.VarChar).Value = newUrl;
  28:         SqlParameter parameter = new SqlParameter("@success", SqlDbType.Bit);
  29:         parameter.Direction = ParameterDirection.Output;
  30:         command.Parameters.Add(parameter);
  31:         userProfileApplication.SocialDatabase.SqlSession.ExecuteNonQuery(command);
  32:         if (!((bool) command.Parameters["@success"].Value))
  33:         {
  34:             return false;
  35:         }
  36:     }
  37:     return true;
  38: }

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):

   1: IF  EXISTS (SELECT * FROM dbo.sysobjects
   2: WHERE id = OBJECT_ID(N'[dbo].[proc_SocialData_MergeSocialNotes]')
   3: AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
   4: DROP PROCEDURE [dbo].[proc_SocialData_MergeSocialNotes] 
   5: GO
   7: CREATE PROCEDURE [dbo].[proc_SocialData_MergeSocialNotes] 
   8:     @partitionID uniqueidentifier,
   9:     @oldUrl varchar(2048),
  10:     @newUrl varchar(2048),
  11:     @success bit = null output,
  12:     @correlationId uniqueidentifier = null
  13: AS
  14: BEGIN
  15:     SET NOCOUNT ON;
  17:     DECLARE @oldUrlID bigint
  18:     DECLARE @newUrlID bigint
  20:     exec proc_Social_GetUrlID @partitionID, @oldUrl, @oldUrlID output, @correlationId
  21:     exec proc_Social_GetUrlID @partitionID, @newUrl, @newUrlID output, @correlationId
  23:     IF (@oldUrlID is null)
  24:     BEGIN
  25:         -- nothing to merge
  26:         set @success = 1
  27:         return
  28:     END
  30:     IF (@newUrlID is null)
  31:     BEGIN
  32:         EXEC proc_Social_EnsureUrlID @partitionID = @partitionID, @url = @newUrl,
  33: @urlID = @newUrlID output, @correlationId = @correlationId;
  34:     END
  36:     IF (@newUrlID is null)
  37:     BEGIN
  38:         -- not able to create new Url - error
  39:         set @success = 0
  40:         return
  41:     END
  43:     UPDATE SocialComments
  44:     SET UrlID = @newUrlID
  45:     WHERE UrlID = @oldUrlID
  47:     -- we succeed, even if there is no data to migrate
  48:     set @success = 1
  49: END
  50: GO

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 to the parent site During content migration we need to preserve social comments. It can be done with the following SQL script:

   1: declare @comment nvarchar(4000), @url nvarchar(2048), @newUrl nvarchar(2048)
   2: declare @partitionId uniqueidentifier
   3: declare @b bit
   5: set @partitionId = CAST('...' as uniqueidentifier)
   7: declare CommentsCursor cursor for
   8: select sc.Comment, u.Url from dbo.SocialComments sc
   9:     inner join dbo.Urls u on sc.UrlID = u.UrlID
  10: where Url like '%/Departmens/IT/%'
  12: open CommentsCursor
  14: FETCH NEXT FROM CommentsCursor 
  15: INTO @comment, @url
  17: while @@FETCH_STATUS = 0
  18: begin
  19:     print @url + '        ' + @comment
  21:     set @newUrl = replace(@url, '/Departmens/IT/', '/Departmens/')
  22:     if @newUrl <> @url
  23:     begin
  24:         print 'Change url ' + @url + ' -> ' + @newUrl
  25:         exec proc_SocialData_MergeSocialNotes @partitionId, @url, @newUrl, @b output
  26:         print 'Result: ' + str(@b)
  27:     end
  29:     FETCH NEXT FROM CommentsCursor 
  30:     INTO @comment, @url
  31: end
  33: close CommentsCursor
  34: deallocate CommentsCursor

(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:

   1: select sc.Comment, sc.PartitionID, u.Url, u.PartitionID from dbo.SocialComments sc
   2:     inner join dbo.Urls u on sc.UrlID = u.UrlID

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.


  1. My social tags are pointing to the url http://xxxx I need them to point to the URL - How do I do that?
    I have default site at: http://xxxx and AAM for public at

  2. hi shobs,
    as I understand you shouldn't use the technique from this article. It covers the case when content was moved. In your case your content was not moved. It is still accessible by http://xxx URL. If you will change URLs on - then comments will be shown only on URL, but not on http://xxx (i.e. always only one).

    But it is interesting question: why Sharepoint doesn't show social comments on the all authentication zones. Probably it was made by design: you can use different authentication providers for different zones and users from one zone won't be able to see users from other zone and if you change URL using described technique it may lead to unpredictable results.

  3. Actually the tag URL is pointing to http://xxxx eventhough I access the SP site from and tag the site from public URL.

  4. shobs,
    this is interesting. I will investigate it when will have time. However it may be by design - I still not sure that this is a bug.

  5. This comment has been removed by the author.

  6. Recently we are implementing SSL in Sharepoint 2013. Can we apply this for moving content from http to https?

  7. ashish, if comments are missing after that then yes you may try it out.