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 one 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™ 2000 when there are one or more heterogeneous Subscribers.

If all Subscribers are instances of SQL Server 2000, SQL Server 2000 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, DELETE statements are not replicated for that article.
  
  
• Specify that a custom procedure 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 2000 Subscribers.

About Custom Stored Procedures

Depending on the requirements of the application, the parameters of the stored procedures can be specified using:

• CALL syntax
  
  
• XCALL syntax
  
  
• MCALL syntax

Each method differs in the amount of data that is propagated to the Subscriber. For example, MCALL will pass in values only for the columns that are actually affected by the update, and a bitmask representing the changed columns and XCALL will pass in all columns (whether affected by an update or not) and all the old data values for each column. This allows flexibility to application developers with diverse requirements. When using XCALL, the before image values for text and image columns are expected to be NULL.

To implement custom stored procedure–based replication for a published table, stored procedures must be created either by replication or by 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 (the before image) values for all columns defined in the article, followed by the update (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 (the before image) values for all columns defined in the article.

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

If you want 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 capture the failure status coming back. If the severity is greater than 12, the Distributor 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 quotation marks (') can be used to define the RAISERROR message string. The use of double quotation marks (") generates an error.

You can also program a custom stored procedure to skip specified errors. For more information, see Handling Agent Errors.

Indicate whether you want to use single quotation marks or double quotation marks when you specify article properties in the Create Publication Wizard. You can also make this choice in the Properties dialog box for the article.