Steps for implementing a request stored procedure

To implement a request stored procedure, perform the following steps:

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

As necessary, set up a route from the replicate Replication Server to the primary Replication Server where the data is updated, and from the primary Replication Server to the replicate Replication Server that sends the update.

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

Create a login name and password at the primary Replication Server for the user at the replicate Replication Server.

See Chapter 8, “Managing Replication Server Security” in the Replication Server Administration Guide Volume 1 for details.

At the replicate Replication Server, create the necessary permissions for this user to execute the stored procedure at the primary Replication Server.

See Chapter 8, “Managing Replication Server Security” in the Replication Server Administration Guide Volume 1 for details.

At the primary Replication Server, locate or create a replication definition 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.

The replicate Replication Server may have subscriptions on the replication definition.

Create the stored procedure, which does not perform any updates, on the replicate database. For example:

create proc upd_emp
 @emp_id int, @salary float
 as
 print "Transaction accepted."

If you want the stored procedure to have the same name as those in different replicate databases, see “Specifying a nonunique name for a user-defined function” for details.

In the replicate 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 374 in the Replication Server Administration Guide Volume 1 for details on using sp_setrepproc.

Create a stored procedure on the primary database with the same name as the stored procedure on the replicate database, but do not mark the procedure as replicated. This stored procedure modifies a 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

The stored procedure names on the primary and replicate databases can differ if you alter the function string for the function to execute a stored procedure with a different name. See “Mapping to a different stored procedure name” for more information.

Grant permission on the stored procedure to the replicate Replication Server users who will execute this stored procedure. For example:

grant all on upd_emp to public

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)

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

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