sp_dropmergearticle (Transact-SQL)
Removes an article from a merge publication. This stored procedure is executed at the Publisher on the publication database.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
sp_dropmergearticle [ @publication= ] 'publication'
, [ @article= ] 'article'
[ , [ @ignore_distributor= ] ignore_distributor
[ , [ @reserved= ] reserved
[ , [ @force_invalidate_snapshot= ] force_invalidate_snapshot ]
[ , [ @force_reinit_subscription = ] force_reinit_subscription ]
[ , [ @ignore_merge_metadata = ] ignore_merge_metadata ]
Arguments
- [ @publication=] 'publication'
Is the name of the publication from which to drop an article. publicationis sysname, with no default.
- [ @article=] 'article'
Is the name of the article to drop from the given publication. articleis sysname, with no default. If all, all existing articles in the specified merge publication are removed. Even if article is all, the publication still must be dropped separately from the article.
- [ @ignore_distributor=] ignore_distributor
Indicates whether this stored procedure is executed without connecting to the Distributor. ignore_distributor is bit, with a default of 0.
- [ @reserved=] reserved
Is reserved for future use. reserved is nvarchar(20), with a default of NULL.
[ @force_invalidate_snapshot=] force_invalidate_snapshot
Enables or disables the ability to have a snapshot invalidated. force_invalidate_snapshot is a bit, with a default 0.0 specifies that changes to the merge article do not cause the snapshot to be invalid.
1 means that changes to the merge article may cause the snapshot to be invalid, and if that is the case, a value of 1 gives permission for the new snapshot to occur.
[ @force_reinit_subscription = ] force_reinit_subscription
Acknowledges that dropping the article requires existing subscriptions to be reinitialized. force_reinit_subscription is a bit, with a default of 0.0 specifies that dropping the article does not cause the subscription to be reinitialized.
1 means that dropping the article causes existing subscriptions to be reinitialized, and gives permission for the subscription reinitialization to occur.
- [ @ignore_merge_metadata= ] ignore_merge_metadata
Internal use only.
Return Code Values
0 (success) or 1 (failure)
Remarks
sp_dropmergearticle is used in merge replication. For more information about dropping articles, see Adding Articles to and Dropping Articles from Existing Publications.
Executing sp_dropmergearticle to drop an article from a publication does not remove the object from the publication database or the corresponding object from the subscription database. Use DROP <object> to remove these objects manually if necessary.
Permissions
Only members of the sysadmin fixed server role or the db_owner fixed database role can execute sp_dropmergearticle.
Example
DECLARE @publication AS sysname;
DECLARE @article1 AS sysname;
DECLARE @article2 AS sysname;
SET @publication = N'AdvWorksSalesOrdersMerge';
SET @article1 = N'SalesOrderDetail';
SET @article2 = N'SalesOrderHeader';
-- Remove articles from a merge publication.
USE [AdventureWorks]
EXEC sp_dropmergearticle
@publication = @publication,
@article = @article1,
@force_invalidate_snapshot = 1;
EXEC sp_dropmergearticle
@publication = @publication,
@article = @article2,
@force_invalidate_snapshot = 1;
GO
See Also
Reference
sp_addmergearticle (Transact-SQL)
sp_changemergearticle (Transact-SQL)
sp_helpmergearticle (Transact-SQL)
Replication Stored Procedures (Transact-SQL)
Other Resources
How to: Delete an Article (Replication Transact-SQL Programming)
Adding Articles to and Dropping Articles from Existing Publications