create function replication definition

Description

Creates a function replication definition and user-defined function for a stored procedure that is to be replicated.

Syntax

create function replication definition
        function_rep_def
with primary at data_server.database
[deliver as 'proc_name']
        ([@param_name datatype [, @param_name datatype]...])
        [searchable parameters (@param_name
         [, @param_name]...)]
        [send standby {all | replication definition}
        parameters]

Parameters

Examples

Example 1

Creates a function replication definition named titles_frep for a function and stored procedure of the same name. The primary data is in the pubs2 database in the LDS data server. Use a function replication definition like this for an applied function:

create function replication definition titles_frep
 with primary at LDS.pubs2
 (@title_id varchar(6), @title varchar(80),
  @type char(12), @pub_id char(4),
  @price money, @advance money,
  @total_sales int)
  searchable parameters (@title_id, @title)

Example 2

Creates a function replication definition named titles_frep for a function and stored procedure of the same name, as in the previous example. In this case, the stored procedure to be invoked in the destination database is named upd_titles. Use a function replication definition like this for a request function:

create function replication definition titles_frep
 with primary at LDS.pubs2
 deliver as 'upd_titles'
 (@title_id varchar(6), @title varchar(80),
  @type char(12), @pub_id char(4),
  @price money, @advance money,
  @total_sales int)
  searchable parameters (@title_id, @title)

Usage

• When you create a function replication definition, Replication Server automatically creates a corresponding user-defined function.

• For each of the three system-provided function-string classes in which the user-defined function associated with this function replication definition will be used, and for each derived class that inherits from these classes, Replication Server generates a default function string for the user-defined function.

• You can customize the function string in rs_sqlserver_function_class and in user-created function-string classes using create function string.

• For each user-created base function-string class in which the user-defined function will be used, and for each derived class that inherits from such a class, you must create a function string, using create function string. The function string should invoke a stored procedure or RPC, with language appropriate for the replicate data server.

• For an overview of function-string classes, function strings, and functions, see the Replication Server Administration Guide Volume 2.

• Use the with primary at clause to specify the data server and database containing the primary data. This is not necessarily the database that contains the invoked stored procedure.

  For applied functions (primary-to-replicate function replication) and request functions (replicate-to-primary function replication), create the function replication definition at the Replication Server managing the primary data, and specify the primary database using the with primary at clause.

• Use the optional deliver as clause to specify the name of the stored procedure to execute at the destination database where you are delivering the replicated function. If you do not use this clause when you create or alter the function replication definition, the function is delivered as a stored procedure with the same name as the function replication definition.

  In a warm standby database the stored procedure has the same name as in the active database so the deliver as clause is ignored.

  Typically, you would use the deliver as clause for request function delivery; that is, when a function is replicated from a replicate Replication Server to a primary Replication Server. This way, the name of the replicated function is not the same as the stored procedure that is executed.

  Use this method with “round-trip” stored procedure replication, where the primary Replication Server that is the destination for the request function executes an applied function, to which the originating replicate Replication Server in turn subscribes.

  See the Replication Server Administration Guide Volume 1 for more information.

• Although you cannot create function replication definitions that alter the datatype of a parameters value, you can use HDS datatype definitions to declare parameters for applied function replication definitions. Such parameters are then subject to class-level translations. See the Replication Server Administration Guide Volume 1 for more information about HDS.

• Replication Server does not perform translations on parameter values for request functions. Note, however, that during function-string mapping Replication Server uses the delimiters defined for the parameter values of their declared datatype to generate SQL.

• Use alter function replication definition to add parameters or searchable parameters to an existing function replication definition. You can also specify a new stored procedure name to use when delivering the replicated function at the destination database.

• If you need to remove or rename parameters in function replication definition, you must drop all subscriptions to the function replication definition (applied functions only). Then drop the function replication definition and re-create it.

• In order to subscribe to a function replication definition, use create subscription with the without materialization clause, or use define subscription and the other commands involving bulk materialization.

• In replicating stored procedures through applied functions, it is advisable to create table replication definitions and subscriptions for the same tables that the replicated stored procedures will affect. By doing this, you can ensure that any normal transactions that affect the tables will be replicated as well as the stored procedure executions.

  DML inside stored procedures marked as replicated is not replicated through table replication and you must subscribe to the stored procedure even if you have subscribed to the table.

• If you plan to use both kinds of replication definition for the same table, you can materialize the table data with the subscription for the table replication definition. Then you can create the subscription for the function replication definition using create subscription with the without materialization clause.

Permissions

create function replication definition requires “create object” permission.

See also