proper way to drop publication?

Hi There

According to BOL, the correct procedure for deleting a publication are as follows:
These must be run on the publication database.

Execute sp_dropsubscription to delete all snapshot subscriptions.

Execute sp_droppublication to delete the publication and all of its articles.

My only issue is after step 1 the subscription still exists at the subscriber and it marked as failed (does not exist), i always have to go delete the subscription afterwards at the subscriber. AM i missing something? WHy does sp_dropsubscription not do this? Or is there another sp i should be running first?

Thanx

Hi Dietz,

The actual step to cleanup subscription depending on the subscription is push or pull. Basically, for push, you need to call sp_dropsubscription at publisher followed by sp_subscription_cleanup at subscriber. For Pull, you need to call sp_droppullsubscription at subscriber followed by sp_dropsubscription at pubisher.

Please refer to BOL topic "How to: Delete a Pull Subscription (Replication Transact-SQL Programming)" and "How to: Delete a Push Subscription (Replication Transact-SQL Programming)".

Thanks,|||Great thanx Peng, i wonder why they do not mention any of those sp's under the How to Delete Publications and Articles (Transact-SQL) BOL topic.
Also i cannot find

"How to: Delete a Pull Subscription (Replication Transact-SQL Programming)" and "How to: Delete a Push Subscription (Replication Transact-SQL Programming)".in BOL only TSQL references not Replication Transact sql programming.

I am using SS2000 not SS2005?

Thanx again|||Sorry, dietz. I made a bad assumption to think your system is SQL 2005. My previous post really means for SQL 2005.

In SQL 2000 BOL, there is similar topic on how to drop subscription, depending on pull/push of the subscription. You probably already see it in "How To"->"Transact SQL" section.

Peng
sql

Programmatically reinitializing merge subscription from publisher - SQL 2005

From the SSMS GUI it is possible to re-initialize one or all subscriptions to a merge publication. This is done at the publisher.

How can I achieve these operations programmatically?

In particular, how do I initialize a single merge subscription from the publisher?

I have looked at the documentation for sp_reinitmergepullsubscription but it says that this proc must be run at the subscriber - which isn't much use when subscribers are disconnected for the majority of the time

I have a large number of merge subscribers and want to reinitialize all except one

aero1

You can use the following statement to re-initiliase your subscribers:

exec sp_reinitmergesubscription @.publication=<Publication Name>, @.subscriber=<Subscriber Name>, @.subscriber_db = <Subscriber DB>, @.upload_first = <TRUE|FALSE>

You can get the details of the subscribers from the sysmergesubscriptions table.

More info on re-intiialising here:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/249a4048-e885-48e0-a92a-6577f59de751.htm

|||

Just what I needed

Thanks

aero1

Programmatically reinitializing merge subscription from publisher - SQL 2005

From the SSMS GUI it is possible to re-initialize one or all subscriptions to a merge publication. This is done at the publisher.

How can I achieve these operations programmatically?

In particular, how do I initialize a single merge subscription from the publisher?

I have looked at the documentation for sp_reinitmergepullsubscription but it says that this proc must be run at the subscriber - which isn't much use when subscribers are disconnected for the majority of the time

I have a large number of merge subscribers and want to reinitialize all except one

aero1

You can use the following statement to re-initiliase your subscribers:

exec sp_reinitmergesubscription @.publication=<Publication Name>, @.subscriber=<Subscriber Name>, @.subscriber_db = <Subscriber DB>, @.upload_first = <TRUE|FALSE>

You can get the details of the subscribers from the sysmergesubscriptions table.

More info on re-intiialising here:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/249a4048-e885-48e0-a92a-6577f59de751.htm

|||

Just what I needed

Thanks

aero1

Programatically create replication publication?

Is it possible to create a publication entirely from within my application?
If so, are there some samples or documentation anyone can point me to?
Thanks in advance,
Terry
Terry,
please have a look at the SQLDMO and ActiveX scripts on this page:
http://www.replicationanswers.com/Scripts.asp.
Also, you might want to investigate creating a replication setup in a test
environment, having enterprise amnager script out the setup, modifying it
then using this as a starting point for your setup. This would achieve the
same ends, but be entirely in TSQL.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hello Terry,
If you are using SQL 2005, you may need to consider RMO
Programming with Replication Management Objects
http://msdn2.microsoft.com/en-us/library/ms146869.aspx
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>From: "Paul Ibison" <Paul.Ibison@.Pygmalion.Com>
>References: <OXitg7E9FHA.3020@.TK2MSFTNGP09.phx.gbl>
>Subject: Re: Programatically create replication publication?
>Date: Mon, 28 Nov 2005 19:31:30 -0000
>Lines: 13
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>X-RFC2646: Format=Flowed; Response
>Message-ID: <uT77YJF9FHA.3880@.TK2MSFTNGP12.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.replication
>NNTP-Posting-Host: 82-43-215-101.cable.ubr10.newm.blueyonder.co.uk
82.43.215.101
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP12.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.replication:67037
>X-Tomcat-NG: microsoft.public.sqlserver.replication
>Terry,
>please have a look at the SQLDMO and ActiveX scripts on this page:
>http://www.replicationanswers.com/Scripts.asp.
>Also, you might want to investigate creating a replication setup in a test
>environment, having enterprise amnager script out the setup, modifying it
>then using this as a starting point for your setup. This would achieve the
>same ends, but be entirely in TSQL.
>Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Thanks guys ...
Your suggestions look like my answers.
Tery
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:HNKee6K9FHA.4000@.TK2MSFTNGXA02.phx.gbl...
> Hello Terry,
> If you are using SQL 2005, you may need to consider RMO
> Programming with Replication Management Objects
> http://msdn2.microsoft.com/en-us/library/ms146869.aspx
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> --
> 82.43.215.101
>
|||Hello Terry,
My pleasure! :-)
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>From: "Tery" <justme@.noemail.noemail>
>References: <OXitg7E9FHA.3020@.TK2MSFTNGP09.phx.gbl>
<uT77YJF9FHA.3880@.TK2MSFTNGP12.phx.gbl>
<HNKee6K9FHA.4000@.TK2MSFTNGXA02.phx.gbl>
>Subject: Re: Programatically create replication publication?
>Date: Tue, 29 Nov 2005 08:55:11 -0600
>Lines: 68
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>Message-ID: <uh0JjTP9FHA.2192@.TK2MSFTNGP14.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.replication
>NNTP-Posting-Host: dsl-201-155-245-16.prod-empresarial.com.mx
201.155.245.16[vbcol=seagreen]
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP14.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.replication:67058
>X-Tomcat-NG: microsoft.public.sqlserver.replication
>Thanks guys ...
>Your suggestions look like my answers.
>Tery
>"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
>news:HNKee6K9FHA.4000@.TK2MSFTNGXA02.phx.gbl...
test[vbcol=seagreen]
the
>
>