Hi,
Is there any way to drop a single replication. one subscriber server was taken down without dropping first. Now I have a pull at publisher which is in state of reinitialized and waiting for the server to be back on.
I don not want to drop all replication and create them again. So I wonder if there is anyway to just drop that single replication belong to the taken off server.
Thanks for the help in advance.Yes, just execute 'sp_dropsubscription' with the correct parameters in the publication.
Just for future references, the pull subscription should be dropped in the subscriber before you take the server down.|||ty.
Edit:
according to BOL.
sp_dropsubscription is used in snapshot and transactional replication.
but this is merge.|||Did you mention it was a Merge?|||yup it is merge replication. I am sorry I didn't mention on first msg.
Showing posts with label publisher. Show all posts
Showing posts with label publisher. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
Dropping an article
I want to drop a table from some of my transactional publications. Can I just
drop the article on the publisher and then be able to delete the table at the
subscriber?
Or do I have to drop the article and re-snapshot the subscriber?
Russell,
using the GUI you'd have to drop the subscriptions then drop the article,
but this is one of those cases where doing things in code is a little
different - you can drop the subscription to the individual article then
drop the article itself:
exec sp_dropsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
exec sp_droparticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for the info Paul. If I carryout the two commands can I then just
delete the table from the subscribing database. Doese this process work for
both Merge and Transactional Publications.
"Paul Ibison" wrote:
> Russell,
> using the GUI you'd have to drop the subscriptions then drop the article,
> but this is one of those cases where doing things in code is a little
> different - you can drop the subscription to the individual article then
> drop the article itself:
> exec sp_dropsubscription @.publication = 'tTestFNames'
> , @.article = 'tEmployees'
> , @.subscriber = 'RSCOMPUTER'
> , @.destination_db = 'testrep'
> exec sp_droparticle @.publication = 'tTestFNames'
> , @.article = 'tEmployees'
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Russell,
yes - you can drop the table after removing the subscriptions to it and
removing it from the publication.
no - it only works for transactional. For merge you'll need to drop the
subscription entirely before being able to drop the article and the table.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I also use the script to drop the article(s) I don't want.
What I do is:
1) Right click on the publication that you want to remove the table from.
2) Generate DELETE script.
3) Paste into Query Analyzer
4) Find and run the sp_dropsubscription and sp_droparticle commands for the
table you want to run.
5) All done.
This technique, in conjunction with the CREATE script can be used if the
schema of a replicated table needs to be changed.
You generate & SAVE both the DELETE & CREATE scripts. Remove the article from
replication, change the table, then add it back in with the relevant portion
of the create script.
Russell wrote:
>I want to drop a table from some of my transactional publications. Can I just
>drop the article on the publisher and then be able to delete the table at the
>subscriber?
>Or do I have to drop the article and re-snapshot the subscriber?
drop the article on the publisher and then be able to delete the table at the
subscriber?
Or do I have to drop the article and re-snapshot the subscriber?
Russell,
using the GUI you'd have to drop the subscriptions then drop the article,
but this is one of those cases where doing things in code is a little
different - you can drop the subscription to the individual article then
drop the article itself:
exec sp_dropsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
exec sp_droparticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for the info Paul. If I carryout the two commands can I then just
delete the table from the subscribing database. Doese this process work for
both Merge and Transactional Publications.
"Paul Ibison" wrote:
> Russell,
> using the GUI you'd have to drop the subscriptions then drop the article,
> but this is one of those cases where doing things in code is a little
> different - you can drop the subscription to the individual article then
> drop the article itself:
> exec sp_dropsubscription @.publication = 'tTestFNames'
> , @.article = 'tEmployees'
> , @.subscriber = 'RSCOMPUTER'
> , @.destination_db = 'testrep'
> exec sp_droparticle @.publication = 'tTestFNames'
> , @.article = 'tEmployees'
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Russell,
yes - you can drop the table after removing the subscriptions to it and
removing it from the publication.
no - it only works for transactional. For merge you'll need to drop the
subscription entirely before being able to drop the article and the table.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I also use the script to drop the article(s) I don't want.
What I do is:
1) Right click on the publication that you want to remove the table from.
2) Generate DELETE script.
3) Paste into Query Analyzer
4) Find and run the sp_dropsubscription and sp_droparticle commands for the
table you want to run.
5) All done.
This technique, in conjunction with the CREATE script can be used if the
schema of a replicated table needs to be changed.
You generate & SAVE both the DELETE & CREATE scripts. Remove the article from
replication, change the table, then add it back in with the relevant portion
of the create script.
Russell wrote:
>I want to drop a table from some of my transactional publications. Can I just
>drop the article on the publisher and then be able to delete the table at the
>subscriber?
>Or do I have to drop the article and re-snapshot the subscriber?
Monday, March 19, 2012
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...
>
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 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
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...
>
>
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...
>
>
Subscribe to:
Posts (Atom)