Skip to content

Latest commit

 

History

History
100 lines (67 loc) · 4.35 KB

sp-droparticle-transact-sql.md

File metadata and controls

100 lines (67 loc) · 4.35 KB
Raw
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
sp_droparticle (Transact-SQL)
sp_droparticle drops an article from a snapshot or transactional publication.
markingmyname
maghan
randolphwest
11/28/2023
sql
replication
reference
sp_droparticle_TSQL
sp_droparticle
sp_droparticle
TSQL

sp_droparticle (Transact-SQL)

[!INCLUDE SQL Server SQL MI]

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

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

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

Arguments

[ @publication = ] N'publication'

The name of the publication that contains the article to be dropped. @publication is sysname, with no default.

[ @article = ] N'article'

The name of the article to be dropped. @article is sysname, with no default.

[ @ignore_distributor = ] ignore_distributor

[!INCLUDE ssinternalonly-md]

[ @force_invalidate_snapshot = ] force_invalidate_snapshot

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

  • 0 specifies that changes to the article don't 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 might 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 = ] N'publisher'

Specifies a non-[!INCLUDE ssNoVersion] Publisher. @publisher is sysname, with a default of NULL.

This parameter shouldn't be used when changing article properties on a [!INCLUDE ssNoVersion] Publisher.

[ @from_drop_publication = ] from_drop_publication

[!INCLUDE ssinternalonly-md]

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's dropped with the article. If it was manually created, it isn't dropped.

Executing sp_droparticle to drop an article from a publication doesn't 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.

Examples

:::code language="sql" source="../replication/codesnippet/tsql/sp-droparticle-transact-_1.sql":::

Permissions

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

Related content