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 name, 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
WARNING! A stored procedure invoked in a replicate database in applied 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 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 a function replication definition for the stored procedure. For example:
create function replication definition update_pubs with primary at TOKYO_DS.pubs2 (@pub_id char(4), @pub_name varchar(40), searchable parameters (@pub_name, @state)
The function replication definition must use the same name, parameters, and datatypes as the stored procedure in the primary database. You have the option to include only the parameters you want to replicate. You need not include parameters and their datatypes, but you must include the parentheses for this clause, whether or not you include the parameters.
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 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.
WARNING! 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 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.
