sp_addmergearticle

Transact-SQL Reference

Transact-SQL Reference

sp_addmergearticle

Adds an article to an existing merge publication. This stored procedure is executed at the Publisher on the publication database.

Syntax

sp_addmergearticle [ @publication = ] 'publication'
    ,
[ @article = ] 'article'
    ,
[ @source_object = ] 'source_object'
    
[ , [ @type = ] 'type' ]
    
[ , [ @description = ] 'description' ]
    [ , [ @column_tracking = ] 'column_tracking' ]
    [ , [ @status = ] 'status' ]
    [ , [ @pre_creation_cmd = ] 'pre_creation_cmd' ]
    [ , [ @creation_script = ] 'creation_script' ]
    [ , [ @schema_option = ] schema_option ]
    [ , [ @subset_filterclause = ] 'subset_filterclause' ]
    
[ , [ @article_resolver = ] 'article_resolver' ]
    [ , [ @resolver_info = ] 'resolver_info' ]
    [ , [ @source_owner = ] 'source_owner' ]
    [ , [ @destination_owner = ] 'destination_owner' ]
    [ , [ @vertical_partition= ] 'vertical_partition' ]
    [ , [ @auto_identity_range = ] 'auto_identity_range' ]
    [ , [ @pub_identity_range = ] pub_identity_range ]
    [ , [ @identity_range = ] identity_range ]
    [ , [ @threshold = ] threshold ]
    [ , [ @verify_resolver_signature = ] verify_resolver_signature ]
    [ , [ @destination_object = ] 'destination_object' ]
    [ , [ @allow_interactive_resolver = ] 'allow_interactive_resolver' ]
    [ , [ @fast_multicol_updateproc = ] 'fast_multicol_updateproc' ]
    [ , [ @check_permissions = ] check_permissions ]
    [ , [ @force_invalidate_snapshot = ] force_invalidate_snapshot ]

Arguments

[@publication = ] 'publication'

Is the name of the publication that contains the article. publication is sysname, with no default.

[@article = ] 'article'

Is the name of the article. article is sysname, with no default. article must be on the local SQL Server computer, and must conform to the rules for identifiers.

[@source_object = ] 'source_object'

Is the name of the source object from which to add the article. source_object is sysname, with no default.

[@type = ] 'type'

Is the type of article. type is sysname, with a default of table, and can be one of these values.

Value Description
table (default) Article monitors a table to determine replicated data.
indexed view schema only Article monitors an indexed view and schema to determine source data.
view schema only Article monitors a view and schema to determine source data.
proc schema only Article uses stored procedure execution and schema to determine source data.
func schema only Article uses user-defined function execution and schema to determine source data.
NULL (default)  

[@description = ] 'description'

Is a description of the article. description is nvarchar(255), with a default of NULL.

[@column_tracking = ] 'column_tracking'

Is the setting for column-level tracking. column_tracking is nvarchar(10), with a default of FALSE. true turns on column tracking. false turns off column tracking and leaves conflict detection at the row level. If the table is already published in other merge publications, you must use the same column tracking value used by existing articles based on this table. This parameter is specific to table articles only.

[@status = ] 'status'

Is the status of the article. status is nvarchar(10), with a default of unsynced. If active, the initial processing script to publish the table is run. If unsynced, the initial processing script to publish the table is run at the next time the Snapshot Agent runs.

[@pre_creation_cmd = ] 'pre_creation_cmd'

Specifies what the system is to do if the table exists at the subscriber when applying the snapshot. pre_creation_cmd is nvarchar(10), and can be one of these values.

Value Description
None If the table already exists at the Subscriber, no action is taken.
Delete Issues a delete based on the WHERE clause in the subset filter.
drop (default) Drops the table before re-creating it.
Truncate Same as delete, but deletes pages instead of rows. Does not accept a WHERE clause.

[@creation_script = ] 'creation_script'

Is the optional schema precreation script for the article. creation_script is nvarchar(255), with a default of NULL.

[@schema_option = ] schema_option

Is a bitmap of the schema generation option for the given article. schema_option is binary(8), and can be one of these values. If this value is NULL, the system will auto-generate a valid schema option for the article. The table given in the Remarks shows the value that will be chosen based upon the combination of the article type and the replication type. Also, not all @schema_option values are valid for every type of replication and article type. The Valid Schema Option table given in the Remarks shows the valid schema options that can be chosen based upon the combination of the article type and the replication type.

Value Description
0x00 Disables scripting by the Snapshot Agent and uses the provided CreationScript.
0x01 Generates the object creation (CREATE TABLE, CREATE PROCEDURE, and so on). This is the default value for stored procedure articles.
0x10 Generates a corresponding clustered index.
0x20 Converts user-defined data types to base data types.
0x40 Generates corresponding nonclustered index(es).
0x80 Includes declared referential integrity on the primary keys.
0x100 Replicates user triggers on a table article, if defined.
0x200 Replicates foreign key constraints. If the referenced table is not part of a publication, all foreign key constraints on a published table will not be replicated.
0x400 Replicates check constraints.
0x800 Replicates defaults.
0x1000 Replicates column-level collation.
0x2000 Replicates extended properties associated with the published article source object.
0x4000 Replicates unique keys if defined on a table article.
0x8000 Replicates a primary key and unique keys on a table article as constraints using ALTER TABLE statements.

[@subset_filterclause = ] 'subset_filterclause'

Is a WHERE clause specifying the horizontal filtering of a table article without the word WHERE included. subset_filterclause is of nvarchar(1000), with a default of an empty string. For more information, see Generate Filters Automatically.

[@article_resolver = ] 'article_resolver'

Is the resolver used to resolve conflicts on the table article. article_resolver is varchar(255), with a default of NULL. Available values for this parameter are listed in Microsoft Resolver Descriptions. If the value provided is not one of the Microsoft Resolvers, SQL Server uses the specified resolver instead of the system-supplied resolver. Use sp_enumcustomresolvers to enumerate the list of available custom resolvers.

[@resolver_info = ] 'resolver_info'

Is used to specify additional information required by a custom resolver. Some of the Microsoft Resolvers require a column provided as input to the resolver. resolver_info is nvarchar(255), with a default of NULL. For more information, see Microsoft Resolver Descriptions.

[@source_owner = ] 'source_owner'

Is the name of the owner of the source_object. source_owner is sysname, with a default of NULL. If NULL, the current user is assumed to be the owner.

[@destination_owner = ] 'destination_owner'

Is the owner of the object in the subscription database, if not 'dbo'. destination_owner is sysname, with a default of NULL. If NULL, 'dbo' is assumed to be the owner.

[@vertical_partition = ] 'column_filter'

Enables and disables column filtering on a table article. vertical_partition is nvarchar(5) with a default of FALSE. false indicates there is no vertical filtering and publishes all columns. true clears all columns except the declared primary key and ROWGUID columns. Columns are added using sp_articlecolumn.

[@auto_identity_range = ] 'automatic_identity_range'

Enables and disables automatic identity range handling for this table article on a publication at the time it is created. auto_identity_range is nvarchar(5), with a default of FALSE. true enables automatic identity range handling, while false disables it. For more information, see Managing Identity Values.

[@pub_identity_range = ] pub_identity_range

Controls the range size at the Publisher if the article has auto_identity_range set to true. auto_identity_range is bigint, with a default of NULL.

[@identity_range = ] identity_range

Controls the range size at the Subscriber if the article has auto_identity_range set to true. identity_range is bigint, with a default of NULL.

[@threshold = ] threshold

Percentage value that controls when the Merge Agent assigns a new identity range. When the percentage of values specified in threshold is used, the Merge Agent creates a new identity range. threshold is int, with a default of NULL. Used when auto_identity_range is set to true.

[@verify_resolver_signature = ] verify_resolver_signature

Specifies if a digital signature is verified before using a resolver in merge replication. verify_resolver_signature is int, with a default of 0. 0 specifies that the signature will not be verified. 1 specifies that the signature will be verified to see if it is from a trusted source. For more information, see Replication Signature Verification Constants(SQLDMO_VERIFYSIGNATURE_TYPE).

[@destination_object = ] 'destination_object'

Is the name of the object in the subscription database. destination_object is sysname, with a default value of what is in @source_object. This parameter can be specified only if the article is a schema-only article, such as stored procedures, views, and UDFs. If the article specified is a table article, the value in @source_object will override the value in destination_object.

[@allow_interactive_resolver = ] 'allow_interactive_resolver'

Enables or disables the use of the Interactive Resolver on an article. allow_interactive_resolver is nvarchar(5), with a default of FALSE. true enables the use of the Interactive Resolver on the article; false disables it.

[@fast_multicol_updateproc = ] 'fast_multicol_updateproc'

Enables or disables the Merge Agent to apply changes to multiple columns in the same row in one UPDATE statement. fast_multicol_updateproc is nvarchar(5), with a default of TRUE. true updates multiple columns in one statement. false issues a separate UPDATE for each column changed. For performance reasons, it is desirable to set the value to true if two or more columns are being updated. However, the option should be set to false if there is a user trigger on the table that raises an error on updates to a specific column, detected via the IF UPDATE(col). Even if that column is not updated to a new value, the IF UPDATE(col) will detect a column update and raise the error. This is because with the option set to true, all columns (except special columns like ones involved in filters) are set in one UPDATE statement. If the value of a particular column didn't change, it is set to the old value.

[@check_permissions = ] check_permissions

Is a bitmap of the table-level permissions that will be verified when the Merge Agent applies changes to the Publisher. If the Publisher login/user account used by the merge process does not have the correct table permissions, the invalid changes will be logged as conflicts. check_permissions is int, and can have one of these values.

Value Description
0x00 (default) Permissions will not be checked.
0x10 Checks permissions at the Publisher before INSERTs made at a Subscriber can be uploaded.
0x20 Checks permissions at the Publisher before UPDATEs made at a Subscriber can be uploaded.
0x40 Checks permissions at the Publisher before DELETEs made at a Subscriber can be uploaded.

[@force_invalidate_snapshot = ] force_invalidate_snapshot

Acknowledges that the action taken by this stored procedure may invalidate an existing snapshot. force_invalidate_snapshot is a bit, with a default of 0. 0 specifies that adding an article will not cause the snapshot to be invalid. If the stored procedure detects that the change does require a new snapshot, an error will occur and no changes will be made. 1 specifies that adding an article may cause the snapshot to be invalid, and if there are existing subscriptions that require a new snapshot, gives permission for the existing snapshot to be marked as obsolete and a new snapshot generated.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_addmergearticle is used in merge replication.

The table describes the default @schema_option value that will be chosen for the stored procedure if a NULL value is passed in by the user. The default value is based upon the replication type shown across the top, and the article type shown down the first column.  Empty cells are article and replication type pairs that are not valid combinations, and therefore, have no default.

Article Type Replication Type
  Merge
logbased  
logbased manualfilter  
logbased manualview  
indexed view logbased  
indexed view logbased manualfilter  
indexed view logbased manualview  
indexed view logbase manualboth  
proc exec  
serialized proc exec  
proc schema only 0x01
view schema only 0x01
func schema only 0x01
indexed view schema only 0x01
table 0xccf1

Valid Schema Option Table

Article Type Replication Type
  Merge
logbased  
logbased manualfilter  
logbased manualview  
indexed view logbased  
indexed view logbased manualfilter  
indexed view logbased manualview  
indexed view logbase manualboth  
proc exec 0x01 and 0x2000
serialized proc exec 0x01 and 0x2000
proc schema only 0x01 and 0x2000
view schema only 0x01, 0x0100, and 0x2000
func schema only 0x01 and 0x2000
indexed view schema only 0x01, 0x10, 0x040, 0x0100, and 0x2000
table All options but 0x02 and 0x8000

Permissions

Only members of the sysadmin fixed server role or db_owner fixed database role can execute sp_addmergearticle.

See Also

Managing Identity Values

Row-Level Tracking and Column-Level Tracking

sp_changemergearticle

sp_dropmergearticle

sp_helpmergearticle

System Stored Procedures

Specifying a Custom Resolver