You are here:Home » transaction replication » How to add filtered table in replication - SQL Server

How to add filtered table in replication - SQL Server

I wrote some of the articles related to replication e.g. adding tables, stored procedures, views and functions in transactional replication. Now I am sharing one another script to add a filtered table in the transactions table. In script I have applied filters on CreatedDate column SampleTable table. It helps to fit the need of only required rows at subscriber and reduce the overhead of data transfer.

Script :
USE [PublisherDB]
GO

-- Adding the transactional articles
EXEC sp_addarticle 
 @publication = N'FilteredTables', 
 @article = N'SampleTable', 
 @source_owner = N'dbo', 
 @source_object = N'SampleTable', 
 @type = N'logbased', 
 @description = N'', 
 @creation_script = N'', 
 @pre_creation_cmd = N'drop', 
 @schema_option = 0x000000000803509F, 
 @identityrangemanagementoption = N'manual', 
 @destination_table = N'SampleTable', 
 @destination_owner = N'dbo', 
 @status = 24, 
 @vertical_partition = N'false', 
 @ins_cmd = N'CALL [sp_MSins_dboSampleTable]', 
 @del_cmd = N'CALL [sp_MSdel_dboSampleTable]', 
 @upd_cmd = N'SCALL [sp_MSupd_dboSampleTable]', 
 @filter_clause = N'[CreatedDate] between ''2014-04-01 00:00:00.000'' and ''2014-04-15 00:00:00.000'''

-- Adding the article filter
EXEC sp_articlefilter 
 @publication = N'FilteredTables', 
 @article = N'SampleTable', 
 @filter_name = N'FLTR_SampleTable_1__51', 
 @filter_clause = N'[CreatedDate] between ''2014-04-01 00:00:00.000'' and ''2014-04-15 00:00:00.000''', 
 @force_invalidate_snapshot = 1, 
 @force_reinit_subscription = 1

-- Adding the article synchronization object
EXEC sp_articleview 
 @publication = N'FilteredTables', 
 @article = N'SampleTable', 
 @view_name = N'SYNC_SampleTable_1__51', 
 @filter_clause = N'[CreatedDate]  between ''2014-04-01 00:00:00.000'' and ''2014-04-15 00:00:00.000''', 
 @force_invalidate_snapshot = 1, 
 @force_reinit_subscription = 1
GO
UI :



Hope you like this post. Have a great day!