Muokkaa

Jaa

View and Modify Publication Properties

  • Artikkeli

Applies to: SQL Server Azure SQL Managed Instance

This topic describes how to view and modify publication properties in SQL Server by using SQL Server Management Studio, Transact-SQL, or Replication Management Objects (RMO).

In This Topic

Before You Begin

Limitations and Restrictions

  • Some properties cannot be modified after a publication has been created, and others cannot be modified if there are subscriptions to the publication. Properties that cannot be modified are displayed as read-only.

Recommendations

Using SQL Server Management Studio

View and modify publication properties in the Publication Properties - <Publication> dialog box, which is available in SQL Server Management Studio and Replication Monitor. For information about starting Replication Monitor, see Start the Replication Monitor.

The Publication Properties - <Publication> dialog box includes the following pages:

  • The General page includes the publication name and description, the database name, the type of publication, and the subscription expiration settings.

  • The Articles page corresponds to the Articles page in the New Publication Wizard. Use this page to add and delete articles, and to change properties and column filtering for articles.

  • The Filter Rows page corresponds to the Filter Table Rows page in the New Publication Wizard. Use this page to add, edit, and delete static row filters for all types of publications, and to add, edit, and delete parameterized row filters and join filters for merge publications.

  • The Snapshot page allows you to specify the format and location of the snapshot, whether the snapshot should be compressed, and scripts to run before and after the snapshot is applied.

  • The FTP Snapshot page (for snapshot and transactional publications, and merge publications for Publishers running versions prior to SQL Server 2005) allows you to specify whether Subscribers can download snapshot files through File Transfer Protocol (FTP).

  • The FTP Snapshot and Internet page (for merge publications from Publishers running SQL Server 2005 or later) allows you to specify whether Subscribers can download snapshot files through FTP, and whether Subscribers can synchronize subscriptions through HTTPS.

  • The Subscription Options page allows you to set a number of options that apply to all subscriptions. The options differ depending on the type of publication.

  • The Publication Access List page allows you to specify which logins and groups can access a publication.

  • The Agent Security page allows you to access settings for the accounts under which the following agents run and make connections to the computers in a replication topology: the Snapshot Agent for all publications; the Log Reader Agent for all transactional publications; and the Queue Reader Agent for transactional publications that allow queued updating subscriptions.

  • The Data Partitions page (for merge publications from Publishers running SQL Server 2005 or later) allows you to specify whether Subscribers to publications with parameterized filters can request a snapshot if one is not available. It also allows you to generate snapshots for one or more partitions, either once or on a recurring schedule.

To view and modify publication properties in Management Studio

  1. Connect to the Publisher in Management Studio, and then expand the server node.

  2. Expand the Replication folder, and then expand the Local Publications folder.

  3. Right-click a publication, and then click Properties.

  4. Modify any properties if necessary, and then click OK.

To view and modify publication properties in Replication Monitor

  1. Expand a Publisher group in the left pane of Replication Monitor, and then expand a Publisher.

  2. Right-click a publication, and then click Properties.

  3. Modify any properties if necessary, and then click OK.

Using Transact-SQL

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

  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.

To change the properties of a snapshot or transactional publication

  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 Change Publication and Article Properties.

To view the properties of a merge publication

  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.

To change the properties of a merge publication

  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 Change Publication and Article Properties.

To view the properties of a snapshot

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

To change the properties of a snapshot

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

Examples (Transact-SQL)

This transactional replication example returns the properties of the publication.

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

USE [AdventureWorks2022]
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 [AdventureWorks2022]
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 [AdventureWorks2022]
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 [AdventureWorks2022]
EXEC sp_changemergepublication 
  @publication = @publication, 
  @property = N'replicate_ddl', 
  @value = 0,
  @force_invalidate_snapshot = 0, 
  @force_reinit_subscription = 0;
GO

Using Replication Management Objects (RMO)

You can modify publications and access their properties programmatically by using Replication Management Objects (RMO). The RMO classes that you use to view or modify publication properties depend on the type of publication.

To view or modify properties of a snapshot or transactional publication

  1. Create a connection to the Publisher by using the ServerConnection class.

  2. Create an instance of the TransPublication class, set the Name and DatabaseName properties for the publication, and set the ConnectionContext property to the connection created in step 1.

  3. Call the LoadProperties method to get the properties of the object. If this method returns false, either the publication properties in step 2 were defined incorrectly or the publication does not exist.

  4. (Optional) To change properties, set a new value for one or more of the settable properties. Use the logical AND operator (& in Microsoft Visual C# and And in Microsoft Visual Basic) to determine if a given PublicationAttributes value is set for the Attributes property. Use the inclusive logical OR operator (| in Visual C# and Or in Visual Basic) and the exclusive logical OR operator (^ in Visual C# and Xor in Visual Basic) to change the PublicationAttributes values for the Attributes property.

  5. (Optional) If you specified a value of true for CachePropertyChanges, call the CommitPropertyChanges method to commit changes on the server. If you specified a value of false for CachePropertyChanges (the default), changes are sent to the server immediately.

To view or modify properties of a merge publication

  1. Create a connection to the Publisher by using the ServerConnection class.

  2. Create an instance of the MergePublication class, set the Name and DatabaseName properties for the publication, and set the ConnectionContext property to the connection created in step 1.

  3. Call the LoadProperties method to get the properties of the object. If this method returns false, either the publication properties in step 2 were defined incorrectly or the publication does not exist.

  4. (Optional) To change properties, set a new value for one or more of the settable properties. Use the logical AND operator (& in Visual C# and And in Visual Basic) to determine if a given PublicationAttributes value is set for the Attributes property. Use the inclusive logical OR operator (| in Visual C# and Or in Visual Basic) and the exclusive logical OR operator (^ in Visual C# and Xor in Visual Basic) to change the PublicationAttributes values for the Attributes property.

  5. (Optional) If you specified a value of true for CachePropertyChanges, call the CommitPropertyChanges method to commit changes on the server. If you specified a value of false for CachePropertyChanges (the default), changes are sent to the server immediately.

Examples (RMO)

This example sets publication attributes for a transactional publication. The changes are cached until explicitly sent to the server.

// Define the server, database, and publication names
string publisherName = publisherInstance;
string publicationName = "AdvWorksProductTran";
string publicationDbName = "AdventureWorks2022";

TransPublication publication;

// Create a connection to the Publisher.
ServerConnection conn = new ServerConnection(publisherName);

try
{
    // Connect to the Publisher.
    conn.Connect();

    // Set the required properties for the publication.
    publication = new TransPublication();
    publication.ConnectionContext = conn;
    publication.Name = publicationName;
    publication.DatabaseName = publicationDbName;

    // Explicitly enable caching of property changes on this object.
    publication.CachePropertyChanges = true;

    // If we can't get the properties for this publication, 
    // throw an application exception.
    if (publication.LoadProperties())
    {
        // Enable support for push subscriptions and disable support 
        // for pull subscriptions.
        if ((publication.Attributes & PublicationAttributes.AllowPull) != 0)
        {
            publication.Attributes ^= PublicationAttributes.AllowPull;
        }
        if ((publication.Attributes & PublicationAttributes.AllowPush) == 0)
        {
            publication.Attributes |= PublicationAttributes.AllowPush;
        }

        // Send changes to the server.
        publication.CommitPropertyChanges();
    }
    else
    {
        throw new ApplicationException(String.Format(
            "Settings could not be retrieved for the publication. " +
            "Ensure that the publication {0} exists on {1}.",
            publicationName, publisherName));
    }
}
catch (Exception ex)
{
    // Do error handling here.
    throw new ApplicationException(
        "The publication property could not be changed.", ex);
}
finally
{
    conn.Disconnect();
}

' Define the server, database, and publication names
Dim publisherName As String = publisherInstance
Dim publicationName As String = "AdvWorksProductTran"
Dim publicationDbName As String = "AdventureWorks2022"

Dim publication As TransPublication

' Create a connection to the Publisher.
Dim conn As ServerConnection = New ServerConnection(publisherName)

Try
    ' Connect to the Publisher.
    conn.Connect()

    ' Set the required properties for the publication.
    publication = New TransPublication()
    publication.ConnectionContext = conn
    publication.Name = publicationName
    publication.DatabaseName = publicationDbName

    ' Explicitly enable caching of property changes on this object.
    publication.CachePropertyChanges = True

    ' If we can't get the properties for this publication, 
    ' throw an application exception.
    If publication.LoadProperties() Then
        ' Enable support for push subscriptions and disable support 
        ' for pull subscriptions.
        If (publication.Attributes And PublicationAttributes.AllowPull) <> 0 Then
            publication.Attributes = publication.Attributes _
            Xor PublicationAttributes.AllowPull
        End If
        If (publication.Attributes And PublicationAttributes.AllowPush) = 0 Then
            publication.Attributes = publication.Attributes _
            Or PublicationAttributes.AllowPush
        End If

        ' Send changes to the server.
        publication.CommitPropertyChanges()
    Else
        Throw New ApplicationException(String.Format( _
         "Settings could not be retrieved for the publication. " + _
         "Ensure that the publication {0} exists on {1}.", _
         publicationName, publisherName))
    End If
Catch ex As Exception
    ' Do error handling here.
    Throw New ApplicationException( _
        "The publication property could not be changed.", ex)
Finally
    conn.Disconnect()
End Try

This example disables DDL replication for a merge publication.

// Define the server, database, and publication names
string publisherName = publisherInstance;
string publicationName = "AdvWorksSalesOrdersMerge";
string publicationDbName = "AdventureWorks2022";

MergePublication publication;

// Create a connection to the Publisher.
ServerConnection conn = new ServerConnection(publisherName);

try
{
    // Connect to the Publisher.
    conn.Connect();

    // Set the required properties for the publication.
    publication = new MergePublication();
    publication.ConnectionContext = conn;
    publication.Name = publicationName;
    publication.DatabaseName = publicationDbName;


    // If we can't get the properties for this merge publication, then throw an application exception.
    if (publication.LoadProperties())
    {
        // If DDL replication is currently enabled, disable it.
        if (publication.ReplicateDdl == DdlReplicationOptions.All)
        {
            publication.ReplicateDdl = DdlReplicationOptions.None;
        }
        else
        {
            publication.ReplicateDdl = DdlReplicationOptions.All;
        }
    }
    else
    {
        throw new ApplicationException(String.Format(
            "Settings could not be retrieved for the publication. " +
            "Ensure that the publication {0} exists on {1}.",
            publicationName, publisherName));
    }
}
catch (Exception ex)
{
    // Do error handling here.
    throw new ApplicationException(
        "The publication property could not be changed.", ex);
}
finally
{
    conn.Disconnect();
}

' Define the server, database, and publication names
Dim publisherName As String = publisherInstance
Dim publicationName As String = "AdvWorksSalesOrdersMerge"
Dim publicationDbName As String = "AdventureWorks2022"

Dim publication As MergePublication

' Create a connection to the Publisher.
Dim conn As ServerConnection = New ServerConnection(publisherName)

Try
    ' Connect to the Publisher.
    conn.Connect()

    ' Set the required properties for the publication.
    publication = New MergePublication()
    publication.ConnectionContext = conn
    publication.Name = publicationName
    publication.DatabaseName = publicationDbName

    ' If we can't get the properties for this merge publication, then throw an application exception.
    If publication.LoadProperties() Then
        ' If DDL replication is currently enabled, disable it.
        If publication.ReplicateDdl = DdlReplicationOptions.All Then
            publication.ReplicateDdl = DdlReplicationOptions.None
        Else
            publication.ReplicateDdl = DdlReplicationOptions.All
        End If
    Else
        Throw New ApplicationException(String.Format( _
         "Settings could not be retrieved for the publication. " + _
         "Ensure that the publication {0} exists on {1}.", _
         publicationName, publisherName))
    End If
Catch ex As Exception
    ' Do error handling here.
    Throw New ApplicationException( _
        "The publication property could not be changed.", ex)
Finally
    conn.Disconnect()
End Try

Related content