Implementing an Applied Function

Learn how to create replication objects and execute commands to implement an applied function.

1. Review the prerequisites and restrictions for applied functions.
2. Set up replicate databases containing replicate tables that the stored procedure will modify.
3. 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

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

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

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

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

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

   See Replication Server Reference Manual > Replication Server Commands > create applied function replication definition.

8. When you create a function replication definition, Replication Server automatically creates a corresponding function in the default function-string class. See Replication Server Administration Guide Volume 2 > Customize Database Operations > Work with Functions, Function Strings, and Classes > Functions > User-defined Functions.

   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.

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

   See Replication Server Reference Manual > Replication Server Commands > create subscription.

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

    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.