sp_subscribe Replication Stored Procedure

Remotely adds a subscription to a particular article within a publication.

Syntax

sp_subscribe publication, [article [, destination_db
[, @sync_type = {'automatic' | 'manual' | 'none'}]]]

where

publication

Is the name of the publication.

article

Is the name of the article. The name must be unique within the publication. If article is not supplied or if set to all, a subscription is added to all articles in the publication.

destination_db

Is the name of the destination (subscription) database in which to place replicated data. The default is NULL (indicating the same database name as the publication database).

@sync_type

Specifies the synchronization type. If set to automatic (the default), the subscription synchronization job is passed to the distribution database. If set to manual, the synchronization holder is passed to the distribution database and must be manually reset by the subscriber.

If set to none, there is no synchronization. A synchronization file is not created, there is no synchronization job passed to the distribution database, and the subscriber's status is set to active instead of subscribed. All future transactions are sent to the subscriber.

Remarks

This stored procedure causes rows to be added to the syssubscriptions table only if those rows indicate that the publication was unrestricted. If the publication was restricted, a valid entry must already exist in syssubscriptions. Running sp_subscribe causes the status for this entry to be changed from inactive to subscribed.

Example

This example adds remote subscription to the types_table article in the pub_alltype publication for the mydb database (on the subscription server) and sets the synchronization type to manual.

sp_subscribe pub_alltypes, types_table, mydb, 'manual'

Permission

Execute permission defaults to the system administrator and database owner and is also granted to repl_subscriber.

Tables Used

sysarticles, syspublications, syssubscriptions, sysservers

See Also