How to: Implement a Stored Procedure-Based Custom Conflict Resolver for a Merge Article (Replication Transact-SQL Programming)

  • Article

You can write your own custom conflict resolver as a Transact-SQL stored procedure at each Publisher. During synchronization, this stored procedure is invoked when conflicts are encountered in an article to which the resolver was registered, and information on the conflict row is passed by the Merge Agent to the required parameters of the procedure. Stored procedure-based custom conflict resolvers are always created at the Publisher.

Note

Microsoft SQL Server stored procedure resolvers are only invoked to handle row change-based conflicts. They cannot be used to handle other types of conflicts such as insert failures due to PRIMARY KEY violations or unique index constraint violations.

To create a stored procedure-based custom conflict resolver

  1. At the Publisher in either the publication or msdb database, create a new system stored procedure that implements the following required parameters:

    Parameter Data type Description

    @tableowner

    sysname

    Name of the owner of the table for which a conflict is being resolved. This is the owner for the table in the publication database.

    @tablename

    sysname

    Name of the table for which a conflict is being resolved.

    @rowguid

    uniqueidentifier

    Unique identifier for the row having the conflict.

    @subscriber

    sysname

    Name of the server from where a conflicting change is being propagated.

    @subscriber_db

    sysname

    Name of the database from where conflicting change is being propagated.

    @log_conflict OUTPUT

    int

    Whether the merge process should log a conflict for later resolution:

    0 = Do not log the conflict.

    1 = Subscriber is the conflict loser.

    2 = Publisher is the conflict loser.

    @conflict_message OUTPUT

    nvarchar(512)

    Message to be given about the resolution if the conflict is logged.

    @destowner

    sysname

    The owner of the published table at the Subscriber.

    This stored procedure uses the values passed by the Merge Agent to these parameters to implement your custom conflict resolution logic; it must return a single row result set that is identical in structure to the base table and contains the data values for the winning version of the row.

  2. Grant EXECUTE permissions on the stored procedure to any logins used by Subscribers to connect to the Publisher.

To use a custom conflict resolver with a new table article

  1. Execute sp_addmergearticle to define an article, specifying a value of MicrosoftSQL Server Stored Procedure for the @article_resolver parameter and the name of the stored procedure that implements the conflict resolver logic for the @resolver_info parameter. For more information, see How to: Define an Article (Replication Transact-SQL Programming).

To use a custom conflict resolver with an existing table article

  1. Execute sp_changemergearticle, specifying @publication, @article, a value of article_resolver for @property, and a value of MicrosoftSQL Server Stored Procedure for @value.

  2. Execute sp_changemergearticle, specifying @publication, @article, a value of resolver_info for @property, and the name of the stored procedure that implements the conflict resolver logic for @value.

See Also

Other Resources

Advanced Merge Replication Conflict Detection and Resolution

Help and Information

Getting SQL Server 2005 Assistance