Microsoft KB Archive/299903

= FIX: sp_scriptpublicationcustomprocs Generates Replication Stored Procedures =

Article ID: 299903

Article Last Modified on 2/22/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Enterprise Edition
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2000 Developer Edition

-



This article was previously published under Q299903



BUG #: 352963 (SHILOH_BUGS)



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



STATUS
Microsoft has confirmed that this is a problem in SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.



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.

