FIX: sp_addmergearticle May Fail with Error 20009 if Article Added Is Vertically Filtered (300172)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q300172
BUG #: 236010 (SHILOH_BUGS)

SYMPTOMS

Adding an article to a publication using sp_addmergearticle may result in the following error:
Msg 20009, Level 16, State 1, Server 'servername', Procedure sp_addmergearticle, Line 1202
The article 'articlename' could not be added to the publication 'publicationname'.
This error can occur if all the following conditions are true:
  • The article has a vertical filter.
  • The article does not already have a uniqueidentifier column with the ROWGUIDCOL property enabled.
  • The article has a identity column where:
    1. The identity column is included in the vertical partition. (For example, if the identity column is a primary key column, it will automatically be included in the filter.)
    2. The identity column is set up to handle identity range across all replicas (@auto_identity_range='true').
    3. The Not for Replication property is not set for the identity column.

RESOLUTION

To resolve this problem, obtain the latest service pack for 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

WORKAROUND

To work around this problem, you can do either of the following things before sp_addmergearticle is run:
  • Manually set the Not For Replication property on the identity column. -or-

  • If there is a uniqueidentifier column already in the table, enable the ROWGUIDCOL property for that column. If there is not a uniqueidentifier column, add a new column called "rowguid" with datatype uniqueidentifier and the ROWGUIDCOL property set.

STATUS

Microsoft has confirmed that this is a problem in SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.
Modification Type:MinorLast Reviewed:1/28/2004
Keywords:kbBug kbfix kbSQLServ2000sp1fix KB300172
©2004 Microsoft Corporation. All rights reserved.