sp_reinitsubscription (Transact-SQL)

Marks the subscription for reinitialization. This stored procedure is executed at the Publisher for push subscriptions.

Transact-SQL Syntax Conventions

Syntax

sp_reinitsubscription [ [ @publication = ] 'publication' ]
    [ , [ @article = ] 'article' ]
        , [ @subscriber = ] 'subscriber'
    [ , [ @destination_db = ] 'destination_db']
    [ , [ @for_schema_change = ] 'for_schema_change']
    [ , [ @publisher = ] 'publisher' ]
    [ , [ @ignore_distributor_failure = ] ignore_distributor_failure ] 
    [ , [ @invalidate_snapshot = ] invalidate_snapshot ]

Arguments

• [ @publication=] 'publication'
  Is the name of the publication. publication is sysname, with a default of all.

• [ @article=] 'article'
  Is the name of the article. article is sysname, with a default of all. For an immediate-updating publication, article must be all; otherwise, the stored procedure skips the publication and reports an error.

• [ @subscriber=] 'subscriber'
  Is the name of the Subscriber. subscriber is sysname, with no default.

• [ @destination_db=] 'destination_db'
  Is the name of the destination database. destination_db is sysname, with a default of all.

• [ @for_schema_change=] 'for_schema_change'
  Indicates whether reinitialization occurs as a result of a schema change at the publication database. for_schema_change is bit, with a default of 0. If 0, active subscriptions for publications that allow immediate updating are reactivated as long as the whole publication, and not just some of its articles, are reinitialized. This means that the reinitialization is being initiated as a result of schema changes. If 1, active subscriptions are not reactivated until the Snapshot Agent runs.

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

  Note

  publisher should not be used for SQL Server Publishers.

• [ @ignore_distributor_failure= ] ignore_distributor_failure
  Allows reinitialization even if the Distributor does not exist or is offline. ignore_distributor_failure is bit, with a default of 0. If 0, reinitialization fails if the Distributor does not exist or is offline.

• [ @invalidate_snapshot= ] invalidate_snapshot
  Invalidates the existing publication snapshot. invalidate_snapshot is bit, with a default of 0. If 1, a new snapshot is generated for the publication.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_reinitsubscription is used in transactional replication.

sp_reinitsubscription is not supported for peer-to-peer transactional replication.

For subscriptions where the initial snapshot is applied automatically and where the publication does not allow updatable subscriptions, the Snapshot Agent must be run after this stored procedure is executed so that schema and bulk copy program files are prepared and the Distribution Agents is then able to resynchronize the subscriptions.

For subscriptions where the initial snapshot is applied automatically and the publication allows updatable subscriptions, the Distribution Agent resynchronizes the subscription using the most recent schema and bulk copy program files previously created by the Snapshot Agent. The Distribution Agent resynchronizes the subscription immediately after the user executes sp_reinitsubscription, if the Distribution Agent is not busy; otherwise, synchronization may occur after the message interval (specified by Distribution Agent command-prompt parameter: MessageInterval).

sp_reinitsubscription has no effect on subscriptions where the initial snapshot is applied manually.

To resynchronize anonymous subscriptions to a publication, pass in all or NULL as subscriber.

Transactional replication supports subscription reinitialization at the article level. The snapshot of the article is reapplied at the Subscriber during the next synchronization after the article is marked for reinitialization. However, if there are dependent articles that are also subscribed to by the same Subscriber, reapplying the snapshot on the article might fail unless dependent articles in the publication are also automatically reinitialized under certain circumstances:

• If the pre-creation command on the article is 'drop', articles for schema-bound views and schema-bound stored procedures on the base object of that article is marked for reinitialization as well.
• If the schema option on the article includes scripting of declared referential integrity on the primary keys, articles that have base tables with foreign key relationships to base tables of the reinitialized article are marked for reinitialization as well.

Permissions

Only members of the sysadmin fixed server role, members of the db_owner fixed database role, or the creator of the subscription can execute sp_reinitsubscription.

Example

-- This script uses sqlcmd scripting variables. They are in the form
-- $(MyVariable). For information about how to use scripting variables  
-- on the command line and in SQL Server Management Studio, see the 
-- "Executing Replication Scripts" section in the topic
-- "Programming Replication Using System Stored Procedures".

DECLARE @subscriptionDB AS sysname;
DECLARE @publication AS sysname;
SET @subscriptionDB = N'AdventureWorksReplica';
SET @publication = N'AdvWorksProductTran';

USE [AdventureWorksReplica]

-- Execute at the Publisher to reinitialize the push subscription.
EXEC sp_reinitsubscription 
    @subscriber = $(SubServer),
    @destination_db = @subscriptionDB,
    @publication = @publication;
GO

-- Start the Distribution Agent.

See Also

Reference

Replication Stored Procedures (Transact-SQL)

Other Resources

How to: Reinitialize a Subscription (Replication Transact-SQL Programming)
Reinitializing a Subscription

Help and Information

Getting SQL Server 2005 Assistance