To implement a request function with versions earlier than 15.1:
Review the requirements described in “Prerequisites and restrictions”.
In the primary Adaptive Server, create a login name and password for the user who executes the stored procedure at the replicate Adaptive Server.
See Chapter 8, “Managing Replication Server Security” in the Replication Server Administration Guide Volume 1 for details.
In the primary database, create a stored procedure that updates the primary 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 a primary database 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. In request function delivery with versions earlier than 15.1, only the stored procedure in the replicate database is marked as replicated.
However, if the primary database is also part of a warm standby application, then mark the stored procedure in the active and standby primary databases as replicated if you want to replicate the stored procedures to the standby database.
In the primary database, grant execute permission on the stored procedure to the same user for whom you created a login name and password in step 2. For example:
grant execute on update_pubs to pubs_user
In the replicate database, create a stored procedure with the same parameters and datatypes as the stored procedure in the primary database. The new stored procedure must either do nothing or display a message to indicate a pending update. 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 database from the one created in the primary database.
You must use a different name if the function will replicate back
to the replicate database as an applied function. When you create
the function replication definition in step 8, you must specify
the name of the stored procedure in the destination (primary) database.
In the replicate 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 replicate database, grant execute permission on the stored procedure to the replicate Replication Server user who invokes it. For example:
grant execute on update_pubs_request to pubs_user
In the primary Replication Server that manages the primary data, create a function replication definition for the stored procedure in the replicate database. For example:
create function replication definition update_pubs_request with primary at TOKYO_DS.pubs2 deliver as 'update_pubs' (@pub_id char(4), @pub_name varchar(40))
The function replication definition must use the same name, parameters 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 function replication definition command.
In the preceding example, the optional deliver as clause specifies that the stored procedure to run at the primary data server is named update_pubs, not update_pubs_request.
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 you want 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.
Verify that all the Replication Server and the database objects in steps 1 through 9 exist at their appropriate locations. You must now be able to run the request function.
See Chapter 6, “Adaptive Server 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.
