Delete table from merge subscriber without dropping current replication
-
Friday, November 16, 2012 12:56 PMI want to delete a table from a subscriber database of merge replication.(i mean i want to delete table from database itself ,not just removing from replication ). So how i can delete a single table from subscriber db without affecting current replication on that database.It is ok for me that same table from publisher can be deleted, So can anyone please help me.
All Replies
-
Friday, November 16, 2012 1:12 PMModeratorYou can drop it from the merge publication properties or by using sp_dropmergesubscription and then sp_dropmergearticle. It will force a reinitialization.
looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
-
Friday, November 16, 2012 1:26 PM
Thank u Hilary..
Will sp_dropmergesubscription delete subscription itself along with rowguid column?
Also without applying snapshot or reinitialization again can i do this?
-
Friday, November 16, 2012 1:30 PMModeratorYes, it will drop the subscription. The rowguid column will not be deleted.
looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
-
Friday, November 16, 2012 1:41 PMSo i have to create again subscription for the merge publisher.but is reinitializating with snapshot of publisher is required?
-
Friday, November 16, 2012 4:56 PMModeratorYou can drop the article from the merge publication properties. Then you will receive a prompt that there will be a reinitialization and you have to go from there.
looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
-
Friday, November 16, 2012 5:08 PMModerator
To drop an article from an existing Merge publication does not require reinitialization. A new snapshot must be generated but reinitialization is not required.
To drop the article without reinitialization you must execute sp_dropmergearticle at the publisher and generate a new snapshot.
USE PublicationDatabase GO EXEC sp_dropmergearticle @publication = 'PublicationName', @article = 'ArticleName', @force_invalidate_snapshot = 1; GO EXEC sp_startpublication_snapshot @publication = 'PublicationName'; GO
This will drop the Merge article from the existing publication. If you also require the table to dropped from the publication or subscription database you must also use DROP <Object> to remove the table.
-
Friday, November 16, 2012 5:30 PMModeratorThere are some exceptions that require reinitialization that are covered in the section Dropping Articles in Add Articles to and Drop Articles from Existing Publications.
-
Friday, November 16, 2012 5:47 PMModeratorBrandon is correct - I am used to filtered publications where dropping a filtered table will cause a reinitialization.
looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
-
Saturday, November 17, 2012 4:37 AMI want to delete table from subscriber also. Can i do it without applying a snapshot of publication again?
-
Saturday, November 17, 2012 4:42 AMModeratorYes, you should be able to. You may have to disable the database triggers to drop it though.
looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
-
Saturday, November 17, 2012 4:51 AMHow to disable the database triggers so that snapshot of publication is not applied again?
-
Saturday, November 17, 2012 4:59 AMModerator
Run this in your subscription database:
DISABLE TRIGGER [MSmerge_tr_altertable] ON DATABASE
Drop the table, then enable the trigger.
looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
-
Saturday, November 17, 2012 5:01 AM
But what does this trigger does?
If i run this should i drop my subscription to delete a table from subscriber db?
-
Saturday, November 17, 2012 5:08 AMModerator
right click on your publication, select properties, articles, and then uncheck the table you wish to drop.Once it is dropped, connect to your subscriber in management studio, and select new query, disable the database trigger, drop the table, re-enable the trigger and you are done.
looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
-
Saturday, November 17, 2012 5:23 AM
ok. In this case can i delete table at both publisher and subscriber?
Also what does these trigger do exactly?
-
Saturday, November 17, 2012 5:26 AMModeratorAfter removing the table from the publication you can drop it from the publisher and subscriber. the trigger prevents schema changes from occurring on the subscriber.
looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
-
Saturday, November 17, 2012 5:31 AMThank u hilary.. I will give it a try. i think this method is some sort of shortcut for dropping a table.I didn't get this method in other websites.
-
Saturday, November 17, 2012 8:00 AM
I tried running it in subscription database
DISABLE TRIGGER [MSmerge_tr_altertable] ON DATABASE
Command(s) completed successfully.
But when i try to delete a table from this database again error comes as cannot drop the table 'dbo.T2' because it is being used for replication.
i have not dropped my subscription or publication.why this trigger didn't work?
-
Sunday, November 18, 2012 12:07 AMModerator
Hi Winman Software,
Disabling triggers is not necessary. This is not a schema change. We are merely dropping an article.
Also, reinitialization is not required. I want to be clear that you will have to generate a new snapshot but reinitialization is not required. There is a difference.
If the article is not a parent in a join filter or is not the last parameterized filter in a publication, follow these steps:
USE PublicationDatabase GO EXEC sp_dropmergearticle @publication = 'PublicationName', @article = 'ArticleName', @force_invalidate_snapshot = 1; GO EXEC sp_startpublication_snapshot @publication = 'PublicationName'; GO
Then synchronize the Merge Agent(s).
After synchronizing, issue the drop table statement at the subscriber.
USE SubscriberDatabase GO DROP TABLE table_name
I have tested this against non-filtered, non-parameterized filtered publication and it works perfectly.
- Proposed As Answer by Shulei ChenModerator Monday, November 19, 2012 5:49 AM
- Marked As Answer by IT researcher Monday, November 19, 2012 11:11 AM
-
Monday, November 19, 2012 11:13 AM
thank you Brandon..
This method worked fine.
.png)
