Droping a subscription

I have two orphan subscriptions on a SQL 2000 server. I need to drop both
of them. Can anyone help me out with the syntax to do that? I have been
trying to use sp_dropsubcriber and sp_dropsubscription, but I can't seem to
figure them out (of course I am assuming that one of these is the one I am
supposed to be using).
The two subscriptions are listed as
'GP8:Bg_Hauler_Tickets:Bg_Hauler_Tickets' and
'LANGES3:Bg_Hauler_Tickets:Bg_Hauler_Tickets'.
Thanks for any help you can give.
Jeff
Jeff,
you probably need to run sp_droppullsubscription on the subscriber. If the
database is no longer involved in replication, I'd run sp_removedbreplication
instead.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||That did the trick.
Thanks
Jeff
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:4D801C50-80FF-4F84-AF2A-22421A8937A5@.microsoft.com...
> Jeff,
> you probably need to run sp_droppullsubscription on the subscriber. If the
> database is no longer involved in replication, I'd run
> sp_removedbreplication
> instead.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Where are these subscriptions showing up? Are they showing up in EM, and
then when you drill down on them you get an error message? or are they in
the subscriber databases and you can't pull new subscriptions there.
If they are showing up in your subscription database you will probably need
to manually delete the contents of following rows
MSreplication_subscriptions
MSsubscription_agents
MSsubscription_properties
-
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
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
news:eGTI4KYAGHA.272@.TK2MSFTNGP09.phx.gbl...
>I have two orphan subscriptions on a SQL 2000 server. I need to drop both
>of them. Can anyone help me out with the syntax to do that? I have been
>trying to use sp_dropsubcriber and sp_dropsubscription, but I can't seem to
>figure them out (of course I am assuming that one of these is the one I am
>supposed to be using).
> The two subscriptions are listed as
> 'GP8:Bg_Hauler_Tickets:Bg_Hauler_Tickets' and
> 'LANGES3:Bg_Hauler_Tickets:Bg_Hauler_Tickets'.
> Thanks for any help you can give.
> Jeff
>

drop subscriptions

Hi all,
How can I remove a list of subscriptions from the server?
My server acts as PUBLISHER and SUBSCRIBER at the same time, but both
features has been disabled. So, I can't delete the subscriptions list
because I have no PUBLISHER and SUBSCRIBER now.
I've tryied with differents commands like sp_dropsubscriptions,
sp_dropsubscriber, etc, but nothing works.
Is there any way to drop all this trash?
thanks.
Can you enable the databases for publishing and then remove them?
If there are no live subscriptions you can merely delete the contents of
MSreplication_subscriptions and MSsubscription_agents if you have
transactional replication.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"JG Espinoza" <jasiele@.NOSPAMhotmail.com> wrote in message
news:ePyf4TS%23EHA.3616@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> How can I remove a list of subscriptions from the server?
>
> My server acts as PUBLISHER and SUBSCRIBER at the same time, but both
> features has been disabled. So, I can't delete the subscriptions list
> because I have no PUBLISHER and SUBSCRIBER now.
>
> I've tryied with differents commands like sp_dropsubscriptions,
> sp_dropsubscriber, etc, but nothing works.
>
> Is there any way to drop all this trash?
>
> thanks.
>
|||I can enable the database but i can't delete it as well.
How can I delete the content of MSreplication_subscriptions and
MSsubscription_agents?
I have both transactional and merge subscriptions.
Thanks for your help.
"Hilary Cotter" <hilary.cotter@.gmail.com> escribi en el mensaje
news:exdnkkS%23EHA.1076@.TK2MSFTNGP10.phx.gbl...
> Can you enable the databases for publishing and then remove them?
> If there are no live subscriptions you can merely delete the contents of
> MSreplication_subscriptions and MSsubscription_agents if you have
> transactional replication.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "JG Espinoza" <jasiele@.NOSPAMhotmail.com> wrote in message
> news:ePyf4TS%23EHA.3616@.TK2MSFTNGP11.phx.gbl...
>
|||As the databases aren't used for publications or
subscriptions, you could use sp_removedbreplication.
Regards,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||My above answer about enabling and disabling the databases for publication
was a bad one.
For transactional all you have to do is
delete from MSreplication_subscriptions
or
delete from MSsubscription_agents
To get rid of merge publication metadata use this script
http://groups-beta.google.com/group/...4?dmode=source
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"JG Espinoza" <jasiele@.NOSPAMhotmail.com> wrote in message
news:%23$xWFoS%23EHA.3416@.TK2MSFTNGP09.phx.gbl...
> I can enable the database but i can't delete it as well.
>
> How can I delete the content of MSreplication_subscriptions and
> MSsubscription_agents?
>
> I have both transactional and merge subscriptions.
>
>
> Thanks for your help.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> escribi en el mensaje
> news:exdnkkS%23EHA.1076@.TK2MSFTNGP10.phx.gbl...
>

Drop and Recreate subscription

I need to drop and recreate few subscriptions in transactional publication
Do I need to worry about log marker issues ?
Do I need to set the primary and replicate databases in 'DBO use only'
The Primary and Replicate databases are being accessed all the time.To your two questions, the answers are

No and No.

drop 1 article from publication and add it back, sql server 2000 s

Recently I have experienced problems with subscriptions getting out of sync.
Some of the published tables are very large and are connected to the
subscriber via t1 connection. To avoid re-initializing the entire publication
I ran sp_dropsubscription followed by sp_droparticle, both specifying the
article that is out of sync. Then I opened the publication properties from
the replication monitor, went to the articles tab, and added the article back
to the publication. When I run the snapshot again a new snapshot is generated
for that article and it subsequently is pushed to the subscriber. Sometimes
the artilces will get out of sync again in a day or two and some articles
stay synchronized without any problems.
The script I run to drop the article is:
use db1
go
declare @.p varchar(128)
declare @.a varchar(128)
declare @.db varchar(128)
set @.p = 'db1_Publication'
set @.a = 'tbl1'
set @.db = 'db1'
exec sp_dropsubscription @.publication = @.p ,@.article = @.a
,@.subscriber = 'rptServer' ,@.destination_db = @.db
exec sp_droparticle @.publication = @.p
,@.article= @.a
,@.force_invalidate_snapshot = 0
I believe this is the correct way to drop 1 article from a publication that
contains many articles and adding it back again to reinitialize, but I
haven't been able to find any examples of this anywhere.
The question I pose to the replication gurus is, does this work? I am unsure
at this point due to the inconsistent results I've been getting.
Thanks,
Jason
Jason,
this is much the same way I do it as well (see
http://www.replicationanswers.com/AddColumn.asp). I would like to
investigate the non-synchronization of these tables though. Do you see any
commands in the distribution database for these tables (sp_browsereplcmds)?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)