Post reply

Changing Column width in replicated table

  • May 12, 2006 at 9:17 am

    #91277

    I have a column in a replicated table (Snapshot) that needs its width increased from VARCHAR(255) to VARCHAR(1000).

    The Table Article Properties is configured to DROP the table at the subscriber, then recreate it.

    Can I simply <GRIN> increase the column width on the published table?  Will that propagate through the subscribers?  Or do I need to drop and recreate the column using the replication stored procedures?

    Thanks,

    - Greg

  • May 15, 2006 at 8:00 am

    This was removed by the editor as SPAM

  • May 15, 2006 at 12:51 pm

    #637969

    If it's only used in snapshot replication, I'm pretty sure you can just change the published table, and it will get pushed to the subscriber next time it runs.

  • May 15, 2006 at 4:00 pm

    #638029

    hi, this is the good question, actually im also want this solution.. according my understanding ... you means i can change the length of a column of that table whcih is included in articles and the changes will also effect automatically on subscriber, without taking the snapshot.???

    is it so???

    Noman

  • May 16, 2006 at 9:43 am

    #638190

    No.  What I mean is, if you are using snapshot replication, then you can change the column length at the publisher. Then, the next time snapshot replication runs, it will drop & recreate the table at the subscriber, thus pushing out the new schema.

    If you're using transactional or merge replication, then it's a bit more complicated.

  • May 16, 2006 at 2:26 pm

    #638273

    oh yes, unfortunatly im using merge replication... but is it complicate or immpossible??

    give me related articles if you have, and you can.

    Thanks

  • May 16, 2006 at 2:41 pm

    #638277

     I did this with good results when I needed to change the length of a field in a replicated table. -- Specifically: Drop a table from a publication so it can be modified, then add the table back in. I've done this with snapshot & trans replication, so I hope it works with merge too. Perhaps you can test it and see if it (or a variation) will work for you:

    1) I just scripted out the CREATE Publication and the DROP publication commands to text files. This creates the sp_droparticle & sp_addarticle commands for you (& other commands if applicable).

    2) Ran just the portion of the DROP publication that had to do with TableA (sp_droparticle)

    3) Modified FieldA from 6 char to 8 char on publication & subscription databases.

    4) Ran just the portion of the CREATE Publication dealing with TableA (sp_addarticle, sp_articlefilter etc..)

    5) Ran the snapshot job

    6) Ran the pull subscription job

    7) Checked the table in the subscription database & it has the same data as the source table.

    I usually set up a test replication and try out these things ahead of time.

  • May 16, 2006 at 3:22 pm

    #638289

    Thanks, I will try this. it really help me.

    one more thing.. can i add indexes on replicated tables.. without running snapshot agent. will indexes take effects on replication. ??

    Thanks again

  • May 17, 2006 at 8:02 am

    #638419

    I haven't tried that. If you add the index at the publisher, you'll probably have to add it manually at the subscriber if you don't want to re-snapshot.

  • May 17, 2006 at 8:53 am

    #638444

    Thanks, have another problem im facing now..

    i want add Primary Key on table which is the part of Replication. if i add, will there any effect on merge replication??? only to add a PK on a column.

    Thanks, waiting for ur prompt reply.

    Noman

  • May 17, 2006 at 8:56 am

    #638447

    Sorry, I haven't used merge replication.

Viewing 11 posts - 1 through 10 (of 10 total)

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