Steps for implementing an applied stored procedure

To implement an applied stored procedure, perform the following steps:

Review the requirements described in “Asynchronous stored procedure prerequisites”.

Set up replicate databases that contain replicate tables. These tables may or may not match the replication definition for the primary table.

As necessary, set up routes from the primary Replication Server to the replicate Replication Servers that have subscriptions to replication definitions for the primary table.

See Chapter 6, “Managing Routes” in the Replication Server Administration Guide Volume 1 for details on setting up routes.

Locate or create a replication definition on the primary Replication Server that identifies the table to be modified.

See Chapter 9, “Managing Replicated Tables” in the Replication Server Administration Guide Volume 1 for information on creating replication definitions.

In the primary database, use the sp_setreplicate system procedure or the sp_setreptable system procedure to mark the table for replication. For example, for a table named employee:

sp_setreplicate employee, 'true'

sp_setreptable employee, 'true'

For sp_setreptable, the single quotes are optional.

See “Specifying stored procedures and tables for replication” for details on using sp_setreplicate. See “Using the sp_setreptable system procedure” on page 281 in the Replication Server Administration Guide Volume 1 for details on using sp_setreptable.

Create the stored procedure on the primary database. The first statement in the stored procedure must contain an update command for the first row of the primary table. For example:

create proc upd_emp
 @emp_id int, @salary float
 as
 update employee
 set salary = salary * @salary
 where emp_id = @emp_id

WARNING! If the first statement in the stored procedure contains an operation other than update, Replication Server cannot distribute the stored procedure to replicate databases. See “Warning conditions” for more information. Never include dump transaction or dump database commands in the stored procedure. If the stored procedure contains commands with statement level errors, the error may occur at the replicate DSI. Depending on the error actions, the DSI may shut down.

In the primary database, use the sp_setreplicate system procedure or the sp_setrepproc system procedure to mark the stored procedure for replication. For example:

sp_setreplicate upd_emp, 'true'

sp_setrepproc upd_emp, 'table'

See “Specifying stored procedures and tables for replication” for details on using sp_setreplicate. See “Marking stored procedures for replication” on page 352 in the Replication Server Administration Guide Volume 1 for details on using sp_setrepproc.

At the replicate Replication Servers, create subscriptions to a replication definition for the table that the stored procedure at the primary database updates.

See Chapter 11, “Managing Subscriptions” in the Replication Server Administration Guide Volume 1 for details on creating subscriptions.

WARNING! Be sure the replicate database subscribes to both the before image and after image of the updated row. If it does not, Replication Server cannot distribute the stored procedure to the replicate database. See “Warning conditions” for more information.

Create a stored procedure on the replicate database with the same name and parameters as the stored procedure on the primary database, but do not mark the procedure as replicated. For example:

create proc upd_emp
 @emp_id int, @salary float
 as
 update employee
 set salary = salary * @salary
 where emp_id = @emp_id

Grant execute permission on the stored procedure to the maintenance user. For example:

grant execute on upd_emp to maint_user

Create a user-defined function on the primary Replication Server that associates the stored procedure to the name of a replication definition for the table it updates. For example:

create function employee_rep.upd_emp
 (@emp_id int, @salary float)

Only one user-defined function are shared by all replication definitions for the same table. You can specify the name of any of these replication definitions.

Verify that all Replication Server and database objects in steps 1 through 11 exist at the appropriate locations.

Refer to Chapter 6, “Adaptive Server Stored Procedures,” in the Replication Server Reference Manual for information about stored procedures used to query the RSSD for system information.