FIX: Intstdist.sql Updates MSjob_commands Incorrectly

• Microsoft SQL Server version 6.5 Service Pack 3, 6.5 Service Pack 4

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 

WORKAROUND

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.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 5a for Microsoft SQL Server version 6.5. For information about downloading and installing the latest SQL Server Service Pack, see http://support.microsoft.com/support/sql/.

For more information, contact your primary support provider.

Additional query words: prodsql repl sp sp3 sp4 sp5

Keywords          : kbbug6.50.sp3 kbbug6.50.sp4 kbfix6.50.SP5 
Version           : 
Platform          : 
Issue type        : kbbug 

Last Reviewed: April 21, 1999