sp_mergearticlecolumn (Transact-SQL)

Partitions a merge publication vertically. This stored procedure is executed at the Publisher on the publication database.

Transact-SQL Syntax Conventions

Syntax

sp_mergearticlecolumn [ @publication = ] 'publication'
        , [ @article = ] 'article'
    [ , [ @column = ] 'column'
    [ , [ @operation = ] 'operation' 
    [ , [ @schema_replication = ] 'schema_replication' ]
    [ , [ @force_invalidate_snapshot = ] force_invalidate_snapshot ] 
    [ , [ @force_reinit_subscription = ] force_reinit_subscription ] 

Arguments

• [ @publication =] 'publication'
  Is the name of the publication. Publication is sysname, with no default.

• [ @article =] 'article'
  Is the name of the article in the publication. article is sysname, with no default.

• [ @column =] 'column'
  Identifies the columns on which to create the vertical partition. column is sysname, with a default of NULL. If NULL and @operation = N'add', all columns in the source table are added to the article by default. column cannot be NULL when operation is set to drop. To exclude columns from an article, execute sp_mergearticlecolumn and specify column and @operation = N'drop' for each column to be removed from the specified article.

• [ @operation =] 'operation'
  Is the replication status. operation is nvarchar(4), with a default of ADD. add marks the column for replication. drop clears the column.

• [ @schema_replication=] 'schema_replication'
  Specifies that a schema change will be propagated when the Merge Agent runs. schema_replication is nvarchar(5), with a default of FALSE.

  Note

  Only FALSE is supported for schema_replication.

• [ @force_invalidate_snapshot = ] force_invalidate_snapshot
  Enables or disables the ability to have a snapshot invalidated. force_invalidate_snapshot is a bit, with a default of 0.

  0 specifies that changes to the merge article will not cause the snapshot to be invalid.

  1 specifies that changes to the merge article may cause the snapshot to be invalid, and if that is the case, a value of 1 gives permission for the new snapshot to occur.

• [ **@force_reinit_subscription = ]**force_reinit_subscription
  Enables or disables the ability to have the subscription reinitializated. force_reinit_subscription is a bit with a default of 0.

  0 specifies that changes to the merge article will not cause the subscription to be reinitialized.

  1 specifies that changes to the merge article may cause the subscription to be reinitialized, and if that is the case, a value of 1 gives permission for the subscription reinitialization to occur.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_mergearticlecolumn is used in merge replication.

An identity column cannot be dropped from the article if automatic identity range management is being used. For more information, see Replicating Identity Columns.

If an application sets a new vertical partition after the initial snapshot is created, a new snapshot must be generated and reapplied to each subscription. Snapshots are applied when the next scheduled snapshot and distribution or merge agent run.

If row tracking is used for conflict detection (the default), the base table can include a maximum of 1,024 columns, but columns must be filtered from the article so that a maximum of 246 columns is published. If column tracking is used, the base table can include a maximum of 246 columns. For more information, see the "Tracking Level" section of How Merge Replication Detects and Resolves Conflicts.

Permissions

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

Example

DECLARE @publication AS sysname;
DECLARE @table1 AS sysname;
DECLARE @table2 AS sysname;
DECLARE @table3 AS sysname;
DECLARE @salesschema AS sysname;
DECLARE @hrschema AS sysname;
DECLARE @filterclause AS nvarchar(1000);
SET @publication = N'AdvWorksSalesOrdersMerge'; 
SET @table1 = N'Employee'; 
SET @table2 = N'SalesOrderHeader'; 
SET @table3 = N'SalesOrderDetail'; 
SET @salesschema = N'Sales';
SET @hrschema = N'HumanResources';
SET @filterclause = N'Employee.LoginID = HOST_NAME()';

-- Add a filtered article for the Employee table.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table1, 
  @source_object = @table1, 
  @type = N'table', 
  @source_owner = @hrschema,
  @schema_option = 0x0004CF1,
  @description = N'article for the Employee table',
  @subset_filterclause = @filterclause;

-- Add an article for the SalesOrderHeader table that is filtered
-- based on Employee and horizontally filtered.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table2, 
  @source_object = @table2, 
  @type = N'table', 
  @source_owner = @salesschema, 
  @vertical_partition = N'true',
  @schema_option = 0x0034EF1,
  @description = N'article for the SalesOrderDetail table';

-- Add an article for the SalesOrderDetail table that is filtered
-- based on SaledOrderHeader.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table3, 
  @source_object = @table3, 
  @source_owner = @salesschema,
  @description = 'article for the SalesOrderHeader table', 
  @identityrangemanagementoption = N'auto', 
  @pub_identity_range = 100000, 
  @identity_range = 100, 
  @threshold = 80,
  @schema_option = 0x0004EF1;

-- Add all columns to the SalesOrderHeader article.
EXEC sp_mergearticlecolumn 
  @publication = @publication, 
  @article = @table2, 
  @force_invalidate_snapshot = 1, 
  @force_reinit_subscription = 1;

-- Remove the credit card Approval Code column.
EXEC sp_mergearticlecolumn 
  @publication = @publication, 
  @article = @table2, 
  @column = N'CreditCardApprovalCode', 
  @operation = N'drop', 
  @force_invalidate_snapshot = 1, 
  @force_reinit_subscription = 1;

-- Add a merge join filter between Employee and SalesOrderHeader.
EXEC sp_addmergefilter 
  @publication = @publication, 
  @article = @table2, 
  @filtername = N'SalesOrderHeader_Employee', 
  @join_articlename = @table1, 
  @join_filterclause = N'Employee.EmployeeID = SalesOrderHeader.SalesPersonID', 
  @join_unique_key = 1, 
  @filter_type = 1, 
  @force_invalidate_snapshot = 1, 
  @force_reinit_subscription = 1;

-- Add a merge join filter between SalesOrderHeader and SalesOrderDetail.
EXEC sp_addmergefilter 
  @publication = @publication, 
  @article = @table3, 
  @filtername = N'SalesOrderDetail_SalesOrderHeader', 
  @join_articlename = @table2, 
  @join_filterclause = N'SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID', 
  @join_unique_key = 1, 
  @filter_type = 1, 
  @force_invalidate_snapshot = 1, 
  @force_reinit_subscription = 1;
GO

See Also

Reference

Replication Stored Procedures (Transact-SQL)

Other Resources

How to: Define and Modify a Join Filter Between Merge Articles (Replication Transact-SQL Programming)
How to: Define and Modify a Parameterized Row Filter for a Merge Article (Replication Transact-SQL Programming)
Filtering Published Data

Help and Information

Getting SQL Server 2005 Assistance

Change History