Cannot Alter Table being Published for Replication

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 ***

Re: Cannot Alter Table being Published for Replication

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 ***

Re: Cannot Alter Table being Published for Replication

"Brian Wotherspoon" wrote in message
news:zjThg.1$GO4.793@news.uswest.net...
>
> Hi all,
>


Try sp_repladdcolumn.

Generally in a case like this, I make it a multi-step process.

sp_repladdcolumn and add say "SerialNumberTemp"

Then update the published table so SerialNumberTemp= SerialNumber.

Then, sp_repldropcolumn on SerialNumber

then add it the way I want.

Then copy the data back

Then drop the temp column.



> 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 ***

Re: Cannot Alter Table being Published for Replication

"Eugene" wrote in message
news:1149782385.759937.140310@c74g2000cwc.googlegroups.com.. .
> 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.
>


Problem with this I believe is that if you go this route, if data in other
articles is modified, when you resubscribe, you'll lose the data or have
problems with it in the other articles.


>
>
>
> 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 ***
>