How to: Publish the Execution of a Stored Procedure in a Transactional Publication (SQL Server Management Studio)

Specify that the execution of a stored procedure (rather than just its definition) should be published in the Article Properties - <Article> dialog box. This dialog box 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).

The definition of the procedure (the CREATE PROCEDURE statement) is replicated to the Subscriber when the subscription is initialized; when the procedure is executed at the Publisher, replication executes the corresponding procedure at the Subscriber.

To publish the execution of a stored procedure

1. On the Articles page of the New Publication Wizard or the Publication Properties - <Publication> dialog box, select a stored procedure.

2. Click Article Properties, and then click Set Properties of Highlighted Stored Procedure.

3. In the Article Properties - <Article> dialog box, specify one of the following values for the Replicate option:

   • Execution of the stored procedure
   • Execution in a serialized transaction of the SP
     This is the recommended option, because it replicates the procedure execution only if the procedure is executed within the context of a serializable transaction. If the stored procedure is executed outside of a serializable transaction, changes to data in published tables are replicated as a series of data manipulation language (DML) statements.

4. Click OK.

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

See Also

Concepts

Publishing Stored Procedure Execution in Transactional Replication

Help and Information

Getting SQL Server 2005 Assistance