Posted by Eugene on 06/08/06 15:59

1) Drop subscription to a specific article that you want to alter,
using sp_dropsubscription.
2) Drop this article using sp_droparticle.
3) Alter your table.
4) Add the article back to publication, using sp_addarticle.
5) Subscribe to this article using sp_addsubscription.
6) Run the snapshot agent to deliver the modified table to the
subscriber.




Brian Wotherspoon wrote:
> Hi all,
>
> I'm using SQL Server 2000 SP3 to store data for real time transaction
> processing.
>
> I have set up replication to another server using a push subscription to
> give me immediate backup.
>
> I need to alter the data type of one of the columns and am using the
> following basic sql:
>
>
> alter table Voucher
> alter column SerialNumber varchar(20) NOT NULL
>
> However I keep getting this error message:
>
> Server: Msg 4929, Level 16, State 1, Line 1
> Cannot alter the table 'Terminals' because it is being published for
> replication.
>
> Is there anything I can do to allow this update taking place, short of
> deleting the subscription and recreating it. (I want to try and avoid
> this as the same update needs to be applied to about 10 databases that
> are also replicated in the same way).
>
> All help is appreciated.
>
> Brian.
>
> *** Sent via Developersdex http://www.developersdex.com ***

[Back to original message]