Example 1: A basic example using pre15.1 no-subscription request functions

In this example, a client application at the replicate site (Sydney) 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 site (Tokyo).

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.

At the replicate site:

Create an empty-body stored procedure at the replicate data server, and:
- Mark the procedure for the replicated function delivery using sp_setrepproc.
- Grant appropriate permissions to the procedure for 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.

At the primary site:

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) either via table replication or applied function replication.

It is not necessary to create a subscription for a request function.

Figure C-1: Request functions

In Figure C-1, a client application at the replicate site executes an empty-body stored procedure, which makes no changes to the replicate database, but causes an associated stored procedure to execute and change data on the primary site.

Stored procedures

At the primary site:

This script creates the user stored procedure upd_publisher_pubs2 at the primary site.

-- Execute this script at Sydney data server
 -- Creates stored procedure upd_publishers_pub2
create procedure upd_publishers_pubs2
 (@pub_id char(4),
 @pub_name varchar(40),
 @city varchar(20),
 @state char(2))
 as
 insert into publishers
 values
 (@pub_id, @pub_name, @city, @state)
 go
 /* end of script */

At the replicate 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 */

Function replication definition

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 run 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 */