none
Transnational Replication (Sql Server 2005) -- Issue RRS feed

  • Question

  • Hi All,

    I have table that is getting replicated to three different databases and is part of a publication that has only this table as a article.

    Here is the schema


    CREATE TABLE [dbo].[tblSrctrades](
    [IdenID] [int] NOT NULL,
    [TradeType] [varchar](10) NULL,
    [PCode] [varchar](20) NULL,
    PRIMARY KEY CLUSTERED 
    (
    [IdenID] ASC
    )

    IdentID - is the primary key column

    Now there is a need to change the primary key constraint and make it as a composite primary key constraint to include above three columns (IdentID, TradeType & pCode). But when make the change on the table its throwing error as it a part of a replicated column.

    "Cannot alter/drop the table 'tablename' because it is being published for replication". 

    Please let me know how can i make the change without having to run the snapshot again.

    The table has about  6 million records.

    Wednesday, May 31, 2017 8:02 AM

All replies

  • Transactional Replication is using PK in order to replicate data. Any modification to PK are not possible once table is published.

    You need to drop table from publication, alter PK and publish again etc. Note that if u remove table from publication the data wont be replicated down and u miss data on subscriber. you have two options:

    1. Do the change (remove table from publication, alter PK add back table), during maintenance window when that table wont get ANY new data coming in

    2. Reinitialize and  generate new snapshot and send it to your subscribers.

    Wednesday, May 31, 2017 8:22 AM
  • Thanks for the detail. One quick query

    There are other publications as well in the main server so with Reintialize and generating new snapshot will that impact other publication as well.

    Is it possible to generate the snapshot for the affected publication only where this table is only one article present.

    Please suggest

    • Edited by MS_Novice Wednesday, May 31, 2017 9:47 AM More text
    Wednesday, May 31, 2017 9:45 AM
  • It will only affect the other publications if the table is part of the other publications as well.
    Wednesday, May 31, 2017 9:47 AM
    Moderator
  • For other publications, there are views where this table is referred  in that case will it affect in terms of snapshot generation?
    • Edited by MS_Novice Wednesday, May 31, 2017 10:14 AM more test
    Wednesday, May 31, 2017 10:12 AM
  • When you remove the article from the publication, it will simply stop updating the table in the subscribers.

    You then make the change, and reinitialize the subscribers. 

    For a short period, the view will fail when the table is dropped and recreated during the initialization of the subscriber.

    Then the views will start working again.

    Wednesday, May 31, 2017 11:51 AM