Contents Index A first conflict resolution example Designing to avoid referential integrity errors

SQL Remote User's Guide
  SQL Remote Design for Adaptive Server Enterprise
    Managing conflicts

A second conflict resolution example


This example shows a slightly more elaborate example of resolving a conflict, based on the same situation as the previous example, discussed in A first conflict resolution example.

Goals of the conflict resolution 

In this case, the conflict resolution has the following goals:

The conflict resolution objects 

In this case, the ConflictLog table has an additional column to record the user ID of the remote user. The table is as follows:

CREATE TABLE ConflictLog (
   conflict_key numeric(5, 0) identity not null,
   lost_name char(40) not null ,
   won_name char(40) not null ,
   remote_user char(40) not null ,
   primary key ( conflict_key )
)

The stored procedure is more elaborate. As the update will be disallowed, rather than allowed, the lost_name value now refers to the value arriving in the message. It is first applied, but then the conflict resolution procedure replaces it with the value that was previously present.

The stored procedure uses data from the temporary table #remote. In order to create a procedure that references a temporary table you first need to create that temporary table. The statement is as follows:

CREATE TABLE #remote (
   current_remote_user varchar(128),
   current_publisher varchar(128)
)

This table is created in TEMPDB, and exists only for the current session. The Message Agent creates its own #remote table when it connects, and uses it when the procedure is executed.

CREATE PROCEDURE ResolveCustomer
AS
BEGIN
   DECLARE @cust_key CHAR(12)
   DECLARE @lost_name CHAR(40)
   DECLARE @won_name CHAR(40)
   DECLARE @remote_user varchar(128)
 -- Get the name that was present before
   -- the message was applied, from OldCustomer
   -- This will "win" in the enx
   SELECT @won_name=name,
      @cust_key=cust_key
   FROM OldCustomer
 -- Get the name that was applied by the
   -- Message Agent from Customer. This will
   -- "lose" in the end
   SELECT @lost_name=name
   FROM Customer
   WHERE cust_key = @cust_key
 -- Get the remote user value from #remote
   SELECT @remote_user = current_remote_user
   FROM #remote
 -- Report the problem
   INSERT INTO ConflictLog ( lost_name,
      won_name, remote_user )
   VALUES ( @lost_name, @won_name, @remote_user )
 -- Disallow the update from the Message Agent
   -- by resetting the row in the Customer table
   UPDATE Customer
   SET name = @won_name
   WHERE cust_key = @cust_key
END
Notes 

There are several points of note here:

Testing the example 

To test the example

  1. Create the tables and the procedure in the consolidated database, and add them as conflict resolution objects to the Customer table.

  2. Insert and commit a change at the consolidated database. For example:

    UPDATE Customer
    SET name = 'Consolidated Sports'
    WHERE cust_key='cust1'
    go
    COMMIT
    go
  3. Insert and commit a different change to the same line at the remote database. For example:

    UPDATE Customer
    SET name = 'Field Sports'
    WHERE cust_key='cust1'
    go
    COMMIT
    go
  4. Replicate the change from the remote to the consolidated database, by running the Message Agent at the remote database to send the message, and then at the consolidated database to receive and apply the message.

  5. At the consolidated database, view the Customer table and the ConflictLog table. The Customer table contains the value from the consolidated database:

    cust_key name rep_key
    cust1 Consolidated Sports rep1

    The ConflictLog table has a single row, showing the conflict and recording the value entered at the remote database:

    conflict_key lost_name won_name remote_user
    1 Field Sports Consolidated Sports field_user
  6. Run the Message Agent again at the remote database. This receives the corrected update from the consolidated database, so that the name of the customer is set to Consolidated Sports here as well.


Contents Index A first conflict resolution example Designing to avoid referential integrity errors