How to: Define an Article (Replication Transact-SQL Programming)
After a publication has been created, articles can be created programmatically using replication stored procedures. The stored procedures used to create an article will depend on the type of publication for which the article is being defined. For more information, see How to: Create a Publication (Replication Transact-SQL Programming).
Note
Article names cannot include any of the following characters: % , * , [ , ] , | , : , " , ? , ' , \ , / , < , >. If objects in the database include any of these characters and you want to replicate them, you must specify an article name that is different from the object name.
To define an article for a Snapshot or Transactional Publication
At the Publisher on the publication database, execute sp_addarticle. Specify the name of the publication to which the article belongs for @publication, a name for the article for @article, the database object being published for @source_object, and any other optional parameters. Use @source_owner to specify the schema ownership of the object, if not dbo. If the article is not a log-based table article, specify the article type for @type; for more information, see How to: Specify Article Types (Replication Transact-SQL Programming).
To horizontally filter rows in a table or view an article, use sp_articlefilter to define the filter clause. For more information, see How to: Define and Modify a Static Row Filter (Replication Transact-SQL Programming).
To vertically filter columns in a table or view an article, use sp_articlecolumn. For more information, see How to: Define and Modify a Column Filter (Replication Transact-SQL Programming).
Execute sp_articleview if the article is filtered.
If the publication has existing subscriptions and sp_helppublication returns a value of 0 in the immediate_sync column, you must call sp_addsubscription to add the article to each existing subscription.
If the publication has existing pull subscriptions, execute sp_refreshsubscriptions at the Publisher to create a new snapshot for existing pull subscriptions that contains just the new article.
Note
For subscriptions that are not initialized using a snapshot, you do not need to execute sp_refreshsubscriptions as this procedure is executed by sp_addarticle.
To define an article for a Merge Publication
At the Publisher on the publication database, execute sp_addmergearticle. Specify the name of the publication for @publication, a name for the article name for @article, and the object being published for @source_object. To horizontally filter table rows, specify a value for @subset_filterclause. For more information, see How to: Define and Modify a Parameterized Row Filter for a Merge Article (Replication Transact-SQL Programming) and How to: Define and Modify a Static Row Filter (Replication Transact-SQL Programming). If the article is not a table article, specify the article type for @type. For more information, see How to: Specify Article Types (Replication Transact-SQL Programming).
(Optional) At the Publisher on the publication database, execute sp_addmergefilter to define a join filter between two articles. For more information, see How to: Define and Modify a Join Filter Between Merge Articles (Replication Transact-SQL Programming).
(Optional) At the Publisher on the publication database, execute sp_mergearticlecolumn to filter table columns. For more information, see How to: Define and Modify a Column Filter (Replication Transact-SQL Programming).
Example
This example defines an article based on the Product table for a transactional publication, where the article is filtered both horizontally and vertically.
DECLARE @publication AS sysname;
DECLARE @table AS sysname;
DECLARE @filterclause AS nvarchar(500);
DECLARE @filtername AS nvarchar(386);
DECLARE @schemaowner AS sysname;
SET @publication = N'AdvWorksProductTran';
SET @table = N'Product';
SET @filterclause = N'[DiscontinuedDate] IS NULL';
SET @filtername = N'filter_out_discontinued';
SET @schemaowner = N'Production';
-- Add a horizontally and vertically filtered article for the Product table.
-- Manually set @schema_option to ensure that the Production schema
-- is generated at the Subscriber (0x8000000).
EXEC sp_addarticle
@publication = @publication,
@article = @table,
@source_object = @table,
@source_owner = @schemaowner,
@schema_option = 0x80030F3,
@vertical_partition = N'true',
@type = N'logbased',
@filter_clause = @filterclause;
-- (Optional) Manually call the stored procedure to create the
-- horizontal filtering stored procedure. Since the type is
-- 'logbased', this stored procedures is executed automatically.
EXEC sp_articlefilter
@publication = @publication,
@article = @table,
@filter_clause = @filterclause,
@filter_name = @filtername;
-- Add all columns to the article.
EXEC sp_articlecolumn
@publication = @publication,
@article = @table;
-- Remove the DaysToManufacture column from the article
EXEC sp_articlecolumn
@publication = @publication,
@article = @table,
@column = N'DaysToManufacture',
@operation = N'drop';
-- (Optional) Manually call the stored procedure to create the
-- vertical filtering view. Since the type is 'logbased',
-- this stored procedures is executed automatically.
EXEC sp_articleview
@publication = @publication,
@article = @table,
@filter_clause = @filterclause;
GO
This example defines articles for a merge publication, where the SalesOrderHeader article is statically filtered based on SalesPersonID, and the SalesOrderDetail article is join filtered based on SalesOrderHeader.
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.BusinessEntityID = 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