FIX: Procedure for Repl. Can Fail When Referencing NCI

• Microsoft SQL Server, version 6.0

SYMPTOMS

If a published article has a stored procedure created FOR REPLICATION that references a column in a non-clustered index on the article's base table, the filter procedure can prevent a transaction from being replicated. Examining the MSjob_commands table in the distribution database can help you detect this problem.

CAUSE

The SELECT statement in the replication filter procedure is being incorrectly evaluated by SQL Server when applying the filter procedure against the logged change in the published database.

The most common occurrence of this problem is when a "restriction clause" for the article is created using SQL Enterprise Manager where the column listed in the clause is part of a non-clustered index.

This problem can also occur if you develop your own filter procedure using the CREATE PROCEDURE FOR REPLICATION command. This problem does not affect the proper synchronization of data when first subscribing, only changes made after synchronization.

WORKAROUND

1. Drop the non-clustered index.

2. Remove the column referenced in the filter procedure from the non- clustered index.

3. Filter the replicated changes on the subscriber by using custom stored procedures.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.0. This problem was corrected in Service Pack 2 for SQL Server version 6.0. For more information, contact your primary support provider.

MORE INFORMATION

If a "restriction clause" is specified when creating an article using SQL Enterprise Manager, a "filter stored procedure," which can be manually created using the CREATE PROCEDURE FOR REPLICATION command, is generated to include the criteria for the horizontal partition. Procedures of this type are applied by SQL Server as part of processing initiated by the Log Reader task.

For example, for the employee table in the pubs database, an article with a restriction clause of "lname like 'A%'" will result in the creation of the following replication filter procedure automatically by SQL Enterprise Manager:

CREATE PROCEDURE FLTR_employee_Table_1__10 FOR REPLICATION AS IF EXISTS (SELECT * FROM DBO.EMPLOYEE (NOLOCK) WHERE LNAME LIKE 'A%')

   RETURN 1

   RETURN 0

In the above example, any modification affecting the employee table where lname LIKE 'A%' should be replicated to subscribers. However, if a non-clustered index was created on the lname column, these changes would not pass the filter stored procedure test and not be stored in the distribution database.