sp_articleview Replication Stored Procedure

Creates the synchronization object for an article when a table is filtered vertically and/or horizontally. This synchronization object is a view that is used as the filtered source of the schema and data for the destination tables.

Syntax

sp_articleview publication, article [, view_name] [, filter_clause]

where

publication: Is the name of the publication that contains the article.
article: Is the name of the article.
view_name: Is the name of the synchronization object.
filter_clause: Is a restriction (WHERE) clause that defines a horizontal partition. When entering the restriction clause, omit the word "WHERE."

Remarks

This stored procedure creates the view and inserts the ID of the synchronization object (the view) in the sync_objid column of the sysarticles table, and the text of the restriction clause in the filter_clause column. If all columns are replicated and there is no filter_clause, the sync_objid in the sysarticles table is set to the ID of the base table and the use of sp_articleview is not required.

Only unsubscribed articles can be modified by this stored procedure.

To publish a vertically partitioned table (that is, to filter columns) first run sp_addarticle with no sync_object parameter, run sp_articlecolumn once for each column to be replicated (defining the vertical partition), and then run sp_articleview to create the synchronization object.

To publish a horizontally partitioned table (that is, to filter rows), run sp_addarticle with no filter parameter. Run sp_articlefilter, providing all parameters including filter_clause. Then run sp_articleview, providing all parameters including the identical filter_clause.

To publish a vertically and horizontally partitioned table, run sp_addarticle with no sync_object or filter parameters, run sp_articlecolumn once for each column to be replicated, and then run sp_articlefilter and sp_articleview.

If the article already has a synchronization object (a view), sp_articleview will drop the existing view and create a new one if the type in sysarticles is 1 (log-based article) or 3 (log-based article with manual filter).

If you manually create a custom filter stored procedure and a synchronization object, do not run sp_articleview. Instead, provide them as the filter and sync_object parameters to sp_addarticle, along with the appropriate type value.

Example

This example creates the synchronization view MyView for MyPub and filters on the zip code 94609.

sp_articleview 'MyPub', 'MyArticle', 'MyView', 'zip = "94609"'

Permission

Only the system administrator or the database owner can use this stored procedure.

Tables Used

sysobjects, syscolumns, syspublications, syssubscriptions, sysarticles