Microsoft Internet Business Development Kit/PRODUCT_CD.iso/sqlsvr/ppc/early1.sql

Text File | 1995-12-15 | 98.7 KB | 3,591 lines

/***********

Early1.SQL  (For 6.5 Beta_1 this will have exact same code as InstDist.SQL;
         but this will become obsolete for 6.5 release.)

InstDist.SQL   1995/12/11 19:34

***********/

go
use master
go

declare @special_db_name varchar(30)
select  @special_db_name = 'distribution'

if not exists (select * from sysdatabases where name=@special_db_name)
    begin
    raiserror('No error but, db=''%s'' does NOT exist, so this ISQL run will terminate now.'
                 ,1,127 ,@special_db_name)
    end
go

declare @special_db_name varchar(30)
select  @special_db_name = 'distribution'

if not exists (select * from sysdatabases where name=@special_db_name)
    begin
    raiserror('Minor error, because you should not see this message!  Harshly killing spid.'
                 ,22,127) with log
    end
go

------------------------------------------------------
go
use distribution
go

dump transaction distribution with no_log
go

select 'At top, db_name()=',db_name()

go
EXEC sp_configure 'allow updates', 1
GO
reconfigure with override
GO

/****************************************************************************/
PRINT ''
PRINT 'Create distribution tables'
PRINT ''
/****************************************************************************/
EXEC sp_create_distribution_tables
GO

/****************************************************************************/
PRINT ''
PRINT 'Dropping all distribution stored procedures'
PRINT ''
/****************************************************************************/
IF EXISTS (select * from sysobjects where
    name = 'sp_MSdistribution_cleanup' and type = 'P')
       DROP PROCEDURE sp_MSdistribution_cleanup
GO

IF EXISTS (select * from sysobjects where
   name = 'sp_replcleanup' and type = 'P')
      DROP PROCEDURE sp_replcleanup
GO

IF EXISTS (SELECT * FROM sysobjects WHERE
   name = 'sp_MSadd_job' and type = 'P')
      DROP PROCEDURE sp_MSadd_job
GO

IF EXISTS (SELECT * FROM sysobjects WHERE
   name = 'sp_MSadd_job_command' and type = 'P')
      DROP PROCEDURE sp_MSadd_job_command
GO

IF EXISTS (SELECT * FROM sysobjects WHERE
   name = 'sp_MSsubscriber_status' and type = 'P')
      DROP PROCEDURE sp_MSsubscriber_status
GO

IF EXISTS (SELECT * FROM sysobjects WHERE
   name = 'sp_MSadd_subscription' and type = 'P')
      DROP PROCEDURE sp_MSadd_subscription
GO

IF EXISTS (SELECT * FROM sysobjects WHERE
   name = 'sp_MSdrop_subscription' and type = 'P')
      DROP PROCEDURE sp_MSdrop_subscription
GO

IF EXISTS (SELECT * FROM sysobjects WHERE
    name = 'sp_MSupdate_job' and type = 'P')
    DROP PROCEDURE sp_MSupdate_job
GO

IF EXISTS (SELECT * FROM sysobjects WHERE
    name = 'sp_MSupdate_subscription' and type = 'P')
       DROP PROCEDURE sp_MSupdate_subscription
GO

IF EXISTS (SELECT * FROM sysobjects WHERE
    name = 'sp_MSadd_subscriber_job' and type = 'P')
       DROP PROCEDURE sp_MSadd_subscriber_job
GO

IF EXISTS (SELECT * FROM sysobjects WHERE
    name = 'sp_MSget_subscriber_jobs' and type = 'P')
       DROP PROCEDURE sp_MSget_subscriber_jobs
GO

IF EXISTS (SELECT * FROM sysobjects WHERE
   name = 'sp_MSfind_subscriber_job' and type = 'P')
      DROP PROCEDURE sp_MSfind_subscriber_job
GO

IF EXISTS (SELECT * FROM sysobjects WHERE
   name = 'sp_MSget_last_job' and type = 'P')
      DROP PROCEDURE sp_MSget_last_job
GO

IF EXISTS (SELECT * FROM sysobjects WHERE
   name = 'sp_MSget_last_trans' and type = 'P')
      DROP PROCEDURE sp_MSget_last_trans
GO

IF EXISTS (SELECT * FROM sysobjects WHERE
   name = 'sp_MSadd_subscriber_info' and type = 'P')
      DROP PROCEDURE sp_MSadd_subscriber_info
GO

IF EXISTS (SELECT * FROM sysobjects WHERE
   name = 'sp_MSupdate_subscriber_info' and type = 'P')
      DROP PROCEDURE sp_MSupdate_subscriber_info
GO

IF EXISTS (SELECT * FROM sysobjects WHERE
   name = 'sp_MSdrop_subscriber_info' and type = 'P')
      DROP PROCEDURE sp_MSdrop_subscriber_info
GO

IF EXISTS (SELECT * FROM sysobjects WHERE
   name = 'sp_MShelp_subscriber_info' and type = 'P')
      DROP PROCEDURE sp_MShelp_subscriber_info
GO

IF EXISTS (select * from sysobjects where
   name = 'sp_MSkill_job' and type = 'P')
      DROP PROCEDURE sp_MSkill_job
GO

IF EXISTS (select * from sysobjects where
   name = 'sp_MSnew_sync' and type = 'P')
      DROP PROCEDURE sp_MSnew_sync
GO

IF EXISTS (select * from sysobjects where
   name = 'sp_MSdistribution_counters' and type = 'P')
      DROP PROCEDURE sp_MSdistribution_counters
GO

IF EXISTS (select * from sysobjects where
   name = 'sp_MSremove_published_jobs' and type = 'P')
      DROP PROCEDURE sp_MSremove_published_jobs
GO

dump transaction distribution with no_log
go

/****************************************************************************/
PRINT ''
PRINT 'Creating Stored Procedure:  dbo.sp_MSdistribution_cleanup'
PRINT ''
/****************************************************************************/
GO
CREATE PROCEDURE sp_MSdistribution_cleanup
@publisher_id smallint,
@publisher_db varchar(30),
@subscriber_id smallint,
@subscriber_db varchar(30),
@cutoff_time datetime,
@num_commands int OUTPUT

as
   declare @max_cleanup_job int

   SET NOCOUNT ON

   /*
   ** Delete all subscriber jobs that have been completed and archived for
   ** the subscriber's retention period.
   */
   select @max_cleanup_job = max (job_id) from
      MSsubscriber_status (index = ucMSsubscriber_status) where
      publisher_id = @publisher_id and
      publisher_db = @publisher_db and
      subscriber_id = @subscriber_id and
      subscriber_db = @subscriber_db and
      completion_time < @cutoff_time
   if @@error <> 0
      return (1)

   select @num_commands = count(*)
    from MSsubscriber_jobs (index = ucMSsubscriber_jobs) where
         publisher_id = @publisher_id and
         publisher_db = @publisher_db and
    subscriber_id = @subscriber_id and
    subscriber_db = @subscriber_db and
    job_id <= @max_cleanup_job
   if @@error <> 0
      return (1)

   delete MSsubscriber_jobs from MSsubscriber_jobs where
         publisher_id = @publisher_id and
         publisher_db = @publisher_db and
    subscriber_id = @subscriber_id and
    subscriber_db = @subscriber_db and
    job_id <= @max_cleanup_job
   if @@error <> 0
      return (1)

   /*
   ** Delete all subscriber status that have been around for longer than
   ** the subscriber's retention period. Always leave the last entry for
   ** each distribution process.
   */
   delete MSsubscriber_status from MSsubscriber_status ss1 where
       publisher_id = @publisher_id and
       publisher_db = @publisher_db and
       subscriber_id = @subscriber_id and
       subscriber_db = @subscriber_db and
       job_id < @max_cleanup_job and
       job_id <> (select max(job_id) from
        MSsubscriber_status ss2 (index = ucMSsubscriber_status) where
        ss2.publisher_id = @publisher_id and
        ss2.publisher_db = @publisher_db and
        ss2.subscriber_id = @subscriber_id and
        ss2.subscriber_db = @subscriber_db)
   if @@error <> 0
      return (1)

go

/* Set category bit to reflect MS objects */
UPDATE sysobjects SET category = category | 2 WHERE sysstat & 0xf = 4 AND
    name = 'sp_MSdistribution_cleanup'
go

/****************************************************************************/
PRINT ''
PRINT 'Creating Stored Procedure:  dbo.sp_replcleanup'
PRINT ''
/****************************************************************************/
GO
CREATE PROCEDURE sp_replcleanup
@publisher varchar(30),
@subscriber varchar(30),
@retention int

as
   declare @publisher_id smallint
   declare @publisher_db varchar (30)
   declare @subscriber_id smallint
   declare @subscriber_db varchar (30)
   declare @cutoff_time datetime
   declare @retcode int
   declare @novalue int
   declare @num_commands int
   declare @total_commands int
   declare @max_cleanup_job int
   declare @delcmd varchar (255)
   declare @job_command varchar (255)
   declare @file varchar (255)
   declare @table varchar (255)


   SET NOCOUNT ON

   select @cutoff_time = dateadd(hour, -@retention, getdate())
   select @total_commands = 0

   /*
   ** Make sure the publisher is valid.
   */
   select @publisher_id = srvid from master..sysservers where
       srvname = @publisher
   if @publisher_id is NULL
   begin
      RAISERROR(14048, 16, -1, @publisher)
      return (1)
   end

   /*
   ** Make sure the subscriber is valid.
   */
   select @subscriber_id = srvid from master..sysservers where
       srvname = @subscriber
   if @subscriber_id is NULL
   begin
      RAISERROR(14048, 16, -1, @subscriber)
      return (1)
   end

   begin transaction sp_replcleanup

   /* To prevent deadlocking with running replication, get an exclusive
   ** lock on distribution tables.
   */
   select @novalue = job_id from MSjobs (TABLOCKX HOLDLOCK) where 1=2
   select @novalue = job_id from MSjob_commands (TABLOCKX HOLDLOCK) where 1=2
   select @novalue = job_id from MSsubscriber_jobs (TABLOCKX HOLDLOCK) where 1=2

   /* Open a cursor to get each publisher/subscriber database pair */
   declare hC cursor for select distinct publisher_db, subscriber_db
      from MSjob_subscriptions
      where publisher_id = @publisher_id for read only

   open hC
   fetch hC into @publisher_db, @subscriber_db
   while (@@fetch_status <> -1)
      begin

       execute @retcode = sp_MSdistribution_cleanup @publisher_id,
        @publisher_db, @subscriber_id, @subscriber_db, @cutoff_time,
        @num_commands OUTPUT

     select 'publisher_db' = @publisher_db,
        'subscriber_db' = @subscriber_db,
        'removed commands' = @num_commands
     if @retcode <> 0
        begin
           close hC
               DEALLOCATE hC
           rollback transaction sp_replcleanup
           return (1)
        end

     select @total_commands = @total_commands + @num_commands

         fetch hC into @publisher_db, @subscriber_db
      end
   close hC
   DEALLOCATE hC

   /* Open a cursor to get each publisher database */
   declare hC2 cursor for select distinct publisher_db from MSjobs
      where publisher_id = @publisher_id for read only
   open hC2
   fetch hC2 into @publisher_db
   while (@@fetch_status <> -1)
      begin
       /*
       ** For each sync command that no longer has a subscriber job delete the
       ** sync file.
       */
       declare hC3 cursor for select jc.command from MSjobs j (index = ucMSjobs),
         MSjob_commands jc (index = ucMSjob_commands) where
         j.publisher_id = @publisher_id and
         j.publisher_db = @publisher_db and
         (j.type = 3 or j.type = 4 or j.type = 5) and
         not exists (select * from MSsubscriber_jobs sj
     (index = ucMSsubscriber_jobs) where
            j.publisher_id = sj.publisher_id and
        j.publisher_db = sj.publisher_db and
        j.job_id = sj.job_id) and
         jc.publisher_id = j.publisher_id and
         jc.publisher_db = j.publisher_db and
         jc.job_id = j.job_id

      OPEN hC3
      FETCH hC3 INTO @job_command
      WHILE (@@fetch_status <> -1)
         BEGIN
            EXEC @retcode = master..xp_sscanf @job_command, 'sync -t%s -d%s',
           @value1 = @table output, @value2 =@file output
            IF @retcode <> 0
        begin
           CLOSE hC3
           DEALLOCATE hC3
               return (1)
        end

            EXEC @retcode = master..xp_sprintf @string = @delcmd output,
        @format = 'del %s', @value = @file
            IF @retcode <> 0
        begin
           CLOSE hC3
           DEALLOCATE hC3
               return (1)
        end

            EXEC master..xp_cmdshell @delcmd

            FETCH hC3 INTO @job_command
         END
      CLOSE hC3
      DEALLOCATE hC3

      /* Remove all orphaned jobs (no entry in MSsubscriber_jobs) from MSjobs */
      delete MSjobs from MSjobs j where
         j.publisher_id = @publisher_id and
         j.publisher_db = @publisher_db and
         j.job_id not in (select job_id from MSsubscriber_jobs sj (index = ncMSsubscriber_jobs) where
            sj.publisher_id = j.publisher_id and
        sj.publisher_db = j.publisher_db and
        sj.job_id = j.job_id) and
         j.job_id <> (select max(job_id) from MSjobs j (index = ucMSjobs) where
            j.publisher_id = @publisher_id and
            j.publisher_db = @publisher_db and
        j.xactid_page <> 0) and
         j.job_id <> (select max(job_id) from MSjobs j (index = ucMSjobs) where
            j.publisher_id = @publisher_id and
            j.publisher_db = @publisher_db)
      if @@error <> 0
         begin
            close hC2
            DEALLOCATE hC2
            rollback transaction sp_replcleanup
            return (1)
         end

       /* Remove all orphaned command (no entry in MSjobs) from MSjob_commands */
       delete MSjob_commands from MSjob_commands jc where
         jc.publisher_id = @publisher_id and
         jc.publisher_db = @publisher_db and
         jc.job_id not in (select job_id from MSjobs j (index = ucMSjobs) where
            j.publisher_id = jc.publisher_id and
        j.publisher_db = jc.publisher_db and
            j.job_id = jc.job_id)
      if @@error <> 0
         begin
            close hC2
            DEALLOCATE hC2
            rollback transaction sp_replcleanup
            return (1)
         end

       fetch hC2 into @publisher_db
      end
   close hC2
   DEALLOCATE hC2

   commit transaction sp_replcleanup

   RAISERROR(14089, 10, -1, @total_commands, @subscriber)
go

/* Set category bit to reflect MS objects */
UPDATE sysobjects SET category = category | 2 WHERE sysstat & 0xf = 4 AND
    name = 'sp_replcleanup'
go

/****************************************************************************/
PRINT ''
PRINT 'Creating Stored Procedure:  dbo.sp_MSadd_job'
PRINT ''
/****************************************************************************/
GO
CREATE PROCEDURE sp_MSadd_job
@publisher_id smallint,
@publisher_db varchar (30),
@job_id int = 0,
@type tinyint = 0,
@xactid_page int = 0,
@xactid_row smallint = 0,
@xactid_ts binary(8) = 0,

@job_id01 int = 0,
@xactid_page01 int = NULL,
@xactid_row01 smallint = NULL,
@xactid_ts01 binary(8) = NULL,

@job_id02 int = 0,
@xactid_page02 int = NULL,
@xactid_row02 smallint = NULL,
@xactid_ts02 binary(8) = NULL,

@job_id03 int = 0,
@xactid_page03 int = NULL,
@xactid_row03 smallint = NULL,
@xactid_ts03 binary(8) = NULL,

@job_id04 int = 0,
@xactid_page04 int = NULL,
@xactid_row04 smallint = NULL,
@xactid_ts04 binary(8) = NULL,

@job_id05 int = 0,
@xactid_page05 int = NULL,
@xactid_row05 smallint = NULL,
@xactid_ts05 binary(8) = NULL,

@job_id06 int = 0,
@xactid_page06 int = NULL,
@xactid_row06 smallint = NULL,
@xactid_ts06 binary(8) = NULL,

@job_id07 int = 0,
@xactid_page07 int = NULL,
@xactid_row07 smallint = NULL,
@xactid_ts07 binary(8) = NULL,

@job_id08 int = 0,
@xactid_page08 int = NULL,
@xactid_row08 smallint = NULL,
@xactid_ts08 binary(8) = NULL,

@job_id09 int = 0,
@xactid_page09 int = NULL,
@xactid_row09 smallint = NULL,
@xactid_ts09 binary(8) = NULL,

@job_id10 int = 0,
@xactid_page10 int = NULL,
@xactid_row10 smallint = NULL,
@xactid_ts10 binary(8) = NULL,

@job_id11 int = 0,
@xactid_page11 int = NULL,
@xactid_row11 smallint = NULL,
@xactid_ts11 binary(8) = NULL,

@job_id12 int = 0,
@xactid_page12 int = NULL,
@xactid_row12 smallint = NULL,
@xactid_ts12 binary(8) = NULL,

@job_id13 int = 0,
@xactid_page13 int = NULL,
@xactid_row13 smallint = NULL,
@xactid_ts13 binary(8) = NULL,

@job_id14 int = 0,
@xactid_page14 int = NULL,
@xactid_row14 smallint = NULL,
@xactid_ts14 binary(8) = NULL,

@job_id15 int = 0,
@xactid_page15 int = NULL,
@xactid_row15 smallint = NULL,
@xactid_ts15 binary(8) = NULL,

@job_id16 int = 0,
@xactid_page16 int = NULL,
@xactid_row16 smallint = NULL,
@xactid_ts16 binary(8) = NULL,

@job_id17 int = 0,
@xactid_page17 int = NULL,
@xactid_row17 smallint = NULL,
@xactid_ts17 binary(8) = NULL,

@job_id18 int = 0,
@xactid_page18 int = NULL,
@xactid_row18 smallint = NULL,
@xactid_ts18 binary(8) = NULL,

@job_id19 int = 0,
@xactid_page19 int = NULL,
@xactid_row19 smallint = NULL,
@xactid_ts19 binary(8) = NULL,

@job_id20 int = 0,
@xactid_page20 int = NULL,
@xactid_row20 smallint = NULL,
@xactid_ts20 binary(8) = NULL,

@job_id21 int = 0,
@xactid_page21 int = NULL,
@xactid_row21 smallint = NULL,
@xactid_ts21 binary(8) = NULL,

@job_id22 int = 0,
@xactid_page22 int = NULL,
@xactid_row22 smallint = NULL,
@xactid_ts22 binary(8) = NULL,

@job_id23 int = 0,
@xactid_page23 int = NULL,
@xactid_row23 smallint = NULL,
@xactid_ts23 binary(8) = NULL,

@job_id24 int = 0,
@xactid_page24 int = NULL,
@xactid_row24 smallint = NULL,
@xactid_ts24 binary(8) = NULL,

@job_id25 int = 0,
@xactid_page25 int = NULL,
@xactid_row25 smallint = NULL,
@xactid_ts25 binary(8) = NULL,

@job_id26 int = 0,
@xactid_page26 int = NULL,
@xactid_row26 smallint = NULL,
@xactid_ts26 binary(8) = NULL,

@job_id27 int = 0,
@xactid_page27 int = NULL,
@xactid_row27 smallint = NULL,
@xactid_ts27 binary(8) = NULL,

@job_id28 int = 0,
@xactid_page28 int = NULL,
@xactid_row28 smallint = NULL,
@xactid_ts28 binary(8) = NULL,

@job_id29 int = 0,
@xactid_page29 int = NULL,
@xactid_row29 smallint = NULL,
@xactid_ts29 binary(8) = NULL,

@job_id30 int = 0,
@xactid_page30 int = NULL,
@xactid_row30 smallint = NULL,
@xactid_ts30 binary(8) = NULL,

@job_id31 int = 0,
@xactid_page31 int = NULL,
@xactid_row31 smallint = NULL,
@xactid_ts31 binary(8) = NULL,

@job_id32 int = 0,
@xactid_page32 int = NULL,
@xactid_row32 smallint = NULL,
@xactid_ts32 binary(8) = NULL,

@job_id33 int = 0,
@xactid_page33 int = NULL,
@xactid_row33 smallint = NULL,
@xactid_ts33 binary(8) = NULL,

@job_id34 int = 0,
@xactid_page34 int = NULL,
@xactid_row34 smallint = NULL,
@xactid_ts34 binary(8) = NULL,

@job_id35 int = 0,
@xactid_page35 int = NULL,
@xactid_row35 smallint = NULL,
@xactid_ts35 binary(8) = NULL,

@job_id36 int = 0,
@xactid_page36 int = NULL,
@xactid_row36 smallint = NULL,
@xactid_ts36 binary(8) = NULL,

@job_id37 int = 0,
@xactid_page37 int = NULL,
@xactid_row37 smallint = NULL,
@xactid_ts37 binary(8) = NULL,

@job_id38 int = 0,
@xactid_page38 int = NULL,
@xactid_row38 smallint = NULL,
@xactid_ts38 binary(8) = NULL,

@job_id39 int = 0,
@xactid_page39 int = NULL,
@xactid_row39 smallint = NULL,
@xactid_ts39 binary(8) = NULL,

@job_id40 int = 0,
@xactid_page40 int = NULL,
@xactid_row40 smallint = NULL,
@xactid_ts40 binary(8) = NULL,

@job_id41 int = 0,
@xactid_page41 int = NULL,
@xactid_row41 smallint = NULL,
@xactid_ts41 binary(8) = NULL,

@job_id42 int = 0,
@xactid_page42 int = NULL,
@xactid_row42 smallint = NULL,
@xactid_ts42 binary(8) = NULL,

@job_id43 int = 0,
@xactid_page43 int = NULL,
@xactid_row43 smallint = NULL,
@xactid_ts43 binary(8) = NULL,

@job_id44 int = 0,
@xactid_page44 int = NULL,
@xactid_row44 smallint = NULL,
@xactid_ts44 binary(8) = NULL,

@job_id45 int = 0,
@xactid_page45 int = NULL,
@xactid_row45 smallint = NULL,
@xactid_ts45 binary(8) = NULL,

@job_id46 int = 0,
@xactid_page46 int = NULL,
@xactid_row46 smallint = NULL,
@xactid_ts46 binary(8) = NULL,

@job_id47 int = 0,
@xactid_page47 int = NULL,
@xactid_row47 smallint = NULL,
@xactid_ts47 binary(8) = NULL,

@job_id48 int = 0,
@xactid_page48 int = NULL,
@xactid_row48 smallint = NULL,
@xactid_ts48 binary(8) = NULL,

@job_id49 int = 0,
@xactid_page49 int = NULL,
@xactid_row49 smallint = NULL,
@xactid_ts49 binary(8) = NULL,

@job_id50 int = 0,
@xactid_page50 int = NULL,
@xactid_row50 smallint = NULL,
@xactid_ts50 binary(8) = NULL,

@job_id51 int = 0,
@xactid_page51 int = NULL,
@xactid_row51 smallint = NULL,
@xactid_ts51 binary(8) = NULL,

@job_id52 int = 0,
@xactid_page52 int = NULL,
@xactid_row52 smallint = NULL,
@xactid_ts52 binary(8) = NULL,

@job_id53 int = 0,
@xactid_page53 int = NULL,
@xactid_row53 smallint = NULL,
@xactid_ts53 binary(8) = NULL,

@job_id54 int = 0,
@xactid_page54 int = NULL,
@xactid_row54 smallint = NULL,
@xactid_ts54 binary(8) = NULL,

@job_id55 int = 0,
@xactid_page55 int = NULL,
@xactid_row55 smallint = NULL,
@xactid_ts55 binary(8) = NULL,

@job_id56 int = 0,
@xactid_page56 int = NULL,
@xactid_row56 smallint = NULL,
@xactid_ts56 binary(8) = NULL,

@job_id57 int = 0,
@xactid_page57 int = NULL,
@xactid_row57 smallint = NULL,
@xactid_ts57 binary(8) = NULL,

@job_id58 int = 0,
@xactid_page58 int = NULL,
@xactid_row58 smallint = NULL,
@xactid_ts58 binary(8) = NULL,

@job_id59 int = 0,
@xactid_page59 int = NULL,
@xactid_row59 smallint = NULL,
@xactid_ts59 binary(8) = NULL,

@job_id60 int = 0,
@xactid_page60 int = NULL,
@xactid_row60 smallint = NULL,
@xactid_ts60 binary(8) = NULL,

@job_id61 int = 0,
@xactid_page61 int = NULL,
@xactid_row61 smallint = NULL,
@xactid_ts61 binary(8) = NULL,

@job_id62 int = 0,
@xactid_page62 int = NULL,
@xactid_row62 smallint = NULL,
@xactid_ts62 binary(8) = NULL

AS

   set nocount on
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id, @type,
       @xactid_page, @xactid_row, @xactid_ts,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id01 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id01, @type,
       @xactid_page01, @xactid_row01, @xactid_ts01,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id02 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id02, @type,
       @xactid_page02, @xactid_row02, @xactid_ts02,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id03 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id03, @type,
       @xactid_page03, @xactid_row03, @xactid_ts03,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id04 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id04, @type,
       @xactid_page04, @xactid_row04, @xactid_ts04,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id05 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id05, @type,
       @xactid_page05, @xactid_row05, @xactid_ts05,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id06 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id06, @type,
       @xactid_page06, @xactid_row06, @xactid_ts06,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id07 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id07, @type,
       @xactid_page07, @xactid_row07, @xactid_ts07,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id08 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id08, @type,
       @xactid_page08, @xactid_row08, @xactid_ts08,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id09 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id09, @type,
       @xactid_page09, @xactid_row09, @xactid_ts09,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id10 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id10, @type,
       @xactid_page10, @xactid_row10, @xactid_ts10,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id11 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id11, @type,
       @xactid_page11, @xactid_row11, @xactid_ts11,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id12 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id12, @type,
       @xactid_page12, @xactid_row12, @xactid_ts12,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id13 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id13, @type,
       @xactid_page13, @xactid_row13, @xactid_ts13,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id14 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id14, @type,
       @xactid_page14, @xactid_row14, @xactid_ts14,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id15 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id15, @type,
       @xactid_page15, @xactid_row15, @xactid_ts15,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id16 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id16, @type,
       @xactid_page16, @xactid_row16, @xactid_ts16,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id17 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id17, @type,
       @xactid_page17, @xactid_row17, @xactid_ts17,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id18 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id18, @type,
       @xactid_page18, @xactid_row18, @xactid_ts18,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id19 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id19, @type,
       @xactid_page19, @xactid_row19, @xactid_ts19,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id20 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id20, @type,
       @xactid_page20, @xactid_row20, @xactid_ts20,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id21 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id21, @type,
       @xactid_page21, @xactid_row21, @xactid_ts21,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id22 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id22, @type,
       @xactid_page22, @xactid_row22, @xactid_ts22,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id23 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id23, @type,
       @xactid_page23, @xactid_row23, @xactid_ts23,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id24 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id24, @type,
       @xactid_page24, @xactid_row24, @xactid_ts24,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id25 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id25, @type,
       @xactid_page25, @xactid_row25, @xactid_ts25,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id26 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id26, @type,
       @xactid_page26, @xactid_row26, @xactid_ts26,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id27 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id27, @type,
       @xactid_page27, @xactid_row27, @xactid_ts27,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id28 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id28, @type,
       @xactid_page28, @xactid_row28, @xactid_ts28,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id29 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id29, @type,
       @xactid_page29, @xactid_row29, @xactid_ts29,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id30 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id30, @type,
       @xactid_page30, @xactid_row30, @xactid_ts30,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id31 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id31, @type,
       @xactid_page31, @xactid_row31, @xactid_ts31,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id32 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id32, @type,
       @xactid_page32, @xactid_row32, @xactid_ts32,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id33 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id33, @type,
       @xactid_page33, @xactid_row33, @xactid_ts33,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id34 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id34, @type,
       @xactid_page34, @xactid_row34, @xactid_ts34,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id35 = 0
          return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id35, @type,
       @xactid_page35, @xactid_row35, @xactid_ts35,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id36 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id36, @type,
       @xactid_page36, @xactid_row36, @xactid_ts36,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id37 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id37, @type,
       @xactid_page37, @xactid_row37, @xactid_ts37,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id38 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id38, @type,
       @xactid_page38, @xactid_row38, @xactid_ts38,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id39 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id39, @type,
       @xactid_page39, @xactid_row39, @xactid_ts39,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id40 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id40, @type,
       @xactid_page40, @xactid_row40, @xactid_ts40,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id41 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id41, @type,
       @xactid_page41, @xactid_row41, @xactid_ts41,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id42 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id42, @type,
       @xactid_page42, @xactid_row42, @xactid_ts42,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id43 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id43, @type,
       @xactid_page43, @xactid_row43, @xactid_ts43,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id44 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id44, @type,
       @xactid_page44, @xactid_row44, @xactid_ts44,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id45 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id45, @type,
       @xactid_page45, @xactid_row45, @xactid_ts45,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id46 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id46, @type,
       @xactid_page46, @xactid_row46, @xactid_ts46,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id47 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id47, @type,
       @xactid_page47, @xactid_row47, @xactid_ts47,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id48 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id48, @type,
       @xactid_page48, @xactid_row48, @xactid_ts48,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id49 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id49, @type,
       @xactid_page49, @xactid_row49, @xactid_ts49,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id50 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id50, @type,
       @xactid_page50, @xactid_row50, @xactid_ts50,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id51 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id51, @type,
       @xactid_page51, @xactid_row51, @xactid_ts51,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id52 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id52, @type,
       @xactid_page52, @xactid_row52, @xactid_ts52,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id53 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id53, @type,
       @xactid_page53, @xactid_row53, @xactid_ts53,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id54 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id54, @type,
       @xactid_page54, @xactid_row54, @xactid_ts54,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id55 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id55, @type,
       @xactid_page55, @xactid_row55, @xactid_ts55,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id56 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id56, @type,
       @xactid_page56, @xactid_row56, @xactid_ts56,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id57 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id57, @type,
       @xactid_page57, @xactid_row57, @xactid_ts57,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id58 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id58, @type,
       @xactid_page58, @xactid_row58, @xactid_ts58,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id59 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id59, @type,
       @xactid_page59, @xactid_row59, @xactid_ts59,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id60 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id60, @type,
       @xactid_page60, @xactid_row60, @xactid_ts60,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id61 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id61, @type,
       @xactid_page61, @xactid_row61, @xactid_ts61,
       getdate())
   if @@error <> 0
      return(1)

   if @job_id62 = 0
       return
   insert into MSjobs values (@publisher_id, @publisher_db, @job_id62, @type,
       @xactid_page62, @xactid_row62, @xactid_ts62,
       getdate())
   if @@error <> 0
      return(1)


GO

   /* Set category bit to reflect MS objects */
UPDATE sysobjects SET category = category | 2 WHERE sysstat & 0xf = 4 AND
    name = 'sp_MSadd_job'
GO

/****************************************************************************/
PRINT ''
PRINT 'Creating Stored Procedure:  dbo.sp_MSadd_job_command'
PRINT ''
/****************************************************************************/
GO
CREATE PROCEDURE sp_MSadd_job_command
@publisher_id smallint,
@publisher_db varchar (30),
@job_id int,
@command_id int,
@art_id int,
@incomplete bit,
@command varchar(255),

@job_id1 int = 0,
@command_id1 int = 0,
@art_id1 int = 0,
@incomplete1 bit = 0,
@command1 varchar(255) = NULL,

@job_id2 int = 0,
@command_id2 int = 0,
@art_id2 int = 0,
@incomplete2 bit = 0,
@command2 varchar(255) = NULL,

@job_id3 int = 0,
@command_id3 int = 0,
@art_id3 int = 0,
@incomplete3 bit = 0,
@command3 varchar(255) = NULL,

@job_id4 int = 0,
@command_id4 int = 0,
@art_id4 int = 0,
@incomplete4 bit = 0,
@command4 varchar(255) = NULL,

@job_id5 int = 0,
@command_id5 int = 0,
@art_id5 int = 0,
@incomplete5 bit = 0,
@command5 varchar(255) = NULL,

@job_id6 int = 0,
@command_id6 int = 0,
@art_id6 int = 0,
@incomplete6 bit = 0,
@command6 varchar(255) = NULL,

@job_id7 int = 0,
@command_id7 int = 0,
@art_id7 int = 0,
@incomplete7 bit = 0,
@command7 varchar(255) = NULL,

@job_id8 int = 0,
@command_id8 int = 0,
@art_id8 int = 0,
@incomplete8 bit = 0,
@command8 varchar(255) = NULL,

@job_id9 int = 0,
@command_id9 int = 0,
@art_id9 int = 0,
@incomplete9 bit = 0,
@command9 varchar(255) = NULL,

@job_id10 int = 0,
@command_id10 int = 0,
@art_id10 int = 0,
@incomplete10 bit = 0,
@command10 varchar(255) = NULL,

@job_id11 int = 0,
@command_id11 int = 0,
@art_id11 int = 0,
@incomplete11 bit = 0,
@command11 varchar(255) = NULL,

@job_id12 int = 0,
@command_id12 int = 0,
@art_id12 int = 0,
@incomplete12 bit = 0,
@command12 varchar(255) = NULL,

@job_id13 int = 0,
@command_id13 int = 0,
@art_id13 int = 0,
@incomplete13 bit = 0,
@command13 varchar(255) = NULL,

@job_id14 int = 0,
@command_id14 int = 0,
@art_id14 int = 0,
@incomplete14 bit = 0,
@command14 varchar(255) = NULL,

@job_id15 int = 0,
@command_id15 int = 0,
@art_id15 int = 0,
@incomplete15 bit = 0,
@command15 varchar(255) = NULL,


@job_id16 int = 0,
@command_id16 int = 0,
@art_id16 int = 0,
@incomplete16 bit = 0,
@command16 varchar(255) = NULL,

@job_id17 int = 0,
@command_id17 int = 0,
@art_id17 int = 0,
@incomplete17 bit = 0,
@command17 varchar(255) = NULL,

@job_id18 int = 0,
@command_id18 int = 0,
@art_id18 int = 0,
@incomplete18 bit = 0,
@command18 varchar(255) = NULL,

@job_id19 int = 0,
@command_id19 int = 0,
@art_id19 int = 0,
@incomplete19 bit = 0,
@command19 varchar(255) = NULL,

@job_id20 int = 0,
@command_id20 int = 0,
@art_id20 int = 0,
@incomplete20 bit = 0,
@command20 varchar(255) = NULL,

@job_id21 int = 0,
@command_id21 int = 0,
@art_id21 int = 0,
@incomplete21 bit = 0,
@command21 varchar(255) = NULL,

@job_id22 int = 0,
@command_id22 int = 0,
@art_id22 int = 0,
@incomplete22 bit = 0,
@command22 varchar(255) = NULL,

@job_id23 int = 0,
@command_id23 int = 0,
@art_id23 int = 0,
@incomplete23 bit = 0,
@command23 varchar(255) = NULL,

@job_id24 int = 0,
@command_id24 int = 0,
@art_id24 int = 0,
@incomplete24 bit = 0,
@command24 varchar(255) = NULL,

@job_id25 int = 0,
@command_id25 int = 0,
@art_id25 int = 0,
@incomplete25 bit = 0,
@command25 varchar(255) = NULL,

@job_id26 int = 0,
@command_id26 int = 0,
@art_id26 int = 0,
@incomplete26 bit = 0,
@command26 varchar(255) = NULL,

@job_id27 int = 0,
@command_id27 int = 0,
@art_id27 int = 0,
@incomplete27 bit = 0,
@command27 varchar(255) = NULL,

@job_id28 int = 0,
@command_id28 int = 0,
@art_id28 int = 0,
@incomplete28 bit = 0,
@command28 varchar(255) = NULL,

@job_id29 int = 0,
@command_id29 int = 0,
@art_id29 int = 0,
@incomplete29 bit = 0,
@command29 varchar(255) = NULL,

@job_id30 int = 0,
@command_id30 int = 0,
@art_id30 int = 0,
@incomplete30 bit = 0,
@command30 varchar(255) = NULL,

@job_id31 int = 0,
@command_id31 int = 0,
@art_id31 int = 0,
@incomplete31 bit = 0,
@command31 varchar(255) = NULL,

@job_id32 int = 0,
@command_id32 int = 0,
@art_id32 int = 0,
@incomplete32 bit = 0,
@command32 varchar(255) = NULL,

@job_id33 int = 0,
@command_id33 int = 0,
@art_id33 int = 0,
@incomplete33 bit = 0,
@command33 varchar(255) = NULL,

@job_id34 int = 0,
@command_id34 int = 0,
@art_id34 int = 0,
@incomplete34 bit = 0,
@command34 varchar(255) = NULL,

@job_id35 int = 0,
@command_id35 int = 0,
@art_id35 int = 0,
@incomplete35 bit = 0,
@command35 varchar(255) = NULL,


@job_id36 int = 0,
@command_id36 int = 0,
@art_id36 int = 0,
@incomplete36 bit = 0,
@command36 varchar(255) = NULL,

@job_id37 int = 0,
@command_id37 int = 0,
@art_id37 int = 0,
@incomplete37 bit = 0,
@command37 varchar(255) = NULL,

@job_id38 int = 0,
@command_id38 int = 0,
@art_id38 int = 0,
@incomplete38 bit = 0,
@command38 varchar(255) = NULL,

@job_id39 int = 0,
@command_id39 int = 0,
@art_id39 int = 0,
@incomplete39 bit = 0,
@command39 varchar(255) = NULL,

@job_id40 int = 0,
@command_id40 int = 0,
@art_id40 int = 0,
@incomplete40 bit = 0,
@command40 varchar(255) = NULL,

@job_id41 int = 0,
@command_id41 int = 0,
@art_id41 int = 0,
@incomplete41 bit = 0,
@command41 varchar(255) = NULL,

@job_id42 int = 0,
@command_id42 int = 0,
@art_id42 int = 0,
@incomplete42 bit = 0,
@command42 varchar(255) = NULL,

@job_id43 int = 0,
@command_id43 int = 0,
@art_id43 int = 0,
@incomplete43 bit = 0,
@command43 varchar(255) = NULL,

@job_id44 int = 0,
@command_id44 int = 0,
@art_id44 int = 0,
@incomplete44 bit = 0,
@command44 varchar(255) = NULL,

@job_id45 int = 0,
@command_id45 int = 0,
@art_id45 int = 0,
@incomplete45 bit = 0,
@command45 varchar(255) = NULL,

@job_id46 int = 0,
@command_id46 int = 0,
@art_id46 int = 0,
@incomplete46 bit = 0,
@command46 varchar(255) = NULL,

@job_id47 int = 0,
@command_id47 int = 0,
@art_id47 int = 0,
@incomplete47 bit = 0,
@command47 varchar(255) = NULL,

@job_id48 int = 0,
@command_id48 int = 0,
@art_id48 int = 0,
@incomplete48 bit = 0,
@command48 varchar(255) = NULL,

@job_id49 int = 0,
@command_id49 int = 0,
@art_id49 int = 0,
@incomplete49 bit = 0,
@command49 varchar(255) = NULL

as

   set nocount on

   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id,
       @command_id, @art_id, @incomplete, @command)
   if @@error <> 0
      return(1)

   if (@job_id1 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id1 ,
       @command_id1 , @art_id1, @incomplete1, @command1)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id2 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id2 ,
       @command_id2 , @art_id2, @incomplete2, @command2)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id3 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id3 ,
       @command_id3 , @art_id3, @incomplete3, @command3)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id4 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id4 ,
       @command_id4 , @art_id4, @incomplete4, @command4)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id5 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id5 ,
       @command_id5 , @art_id5, @incomplete5, @command5)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id6 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id6 ,
       @command_id6 , @art_id6, @incomplete6, @command6)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id7 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id7 ,
       @command_id7 , @art_id7, @incomplete7, @command7)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id8 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id8 ,
       @command_id8 , @art_id8, @incomplete8, @command8)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id9 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id9 ,
       @command_id9 , @art_id9, @incomplete9, @command9)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id10 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id10,
       @command_id10,  @art_id10, @incomplete10, @command10)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id11 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id11,
       @command_id11,  @art_id11, @incomplete11, @command11)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id12 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id12,
       @command_id12,  @art_id12, @incomplete12, @command12)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id13 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id13,
       @command_id13,  @art_id13, @incomplete13, @command13)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id14 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id14,
       @command_id14,  @art_id14, @incomplete14, @command14)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id15 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id15,
       @command_id15,  @art_id15, @incomplete15, @command15)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id16 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id16,
       @command_id16,  @art_id16, @incomplete16, @command16)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id17 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id17,
       @command_id17,  @art_id17, @incomplete17, @command17)
   if @@error <> 0
      return(1)
   end
   else
   return 0


   if (@job_id18 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id18,
       @command_id18,  @art_id18, @incomplete18, @command18)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id19 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id19,
       @command_id19, @art_id19, @incomplete19, @command19)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id20 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id20,
       @command_id20, @art_id20, @incomplete20, @command20)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id21 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id21,
       @command_id21, @art_id21, @incomplete21, @command21)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id22 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id22,
       @command_id22, @art_id22, @incomplete22, @command22)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id23 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id23,
       @command_id23, @art_id23, @incomplete23, @command23)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id24 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id24,
       @command_id24, @art_id24, @incomplete24, @command24)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id25 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id25,
       @command_id25, @art_id25, @incomplete25, @command25)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id26 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id26,
       @command_id26, @art_id26, @incomplete26, @command26)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id27 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id27,
       @command_id27, @art_id27, @incomplete27, @command27)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id28 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id28,
       @command_id28, @art_id28, @incomplete28, @command28)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id29 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id29,
       @command_id29, @art_id29, @incomplete29, @command29)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id30 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id30,
       @command_id30, @art_id30, @incomplete30, @command30)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id31 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id31,
       @command_id31, @art_id31, @incomplete31, @command31)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id32 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id32,
       @command_id32, @art_id32, @incomplete32, @command32)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id33 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id33,
       @command_id33, @art_id33, @incomplete33, @command33)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id34 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id34,
       @command_id34, @art_id34, @incomplete34, @command34)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id35 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id35,
       @command_id35, @art_id35, @incomplete35, @command35)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id36 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id36,
       @command_id36, @art_id36, @incomplete36, @command36)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id37 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id37,
       @command_id37, @art_id37, @incomplete37,  @command37)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id38 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id38,
       @command_id38, @art_id38, @incomplete38, @command38)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id39 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id39,
       @command_id39, @art_id39, @incomplete39, @command39)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id40 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id40,
       @command_id40, @art_id40, @incomplete40, @command40)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id41 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id41,
       @command_id41, @art_id41, @incomplete41, @command41)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id42 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id42,
       @command_id42, @art_id42, @incomplete42, @command42)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id43 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id43,
       @command_id43, @art_id43, @incomplete43, @command43)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id44 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id44,
       @command_id44, @art_id44, @incomplete44, @command44)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id45 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id45,
       @command_id45, @art_id45, @incomplete45, @command45)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id46 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id46,
       @command_id46, @art_id46, @incomplete46, @command46)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id47 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id47,
       @command_id47, @art_id47, @incomplete47, @command47)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id48 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id48,
       @command_id48, @art_id48, @incomplete48, @command48)
   if @@error <> 0
      return(1)
   end
   else
   return 0

   if (@job_id49 <> 0)
   begin
   insert into MSjob_commands values (@publisher_id, @publisher_db, @job_id49,
       @command_id49, @art_id49, @incomplete49, @command49)
   if @@error <> 0
      return(1)
   end
   else
   return 0

GO

/* Set category bit to reflect MS objects */
UPDATE sysobjects SET category = category | 2 WHERE sysstat & 0xf = 4 AND
    name = 'sp_MSadd_job_command'
go

/****************************************************************************/
PRINT ''
PRINT 'Creating Stored Procedure:  dbo.sp_MSsubscriber_status'
PRINT ''
/****************************************************************************/
GO
CREATE PROCEDURE sp_MSsubscriber_status
@publisher_id smallint,
@publisher_db varchar (30),
@job_id int,
@subscriber_id smallint,
@subscriber_db varchar (30),
@delivered_jobs int = 0,
@delivery_time int = 0,        /* milliseconds */
@status int

AS

   set nocount on

   declare @entry_time datetime
   declare @completion_time datetime
   declare @total_delivered_jobs int
   declare @delivery_latency int
   declare @delivery_rate int
   declare @delivery_seconds real

   /*
   ** If the status record already exists, return.
   */
   if exists (select * from MSsubscriber_status where job_id = @job_id and
      publisher_id = @publisher_id and
      publisher_db = @publisher_db and
      subscriber_id = @subscriber_id and
      subscriber_db = @subscriber_db)
       return (0)

   begin transaction sp_MSsubscriber_status

   /* Get the entry time of the job_id of this status record */
   select @entry_time = entry_time from MSjobs where
      publisher_id = @publisher_id and
      publisher_db = @publisher_db and
      job_id = @job_id

   /* Calculate delivery lantency of the job */
   select @completion_time = getdate()
   select @delivery_latency = datediff(second, @entry_time, @completion_time)
   if @delivery_latency is null
      select @delivery_latency = 0

   if @delivery_time is null
      select @delivery_time = 0

   /* Calculate transactions delivered/sec */
   if @delivery_time <> 0
      begin
         select @delivery_seconds = @delivery_time/1000.0
         select @delivery_rate = @delivered_jobs / @delivery_seconds
      end

   if @delivery_rate is null
      select @delivery_rate = 0

   insert MSsubscriber_status values (@publisher_id, @publisher_db, @job_id,
      @subscriber_id, @subscriber_db, @completion_time, @delivery_latency,
      @delivered_jobs, @delivery_rate, @status)
   if @@error <> 0
      begin
         rollback transaction sp_MSsubscriber_status
         return (1)
      end

   commit transaction sp_MSsubscriber_status
GO

/* Set category bit to reflect MS objects */
UPDATE sysobjects SET category = category | 2 WHERE sysstat & 0xf = 4 AND
    name = 'sp_MSsubscriber_status'
GO

/****************************************************************************/
PRINT ''
PRINT 'Creating Stored Procedure:  dbo.sp_MSadd_subscription'
PRINT ''
/****************************************************************************/
GO
CREATE PROCEDURE sp_MSadd_subscription
@publisher varchar (30),
@publisher_db varchar (30),
@subscriber varchar (30),
@art_id int,
@subscriber_db varchar (30),
@status tinyint,    /* 0 = inactive, 1 = subscribed, 2 = active */
@sub_ts binary (8)

as

set nocount on

   declare @publisher_id smallint
   declare @subscriber_id smallint
   declare @command varchar (255)
   declare @type tinyint
   declare @commit_batch_size int
   declare @status_batch_size int
   declare @frequency_type int
   declare @frequency_interval int
   declare @frequency_relative_interval int
   declare @frequency_recurrence_factor int
   declare @frequency_subday int
   declare @frequency_subday_interval int
   declare @active_start_time_of_day int
   declare @active_end_time_of_day int
   declare @active_start_date int
   declare @active_end_date int
   declare @database varchar (30)
   declare @name varchar (60)
   declare @retcode int
   declare @login varchar (30)
   declare @password varchar (30)
   declare @retryattempts int
   declare @retrydelay int


   begin transaction MSadd_subscription

   select @publisher_id = srvid from master..sysservers where
       srvname = @publisher
   select @subscriber_id = srvid from master..sysservers where
       srvname = @subscriber

   /*
   ** Insert subscription
   */
   insert into MSjob_subscriptions values (@publisher, @publisher_id,
       @publisher_db, @subscriber, @subscriber_id, @art_id,
       @subscriber_db, @status, @sub_ts)
   if @@ERROR <> 0
       begin
           rollback transaction MSadd_subscription
       return (1)
       end

   /*
   ** If no subscriber information then return
   */
   if not exists (select * from MSsubscriber_info where
       publisher = @publisher and
    subscriber = @subscriber)
    begin
       commit transaction MSadd_subscription
       return 0
    end

   /* Construct task command */
   select @command = '-S' + @subscriber  + ' '
   select @login = login, @password = password,
       @type = type, @commit_batch_size = commit_batch_size,
       @status_batch_size = status_batch_size
       from
       MSsubscriber_info
       where
       publisher = @publisher and
       subscriber = @subscriber
   if @login is not null
       select @command = @command  + '-U' + @login + ' '
   if @password is not null
       select @command = @command  + '-P' + @password+ ' '
   select @command = @command  + '-d' + @subscriber_db + ' '
   select @command = @command + '-p' + @publisher + ' '
   if @type = 1 /* Commit is always 1 for non-SQL Server subscribers */
      select @commit_batch_size = 1
   if @commit_batch_size is not null and @commit_batch_size <> 0
       select @command = @command + '-c' + convert (varchar(10),@commit_batch_size) + ' '
   if @status_batch_size is not null and @status_batch_size <> 0
       select @command = @command + '-b' + convert (varchar(10),@status_batch_size) + ' '
   select @command = @command + '-n' + @publisher_db + ' '
   select @command = @command + '-i2000 '
   select @command = @command + '-t' + convert (varchar(10),@type) + ' '

   /*
   ** Construct unique name
   */
   select @name =  @publisher + '_' + @publisher_db + '_' +
       @subscriber + '_' + @subscriber_db

   /*
   ** Create a distribution task, if one does not already exists.
   */
   if exists (select * from msdb..systasks_view where
       subsystem = 'Distribution' and
       name = @name)
       begin
           commit transaction MSadd_subscription
       return 0
       end

   /*
   ** Get sp_addtask parameters from MSsubscriber_info
   */
   select @database = DB_name()
   select @frequency_type = frequency_type,
          @frequency_interval = frequency_interval,
          @frequency_relative_interval = frequency_relative_interval,
          @frequency_recurrence_factor = frequency_recurrence_factor,
          @frequency_subday = frequency_subday,
          @frequency_subday_interval = frequency_subday_interval,
          @active_start_time_of_day = active_start_time_of_day,
          @active_end_time_of_day = active_end_time_of_day,
          @active_start_date = active_start_date,
          @active_end_date = active_end_date,
          @retryattempts = retryattempts,
          @retrydelay = retrydelay
          from MSsubscriber_info where
          publisher = @publisher and
          subscriber = @subscriber

   execute @retcode = msdb..sp_addtask
       @name = @name,
       @subsystem = 'Distribution',
       @server = @@SERVERNAME,
       @username = NULL,
       @databasename = @database,
       @enabled = 1,
       @freqtype = @frequency_type,
       @freqinterval = @frequency_interval,
       @freqsubtype = @frequency_subday,
       @freqsubinterval = @frequency_subday_interval,
       @freqrelativeinterval = @frequency_relative_interval,
       @freqrecurrencefactor = 0,
       @activestartdate = @active_start_date,
       @activeenddate = @active_end_date,
       @activestarttimeofday = @active_start_time_of_day,
       @activeendtimeofday = @active_end_time_of_day,
       @nextrundate = 0,
       @nextruntime = 0,
       @runpriority = 0,
       @emailoperatorname = NULL,
       @retryattempts = @retryattempts,
       @retrydelay = @retrydelay,
       @command = @command,
       @loghistcompletionlevel = 0,
       @emailcompletionlevel = 0,
       @description = NULL
   if @@ERROR <> 0 or @retcode <> 0
      begin
          rollback transaction MSadd_subscription
          return (1)
      end

   /* Make sure the task is owned by 'repl_subscriber' */
   if (suser_id() = 1)
      begin
         exec @retcode =  msdb.dbo.sp_reassigntask @name, 'repl_subscriber', 'sa'
         if @@ERROR <> 0 or @retcode <> 0
            begin
               rollback transaction MSadd_subscription
           return (1)
        end
      end

   commit transaction MSadd_subscription
GO

/* Set category bit to reflect MS objects */
UPDATE sysobjects SET category = category | 2 WHERE sysstat & 0xf = 4 AND
    name = 'sp_MSadd_subscription'
GO

/****************************************************************************/
PRINT ''
PRINT 'Creating Stored Procedure:  dbo.sp_MSdrop_subscription'
PRINT ''
/****************************************************************************/
GO
CREATE PROCEDURE sp_MSdrop_subscription
@publisher varchar (30),
@publisher_db varchar (30),
@subscriber varchar (30),
@art_id int,
@subscriber_db varchar (30)

as

   set nocount on

   declare @publisher_id smallint
   declare @subscriber_id smallint
   declare @name varchar (60)
   declare @retcode int

   begin transaction MSdrop_subscription

   select @publisher_id = srvid from master..sysservers where
       srvname = @publisher
   select @subscriber_id = srvid from master..sysservers where
       srvname = @subscriber

   /* Delete the all subscriber jobs based on the subscription */
   delete from MSsubscriber_jobs where
       publisher_id = @publisher_id and
       publisher_db = @publisher_db and
       subscriber_id = @subscriber_id and
       subscriber_db = @subscriber_db and
       job_id in (select distinct job_id
           from MSjob_commands (index = ucMSjob_commands)
           where
           publisher_id = @publisher_id and
           publisher_db = @publisher_db and
           art_id = @art_id)

   if @@ERROR <> 0
       begin
           rollback transaction MSdrop_subscription
       return 1
       end

   /* Delete the subscription */
   delete from MSjob_subscriptions where
       art_id = @art_id and
       publisher_id = @publisher_id and
       publisher_db = @publisher_db and
       subscriber_id = @subscriber_id and
       subscriber_db = @subscriber_db

   if @@ERROR <> 0
       begin
           rollback transaction MSdrop_subscription
       return 1
       end

   /*
   ** If there are other subscriptions on the subscriber database,
   ** then return.  Otherwise, delete corresponding distribution process and
   ** set last job xactid_page = -1.
   */
   if exists (select * from MSjob_subscriptions where
       subscriber = @subscriber and
       subscriber_db = @subscriber_db)
       begin
          commit transaction MSdrop_subscription
          return 0
       end

   /* Set transaction rid value to -1, 0. This signals the Log Reader to
   ** go to last distributed transaction.
   */
   update MSjobs set xactid_page = -1, xactid_row = 0 where
       publisher_id = @publisher_id and
       publisher_db = @publisher_db  and
       job_id = (select max(job_id) from MSjobs where
          publisher_id = @publisher_id and
          publisher_db = @publisher_db)
   if @@error <> 0
         return (1)

   /*
   ** Drop the distribution task, if one exists.
   */
   select @name =  @publisher + '_' + @publisher_db + '_' +
       @subscriber + '_' + @subscriber_db
   if not exists (select * from msdb..systasks_view where
      name = @name)
       begin
           commit transaction MSdrop_subscription
       return 0
       end

   /*
   ** Delete distribution task.
   */
   execute @retcode = msdb..sp_droptask @name = @name
   if @@ERROR <> 0 or @retcode <> 0
       begin
           rollback transaction MSdrop_subscription
       return 1
       end

   commit transaction MSdrop_subscription
GO

/* Set category bit to reflect MS objects */
UPDATE sysobjects SET category = category | 2 WHERE sysstat & 0xf = 4 AND
    name = 'sp_MSdrop_subscription'
GO

/****************************************************************************/
PRINT ''
PRINT 'Creating Stored Procedure:  dbo.sp_MSupdate_subscription'
PRINT ''
/****************************************************************************/
GO
CREATE PROCEDURE sp_MSupdate_subscription
@publisher varchar (30),
@publisher_db varchar (30),
@subscriber varchar (30),
@art_id int,
@status int,
@sub_ts binary (8)

as

   set nocount on

   declare @publisher_id smallint
   declare @subscriber_id smallint

   begin transaction MSupdate_subscription

   select @publisher_id = srvid from master..sysservers where
       srvname = @publisher
   select @subscriber_id = srvid from master..sysservers where
       srvname = @subscriber

   /*
   ** If the subscription is not defined then return.
   */
   if not exists (select * from MSjob_subscriptions where
       publisher_id = @publisher_id and
       publisher_db = @publisher_db and
       subscriber_id = @subscriber_id and
       art_id = @art_id)
       begin
           rollback transaction MSupdate_subscription
           return (1)
       end

   /*
   ** Update subscription
   */
   update MSjob_subscriptions set status = @status, ts = @sub_ts where
      publisher_id = @publisher_id and
      publisher_db = @publisher_db and
      subscriber_id = @subscriber_id and
      art_id = @art_id
   if @@ERROR <> 0
      begin
      rollback transaction MSupdate_subscription
      return (1)
      end

   commit transaction MSupdate_subscription
GO

/* Set category bit to reflect MS objects */
UPDATE sysobjects SET category = category | 2 WHERE sysstat & 0xf = 4 AND
    name = 'sp_MSupdate_subscription'
GO

/****************************************************************************/
PRINT ''
PRINT 'Creating Stored Procedure:  dbo.sp_MSadd_subscriber_job'
PRINT ''
/****************************************************************************/
GO
CREATE PROCEDURE sp_MSadd_subscriber_job
@publisher_id smallint = 0,
@publisher_db varchar ( 30),
@job_id int,
@subscriber varchar (30) = '%'
AS
   set nocount on
   set forceplan on

   /*
   ** To receive sql commands the subscription status must be 'active'.
   */
   if ((select type from MSjobs (index = ucMSjobs) where
      publisher_id = @publisher_id and publisher_db = @publisher_db and
      job_id = @job_id) = 0)
      begin
         insert MSsubscriber_jobs select @publisher_id, @publisher_db,
        jc.job_id, s.subscriber_id, s.subscriber_db, jc.command_id from
            MSjob_commands jc (index = ucMSjob_commands),
        MSjobs j (index = ucMSjobs),
            MSjob_subscriptions s (index = ucMSsubscriptions)
            where
        jc.publisher_id = @publisher_id and
        jc.publisher_db = @publisher_db and
            jc.job_id >= @job_id and
        j.publisher_id = jc.publisher_id and
        j.publisher_db = jc.publisher_db and
            j.job_id = jc.job_id and
            s.publisher_id = jc.publisher_id and
            s.publisher_db = jc.publisher_db and
            s.art_id = jc.art_id and
            s.subscriber like @subscriber and
        s.status = 2 and
            s.ts <= j.xactid_ts
      end
   else
      begin
         insert MSsubscriber_jobs select @publisher_id, @publisher_db,
        jc.job_id, s.subscriber_id, s.subscriber_db, jc.command_id from
            MSjob_commands jc (index = ucMSjob_commands),
        MSjobs j (index = ucMSjobs),
            MSjob_subscriptions s (index = ucMSsubscriptions)
            where
        jc.publisher_id = @publisher_id and
        jc.publisher_db = @publisher_db and
            jc.job_id >= @job_id and
        j.publisher_id = jc.publisher_id and
        j.publisher_db = jc.publisher_db and
            j.job_id = jc.job_id and
            s.publisher_id = jc.publisher_id and
            s.publisher_db = jc.publisher_db and
            s.art_id = jc.art_id and
            s.subscriber like @subscriber and
        s.status > 0 and
            s.ts <= j.xactid_ts
      end

   if @@error <> 0
      return(1)

GO

/* Set category bit to reflect MS objects */
UPDATE sysobjects SET category = category | 2 WHERE sysstat & 0xf = 4 AND
    name = 'sp_MSadd_subscriber_job'
GO

/****************************************************************************/
PRINT ''
PRINT 'Creating Stored Procedure:  dbo.sp_MSget_subscriber_jobs'
PRINT ''
/****************************************************************************/
GO
CREATE PROCEDURE sp_MSget_subscriber_jobs
@publisher_id smallint,
@publisher_db varchar (30) = '%',
@subscriber_id smallint,
@subscriber_db varchar (30),
@last_job_id int,
@num_jobs int = 0

as

   set nocount on
   set forceplan on

   select jc.job_id, jc.incomplete, j.type, jc.command
      from
      MSsubscriber_jobs sj (index = ucMSsubscriber_jobs),
      MSjob_commands jc (NOLOCK),
      MSjobs j (NOLOCK)
      where
      sj.publisher_id = @publisher_id and
      sj.publisher_db = @publisher_db and
      sj.job_id >= (select min(sj.job_id)
       from
       MSsubscriber_jobs sj (index = ucMSsubscriber_jobs)
       where
       sj.publisher_id = @publisher_id and
       sj.publisher_db = @publisher_db and
       sj.job_id > @last_job_id and
       sj.subscriber_id = @subscriber_id and
       sj.subscriber_db = @subscriber_db ) and
      (@num_jobs = 0 or
      sj.job_id < @num_jobs + (select min(sj.job_id)
       from
       MSsubscriber_jobs sj (index = ucMSsubscriber_jobs)
       where
       sj.publisher_id = @publisher_id and
       sj.publisher_db = @publisher_db and
       sj.job_id > @last_job_id and
       sj.subscriber_id = @subscriber_id and
       sj.subscriber_db = @subscriber_db)) and
       sj.subscriber_id = @subscriber_id and
       sj.subscriber_db = @subscriber_db and
       jc.publisher_id = sj.publisher_id and
       jc.publisher_db = sj.publisher_db and
       jc.job_id = sj.job_id and
       jc.command_id = sj.command_id and
       j.job_id = sj.job_id and
       j.publisher_id = sj.publisher_id and
       j.publisher_db = sj.publisher_db
GO

/* Set category bit to reflect MS objects */
UPDATE sysobjects SET category = category | 2 WHERE sysstat & 0xf = 4 AND
    name = 'sp_MSget_subscriber_jobs'
GO

/****************************************************************************/
PRINT ''
PRINT 'Creating Stored Procedure:  dbo.sp_MSfind_subscriber_job'
PRINT ''
/****************************************************************************/
GO
CREATE PROCEDURE sp_MSfind_subscriber_job
@publisher_id smallint,
@publisher_db varchar (30),
@subscriber_id smallint,
@subscriber_db varchar (30),
@job_id int

as

   set nocount on

   select job_id
      from
      MSsubscriber_jobs (index = ucMSsubscriber_jobs)
      where
      publisher_db = @publisher_db and
      publisher_id = @publisher_id and
      subscriber_db = @subscriber_db and
      subscriber_id = @subscriber_id and
      job_id = @job_id
GO

/* Set category bit to reflect MS objects */
UPDATE sysobjects SET category = category | 2 WHERE sysstat & 0xf = 4 AND
    name = 'sp_MSfind_subscriber_job'
GO

/****************************************************************************/
PRINT ''
PRINT 'Creating Stored Procedure:  dbo.sp_MSget_last_job'
PRINT ''
/****************************************************************************/
GO
CREATE PROCEDURE sp_MSget_last_job
@publisher_id int,
@publisher_db varchar (30),
@ex_tablock bit = 0

AS

   set nocount on

   if @ex_tablock = 1
      select max(job_id) from MSjobs (TABLOCKX index = ucMSjobs) where
       publisher_id = @publisher_id
       and publisher_db = @publisher_db
   else
      select max(job_id) from MSjobs (UPDLOCK index = ucMSjobs) where
       publisher_id = @publisher_id
       and publisher_db = @publisher_db

GO

/* Set category bit to reflect MS objects */
UPDATE sysobjects SET category = category | 2 WHERE sysstat & 0xf = 4 AND
    name = 'sp_MSget_last_job'
GO

/****************************************************************************/
PRINT ''
PRINT 'Creating Stored Procedure:  dbo.sp_MSget_last_trans'
PRINT ''
/****************************************************************************/
GO
CREATE PROCEDURE sp_MSget_last_trans
@publisher_id int,
@publisher_db varchar (30)

AS

   set nocount on

   select job_id, xactid_page, xactid_row, xactid_ts, type from MSjobs where
       publisher_id = @publisher_id and
       publisher_db = @publisher_db and
       job_id = (select max(job_id) from MSjobs
           where publisher_id = @publisher_id
           and publisher_db = @publisher_db
           and not (xactid_page = 0 and xactid_row = 0))

GO

/* Set category bit to reflect MS objects */
UPDATE sysobjects SET category = category | 2 WHERE sysstat & 0xf = 4 AND
    name = 'sp_MSget_last_trans'
GO

/****************************************************************************/
PRINT ''
PRINT 'Creating Stored Procedure:  dbo.sp_MSadd_subscriber_info'
PRINT ''
/****************************************************************************/
GO
CREATE PROCEDURE sp_MSadd_subscriber_info
@publisher varchar (30),
@subscriber varchar (30),
@type tinyint,
@login varchar (30) = NULL,
@password varchar (30) = NULL,
@commit_batch_size int,
@status_batch_size int,
@flush_frequency int,
@frequency_type int,
@frequency_interval int,
@frequency_relative_interval int,
@frequency_recurrence_factor int,
@frequency_subday int,
@frequency_subday_interval int,
@active_start_time_of_day int,
@active_end_time_of_day int,
@active_start_date int,
@active_end_date int,
@retryattempts int = 0,
@retrydelay int = 0,
@description varchar (255) = NULL

AS
   set nocount on

   declare @name varchar (30)
   declare @database varchar (30)
   declare @command varchar (255)
   declare @retcode int

   /* Add the subscriber to sysservers as a RPC server, if it does not
   ** already exist.
   */
   if not exists (select * from  master..sysservers where  srvname = @subscriber)
      begin
          exec @retcode = sp_addserver @subscriber
          if @retcode <> 0
             return 1
      end

   /*
   ** Set default retries for continuous distribution task to every minute
   ** for 24 hours.
   **
   */
   if @retryattempts = 0 and @retrydelay = 0 and @frequency_type = 64
      begin
       select @retryattempts = 1440
       select @retrydelay = 1
      end

   /* Delete any existing row */
   if exists (select * from MSsubscriber_info where
       publisher = @publisher and
       subscriber = @subscriber)
       begin
           delete from MSsubscriber_info where publisher = @publisher and
         subscriber = @subscriber
          if @@error <> 0
             return 1
       end

   insert MSsubscriber_info (publisher, subscriber, type, login, password,
      commit_batch_size, status_batch_size, flush_frequency,
      frequency_type, frequency_interval, frequency_relative_interval,
      frequency_recurrence_factor, frequency_subday, frequency_subday_interval,
      active_start_time_of_day, active_end_time_of_day, active_start_date,
      active_end_date, retryattempts, retrydelay, description)
      values (@publisher, @subscriber, @type, @login, @password,
      @commit_batch_size, @status_batch_size, @flush_frequency,
      @frequency_type, @frequency_interval, @frequency_relative_interval,
      @frequency_recurrence_factor, @frequency_subday, @frequency_subday_interval,
      @active_start_time_of_day, @active_end_time_of_day, @active_start_date,
      @active_end_date, @retryattempts, @retrydelay, @description)
   if @@error <> 0
      return 1

   /*
   ** Create a distribution cleanup task for the subscriber, if one does not
   ** already exists.
   */
   select @name =  @publisher + '_' + @subscriber + '_Cleanup'

   if exists (select * from msdb..systasks_view where
       subsystem = 'TSQL' and
       name = @name)
       begin
       return 0
       end

   select @database = DB_name()

   /* Construct task command */
   select @command = @database + '..' + 'sp_replcleanup ' + '' + @publisher +
       '' + ', ' + '' + @subscriber  +  '' + ', ' +
       CONVERT( varchar(10), @flush_frequency)

   execute @retcode = msdb..sp_addtask
       @name = @name,
       @subsystem = 'TSQL',
       @server = @@SERVERNAME,
       @username = NULL,
       @databasename = @database,
       @enabled = 1,
       @freqtype = 4,
       @freqinterval = 1,
       @freqsubtype = 1,
       @freqsubinterval = 0,
       @freqrelativeinterval = 1,
       @freqrecurrencefactor = 1,
       @activestartdate = @active_start_date,
       @activeenddate = @active_end_date,
       @activestarttimeofday = 10500,
       @activeendtimeofday = 235959,
       @nextrundate = 0,
       @nextruntime = 0,
       @runpriority = 0,
       @emailoperatorname = NULL,
       @retryattempts = 0,
       @retrydelay = 0,
       @command = @command,
       @loghistcompletionlevel = 0,
       @emailcompletionlevel = 0,
       @description = NULL
   if @@ERROR <> 0 or @retcode <> 0
      begin
          return (1)
      end
GO

/* Set category bit to reflect MS objects */
UPDATE sysobjects SET category = category | 2 WHERE sysstat & 0xf = 4 AND
    name = 'sp_MSadd_subscriber_info'
GO

/****************************************************************************/
PRINT ''
PRINT 'Creating Stored Procedure:  dbo.sp_MSupdate_subscriber_info'
PRINT ''
/****************************************************************************/
GO
CREATE PROCEDURE sp_MSupdate_subscriber_info
@publisher varchar (30),
@subscriber varchar (30),
@type tinyint = NULL,
@login varchar (30) = NULL,
@password varchar (30) = NULL,
@commit_batch_size int = NULL,
@status_batch_size int = NULL,
@flush_frequency int = NULL,
@frequency_type int = NULL,
@frequency_interval int = NULL,
@frequency_relative_interval int = NULL,
@frequency_recurrence_factor int = NULL,
@frequency_subday int = NULL,
@frequency_subday_interval int = NULL,
@active_start_time_of_day int = NULL,
@active_end_time_of_day int = NULL,
@active_start_date int = NULL,
@active_end_date int = NULL,
@retryattempts int = NULL,
@retrydelay int = NULL,
@description varchar (255) = NULL

AS
   set nocount on

   declare @cmd1 varchar (255)
   declare @task_id int
   declare @retcode int

   begin transaction update_subscriber

   /* Check if subscriber exists */
   if not exists (select * from MSsubscriber_info where publisher = @publisher and subscriber = @subscriber)
       goto FAILED

   if @type is not NULL
      update MSsubscriber_info set type = @type where publisher = @publisher and subscriber = @subscriber
      if @@error <> 0
         goto FAILED

   if @login is not NULL
      update MSsubscriber_info set login = @login where publisher = @publisher and subscriber = @subscriber
      if @@error <> 0
         goto FAILED

   if @password is not NULL
      update MSsubscriber_info set password = @password where publisher = @publisher and subscriber = @subscriber
      if @@error <> 0
         goto FAILED

   if @commit_batch_size is not NULL
      update MSsubscriber_info set commit_batch_size = @commit_batch_size where publisher = @publisher and subscriber = @subscriber
      if @@error <> 0
         goto FAILED

   if @status_batch_size is not NULL
      update MSsubscriber_info set status_batch_size = @status_batch_size where publisher = @publisher and subscriber = @subscriber
      if @@error <> 0
         goto FAILED

   if @flush_frequency is not NULL
      update MSsubscriber_info set flush_frequency = @flush_frequency where publisher = @publisher and subscriber = @subscriber
      if @@error <> 0
         goto FAILED

   if @frequency_type is not NULL
      update MSsubscriber_info set frequency_type = @frequency_type where publisher = @publisher and subscriber = @subscriber
      if @@error <> 0
         goto FAILED

   if @frequency_interval is not NULL
      update MSsubscriber_info set frequency_interval = @frequency_interval where publisher = @publisher and subscriber = @subscriber
      if @@error <> 0
         goto FAILED

   if @frequency_relative_interval is not NULL
      update MSsubscriber_info set frequency_relative_interval = @frequency_relative_interval where publisher = @publisher and subscriber = @subscriber
      if @@error <> 0
         goto FAILED

   if @frequency_recurrence_factor is not NULL
      update MSsubscriber_info set frequency_recurrence_factor = @frequency_recurrence_factor where publisher = @publisher and subscriber = @subscriber
      if @@error <> 0
         goto FAILED

   if @frequency_subday is not NULL
      update MSsubscriber_info set frequency_subday = @frequency_subday where publisher = @publisher and subscriber = @subscriber
      if @@error <> 0
         goto FAILED

   if @frequency_subday_interval is not NULL
      update MSsubscriber_info set frequency_subday_interval = @frequency_subday_interval where publisher = @publisher and subscriber = @subscriber
      if @@error <> 0
         goto FAILED

   if @active_start_time_of_day is not NULL
      update MSsubscriber_info set active_start_time_of_day = @active_start_time_of_day where publisher = @publisher and subscriber = @subscriber
      if @@error <> 0
         goto FAILED

   if @active_end_time_of_day is not NULL
      update MSsubscriber_info set active_end_time_of_day = @active_end_time_of_day where publisher = @publisher and subscriber = @subscriber
      if @@error <> 0
         goto FAILED

   if @active_start_date is not NULL
      update MSsubscriber_info set active_start_date = @active_start_date where publisher = @publisher and subscriber = @subscriber
      if @@error <> 0
         goto FAILED

   if @active_end_date is not NULL
      update MSsubscriber_info set active_end_date = @active_end_date where publisher = @publisher and subscriber = @subscriber
      if @@error <> 0
         goto FAILED

   if @retryattempts is not NULL
      update MSsubscriber_info set retryattempts = @retryattempts where publisher = @publisher and subscriber = @subscriber
      if @@error <> 0
         goto FAILED

   if @retrydelay is not NULL
      update MSsubscriber_info set retrydelay = @retrydelay where publisher = @publisher and subscriber = @subscriber
      if @@error <> 0
         goto FAILED

   if @description is not NULL
      update MSsubscriber_info set description = @description where publisher = @publisher and subscriber = @subscriber
      if @@error <> 0
         goto FAILED

    /* Update all distribution task for the subscriber */
   select @cmd1 = 'declare hC1 scroll cursor for '
   select @cmd1 = @cmd1 + 'select id from msdb.dbo.systasks_view where '
   select @cmd1 = @cmd1 + 'subsystem = ''Distribution'' and server = '
   select @cmd1 = @cmd1 + '''' + @subscriber + ''''
   EXECUTE (@cmd1)
   OPEN hC1
   FETCH hC1 INTO @task_id
   WHILE (@@fetch_status <> -1)
      BEGIN
       exec @retcode = msdb..sp_updatetask
          @id = @task_id,
          @freqtype = @frequency_type,
          @freqinterval = @frequency_interval,
          @freqsubtype = @frequency_subday,
          @freqsubinterval = @frequency_subday_interval,
          @freqrelativeinterval = @frequency_relative_interval,
          @freqrecurrencefactor = @frequency_recurrence_factor,
          @activestartdate = @active_start_date,
          @activeenddate = @active_end_date,
          @activestarttimeofday = @active_start_time_of_day,
          @activeendtimeofday = @active_end_time_of_day,
          @retryattempts =  @retryattempts,
          @retrydelay = @retrydelay
       if @@error <> 0 OR @retcode <> 0
          begin
             CLOSE hC1
             DEALLOCATE hC1
             goto FAILED
          end
       FETCH hC1 INTO @task_id
      END
   CLOSE hC1
   DEALLOCATE hC1

   commit transaction update_subscriber
   return (0)

FAILED:

   rollback transaction update_subscriber
   return (1)
GO

/* Set category bit to reflect MS objects */
UPDATE sysobjects SET category = category | 2 WHERE sysstat & 0xf = 4 AND
    name = 'sp_MSupdate_subscriber_info'
GO

/****************************************************************************/
PRINT ''
PRINT 'Creating Stored Procedure:  dbo.sp_MSdrop_subscriber_info'
PRINT ''
/****************************************************************************/
GO
CREATE PROCEDURE sp_MSdrop_subscriber_info
@publisher varchar (30),
@subscriber varchar (30)

AS
   set nocount on

   declare @name varchar (30)
   declare @retcode int

   if exists (select * from MSsubscriber_info where
       subscriber = @subscriber)
       begin
           delete MSsubscriber_info where publisher = @publisher and subscriber = @subscriber
           if @@error <> 0
               return 1
       end
   /*
   ** Drop the distribution cleanup task for the subscriber, if one exists.
   */
   select @name =  @publisher + '_' + @subscriber + '_Cleanup'

   if not exists (select * from msdb..systasks_view where
       subsystem = 'TSQL' and name = @name)
       begin
       return 0
       end

   execute @retcode = msdb..sp_droptask @name = @name
   if @@ERROR <> 0 or @retcode <> 0
       begin
       return 1
       end

GO

/* Set category bit to reflect MS objects */
UPDATE sysobjects SET category = category | 2 WHERE sysstat & 0xf = 4 AND
    name = 'sp_MSdrop_subscriber_info'
GO

/****************************************************************************/
PRINT ''
PRINT 'Creating Stored Procedure:  dbo.sp_MShelp_subscriber_info'
PRINT ''
/****************************************************************************/
GO
CREATE PROCEDURE sp_MShelp_subscriber_info
@publisher varchar (30),
@subscriber varchar (30)

AS
   set nocount on

   select * from MSsubscriber_info where publisher = @publisher and subscriber = @subscriber
GO

/* Set category bit to reflect MS objects */
UPDATE sysobjects SET category = category | 2 WHERE sysstat & 0xf = 4 AND
    name = 'sp_MShelp_subscriber_info'
GO

/****************************************************************************/
PRINT ''
PRINT 'Creating Stored Procedure:  dbo.sp_MSkill_job'
PRINT ''
/****************************************************************************/
GO
CREATE PROCEDURE sp_MSkill_job

@job_id int,
@publisher varchar (30),
@publisher_db varchar (30),
@subscriber varchar (30) = '%',
@subscriber_db varchar (30) = '%'

as

   set nocount on

   delete from MSsubscriber_jobs where job_id = @job_id and
       publisher_id = (select srvid from master..sysservers where
           srvname = @publisher) and
       publisher_db = @publisher_db and
       subscriber_id IN (select srvid from master..sysservers where
           srvname like @subscriber) and
       subscriber_db like @subscriber_db
   if @@error <> 0
      return 1

GO

/* Set category bit to reflect MS objects */
UPDATE sysobjects SET category = category | 2 WHERE sysstat & 0xf = 4 AND
    name = 'sp_MSkill_job'
GO

dump transaction distribution with no_log
go

/****************************************************************************/
PRINT ''
PRINT 'Creating Stored Procedure:  dbo.sp_MSnew_sync'
PRINT ''
/****************************************************************************/
GO
CREATE PROCEDURE sp_MSnew_sync
@publisher_id smallint,
@publisher_db varchar (30),
@last_job_id int,
@article_id int,
@subscriber varchar (30)

as

   set nocount on

   declare @subscriber_id int

   /* Get the subscriber's id */
   select @subscriber_id = srvid from master..sysservers where
       srvname = @subscriber

   /*
   ** Remove all previous subscriber jobs for each subscriber in the new
   ** sync job.
   */
   delete from MSsubscriber_jobs where
       publisher_id = @publisher_id AND
       publisher_db = @publisher_db AND
       subscriber_id = @subscriber_id and
       job_id in
            (select distinct job_id from MSjob_commands
            (index = ucMSjob_commands) where
            publisher_id = @publisher_id AND
            publisher_db = @publisher_db AND
            job_id <= @last_job_id AND
            art_id = @article_id)
   if @@error <> 0
         return (1)

GO

/* Set category bit to reflect MS objects */
UPDATE sysobjects SET category = category | 2 WHERE sysstat & 0xf = 4 AND
    name = 'sp_MSnew_sync'
GO

print ''
print 'Creating procedure sp_MSdistribution_counters'
go
CREATE PROCEDURE sp_MSdistribution_counters (
        @publisher varchar(30)      /* publication server name */
        ) AS

    SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    DECLARE @publisher_id smallint
    DECLARE @subscriber_id smallint
    DECLARE @publisher_db varchar(30)
    DECLARE @subscriber varchar(30)
    DECLARE @subscriber_db varchar(30)
    DECLARE @max_ss_jobid int
    DECLARE @delivered_jobs int
    DECLARE @undelivered_jobs int
    DECLARE @delivery_rate int
    DECLARE @delivery_latency int

    /* Make sure publisher is defined on distributor */
    SELECT @publisher_id = srvid FROM master..sysservers WHERE
    srvname = @publisher
    if @publisher_id is null
       return (1)

   create table #distcounters (
    publisher_db varchar (30),
        subscriber varchar (30),
    subscriber_id smallint,
    subscriber_db varchar (30),
    delivered_jobs int,
    undelivered_jobs int,
    delivery_rate int,
    delivery_latency int)

    /* Open a cursor to get each publisher/subscriber database pair */
    declare hC cursor for select distinct publisher_db,
      subscriber, subscriber_id, subscriber_db
      from MSjob_subscriptions
      where publisher_id = @publisher_id for read only

    open hC
    fetch hC into @publisher_db, @subscriber, @subscriber_id,
       @subscriber_db
    while (@@fetch_status <> -1)
      begin
     select @max_ss_jobid = NULL
     select @delivery_rate = NULL
     select @delivery_latency = NULL

         /*
     ** Get the subscriber status info of the last subscriber status
     ** entry.
     */
     select @max_ss_jobid = job_id,
        @delivery_rate = delivery_rate,
        @delivery_latency = delivery_latency from
        MSsubscriber_status (index = ucMSsubscriber_status) where
        publisher_id = @publisher_id and
        publisher_db = @publisher_db and
        subscriber_id = @subscriber_id and
        subscriber_db = @subscriber_db and
        job_id = (select max(job_id) from MSsubscriber_status ss2
           (index = ucMSsubscriber_status) where
           publisher_id = @publisher_id and
           publisher_db = @publisher_db and
           subscriber_id = @subscriber_id and
           subscriber_db = @subscriber_db)

     /* Get the number of delivered jobs */
     select @delivered_jobs = count (distinct job_id) from
        MSsubscriber_jobs (index = ucMSsubscriber_jobs) where
        publisher_id = @publisher_id and
        publisher_db = @publisher_db and
        subscriber_id = @subscriber_id and
        subscriber_db = @subscriber_db and
        job_id <= isnull(@max_ss_jobid, 0)

     /* Get the number of undelivered jobs */
     select @undelivered_jobs = count (distinct job_id) from
        MSsubscriber_jobs (index = ucMSsubscriber_jobs) where
        publisher_id = @publisher_id and
        publisher_db = @publisher_db and
        subscriber_id = @subscriber_id and
        subscriber_db = @subscriber_db and
        job_id > isnull(@max_ss_jobid, 0)

      insert into #distcounters values (
        @publisher_db,
        @subscriber,
        @subscriber_id,
        @subscriber_db,
        isnull(@delivered_jobs, 0),
        isnull(@undelivered_jobs, 0),
        isnull(@delivery_rate, 0),
        isnull(@delivery_latency, 0))

     fetch hC into @publisher_db, @subscriber, @subscriber_id,
            @subscriber_db
      end
    close hC
    DEALLOCATE hC

    select subscriber, 'delivered jobs' = sum(delivered_jobs),
       'undelivered jobs' = sum(undelivered_jobs),
       'delivery rate' = avg(delivery_rate),
       'delivery latency' = avg(delivery_latency)
       from #distcounters
       group by subscriber

    drop table #distcounters
GO

/* Set category bit to reflect MS objects */
UPDATE sysobjects SET category = category | 2 WHERE sysstat & 0xf = 4 AND
    name = 'sp_MSdistribution_counters'
GO

/****************************************************************************/
PRINT ''
PRINT 'Creating Stored Procedure:  dbo.sp_MSremove_published_jobs'
PRINT ''
/****************************************************************************/
GO
CREATE PROCEDURE sp_MSremove_published_jobs
@publisher varchar(30),
@publisher_db varchar(30)

as
   declare @publisher_id smallint
   declare @retcode int
   declare @cmd1 varchar (255)
   declare @cmd2 varchar (255)
   declare @cmd3 varchar (255)
   declare @delcmd varchar (255)
   declare @job_command varchar (255)
   declare @file varchar (255)
   declare @table varchar (255)


   SET NOCOUNT ON

   /*
   ** Make sure the publisher is valid.
   */
   select @publisher_id = srvid from master..sysservers where
       srvname = @publisher
   if @publisher_id is NULL
   begin
      RAISERROR(14048, 16, -1, @publisher)
      return (1)
   end

   /*
   ** For each sync command that will be removed delete the sync file.
   */
   select @cmd1 = ''
   select @cmd2 = ''
   select @cmd3 = ''
   select @cmd1 = @cmd1 + 'declare hC1 scroll cursor for '
   select @cmd1 = @cmd1 + 'select jc.command from MSjobs j (index = ucMSjobs), MSjob_commands jc (index = ucMSjob_commands) '
   select @cmd1 = @cmd1 + 'where j.publisher_id = '
   select @cmd1 = @cmd1 + convert (varchar (10), @publisher_id) + ' and '
   select @cmd2 = @cmd2 + 'j.publisher_db = ''' + @publisher_db + ''' and '
   select @cmd2 = @cmd2 + 'jc.publisher_id = j.publisher_id '
   select @cmd3 = @cmd3 + 'and jc.publisher_db = j.publisher_db and '
   select @cmd3 = @cmd3 + 'jc.job_id = j.job_id and '
   select @cmd3 = @cmd3 + '(j.type = 3 or j.type = 4 or j.type = 5)'

   EXECUTE (@cmd1+@cmd2+@cmd3)
   OPEN hC1
   FETCH hC1 INTO @job_command
   WHILE (@@fetch_status <> -1)
      BEGIN
         EXEC @retcode = master..xp_sscanf @job_command, 'sync -t%s -d%s',
           @value1 = @table output, @value2 =@file output
         IF @retcode <> 0
     begin
        rollback transaction replcleanup
        CLOSE hC1
        DEALLOCATE hC1
            return (1)
     end

         EXEC @retcode = master..xp_sprintf @string = @delcmd output,
        @format = 'del %s', @value = @file
         IF @retcode <> 0
     begin
        CLOSE hC1
        DEALLOCATE hC1
            return (1)
     end

         EXEC master..xp_cmdshell @delcmd

         FETCH hC1 INTO @job_command
      END
   CLOSE hC1
   DEALLOCATE hC1

   /*
   ** Remove all but the last job of the publisher database jobs.  The last
   ** job is left incase the datatbase is enabled again. This will allow
   ** subscriber's last job ids to go unmodified.
   */
   delete from MSjobs where
    publisher_id = @publisher_id and
    publisher_db = @publisher_db and
    job_id <> (select max(job_id) from MSjobs j where
            j.publisher_id = @publisher_id and
            j.publisher_db = @publisher_db)
   if @@error <> 0
      return (1)

   /*
   ** Set transaction rid to -1, 0. This will set the published log to the
   ** last distributed transaction.
   */
   update MSjobs set xactid_page = -1, xactid_row = 0 where
    publisher_id = @publisher_id and
    publisher_db = @publisher_db
   if @@error <> 0
      return (1)

   /* Remove all the publisher database commands */
   delete from MSjob_commands where
      publisher_id = @publisher_id and
      publisher_db = @publisher_db
   if @@error <> 0
      return (1)

   /* Remove all the publisher database statuses */
   delete from MSsubscriber_status where
      publisher_id = @publisher_id and
      publisher_db = @publisher_db
   if @@error <> 0
      return (1)
GO

/* Set category bit to reflect MS objects */
UPDATE sysobjects SET category = category | 2 WHERE sysstat & 0xf = 4 AND
    name = 'sp_MSremove_published_jobs'
GO


/****************************************************************************/
PRINT ''
PRINT 'Creating Stored Procedure:  dbo.sp_MSupdate_job'
PRINT ''
/****************************************************************************/
GO

CREATE PROCEDURE sp_MSupdate_job
@publisher_id smallint,
@publisher_db varchar (30),
@job_id int = 0,
@type tinyint = 0,
@xactid_page int = 0,
@xactid_row smallint = 0,
@xactid_ts binary(8) = 0
AS

set nocount on
update MSjobs set type = @type, xactid_page = @xactid_page,
    xactid_row = @xactid_row, xactid_ts = @xactid_ts,
    entry_time = getdate()
    where
    publisher_id = @publisher_id and
    publisher_db = @publisher_db and
    job_id = @job_id
if @@error <> 0
   return(1)

GO

/* Set category bit to reflect MS objects */
UPDATE sysobjects SET category = category | 2 WHERE sysstat & 0xf = 4 AND
    name = 'sp_MSupdate_job'
go


/****************************************************************************/
print ''
print 'Create users'
print ''
/****************************************************************************/
GO
if not exists (select * from sysusers where name = 'repl_subscriber')
    exec sp_adduser repl_subscriber
GO
if not exists (select * from sysusers where name = 'probe')
    exec sp_adduser probe
GO

/****************************************************************************/
print ''
print 'Granting privileges on distribution stored procedures'
print ''
/****************************************************************************/
GO
grant execute on sp_MSadd_subscription to public
grant execute on sp_MSupdate_subscription to public
grant execute on sp_MSdrop_subscription to public
grant execute on sp_MSdistribution_counters to public
grant execute on sp_MShelp_subscriber_info to public
GO

EXEC sp_configure 'allow updates', 0
GO

reconfigure with override
GO
-- - ----