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!

Script to add articles in publication - SQL Server transactional replication by serverku

Recently I wrote a post which having a script to get articles details added in replication for all publications. Today I am sharing a script to add articles in the existing publication of transactional replication, which we can also add through user interface of publication property. Here I am sharing a query to add tables, views, stored procedures, indexed views and functions.

1. Table :

USE PublisherDB
GO
EXEC sp_addarticle 
 @publication = N'PublicationName', 
 @article = N'TableName', 
 @source_owner = N'SchemaName', 
 @source_object = N'TableName', 
 @type = N'logbased', 
 @description = N'', 
 @creation_script = N'', 
 @pre_creation_cmd = N'drop', 
 @schema_option = 0x00000000080350DF, 
 @identityrangemanagementoption = N'manual', 
 @destination_table = N'TableName', 
 @destination_owner = N'SchemaName', 
 @status = 24, 
 @vertical_partition = N'false', 
 @ins_cmd = N'CALL [sp_MSins_SchemaNameTableName]', 
 @del_cmd = N'CALL [sp_MSdel_SchemaNameTableName]', 
 @upd_cmd = N'SCALL [sp_MSupd_SchemaNameTableName]',
 @force_invalidate_snapshot = 1
GO

2. View :

USE PublisherDB
GO
EXEC sp_addarticle 
 @publication = N'PublicationName', 
 @article = N'ViewName', 
 @source_owner = N'schemaName', 
 @source_object = N'ViewName', 
 @type = N'view schema only', 
 @description = N'', 
 @creation_script = N'', 
 @pre_creation_cmd = N'drop', 
 @schema_option = 0x0000000008000001, 
 @destination_table = N'ViewName', 
 @destination_owner = N'schemaName', 
 @status = 16
GO

3. Stored Procedure :

USE PublisherDB
GO
EXEC sp_addarticle 
 @publication = N'PublicationName', 
 @article = N'ProcedureName', 
 @source_owner = N'SchemaName', 
 @source_object = N'ProcedureName', 
 @type = N'proc schema only', 
 @description = N'', 
 @creation_script = N'', 
 @pre_creation_cmd = N'drop', 
 @schema_option = 0x0000000008000001, 
 @destination_table = N'ProcedureName', 
 @destination_owner = N'SchemaName', 
 @status = 16
GO

4. Indexed View:

USE PublisherDB
GO
EXEC sp_addarticle 
 @publication = N'PublicationName', 
 @article = N'Indexed View Name', 
 @source_owner = N'SchemaName', 
 @source_object = N'Indexed View Name', 
 @type = N'indexed view schema only', 
 @description = N'', 
 @creation_script = N'', 
 @pre_creation_cmd = N'drop', 
 @schema_option = 0x0000000008000001, 
 @destination_table = N'Indexed View Name', 
 @destination_owner = N'SchemaName', 
 @status = 16
GO

5. Function :

USE PublisherDB
GO
exec sp_addarticle 
 @publication = N'PublicationName', 
 @article = N'FunctionName', 
 @source_owner = N'SchemaName', 
 @source_object = N'FunctionName', 
 @type = N'func schema only', 
 @description = N'', 
 @creation_script = N'', 
 @pre_creation_cmd = N'drop', 
 @schema_option = 0x0000000008000001, 
 @destination_table = N'FunctionName', 
 @destination_owner = N'SchemaName', 
 @status = 16
GO

Please note these queries run against publisher server and database. I will add one more post next to add articles in existing publications without initialize whole subscription. So stay tuned for more!