Post reply

Schema Change to a replicated table

  • December 30, 2008 at 11:33 am

    #197629

    We are using Transactional Replication. I need to alter the table schema for an article.

    Im am trying to update a composite key. I want to drop / alter the primary key constraint but I am getting the following error:

    - Unable to delete index 'indexName'.

    Cannot alter the table 'tableName' because it is being published for replication.

    Could not drop constraint. See previous errors.

    Can someone lend their expertise?

    Thanks 😀

  • December 30, 2008 at 12:37 pm

    #919209

    To do that you need to remove the article from the publication first. Then you do the schema change, after that, you add it back to the publication and finally you re-run the snapshot agent.


    * Noel

  • December 30, 2008 at 1:39 pm

    #919261

    noel,

    thanks for your quick response. I tried to remove the article from publication but I get an error saying: "Cannot drop ariticle. A subscription exists on it." Is it possible to disable a subscription with out dropping it?

    Peter

  • December 30, 2008 at 2:05 pm

    #919286

    Note: This is not really supported from the UI but as long as there is not activity on the table while running the scripts you should be ok.

    Here[/url] is a link to the "SCRIPTS" that you need.


    * Noel

  • December 30, 2008 at 5:12 pm

    #919375

    Thanks Noel,

    I will give this a shot.

    Peter

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply