FIX: Replication Filter Stored Procedures Not Upgraded to 6.5

• Microsoft SQL Server, version 6.5

SYMPTOMS

Replication filter stored procedures created in Microsoft SQL Server 6.0 are not upgraded during the upgrade process to Microsoft SQL Server 6.5.

Because these procedures are not upgraded, any articles that rely on these procedures may not have their replication jobs processed. No error is provided by replication when running the filter procedure under these conditions. The upgrade process (either by an actual upgrade using Setup.exe or LOAD DATABASE) should provide a message in the SQL Server errorlog or Upgrad1b.out file (found in the Mssql\Install directory) indicating a problem upgrading a replication filter procedure.

NOTE: Any procedure not upgraded will be listed by name. The error does not indicate it is a replication filter procedure.

NOTE: In Microsoft SQL Server 6.0 and 6.5, a replication filter stored procedure is designated in sysobjects with a type = 'RF' value.

WORKAROUND

To properly upgrade any replication filter procedure, you must drop and re- create the replication filter procedure. There are basically two techniques to properly re-create a replication filter procedure:

• Drop and re-create the article that is associated with the procedure using SQL Enterprise Manager. Specifying a restriction clause when creating the article automatically generates a filter stored procedure.
• Manually drop and re-create the procedure by using the Transact-SQL commands DROP PROCEDURE and CREATE PROCEDURE. If you do not have a script to perform this operation, note that the syscomments table does not contain the keyword FOR REPLICATION necessary to identify a replication filter procedure when running CREATE PROCEDURE. Furthermore, if you do not use SQL Enterprise Manager to associate the filter procedure with an article, you can directly run master.dbo.sp_changearticle. See the Transact-SQL Reference for more information on running sp_changearticle to specify a filter procedure for an article.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider.

MORE INFORMATION

Because SQL Server 6.5 Service Pack 3 is applied after upgrading from SQL Server 6.0 to SQL Server 6.5, replication filter procedures are not upgraded automatically by just applying the service pack. However, after upgrading to SQL Server 6.5 and applying Service Pack 3, you can run the stored procedure master.dbo.sp_db_upgrade '<db>' to upgrade all replication filter procedures. Sp_db_upgrade is documented in the SQL Server Setup 6.0 book in Chapter 7, "Installation Troubleshooting."

If you load a database from SQL Server 6.0 with replication filter procedures into a server running SQL Server 6.5 Service Pack 3, the procedures will be upgraded correctly without requiring any further action.