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

   6:  

   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;

  16:  

  17:     DECLARE @oldUrlID bigint

  18:     DECLARE @newUrlID bigint

  19:  

  20:     exec proc_Social_GetUrlID @partitionID, @oldUrl, @oldUrlID output, @correlationId

  21:     exec proc_Social_GetUrlID @partitionID, @newUrl, @newUrlID output, @correlationId

  22:  

  23:     IF (@oldUrlID is null)

  24:     BEGIN

  25:         -- nothing to merge

  26:         set @success = 1

  27:         return

  28:     END

  29:  

  30:     IF (@newUrlID is null)

  31:     BEGIN

  32:         EXEC proc_Social_EnsureUrlID @partitionID = @partitionID, @url = @newUrl,

  33: @urlID = @newUrlID output, @correlationId = @correlationId;

  34:     END

  35:  

  36:     IF (@newUrlID is null)

  37:     BEGIN

  38:         -- not able to create new Url - error

  39:         set @success = 0

  40:         return

  41:     END

  42:  

  43:     UPDATE SocialComments

  44:     SET UrlID = @newUrlID

  45:     WHERE UrlID = @oldUrlID

  46:  

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

   1: declare @comment nvarchar(4000), @url nvarchar(2048), @newUrl nvarchar(2048)

   2: declare @partitionId uniqueidentifier

   3: declare @b bit

   4:  

   5: set @partitionId = CAST('...' as uniqueidentifier)

   6:  

   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/%'

  11:  

  12: open CommentsCursor

  13:  

  14: FETCH NEXT FROM CommentsCursor 

  15: INTO @comment, @url

  16:  

  17: while @@FETCH_STATUS = 0

  18: begin

  19:     print @url + '        ' + @comment

  20:     

  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

  28:     

  29:     FETCH NEXT FROM CommentsCursor 

  30:     INTO @comment, @url

  31: end

  32:  

  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.