How to: Specify Article Types (Replication Transact-SQL Programming)

The default article types for replication are table articles, but you can publish other database objects as articles, including views, stored procedures, user-defined functions, and stored procedure execution. You can use replication stored procedures to specify an article type programmatically when you define an article. The procedures that you use depend on the type of replication and article type.

Note

The schema-only designation when defining table, view, and stored procedure articles indicates that only the object definition is replicated.

To publish a table article in a transactional or snapshot publication

1. At the Publisher on the publication database, execute sp_addarticle. Specify one of the following values for @type to define the type of article:

   • logbased - a log-based table article, which is the default for transactional and snapshot replication. Replication automatically generates the stored procedure used for horizontal filtering and the view that defines a vertically filtered article.
   • logbased manualfilter - a log-based, horizontally filtered article where the stored procedure used for horizontal filtering is manually created and defined by the user and specified for @filter. For more information, see How to: Define and Modify a Static Row Filter (Replication Transact-SQL Programming).
   • logbased manualview - a log-based, vertically filtered article where the view that defines the vertically filtered article is created and defined by the user and specified for @sync_object. For more information, see How to: Define and Modify a Static Row Filter (Replication Transact-SQL Programming) and How to: Define and Modify a Column Filter (Replication Transact-SQL Programming).
   • logbased manualboth - a log-based, horizontally and vertically filtered article where both the stored procedure used for horizontal filtering and the view that defines the vertically filtered article are created and defined by the user and specified for @filter and @sync_object, respectively. For more information, see How to: Define and Modify a Static Row Filter (Replication Transact-SQL Programming) and How to: Define and Modify a Column Filter (Replication Transact-SQL Programming).

   This defines a new article for the publication. For more information, see How to: Define an Article (Replication Transact-SQL Programming).

2. For logbased manualboth and logbased manualfilter articles, execute sp_articlefilter to generate the filtering stored procedure for a horizontally filtered article. For more information, see How to: Define and Modify a Static Row Filter (Replication Transact-SQL Programming).

3. For logbased manualboth, logbased manualview, and logbased manualfilter articles, execute sp_articleview to generate the view that defines the vertically filtered article. For more information, see How to: Define and Modify a Column Filter (Replication Transact-SQL Programming).

To publish a view or indexed view article in a transactional or snapshot publication

1. At the Publisher on the publication database, execute sp_addarticle. Specify one of the following values for @type to define the type of article:

   • indexed view logbased - a log-based indexed view article. Replication automatically generates the stored procedure used for horizontal filtering and the view that defines a vertically filtered article.
   • view schema only - a schema-only view article. The base table must also be replicated.
   • indexed view schema only - a schema-only indexed view article. The base table must also be replicated.
   • indexed view logbased manualfilter - a log-based, horizontally filtered indexed view article where the stored procedure used for horizontal filtering is manually created and defined by the user and specified for @filter. For more information, see How to: Define and Modify a Static Row Filter (Replication Transact-SQL Programming).
   • indexed view logbased manualview - a log-based, filtered indexed view article where the view that defines a vertically filtered article is created and defined by the user and specified for @sync_object. For more information, see How to: Define and Modify a Static Row Filter (Replication Transact-SQL Programming) and How to: Define and Modify a Column Filter (Replication Transact-SQL Programming).
   • indexed view logbased manualboth - a log-based, filtered indexed view article where both the stored procedure used for horizontal filtering and the view that defines a vertically filtered article are created and defined by the user and specified for @filter and @sync_object, respectively. For more information, see How to: Define and Modify a Static Row Filter (Replication Transact-SQL Programming) and How to: Define and Modify a Column Filter (Replication Transact-SQL Programming).

   This defines a new article for the publication. For more information, see How to: Define an Article (Replication Transact-SQL Programming).

2. For logbased manualboth and logbased manualfilter articles, execute sp_articlefilter to generate the filtering stored procedure for a horizontally filtered article. For more information, see How to: Define and Modify a Static Row Filter (Replication Transact-SQL Programming).

3. For logbased manualboth, logbased manualview, and logbased manualfilter articles, execute sp_articleview to generate the view that defines the vertically filtered article. For more information, see How to: Define and Modify a Column Filter (Replication Transact-SQL Programming).

To publish a stored procedure, stored procedure execution, or user-defined function article in a transactional or snapshot publication

1. At the Publisher on the publication database, execute sp_addarticle. Specify one of the following values for @type to define the type of article:

   • proc schema only - a schema-only stored procedure article.
   • proc exec - replicates the execution of the stored procedure to all Subscribers of the article. For more information, see Publishing Stored Procedure Execution in Transactional Replication.
   • serializable proc exec - replicates the execution of the stored procedure only if it is executed within the context of a serializable transaction. For more information, see Publishing Stored Procedure Execution in Transactional Replication.
   • func schema only - a schema-only user-defined function article.

   This defines a new article for the publication. For more information, see How to: Define an Article (Replication Transact-SQL Programming).

To publish a table or view article in a merge publication

1. At the Publisher on the publication database, execute sp_addmergearticle. Specify one of the following values for @type to define the type of article:

   • table - a table article.
   • indexed view schema only - a schema-only indexed view article.
   • view schema only - a schema-only view article.

   This defines a new article for the publication. For more information, see How to: Define an Article (Replication Transact-SQL Programming).

To publish a stored procedure or user-defined function article in a merge publication

1. At the Publisher on the publication database, execute sp_addmergearticle. Specify one of the following values for @type to define the type of article:

   • func schema only - a schema-only user-defined function article.
   • proc schema only - a schema-only stored procedure article.

   This defines a new article for the publication. For more information, see How to: Define an Article (Replication Transact-SQL Programming).

See Also

Concepts

Programming Replication Using System Stored Procedures

Other Resources

Publishing Data and Database Objects

Help and Information

Getting SQL Server 2005 Assistance