This topic has not yet been rated - Rate this topic

sp_droparticle (Transact-SQL)

SQL Server 2012

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.

Topic link icon 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

TSQL

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.

Reference

sp_addarticle (Transact-SQL)

sp_changearticle (Transact-SQL)

sp_helparticle (Transact-SQL)

sp_helparticlecolumns (Transact-SQL)

Replication Stored Procedures (Transact-SQL)

Concepts

Delete an Article

Add Articles to and Drop Articles from Existing Publications

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD