In this example, a client application at the replicate (Sydney) site executes the stored procedure upd_publishers_pubs2_req, which makes no changes to the replicate database but causes an associated stored procedure, upd_publishers_pubs2, to execute and change data on the primary (Tokyo) site. upd_publishers_pubs2_req is an empty-body stored procedure.
Perform these tasks to create a request function. No subscriptions are necessary at the primary database; function executions at a site other than the primary for the replication definition automatically flow to the primary site.
Create an empty-body stored procedure at the replicate data server, and
Mark the procedure for replicated function delivery using sp_setrepproc.
Grant appropriate permissions to the procedure to the appropriate user.
The empty-body procedure either does nothing or displays a message indicating a pending update. It may have a different name than the associated stored procedure in the primary database.
Create a stored procedure that updates primary data. Grant permissions to the appropriate user.
Create a function replication definition at the primary Replication Server for the empty-body stored procedure in the replicate database. The function replication definition and the empty-body stored procedure have the same name.
Once the primary data has been updated via the replication of the request stored procedure, data can be delivered to replicate sites (including the requestor) via either table replication or applied function replication.
It is not necessary to create a subscription for a request
function.
Figure 3-12: Request functions
The script on creates the user stored procedure upd_publisher_pubs2 at the primary site.
This script creates the empty-body stored procedure upd_publishers_pubs2_req.
-- Execute this script at Sydney replication server
-- Creates stored procedure
upd_publishers_pubs2_req
create procedure upd_publishers_pubs2_req
(@pub_id char(4),
@pub_name varchar(40),
@city varchar(20),
@state char(2))
as
begin
if (select 1) > 1
print "Submitting request."
end
go
/* end of script */
This script creates the function replication definition at the primary Replication Server. It has the same name as the empty-body stored procedure. The deliver as clause tells the primary data server to execute upd_publisher_pubs2 at the primary site.
-- Execute this script at Tokyo replication server
-- Creates replication definition
create function replication definition upd_publishers_pubs2_req
with primary at TOKYO_DS.pubs2
deliver as 'upd_publisher_pubs2'
(@pub_id char(4),
@pub_name varchar(40),
@city varchar(20),
@state char(2))
go
/* end of script */
