Post reply

In replication, how to delete Clustered and Primary key constraint in publisher database

  • March 22, 2012 at 6:45 am

    #258943

    Hi,

    In my transactional replication, I have a published database with many tables. Consider a single table which has the following structure.

    Table_ABC: it has only one varchar column Col_1 varchar (50)

    There is a clustered and primary key constraint on this field.

    I have to change the datatype of this column to nvarchar(100).

    To change the datatype of this column, I have to drop the constraint first. When I execute the script to drop the constraint, it gives me following error;

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

  • March 22, 2012 at 7:01 am

    #1462674

    You'll have to remove the table from the publication, make the changes and then add it back in.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 22, 2012 at 7:42 am

    #1462709

    @GilaMonster: Thanks for your suggestion and it worked

    I removed the table article from replication using sp_droparticle stored procedure, made schema changes and then re-added the table article in the replication by using sp_addarticle stored procedure

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

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