Microsoft KB Archive/309251

= FIX: Execution of Sp_addsynctriggers Results in Syntax Error After ALTER TABLE =

Article ID: 309251

Article Last Modified on 9/26/2005

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q309251



BUG #: 355423 (SHILOH_BUGS)



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 &quot;More Information&quot; 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.



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 2.



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.

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

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.

Keywords: kbbug kbfix kbqfe kbsqlserv2000presp2fix kbhotfixserver KB309251

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.