Chapter 4: ASE Replicator Procedures

sp_addreplicateart

Description

Creates a new replicate article (or articles) in a specified subscription.

Syntax

sp_addreplicateart sub_name [, pri_art [, rep_art 
	[, field_nums 
	[, where_clause]]]]

Parameters

sub_name: The name of the subscription to which the new article is added.

pri_art: The name of a primary article. Primary article names can be specified in the form owner.name.

rep_art: The name of a replicate article, if different from the primary article. This parameter can be used when the name of the replicate object is different from the name of the primary object, which is always the same as the name of the primary article. Replicate article names can be specified in the form owner.name.

To avoid problems on case-insensitive data servers, always specify replicate object names using the same character case as returned by the catalog stored procedures on the replicate data server.

field_nums: One or more numbers that identify the published fields to be subscribed to. Numbers can be entered with separating commas, or with dashes to indicate inclusive ranges. Numeric values are treated as strings and must be enclosed in quotes.

where_clause: A SQL-type where clause that further selects the published data to be received by the replicate object identified by the replicate article. The SQL keyword where is optional.

Examples

Example 1

sp_addreplicateart subdoc, table1, @field_nums="2-4"

Creates a new replicate article in the subscription subdoc, for the primary article table1, subscribing only to published fields 2, 3, and 4 in the primary article.

Example 2

sp_addreplicateart subdoc, table1, @where_clause="where style = 'round' or 
style = 'square'"

Creates a new replicate article in the subscription subdoc, for the primary article table1, subscribing to all published fields in the primary article, and replicating only rows in which the value of style is either round or square.

Example 3

sp_addreplicateart subdoc, table1, reptable1, "1,3-4", "where style = 'round' or style = 'square'"

Creates a new replicate article in the subscription subdoc, for the primary article table1, with the replicate object named reptable1, subscribing only to published fields 1, 3, and 4 in the primary article, and replicating only rows in which the value of style is either round or square.

Usage

Before you invoke sp_addreplicateart to create a replicate article, you must suspend the subscription using sp_suspendsub. After the replicate article is created, you can resume the subscription using sp_resumesub.
When a replicate article for a table is created, a proxy table is created in the Distribution Database. If the replicate table does not exist in the replicate database, it is created at the replicate database with default attributes and with columns based on the published fields selected for subscription.

If the replicate table is created by ASE Replicator when the replicate article is created, ASE Replicator deletes the replicate table in the replicate database when you delete the replicate article.
When ASE Replicator creates a replicate table in the replicate database, the owner of the table is either:
- The ASE Replicator system user, if no separate login is specified for the Maintenance User, or
- The Maintenance User login that the ASE Replicator system user was mapped to with sp_addexternlogin when the replicate database was set up or when the replicate database connection was created.
When a replicate article for a stored procedure is created, a distribution procedure that calls the replicate stored procedure is created in the Distribution Database.

When a replicate article for a stored procedure is created, the replicate procedure must already exist in the replicate database.
The replicate article (rep_art) you specify must identify a replicate object of the same type (table or stored procedure) as the primary article (pri_art). When the replicate object is a table, the published fields available for subscription are columns in the primary table. When the replicate object is a stored procedure, the published fields available for subscription are the primary stored procedure’s input parameters.

To avoid problems on case-insensitive data servers, always specify replicate object names using the same character case as returned by the catalog stored procedures on the replicate data server.
When a replicate article subscribes to a table, it must subscribe to at least one field (column).
When a replicate article subscribes to a procedure, you can choose to subscribe to none of the published fields. To specify that none of the published fields should be subscribed to, use the number 0 (zero) for the field_nums parameter in sp_addreplicateart. For example:
```
sp_addreplicateart subdoc, proc1, repproc1, "0"
```
If you specify a where clause for a replicate procedure article, you must reference the primary fields (parameter names) without using the at sign (@). For example:
```
sp_addreplicateart subdoc, proc1, repproc1, "1-3", "where param1 = 'round' or param1 = 'square'"
```
The published fields that a replicate article subscribes to are mapped to the first available fields (columns or parameters) in the replicate object. For example, if a replicate article subscribes to columns 1, 3, and 4 in the primary article, those fields are mapped to columns 1, 2, and 3 in the replicate table.
The primary article (pri_art) you specify must exist in the publication to which the specified subscription (sub_name) subscribes.
ASE Replicator creates a table named rl_lastcommit in the replicate database to keep track of transactions committed there. If you create a primary database connection to a replicate database (to implement bidirectional replication, for example), you cannot publish the rl_lastcommit table.

ASE Replicator does not support replicating transactions to or from the rl_lastcommit table.
When sp_addreplicateart is invoked with no primary article (pri_art) specified, a replicate article subscribing to all published fields is created for every primary article in the publication.
When sp_addreplicateart is invoked with only a primary article (pri_art) specified, a replicate article subscribing to all published fields in the specified primary article is created.
When sp_addreplicateart is invoked with both a primary article (pri_art) and a replicate article (rep_art) specified, a replicate article is created with the rep_art name specified.
When sp_addreplicateart is invoked with a primary article (pri_art) specified, and field numbers (field_nums) specified, a replicate article subscribing to the specified published fields in the specified primary article is created.
When sp_addreplicateart is invoked with a primary article (pri_art) specified, and a where clause (where_clause) is specified, a replicate article subscribing to the selected data specified by the where clause is created.

If you specify a where clause for a replicate procedure article, do not use the @ character to reference the primary fields (input parameters) in the where clause.
You can combine field numbers (field_nums) and a where clause (where_clause) to select both a subset of the published fields and selected data from the primary article.
When the requested action occurs successfully, no results are returned.
When an error occurs, an error message is returned.