SQL Server Performance

change primary key of a published table

Discussion in 'Performance Tuning for SQL Server Replication' started by vulcanoro, May 18, 2004.

  1. vulcanoro New Member



    I use a transactional replication and i want to change a primary key of a published table. If i use alter table drop constraint PK_Costumer i get an error: "cannot drop constraint PK_Costumer because table Costumer it is used for replication" . I get the same error if i use sp_droparticle.
    Can somebody tell me how can i change a primary key of a published table?
  2. Raulie New Member

    You must unpublish and then re-published the table to alter the PK constaint.
  3. vulcanoro New Member

    quote:Originally posted by Lazy_DBA

    You must unpublish and then re-published the table to alter the PK constaint.



    I have 200 distibuted agents. If i drop the publication i must recreate those agents.
    Do you another method?
  4. Raulie New Member

    Well you didn't mention that. What version are you running? Hopefully the other members can come up with a work around.
  5. satya Moderator

    Take help of scripting the replication in case of unpublishing and re-publishing after the constraint correction, BOL is the first hand to help in this matter.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  6. vulcanoro New Member

    quote:Originally posted by satya

    Take help of scripting the replication in case of unpublishing and re-publishing after the constraint correction, BOL is the first hand to help in this matter.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

    I script the publication. Now i must syncronize the subscription.

    Thank you for your help.

Share This Page