Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2006

    Unanswered: SQL2000 Replication,modifying the width of a column

    Hi All,
    Is there a way by which we can modify the width of a column of a table which is being replicated without touching the ongoing transactional replication? This is for MSSQL2000 Transactional Replication.

    I know (and successfully tried) that we can add a column to a table and that gets propaged to the replicate database and indeed the added column gets reflected there. How to add a column? sp_repaddcolumn or Right Click on the Publication-Properties and it shows a button to Add a Column.

    This is what I have tried for modifying the width of a column of a table participating in Transactional Replication from varchar(10) to varchar(100)

    MH (source) -> MH1 (Replicate)

    The column “col1” had width of varchar(10) and this was altered to varchar(100).

    insert into MH..test_mh values(4,'abcdeabcdefff')

    select * from MH1..test_mh

    exec sp_dropsubscription @publication = N'MH', @article = N'test_mh', @subscriber = N'UKPBDRMTST2', @destination_db = N'MH1'

    exec sp_droparticle @publication = N'MH', @article = N'test_mh'

    alter table test_mh alter column col2 varchar(100) null OR

    MH1..sp_help test_mh

    exec sp_addarticle @publication = N'MH', @article = N'test_mh', @source_table = N'test_mh'

    exec sp_addsubscription @publication = N'MH', @article = N'test_mh', @subscriber = N'UKPBDRMTST2' , @destination_db = N'MH1'

    Needless to say, help would be apreciated -

    However, adding a column was possible.

  2. #2
    Join Date
    Nov 2004
    Without touching the replication, no it cannot be done. You are on the right track with dropping/ recreating the article.

Posting Permissions

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