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 |
| 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. 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. 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. |
| 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
Only members of the sysadmin fixed server role or db_owner fixed database role can execute sp_changepublication.