sp_changepublication (Transact-SQL)

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

Transact-SQL Syntax Conventions

Syntax

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

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(255).

• [ @value = ] 'value'
  Is the new property value. value is nvarchar(255), with a default of NULL.

  This table describes the properties of the publication that can be changed and restrictions on the values for those properties.

  Property

• [ @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 do not cause the snapshot to be invalid. If the stored procedure detects that the change does require a new snapshot, an error occurs and no changes are made.

  1 specifies that changes to the article may cause the snapshot to be invalid. If there are existing subscriptions that would require a new snapshot, this value gives permission for the existing snapshot to be marked as obsolete and a new snapshot generated.

  See the Remarks section for the properties that, when changed, require the generation of a new snapshot.

• [@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 do not cause the subscription to be reinitialized. If the stored procedure detects that the change would require existing subscriptions to be reinitialized, an error occurs and no changes are made.

  1 specifies that changes to the article cause the existing subscription to be reinitialized, and gives permission for the subscription reinitialization to occur.

• [ @publisher = ] 'publisher'
  Specifies a non-SQL Server Publisher. publisher is sysname, with a default of NULL.

  Note

  publisher should not be used when changing article properties on a SQL Server Publisher.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_changepublication is used in snapshot replication and transactional replication.

Changing any of the following properties requires that a new snapshot be generated, and you must specify a value of 1 for the force_invalidate_snapshot parameter.

• alt_snapshot_folder
• compress_snapshot
• enabled_for_het_sub
• ftp_address
• ftp_login
• ftp_password
• ftp_port
• ftp_subdirectory
• post_snapshot_script
• pre_snapshot_script
• snapshot_in_defaultfolder
• sync_mode

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.

Permissions

Only members of the sysadmin fixed server role or db_owner fixed database role can execute sp_changepublication.

Example

DECLARE @publication AS sysname
SET @publication = N'AdvWorksProductTran' 

-- Turn off DDL replication for the transactional publication.
USE [AdventureWorks]
EXEC sp_changepublication 
  @publication = @publication, 
  @property = N'replicate_ddl', 
  @value = 0
GO

See Also

Reference

sp_addpublication (Transact-SQL)
sp_droppublication (Transact-SQL)
sp_helppublication (Transact-SQL)
Replication Stored Procedures (Transact-SQL)

Other Resources

How to: View and Modify Publication Properties (Replication Transact-SQL Programming)
Changing Publication and Article Properties

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
12 December 2006	New content: Added information about new values for the sync_method property.