none
Changing a PK in Trans Replication RRS feed

  • Question

  •  

    I have a Clustered Primary Key on a table, which is used in transactional replication.

     

    I wish to change this to a Nonclustered Primary key, can I do this without dropping the article?

    Thursday, November 1, 2007 1:14 PM

Answers

  • Regreatably not. While SQL 2005 does support replication of schema changes, it does not support modifications to the pk. sp_repladdcolumn and sp_repldropcolumn do not support modifications to the pk either.
    Thursday, November 1, 2007 2:36 PM
    Moderator

All replies

  • Regreatably not. While SQL 2005 does support replication of schema changes, it does not support modifications to the pk. sp_repladdcolumn and sp_repldropcolumn do not support modifications to the pk either.
    Thursday, November 1, 2007 2:36 PM
    Moderator
  • Thanks Hilary.

     

    So I need to drop the artcile, do the PK mods and reacreate it?

     

    When I run:

     

    EXEC sp_droparticle @publication = 'Pubname' ,@article = 'Tablename'

     

    I get the following error?

     

    Msg 14046, Level 16, State 1, Procedure sp_MSrepl_droparticle, Line 284

    Could not drop article. A subscription exists on it.

     

    Can you help?

    Friday, November 2, 2007 9:28 AM
  • Got it now I run:

     

    EXEC sp_dropsubscription @publication = 'PubName', @article = 'TableName', @subscriber = 'SubName'

    EXEC sp_droparticle @publication = 'PubName' ,@article = 'TableName'

     

    I can then drop the replication procedures and table from the replicated server if I choose.

    Friday, November 2, 2007 3:39 PM