View and Modify Article Properties

Article
03/01/2023

Applies to: SQL Server Azure SQL Managed Instance

This topic describes how to view and modify article 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

Recommendations
To view and modify article properties, using:

SQL Server Management Studio

Transact-SQL

Replication Management Objects (RMO)

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

After a publication is created, some property changes require a new snapshot. If a publication has subscriptions, some changes also require all subscriptions to be reinitialized. For more information, see Change Publication and Article Properties and Add Articles to and Drop Articles from Existing Publications.

Using SQL Server Management Studio

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

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 article properties

On the Articles Page of the Publication Properties - <Publication> dialog box, select an article, and then click Article Properties.
Select which articles property changes should apply to:
- Click Set Properties of Highlighted <ObjectType> Article to launch the Article Properties - <ObjectName> dialog box; property changes made in this dialog box are applied only to the object that is highlighted in the object pane on the Articles page.
- Click Set Properties of All <ObjectType> Articles, to launch the Properties for All <ObjectType> Articles dialog box; property changes made in this dialog box are applied to all objects of that type in the object pane on the Articles page, including ones not yet selected for publication.
  
  Note
  
  Property changes made in the Properties for All <ObjectType> Articles dialog box override any made previously in the Article Properties - <ObjectName> dialog box. If, for example, you want to set a number of defaults for all articles of an object type, but also want to set some properties for individual objects, set the defaults for all articles first. Then set the properties for the individual objects.
Modify any properties if necessary, and then click OK.
Click OK on the Publication Properties - <Publication> dialog box.

Using Transact-SQL

Articles can be modified and their properties returned programmatically using replication stored procedures. The stored procedures that you use depend on the type of publication to which the article belongs.

To view the properties of an article belonging to a snapshot or transactional publication

Execute sp_helparticle, specifying the name of the publication for the @publication parameter and the name of the article for the @article parameter. If you do not specify @article, information will be returned for all articles in the publication.
Execute sp_helparticlecolumns for table articles to list all columns available in the base table.

To modify the properties of an article belonging to a snapshot or transactional publication

Execute sp_changearticle, specifying the article property being changed in the @property parameter and the new value of this property in the @value parameter.

Note

If the change requires the generation of a new snapshot, you must also specify a value of 1 for @force_invalidate_snapshot, and if the change requires that Subscribers be reinitialized, you must also 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 an article belonging to a merge publication

Execute sp_helpmergearticle, specifying the name of the publication for the @publication parameter and the name of the article for the @article parameter. If you do not specify these parameters, information will be returned for all articles in a publication or at the publisher.
Execute sp_helpmergearticlecolumn for table articles to list all columns available in the base table.

To modify the properties of an article belonging to a merge publication

Execute sp_changemergearticle, specifying the article property being changed in the @property parameter and the new value of this property in the @value parameter.

Note

If the change requires the generation of a new snapshot, you must also specify a value of 1 for @force_invalidate_snapshot, and if the change requires that Subscribers be reinitialized, you must also 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.

Example (Transact-SQL)

This transactional replication example returns the properties of the published article.

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

USE [AdventureWorks2022]
EXEC sp_helparticle
  @publication = @publication;
GO

This transactional replication example changes the schema options for the published article.

DECLARE @publication AS sysname;
DECLARE @article AS sysname;
DECLARE @option AS int;
SET @publication = N'AdvWorksProductTran';
SET @article = N'Product';
SET @option = (SELECT CAST(0x0000000002030073 AS int));

-- Change the schema options to replicate schema with XML.
USE [AdventureWorks2022]
EXEC sp_changearticle 
  @publication = @publication,
  @article = @article, 
  @property = N'schema_option', 
  @value = @option,
  @force_invalidate_snapshot = 1;
GO

This merge replication example returns the properties of the published article.

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

USE [AdventureWorks2022]
EXEC sp_helpmergearticle
  @publication = @publication;
GO

This merge replication example changes the conflict detection settings for a published article.

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

-- Enable column-level conflict tracking.
-- Changing this property requires that existing subscriptions
-- be reinitialized and that a new snapshot be generated.
USE [AdventureWorks2022]
EXEC sp_changemergearticle 
  @publication = @publication,
  @article = @article, 
  @property = N'column_tracking', 
  @value = N'true',
  @force_invalidate_snapshot = 1,
  @force_reinit_subscription = 1;
GO

Using Replication Management Objects (RMO)

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

To view or modify properties of an article that belongs to a snapshot or transactional publication

Create a connection to the Publisher by using the ServerConnection class.
Create an instance of the TransArticle class.
Set the Name, PublicationName, and DatabaseName properties.
Set the connection from step 1 for the ConnectionContext property.
Call the LoadProperties method to get the properties of the object. If this method returns false, either the article properties in step 3 were defined incorrectly or the article does not exist.
(Optional) To change properties, set a new value for one of the TransArticle properties that can be set.
(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 an article that belongs to a merge publication

Create a connection to the Publisher by using the ServerConnection class.
Create an instance of the MergeArticle class.
Set the Name, PublicationName, and DatabaseName properties.
Set the connection from step 1 for the ConnectionContext property.
Call the LoadProperties method to get the properties of the object. If this method returns false, either the article properties in step 3 were defined incorrectly or the article does not exist.
(Optional) To change properties, set a new value for one of the MergeArticle properties that can be set.
(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.

Example (RMO)

This example changes a merge article to specify the business logic handler used by the article.

// Define the Publisher, publication, and article names.
string publisherName = publisherInstance;
string publicationName = "AdvWorksSalesOrdersMerge";
string publicationDbName = "AdventureWorks2022";
string articleName = "SalesOrderHeader";

// Set the friendly name of the business logic handler.
string customLogic = "OrderEntryLogic";

MergeArticle article = new MergeArticle();

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

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

    // Set the required properties for the article.
    article.ConnectionContext = conn;
    article.Name = articleName;
    article.DatabaseName = publicationDbName;
    article.PublicationName = publicationName;

    // Load the article properties.
    if (article.LoadProperties())
    {
        article.ArticleResolver = customLogic;
    }
    else
    {
        // Throw an exception of the article does not exist.
        throw new ApplicationException(String.Format(
        "{0} is not published in {1}", articleName, publicationName));
    }
    
}
catch (Exception ex)
{
    // Do error handling here and rollback the transaction.
    throw new ApplicationException(String.Format(
        "The business logic handler {0} could not be associated with " +
        " the {1} article.",customLogic,articleName), ex);
}
finally
{
    conn.Disconnect();
}

' Define the Publisher, publication, and article names.
Dim publisherName As String = publisherInstance
Dim publicationName As String = "AdvWorksSalesOrdersMerge"
Dim publicationDbName As String = "AdventureWorks2022"
Dim articleName As String = "SalesOrderHeader"

' Set the friendly name of the business logic handler.
Dim customLogic As String = "OrderEntryLogic"

Dim article As MergeArticle = New MergeArticle()

' 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 article.
    article.ConnectionContext = conn
    article.Name = articleName
    article.DatabaseName = publicationDbName
    article.PublicationName = publicationName

    ' Load the article properties.
    If article.LoadProperties() Then
        article.ArticleResolver = customLogic
    Else
        ' Throw an exception of the article does not exist.
        Throw New ApplicationException(String.Format( _
         "{0} is not published in {1}", articleName, publicationName))
    End If

Catch ex As Exception
    ' Do error handling here and rollback the transaction.
    Throw New ApplicationException(String.Format( _
     "The business logic handler {0} could not be associated with " + _
     " the {1} article.", customLogic, articleName), ex)
Finally
    conn.Disconnect()
End Try