Attached is a sample Merge Conflict Handler sample I created.  I am including a walkthrough of the sample below.

 

1.  Create the sample merge publication/subscription databases

Code Snippet:  From file  <1-CreatePub_Sub_DBs.sql>

 

clip_image001

2. Via management studio or TSQL scripts ( file 2-CreatePublication.sql) , create the merge publication

·         For Publication name specify: SamplePub1

·         Publish both tables: t1, t2

·         For each article's Tracking Level specify to  use "Column-Level tracking" (@column_tracking=true)

 

clip_image002

 

3. Via management studio or TSQL scripts ( file 3-CreateSubscription.sql), create a merge push subscription

 

4. Run the Snapshot Agent

    Then run the Merge Agent to apply the initial snapshot

 

5.  Create the Merge Conflict Handler on a machine with Visual Studio. To do this we will create the   C_t1ConflictHandler class. Create a new Visual Studio C# Windows Class Library project, named  Sample_t1MergeConflictHandler_Lib

clip_image003

a.    Add the following references to the project:

 

C:\Program Files\Microsoft SQL Server\100\COM\Microsoft.SqlServer.Replication.BusinessLogicSupport.dll

 

Note: RMO depends on the replication components and client connectivity components that are included with all versions of SQL Server except SQL Server Compact. To deploy an application based on RMO, you must install a version of SQL Server that includes replication components and client connectivity components on the computer on which the application will run. http://msdn.microsoft.com/en-us/library/ms146869.aspx

b.    (optional) Rename Class1.cs to Sample_t1MergeConflictHandler_Lib.cs

c.    In the class source code file (Sample_t1MergeConflictHandler_Lib.cs), add the necessary  using statements to include the proper libraries.

 

Code Snippet: from file  <5-Sample_t1MergeConflictHandler_Lib.cs>

clip_image004

 

d.    This class is defined as follows (from Sample_t1MergeConflictHandler_Lib.cs), I will describe in sections

 

Code Section 1

·         Note the class inherits from the BusinessLogicModule class. This is necessary in order to implement the custom business logic that is invoked during the merge replication synchronization process.

·         We override the HandledChangedStates method to specify what type of activity our Business Logic Handler will address. In this case we are specifying we will handle update conflicts only.

 

Reference:  <http://technet.microsoft.com/en-us/library/microsoft.sqlserver.replication.businesslogicsupport.businesslogicmodule(SQL.100).aspx>

 

 

 

clip_image005

 

 

 

Code Section 2

·         The Populate_ConflictRow_VersionInfo function, makes a direct connection to the publisher and subscriber (lines 41-48)

·         Calls sp_showrowreplicaninfo for the rowguid provided (lines  52-54,lines 66-68)

·         The stored procedure sp_showrowreplicaninfo returns TWO resultsets, the first contains row version information

·         The second resultset contains column version information for each column in the row

·         The two publisher resultsets are place in   PubReplicaDataSet (line 55)

·         The two subscriber resultsets are place in   SubReplicaDataSet (line 69)

·         The publisher  row version is  extracted from the first resultset and written to a variable pVer (line 62)

·         The subscriber row version is  extracted from the first resultset and written to a variable sVer (line 77)

·         The resultsets are returned by reference

 

 

 

clip_image006

 

Code Section 3

·         We are also overriding the method  UpdateConflictsHandler to provide our own logic to handle an update conflict

·         When the conflict is raised, we will  have access to the publisher and subscriber row via the publisherDataSet and subscriberDataSet parameters

·         We will build the final row to send to the publisher + subscriber by setting the column values we want in the customDataSet

·         We have the ability to set a custom conflict message and history message

·         By setting the conflictLogType, we can specify if a conflict error is raised during sync. Note what you set this value to also affects the output seen in the conflict tables.

·         In line 105 we prepare the customDataSet object to initially hold the column values in the publisher  row

·         We then call Populate_ConflictRow_VersionInfo to populate the replica datasets with the output of sp_showrowreplicainfo

 

 

clip_image007

 

 

Code Section 4

·         We then loop through all the columns in the conflict row to see which columns were modified at the publisher or subscriber - or both. If a column was changed it will contain replica output in sp_showrowreplicainfo (lines 137-139, 147-149)

·         If a column was changed at both the publisher and subscriber, the logic below will choose the publisher row (lines 167-168)

clip_image008

 

Code Section 5

·         Set  logging options

·         If we set "conflictLogType = ConflictLogType.ConflictLogNone" thenmerge agent  sync status will not indicate a conflict has occurred

·         If we set " conflictLogType = ConflictLogType.ConflictLogSubscriber" then the subriber row is marked as the losing row - which means  in the conflict tables it will indicate the publisher row won - when in reality the handler build a custom row

·         I have set it to ConflictLogSubscriber to receive an indication a conflict was raised in the sync status

clip_image002[4]

6.    Build the project above and copy the generated DLL to your sql server machine to install the Business Logic Handler (6-InstallBusinessLogicHandler.sql)

·   Note the script below registers the resolver at the distributor and then sets the article property  to use the resolver

·   Note, I am also setting verify_resolver_signature=0  because for this specific sample I am not verifying the dll signature which is a potential security violation

 

clip_image009

 

7.    Step 7: Now reproduce a conflict (file 7-ReproduceConflict.sql) for the table t1 which has a custom business logic handler ,and table t2 which does not. Observe the difference in the final results:

·         Initial row values:

 

clip_image010

 

·         Now update columns  c2,c3 on the publisher.

·         For the same row, update columns c3,c4 on the subscriber.

·         Even with column-level tracking this is a conflict because c3 is changed at both the publisher and subscriber

 

 

clip_image011

 

 

·         Now run the merge agent

·         Note, the final row  on the t1 table (which has a custom handler) includes values from the publisher and subscriber changes, note c2,c3 contains the publisher changed value, c4 contained the subscriber provided value

 

 

clip_image012

 

·         Note, the final row  on the t2 table includes values from the publisher only, note c2,c3 contains the publisher changed value, c4 contains the previous  publisher value as well, all subscriber changes are lost for this row (default handler behavior)

 

 

clip_image013

 

 

clip_image014

 

Sample Download Files: 

- Fany Carolina Vargas | SQL Dedicated Premier Field Engineer | Microsoft Services