Export (0) Print
Expand All

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.

  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.

    ms147364.note(en-US,SQL.90).gifNote:
    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.

    ms147364.note(en-US,SQL.90).gifNote:
    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

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft