Does adding a new column cause entire subscription to reinitialize?

Hello,
Some hopefully simple questions for the replication experts!
SQL Server 2000, transactional replication, one publisher, one
subscriber.
I have added a column to an article on the publisher and the publisher
is already published. Initially I tried to do this as follows:
sp_repladdcolumn@.source_object = 'tblA',
@.column = 'NewField',
@.typetext = 'int NULL',
@.publication_to_add = 'MyPublication',
@.force_reinit_subscription = 0
but this gave me an error message:
"Error 21381. Cannot add (drop) column to table 'tblA' because the
table belongs to publication(s) with an active updatable subscription.
Set @.force_reinit_subscription to 1 to force reinitialization"
I then re-ran the above with @.force_reinit_subscription=1. The command
worked and returned the message "subscription(s) reinitliazed" but the
new column hasn't appeared on the subscriber.
My questions are as follows:
1 - Do I have to re-run the Snapshot Agent?
2 - If I do re-run the Snapshot Agent, will it reinitialize all other
tables (and their data) or will it just add the new column?
3 - Is there a way I can view the reinitialization commands that are
queued at the publisher to see whether the queued command is to add the
new column or whether it is to reinitialize everything?
Thanks very much!
Pawel
For information: the snapshot agent ran in the evening. The snapshot
agent copied across the new field ALONG WITH all tables related to
'tblA', so it's just as well that I didn't kick it off manually during
the day.
I would still like to know if there is a way to view the commands which
the snapshot agent is due to execute. Does anyone know?
Thanks
Pawel
Pawel wrote:
> Hello,
> Some hopefully simple questions for the replication experts!
> SQL Server 2000, transactional replication, one publisher, one
> subscriber.
> I have added a column to an article on the publisher and the publisher
> is already published. Initially I tried to do this as follows:
> sp_repladdcolumn@.source_object = 'tblA',
> @.column = 'NewField',
> @.typetext = 'int NULL',
> @.publication_to_add = 'MyPublication',
> @.force_reinit_subscription = 0
> but this gave me an error message:
> "Error 21381. Cannot add (drop) column to table 'tblA' because the
> table belongs to publication(s) with an active updatable subscription.
> Set @.force_reinit_subscription to 1 to force reinitialization"
> I then re-ran the above with @.force_reinit_subscription=1. The command
> worked and returned the message "subscription(s) reinitliazed" but the
> new column hasn't appeared on the subscriber.
> My questions are as follows:
> 1 - Do I have to re-run the Snapshot Agent?
> 2 - If I do re-run the Snapshot Agent, will it reinitialize all other
> tables (and their data) or will it just add the new column?
> 3 - Is there a way I can view the reinitialization commands that are
> queued at the publisher to see whether the queued command is to add the
> new column or whether it is to reinitialize everything?
> Thanks very much!
> Pawel
|||Pawel,
as your test shows, if you are specifying @.force_reinit_subscription=1, the
snapshot will contain everything and if not, it won't. To see the commands
that the snapshot agent runs you can use sp_browsereplcmds. Also you can
look in the distribution working folder to see the actual files.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Just to clarify, I don't think the snapshot contained everything. It
looks like it only contained the tables which have a relationship with
'tblA', not all the tables in the published database.
Thanks for the note about sp_browsereplcmds.
Pawel
Paul Ibison wrote:
> Pawel,
> as your test shows, if you are specifying @.force_reinit_subscription=1, the
> snapshot will contain everything and if not, it won't. To see the commands
> that the snapshot agent runs you can use sp_browsereplcmds. Also you can
> look in the distribution working folder to see the actual files.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)