Learn the steps to implement an request stored procedure.
Prerequisites
Verify that you have completed the asynchronous stored procedure prerequisites.
Task
SeeReplication Server Reference Manual > RSSD Stored Procedures for
information about stored procedures used to query the RSSD for system
information.
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.
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.
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.
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.
Create the stored procedure, which does not perform
any updates, on the replicate database.
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.
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.
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.
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 all the exist at the appropriate locations.