FIX: The Replication Queue Reader Agent Utility Shuts Down After Error 2601 Occurs (323550)

SYMPTOMS

When the Replication Queue Reader Agent utility applies a change to the publisher, it shuts down if a duplicate key violation error (Transact-SQL error 2601), such as the following, occurs:

Server: %s, Database %s: ODBC Error:Cannot insert duplicate key row in object '%.*ls' with unique index '%.*ls'.

CAUSE

This problem occurs because the INSERT, UPDATE, and DELETE stored procedures do not contain the necessary logic to handle duplicate key violation errors (Transact-SQL error 2601).

When you create a publication and you configure it for queued updating, by default, stored procedures that insert, update, and delete data in the published table are created automatically in the publication database.

These stored procedures are called by the Replication Queue Reader Agent utility to apply transactions at the publisher, to detect conflicts, and to generate compensating commands, which are posted to the distribution database, and then delivered to the subscriber. INSERT, UPDATE, and DELETE stored procedures are created for each article.

RESOLUTION

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How To Obtain the Latest SQL Server 2000 Service Pack

NOTE: The following hotfix was created before the release of Microsoft SQL Server 2000 Service Pack 3.

The English version of this fix has the file attributes (or later) that are listed in the following table. The dates and times for these files are listed in coordinated universal time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.

     Date         Time       Version        Size          File name
     ------------------------------------------------------------------

     24-MAY-2002  16:58:55 PM   8.00.632    96,473 bytes   Qfe357995.sql

NOTE: Because of file dependencies, the most recent hotfix or feature that contains these files may also contain additional files.

MORE INFORMATION

This problem is similar to the bug that is described in the following Microsoft Knowledge Base article:

322384 FIX: Replication Queued Updating Supports Unique Constraints

The distinction between this problem and the problem that is documented in Q322384 is the presence of a unique index and the absence of a unique constraint. You do not experience this problem if the duplicate key violation error occurs for a column (or columns) that is defined to a unique constraint.