How to: Set the Propagation Method for Data Changes to Transactional Articles (Replication Transact-SQL Programming)
By default, replication automatically generates the stored procedures used to propagate changes to Subscribers. Transactional replication enables you to control how changes are propagated from the Publisher to Subscribers, and this propagation method can be set programmatically when an article is created and changed later using replication stored procedures.
Note
You can specify a different propagation method for each type of DML (data manipulation language) operation (insert, update, or delete) that occurs on a row of a published data.
For more information, see Specifying How Changes Are Propagated for Transactional Articles.
To create an article that uses Transact-SQL commands to propagate data changes
At the Publisher on the publication database, execute sp_addarticle. Specify the name of the publication to which the article belongs for @publication, a name for the article for @article, the database object being published for @source_object, and a value of SQL for at least one of the following parameters:
@ins_cmd - controls the replication of INSERT commands.
@upd_cmd - controls the replication of UPDATE commands.
@del_cmd - controls the replication of DELETE commands.
Note
When specifying a value of SQL for any of the above parameters, commands of that type will be replicated to the Subscriber as the appropriate Transact-SQL command.
For more information, see How to: Define an Article (Replication Transact-SQL Programming).
To create an article that does not propagate data changes
At the Publisher on the publication database, execute sp_addarticle. Specify the name of the publication to which the article belongs for @publication, a name for the article for @article, the database object being published for @source_object, and a value of NONE for at least one of the following parameters:
@ins_cmd - controls the replication of INSERT commands.
@upd_cmd - controls the replication of UPDATE commands.
@del_cmd - controls the replication of DELETE commands.
Note
When specifying a value of NONE for any of the above parameters, commands of that type will not be replicated to the Subscriber.
For more information, see How to: Define an Article (Replication Transact-SQL Programming).
To create an article with user-modified custom stored procedures
At the Publisher on the publication database, execute sp_addarticle. Specify the name of the publication to which the article belongs for @publication, a name for the article for @article, the database object being published for @source_object, a value for the @schema_option bitmask that contains the value 0x02 (enables automatic generation of custom stored procedures), and at least one of the following parameters:
@ins_cmd - specify a value of CALL sp_MSins_article_name, where article_name is the value specified for @article.
@del_cmd - specify a value of CALL sp_MSdel_article_name or XCALL sp_MSdel_article_name, where article_name is the value specified for @article.
@upd_cmd - specify a value of SCALL sp_MSupd_article_name, CALL sp_MSupd_article_name, XCALL sp_MSupd_article_name, or MCALL sp_MSupd_article_name, where article_name is the value specified for @article.
Note
For each of the above command parameters, you can specify your own name for the stored procedures that replication generates.
Note
For more information on CALL, SCALL, XCALL, and MCALL syntax, see Specifying How Changes Are Propagated for Transactional Articles.
For more information, see How to: Define an Article (Replication Transact-SQL Programming).
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.exe, 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. For more information, see Specifying How Changes Are Propagated for Transactional Articles.
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.
To create an article with custom scripting in the custom stored procedures to propagate data changes
At the Publisher on the publication database, execute sp_addarticle. Specify the name of the publication to which the article belongs for @publication, a name for the article for @article, the database object being published for @source_object, a value for the @schema_option bitmask that contains the value 0x02 (enables automatic generation of custom stored procedures), and at least one of the following parameters:
@ins_cmd - specify a value of CALL sp_MSins_article_name, where article_name is the value specified for @article.
@del_cmd - specify a value of CALL sp_MSdel_article_name or XCALL sp_MSdel_article_name, where article_name is the value specified for @article.
@upd_cmd - specify a value of SCALL sp_MSupd_article_name, CALL sp_MSupd_article_name, XCALL sp_MSupd_article_name, MCALL sp_MSupd_article_name, where article_name is the value specified for @article.
Note
For each of the above command parameters, you can specify your own name for the stored procedures that replication generates.
Note
For more information on CALL, SCALL, XCALL, and MCALL syntax, see Specifying How Changes Are Propagated for Transactional Articles.
For more information, see How to: Define an Article (Replication Transact-SQL Programming).
At the Publisher on the publication database, use the ALTER PROCEDURE statement to edit sp_scriptpublicationcustomprocs so that it returns a CREATE PROCEDURE script for the insert, update, and delete custom stored procedures. For more information, see Specifying How Changes Are Propagated for Transactional Articles.
Warning
Care must be taken when editing sp_scriptpublicationcustomprocs; you must test and support custom logic in this stored procedure. Microsoft does not provide support for custom logic or changes to system stored procedures.
To change the method of propagating changes for an existing article
At the Publisher on the publication database, execute sp_changearticle. Specify @publication, @article, a value of ins_cmd, upd_cmd, or del_cmd for @property, and the appropriate propagation method for @value.
Repeat step 1 for each propagation method to be changed.