Muokkaa

Jaa

Set the propagation method for data changes to transactional articles

  • Artikkeli

Applies to: SQL Server Azure SQL Managed Instance

This topic describes how to set the propagation method for data changes to transactional articles in SQL Server by using SQL Server Management Studio or Transact-SQL.

By default, transactional replication propagates changes to Subscribers using a set of stored procedures for each article. You can replace these procedures with custom procedures. For more information, see Specify How Changes Are Propagated for Transactional Articles.

Before you begin

Limitations and restrictions

  • Care must be taken when editing any of the snapshot files generated by replication. Test and support custom logic in the custom stored procedures. Microsoft doesn't provide support for custom logic.

Using 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 Create a Publication and View and Modify Publication Properties.

To specify the propagation method

  1. On the Articles page of the New Publication Wizard or the Publication Properties - <Publication> dialog box, select a table, and then select Article Properties.

  2. Select Set Properties of Highlighted Table Article.

  3. 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.

  4. Select OK.

  5. If you are in the Publication Properties - <Publication> dialog box, select OK to save and close the dialog box.

To generate and use custom stored procedures

  1. On the Articles page of the New Publication Wizard or the Publication Properties - <Publication> dialog box, select a table, and then select Article Properties.

  2. Select 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 Specify How Changes Are Propagated for Transactional Articles.

  3. Select OK.

  4. If you are in the Publication Properties - <Publication> dialog box, select OK to save and close the dialog box.

  5. 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.

Using Transact-SQL

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 Specify How Changes Are Propagated for Transactional Articles.

To create an article that uses Transact-SQL commands to propagate data changes

  1. 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 Define an Article.

To create an article that doesn't propagate data changes

  1. 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 Define an Article.

To create an article with user-modified custom stored procedures

  1. 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 Specify How Changes Are Propagated for Transactional Articles.

    For more information, see Define an Article.

  2. 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 Specify How Changes Are Propagated for Transactional Articles.

To create an article with custom scripting in the custom stored procedures to propagate data changes

  1. 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 Specify How Changes Are Propagated for Transactional Articles.

    For more information, see Define an Article.

  2. 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 Specify How Changes Are Propagated for Transactional Articles.

To change the method of propagating changes for an existing article

  1. 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.

  2. Repeat step 1 for each propagation method to be changed.

Related content