FIX: Execution of Sp_addsynctriggers Results in Syntax Error After ALTER TABLE (309251)

SYMPTOMS

The Replication Agent may fail during the execution of the sp_addsynctriggers stored procedure after the publication table is altered, which causes a syntax error message and prevents a complete immediate updating subscriber establishment.

The error message that occurs is similar to:

Repl Agent Status: 3
ErrorId = 10, SourceTypeId = 5
ErrorCode = '50007'
ErrorText = 'Incorrect syntax near the keyword 'and'.'

CAUSE

The improper maintenance of the column ordinal versus the column id for the publication table causes this problem. For more details, refer to the "More Information" section of this 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

Hotfix

NOTE: The following hotfix was created prior to Microsoft SQL Server 2000 Service Pack 2.

The English version of this fix should have the following file attributes or later:

   File name       Version
   -------------------------
   QFE355423.sql   8.00.0450

NOTE: Due to file dependencies, the most recent hotfix or feature that contains the preceding files may also contain additional files.

MORE INFORMATION

To encounter this rare condition, you must alter the table at the Publisher in a manner that causes the column ordinals to differ from the column ids.

For example, creating and altering the following table where colA and colC are the compound primary key.

Column Name	Column Ordinal	Column Id
colA	1	1
colB	2	2
colC	3	3

If you alter the table and remove colB, the following information applies.

Column Name	Column Ordinal	Column Id
colA	1	1
colC	2	3

When the sp_addsynctriggers stored procedure executes, the replication process incorrectly treats the column ordinal as the column id, which leads to incorrect commands being built and eventually the syntax error message occurs.