How to: Set the Propagation Method for Data Changes to Transactional Articles (SQL Server Management Studio)
Specify the propagation method on the Properties tab of the Article Properties - <Article> dialog box, which is available in the New Publication Wizard and the Publication Properties - <Publication> dialog box. For more information about using the wizard and accessing the dialog box, see How to: Create a Publication and Define Articles (SQL Server Management Studio) and How to: View and Modify Publication and Article Properties (SQL Server Management Studio).
By default, transactional replication propagates changes to Subscribers using a set of stored procedures for each article. It is possible to replace these procedures with custom procedures. For more information, see Specifying How Changes Are Propagated for Transactional Articles.
To specify the propagation method
On the Articles page of the New Publication Wizard or the Publication Properties - <Publication> dialog box, select a table, and then click Article Properties.
Click Set Properties of Highlighted Table Article.
On the Properties tab of the Article Properties - <Article> dialog box, in the Statement Delivery section, specify the propagation method for each operation using the INSERT delivery format, UPDATE delivery format, and DELETE delivery format menus.
Click OK.
If you are in the Publication Properties - <Publication> dialog box, click OK to save and close the dialog box.
To generate and use custom stored procedures
On the Articles page of the New Publication Wizard or the Publication Properties - <Publication> dialog box, select a table, and then click Article Properties.
Click Set Properties of Highlighted Table Article.
On the Properties tab of the Article Properties - <Article> dialog box, in the Statement Delivery section, select the CALL syntax from the appropriate delivery format menu (INSERT delivery format, UPDATE delivery format, or DELETE delivery format), and then type the name of the procedure to use in INSERT stored procedure, DELETE stored procedure, or UPDATE stored procedure. For more information about CALL syntax, see the section "Call syntax for stored procedures" in Specifying How Changes Are Propagated for Transactional Articles.
Click OK.
If you are in the Publication Properties - <Publication> dialog box, click OK to save and close the dialog box.
When the snapshot for the publication is generated, it will include the procedure you specified in the previous step. The procedures will use the CALL syntax you specified, but will include the default logic that replication uses.
After the snapshot has been generated, navigate to the snapshot folder for the publication to which this article belongs and locate the .sch file with the same name as the article. Open this file using Notepad or another text editor, locate the CREATE PROCEDURE command for the insert, update, or delete stored procedures, and edit the procedure definition to supply any custom logic for propagating data changes. If the snapshot is regenerated, you must re-create the custom procedure.
Warning
Care must be taken when editing any of the snapshot files generated by replication. You must test and support custom logic in the custom stored procedures; Microsoft does not provide support for custom logic.