sp_reinitsubscription
Marks the subscription for reinitialization. This stored procedure is executed at the Publisher for push subscriptions.
Syntax
sp_reinitsubscription [ [ @publication = ] 'publication' ]
[ , [ @article = ] 'article' ]
, [ @subscriber = ] 'subscriber'
[ , [ @destination_db = ] 'destination_db']
[ , [ @for_schema_change = ] 'for_schema_change']
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 will be reactived as long as the whole publication, and not just some of its articles, are reinitialized. This means that the reinitialization is being called as a result of schema changes. If 1, active subscriptions will not be reactivated until the Snapshot Agent runs.
Return Code Values
0 (success) or 1 (failure)
Remarks
sp_reinitsubscription is used in 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 will then be 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).
For subscriptions where the initial snapshot is applied manually, it is up to the user to make sure that the tables at the Subscriber are in synchronization with those at the Publisher and that there are no undelivered replication commands for the Subscriber pending before executing this stored procedure.
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 will be 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 precreation command on the article is 'drop', articles for schema-bound views and schema-bound stored procedures on the base object of that article will be 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 will be 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.