Change 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 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. |
See Also
Reference
sp_addmergefilter (Transact-SQL)
sp_articlecolumn (Transact-SQL)
sp_articlefilter (Transact-SQL)
sp_changearticle (Transact-SQL)
sp_changearticlecolumndatatype (Transact-SQL)
sp_changedistpublisher (Transact-SQL)
sp_changemergearticle (Transact-SQL)
sp_changemergefilter (Transact-SQL)
sp_changemergepublication (Transact-SQL)
sp_changepublication (Transact-SQL)
sp_dropmergearticle (Transact-SQL)
sp_dropmergefilter (Transact-SQL)
sp_mergearticlecolumn (Transact-SQL)