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

1. Execute sp_helppublication, specifying the name of the publication for the @publication parameter. If you do not specify this parameter, information on all publications at the Publisher is returned.

1. Execute sp_changepublication, specifying the publication property to change in the @property parameter and the new value of this property in the @value parameter.

   Note:

   If the change will require the generation of a new snapshot, you must also specify a value of 1 for @force_invalidate_snapshot, and if the change will require that Subscribers be reinitialized, you must specify a value of 1 for @force_reinit_subscription. For more information on the properties that, when changed, require a new snapshot or reinitialization, see Changing Publication and Article Properties.

1. Execute sp_helpmergepublication, specifying the name of the publication for the @publication parameter. If you do not specify this parameter, information on all publications at the Publisher is returned.

1. Execute sp_changemergepublication, specifying the publication property being changed in the @property parameter and the new value of this property in the @value parameter.

   Note:

   If the change will require the generation of a new snapshot, you must also specify a value of 1 for @force_invalidate_snapshot, and if the change will require that Subscribers be reinitialized, you must specify a value of 1 for @force_reinit_subscription For more information on the properties that, when changed, require a new snapshot or reinitialization, see Changing Publication and Article Properties.

1. Execute sp_helppublication_snapshot, specifying the name of the publication for the @publication parameter.

1. Execute sp_changepublication_snapshot, specifying one or more of the new snapshot properties for the appropriate snapshot parameters.

This transactional replication example returns the properties of the publication.

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

USE [AdventureWorks]
EXEC sp_helppublication @publication = @myTranPub
GO

This transactional replication example disables schema replication for the publication.

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

This merge replication example returns the properties of the publication.

DECLARE @publication AS sysname;
SET @publication = N'AdvWorksSalesOrdersMerge';

USE [AdventureWorks]
EXEC sp_helpmergepublication @publication = @publication;
GO

This merge replication example disables schema replication for the publication.

DECLARE @publication AS sysname;
SET @publication = N'AdvWorksSalesOrdersMerge'; 

-- Disable DDL replication for the publication.
USE [AdventureWorks]
EXEC sp_changemergepublication 
  @publication = @publication, 
  @property = N'replicate_ddl', 
  @value = 0,
  @force_invalidate_snapshot = 0, 
  @force_reinit_subscription = 0;
GO

Tasks

Concepts

Other Resources

Help and Information