Chapter 8. SQL Remote Design for Adaptive Server Enterprise

 

 

Naming the objects

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.

 

Limitations

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

167

Page 185
Image 185
Sybase DC38133-01-0902-01 Names of the conflict resolution objects, Remoterowtable. These three are discussed in turn, 167