The applied and request function are very similar. The difference is that the maintenance user executes the applied function at the replicate site, while the same user who executes the stored procedure at the primary database executes the request function at the replicate site.
To implement an applied function:
Review the requirements described in “Prerequisites and restrictions”.
Set up replicate databases containing replicate tables that the stored procedure will modify.
In the primary database, create the stored procedure. The stored procedure may or may not modify primary data. For example, this stored procedure uses the @pub_name parameter to update the pub_name column of the publishers table:
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
In the primary database, mark the stored procedure for replicated function delivery, using the sp_setrepproc system procedure. For example:
sp_setrepproc update_pubs, 'function'
See “Marking stored procedures for replication” for details.
In the replicate database, create a stored procedure with the same parameters and datatypes as the stored procedure in the primary database. Typically, the two stored procedures perform the same operations. 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
The stored procedure created in the replicate database
does not have to have the same name, but must have the same parameter
name and datatype.
WARNING! A stored procedure invoked in a replicate database in applied 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 applied function delivery, only the stored procedure in the primary database is marked as replicated.
However, if the replicate database modifies a standby database, mark the stored procedure in the active and standby replicate databases as replicated if you want to use stored procedure replication to the standby.
In the replicate database, grant execute permission on the stored procedure to the maintenance user. For example:
grant execute on update_pubs to maint_user
In the primary Replication Server, create an applied function replication definition for the stored procedure. For example:
create applied function replication definition update_pubs_rep with primary at TOKYO_DS.pubs2 with all functions named update_pubs (@pub_id char(4), @pub_name varchar(40), @state char (2)) searchable parameters (@pub_name, @state)
The function replication definition must use the same parameter names and datatypes as the stored procedure in the primary database. You have the option to include only the parameters you want to replicate. If the function replication definition has 0 parameters, you must still include the parentheses for this clause.
If you specify searchable parameters, you can subscribe to function invocations based on the value of the function’s parameters. In the preceding example, @pub_name and @state are searchable parameters. Thus, for example, they can subscribe only to “CA” updates.
If you want to replicate the Adaptive Server timestamp datatype, declare the datatype binary(8) in the function replication definition.
Refer to Chapter 3, “Replication Server Commands,” in the Replication Server Reference Manual for more information about create applied function replication definition command.
See “Modifying or dropping replicated functions” for information about changing function replication definitions.
When you create a function replication definition, Replication Server automatically creates a corresponding function in the default function-string class. See “User-defined functions” on page 14 in the Replication Server Administration Guide Volume 2 for more information.
If you are not using a default function-string class or a class inherited from the default or if 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.
In the replicate Replication Server, create a subscription to the function replication definition, using create subscription and the no-materialization method or define subscription and the other bulk materialization commands.
You must use the no-materialization method or bulk materialization—instead
of atomic or nonatomic materialization—because Replication
Server cannot determine in advance what data is needed for the stored
procedure at the replicate site.
For example:
create subscription pubs_sub for update_pubs_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.
Refer to 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”.
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 applied 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.
