FIX: Updates Do Not Replicate to Subscriber After Drop or Add of Columns (314406)

SYMPTOMS

Updates to columns may not replicate to subscribers, or may replicate incorrectly, under the following two conditions:

Columns are dropped and added from the article such that the column IDs in the syscolumns system table are not contiguous.

-and-
Transact-SQL commands, as opposed to stored procedures, are used to replicate changes to subscribers.

CAUSE

Internally, the log reader makes an incorrect assumption when it builds the Transact-SQL commands from the publisher transaction log to replicate changes to subscribers. This only occurs when you process the "command buffer" for the SQL command format, and not for the stored procedure command format.

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 should have the following file attributes or later:

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

   12/6/01     8:00 PM     8.00.550     7,269 KB     Sqlservr.exe

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

WORKAROUND

To work around this problem, use stored procedures instead of Transact-SQL commands to replicate updates to the subscriber. You can control this if you specify a stored procedure to execute at the subscriber in the @upd_cmd parameter of the sp_addarticle system stored procedure.

For more information about how to specify the stored procedure, see the "Using Custom Stored Procedures in Articles" topic in SQL Server 2000 Books Online.

Note that this change does not require your application to use particular stored procedures to make changes. The Distribution Agent uses these stored procedures when it applies changes at the subscriber, and they are not used on the publisher.

STATUS

Microsoft has confirmed that this is a problem in Microsoft SQL Server 2000.
This problem was first corrected in Microsoft SQL Server 2000 Service Pack 3.

REFERENCES

SQL Server 2000 Books Online; topics: "sp_addarticle"; "Using Custom Stored Procedures in Articles"