Dropping Articles (Replication) Can it be done

Could any of you kind Gentlemen/Ladies tell me :-
Is there a way to drop Articles within a Publication without having to kill the Subscription ?
SQL Server 2000 SP3
Merge Publication of Articles Include Views,Stored Procedures & Tables
Many Thanks
GWYou can not drop any articles or columns if the publication has any subscriptions. You must first drop the subscription(s).|||Thanks For Trying JS but

I don't want to kill the Subscription as it means halting Production

and

you can drop columns in SQL 2k without dropping the Subscription

GW|||-IF- you have replication setup using stored procedures and not verb based, you can be vewwy sneaky and blank out the code within the sprocs and recompile them.

wahh-lahh

this, effectively does the same thing- (and works)

cheers,

isaac|||Lol Thanks Isaac

I don't know what verb based replication is but hear whot u r saying about modifying the SP's

In this case I will drop the subscriptions

Maybe next time delve into the SP's

Thanks

GW|||I thought you'd like that little trick :)

when you're creating the publication, you have the option of doing verb based or sproc based replication.

verb based just means that the distributor is issuing INSERT/UPDATE/DELETE commands directly against the server, instead of proxying them through stored procedures.

I prefer the sproc route because it gives you a lot of flexibility without having to rely on MS's row-based filtering stuff in the wizards.

I also heartily recommend using the "script to sql" option in the right-click context menu of the publication entry within e-mangler (and i mean that lovingly).

After you observe the code, you'll find that replication is much simpler than what the manuals make it out to be (which is usually the case).

at any rate, I can now recreate my publication from scratch pointed to a separate distributor, and setup 5 separate push-subscriptions in about 30 seconds through ISQL. This also allows you to keep your production config and setup scripts checked nicely into VSS where it belongs! ;)

good luck,

-isaac|||I will look further into the verb based stuff when the time comes.

I agree the SQL Scripting Option is a bonus in many situations & I use it all the time !

All Hail the Learning Curve

Thanks again M8y

Dropping article on transaction publications.

In 2005 transactional replication, The following procedure worked (without dropping the subscription) when I dropped an article from a replicated database:

Drop article: On Publication Properties, uncheck the article (table, stored procedure or function).

Create a new snapshot.

Synchronize the push subscription.

DROP the article on the Publication and Subscriber databases.

Replication still works!

However, the following article says the subscription needs to be dropped and re-created when an article is dropped from publication: http://msdn2.microsoft.com/en-us/library/ms152493.aspx (Adding Articles to and Dropping Articles from Existing Publications ). For transactional publications, articles can be dropped with no special considerations prior to subscriptions being created. If an article is dropped after one or more subscriptions is created, the subscriptions must be dropped, recreated, and synchronized.

Under what conditions is dropping the subscription and recreating it absolutely necessary? I do not want to include this extra step.

Linda

Hi Linda, the documentation is correct. If you try to drop the artice via TSQL stored proc sp_droparticle, it will raise an error saying you you cannot drop the article because you have existing subscription. What I think BOL should say is that "the subscriptions to the article must be dropped before you can drop the article".

This is what the UI is doing - it will first drop the article from all existing subscriptions (via sp_dropsubscription), then drop the article from the publication and then invalidate the snapshot. So for existing subscriptions, there isn't anything else you need to do, but if you add a new subscription afterwards, you have to regenerate a new snapshot.

I hope this is a little more clear.

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 subscription locking users

Why would a delete of a subscription & publication lock users in the database?
It is a large publication, but when I looked at the activity it was doing a sp_dropsubscription, and I don't understand why this locks the users out of the tables.
What can I do to drop this old subscription & publication?
The new publication & subscription are up on the new server, but I want to delete the old without locking the users, how?
Thanx!
From what you have described below, it would appear that you were dropping the last subscription on the old publisher database (I am guessing here... the new publication is on a different server right?) What happens in this case is that the "replicated" bits on the published tables are reset to 0 which, unfortunately, is considered a schema change by the server and thus requiring the use of sch-mod lock on the published table. When you drop a subscription through SEM, sp_dropsubscription is called with @.article = 'all' which would in turn cause sch-mod lock to be obtained for all published tables in the publication. Obviously, this is not something that is easily achievable when there are concurrent activities at the publisher (the old one you have) database. One way to workaround this is to drop subscription one article at a time by manually calling sp_dropsusbcription within a cursor through the list of article names that you have in your publication.
Things get a bit more complicated if your publication has the immediate_sync property set to 1 (a requirement for allowing anonymous subscriptions) as the "last" subscription on the publication is actually the "virtual" subscription that we create for you automatically and the virtual subscription will not be dropped unless the articles in your publication are dropped. So, if your subscription has the immediate_sync property set to 1, you would need to drop articles one by one after dropping your subscription to avoid sch-mod locks being taken simultaneously for all published tables in your publication.
Hope that helps.
-Raymond
"JLS" <jlshoop@.hotmail.com> wrote in message news:eIaHflI5FHA.156@.TK2MSFTNGP15.phx.gbl...
Why would a delete of a subscription & publication lock users in the database?
It is a large publication, but when I looked at the activity it was doing a sp_dropsubscription, and I don't understand why this locks the users out of the tables.
What can I do to drop this old subscription & publication?
The new publication & subscription are up on the new server, but I want to delete the old without locking the users, how?
Thanx!
|||The new publication is on the same server, same database, the subscriber is a new server, so in essence your assumption is correct. I want to drop the old publication on the existing publishing server/database, since the subscribing server will be retired.
Ok, so I need to drop my articles on this publication one by one. Ugh! That's 1600+ articles.
Thanx for the answer.
"Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message news:%23KCcOfJ5FHA.2432@.TK2MSFTNGP10.phx.gbl...
From what you have described below, it would appear that you were dropping the last subscription on the old publisher database (I am guessing here... the new publication is on a different server right?) What happens in this case is that the "replicated" bits on the published tables are reset to 0 which, unfortunately, is considered a schema change by the server and thus requiring the use of sch-mod lock on the published table. When you drop a subscription through SEM, sp_dropsubscription is called with @.article = 'all' which would in turn cause sch-mod lock to be obtained for all published tables in the publication. Obviously, this is not something that is easily achievable when there are concurrent activities at the publisher (the old one you have) database. One way to workaround this is to drop subscription one article at a time by manually calling sp_dropsusbcription within a cursor through the list of article names that you have in your publication.
Things get a bit more complicated if your publication has the immediate_sync property set to 1 (a requirement for allowing anonymous subscriptions) as the "last" subscription on the publication is actually the "virtual" subscription that we create for you automatically and the virtual subscription will not be dropped unless the articles in your publication are dropped. So, if your subscription has the immediate_sync property set to 1, you would need to drop articles one by one after dropping your subscription to avoid sch-mod locks being taken simultaneously for all published tables in your publication.
Hope that helps.
-Raymond
"JLS" <jlshoop@.hotmail.com> wrote in message news:eIaHflI5FHA.156@.TK2MSFTNGP15.phx.gbl...
Why would a delete of a subscription & publication lock users in the database?
It is a large publication, but when I looked at the activity it was doing a sp_dropsubscription, and I don't understand why this locks the users out of the tables.
What can I do to drop this old subscription & publication?
The new publication & subscription are up on the new server, but I want to delete the old without locking the users, how?
Thanx!

drop subscription

is there anyone know how to drop subscription from the publisher database without a connection to the subscriber(because the subscriber server is formated)You can certainly do this using the replication stored procedures, but the GUI can't do it for you. You'll need to get someone that understands both SQL Server replication and your environment in order to do this safely. Microsoft Professional Support Services will help you work through this for a fee.

Another alternative is to backup the database, drop the database, then restore the backup. This is simple and except for the down time relatively painless.

-PatP|||actually the backup, drop and restore solution is the easiest way but you can do that in a test environment but I have a live environment of 50 subscribers and if I want to create a snapshots for these subscribers it'll take a lot of time thats for coping a bout 800MB at least throw a WAN network
However, you can drop the publications and rebuild them on the publisher database.
You cay find the sp_dropsubscriptions but it's not working good
I'm trying to reach someone can drop the subscription for one subscriber only without dropping all publication|||I can do it, and I'm sure that some of the folks here on DBForums can do it, but I don't know any simple way to describe how to do it. You really need to understand how SQL Replication works, and how your particular setup is configured.

I'm sorry, but I don't know of a simple solution to your problem.

The cheapest answer long term is likely to be to call MS-PSS and ask them to walk you through the process. It will cost you some money, but only a tiny fraction of what rebuilding whole databases might cost.

-PatP

Drop replication Pull job without Subscription publication databas

Publisher/Distribution job was removed but on Subscriver was not, since then
we are not able to remobe Pull job without Subscription publication database
on Subscriver server, any help how to remove it?
Identify the job by its name and then use sp_dropjob to remove it.
"Jose Milhazes" <Jose Milhazes@.discussions.microsoft.com> wrote in message
news:FFB75B9A-C4EF-42CA-8B27-313E70972490@.microsoft.com...
> Publisher/Distribution job was removed but on Subscriver was not, since
> then
> we are not able to remobe Pull job without Subscription publication
> database
> on Subscriver server, any help how to remove it?
|||Thanks, job was removed
"Hilary Cotter" wrote:

> Identify the job by its name and then use sp_dropjob to remove it.
> "Jose Milhazes" <Jose Milhazes@.discussions.microsoft.com> wrote in message
> news:FFB75B9A-C4EF-42CA-8B27-313E70972490@.microsoft.com...
>
>

Drop merge filter (pull subscription)

Hi everybody,
I'm trying to remove a rowfilter (and 'JOINS') from one of my publication.
When I first tried this on a testdatabase everything seemed to work. But it's
not working on the productiondatabase. I always receive this message:
Cannot drop filter 'Table1_Table2' from publication 'NamePublication'
because its snapshot has been run and this publication could have active
subscriptions.
I don't know how to solve this. Any help would be appreciated!
Thanks!
Benno,
try scripting it out, delete the publication then run the script, be sure to
disable the snapshot agent, then remove the filter.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
thanks for your reply bu I'm afraid I don't understand all of it: do you
think I should delete the publication (without deleting the subscriptions?)
and than recreate it (using the script I created first)? And how would you
delete the publication: EM or stored procedure?
Thanks!
"Paul Ibison" wrote:

> Benno,
> try scripting it out, delete the publication then run the script, be sure to
> disable the snapshot agent, then remove the filter.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Benno - that's right. If you script out the publication and subscriptions,
you can then delete the subscriptions and the publication. Aftert that you
use the script to create the publication. Delete the filters you don't want
then add the subscriptions and initialize.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
can I create and drop the PULL subscriptions "remotely". I don't want to
visit all subscribers to drop the subscription and recreate it afterwards...
Thanks!
"Paul Ibison" wrote:

> Benno - that's right. If you script out the publication and subscriptions,
> you can then delete the subscriptions and the publication. Aftert that you
> use the script to create the publication. Delete the filters you don't want
> then add the subscriptions and initialize.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||Benno,
in the case of merge replication, the script to be run at Subscriber
includes:
exec sp_addmergepullsubscription
exec sp_addmergepullsubscription_agent
If you can connect to and register in EM all the subscribers and run this,
then it can all be set up from one location.
The corresponding script to set up all these subscriptions is also
obtainable for EM: Right Clich the publications foldes and select to
Generate SQL Script...
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

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.