Implementing a Request Stored Procedure

Learn the steps to implement an request stored procedure.

1. 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 Replication Server Administration Guide Volume 1 > Manage Routes.

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

   See Replication Server Administration Guide Volume 1 > Manage Replication Server Security.

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

   See Replication Server Administration Guide Volume 1 > Manage Replication Server Security.

4. At the primary Replication Server, locate or create a replication definition that identifies the table to be modified.

   See Replication Server Administration Guide Volume 1 > Manage Replicated Tables for information on creating replication definitions.

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

5. 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, follow the guidelines for specifying a nonunique name for a user-defined function.

6. In the replicate database, use the sp_setreplicate or sp_setrepproc system procedure to mark the stored procedure for replication.
   For example, enter one of:

   sp_setreplicate upd_emp, 'true'

   Follow the guidelines when specifying stored procedures and tables and for replication.

   or

   sp_setrepproc upd_emp, 'table'

   See Replication Server Administration Guide Volume 1 > Manage Replicated Functions > Mark Stored Procedures for Replication.
7. 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

   Note: 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.

   You can map the function to a different stored procedure name.

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

9. 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)

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