https://www.betaarchive.com/wiki/index.php?title=Microsoft_KB_Archive/827175&feed=atom&action=history
Microsoft KB Archive/827175 - Revision history
2024-03-28T14:48:26Z
Revision history for this page on the wiki
MediaWiki 1.39.3
https://www.betaarchive.com/wiki/index.php?title=Microsoft_KB_Archive/827175&diff=198810&oldid=prev
3155ffGd: importing KB archive
2020-07-18T17:09:22Z
<p>importing KB archive</p>
<p><b>New page</b></p><div><div id="nsbanner"><br />
<br />
<div id="bannerrow1"><br />
<br />
{|<br />
| Knowledge Base<br />
|<br />
|}<br />
<br />
<br />
</div><br />
<div id="TitleRow"><br />
<br />
= <span id="KB827175"></span>FIX: Incorrect Parameter Numbering Occurs in Custom Stored Procedures That Are Generated with the Sp_scriptpublicationcustomprocs Stored Procedure =<br />
<br />
<br />
</div><br />
<br />
</div><br />
<div id="nstext" valign="BOTTOM"><br />
<br />
Article ID: 827175<br />
<br />
Article Last Modified on 11/2/2007<br />
<br />
<br />
-----<br />
<br />
APPLIES TO<br /><br />
<br /><br />
<br />
* Microsoft SQL Server 2000 Service Pack 3<br />
* Microsoft SQL Server 2000 Developer Edition<br />
* Microsoft SQL Server 2000 Standard Edition<br />
* Microsoft SQL Server 2000 Enterprise Edition<br />
* Microsoft SQL Server 2000 Personal Edition<br />
* Microsoft SQL Server 2000 Enterprise Edition<br />
* Microsoft SQL Server 2000 Workgroup Edition<br />
* Microsoft SQL Server 2000 Desktop Engine (Windows)<br />
* Microsoft SQL Server 2000 Developer Edition<br />
* Microsoft SQL Server 2000 Enterprise Edition 64-bit<br />
<br />
<br />
-----<br />
<br />
<div class="notice_section"><br />
<br />
<br />
<br />
</div><br />
<div class="symptoms_section"><br />
<br />
== SYMPTOMS ==<br />
<br />
When you try to run custom stored procedures that were generated with the '''sp_scriptpublicationcustomprocs''' stored procedure, you receive the following Error 137 message<br />
<div class="errormessage"><br />
<br />
Must declare the variable '%.*ls'<br />
<br />
</div><br />
if all the following conditions are true:<br /><br />
<br />
* Immediate-updating subscriptions are permitted on the publication (''@allow_sync_tran'' = 'True').<br />
* Queuing of changes at the subscribers is enabled (''@allow_queued_tran'' = 'True').<br />
* The table that has the incorrect custom stored procedures that were created with the '''sp_scriptpublicationcustomprocs''' stored procedure contains computed columns.<br />
* The table that has the incorrect custom stored procedures that were created with the '''sp_scriptpublicationcustomprocs''' stored procedure has unique indexes.<br />
<br />
<br />
</div><br />
<div class="resolution_section"><br />
<br />
== RESOLUTION ==<br />
<br />
=== Service pack information ===<br />
<br />
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:<br />
<div class="indent"><br />
<br />
[[../290211|290211]] How to obtain the latest SQL Server 2000 service pack<br />
<br />
<br />
</div><br />
=== Hotfix information ===<br />
<br />
Follow the steps in the &quot;More Information&quot; section to install and to activate this fix.<br /><br />
<br /><br />
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.<br />
<pre class="fixed_text"> Date Time Version Size File name<br />
----------------------------------------------------------------------<br />
31-May-2003 09:45 2000.80.818.0 78,400 Console.exe <br />
24-Jun-2003 16:01 2000.80.818.0 33,340 Dbmslpcn.dll<br />
24-Apr-2003 17:12 786,432 Distmdl.ldf<br />
24-Apr-2003 17:12 2,359,296 Distmdl.mdf<br />
29-Jan-2003 16:55 180 Drop_repl_hotfix.sql<br />
23-Jun-2003 13:40 2000.80.837.0 1,557,052 Dtsui.dll<br />
23-Jun-2003 13:40 2000.80.837.0 639,552 Dtswiz.dll<br />
23-Apr-2003 17:51 747,927 Instdist.sql<br />
02-May-2003 16:56 1,581 Inst_repl_hotfix.sql<br />
07-Feb-2003 21:40 2000.80.765.0 90,692 Msgprox.dll<br />
31-Mar-2003 17:07 1,873 Odsole.sql<br />
04-Apr-2003 16:46 2000.80.800.0 62,024 Odsole70.dll<br />
07-May-2003 11:41 2000.80.819.0 25,144 Opends60.dll<br />
02-Apr-2003 12:48 2000.80.796.0 57,904 Osql.exe<br />
02-Apr-2003 14:15 2000.80.797.0 279,104 Pfutil80.dll<br />
04-Aug-2003 09:17 550,780 Procsyst.sql<br />
22-May-2003 13:57 19,195 Qfe469571.sql<br />
11-Jul-2003 08:04 1,084,147 Replmerg.sql<br />
04-Apr-2003 12:53 2000.80.798.0 221,768 Replprov.dll <br />
07-Feb-2003 21:40 2000.80.765.0 307,784 Replrec.dll<br />
13-Aug-2003 07:28 1,086,797 Replsys.sql<br />
13-Aug-2003 07:28 986,603 Repltran.sql<br />
29-Jul-2003 11:13 2000.80.819.0 492,096 Semobj.dll<br />
31-May-2003 09:27 2000.80.818.0 172,032 Semobj.rll<br />
05-Aug-2003 12:06 127,884 Sp3_serv_uni.sql<br />
31-May-2003 16:01 2000.80.818.0 4,215,360 Sqldmo.dll<br />
07-Apr-2003 08:44 25,172 Sqldumper.exe <br />
19-Mar-2003 09:20 2000.80.789.0 28,672 Sqlevn70.rll<br />
01-Jul-2003 15:18 2000.80.834.0 180,736 Sqlmap70.dll<br />
07-Feb-2003 21:40 2000.80.765.0 57,920 Sqlrepss.dll<br />
13-Aug-2003 11:04 2000.80.851.0 7,598,161 Sqlservr.exe<br />
25-Jul-2003 12:44 2000.80.845.0 590,396 Sqlsort.dll<br />
07-Feb-2003 21:40 2000.80.765.0 45,644 Sqlvdi.dll<br />
24-Jun-2003 16:01 2000.80.818.0 33,340 Ssmslpcn.dll<br />
31-May-2003 16:01 2000.80.818.0 82,492 Ssnetlib.dll<br />
31-May-2003 16:01 2000.80.818.0 25,148 Ssnmpn70.dll<br />
31-May-2003 16:01 2000.80.818.0 158,240 Svrnetcn.dll<br />
31-May-2003 09:59 2000.80.818.0 76,416 Svrnetcn.exe<br />
30-Apr-2003 14:52 2000.80.816.0 45,132 Ums.dll<br />
01-Jul-2003 15:19 2000.80.834.0 98,816 Xpweb70.dll<br />
<br />
</pre><br />
'''Note''' Because of file dependencies, the most recent hotfix or feature that contains the files may also contain additional files.<br />
<br />
</div><br />
<div class="status_section"><br />
<br />
== STATUS ==<br />
<br />
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section of this article. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 4.<br />
<br />
</div><br />
<div class="moreinformation_section"><br />
<br />
== MORE INFORMATION ==<br />
<br />
To install and activate the fix, follow these steps:<br />
# Obtain the fix that is described in the &quot;Resolution&quot; section of this article.<br />
# Apply the fix to the affected publishing instance of SQL Server 2000.<br />
# Mark the affected subscription for reinitialization:<br />
#* For Push subscriptions:<br /><br />
<br /><br />
In Enterprise Manager, open the Publisher server tree, and then locate the '''Replication - Subscriptions''' node. Right-click the affected subscription, and then click '''Reinitialize'''.<br />
#* For Pull subscriptions:<br /><br />
<br /><br />
In Enterprise Manager, open the Subscriber server tree, and then locate the '''Replication - Subscriptions''' node. Right-click the affected subscription, and then click '''Reinitialize'''.<br />
# Run the Snapshot Agent for the affected publication.<br />
<br />
To reproduce the problem, run the following script in a server that already has replication configured:<br />
<pre class="codesample">USE master<br />
go<br />
CREATE DATABASE TestDatabase<br />
go<br />
USE TestDatabase<br />
go<br />
<br />
CREATE TABLE [dbo].[TestTable] (<br />
[col1] [int] NOT NULL ,<br />
[col2] [int] NOT NULL ,<br />
[col3] AS ([col1] + [col2]) ,<br />
[msrepl_tran_version] [uniqueidentifier] NOT NULL<br />
) ON [PRIMARY]<br />
go<br />
<br />
ALTER TABLE [dbo].[TestTable] WITH NOCHECK ADD<br />
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED<br />
(<br />
[col1]<br />
) ON [PRIMARY]<br />
go<br />
<br />
ALTER TABLE [dbo].[TestTable] ADD<br />
CONSTRAINT [DF_TestTable_msrepl_tran_vers] DEFAULT (newid()) FOR<br />
[msrepl_tran_version]<br />
go<br />
<br />
CREATE UNIQUE INDEX [IX_T1UNIQUE] ON<br />
[dbo].[TestTable]([msrepl_tran_version]) ON [PRIMARY]<br />
go<br />
<br />
use [TestDatabase]<br />
go<br />
exec sp_replicationdboption @dbname = N'TestDatabase', @optname = N'publish', @value = N'true'<br />
go<br />
exec sp_addpublication @publication = N'TestDatabase', @restricted = N'false', <br />
@sync_method = N'native', @repl_freq = N'continuous', <br />
@description = N'Transactional publication of TestDatabase database from Publisher NAURU-SRV-01.', <br />
@status = N'inactive', @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', <br />
@enabled_for_internet = N'false', @independent_agent = N'false', @immediate_sync = N'false', <br />
@allow_sync_tran = N'true', @autogen_sync_procs = N'true', @retention = 336, <br />
@allow_queued_tran = N'true', @snapshot_in_defaultfolder = N'true', <br />
@compress_snapshot = N'false', @ftp_port = 21, @allow_dts = N'false', <br />
@allow_subscription_copy = N'false', @conflict_policy = N'pub wins', <br />
@centralized_conflicts = N'true', @conflict_retention = 14, <br />
@queue_type = N'sql', @add_to_active_directory = N'false'<br />
<br />
exec sp_addpublication_snapshot @publication = N'TestDatabase',@frequency_type = 4, @frequency_interval = 1, <br />
@frequency_relative_interval = 0, @frequency_recurrence_factor = 1, <br />
@frequency_subday = 1, @frequency_subday_interval = 0, @active_start_date = 0, <br />
@active_end_date = 20030814, @active_start_time_of_day = 224200, @active_end_time_of_day = 0<br />
<br />
exec sp_addarticle @publication = N'TestDatabase', @article = N'TestTable', @source_owner = N'dbo', <br />
@source_object = N'TestTable', <br />
@destination_table = N'TestTable', @type = N'logbased', @creation_script = null, <br />
@description = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000000CEF3, <br />
@status = 16, @force_invalidate_snapshot = 1, @vertical_partition = N'false', <br />
@ins_cmd = null, @del_cmd = null, @upd_cmd = null, @filter = null, @sync_object = null, <br />
@auto_identity_range = N'false'<br />
<br />
exec sp_changepublication @publication = N'TestDatabase', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1, <br />
@property = N'status', @value = N'active'<br />
</pre><br />
After the publication is created, if you run the following statement:<br />
<pre class="codesample">exec sp_scriptpublicationcustomprocs 'TestDatabase'</pre><br />
<br /><br />
you will receive the following (incorrect) script:<br /><br />
<br />
<pre class="codesample">--<br />
-- Transactional replication custom procedures for publication 'TestDatabase' from database 'TestDatabase':<br />
--<br />
<br />
<br />
----<br />
---- Replication custom procedures for article 'TestTable':<br />
----<br />
<br />
if exists (select * from sysobjects where type = 'P' and name = 'sp_MSins_TestTable') drop proc [sp_MSins_TestTable]<br />
go<br />
create procedure [sp_MSins_TestTable] @c1 int,@c2 int,@c3 uniqueidentifier,@c4 int<br />
<br />
AS<br />
BEGIN<br />
<br />
if not exists (select * from [TestTable] <br />
where ( [col1] = @c1 ) or ( [col4] = @c5 ) <br />
)<br />
BEGIN<br />
<br />
insert into [TestTable]( <br />
[col1], [col2], [msrepl_tran_version], [col4]<br />
)<br />
<br />
values ( <br />
@c1, @c2, @c3, @c4<br />
)<br />
<br />
END<br />
END<br />
go<br />
<br />
if exists (select * from sysobjects where type = 'P' and name = 'sp_MSupd_TestTable') drop proc [sp_MSupd_TestTable]<br />
go<br />
create procedure [sp_MSupd_TestTable] <br />
@c1 int,@c2 int,@c3 uniqueidentifier,@c4 int,@c5 int,@c6 int,@c7 uniqueidentifier,@c8 int<br />
as<br />
<br />
if not exists (select * from [TestTable] <br />
where ( [col4] = @c9 and @c9 != @c5 ) <br />
)<br />
begin<br />
if @c5 = @c1<br />
begin<br />
update [TestTable] set [col2] = @c6,[msrepl_tran_version] = @c7,[col4] = @c8<br />
where [col1] = @c1<br />
and msrepl_tran_version = @c3<br />
end<br />
else<br />
begin<br />
<br />
if not exists (select * from [TestTable] <br />
where ( [col1] = @c5 ) <br />
)<br />
begin<br />
update [TestTable] set [col1] = @c5,[col2] = @c6,[msrepl_tran_version] = @c7,[col4] = @c8<br />
where [col1] = @c1<br />
and msrepl_tran_version = @c3<br />
<br />
end<br />
end<br />
<br />
end<br />
go<br />
<br />
if exists (select * from sysobjects where type = 'P' and name = 'sp_MSdel_TestTable') drop proc [sp_MSdel_TestTable]<br />
go<br />
create procedure [sp_MSdel_TestTable] @c1 int,@c2 int,@c3 uniqueidentifier,@c4 int<br />
as<br />
delete [TestTable]<br />
where [col1] = @c1<br />
and msrepl_tran_version = @c3<br />
go<br />
</pre><br />
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''.<br />
<br />
</div><br />
Keywords: kbbug kbfix kbqfe kbsqlserv2000presp4fix kbhotfixserver KB827175<br />
<br />
<div class="footer"><br />
<br />
<br /><br />
<br />
<br />
-----<br />
<br />
[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]<br />
<br />
<span>© Microsoft Corporation. All rights reserved.</span><br />
<br />
<br />
</div><br />
<br />
</div></div>
3155ffGd