How to duplicate table structure in Transactional replication?

Dear all:
After I create a new publication and its subscription by Transactional
replication in SQL server 2000,I want to add a column or alter a column
length in publication table and hope that the column can be duplicated to
subscription table.However,I find that Transactional replication in SQL
server 2000 can only replicate data and can not replicate table structure.
The replication type I use is "Transactional replication".
My problem is:
How to duplicate table structure in Transactional replication of SQL server
2000?
On 8 Jun, 04:45, gzwangyang <gzwangy...@.discussions.microsoft.com>
wrote:
> Dear all:
> After I create a new publication and its subscription by Transactional
> replication in SQL server 2000,I want to add a column or alter a column
> length in publication table and hope that the column can be duplicated to
> subscription table.However,I find that Transactional replication in SQL
> server 2000 can only replicate data and can not replicate table structure.
> The replication type I use is "Transactional replication".
> My problem is:
> How to duplicate table structure in Transactional replication of SQL server
> 2000?
What you're trying to do isn't strictly possible. You can't alter a
table that is published for replication.
You would need to drop replication, change the table and then re-
enable the replication with snapshot enabled. Provided your articles
snapshot attributes are correct it will create the table at the
subscriber.
Thanks
James
|||This is not correct. You can use sp_repladdcolumn and sp_repldropcolumn.
Altering a column is not straightforward but is achievable
(http://www.replicationanswers.com/AddColumn.asp).
Cheers,
Paul Ibison

How to duplicate Identity Column in SQL Server 2000?

Dear All:
The table for subscription is the same as the table for publication.
create table zt_company(company_id int identity(1,1) not for replication
primary key,companyname varchar(200),create_date datetime,modify_date
datetime)
If I use "not for replication" option when creating table,publication and
subscription is successful.However,company_id column of subscription table
loses identity attribute and primary key.
If I do not use "not for replication" option when creating table,publication
and subscription fails.
I want to keep "identity attribute and primary key" of identity column in
subscription table ,what should I do?
I use Enterprise manager for publication and subscription.
thanks.
You didn't mention what type of replication you're using, but presumably
you're talking about transactional? If so, you can set up queued updating
subscribers to maintain the identity attribute and have it set up ready for
failover - is that what you want?
Cheers,
Paul Ibison
|||Dear Paul Ibison:
Thanks for your help."queued updating subscribers" sucessfully solved my
problem and is what I want.
wangyang.
"Paul Ibison" wrote:

> You didn't mention what type of replication you're using, but presumably
> you're talking about transactional? If so, you can set up queued updating
> subscribers to maintain the identity attribute and have it set up ready for
> failover - is that what you want?
> Cheers,
> Paul Ibison
>