Implementing a Request Function

Learn how to create replication objects and execute commands to implement a request function.

1. Review the prerequisites and restrictions for request functions.
2. In the replicate Adaptive Server, to manage security, create a login name and password for the user who will execute the stored procedure at the replicate Adaptive Server.
3. 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 Users 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.

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

5. 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."

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

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

8. 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 Replication Server Reference Manual > Replication Server Commands > create request function replication definition.

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

10. 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 Replication Server Reference Manual > Replication Server Commands > create subscription.

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

    See Replication Server Reference Manual > RSSD Stored Procedures for information about stored procedures, such as rs_helpfunc, that you can use to query the RSSD for information about the replication system.