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 :
Hope you like this post. Have a great day!
Script :
USE [PublisherDB]UI :
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
Hope you like this post. Have a great day!
No comments:
Post a Comment
Please Use Good Leanguage