How to: View and Modify Publication Properties (Replication Transact-SQL Programming)
Publications can be modified and their properties returned programmatically using replication stored procedures. The stored procedures that you use will depend on the type of publication.
To view the properties of a snapshot or transactional publication
- 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.
To change the properties of a snapshot or transactional publication
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.
To view the properties of a merge publication
- 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.
To change the properties of a merge publication
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.
To view the properties of a snapshot
- Execute sp_helppublication_snapshot, specifying the name of the publication for the @publication parameter.
To change the properties of a snapshot
- Execute sp_changepublication_snapshot, specifying one or more of the new snapshot properties for the appropriate snapshot parameters.
Example
This transactional replication example returns the properties of the publication.
DECLARE @myTranPub AS sysname
SET @myTranPub = N'AdvWorksProductTran'
USE [AdventureWorks2008R2]
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 [AdventureWorks2008R2]
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 [AdventureWorks2008R2]
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 [AdventureWorks2008R2]
EXEC sp_changemergepublication
@publication = @publication,
@property = N'replicate_ddl',
@value = 0,
@force_invalidate_snapshot = 0,
@force_reinit_subscription = 0;
GO