Using Custom Stored Procedures in Articles

When the Log Reader Agent encounters an INSERT, UPDATE, or DELETE statement marked for replication in the transaction log of a publication database, it usually reconstructs a single row Transact-SQL statement from the recorded data changes. The Distribution Agent then sends that reconstructed Transact-SQL statement to each Subscriber and applies the statement to the destination table in each destination database. This is the default data replication mechanism used by Microsoft® SQL Server™ when there are one or more heterogeneous Subscribers.

If all Subscribers are SQL Server databases, SQL Server can override the INSERT, UPDATE, and DELETE statements from the transaction log with custom stored procedures at each Subscriber. For each published table, there are three ways you can handle each type of statement (INSERT, UPDATE, or DELETE) detected by the Log Reader Agent. You can:

Leave the default replication mechanism in place.
Specify that no action will be taken at any Subscriber. Transactions of that type are not replicated. For example, if you select Replace DELETE statements with this stored procedure and enter NONE, then DELETE statements are not replicated for that article.
Specify that a custom procedure will be called at all Subscribers. When the Log Reader Agent encounters a statement of the specified type (INSERT, UPDATE, or DELETE) in a transaction marked for replication, it constructs a stored procedure call based on this syntax and passes column values to the referenced stored procedure. This is the default behavior for SQL Server Subscribers.

About Custom Stored Procedures

To implement custom, stored procedure–based replication for a published table, stored procedures must be created by either replication or the user. These custom stored procedures expect to receive and process these parameters:

call Syntax

INSERT stored procedures: Stored procedures handling INSERT statements will be passed the inserted values for all columns:

c1, c2, c3,... cn

UPDATE stored procedures: Stored procedures handling UPDATE statements will be passed the updated values for all columns defined in the article, followed by the original values for the primary key columns:

c1, c2, c3,... cn, pkc1, pkc2,... pkcn

Note No attempt is made to determine which columns were changed.

DELETE stored procedures: Stored procedures handling DELETE statements will be passed values for the primary key columns:

pkc1, pkc2,... pkcn

mcall Syntax

UPDATE stored procedures: Stored procedures handling UPDATE statements will be passed the updated values for all columns defined in the article, followed by the original values for the primary key columns, followed by a bitmask (binary(n)) parameter that indicates the changed columns:

c1, c2, c3,... cn, pkc1, pkc2,... pkcn, bitmask

xcall Syntax

UPDATE stored procedures: Stored procedures handling UPDATE statements will be passed the original (that is, the "before" image) values for all columns defined in the article, followed by the update (that is, the "after" image) values for all columns defined in the article.

old-c1, old-c2, old-c3,... old-cn, c1, c2, c3,... cn,

DELETE stored procedures: Stored procedures handling UPDATE statements will be passed the original (that is, the "before" image) values for all columns defined in the article,

old-c1, old-c2, old-c3,... old-cn

If you would like your INSERT, UPDATE, or DELETE stored procedure to return an error when a failure status is encountered, you must add a RAISERROR statement so that the distributor will catch the failure status coming back. If severity is greater than 12, it stops the distribution process to that subscriber. If this procedure definition is distributed as part of the article schema definition file, it will be sent using ODBC. In this case, only single quotes (') may be used to define the RAISERROR message string, because use of double quotes (") will generate an error.

Indicate your choice when you specify article properties in the Create Publication Wizard. You can also make this choice on the Properties dialog box for the article.

To replace statements with custom stored procedures in an article