FIX: Incorrect Parameter Numbering Occurs in Custom Stored Procedures That Are Generated with the Sp_scriptpublicationcustomprocs Stored Procedure

This article has been archived. It is offered "as is" and will no longer be updated.
SYMPTOMS
When you try to run custom stored procedures that were generated with the sp_scriptpublicationcustomprocs stored procedure, you receive the following Error 137 message
Must declare the variable '%.*ls'
if all the following conditions are true:
  • Immediate-updating subscriptions are permitted on the publication (@allow_sync_tran = 'True').
  • Queuing of changes at the subscribers is enabled (@allow_queued_tran = 'True').
  • The table that has the incorrect custom stored procedures that were created with the sp_scriptpublicationcustomprocs stored procedure contains computed columns.
  • The table that has the incorrect custom stored procedures that were created with the sp_scriptpublicationcustomprocs stored procedure has unique indexes.
RESOLUTION

Service pack information

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

Hotfix information

Follow the steps in the "More Information" section to install and to activate this fix.

The English version of this fix has the file attributes (or later) that are listed in the following table. The dates and times for these files are listed in coordinated universal time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.
   Date         Time   Version            Size    File name   ----------------------------------------------------------------------   31-May-2003  09:45  2000.80.818.0      78,400  Console.exe         24-Jun-2003  16:01  2000.80.818.0      33,340  Dbmslpcn.dll   24-Apr-2003  17:12                    786,432  Distmdl.ldf   24-Apr-2003  17:12                  2,359,296  Distmdl.mdf   29-Jan-2003  16:55                        180  Drop_repl_hotfix.sql   23-Jun-2003  13:40  2000.80.837.0   1,557,052  Dtsui.dll   23-Jun-2003  13:40  2000.80.837.0     639,552  Dtswiz.dll   23-Apr-2003  17:51                    747,927  Instdist.sql   02-May-2003  16:56                      1,581  Inst_repl_hotfix.sql   07-Feb-2003  21:40  2000.80.765.0      90,692  Msgprox.dll   31-Mar-2003  17:07                      1,873  Odsole.sql   04-Apr-2003  16:46  2000.80.800.0      62,024  Odsole70.dll   07-May-2003  11:41  2000.80.819.0      25,144  Opends60.dll   02-Apr-2003  12:48  2000.80.796.0      57,904  Osql.exe   02-Apr-2003  14:15  2000.80.797.0     279,104  Pfutil80.dll   04-Aug-2003  09:17                    550,780  Procsyst.sql   22-May-2003  13:57                     19,195  Qfe469571.sql   11-Jul-2003  08:04                  1,084,147  Replmerg.sql   04-Apr-2003  12:53  2000.80.798.0     221,768  Replprov.dll        07-Feb-2003  21:40  2000.80.765.0     307,784  Replrec.dll   13-Aug-2003  07:28                  1,086,797  Replsys.sql   13-Aug-2003  07:28                    986,603  Repltran.sql   29-Jul-2003  11:13  2000.80.819.0     492,096  Semobj.dll   31-May-2003  09:27  2000.80.818.0     172,032  Semobj.rll   05-Aug-2003  12:06                    127,884  Sp3_serv_uni.sql   31-May-2003  16:01  2000.80.818.0   4,215,360  Sqldmo.dll   07-Apr-2003  08:44                     25,172  Sqldumper.exe       19-Mar-2003  09:20  2000.80.789.0      28,672  Sqlevn70.rll   01-Jul-2003  15:18  2000.80.834.0     180,736  Sqlmap70.dll   07-Feb-2003  21:40  2000.80.765.0      57,920  Sqlrepss.dll   13-Aug-2003  11:04  2000.80.851.0   7,598,161  Sqlservr.exe   25-Jul-2003  12:44  2000.80.845.0     590,396  Sqlsort.dll   07-Feb-2003  21:40  2000.80.765.0      45,644  Sqlvdi.dll   24-Jun-2003  16:01  2000.80.818.0      33,340  Ssmslpcn.dll   31-May-2003  16:01  2000.80.818.0      82,492  Ssnetlib.dll   31-May-2003  16:01  2000.80.818.0      25,148  Ssnmpn70.dll   31-May-2003  16:01  2000.80.818.0     158,240  Svrnetcn.dll   31-May-2003  09:59  2000.80.818.0      76,416  Svrnetcn.exe   30-Apr-2003  14:52  2000.80.816.0      45,132  Ums.dll   01-Jul-2003  15:19  2000.80.834.0      98,816  Xpweb70.dll				
Note Because of file dependencies, the most recent hotfix or feature that contains the files may also contain additional files.
STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section of this article. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 4.
MORE INFORMATION
To install and activate the fix, follow these steps:
  1. Obtain the fix that is described in the "Resolution" section of this article.
  2. Apply the fix to the affected publishing instance of SQL Server 2000.
  3. Mark the affected subscription for reinitialization:
    • For Push subscriptions:

      In Enterprise Manager, open the Publisher server tree, and then locate the Replication - Subscriptions node. Right-click the affected subscription, and then click Reinitialize.
    • For Pull subscriptions:

      In Enterprise Manager, open the Subscriber server tree, and then locate the Replication - Subscriptions node. Right-click the affected subscription, and then click Reinitialize.
  4. Run the Snapshot Agent for the affected publication.
To reproduce the problem, run the following script in a server that already has replication configured:
USE mastergoCREATE DATABASE TestDatabasegoUSE TestDatabasegoCREATE TABLE [dbo].[TestTable] ([col1] [int] NOT NULL ,[col2] [int] NOT NULL ,[col3] AS ([col1] + [col2]) ,[msrepl_tran_version] [uniqueidentifier] NOT NULL) ON [PRIMARY]goALTER TABLE [dbo].[TestTable] WITH NOCHECK ADDCONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED([col1]) ON [PRIMARY]goALTER TABLE [dbo].[TestTable] ADDCONSTRAINT [DF_TestTable_msrepl_tran_vers] DEFAULT (newid()) FOR[msrepl_tran_version]goCREATE UNIQUE INDEX [IX_T1UNIQUE] ON[dbo].[TestTable]([msrepl_tran_version]) ON [PRIMARY]gouse [TestDatabase]goexec sp_replicationdboption @dbname = N'TestDatabase', @optname = N'publish', @value = N'true'goexec sp_addpublication @publication = N'TestDatabase', @restricted = N'false', 			@sync_method = N'native', @repl_freq = N'continuous', 			@description = N'Transactional publication of TestDatabase database from Publisher NAURU-SRV-01.', 			@status = N'inactive', @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', 			@enabled_for_internet = N'false', @independent_agent = N'false', @immediate_sync = N'false', 			@allow_sync_tran = N'true', @autogen_sync_procs = N'true', @retention = 336, 			@allow_queued_tran = N'true', @snapshot_in_defaultfolder = N'true', 			@compress_snapshot = N'false', @ftp_port = 21, @allow_dts = N'false', 			@allow_subscription_copy = N'false', @conflict_policy = N'pub wins', 			@centralized_conflicts = N'true', @conflict_retention = 14, 			@queue_type = N'sql', @add_to_active_directory = N'false'exec sp_addpublication_snapshot @publication = N'TestDatabase',@frequency_type = 4, @frequency_interval = 1, 				@frequency_relative_interval = 0, @frequency_recurrence_factor = 1, 				@frequency_subday = 1, @frequency_subday_interval = 0, @active_start_date = 0, 				@active_end_date = 20030814, @active_start_time_of_day = 224200, @active_end_time_of_day = 0exec sp_addarticle @publication = N'TestDatabase', @article = N'TestTable', @source_owner = N'dbo', 				@source_object = N'TestTable', 				@destination_table = N'TestTable', @type = N'logbased', @creation_script = null, 				@description = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000000CEF3, 				@status = 16, @force_invalidate_snapshot = 1, @vertical_partition = N'false', 				@ins_cmd = null, @del_cmd = null, @upd_cmd = null, @filter = null, @sync_object = null, 				@auto_identity_range = N'false'exec sp_changepublication @publication = N'TestDatabase', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1, 				@property = N'status', @value = N'active'
After the publication is created, if you run the following statement:
exec sp_scriptpublicationcustomprocs 'TestDatabase'

you will receive the following (incorrect) script:
---- Transactional replication custom procedures for publication 'TestDatabase' from database 'TestDatabase':---------- Replication custom procedures for article 'TestTable':----if exists (select * from sysobjects where type = 'P' and name = 'sp_MSins_TestTable')  drop proc [sp_MSins_TestTable]gocreate procedure [sp_MSins_TestTable] @c1 int,@c2 int,@c3 uniqueidentifier,@c4 intASBEGINif not exists (select * from [TestTable]  where  ( [col1] = @c1 )  or  ( [col4] = @c5 ) )BEGINinsert into [TestTable]( [col1], [col2], [msrepl_tran_version], [col4] )values ( @c1, @c2, @c3, @c4 )ENDENDgoif exists (select * from sysobjects where type = 'P'  and name = 'sp_MSupd_TestTable')  drop proc [sp_MSupd_TestTable]gocreate procedure [sp_MSupd_TestTable]  @c1 int,@c2 int,@c3 uniqueidentifier,@c4 int,@c5 int,@c6 int,@c7 uniqueidentifier,@c8 intasif not exists (select * from [TestTable]  where  ( [col4] = @c9 and @c9 != @c5 ) )beginif @c5 = @c1beginupdate [TestTable] set [col2] = @c6,[msrepl_tran_version] = @c7,[col4] = @c8where [col1] = @c1 and msrepl_tran_version = @c3endelsebeginif not exists (select * from [TestTable]  where  ( [col1] = @c5 ) )beginupdate [TestTable] set [col1] = @c5,[col2] = @c6,[msrepl_tran_version] = @c7,[col4] = @c8where [col1] = @c1 and msrepl_tran_version = @c3endendendgoif exists (select * from sysobjects where type = 'P' and name = 'sp_MSdel_TestTable')  drop proc [sp_MSdel_TestTable]gocreate procedure [sp_MSdel_TestTable] @c1 int,@c2 int,@c3 uniqueidentifier,@c4 intasdelete [TestTable]where [col1] = @c1 and msrepl_tran_version = @c3go
Notice that the custom insert stored procedure, sp_MSins_TestTable, references a parameter that is named @c5. @c5 is not declared as a parameter for that procedure, and the custom update stored procedure sp_MSupd_TestTable references another undefined parameter named @c9.
Properties

Article ID: 827175 - Last Review: 02/27/2014 21:19:50 - Revision: 3.4

Microsoft SQL Server 2000 Service Pack 3, Microsoft SQL Server 2000 Developer Edition, Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 2000 Enterprise Edition, Microsoft SQL Server 2000 Personal Edition, Microsoft SQL Server 2000, Workgroup Edition, Microsoft SQL Server 2000 Desktop Engine (Windows), Microsoft SQL Server 2000 Enterprise Edition 64-bit

  • kbnosurvey kbarchive kbhotfixserver kbqfe kbqfe kbsqlserv2000presp4fix kbfix kbbug KB827175
Feedback