How to upgrade conflict resolution when upgrading from SQL Server 2005 to SQL Server 2008

Question

We recently upgraded from SQL Server 2005 to SQL Server 2008 (R2, SP1). This update included some publications that publish all tables using default conflict resolution based on the "later wins" principle. His intellectual name is "Microsoft SQL Server DATETIME (later wins)" Conflict Recover ", and the corresponding DLL file is ssrmax.dll.

As you all know, after publishing a table using conflict resolution, the same conflict resolver should be used in all subsequent publications using this table. Fair enough, but when adding previously published tables to new publications and defining the same conflict resolution that will be used for this table, an error message appears:

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. 

Trying to understand how the same conflict resolver is not considered by the machine as "the same conflict resolver", I found that there were two conflict resolutions with the same name in the registry, different versions:

version 2005:

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

version 2008:

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

And I checked that our 2008 server treats the second as an “accessible custom resolver” (I got this by running sp_enumcustomresolvers). The problem is that both links are available in the registry, so I think that the old publications refer to the 2005 version, while the new publications try to link to the 2008 version, which is really different from the previous one.

So, the question is: how can I only consider one of these two versions of the server, and this (of course), without having to discard and recreate existing publications (which will turn our lives into hell over the next 2 weeks).

Answer 1

Well, so no one got an answer. But I think that (finally) I understood. Guess what ... it's somewhere in the metamodel (as usual)!

• When an item is added to a subscription, new conflict resolution links used by the stored procedure come from the [distribution] table. [MSmerge_articleresolver]
• But for existing signatures, previous conflict resolution links are stored in the system tables of the publication database, that is, [sysmergearticles], [sysmergeextendedarticlesview] and [sysmergepartitioninfoview]

Thus, on the one hand, we have an initial element published with SQLSERVER 2005, where the publication refers to the conflict resolver of 2005, according to the publication publication metamodel. On the other hand, the machine will try to add the same element to the new publication, this time with a default link to the resolution of the conflict available in the distribution database, which is really different from the 2005 version ....

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 

Therefore, it seems to me that I should update the links in the distribution database or the links in the publication database. Give it a try!

Answer 2

Thank you, something similar to a re-publisher, where the subscriber’s article had a CLSID that didn’t make any sense on the server (looked from Regedit), but when trying to add the article to the publication it will produce the indicated error.

Updated resolver_clsid field of sysMergeArticles table for a signed article with a click that he 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 then can add an article