FIX: sp_scriptpublicationcustomprocs Generates Replication Stored Procedures (299903)

SYMPTOMS

When a transactional publication is set up in SQL Server 2000 and automatic synchronization is chosen for the subscriber, an insert, update and delete stored procedure is created at the subscriber for each table that is included in the publication. Scripts that contain these stored procedures are created by the snapshot agent and applied at the subscriber during the initial synchronization process by the distribution agent. If the subscription is manually synchronized these stored procedures are not created and must be created manually. Because the stored procedures do not exist on the subscriber under such circumstances (manual synchronization), subsequent inserts by the distribution job generate the following error message:

Could not find stored procedure 'sp_MSins_tablename'

Updates and deletes generate similar error messages related to the update and delete stored procedures.

RESOLUTION

To resolve this problem, obtain the latest service pack for SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

MORE INFORMATION

SQL Server 2000 provides the following stored procedures to assist in creating these stored procedures:

sp_scriptdelproc
sp_scriptinsproc
sp_scriptmappedupdproc
sp_scriptupdproc
sp_scriptxdelproc
sp_scriptxupdproc

Sometimes it is difficult to know which of these stored procedures to use. Even when it is obvious which stored procedures to use it can be a tedious and time consuming process to create the insert, update and delete stored procedures at the subscriber, especially when there are numerous tables in the publication.

Service Pack 1 for SQL Server 2000 includes a stored procedure that generates a script to create the script insert, update and delete stored procedures for every table in a transactional publication. The sp_scriptpublicationcustomprocs stored procedure is created in the master database when Service Pack 1 is applied to a SQL Server 2000 installation.

You can also check the buffer size of SQL Query Analyzer where you are trying to generate the code. By default, the buffer length is set to 255 characters. If the table has many columns, the generated code for various stored procedures may be truncated. Increase the buffer size to get the correct code. In SQL Query Analyzer, on the Tools menu, point to Options, and then click Results. Increase the maximum characters per column setting.

REFERENCES

For information about the syntax and examples of using the sp_scriptpublicationcustomprocs stored procedure, see the "sp_scriptpublicationcustomprocs" topic in SQL Server 2000 Updated Books Online. For additional information about how to manually synchronize replication subscriptions, click the following article number to view the article in the Microsoft Knowledge Base:

320499 HOW TO: Manually Synchronize Replication Subscriptions by Using Backup or Restore