FIX: Intstdist.sql Updates MSjob_commands Incorrectly (184882)

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.

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