Changes the properties of a publication. This stored procedure is executed at the Publisher on the publication database.
sp_changepublication [ [ @publication = ] 'publication' ] [ , [ @property = ] 'property' ] [ , [ @value = ] 'value' ] [ , [ @force_invalidate_snapshot = ] force_invalidate_snapshot ] [ , [ @force_reinit_subscription = ] force_reinit_subscription ] [ , [ @publisher = ] 'publisher' ]
- [ @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.
[ @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.
publisher should not be used when changing article properties on a SQL Server Publisher.
Return Code Values
0 (success) or 1 (failure)
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.
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.
Only members of the sysadmin fixed server role or db_owner fixed database role can execute sp_changepublication.
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
Help and Information
12 December 2006