How to update conflict resolver when upgrading from SQL-Server 2005 to SQL-Server 2008

Question

We have recently upgraded from SQL Server 2005 to SQL Server 2008 (R2, SP1). This upgrade included some publications, where all tables are published with a default conflict resolver based on the "later wins" principle. Its smart name is 'Microsoft SQL Server DATETIME (Later Wins) Conflict Resolver', and the corresponding dll file is ssrmax.dll.

As you all know, once a table is published with a conflict resolver, the same conflict resolver must be used in all later publications using this table. Fair enough, but, when adding previously published tables to new publications, and specifying the very same conflict resolver to be used for this table, we are getting an error message:

use [myDb]
exec sp_addmergearticle 
    @publication = N'myDb_Pub', 
    @article = N'Tbl_blablabla', 
    @source_owner = N'dbo', 
    @source_object = N'Tbl_blablabla', 
    @type = N'table', 
    @description = N'', 
    @creation_script = N'', 
    @pre_creation_cmd = N'drop', 
    @schema_option = 0x000000000C034FD1, 
    @identityrangemanagementoption = N'none', 
    @destination_owner = N'dbo', 
    @force_reinit_subscription = 1, 
    @column_tracking = N'false', 
    @article_resolver = N'Microsoft SQL Server DATETIME (Later Wins) Conflict Resolver', 
    @subset_filterclause = N'', 
    @resolver_info = N'ddmaj', 
    @vertical_partition = N'false', 
    @verify_resolver_signature = 0, 
    @allow_interactive_resolver = N'false', 
    @fast_multicol_updateproc = N'true', 
    @check_permissions = 0, 
    @subscriber_upload_options = 0, 
    @delete_tracking = N'true', 
    @compensate_for_errors = N'false', 
    @stream_blob_columns = N'false', 
    @partition_options = 0
GO

And this is the error we get:

The article '...' already exists in another publication with a different article resolver.

By trying to understand how the same conflict resolver is not considered by the machine as 'the same conflict resolver', I discovered that there were two conflict resolvers with the same name, different versions, in the registry:

the 2005 version:

• file ssrmax.dll,
• version 2005.90.4035.0,
• cls_id D604B4B5-686B-4304-9613-C4F82B527B10

the 2008 version:

• file ssrmax.dll,
• version 2009.100.2500.0,
• cls_id 77209412-47CF-49AF-A347-DCF7EE481277

And I checked that our 2008 server is considering the second one as the 'available custom resolver' (I got this by running sp_enumcustomresolvers). The problem is that both references are available in the registry, so I guess that old publications do refer to the 2005 version, while new publications try to refere to the 2008 version, which is indeed different from the previous one.

So the question is: how can I have the server consider only one of these 2 versions, and this (of course) without having to drop and recreate the existing publications (which would turn our life into hell for the next 2 weeks).

Charliecharline

Answer 1

Well .. so nobody got an answer. But I think I (finally) got it. Guess what... it is somewhere in the metamodel (as usual)!

• When adding an item to the subscription, the new conflict resolver references to be used by the stored procedure come from the [distribution].[MSmerge_articleresolver] table
• But, for existing subscriptions, previous conflict resolver references are stored in the system tables of the publishing database, ie [sysmergearticles], [sysmergeextendedarticlesview], and [sysmergepartitioninfoview]

So we have on one side an item initialy published with SQLSERVER 2005, where the publication references the 2005 conflict resolver, as per the publishing database metamodel. On the other side, the machine will attempt to add the same item to a new publication, this time with a default reference to the conflict resolver available in the distibution database, which is indeed different from the 2005 one ....

To illustrate this, you can check the following

USE distribution
go
SELECT article_resolver, resolver_clsid
  FROM [MSmerge_articleresolver] WHERE article_resolver like '%Later Wins%'
  GO

Then,

USE myPublicationDatabase
go
SELECT article_resolver, resolver_clsid
  FROM [sysmergearticles] WHERE article_resolver like '%Later Wins%'
  GO
 SELECT article_resolver, resolver_clsid
  FROM [sysmergeextendedarticlesview] WHERE article_resolver like '%Later Wins%'
  GO
 SELECT article_resolver, resolver_clsid
  FROM [sysmergepartitioninfoview] WHERE article_resolver like '%Later Wins%'
  GO  

So it seems that I should update either the references in the distribution database or the references in the publication database. Let's give it a try!

Charliecharline

Answer 2

Thanks, had something similar on a re-publisher where the subscriber article had a CLSID that made no sense on the server (looked with Regedit) but when trying to add the article to a publication would produce said error.

Updated the resolver_clsid field of sysMergeArticles table for the subscribed article with the clisd it was trying to get

{

declare @resolver_clsid  nvarchar(50)

 exec sys.sp_lookupcustomresolver N'Microsoft SQL Server DATETIME (Earlier Wins) Conflict Resolver', @resolver_clsid OUTPUT


 select @resolver_clsid 

}

and could then add the article

Brandling