Monday, 18 May 2015

How to add filtered table in replication - SQL Server by serverku

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!

No comments:

Post a Comment

Please Use Good Leanguage