July 26, 2006 at 2:25 am
Good day all,
I am in the process of replicating data from our live server to a reports server, and have about 20 or so articles at the moment set up as transactional replication. All the smaller tables are replicating perfectly, but i received an error when replicating two of the large tables (49 million rows) Now i need to remove these 2 articles from the publication to try again, but i cannot uncheck the box in the publication properties in enterprise manager.
Does anyone know what cold be the cause of this? I have also tried to use sp_droparticle but to no avail.
Any ideas?
July 26, 2006 at 3:55 pm
you should be able to drop the articles from subscriton. try this:
exec sp_dropsubscription @publication = 'pub_name'
, @article = 'Categories'
, @subscriber = 'sub_name'
, @destination_db = 'Northwind'
exec sp_droparticle @publication = 'pub_name'
, @article = 'Categories'
exec sp_addarticle @publication = 'pub_name'
, @article = 'Categories'
, @source_table = 'application'
exec sp_addsubscription @publication = 'pub_name'
, @article = 'Categories'
, @subscriber = 'sub_name'
, @destination_db = 'Northwind'
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
July 27, 2006 at 3:39 am
Hey Hey, it worked!!!
Thanks a stack, your help is most appreciated!
August 9, 2006 at 8:38 am
Another way is to script out the "drop publication" and "create publication" commands, then run just the ones to drop your specific articles.
This comes in handy if you need to change the schema of a replicated table. Drop the article, change the schema, then add it back in. Saving the scripts will save you having to figure out all the syntax.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply