FIX: Intstdist.sql Updates MSjob_commands Incorrectly

The distribution task fails with the following error:

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: The value in the status column should be 24.

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

1. Use the following statements to turn on 'allow updates':
   use master
   go
   sp_configure 'allow updates', 1
   go
   reconfigure with override
   go
   
2. 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
   
3. Use the following statements to turn off 'allow updates':
   use master
   go
   sp_configure 'allow updates', 0
   go
   reconfigure with override
   go
   
4. Unsubscribe and resubscribe any article related to command in MSjob_commands that results in an "Invalid Distribution Command" error.
5. Drop and re-create the sp_MSadd_job_command stored procedure in the distribution database.

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:For more information, contact your primary support provider.