How to: Specify a Merge Article Resolver (Replication Transact-SQL Programming)
When defining an article for a merge publication, you can specify a custom conflict resolver. For more information, see Advanced Merge Replication Conflict Detection and Resolution. Microsoft SQL Server includes a number of predefined custom conflict resolvers, or you can write your own. If you need to implement custom logic that is executed for each replicated row, not just for conflicting rows, see How to: Implement a Business Logic Handler for a Merge Article (Replication Programming).
To register a custom conflict resolver
If you plan to register your own custom conflict resolver, create one of the following types:
- Managed code-based resolver as a business logic handler. For more information, see How to: Implement a Business Logic Handler for a Merge Article (Replication Programming).
- Stored procedure-based resolver. For more information, see How to: Implement a Stored Procedure-Based Custom Conflict Resolver for a Merge Article (Replication Transact-SQL Programming).
- COM-based resolver. For more information, see How to: Implement a COM-Based Custom Conflict Resolver for a Merge Article (Replication Programming).
To determine if the desired resolver is already registered, execute sp_enumcustomresolvers (Transact-SQL) at the Publisher on any database. This displays a description of the custom resolver as well as the class identifier (CLSID) for each COM-based resolver registered at the Distributor or information on the managed assembly for each business logic handler registered at the Distributor.
If the desired custom resolver is not already registered, execute sp_registercustomresolver (Transact-SQL) at the Distributor. Specify a name for the resolver for @article_resolver; for a business logic handler, this is the friendly name of the assembly. For COM-based resolvers, specify the CLSID of the DLL for @resolver_clsid, and for a business logic handler, specify a value of true for @is_dotnet_assembly, the name of the assembly for @dotnet_assembly_name, and the fully-qualified name of the class that overrides BusinessLogicModule for @dotnet_class_name.
Note
If a business logic handler assembly is not deployed in the same directory as the Merge Agent executable, in the same directory as the application that synchronously starts the Merge Agent, or in the global assembly cache (GAC), you need to specify the full path with the assembly name for @dotnet_assembly_name.
If the resolver is a COM-based resolver:
Copy the custom resolver DLL to the Distributor for push subscriptions or to the Subscriber for pull subscriptions.
Note
Microsoft custom resolvers can be found in the C:\Program Files\Microsoft SQL Server\90\COM directory.
Use regsvr32.exe to register the custom resolver DLL with the operating system. For example, executing the following from the command prompt registers the SQL Server Additive Conflict Resolver:
regsvr32 ssradd.dll
If the resolver is a business logic handler, deploy the assembly in the same folder as the Merge Agent executable (replmerg.exe), in the same folder as an application that invokes the Merge Agent, or in the folder specified for the @dotnet_assembly_name parameter in step 3.
Note
The default installation location of the Merge Agent executable is C:\Program Files\Microsoft SQL Server\90\COM.
To specify a custom resolver when defining a merge article
If you plan to use a custom conflict resolver, create and register the resolver using the above procedure.
At the Publisher, execute sp_enumcustomresolvers (Transact-SQL) and note the name of the desired custom resolver in the value field of result set.
At the Publisher on the publication database, execute sp_addmergearticle (Transact-SQL). Specify the name of the resolver from step 2 for @article_resolver and any required input to the custom resolver using the @resolver_info parameter. For stored procedure-based custom resolvers, @resolver_info is the name of the stored procedure. For more information about required input for resolvers supplied by Microsoft, see Microsoft COM-Based Resolvers.
To specify or change a custom resolver for an existing merge article
To determine if a custom resolver has been defined for an article, or to get the name of the resolver, execute sp_helpmergearticle (Transact-SQL). If there is a custom resolver defined for the article, its name will be displayed in the article_resolver field. Any input supplied to the resolver will be displayed in the resolver_info field of the result set.
At the Publisher, execute sp_enumcustomresolvers (Transact-SQL) and note the name of the desired custom resolver in the value field of the result set.
At the Publisher on the publication database, execute sp_changemergearticle (Transact-SQL). Specify a value of article_resolver, including the full path for business logic handlers, for @property, and the name of the desired custom resolver from step 2 for @value.
To change any required input for the custom resolver, execute sp_changemergearticle (Transact-SQL) again. Specify a value of resolver_info for @property and any required input to the custom resolver for @value. For stored procedure-based custom resolvers, @resolver_info is the name of the stored procedure. For more information about required input, see Microsoft COM-Based Resolvers.
To unregister a custom conflict resolver
At the Publisher, execute sp_enumcustomresolvers (Transact-SQL) and note the name of the custom resolver to remove in the value field of the result set.
Execute sp_unregistercustomresolver (Transact-SQL) at the Distributor. Specify the full name of the custom resolver from step 1 for @article_resolver.
Example
This example creates a new article and specifies that the SQL Server Averaging Conflict Resolver be used to calculate the average of the UnitPrice column when conflicts occur.
DECLARE @publication AS sysname;
DECLARE @article AS sysname;
SET @publication = 'AdvWorksSalesOrdersMerge';
SET @article = 'Products';
EXEC sp_addmergearticle
@publication = @publication,
@article = @article,
@source_object = @article,
@article_resolver = 'Microsoft SQL Server Averaging Conflict Resolver',
@resolver_info = 'UnitPrice';
GO
This example changes an article to specify using the SQL Server Additive Conflict Resolver to calculate the sum of the UnitsOnOrder column when conflicts occur.
DECLARE @publication AS sysname;
DECLARE @article AS sysname;
SET @publication = 'AdvWorksSalesOrdersMerge';
SET @article = 'Products';
EXEC sp_changemergearticle
@publication = @publication,
@article = @article,
@property='article_resolver',
@value='Microsoft SQL Server Additive Conflict Resolver';
EXEC sp_changemergearticle
@publication = @publication,
@article = @article,
@property='resolver_info',
@value='UnitsOnOrder';
GO
See Also
Tasks
How to: Implement a Business Logic Handler for a Merge Article (Replication Programming)
Other Resources
Advanced Merge Replication Conflict Detection and Resolution