---
title: "Set the Propagation Method for Data Changes to Transactional Articles | Microsoft Docs"
ms.custom: ""
ms.date: "06/13/2017"
ms.prod: "sql-server-2014"
ms.reviewer: ""
ms.technology: replication
ms.topic: conceptual
helpviewer_keywords: 
  - "transactional replication, propagation methods"
  - "propagating data changes [SQL Server replication]"
ms.assetid: 0a291582-f034-42da-a1a3-29535b607b74
author: MashaMSFT
ms.author: mathoma
manager: craigg
---
# Set the Propagation Method for Data Changes to Transactional Articles
  This topic describes how to set the propagation method for data changes to transactional articles in [!INCLUDE[ssCurrent](../../../includes/sscurrent-md.md)] by using [!INCLUDE[ssManStudioFull](../../../includes/ssmanstudiofull-md.md)] or [!INCLUDE[tsql](../../../includes/tsql-md.md)].  
  
 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](../transactional/transactional-articles-specify-how-changes-are-propagated.md).  
  
 **In This Topic**  
  
-   **Before you begin:**  
  
     [Limitations and Restrictions](#Restrictions)  
  
-   **To set the propagation method for data changes to transactional articles, using:**  
  
     [SQL Server Management Studio](#SSMSProcedure)  
  
     [Transact-SQL](#TsqlProcedure)  
  
## Before You Begin  
  
###  <a name="Restrictions"></a> Limitations and Restrictions  
  
-   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. [!INCLUDE[msCoName](../../../includes/msconame-md.md)] does not provide support for custom logic.  
  
##  <a name="SSMSProcedure"></a> 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](create-a-publication.md) and [View and Modify Publication Properties](view-and-modify-publication-properties.md).  
  
#### 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 click **Article Properties**.  
  
2.  Click **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.  [!INCLUDE[clickOK](../../../includes/clickok-md.md)]  
  
5.  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  
  
1.  On the **Articles** page of the New Publication Wizard or the **Publication Properties - \<Publication>** dialog box, select a table, and then click **Article Properties**.  
  
2.  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 [Specify How Changes Are Propagated for Transactional Articles](../transactional/transactional-articles-specify-how-changes-are-propagated.md).  
  
3.  [!INCLUDE[clickOK](../../../includes/clickok-md.md)]  
  
4.  If you are in the **Publication Properties - \<Publication>** dialog box, click **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.  
  
##  <a name="TsqlProcedure"></a> 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](../transactional/transactional-articles-specify-how-changes-are-propagated.md).  
  
#### To create an article that uses Transact-SQL commands to propagate data changes  
  
1.  At the Publisher on the publication database, execute [sp_addarticle](/sql/relational-databases/system-stored-procedures/sp-addarticle-transact-sql). 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](/sql/t-sql/statements/insert-transact-sql) commands.  
  
    -   **\@upd_cmd** - controls the replication of [UPDATE](/sql/t-sql/queries/update-transact-sql) commands.  
  
    -   **\@del_cmd** - controls the replication of [DELETE](/sql/t-sql/statements/delete-transact-sql) 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 [!INCLUDE[tsql](../../../includes/tsql-md.md)] command.  
  
     For more information, see [Define an Article](define-an-article.md).  
  
#### To create an article that does not propagate data changes  
  
1.  At the Publisher on the publication database, execute [sp_addarticle](/sql/relational-databases/system-stored-procedures/sp-addarticle-transact-sql). 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](/sql/t-sql/statements/insert-transact-sql) commands.  
  
    -   **\@upd_cmd** - controls the replication of [UPDATE](/sql/t-sql/queries/update-transact-sql) commands.  
  
    -   **\@del_cmd** - controls the replication of [DELETE](/sql/t-sql/statements/delete-transact-sql) 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](define-an-article.md).  
  
#### To create an article with user-modified custom stored procedures  
  
1.  At the Publisher on the publication database, execute [sp_addarticle](/sql/relational-databases/system-stored-procedures/sp-addarticle-transact-sql). 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 <strong>CALL sp_MSins_*article_name*</strong>, where **_article_name_** is the value specified for **\@article**.  
  
    -   **\@del_cmd** - specify a value of <strong>CALL sp_MSdel_*article_name*</strong> or <strong>XCALL sp_MSdel_*article_name*</strong>, where **_article_name_** is the value specified for _*\@article**.  
  
    -   **\@upd_cmd** - specify a value of <strong>SCALL sp_MSupd_*article_name*</strong>, <strong>CALL sp_MSupd_*article_name*</strong>, <strong>XCALL sp_MSupd__article_name*</strong>, or <strong>MCALL sp_MSupd_*article_name*</strong>, 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](../transactional/transactional-articles-specify-how-changes-are-propagated.md).  
  
     For more information, see [Define an Article](define-an-article.md).  
  
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](../transactional/transactional-articles-specify-how-changes-are-propagated.md).  
  
#### 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](/sql/relational-databases/system-stored-procedures/sp-addarticle-transact-sql). 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 <strong>CALL sp_MSins_*article_name*</strong>, where _**article_name**_ is the value specified for **\@article**.  
  
    -   **\@del_cmd** - specify a value of <strong>CALL sp_MSdel_*article_name*</strong> or <strong>XCALL sp_MSdel_*article_name*</strong>, where _**article_name**_ is the value specified for **\@article**.  
  
    -   **\@upd_cmd** - specify a value of <strong>SCALL sp_MSupd_*article_name*</strong>, <strong>CALL sp_MSupd_*article_name*</strong>, <strong>XCALL sp_MSupd_*article_name*</strong>, <strong>MCALL sp_MSupd_*article_name*</strong>, 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](../transactional/transactional-articles-specify-how-changes-are-propagated.md).  
  
     For more information, see [Define an Article](define-an-article.md).  
  
2.  At the Publisher on the publication database, use the [ALTER PROCEDURE](/sql/t-sql/statements/alter-procedure-transact-sql) statement to edit [sp_scriptpublicationcustomprocs](/sql/relational-databases/system-stored-procedures/sp-scriptpublicationcustomprocs-transact-sql) so that it returns a [CREATE PROCEDURE](/sql/t-sql/statements/create-procedure-transact-sql) script for the insert, update, and delete custom stored procedures. For more information, see [Specify How Changes Are Propagated for Transactional Articles](../transactional/transactional-articles-specify-how-changes-are-propagated.md).  
  
#### To change the method of propagating changes for an existing article  
  
1.  At the Publisher on the publication database, execute [sp_changearticle](/sql/relational-databases/system-stored-procedures/sp-changearticle-transact-sql). 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.  
  
## See Also  
 [Specify How Changes Are Propagated for Transactional Articles](../transactional/transactional-articles-specify-how-changes-are-propagated.md)   
 [Create a publication](create-a-publication.md)