drop subscription

Drops a subscription to a database replication definition, table replication definition, function replication definition, article, or publication.

Syntax

drop subscription sub_name
for {table_rep_def | function_rep_def |
{article article_name in pub_name |
    publication pub_name | database replication definition db_repdef 
       with primary at data_server.database}
 with replicate at data_server.database
[without purge [with suspension
     [at active replicate only]] |
   [incrementally] with purge]

Parameters

Examples

Usage

  • When you drop a subscription, Replication Server stops replicating the data specified by the subscription.

  • Execute drop subscription at the Replication Server where you created the subscription.

  • You cannot drop a table replication definition, function replication definition, article, or publication until you have dropped all subscriptions for the object.

The without purge Clause

  • Use without purge to drop a subscription to a table or database replication definition or to a publication. Replicated rows remain in the replicate tables.

  • When you drop a subscription to a table replication definition or publication, you must specify either without purge or with purge.

  • When you drop a subscription to a function replication definition, it is always dropped “without purge”—you do not need to specify without purge.

  • When you drop a publication subscription “without purge,” all of its article subscriptions are dropped together.

The with purge Clause

  • Use the with purge clause to delete the rows (in the replicate table) that were replicated by the subscription. All subscription rows are purged unless they belong to another subscription at the replicate site.

  • When you use with purge, Replication Server selects from the replicate database, the set of rows that could be deleted. It then evaluates the selected rows against other subscriptions and determines whether to delete the row. The maintenance user for the replicate database must have select permission on the table.

  • Deletes using with purge occur in a single transaction performed by an rs_select_with_lock function string in the replicate database.

  • Deletes using with purge and incrementally occur 1000 rows at a time. This operation is performed by an rs_select function string in the replicate database.

  • When you drop a publication subscription “with purge,” its article subscriptions are dropped one at a time in the reverse order that the articles were added to the publication.

Permissions

drop subscription requires “create object” permission at the replicate site and “primary subscribe” permission at the primary Replication Server.

drop subscription ... with purge also requires that the maintenance user have select permission for the replicate table.

Related reference
check subscription
create subscription
define subscription
drop article
drop function replication definition
drop publication
drop replication definition
resume connection
rs_select
rs_select_with_lock