SQL Remote User's Guide
SQL Remote Design for Adaptive Server Enterprise
Managing conflicts
This section describes what you need to do to implement custom conflict resolution in SQL Remote.
For each table on which you wish to resolve conflicts, you must create three database objects to handle the resolution:
An old value table To hold the values that were stored in the table when the conflicting message arrived.
A remote value table To hold the values stored in the table at the remote database when the conflicting update was applied, as determined from the message.
A stored procedure To carry out actions to resolve the conflict.
These objects need to exist only in the consolidated database, as that is where conflict resolution occurs. They should not be included in any publications.
When a table is marked for replication, using the sp_add_remote_table or sp_modify_remote_table stored procedure, optional parameters specify the names of the conflict resolution objects.
The sp_add_remote_table and sp_modify_remote_table procedures take one compulsory argument, which is the name of the table being marked for replication. It takes three additional arguments, which are the names of the objects used to resolve conflicts. For example, the syntax for sp_add_remote_table is:
exec sp_add_remote_table table_name
[ , resolve_procedure ]
[ , old_row_table ]
[ , remote_row_table ]
You must create each of the three objects resolve_procedure, old_row_table, and remote_row_table. These three are discussed in turn.
old_row_table This table must have the same column names and data types as the table table_name, but should not have any foreign keys. When a conflict occurs, a row is inserted into old_row_table containing the values of the row in table_name being updated before the UPDATE was applied. Once resolve_procedure has been run, the row is deleted.
As the Message Agent applies updates as a set of single-row updates, the table only ever contains a single row.
remote_row_table This table must have the same column names and data types as the table table_name, but should not have any foreign keys. When a conflict occurs, a row is inserted into remote_row_table containing the values of the row in table_name from the remote database before the UPDATE was applied. Once resolve_procedure has been run, the row is deleted.
As the Message Agent applies updates as a set of single-row updates, the table only ever contains a single row.
resolve_procedure This procedure carries out whatever actions are required to resolve a conflict, which may include altering the value in the row or reporting values into a separate table.
Once these objects are created, you must run the sp_add_remote_table or sp_modify_remote_table procedure to flag them as conflict resolution objects for a table.
At an Adaptive Server Enterprise database, conflict resolution will not work on a table with more than 128 columns while the VERIFY_ALL_COLUMNS option is set to ON. Even if VERIFY_ALL_COLUMNS is set to OFF, if an UPDATE statement updates more than 128 columns, conflict resolution will not work.