Changing Publication and Article Properties

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 Adding Articles to and Dropping 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 Adding Articles to and Dropping 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.