Edit

Specify a merge article resolver

Applies to: SQL Server

This article describes how to specify a merge article resolver in SQL Server by using SQL Server Management Studio or Transact-SQL.

Recommendations

  • Merge replication supports the following types of article resolvers:

  • To use a resolver other than the default resolver, you must copy the resolver to the computer where the Merge Agent runs and register it. If you're using a business logic handler, you must also register it at the Publisher. The Merge Agent runs at:

    • The Distributor for a push subscription

    • The Subscriber for a pull subscription

    • The Internet Information Services (IIS) server for a pull subscription that uses Web synchronization

Use SQL Server Management Studio

After you register the resolver, specify that an article should use the resolver on the Resolver tab of the Article Properties - <Article> dialog box. You can access this dialog box in the New Publication Wizard and the Publication Properties - <Publication> dialog box. For more information about using the wizard and accessing the dialog box, see Create a publication and View and Modify Publication Properties.

  1. On the Articles page of the New Publication Wizard or the Publication Properties - <Publication> dialog box, select a table.

  2. Select Article Properties, and then select Set Properties of Highlighted Table Article.

  3. On the Article Properties - <Article> page, select the Resolver tab.

  4. Select Use a custom resolver (registered at the Distributor), and then select the resolver in the list.

  5. If the resolver requires input (such as a column name), enter it in the Enter information needed by the resolver text box.

  6. Select OK.

  7. Repeat this process for each article that requires a resolver.

Use Transact-SQL

  1. If you plan to register your own custom conflict resolver, create one of the following types:

  2. To determine if the desired resolver is already registered, execute sp_enumcustomresolvers at the Publisher on any database. This procedure returns a description of the custom resolver, the class identifier (CLSID) for each COM-based resolver registered at the Distributor, and information on the managed assembly for each business logic handler registered at the Distributor.

  3. If the desired custom resolver isn't already registered, execute sp_registercustomresolver at the Distributor. Specify a name for the resolver for @article_resolver. For a business logic handler, this name is the friendly name of the assembly. For COM-based resolvers, specify the CLSID of the DLL for @resolver_clsid. 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 you don't deploy a business logic handler assembly 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.

  4. 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 are in the <drive>:\Program Files\Microsoft SQL Server\<nnn>\COM directory.

    • Use regsvr32.exe to register the custom resolver DLL with the operating system. For example, the following command registers the SQL Server Additive Conflict Resolver:

      regsvr32 ssradd.dll

  5. 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 <drive>:\Program Files\Microsoft SQL Server\<nnn>\COM.

Specify a custom resolver when defining a merge article

  1. If you plan to use a custom conflict resolver, create and register the resolver by following the preceding procedure.

  2. At the Publisher, execute sp_enumcustomresolvers and note the name of the desired custom resolver in the value field of the result set.

  3. At the Publisher on the publication database, execute sp_addmergearticle. 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 Advanced Merge Replication Conflict - COM-Based Resolvers.

Specify or change a custom resolver for an existing merge article

  1. To determine if a custom resolver is defined for an article or to get the name of the resolver, execute sp_helpmergearticle. If there's a custom resolver defined for the article, its name appears in the article_resolver field. The resolver_info field shows any input supplied to the resolver.

  2. At the Publisher, execute sp_enumcustomresolvers and note the name of the desired custom resolver in the value field of the result set.

  3. At the Publisher on the publication database, execute sp_changemergearticle. 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.

  4. To change any required input for the custom resolver, execute sp_changemergearticle 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 Advanced Merge Replication Conflict - COM-Based Resolvers.

Unregister a custom conflict resolver

  1. At the Publisher, execute sp_enumcustomresolvers and note the name of the custom resolver to remove in the value field of the result set.

  2. At the Distributor, execute sp_unregistercustomresolver. Specify the full name of the custom resolver from step 1 for @article_resolver.

Examples (Transact-SQL)

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

Related content

  • Last updated on