The applied and request function are very similar. The difference is that the maintenance user executes the applied function at the replicate site and the same user who executes the stored procedure at the primary database executes the request function at the replicate site.
To implement a request function:
Review the requirements described in “Prerequisites and restrictions”.
In the replicate Adaptive Server, create a login name and password for the user who will execute the stored procedure at the replicate Adaptive Server.
See Chapter 8, “Managing Replication Server Security” for details.
In the replicate database, create a replicate stored procedure that updates the real data. For example:
create proc update_pubs @pub_id char(4), @pub_name varchar(40) as update publishers set pub_name = @pub_name where pub_id = @pub_id
WARNING! A stored procedure invoked in request function delivery is invoked inside a user-defined transaction. See the Adaptive Server Enterprise Transact-SQL User’s Guide for information about operations that are not allowed inside user-defined transactions (for example, the dump transaction and dump database commands).
Do not mark this stored procedure as replicated; however, if this database is also part of a warm standby application, then mark the stored procedure in the active database as replicated if you want to replicate stored procedures to the standby database.
In the replicate database, grant execute permission on the stored procedure to the same user for whom you created a login name and password in step 2. When the request function is replicated in the replicate database, this user executes it. For example:
grant execute on update_pubs to pubs_user
In the primary database, create a request primary stored procedure with the different name, but the same parameters and datatypes as the stored procedure in the replicate database. The new stored procedure should either do nothing or should display a message to indicate a pending update. Typically, the purpose of this stored procedure is to send a request to other databases, instead of performing any data changes on its own database. For example:
create proc update_pubs_request @pub_id char(4), @pub_name varchar(40) as print "Transaction accepted."
Use a different name for the stored procedure you create
in the replicate and primary databases. In the typical applications,
the function will replicate back to the primary database later as
an applied function. When you create the request function replication
definition in step 8, you must specify the name of the stored procedure
in the primary and replicate databases.
In the primary database, mark the stored procedure for replicated function delivery using the sp_setrepproc system procedure. For example:
sp_setrepproc update_pubs_request, 'function'
See “Marking stored procedures for replication” for details.
In the primary database, grant execute permission on the stored procedure to the primary Replication Server user who will invoke it. For example:
grant execute on update_pubs_request to pubs_user
In the primary Replication Server, which manages the request primary stored procedure, create a request function replication definition for this stored procedure. For example:
create request function replication definition update_pubs_request_rep with primary at TOKYO_DS.pubs2 with primary function named update_pubs_request with replicate function named update_pubs (@pub_id char(4), @pub_name varchar(40)), @state char (2)) searchable parameters ( @state)
The request function replication definition must use the same parameter names and datatypes as the stored procedure in the replicate database. You have the option to include only the parameters you want to replicate.
See Chapter 3, “Replication Server Commands,” in the Replication Server Reference Manual for more information about create request function replication definition command.
When you create a function replication definition, Replication Server automatically creates a corresponding user-defined function.
If you are not using a default function string or wish to customize the function’s invocation, you need to create a function string for the user-defined function. See “Creating or modifying a function string for a replicated function” for more information.
In the replicate Replication Server, create a subscription to the request function replication definition, using create subscription and the no materialization method or define subscription and the other bulk materialization commands. For example:
create subscription pubs_sub for update_pubs_request_rep with replicate at SYDNEY_DS.pubs2 where @state = ‘CA’ without materialization
If you specified searchable parameters in the function replication definition, you can subscribe to function invocations based on the value of the function’s parameters. In this example, the subscription only receives rows if the value of the @state parameter is equal to “CA”.
See Chapter 3, “Replication Server Commands,” in the Replication Server Reference Manual for more information about create subscription command. See also “Using create subscription for no materialization”.
You must use the no-materialization method or bulk materialization, instead
of atomic or nonatomic materialization because the Replication Server
cannot determine in advance what data is needed for the stored procedure
at the replicate site.
Verify that all Replication Server and database objects in steps 1 through 10 exist at the appropriate locations. You should now be able to execute the request function at the primary database.
Refer to Chapter 6, “RSSD Stored Procedures,” in the Replication Server Reference Manual for information about stored procedures, such as rs_helpfunc, that you can use to query the RSSD for information about the replication system.
