Post reply

how to alter the existing Primary key Non clustered index to Primary key Clustered Index

  • March 10, 2014 at 8:11 am

    #289134

    hi ALl,

    Can you please help me to do this change:

    how to alter the existing Primary key Non clustered index to Primary key Clustered Index ?

    Thanks

    Bhanu

  • March 10, 2014 at 8:13 am

    #1695537

    while i am trying i got the following error:

    Msg 4929, Level 16, State 1, Line 3

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

    Msg 3727, Level 16, State 0, Line 3

    Could not drop constraint. See previous errors.

  • March 10, 2014 at 8:37 am

    #1695559

    Remove the table from replication

    Drop all foreign keys referencing this table (may require removing other tables from replication)

    Drop the pk

    Recreate the PK clustered

    Recreate all foreign keys

    Add the tables back into the replication and wait for them to be snapshotted and sent out.

    Not a trivial exercise when there's replication involved.

    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 11, 2014 at 2:27 am

    #1695838

    Hi All,

    Thanks for your help.

    i have few more doubts please clarify it.

    1. How to locate that replication publisher ?

    2. How to Remove the table from replication ?

    Thanks

    Bhanu

  • March 11, 2014 at 2:40 am

    #1695842

    The publisher is the database you're working with. As for changing the replication, go speak with the DBA/developer who set the replication up.

    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 11, 2014 at 3:59 am

    #1695866

    is there a way to find the publisher?

  • March 11, 2014 at 4:01 am

    #1695868

    Um....

    GilaMonster (3/11/2014)

    The publisher is the database you're working with.

    i.e. the one you're trying to change the primary keys in. So you've already found it.

    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

Viewing 7 posts - 1 through 6 (of 6 total)

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