dropping table from publication

I have setup transactional replication and its going fine since some time in
testing. I was trying to remove one of the tables from publication, but it
always says that i have to drop subscription inorder to drop table from
publication.
If I drop subscription and recreate new subscription after the table is
dropped, I will be out of synch. with publisher database as publisher is
still on-line.
Is there any easy way to get arround this...
Regards,
Ravi
Hi Ravi,
Not sure if I fully understand your situation but you can definitely drop
the subscription for just the table that you want to drop using
sp_dropsubscription (See Books Online). The UI (SEM & SMSS) doesn't usually
allow you to perform such article-level operation however.
HTH
-Raymond
"SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
message news:EED2FB1D-7481-4027-B418-55BD7A97322E@.microsoft.com...
>I have setup transactional replication and its going fine since some time
>in
> testing. I was trying to remove one of the tables from publication, but it
> always says that i have to drop subscription inorder to drop table from
> publication.
> If I drop subscription and recreate new subscription after the table is
> dropped, I will be out of synch. with publisher database as publisher is
> still on-line.
> Is there any easy way to get arround this...
> Regards,
> Ravi
>
|||Raymond thanks for the reply, all i ma trying to do is drop one table from
existing publication and subscription.
and if possible add another table to same publication and subscription...
Regards,
Ravi
"Raymond Mak [MSFT]" wrote:

> Hi Ravi,
> Not sure if I fully understand your situation but you can definitely drop
> the subscription for just the table that you want to drop using
> sp_dropsubscription (See Books Online). The UI (SEM & SMSS) doesn't usually
> allow you to perform such article-level operation however.
> HTH
> -Raymond
> "SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
> message news:EED2FB1D-7481-4027-B418-55BD7A97322E@.microsoft.com...
>
>
|||Ravi,
I used this to change a column on a replicated table. It is almost exactly
the script you'll need:
exec sp_dropsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
exec sp_droparticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
alter table tEmployees alter column Forename varchar(100) null
exec sp_addarticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.source_table = 'tEmployees'
exec sp_addsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Ravi,
I keep forgetting that folks may be using Merge replication in which case it
is not possible (read: no supported way) to drop a single article without
reinitializing the entire subscription. But in case you are using
transactional\snapshot replication, I think here is what you need to do:
1) Call sp_dropsubscription to drop subscriptions on the article you want to
drop
2) Call sp_droparticle to drop the article you want to drop
3) Call sp_addarticle to add the new table to your publication
4) Call sp_addsubscrption to add subscription to your new table (this is not
necessary if syspublications.immediate_sync = 1)
5) Run snapshot agent to generate snapshot for the new article, run
distribution agent to apply it
You probably know about this already but dropping subscription on an article
doesn't remove data that has already been replicated to the subscriber. Of
course, you should be able to manually drop the table easily at the
subscriber if that is what you want.
-Raymond
"SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
message news:CBE581C0-3897-4A40-A740-CB38D33D61C5@.microsoft.com...[vbcol=seagreen]
> Raymond thanks for the reply, all i ma trying to do is drop one table from
> existing publication and subscription.
> and if possible add another table to same publication and subscription...
> Regards,
> Ravi
> "Raymond Mak [MSFT]" wrote: