To configure an article to use a stored procedure custom resolverYou can create custom resolvers consisting of special queries and code to examine conflicts and override the default way in which conflicts are resolved by Microsoft® SQL Server™. You can override the default conflict resolver simply by substituting your own program with the same name. For example, suppose multiple sites participate in monitoring a chemical process and each records the low and high temperatures achieved in a test. Rather than use a priority or first wins solution, such an application might want to accept the “lowest low” and the “highest high” value.
You can use Transact-SQL to build your custom conflict resolver as a stored procedure at each Publisher. Custom conflict resolvers are always executed at the Publisher. The stored procedure should accept the following required parameters.
| Parameter | Data type | Description |
|---|---|---|
| @tableowner | sysname | Name of the owner of the table for which a conflict is being resolved |
| @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 |
| @conflict_merge OUTPUT | nvarchar(512) | Message to be given about the resolution if the conflict is logged |
The stored procedure uses these parameters to examine the values contained in the row at both the Publisher and Subscriber. The stored procedure can also examine any additional information you specify and manipulate the values to determine what column values the resolved row should have. The stored procedure then returns 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. The stored procedure could potentially use distributed queries or other mechanisms to query the value from the remote database.
Note The stored procedure must be located either in the published database at the Publisher or in the master database and marked as a system object. Execute permission should be granted to public or to a list of all Subscribers.
After the stored procedure is created, you must configure an article to use that stored procedure as its custom resolver. You can specify a custom resolver for an article by:
Specifying a custom resolver for an article registers that resolver at the Distributor.
To configure an article to use a stored procedure custom resolver
| sysmergearticles | MSmerge_delete_conflicts |
| Using a Custom Resolver | sp_addmergearticle |
| Merge Replication |