Transact-SQL Reference

sp_changepublication

Changes the properties of a publication. This stored procedure is executed at the Publisher on the publication database.

Syntax

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

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
taskid	Unique scheduler task ID created using sp_addtask. For backward compatibility only.
sync_method	Synchronization method. Can be: native = produces native-mode bulk copy output of all tables character = produces a character-mode bulk copy output of all tables concurrent = produces native-mode bulk copy program output of all tables but does not lock tables during the snapshot concurrent_c = produces character-mode bulk copy program output of all tables but does not lock tables during the snapshot. Note that the values concurrent and concurrent_c are available for transactional and merge replication, but not snapshot replication.
repl_freq	Frequency of replication. Can be continuous (provides output of all log-based transactions) or snapshot (produces only scheduled synchronization events).
description	Optional entry describing the publication.
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).
independent_agent	Specifies if 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 publication uses a shared Distribution Agent, and each Publisher database/Subscriber database pair has a shared agent.
immediate_sync	Specifies if 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 receive 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 Agent is started and completes.
enabled_for_internet	Specifies if the publication is enabled for the Internet. If true, the synchronization files for the publication are put into the \Repldata\Ftp directory.
allow_push	Specifies if push subscriptions can be created for the given publication. If true, push subscriptions are allowed on the publication.
allow_pull	Specifies if pull subscriptions can be created for the given publication. If true, pull subscriptions are allowed on the publication.
allow_anonymous	Specifies if 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 hours for subscription activity. If a subscription is not active within the retention period, it is removed.
snapshot_in_ defaultfolder	Specifies if snapshot files are stored in the default folder. snapshot_in_defaultfolder is nvarchar(5). If true, snapshot files can be found in the default folder. If false, snapshot files have been stored in the alternate location specified by alt_snapshot_folder. Alternate locations can be on another server, on a network drive, or on removable media (such as CD-ROM or removable disks). You can also save the snapshot files to a File Transfer Protocol (FTP) site, for retrieval by the Subscriber at a later time. Note that this parameter can be true and still have a location in the @alt_snapshot_folder parameter. This combination specifies that the snapshot files will be stored in both the default and alternate locations.
alt_snapshot_folder	Specifies the location of the alternate folder for the snapshot. alternate_snapshot_folder is nvarchar(255).
pre_snapshot_script	Specifies a pointer to an .sql file location. pre_snapshot_script is nvarchar(255). The Distribution Agent will run the pre-snapshot script before running any of the replicated object scripts when applying a snapshot at a Subscriber.
post_snapshot_script	Specifies a pointer to an .sql file location. post_snapshot_script isnvarchar(255). The Distribution Agent will run the post-snapshot script after all the other replicated object scripts and data have been applied during an initial synchronization.
compress_snapshot	Specifies that the snapshot that is written to the @alt_snapshot_folder location is to be compressed into the Microsoft® CAB format. compress_snapshot is nvarchar(5). false specifies that the snapshot will not be compressed; true specifies that the snapshot will be compressed. The snapshot in the default folder cannot be compressed.
ftp_address	Is the network address of the FTP service for the Distributor. ftp_address is sysname. Specifies where publication snapshot files are located for the Distribution Agent or Merge Agent of a subscriber to pick up. Because this property is stored for each publication, each publication can have a different ftp_address. The publication must support propagating snapshots using FTP. For more information, see Configuring a Publication to Allow Subscribers to Retrieve Snapshots Using FTP.
ftp_port	Is the port number of the FTP service for the Distributor. ftp_port is int. The default is 21. Specifies where the publication snapshot files are located for the Distribution Agent or Merge Agent of a subscriber to pick up. Because this property is stored for each publication, each publication can have its own ftp_port.
ftp_subdirectory	Specifies where the snapshot files will be available for the Distribution Agent or Merge Agent of the Subscriber to pick up if the publication supports propagating snapshots using FTP. ftp_subdirectory is nvarchar(255). Because this property is stored for each publication, each publication can have its own ftp_subdirctory or choose to have no subdirectory, indicated with a NULL value.
ftp_login	Is the user name used to connect to the FTP service. ftp_login is sysname. The value ANONYMOUS is allowed.
ftp_password	Is the user password used to connect to the FTP service. ftp_password is sysname.
conflict_policy	Specifies the conflict resolution policy followed when the queued updating subscriber option is used. conflict_policy is nvarchar(100), and can be one of these values: pub wins = Publisher wins the conflict. sub reinit = Reinitialize the subscription. sub wins = Subscriber wins the conflict. NULL = If NULL, and the publication is a snapshot publication, the default policy becomes sub reinit. If NULL and the publication is not a snapshot publication, the default becomes pub wins. This property can be changed only if there are no active subscriptions.
centralized_conflicts	Specifies if conflict records are stored on the Publisher. centralized_conflicts is nvarchar(5). If true, conflict records are stored at the Publisher. If false, conflict records are stored at both the publisher and at the subscriber that caused the conflict. This property can be changed only if there are no active subscriptions.
conflict_retention	Specifies the conflict retention period, in days. conflict_retention is int. The default retention is usually 14 days.
queue_type	Specifies which type of queue is used. queue_type is nvarchar(10), and can be one of these values: msmq = Use Microsoft Message Queuing to store transactions. sql = Use SQL Server to store transactions. NULL = Defaults to sql, which specifies to use SQL Server to store transactions. This property can be changed only if there are no active subscriptions.
publish_to_ ActiveDirectory	Specifies if the publication information is published to the Microsoft Active Directory™. add_to_active_directory is nvarchar(10) This feature is available only for servers running the Microsoft Windows® 2000 operating system. Valid values are: true = publication information is published. false = publication information is not published.
NULL (default)

[@value =] 'value'

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

[@force_invalidate_snapshot = ] force_invalidate_snapshot

Acknowledges that the action taken by this stored procedure may invalidate an existing snapshot. force_invalidate_snapshot is a bit, with a default of 0. 0 specifies that changes to the article will not cause the snapshot to be invalid. If the stored procedure detects that the change does require a new snapshot, an error will occur and no changes will be made. 1 specifies that changes to the article may cause the snapshot to be invalid, and if there are existing subscriptions that would require a new snapshot, gives permission for the existing snapshot to be marked as obsolete and a new snapshot generated.

[@force_reinit_subscription = ] force_reinit_subscription

Acknowledges that the action taken by this stored procedure may require existing subscriptions to be reinitialized. force_reinit_subscription is a bit with a default of 0. 0 specifies that changes to the article will not cause the subscription to be reinitialized. If the stored procedure detects that the change would require existing subscriptions to be reinitialized, an error will occur and no changes will be made. 1 specifies that changes to the article will cause the existing subscription to be reinitialized, and gives permission for the subscription reinitialization to occur.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_changepublication is used in snapshot replication and transactional replication.

To list publication objects in the Active Directory using the @publish_to_Active_Directory parameter, the SQL Server object must already be created in the Active Directory. For more information, see Active Directory Services.

Permissions