Custom Stored Procedure Conflict Resolver
You 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™ 2000. You can override the default conflict resolver 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 - 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 |
| @conflict_message 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 can potentially use distributed queries or other mechanisms to query the value from the remote database.
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.
Note SQL Server stored procedure resolvers will be invoked only to handle update 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.
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 executing sp_addmergearticle to associate the stored procedure with the article. You must set the @article_resolver parameter to Microsoft SQLServer Stored Procedure and set the @resolver_info parameter to the name of stored procedure.
For more information, see Developing Replication Merge Conflict Resolvers Through a Custom Resolver.