Microsoft KB Archive/314406

= FIX: Updates Do Not Replicate to Subscriber After Drop or Add of Columns =

Article ID: 314406

Article Last Modified on 9/26/2005

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q314406



BUG #: 356319 (SHILOH_BUGS)



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 &quot;command buffer&quot; 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 &quot;Using Custom Stored Procedures in Articles&quot; 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.