replication

Hello,
I'm a developer and I've inherited a sql environment that has 1 publisher,
a distributor, and 12 subscribers. Several articles are published using
transactional replication. I need to make a constraint change on a table
that is published. Do I need to drop the publication and recreate it? What
scenario requires dropping of publication?
thanks
Unfortunately in SQL 2000 you need to. In sql 2005 you don't. Make sure you
script out the publications and subscriptions before making your change.
With luck you can do a no-sync subscription.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Troy" <troy@.pushit.com> wrote in message
news:uarP51FHGHA.1088@.tk2msftngp13.phx.gbl...
> Hello,
> I'm a developer and I've inherited a sql environment that has 1
> publisher, a distributor, and 12 subscribers. Several articles are
> published using transactional replication. I need to make a constraint
> change on a table that is published. Do I need to drop the publication and
> recreate it? What scenario requires dropping of publication?
> thanks
>
>
|||Troy,
you don't necessarily need to drop the publication - it is possible to drop
the articles and keep the rest of the publication going:
exec sp_dropsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
exec sp_droparticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
constraint change
exec sp_addarticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.source_table = 'tEmployees'
exec sp_addsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
Start the snapshot then distribution agents.
Note that the validity of this method depends on the relationships between
these tables and the other articles, and if you can prevent changes to the
system while these changes are made.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)