Skip to content

Latest commit

 

History

History
157 lines (94 loc) · 12.5 KB

define-and-modify-a-static-row-filter.md

File metadata and controls

157 lines (94 loc) · 12.5 KB
Raw
title description author ms.author ms.date ms.service ms.subservice ms.topic ms.custom helpviewer_keywords monikerRange
Define and Modify a Static Row Filter
Define and Modify a Static Row Filter
MashaMSFT
mathoma
03/14/2017
sql
replication
how-to
updatefrequency5
modifying filters, static row
static row filters
filters [SQL Server replication], static row
=azuresqldb-mi-current||>=sql-server-2016

Define and Modify a Static Row Filter

[!INCLUDEsql-asdbmi] This topic describes how to define and modify a static row filter in [!INCLUDEssnoversion] by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql].

In This Topic

Before You Begin

Limitations and Restrictions

  • If you add, modify, or delete a static row filter after subscriptions to the publication have been initialized, you must generate a new snapshot and reinitialize all subscriptions after making the change. For more information about requirements for property changes, see Change Publication and Article Properties.

  • If the publication is enabled for peer-to-peer transactional replication, tables cannot be filtered.

Recommendations

Using SQL Server Management Studio

Define, modify, and delete static row filters on the Filter Table Rows page of the New Publication Wizard or the Filter Rows page of the Publication Properties - <Publication> dialog box. For more information about using the wizard and accessing the dialog box, see Create a Publication and View and Modify Publication Properties.

To define a static row filter

  1. On the Filter Table Rows page of the New Publication Wizard or the Filter Rows page of the Publication Properties - <Publication> dialog box, the action you take depends on the type of publication:

    • For a snapshot or transactional publication, click Add.

    • For a merge publication, click Add, and then click Add Filter.

  2. In the Add Filter dialog box, select a table to filter from the drop-down list box.

  3. Create a filter statement in the Filter statement text area. You can type directly in the text area, and you can also drag and drop columns from the Columns list box.

    [!NOTE]
    The WHERE clause should use two-part naming; three-part naming and four-part naming are not supported. If the publication is from an Oracle Publisher, the WHERE clause must be compliant with Oracle syntax.

    • The Filter statement text area includes the default text, which is in the form of:

      SELECT <published_columns> FROM [schema].[tablename] WHERE

    • The default text cannot be changed; type the filter clause after the WHERE keyword using standard SQL syntax. The complete filter clause would appear like:

      SELECT <published_columns> FROM [HumanResources].[Employee] WHERE [LoginID] = 'adventure-works\ranjit0'

    • A static row filter can include a user-defined function. The complete filter clause for a static row filter with a user-defined function would appear like:

      SELECT <published_columns> FROM [Sales].[SalesOrderHeader] WHERE MyFunction([Freight]) > 100

  4. Select OK.

  5. If you are in the Publication Properties - <Publication> dialog box, click OK to save and close the dialog box.

To modify a static row filter

  1. On the Filter Table Rows page of the New Publication Wizard or the Filter Rows page of the Publication Properties - <Publication> dialog box, select a filter in the Filtered Tables pane, and then click Edit.

  2. In the Edit Filter dialog box, modify the filter.

  3. Select OK.

To delete a static row filter

  1. On the Filter Table Rows page of the New Publication Wizard or the Filter Rows page of the Publication Properties - <Publication> dialog box, select a filter in the Filtered Tables pane, and then click Delete.

Using Transact-SQL

When creating table articles, you can define a WHERE clause to filter rows out of an article. You can also change a row filter after it has been defined. Static row filters can be created and modified programmatically using replication stored procedures.

To define a static row filter for a snapshot or transactional publication

  1. Define the article to filter. For more information, see Define an Article.

  2. At the Publisher on the publication database, execute sp_articlefilter (Transact-SQL). Specify the name of the article for @article, the name of the publication for @publication, a name for the filter for @filter_name, and the filtering clause for @filter_clause (not including WHERE).

  3. If a column filter must still be defined, see Define and Modify a Column Filter. Otherwise, execute sp_articleview (Transact-SQL). Specify the publication name for @publication, the name of the filtered article for @article, and the filter clause specified in step 2 for @filter_clause. This creates the synchronization objects for the filtered article.

To modify a static row filter for a snapshot or transactional publication

  1. At the Publisher on the publication database, execute sp_articlefilter (Transact-SQL). Specify the name of the article for @article, the name of the publication for @publication, a name for the new filter for @filter_name, and the new filtering clause for @filter_clause (not including WHERE). Because this change will invalidate data in existing subscriptions, specify a value of 1 for @force_reinit_subscription.

  2. At the Publisher on the publication database, execute sp_articleview (Transact-SQL). Specify the publication name for @publication, the name of the filtered article for @article, and the filter clause specified in step 1 for @filter_clause. This re-creates the view that defines the filtered article.

  3. Rerun the Snapshot Agent job for the publication to generate an updated snapshot. For more information, see Create and Apply the Initial Snapshot.

  4. Reinitialize subscriptions. For more information, see Reinitialize Subscriptions.

To delete a static row filter for a snapshot or transactional publication

  1. At the Publisher on the publication database, execute sp_articlefilter (Transact-SQL). Specify the name of the article for @article, the name of the publication for @publication, a value of NULL for @filter_name, and a value of NULL for @filter_clause. Because this change will invalidate data in existing subscriptions, specify a value of 1 for @force_reinit_subscription.

  2. Rerun the Snapshot Agent job for the publication to generate an updated snapshot. For more information, see Create and Apply the Initial Snapshot.

  3. Reinitialize subscriptions. For more information, see Reinitialize Subscriptions.

To define a static row filter for a merge publication

  1. At the Publisher on the publication database, execute sp_addmergearticle (Transact-SQL). Specify the filtering clause for @subset_filterclause (not including WHERE). For more information, see Define an Article.

  2. If a column filter must still be defined, see Define and Modify a Column Filter.

To modify a static row filter for a merge publication

  1. At the Publisher on the publication database, execute sp_changemergearticle (Transact-SQL). Specify the publication name for @publication, the name of the filtered article for @article, a value of subset_filterclause for @property, and the new filtering clause for @value (not including WHERE). Because this change will invalidate data in existing subscriptions, specify a value of 1 for @force_reinit_subscription.

  2. Rerun the Snapshot Agent job for the publication to generate an updated snapshot. For more information, see Create and Apply the Initial Snapshot.

  3. Reinitialize subscriptions. For more information, see Reinitialize Subscriptions.

Examples (Transact-SQL)

In this transactional replication example, the article is filtered horizontally to remove all discontinued products.

:::code language="sql" source="../codesnippet/tsql/define-and-modify-a-stat_1.sql":::

In this merge replication example, the articles are filtered horizontally to return only rows that belong to the specified salesperson. A join filter is also used. For more information, see Define and Modify a Join Filter Between Merge Articles.

:::code language="sql" source="../codesnippet/tsql/define-and-modify-a-stat_2.sql":::

See Also

Define and Modify a Parameterized Row Filter for a Merge Article
Change Publication and Article Properties
Filter Published Data
Filter Published Data for Merge Replication