Article ID: 827175
Article Last Modified on 11/2/2007
APPLIES TO
- 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 Enterprise Edition
- Microsoft SQL Server 2000 Workgroup Edition
- Microsoft SQL Server 2000 Desktop Engine (Windows)
- Microsoft SQL Server 2000 Developer Edition
- Microsoft SQL Server 2000 Enterprise Edition 64-bit
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
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:
- Obtain the fix that is described in the "Resolution" section of this article.
- Apply the fix to the affected publishing instance of SQL Server 2000.
- Mark the affected subscription for reinitialization:
- For Push subscriptions:
- 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:
- 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.
- 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 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'
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] 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
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.
Keywords: kbbug kbfix kbqfe kbsqlserv2000presp4fix kbhotfixserver KB827175
