sp_addpublication (T-SQL)

Creates a snapshot or transactional or publication.

Syntax

sp_addpublication [@publication =] 'publication' [,[@taskid =] taskid]
    [,[@restricted =] 'restricted'] [,[@sync_method =] 'sync_method']
    [,[@repl_freq =] 'repl_freq'] [,[@description =] 'description']
    [,[@status =] 'status'] [,[@independent_agent =] 'independent_agent']
    [,[@immediate_sync =] 'immediate_sync']
    [,[@enabled_for_internet =] 'enabled_for_internet']
    [,[@allow_push =] 'allow_push', [,[@allow_pull =] 'allow_pull']
    [,[@allow_anonymous =] 'allow_anonymous']
    [,[@allow_sync_tran =] 'allow_sync_tran']
    [,[@autogen_sync_procs =] 'autogen_sync_procs']
    [,[@retention =] retention]

Arguments

[@publication =] 'publication'

Is the name of the publication to create. publication is sysname, with no default. The name must be unique within the database.

[@taskid =] taskid

Is a unique scheduler task ID (must be created before the publication). task_id is int, with a default of 0. Do not use; this parameter is provided for backward compatibility only. Use sp_addpublication_snapshot.

[@restricted =] 'restricted'

Supported for backward compatibility only; use default_access instead.

[@sync_method =] 'sync_method'

Is the synchronization mode. sync_method is nvarchar(13), and can be one of these values.

 

Value	Description
native (default)	Produces native-mode bulk copy program output of all tables.
character	Produces character-mode bulk copy program output of all tables.

[@repl_freq =] 'repl_freq'

Is the type of replication frequency. replication_frequency is nvarchar(10), with a default of continuous. If continuous, the Publisher provides output of all log-based transactions. If Snapshot, the Publisher produces only scheduled synchronization events.

[@description =] 'description'

Is an optional description for the publication. description is nvarchar(255), with a default of NULL.

[@status =] 'status'

Is when publication data is available. status is nvarchar(8), and can be one of these values.

 

Value	Description
active	Publication data is immediately available for Subscribers.
inactive (default)	Publication data is not available for Subscribers when the publication is first created (they can subscribe, but the subscriptions are not processed).

[@independent_agent =] 'independent_agent'

Is whether there is a stand-alone Distribution Agent for this publication. independent_agent is nvarchar(5), with a default of FALSE. If true, there is a stand-alone Distribution Agent for this publication. If false, the publication uses a shared Distribution Agent, and each Publisher database/Subscriber database pair has a shared Agent.

[@immediate_sync =] 'immediate_sync'

Is whether the synchronization files for the publication are created each time the Snapshot Agent runs. immediate_sync is nvarchar(5), with a default of FALSE. If true, the synchronization files are created or re-created each time the Snapshot Agent runs. Subscribers are able to get the synchronization files immediately after the subscription if the Snapshot Agent has been completed once before the subscription. New subscriptions get the newest synchronization files generated by the most recent execution of the Snapshot Agent. independent_agent must be true for immediate_sync to be true. If false, the synchronization files are created only if there are new subscriptions. Subscribers cannot receive the synchronization files after the subscription until the Snapshot Agents are started and completed.

[@enabled_for_internet =] 'enabled_for_internet'

Is whether the publication is enabled for the Internet. enabled_for_internet is nvarchar(5), with a default of FALSE. If true, the synchronization files for the publication are put into the \Repldata\Ftp directory. The user must set up the Ftp directory.

[@allow_push =] 'allow_push'

Is whether push subscriptions can be created for the given publication. allow_push is nvarchar(5), with a default of TRUE, which allows push subscriptions on the publication.

[@allow_pull =] 'allow_pull'

Is whether pull subscriptions can be created for the given publication. allow_pull is nvarchar(5), with a default of FALSE. If false, pull subscriptions are not allowed on the publication.

[@allow_anonymous =] 'allow_anonymous'

Is whether anonymous subscriptions can be created for the given publication. allow_anonymous is nvarchar(5), with a default of FALSE. If true, immediate_sync must also be set to true. If false, anonymous subscriptions are not allowed on the publication.

[@allow_sync_tran =] 'allow_sync_tran'

Is whether immediate-updating subscriptions are allowed on the publication. allow_sync_tran is nvarchar(5), with a default of FALSE.

[@autogen_sync_procs =] 'autogen_sync_procs'

Is whether the synchronizing stored procedure for immediate-updating subscriptions is generated at the Publisher. autogen_sync_procs is nvarchar(5), with a default of TRUE.

[@retention =] retention]

Is the retention period in hours for subscription activity. retention is int, with a default of 72 hours. If a subscription is not active within the retention period, it expires and is removed. The value must be less than or equal to the maximum retention period of the distribution database used by the Publisher. If 0, the default value is used.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_addpublication is used in snapshot, transactional, and merge replication.

Permissions

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

See Also