Microsoft KB Archive/184882

= FIX: Intstdist.sql Updates MSjob_commands Incorrectly =

Article ID: 184882

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 6.5 Service Pack 4
 * Microsoft SQL Server 6.5 Service Pack 3

-



This article was previously published under Q184882



BUG #: 17893 (SQLBUG_65)



SYMPTOMS
The distribution task fails with the following error:

Invalid Distribution Command



CAUSE
The Instdist.sql script that comes with with SQL Server 6.5 Service Pack 3 can cause problems with replication if it is run more than once. This file is also distributed with Service Pack 4, causing it be run again.

The first time Instdist.sql is run, it correctly turns on ANSI_PADDING for the command column in the MSjob_commands table. If Instdist.sql is run a second time, the syscolumns.status value for the commands column is incremented again by 16. This increment occurs each time Instdist.sql is run. As a result, you get unwanted changes in the properties of the command column.

Use the following query to check the distribution database for this problem: select status from syscolumns where id = object_id( 'MSjob_commands' ) and colid = 7 The value in the status column should be 24.



WORKAROUND
If there is a value in the status column other than 24, you can correct it by performing the following steps:

 Use the following statements to turn on 'allow updates':

use master

go

sp_configure 'allow updates', 1

go

reconfigure with override

go

 Run the following commands in the distribution database:

use distribution

go

update syscolumns

set status = 24

where id = object_id( 'MSjob_commands' )

and colid = 7

go

 Use the following statements to turn off 'allow updates':

use master

go

sp_configure 'allow updates', 0

go

reconfigure with override

go

 Unsubscribe and resubscribe any article related to command in MSjob_commands that results in an "Invalid Distribution Command" error. Drop and re-create the sp_MSadd_job_command stored procedure in the distribution database.</ol>

<div class="status_section">

STATUS
Microsoft has confirmed this to be a problem in SQL Server 6.5. This problem has been corrected in Service Pack 5a for Microsoft SQL Server 6.5.For more information, click the following article number to view the article in the Microsoft Knowledge Base:

197177 INF: How to Obtain SQL Server 6.5 Service Pack 5a

For more information, contact your primary support provider.

Additional query words: prodsql repl sp sp3 sp4 sp5

Keywords: kbbug kbfix KB184882

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.