Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Creates the view that defines the published article when a table is filtered vertically or horizontally. This view 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. This stored procedure is executed at the Publisher on the publication database.
sp_articleview [ @publication = ] 'publication' , [ @article = ] 'article' [ , [ @view_name = ] 'view_name'] [ , [ @filter_clause = ] 'filter_clause'] [ , [ @change_active = ] change_active ] [ , [ @force_invalidate_snapshot = ] force_invalidate_snapshot ] [ , [ @force_reinit_subscription = ] force_reinit_subscription ] [ , [ @publisher = ] 'publisher' ] [ , [ @refreshsynctranprocs = ] refreshsynctranprocs ] [ , [ @internal = ] internal ]
sp_articleview creates the view that defines the published article and inserts the ID of this view in the sync_objid column of the sysarticles (Transact-SQL) table, and inserts 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 (Transact-SQL) table is set to the ID of the base table, and the use of sp_articleview is not required.
To publish a vertically filtered table (that is, to filter columns) first run sp_addarticle with no sync_object parameter, run sp_articlecolumn (Transact-SQL) once for each column to be replicated (defining the vertical filter), and then run sp_articleview to create the view that defines the published article.
To publish a horizontally filtered table (that is, to filter rows), run sp_addarticle (Transact-SQL) with no filter parameter. Run sp_articlefilter (Transact-SQL), providing all parameters including filter_clause. Then run sp_articleview, providing all parameters including the identical filter_clause.
To publish a vertically and horizontally filtered table, run sp_addarticle (Transact-SQL) with no sync_object or filter parameters. Run sp_articlecolumn (Transact-SQL) once for each column to be replicated, and then run sp_articlefilter (Transact-SQL) and sp_articleview.
If the article already has a view that defines the published article, sp_articleview drops the existing view and creates a new one automatically. If the view was created manually (type in sysarticles (Transact-SQL) is 5), the existing view is not dropped.
If you create a custom filter stored procedure and a view that defines the published article manually, do not run sp_articleview. Instead, provide these as the filter and sync_object parameters to sp_addarticle (Transact-SQL), along with the appropriate type value.
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