Results 1 to 11 of 11

Thread: replication

  1. #1
    Join Date
    Apr 2003
    Posts
    176

    Unanswered: replication

    I have a replication b/w two servers and the table is having replication. I want to increase the length of the field(varchar) for the table on the publication database. Can i directly change the width in the design table window or is there any other way to do it?
    Please help me in this issue.
    Thanks in advance.

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: replication

    No, you can't make a DDL change like that to a replicated environment unless you uninstall the replication setup or drop the article(table).

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: replication

    Originally posted by bruce_Reid
    I have a replication b/w two servers and the table is having replication. I want to increase the length of the field(varchar) for the table on the publication database. Can i directly change the width in the design table window or is there any other way to do it?
    Please help me in this issue.
    Thanks in advance.
    You can add, remove column but not change properties for existing column.

  4. #4
    Join Date
    Apr 2003
    Posts
    176
    so what should i do now.Please help me in this issue

  5. #5
    Join Date
    Feb 2004
    Location
    Rhode Island
    Posts
    69
    Originally posted by bruce_Reid
    so what should i do now.Please help me in this issue
    exec componentsdb..sp_repladdcolumn 'myTable', 'my_field_temp', 'datetime', 'all', null, 1,1

    UPDATE componentsdb..myTable SET my_field_temp = my_field

    exec componentsdb..sp_repldropcolumn 'myTable', 'my_field', null, 1, 1

    exec componentsdb..sp_repladdcolumn 'myTable', 'my_field', 'datetime', 'all', null, 1,1

    UPDATE componentsdb..myTable SET my_field = my_field_temp

    exec componentsdb..sp_repldropcolumn 'myTable', 'my_field_temp', null, 1, 1

  6. #6
    Join Date
    Feb 2004
    Location
    Rhode Island
    Posts
    69
    Originally posted by bruce_Reid
    so what should i do now.Please help me in this issue
    Use the stored procedures

    sp_repladdcolumn
    repldropcolumn

    Before you doing, make sure you have taken a backup of the database and all your subscribers are active. Sometimes it will go crazy and you have to re-setup from the scratch.

    exec my_db..sp_repladdcolumn 'myTable', 'my_field_temp', 'datetime', 'all', null, 1,1

    UPDATE my_db..myTable SET my_field_temp = my_field

    exec my_db..sp_repldropcolumn 'myTable', 'my_field', null, 1, 1

    exec my_db..sp_repladdcolumn 'myTable', 'my_field', 'datetime', 'all', null, 1,1

    UPDATE my_db..myTable SET my_field = my_field_temp

    exec my_db..sp_repldropcolumn 'myTable', 'my_field_temp', null, 1, 1

  7. #7
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by smasanam
    Use the stored procedures

    sp_repladdcolumn
    repldropcolumn

    Before you doing, make sure you have taken a backup of the database and all your subscribers are active. Sometimes it will go crazy and you have to re-setup from the scratch.

    exec my_db..sp_repladdcolumn 'myTable', 'my_field_temp', 'datetime', 'all', null, 1,1

    UPDATE my_db..myTable SET my_field_temp = my_field

    exec my_db..sp_repldropcolumn 'myTable', 'my_field', null, 1, 1

    exec my_db..sp_repladdcolumn 'myTable', 'my_field', 'datetime', 'all', null, 1,1

    UPDATE my_db..myTable SET my_field = my_field_temp

    exec my_db..sp_repldropcolumn 'myTable', 'my_field_temp', null, 1, 1
    It needs to remember about default or constraints on column if any : remove them on publisher and subscriber - add after....

  8. #8
    Join Date
    Feb 2004
    Location
    Rhode Island
    Posts
    69
    Originally posted by snail
    It needs to remember about default or constraints on column if any : remove them on publisher and subscriber - add after....
    Do you have any constraints like DEFAULT value or something?

  9. #9
    Join Date
    Feb 2004
    Location
    PAKISTAN
    Posts
    106
    What if the column that needs to be altered has an ID-Seed implementation. Full SNAPSHOT???









    Originally posted by smasanam
    Use the stored procedures

    sp_repladdcolumn
    repldropcolumn

    Before you doing, make sure you have taken a backup of the database and all your subscribers are active. Sometimes it will go crazy and you have to re-setup from the scratch.

    exec my_db..sp_repladdcolumn 'myTable', 'my_field_temp', 'datetime', 'all', null, 1,1

    UPDATE my_db..myTable SET my_field_temp = my_field

    exec my_db..sp_repldropcolumn 'myTable', 'my_field', null, 1, 1

    exec my_db..sp_repladdcolumn 'myTable', 'my_field', 'datetime', 'all', null, 1,1

    UPDATE my_db..myTable SET my_field = my_field_temp

    exec my_db..sp_repldropcolumn 'myTable', 'my_field_temp', null, 1, 1

  10. #10
    Join Date
    Feb 2004
    Location
    Rhode Island
    Posts
    69
    Originally posted by TALAT
    What if the column that needs to be altered has an ID-Seed implementation. Full SNAPSHOT???
    First execute sp_repldropcolumn
    It will show you what constraint is attached to that table

    then use that name in the

    alter table [table name] drop constraint

    to drop that constraint

    then again execute repldropcolumn to drop that column

  11. #11
    Join Date
    Feb 2004
    Location
    PAKISTAN
    Posts
    106
    I have a following column that i need to alter.

    COLUMN_A INT (IDENTITY SEED=1) AUTO INCREMENT.

    The column is in a published table. I want to alter it to BIGINT. i.e.,

    COLUMN_A BIGINT(IDENTITY SEED=1) AUTO INCREMENT.

    The column has more that 1000 rows. Since we can't update an ID-Seed column explicitly, and the old values in column are being referenced by other tables. How come it's possible to get the old values of the column into the new column without dropping the publication. Also please guide if it's possible to alter a Primary-Key in a published table.


    Regards!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •