To implement a request function:
Review the requirements described in “Prerequisites and restrictions”.
In the primary 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 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. Refer to 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, 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 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 should either do nothing or should 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."
Sybase recommends that you use a different name for
the stored procedure you create in the replicate 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 will invoke it. For example:
grant execute on update_pubs_request to pubs_user
In the primary Replication Server, which 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.
Refer to 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 execute 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 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.
Verify that all Replication Server and database objects in steps 1 through 9 exist at the appropriate locations. You should now be able to execute the request function.
Refer to 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.
