Data Replication with SQL Remote
   PART 2. Replication Design for SQL Remote
     CHAPTER 6. Principles of SQL Remote Design       

Replication errors and conflicts

SQL Remote is designed to allow databases to be updated at many different sites. Careful design is required to avoid replication errors, especially if the database has a complicated structure. This section describes the kinds of errors and conflict that can occur in a replication setup; subsequent sections describe how you can design your publications to avoid errors and manage conflicts.

  Replication errors

Replication errors fall into the following categories:

• Duplicate primary key errors     Two users INSERT a row using the same primary key values, or one user updates a primary key and a second user inserts a primary key of the new value. The second operation to reach a given database in the replication system fails because it would produce a duplicate primary key.

• Row not found errors     A user DELETES a row (that is, the row with a given primary key value). A second user UPDATES or DELETES the same row at another site.

  In this case, the second statement fails, as the row is not found.

• Referential integrity errors     If a column containing a foreign key is included in a publication, but the associated primary key is not included, the extraction utility leaves the foreign key definition out of the remote database so that INSERTS at the remote database will not fail.

  This can be solved by including proper defaults into the table definitions.

  Also, referential integrity errors can occur when a primary table has a SUBSCRIBE BY expression and the associated foreign table does not: rows from the foreign table may be replicated, but the rows from the primary table may be excluded from the publication.

  Replication conflicts

Replication conflicts are different from errors. Properly handled, conflicts are not a problem in SQL Remote.

• Conflicts     A user updates a row. A second user updates the same row at another site. The second user's operation succeeds, and SQL Remote allows a trigger to be fired (Adaptive Server Anywhere) or a procedure to be called (Adaptive Server Enterprise) to resolve these conflicts in a way that makes sense for the data being changed.

  Conflicts will occur in many installations. SQL Remote allows appropriate resolution of conflicts as part of the regular operation of a SQL Remote setup, using triggers and procedures.

       For information about how SQL Remote handles conflicts as they occur, see the following chapters.

  Tracking SQL errors

SQL errors in replication must be designed out of your setup. SQL Remote includes an option to help you track errors in SQL statements, but this option is not intended to resolve such errors.

By setting the Replication_error option, you can specify a stored procedure to be called by the Message Agent when a SQL error occurs. By default no procedure is called.

  To set the Replication_error option in Adaptive Server Anywhere:

1. Issue the following statement:

   SET OPTION 
   remote-user.Replication_error 
   = 'procedure-name'

   where remote-user is the user ID on the Message Agent command line, and procedure-name is the procedure called when a SQL error is detected.

  To set the Replication_error option in Adaptive Server Enterprise:

1. Issue the following statement:

   exec sp_remote_option Replication_error, procedure-name
   go

   where procedure-name is the procedure called when a SQL error is detected.

Replication error procedure requirements 

The replication error procedure must have a single argument of type CHAR, VARCHAR, or LONG VARCHAR. The procedure is called once with the SQL error message and once with the SQL statement that causes the error.