Change Publication and Article Properties

Artykuł
02/13/2015

After a publication has been created, most publication and article properties can be changed, but some require that the snapshot be regenerated and/or subscriptions be reinitialized. This topic provides information about all properties that require one or both of these actions if they are changed.

Publication Properties for Snapshot and Transactional Replication

Description	Stored procedure	Properties	Requirements
Change snapshot format.	sp_changepublication	sync_method	New snapshot.
Change snapshot location.	sp_changepublication	alt_snapshot_folder snapshot_in_defaultfolder	New snapshot.
Change snapshot location.	sp_changedistpublisher	working_directory	New snapshot.
Change snapshot compression.	sp_changepublication	compress_snapshot	New snapshot.
Change any File Transfer Protocol (FTP) snapshot options.	sp_changepublication	enabled_for_internet ftp_address ftp_login ftp_password ftp_port ftp_subdirectory	New snapshot.
Change pre- or post-snapshot script location.	sp_changepublication	pre_snapshot_script post_snapshot_script	New snapshot (also required if you change the script contents). Reinitialization is required to apply the new script to the Subscriber.
Enable or disable support for non-Microsoft SQL Server Subscribers.	sp_changepublication	is_enabled_for_het_sub	New snapshot.
Change conflict reporting for queued updating subscriptions	sp_changepublication	centralized_conflicts	Can only be changed if there are no active subscriptions.
Change conflict resolution policy for queued updating subscriptions.	sp_changepublication	conflict_policy	Can only be changed if there are no active subscriptions.

Article Properties for Snapshot and Transactional Replication

Description	Stored procedure	Properties	Requirements
Drop an article	sp_droparticle	All parameters.	Articles can be dropped prior to subscriptions being created. Using stored procedures, it is possible to drop a subscription to an article; using SQL Server Management Studio, the entire subscription must be dropped, recreated, and synchronized. For more information, see Add Articles to and Drop Articles from Existing Publications.
Change a column filter.	sp_articlecolumn	@column @operation	New snapshot. Reinitialize subscriptions.
Add a row filter.	sp_articlefilter	All parameters.	New snapshot. Reinitialize subscriptions.
Drop a row filter.	sp_articlefilter	@article	New snapshot. Reinitialize subscriptions.
Change a row filter.	sp_articlefilter	@filter_clause	New snapshot. Reinitialize subscriptions.
Change a row filter.	sp_changearticle	filter	New snapshot. Reinitialize subscriptions.
Change schema options.	sp_changearticle	schema_option	New snapshot.
Change how tables at the Subscriber are handled prior to applying the snapshot.	sp_changearticle	pre_creation_cmd	New snapshot.
Change article status	sp_changearticle	status	New snapshot.
Change INSERT, UPDATE or DELETE commands.	sp_changearticle	ins_cmd upd_cmd del_cmd	New snapshot. Reinitialize subscriptions.
Change destination table name	sp_changearticle	dest_table	New snapshot. Reinitialize subscriptions.
Change destination table owner (schema).	sp_changearticle	destination_owner	New snapshot. Reinitialize subscriptions.
Change data type mappings (applies to Oracle publishing only).	sp_changearticlecolumndatatype	@type @length @precision @scale	New snapshot. Reinitialize subscriptions.

Publication Properties for Merge Replication

Description	Stored procedure	Properties	Requirements
Change snapshot format	sp_changemergepublication	sync_mode	New snapshot.
Change snapshot location.	sp_changemergepublication	alt_snapshot_folder snapshot_in_defaultfolder	New snapshot.
Change snapshot location.	sp_changedistpublisher	working_directory	New snapshot.
Change snapshot compression	sp_changemergepublication	compress_snapshot	New snapshot.
Change any FTP snapshot options	sp_changemergepublication	enabled_for_internet ftp_address ftp_login ftp_password ftp_port ftp_subdirectory	New snapshot.
Change pre- or post-snapshot scripts.	sp_changemergepublication	pre_snapshot_script post_snapshot_script	New snapshot (also required if you change the script contents). Reinitialization is required to apply the new script to the Subscriber.
Add a join filter or logical record.	sp_addmergefilter	All parameters.	New snapshot. Reinitialize subscriptions.
Drop a join filter or logical record.	sp_dropmergefilter	All parameters.	New snapshot. Reinitialize subscriptions.
Change a join filter or logical record.	sp_changemergefilter	@property @value	New snapshot Reinitialize subscriptions.
Disable the use of parameterized filters (enabling parameterized filters does not require any special actions).	sp_changemergepublication	A value of false for dynamic_filters	New snapshot. Reinitialize subscriptions.
Enable or disable the use of precomputed partitions.	sp_changemergepublication	use_partition_groups	New snapshot.
Enable or disable Microsoft SQL Server 2000 partition optimization.	sp_changemergepublication	keep_partition_changes	Reinitialize subscriptions.
Enable or disable Subscriber partition validation.	sp_changemergepublication	validate_subscriber_info	Reinitialize subscriptions.
Change the publication compatibility level to 80sp3 or lower.	sp_changemergepublication	publication_compatibility_level	New snapshot.

Article Properties for Merge Replication

Description	Stored Procedure	Properties	Requirements
Drop an article, where the article has the last parameterized filter in the publication.	sp_dropmergearticle	All parameters	New snapshot. Reinitialize subscriptions.
Drop an article, where the article is a parent in a join filter or logical record (this has the side effect of dropping the join).	sp_dropmergearticle	All parameters	New snapshot. Reinitialize subscriptions.
Drop an article, all other circumstances.	sp_dropmergearticle	All parameters	New snapshot.
Include a column filter that was previously unpublished.	sp_mergearticlecolumn	@column @operation	New snapshot. Reinitialize subscriptions.
Add, drop, or change a row filter.	sp_changemergearticle	subset_filterclause	New snapshot. Reinitialize subscriptions. If you add, drop, or change a parameterized filter, pending changes at the Subscriber cannot be uploaded to the Publisher during reinitialization. If you want to upload pending changes, synchronize all subscriptions before changing the filter. If an article is not involved in any join filters, you can drop the article and add it again with a different row filter, which does not require the entire subscription to be reinitialized. For more information about adding and dropping articles, see Add Articles to and Drop Articles from Existing Publications.
Change schema options.	sp_changemergearticle	schema_option	New snapshot.
Change tracking from column-level to row-level (changing from row-level tracking to column-level tracking does not require any special actions).	sp_changemergearticle	A value of false for column_tracking	New snapshot. Reinitialize subscriptions.
Change whether permissions are checked before statements made at the Subscriber are applied at the Publisher.	sp_changemergearticle	check_permissions	New snapshot. Reinitialize subscriptions.
Enable or disable download-only subscriptions (changing to or from other upload options does not require any special actions).	sp_changemergearticle	Change to or from a value of 2 for subscriber_upload_options	Reinitialize subscriptions.
Change destination table owner.	sp_changemergearticle	destination_owner	New snapshot. Reinitialize subscriptions.