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

MORE INFORMATION

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.

USE master
go
CREATE DATABASE TestDatabase
go
USE TestDatabase
go

CREATE TABLE [dbo].[TestTable] (
[col1] [int] NOT NULL ,
[col2] [int] NOT NULL ,
[col3] AS ([col1] + [col2]) ,
[msrepl_tran_version] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
go

ALTER TABLE [dbo].[TestTable] WITH NOCHECK ADD
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
(
[col1]
) ON [PRIMARY]
go

ALTER TABLE [dbo].[TestTable] ADD
CONSTRAINT [DF_TestTable_msrepl_tran_vers] DEFAULT (newid()) FOR
[msrepl_tran_version]
go

CREATE UNIQUE INDEX [IX_T1UNIQUE] ON
[dbo].[TestTable]([msrepl_tran_version]) ON [PRIMARY]
go

use [TestDatabase]
go
exec sp_replicationdboption @dbname = N'TestDatabase', @optname = N'publish', @value = N'true'
go
exec 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 = 0

exec 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'

exec sp_scriptpublicationcustomprocs 'TestDatabase'

--
-- 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]
go
create procedure [sp_MSins_TestTable] @c1 int,@c2 int,@c3 uniqueidentifier,@c4 int

AS
BEGIN

if not exists (select * from [TestTable] 
 where  ( [col1] = @c1 )  or  ( [col4] = @c5 ) 
)
BEGIN

insert into [TestTable]( 
[col1], [col2], [msrepl_tran_version], [col4]
 )

values ( 
@c1, @c2, @c3, @c4
 )

END
END
go

if exists (select * from sysobjects where type = 'P'  and name = 'sp_MSupd_TestTable')  drop proc [sp_MSupd_TestTable]
go
create procedure [sp_MSupd_TestTable] 
 @c1 int,@c2 int,@c3 uniqueidentifier,@c4 int,@c5 int,@c6 int,@c7 uniqueidentifier,@c8 int
as

if not exists (select * from [TestTable] 
 where  ( [col4] = @c9 and @c9 != @c5 ) 
)
begin
if @c5 = @c1
begin
update [TestTable] set [col2] = @c6,[msrepl_tran_version] = @c7,[col4] = @c8
where [col1] = @c1
 and msrepl_tran_version = @c3
end
else
begin

if not exists (select * from [TestTable] 
 where  ( [col1] = @c5 ) 
)
begin
update [TestTable] set [col1] = @c5,[col2] = @c6,[msrepl_tran_version] = @c7,[col4] = @c8
where [col1] = @c1
 and msrepl_tran_version = @c3

end
end

end
go

if exists (select * from sysobjects where type = 'P' and name = 'sp_MSdel_TestTable')  drop proc [sp_MSdel_TestTable]
go
create procedure [sp_MSdel_TestTable] @c1 int,@c2 int,@c3 uniqueidentifier,@c4 int
as
delete [TestTable]
where [col1] = @c1
 and msrepl_tran_version = @c3
go