Subscriber-Based Resolver Using C++ and a Stored Procedure

This sample application builds a custom stored procedure resolver that executes the stored procedure at the Subscriber. It contains a sample script to set up a publication on Northwind, and install the sample resolver that calls sp_authority_resolver. sp_authority_resolver retrieves the 'authority' value from the table at the Publisher and Subscriber, and the row with the highest authority will win and be returned to the merge process to be applied to both servers. If both rows have the same authority, the source table will win. The Microsoft® Visual C++ sample code is located in the \Microsoft SQL Server\80\Tools\Devtools\Samples\sqlrepl\resolver\subspres directory.

To run the sample program

1. On the computer that will be the Publisher, verify that the SQL Server Agent is running. If it is not running, start it.
   
   
2. Verify that the computer is configured for publishing and distribution  If it is not configured, configure it using the Configure Publishing and Distribution Wizard. The defaults given in the wizard are all acceptable for this sample.

Enterprise Manager

3. Compile and register the Visual C++ program by running the build.cmd file, located in the resolver\subspres directory.

   After successful build and registration, the message "DllRegisterServer in C:\Program Files\Microsoft SQL Server\80\COM\subspres.dll succeeded" is shown.

4. Run the subspres.bat file.

   This batch:

   • Creates a publication database named pubdb.
     
     
   • Creates a subscription database named subdb.
     
     
   • Creates tables in the publication and subscription databases that are required for replication.
     
     
   • Creates a merge publication that includes the testdata table as a table article.
     
     
   • Creates the resolver stored procedure, sp_authority_resolver, in the subscription database, subdb.
     
     
   • Sets the stored procedure, sp_authority_resolver, as the article resolver.

The command prompt window provides messages as the subspres.bat file executes. The messages show that a record is being updated at the Publisher and Subscriber with the same authority, so the Publisher record wins. The next update has an authority of 9 at the Publisher and an authority of 10 at the Subscriber, so the Subscriber update wins. The last update is with the Subscriber authority set to 9 and the Publisher authority set to 10, so the Publisher wins.

See Also

Custom Stored Procedure Conflict Resolver

Merge Replication Conflict Detection and Resolution