If you use function replication definitions, do not attempt to replicate affected data using table replication definitions and subscriptions. If the stored procedures are identical, they will make identical changes to each database. If the affected tables are also replicated, duplicate updates would result.
The names of all replication definitions, including function replication definitions, must be unique in the replication system. If you want to replicate stored procedures that have the same name, they must use different replication definitions.
To replicate a stored procedure with a non-unique name, invoke it with a stored procedure that has a unique name and that is executed in the source database. For example, the non-unique stored procedure upd_sales may invoke the unique stored procedure upd_salesA or vice versa. Mark the unique stored procedure, upd_salesA, for replication using the system procedure, and leave upd_sales, the stored procedure that invokes it, unmarked.
Alternatively, you can declare the first parameter of the stored procedure with a non-unique name as @rs_repdef and pass the unique name of the replication definition in this parameter when the stored procedure is executed. Do not include the @rs_repdef parameter in the create function replication definition command. This method works only with RepAgent for Adaptive Server.
Replication Server does not support nested transactions within replicated stored procedures, or within stored procedures executed at the replicate database. This means that the stored procedure cannot contain begin or commit statements.
If stored procedures with nested stored procedures are marked for replication with sp_setrepproc, then:
The RepAgent shuts down.
The RepAgent forwards only the outer stored procedure call to the Replication Server.
An error message appears in the Adaptive Server error log.
When the maint_user or the replicate database replicates a stored procedure, using sp_setrepproc or sp_setreplicate, Adaptive Server always executes the stored procedure within a transaction. Even if you have not explicitly executed the replicated stored procedure within a transaction at the primary database, Adaptive Server places an implicit begin transaction at the start of the procedure when it is applied by the maint_user in the replicate database.
If the replicated stored procedure contains nested transaction commands, Replication Server replicates several transactions together, as one group, using only the first begin transaction and the last commit transaction of all the grouped transactions. For more information, see dsi_max_xacts_in_group, in “Replication Server parameters that affect performance” on page 129. If the replicated stored procedure contains such commands as begin transaction, commit transaction, or rollback transaction, errors may result when you execute the procedure. For example, a rollback transaction command might roll back to the start of the transaction group, rather than to the nested begin transaction command that was the intended rollback point.
Replicated functions, like Adaptive Server stored procedures, cannot contain parameters with text and image datatypes. Refer to the Adaptive Server Enterprise Reference Manual for more information.
Adaptive Server logs a replicated stored procedure invocation in the database in which the enclosing transaction was started.
If the user does not begin a transaction explicitly, Adaptive Server begins one in the user’s current database before the stored procedure is invoked.
If the user begins the transaction in one database and then executes a replicated stored procedure in another database, the execution is still be logged in the database where the transaction began.
If a single transaction invokes one or more request functions and executes applied functions or contains data modification language (termed a “mixed-mode” transaction), Replication Server processes the request functions after all the other operations. All request operations are processed together in a separate transaction. This can occur if a single Replication Server manages both primary and replicate data.
When you use replicated functions and heterogeneous datatype translations:
You cannot alter the datatype of a parameter value using create function replication definition or alter function replication definition. However, you can use datatype definitions to declare parameters for applied function replication definitions, which are then subject to class-level translations.
Replication Server does not perform translations on parameter values for request functions. However, during function-string mapping, the delimiters defined for the parameter values of their declared datatype are used to generate the SQL.
Do not put a commit statement inside a replicated function as this may cause a duplicate key and make Replication Server recovery fail.
