sp_droparticle (Transact-SQL)

Applies to: SQL Server Azure SQL Managed Instance

Drops an article from a snapshot or transactional publication. An article cannot be removed if one or more subscriptions to it exist. This stored procedure is executed at the Publisher on the publication database.

Transact-SQL syntax conventions

Syntax

  
sp_droparticle [ @publication= ] 'publication'  
        , [ @article= ] 'article'  
    [ , [ @ignore_distributor = ] ignore_distributor ]  
    [ , [ @force_invalidate_snapshot= ] force_invalidate_snapshot ]  
    [ , [ @publisher = ] 'publisher' ]  
    [ , [ @from_drop_publication = ] from_drop_publication ]  

Arguments

[ @publication = ] 'publication' Is the name of the publication that contains the article to be dropped. publication is sysname, with no default.

[ @article = ] 'article' Is the name of the article to be dropped. article is sysname, with no default.

[ @ignore_distributor = ] ignore_distributor

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

[ @force_invalidate_snapshot = ] force_invalidate_snapshot Acknowledges that the action taken by this stored procedure may invalidate an existing snapshot. force_invalidate_snapshot is a bit, with a default of 0.

0 specifies that changes to the article do not cause the snapshot to be invalid. If the stored procedure detects that the change does require a new snapshot, an error occurs and no changes are made.

1 specifies that changes to the article may cause the snapshot to be invalid, and if there are existing subscriptions that would require a new snapshot, gives permission for the existing snapshot to be marked as obsolete and a new snapshot generated.

[ @publisher = ] 'publisher' Specifies a non- Microsoft SQL Server Publisher. publisher is sysname, with a default of NULL.

Note

publisher should not be used when changing article properties on a SQL Server Publisher.

[ @from_drop_publication = ] from_drop_publication

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_droparticle is used in snapshot and transactional replication.

For horizontally filtered articles, sp_droparticle checks the type column of the article in the sysarticles (Transact-SQL) table to determine whether a view or filter should also be dropped. If a view or filter was autogenerated, it is dropped with the article. If it was manually created, it is not dropped.

Executing sp_droparticle 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 manually remove these objects if necessary.

Example

DECLARE @publication AS sysname;
DECLARE @article AS sysname;
SET @publication = N'AdvWorksProductTran'; 
SET @article = N'Product'; 

-- Drop the transactional article.
USE [AdventureWorks2012]
EXEC sp_droparticle 
  @publication = @publication, 
  @article = @article,
  @force_invalidate_snapshot = 1;
GO

Permissions

Only members of the sysadmin fixed server role or db_owner fixed database role can execute sp_droparticle.

See Also

Delete an Article
Add Articles to and Drop Articles from Existing Publications
sp_addarticle (Transact-SQL)
sp_changearticle (Transact-SQL)
sp_helparticle (Transact-SQL)
sp_helparticlecolumns (Transact-SQL)
Replication Stored Procedures (Transact-SQL)