sp_changepublication (T-SQL)

Changes a publication’s properties.

Syntax

sp_changepublication [[@publication =] 'publication']
    [,[@property =] 'property'] [,[@value =] 'value']

Arguments

[@publication =] 'publication'

Is the name of the publication. publication is sysname, with a default of NULL.

[@property =] 'property'

Is the publication property to change. property is nvarchar(20), and can be one of these values.

 

Value	Description
description	Optional entry describing the publication.
taskid	Unique scheduler task ID created using sp_addtask. This is for backward compatibility only.
sync_method	Synchronization method. Can be native (produces native-mode bulk copy output of all tables) or character (produces a character-mode bulk copy output of all tables).
name	Name of the publication.
immediate_sync	Whether the synchronization files for the publication are created each time the Snapshot Agent runs. 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.
independent_agent	Whether there is a stand-alone Distribution Agent for this publication. If true, there is a stand-alone Distribution Agent for this publication. If false, the the publication uses a shared Distribution Agent, and each Publisher database/Subscriber database pair has a shared Agent.
enabled_for_internet	Whether the publication is enabled for the Internet. If true, the synchronization files for the publication are put into the \Repldata\Ftp directory.
allow_push	Whether push subscriptions can be created for the given publication. If true, push subscriptions are allowed on the publication.
allow_pull	Whether pull subscriptions can be created for the given publication. If true, pull subscriptions are allowed on the publication.
allow_anonymous	Whether anonymous subscriptions can be created for the given publication. If true, immediate_sync must also be set to true. If true, anonymous subscriptions are allowed on the publication.
retention	Retention period in days for subscription activity. If a subscription is not active within the retention period, it is removed
status	Publication status. Can be inactive (publication data will not be available for Subscribers when the publication is first created) or active (publication data is available immediately for Subscribers).
repl_freq	Frequency of replication. Can be continuous (provides output of all log-based transactions) or snapshot (produces only scheduled synchronization events).
NULL (default)

[@value =] 'value'

Is the new property value. value is nvarchar(255), with a default of NULL.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_changepublication is used in snapshot and transactional replication.

Permissions

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

See Also