sp_articleview (T-SQL)

Creates the synchronization object for an article when a table is filtered vertically or horizontally. This synchronization object is a view that is used as the filtered source of the schema and data for the destination tables. Only unsubscribed articles can be modified by this stored procedure.

Syntax

sp_articleview [@publication =] 'publication', [@article =] 'article'
    [,[@view_name =] 'view_name'] [,[@filter_clause =] 'filter_clause']

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.

[@view_name =] 'view_name'

Is the name of the synchronization object. view_name is nvarchar(386), with a default of NULL.

[@filter_clause =] 'filter_clause'

Is a restriction (WHERE) clause that defines a horizontal filter. When entering the restriction clause, omit the WHERE keyword. filter_clause is ntext, with a default of NULL.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_articleview 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.

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 drops the existing view and creates a new one automatically. If the view was manually created (type in sysarticles is 5) the existing view is not dropped.

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

Permissions

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

See Also