Step 3: Create Reciprocal Publications

To create reciprocal publications, add the transactional publication and article to each database. Note that:

• Custom stored procedures are used for @ins_cmd, @del_cmd, and @upd_cmd.
• The XCALL style parameters are used in UPDATE and DELETE stored procedures. For more information, see Using Custom Stored Procedures in Articles .
• @schema_option does not enable autogeneration of the default custom stored procedure.

Examples

A. Add the transactional publication and article in test1

USE test1

GO

EXEC sp_addpublication @publication = N'two_way_pub_test1',

    @restricted = N'false', @sync_method = N'native',

    @repl_freq = N'continuous', @description = N'publ1’,

    @status = N'active', @allow_push = N'true',

    @allow_pull = N'true', @allow_anonymous = N'false',

    @enabled_for_internet = N'false',

    @independent_agent = N'false', @immediate_sync = N'false',

    @allow_sync_tran = N'false',

    @autogen_sync_procs = N'false', @retention = 60,

    @default_access = N'true'

EXEC sp_addpublication_snapshot

    @publication = N'two_way_pub_test1',@frequency_type = 4,

    @frequency_interval = 1,

    @frequency_relative_interval = 0,

    @frequency_recurrence_factor = 1, @frequency_subday = 1,

    @frequency_subday_interval = 0, @active_start_date = 0,

    @active_end_date = 0, @active_start_time_of_day = 233000,

    @active_end_time_of_day = 0

GO

EXEC sp_addarticle @publication = N'two_way_pub_test1',

    @article = N'two_way_test1', @source_owner = N'dbo',

    @source_object = N'two_way_test1',

    @destination_TABLE = N'two_way_test2',

    @type = N'logbased', @creation_script = null,

    @description = null, @pre_creation_cmd = N'drop',

    @schema_option = 0x00000000000000F1, @status = 16,

    @vertical_partition = N'false',

    @ins_cmd = N'CALL sp_ins_two_way_test2',

    @del_cmd = N'XCALL sp_del_two_way_test2',

    @upd_cmd = N'XCALL sp_upd_two_way_test2',

    @filter = null, @sync_object = null

GO

B. Add the transactional publication and article in test2

USE test2

GO

EXEC sp_addpublication @publication = N'two_way_pub_test2',

    @restricted = N'false', @sync_method = N'native',

    @repl_freq = N'continuous', @description = N'Pub2’,

    @status = N'active', @allow_push = N'true',

    @allow_pull = N'true', @allow_anonymous = N'false',

    @enabled_for_internet = N'false',

    @independent_agent = N'false', @immediate_sync = N'false',

    @allow_sync_tran = N'false',

    @autogen_sync_procs = N'false', @retention = 60,

    @default_access = N'true'

EXEC sp_addpublication_snapshot

    @publication = N'two_way_pub_test2',@frequency_type = 4,

    @frequency_interval = 1,

    @frequency_relative_interval = 0,

    @frequency_recurrence_factor = 1, @frequency_subday = 1,

    @frequency_subday_interval = 0, @active_start_date = 0,

    @active_end_date = 0, @active_start_time_of_day = 233000,

    @active_end_time_of_day = 0

GO

-- Add the transactional articles in test2

EXEC sp_addarticle @publication = N'two_way_pub_test2',

    @article = N'two_way_test2', @source_owner = N'dbo',

    @source_object = N'two_way_test2', @destination_TABLE =

    N'two_way_test1', @type = N'logbased',

    @creation_script = null,

    @description = null, @pre_creation_cmd = N'drop',

    @schema_option = 0x00000000000000F1, @status = 16,

    @vertical_partition = N'false',

    @ins_cmd = N'CALL sp_ins_two_way_test1',

    @del_cmd = N'XCALL sp_del_two_way_test1',

    @upd_cmd = N'XCALL sp_upd_two_way_test1',

    @filter = null, @sync_object = null

GO