PC Plus SuperCD 4/SuperCD_4.iso/server/servpak1.65/instrepl.sql

Text File | 1996-07-29 | 241KB | 8,452 lines

/*
** instrepl.sql            1996/02/13 22:03
**
**
** Copyright Microsoft, Inc. 1994, 1995, 1996
** All Rights Reserved.
** Use, duplication, or disclosure by the United States Government
** is subject to restrictions as set forth in subdivision (c) (1) (ii)
** of the Rights in Technical Data and Computer Software clause
** at CFR 252.227-7013. Microsoft, Inc. One Microsoft Way, Redmond WA
** 98052.
*/

go
use master
go
dump tran master with no_log
go
set nocount on
go

exec sp_configure 'update',1
go
reconfigure with override
go

exec sp_MS_upd_sysobj_category 1 --Capture time for use below.

go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_addarticle')
    drop procedure sp_addarticle
go

if exists (select * from sysobjects
    where sysstat & 0xf = 4
            and name = 'sp_articlecolumn')
    drop procedure sp_articlecolumn
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_articlefilter')
    drop procedure sp_articlefilter
go

if exists (select * from sysobjects
    where sysstat & 0xf = 4
            and name = 'sp_articletextcol')
    drop procedure sp_articletextcol
go

if exists (select * from sysobjects
    where sysstat & 0xf = 4
            and name = 'sp_textcolstatus')
    drop procedure sp_textcolstatus
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_articleview')
    drop procedure sp_articleview
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_addpublication')
    drop procedure sp_addpublication
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_addpublisher')
    drop procedure sp_addpublisher
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_addsubscriber')
    drop procedure sp_addsubscriber
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_addsubscription')
    drop procedure sp_addsubscription
go


IF EXISTS (SELECT * FROM sysobjects
        WHERE sysstat & 0xf = 4
            AND name = 'sp_changearticle')
    DROP PROCEDURE sp_changearticle
go


IF EXISTS (SELECT * FROM sysobjects
        WHERE sysstat & 0xf = 4
            AND name = 'sp_changepublication')
    DROP PROCEDURE sp_changepublication
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_changesubscriber')
    drop procedure sp_changesubscriber
go

IF EXISTS (SELECT * FROM sysobjects
        WHERE sysstat & 0xf = 4
            AND name = 'sp_changesubscription')
    DROP PROCEDURE sp_changesubscription
go

IF EXISTS (SELECT * FROM sysobjects
        WHERE sysstat & 0xf = 4
            AND name = 'sp_create_distribution_tables')
    DROP PROCEDURE sp_create_distribution_tables
go

dump tran master with no_log
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_hcchangesubstatus1')
    drop procedure sp_hcchangesubstatus1
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_hcchangesubstatus2')
    drop procedure sp_hcchangesubstatus2
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_changesubstatus')
    drop procedure sp_changesubstatus
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_distcounters')
    drop procedure sp_distcounters
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_droparticle')
    drop procedure sp_droparticle
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_droppublication')
    drop procedure sp_droppublication
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_droppublisher')
    drop procedure sp_droppublisher
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_dropsubscriber')
    drop procedure sp_dropsubscriber
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_dropsubscription')
    drop procedure sp_dropsubscription
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_dsninfo')
    drop procedure sp_dsninfo
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_enumdsn')
    drop procedure sp_enumdsn
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_enumfullsubscribers')
    drop procedure sp_enumfullsubscribers
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_helparticle')
    drop procedure sp_helparticle
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_helparticlecolumns')
    drop procedure sp_helparticlecolumns
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_helpdistributor')
    drop procedure sp_helpdistributor
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_helppublication')
    drop procedure sp_helppublication
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_helppublicationsync')
    drop procedure sp_helppublicationsync
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_helpreplicationdb')
    drop procedure sp_helpreplicationdb
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_helpsubscriberinfo')
    drop procedure sp_helpsubscriberinfo
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_helpsubscription')
    drop procedure sp_helpsubscription
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_publishdb')
    drop procedure sp_publishdb
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_replica')
    drop procedure sp_replica
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_replsync')
    drop procedure sp_replsync
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_subscribe')
    drop procedure sp_subscribe
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_MSuninstall_publishing')
    drop procedure sp_MSuninstall_publishing
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_unsubscribe')
    drop procedure sp_unsubscribe
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'xp_dsninfo')
    exec sp_dropextendedproc 'xp_dsninfo'
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'xp_enumdsn')
    exec sp_dropextendedproc 'xp_enumdsn'
go

CREATE PROCEDURE sp_helpdistributor (
    @distributor varchar(30)  = '%' OUTPUT, /* The distribution server name */
    @distribdb   varchar(30)  = '%' OUTPUT, /* The distribution database script */
        @directory   varchar(255) = '%' OUTPUT, /* The working directory */
        @account     varchar(255) = '%' OUTPUT, /* The Windows NT user account */
    @local varchar(5) = NULL        /* Get local server values */
        ) AS

    /*
    ** Declarations.
    */
    DECLARE @loc_distributor varchar(30)
    DECLARE @loc_distribdb varchar(30)
    DECLARE @loc_directory varchar(255)
    DECLARE @loc_account varchar(255)
    DECLARE @proc varchar(255)

    SET NOCOUNT ON

    /*
    ** If @local flag, get current server's distribution values.
    */
    IF LOWER (@local) = 'local'
    SELECT @loc_distributor = @@SERVERNAME
    /*
    ** Get the distribution server
    */
    ELSE
        BEGIN
        SELECT @loc_distributor = srvname
          FROM master..sysservers
         WHERE srvstatus & 8 <> 0

         IF @@error <> 0
        BEGIN
            RAISERROR (14071, 16, -1)
            RETURN (1)
        END
    END

    /*
    ** If remote distribuiton, execute sp_helpdistributor on distribution
    ** server.
    */
    IF @loc_distributor <> @@SERVERNAME
       BEGIN
        SELECT @proc = RTRIM(@loc_distributor) + '.master..sp_helpdistributor '
            EXECUTE @proc
        @loc_distributor OUTPUT,
        @loc_distribdb OUTPUT,
        @loc_directory OUTPUT,
        @loc_account OUTPUT,
        @local = 'local'
        IF @@ERROR <> 0
           RETURN (1)

            GOTO DONE
       END

    /*
    ** Fetch the distribution database name.
    */
    IF (@distributor = '%' AND @distribdb = '%' AND @directory = '%'
    AND @account = '%') OR @distribdb IS NULL
       BEGIN
        SELECT @proc = 'master..xp_regread '
        EXECUTE @proc 'HKEY_LOCAL_MACHINE',
              'SOFTWARE\Microsoft\MSSQLServer\Replication',
              'DistributionDB',
            @param = @loc_distribdb OUTPUT

        IF @@ERROR <> 0 RETURN (1)
    END
    /*
    ** Fetch the distribution working directory.
    */
    IF (@distributor = '%' AND @distribdb = '%' AND @directory = '%'
    AND @account = '%') OR @directory IS NULL
       BEGIN
        SELECT @proc = 'master..xp_regread '
        EXECUTE @proc 'HKEY_LOCAL_MACHINE',
              'SOFTWARE\Microsoft\MSSQLServer\Replication',
              'WorkingDirectory',
            @param = @loc_directory OUTPUT

        IF @@ERROR <> 0 RETURN (1)
       END

    /*
    ** Fetch the distribution account name.
    */
    IF (@distributor = '%' AND @distribdb = '%' AND @directory = '%'
    AND @account = '%') OR @account IS NULL
       BEGIN
        SELECT @proc = 'master..xp_regread '
        EXECUTE @proc 'HKEY_LOCAL_MACHINE',
              'SYSTEM\CurrentControlSet\Services\SQLExecutive',
              'ObjectName',
            @param = @loc_account OUTPUT

        IF @@ERROR <> 0 RETURN (1)
       END

DONE:

    /*
    ** Return result set if no output parameters
    */

    IF @distributor = '%' AND @distribdb = '%' AND @directory = '%'
    AND @account = '%'

    SELECT 'distributor'           = @loc_distributor,
               'distribution database' = @loc_distribdb,
               'directory'             = @loc_directory,
               'account'               = @loc_account

    /*
    ** Return output parameters if requested.
    */

    IF @distributor IS NULL
        SELECT @distributor = @loc_distributor
    IF @distribdb IS NULL
        SELECT @distribdb = @loc_distribdb
    IF @directory IS NULL
        SELECT @directory = @loc_directory
    IF @account IS NULL
    SELECT @account = @loc_account

    RETURN (0)
go

/*
** Create replication stored procedures.
** Part 2:  create all other stored procedures.
*/

print ''
print 'Creating procedure sp_addpublication.'
go
CREATE PROCEDURE sp_addpublication (
    @publication varchar(30),                /* publication name */
    @taskid int,                   /* associated scheduler task */
    @restricted varchar (10) = 'false',    /* publication security */
    @sync_method varchar(13) = 'native',   /* (bcp) native, (bcp) character */
    @repl_freq varchar(10) = 'continuous', /* continuous, snapshot */
    @description varchar (255) = NULL,     /* publication description */
    @status varchar(8) = 'inactive'        /* publication status; 0=inactive, 1=active */
    ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    DECLARE @retcode    int         /* return code value for procedure execution */
    DECLARE @rid bit                /* value for restricted column */
    DECLARE @rfid tinyint           /* identifier for replication frequency */
    DECLARE @publish_bit smallint   /* publication bit (flag) in sysobjects */
    DECLARE @smid tinyint           /* identifier for sync method */
    DECLARE @statid tinyint         /* status id based on @status */
    DECLARE @distributor varchar(30)
    DECLARE @distproc varchar (255)


    SELECT @publish_bit = 32

    /*
    ** Security Check
    ** Only the System Administratr (SA) or the Database Owner (dbo) can
    ** publish a table.
    */

    IF suser_id() <> 1 AND user_id() <> 1
        BEGIN
            RAISERROR (15000, 14, -1)
            RETURN (1)
        END

    /*
    ** Check to see if the database has been activated for publication.
    */

    IF (SELECT category & 1
          FROM master..sysdatabases
         WHERE name = DB_NAME()) = 0

    BEGIN
            RAISERROR (14013, 16, -1)
        RETURN (1)
    END

    /*
    ** Parameter Check: @publication.
    ** The @publication name must conform to the rules for identifiers,
    ** and must not be the keyword 'all'.
    */

    IF @publication IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The publication')
            RETURN (1)
        END

    EXECUTE @retcode = sp_validname @publication

    IF @@ERROR <> 0 OR @retcode <> 0
    RETURN (1)

    IF LOWER (@publication) = 'all'
        BEGIN
            RAISERROR (14034, 16, -1)
            RETURN (1)
        END

    /*
    ** Get distribution server information for remote RPC
    ** task verification.
    */
    EXEC @retcode = sp_helpdistributor @distributor = @distributor OUTPUT
    IF @@error <> 0 OR @retcode <> 0
        BEGIN
        RAISERROR (14071, 16, -1)
            RETURN (1)
    END

    /*
    ** Parameter Check:  @taskid
    ** The @taskid must exists in the systasks table.  The @taskid
    ** must also be unique.
    */
    SELECT @distproc = RTRIM(@distributor) + '.msdb..sp_verifytaskid'
    EXECUTE @retcode = @distproc @taskid = @taskid, @subsystem = 'Sync'
    IF @@ERROR <> 0 or @retcode <> 0
        BEGIN
            RAISERROR (14002, 16, -1, @taskid)
            RETURN (1)
        END

    IF EXISTS (SELECT * FROM syspublications WHERE taskid = @taskid)
        BEGIN
            RAISERROR (14045, 16, -1)
            RETURN (1)
        END

    /*
    ** Parameter Check: @sync_method
    ** The synchronization method must be one of the following:
    **
    **      0  [bcp] native
    **      1  [bcp] character
    */

    SELECT @sync_method = LOWER(@sync_method)
    IF @sync_method IS NULL OR @sync_method NOT IN ('native', 'character', 'bcp native', 'bcp character')
        BEGIN
            RAISERROR (14014, 16, -1)
            RETURN (1)
        END

    IF @sync_method IN ('character', 'bcp character')
        SELECT @smid = 1
    ELSE
        SELECT @smid = 0

    /*
    ** Parameter Check: @repl_freq.
    ** Make sure that the replication frequency is one of the following:
    **
    **  id  frequency
    **  ==  ==========
    **   0  continuous
    **   1  snapshot
    */

    SELECT @repl_freq = LOWER(@repl_freq)
    IF @repl_freq IS NULL OR @repl_freq NOT IN ('continuous', 'snapshot')
        BEGIN
            RAISERROR (14015, 16, -1)
            RETURN (1)
        END

    IF @repl_freq = 'snapshot' SELECT @rfid = 1
    ELSE SELECT @rfid = 0

    /*
    **  Check if the publication already exists.
    */

    IF EXISTS (SELECT * FROM syspublications WHERE name = @publication)
        BEGIN
            RAISERROR (14016, 16, -1, @publication)
            RETURN (1)
        END

    /*
    ** Parameter Check:  @restricted.
    */

    IF (@restricted IS NULL) OR (LOWER(@restricted) NOT IN ('true', 'false'))
        BEGIN
            RAISERROR (14017, 16, -1)
            RETURN (1)
        END

    IF LOWER(@restricted) = 'true'
        SELECT @rid = 1
    ELSE
        SELECT @rid = 0

    /*
    ** Parameter Check:  @status.
    ** The @status value can be:
    **
    **      statid  status
    **      ======  ========
    **           0  inactive
    **           1  active
    */

    IF @status IS NULL OR LOWER(@status) NOT IN ('inactive', 'active')
        BEGIN
            RAISERROR (14012, 16, -1)
            RETURN (1)
        END

    IF LOWER(@status) = 'active' SELECT @statid = 1
    ELSE SELECT @statid = 0

    /*
    **  Add publication to syspublications.
    */

    INSERT syspublications(description, name, repl_freq,
                           restricted, status, sync_method, taskid)
    VALUES (@description, @publication, @rfid, @rid, @statid, @smid, @taskid)

    IF @@ERROR <> 0
        BEGIN
            RAISERROR (14018, 16, -1)
            RETURN (1)
        END
go

print ''
print 'Creating procedure sp_changepublication.'
go
CREATE PROCEDURE sp_changepublication (
    @publication varchar(30) = NULL,        /* Publication name */
    @property varchar(15) = NULL,           /* The property to change */
    @value varchar(255) = NULL              /* The new property value */
    ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    DECLARE @cmd varchar(255)
    DECLARE @pubid int
    DECLARE @replfreqid tinyint
    DECLARE @restrictedid bit
    DECLARE @retcode int
    DECLARE @statusid tinyint
    DECLARE @syncmethodid tinyint
    DECLARE @taskid int
    DECLARE @distributor varchar(30)
    DECLARE @distproc varchar (255)
    DECLARE @subscribed int


    select @subscribed = 1

    /*
    ** Security Check
    ** Only the System Administrator (SA) or the Database Owner (dbo) can
    ** perform this procedure.
    */

    IF suser_id() <> 1 AND user_id() <> 1
        BEGIN
            RAISERROR (15000, 14, -1)
            RETURN (1)
        END

    /*
    ** Check to see if the database has been activated for publication.
    */

    IF (SELECT category & 1
          FROM master..sysdatabases
         WHERE name = DB_NAME()) = 0

    BEGIN
            RAISERROR (14013, 16, -1)
        RETURN (1)
    END

    /*
    ** Parameter Check:  @property.
    ** If the @property parameter is NULL, print the options.
    */

    IF @property IS NULL
        BEGIN
            CREATE TABLE #tab1 (properties varchar(30))
            INSERT INTO #tab1 VALUES ('name')
            INSERT INTO #tab1 VALUES ('description')
            INSERT INTO #tab1 VALUES ('taskid')
            INSERT INTO #tab1 VALUES ('sync_method')
            INSERT INTO #tab1 VALUES ('status')
            INSERT INTO #tab1 VALUES ('repl_freq')
            INSERT INTO #tab1 VALUES ('restricted')
            PRINT ''
            SELECT * FROM #tab1
            RETURN (0)
        END

    /*
    ** Parameter Check:  @publication.
    ** Make sure that the publication exists.
    */

    IF @publication IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The publication')
            RETURN (1)
        END

    EXECUTE @retcode = sp_validname @publication

    IF @@ERROR <> 0 OR @retcode <> 0
    RETURN (1)

    SELECT @pubid = pubid FROM syspublications WHERE name = @publication

    IF @pubid IS NULL
        BEGIN
            RAISERROR (15001, 11, -1, @publication)
            RETURN (1)
        END
    ELSE

    /*
    ** Parameter Check:  @property.
    ** Check to make sure that @property is a valid property in
    ** syspublications.
    */

    IF LOWER(@property) NOT IN ('name', 'description', 'taskid', 'sync_method', 'status', 'repl_freq', 'restricted')
        BEGIN
            RAISERROR (14078, 16, -1)
            RETURN (1)
        END

    /*
    ** Change the property.
    */

    IF LOWER(@property) IN ('name', 'description')
        BEGIN

            IF LOWER(@property) = 'name'
                BEGIN

                    IF @value IS NULL
                        BEGIN
                            RAISERROR (14043, 16, -1, 'The publication')
                            RETURN (1)
                        END

                    EXECUTE @retcode = sp_validname @value

                    IF @@ERROR <> 0 OR @retcode <> 0
            RETURN (1)

                    IF EXISTS (SELECT * FROM syspublications WHERE name = @value)
                        BEGIN
                            RAISERROR (14016, 16, -1, @value)
                            RETURN (1)
                        END

                END

            SELECT @cmd = ''
            SELECT @cmd = @cmd + 'UPDATE syspublications '
            SELECT @cmd = @cmd + '   SET ' + @property + ' = ''' + @value + ''''
            SELECT @cmd = @cmd + ' WHERE pubid = ' + STR(@pubid)
            EXECUTE (@cmd)
            IF @@ERROR <> 0 RETURN (1)
        END

    IF LOWER(@property) = 'taskid'
       BEGIN
        SELECT @taskid = CONVERT(int, @value)
            /*
            ** Get distribution server information for remote RPC
            ** task verification.
            */
            EXEC @retcode = sp_helpdistributor @distributor = @distributor OUTPUT
            IF @@error <> 0 OR @retcode <> 0
               BEGIN
              RAISERROR (14071, 16, -1)
                  RETURN (1)
           END

        /*
        ** The @taskid must exists in the systasks table.  The @taskid
        ** must also be unique.
        */
        SELECT @distproc = RTRIM(@distributor) + '.msdb..sp_verifytaskid'
        EXECUTE @retcode = @distproc @taskid = @taskid,
           @subsystem = 'Sync'
            IF @@ERROR <> 0 or @retcode <> 0
        BEGIN
            RAISERROR (14002, 16, -1, @taskid)
            RETURN (1)
            END

        IF EXISTS (SELECT * FROM syspublications WHERE taskid = @taskid)
        BEGIN
            RAISERROR (14045, 16, -1)
            RETURN (1)
        END

        UPDATE syspublications SET taskid = @taskid
            WHERE pubid = @pubid

            IF @@ERROR <> 0 RETURN (1)
       END

    IF LOWER(@property) = 'sync_method'
        BEGIN

            /*
            ** Check for a valid synchronization method.
            */

            IF LOWER(@value) NOT IN ('native', 'character', 'bcp native', 'bcp character')
                BEGIN
                    RAISERROR (14026, 16, -1)
                    RETURN (1)
                END

            /*
            ** Determine the integer value for the sync_method.
            */

            IF LOWER(@value) IN ('native', 'bcp native')
                SELECT @syncmethodid = 0
            ELSE IF LOWER(@value) IN ('character', 'bcp character')
                SELECT @syncmethodid = 1

            /*
            ** Update the publication with the new synchronization method.
            */

            UPDATE syspublications
               SET sync_method = @syncmethodid
             WHERE pubid = @pubid

            IF @@ERROR <> 0 RETURN (1)

        END

    IF LOWER(@property) = 'status'
        BEGIN

            /*
            ** Check to make sure that we have a valid status.
            */

            IF LOWER(@value) NOT IN ('active', 'inactive')
                BEGIN
                    RAISERROR (14024, 16, -1)
                    RETURN (1)
                END

            /*
            ** Determine the integer value for the status.
            */

            IF LOWER(@value) = 'active'
                SELECT @statusid = 1
            ELSE
                SELECT @statusid = 0

            /*
            ** Update the publication with the new status.
            */

            UPDATE syspublications
               SET status = @statusid
             WHERE pubid = @pubid

            IF @@ERROR <> 0 RETURN (1)

        END

    IF LOWER(@property) = 'repl_freq'
        BEGIN

        /*
        ** Only unsubscribed publications may have this modified.
        */
        IF EXISTS (SELECT * FROM syssubscriptions
        WHERE status <> @subscribed
        AND artid IN (SELECT artid FROM sysarticles where pubid
           = @pubid))
        BEGIN
            RAISERROR (14033, 11, -1)
            RETURN (1)
        END

            /*
            ** Check for a valid replication frequency value.
            */

            IF LOWER(@value) NOT IN ('continuous', 'snapshot')
                BEGIN
                    RAISERROR (14015, 16, -1)
                    RETURN (1)
                END

            /*
            ** Determine the integer value for the replication frequency.
            */

            IF LOWER(@value) = 'continuous'
                SELECT @replfreqid = 0
            ELSE
                SELECT @replfreqid = 1

            /*
            ** Update the publication with the new replication frequency.
            */

            UPDATE syspublications
               SET repl_freq = @replfreqid
             WHERE pubid = @pubid

            IF @@ERROR <> 0 RETURN (1)

        END

    IF LOWER(@property) = 'restricted'
        BEGIN

            /*
            ** Check for a valid restricted value.
            */

            IF LOWER(@value) NOT IN ('true', 'false')
                BEGIN
                    RAISERROR (14017, 16, -1)
                    RETURN (1)
                END

            /*
            ** Determine the integer value for the restricted column.
            */

            IF LOWER(@value) = 'true'
                SELECT @restrictedid = 1
            ELSE
                SELECT @restrictedid = 0

            /*
            ** Update the publication with the new restriction value.
            */

            UPDATE syspublications
               SET restricted = @restrictedid
             WHERE pubid = @pubid

            IF @@ERROR <> 0 RETURN (1)

        END

    /*
    ** Return succeed.
    */

    RAISERROR (14077, 10, -1)
    RETURN (0)
GO

print ''
print 'Creating procedure sp_changesubscription.'
GO
CREATE PROCEDURE sp_changesubscription (
    @publication varchar(30) = NULL,        /* Publication name */
    @article varchar(30) = NULL,            /* Article name */
    @subscriber varchar(30),              /* Subscriber name */
    @property varchar(15) = NULL,           /* The property to change */
    @value varchar(255) = NULL              /* The new property value */
    ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    DECLARE @artid int
    DECLARE @inactive tinyint
    DECLARE @pubid int
    DECLARE @retcode int
    DECLARE @srvid int
    DECLARE @subscribed tinyint
    DECLARE @subscriber_bit smallint
    DECLARE @synctypeid int
    DECLARE @none tinyint
    DECLARE @automatic tinyint
    DECLARE @manual tinyint

    /*
    ** Initializations.
    */

    SELECT @inactive = 0        /* Const: subscription status 'inactive' */
    SELECT @subscribed = 1      /* Const: subscription status 'subscribed' */
    SELECT @subscriber_bit = 4  /* Const: subscription server status */
    SELECT @none = 2            /* Const: synchronization type 'none' */
    SELECT @automatic = 1       /* Const: synchronization type 'automatic' */
    SELECT @manual = 0          /* Const: synchronization type 'manual' */

    /*
    ** Security Check.
    */
    IF suser_id() <> 1 AND user_id() <> 1
        BEGIN
            RAISERROR (15000, 14, -1)
            RETURN (1)
        END

    /*
    ** Parameter Check:  @property.
    ** If the @property parameter is NULL, print the options.
    */

    IF @property IS NULL
        BEGIN
            CREATE TABLE #tab1 (properties varchar(30))
            INSERT INTO #tab1 VALUES ('sync_type')
            INSERT INTO #tab1 VALUES ('dest_db')
            SELECT * FROM #tab1
            RETURN (0)
        END

    /*
    ** Parameter Check:  @publication.
    ** Make sure that the publication exists.
    */

    IF @publication IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The publication')
            RETURN (1)
        END

    EXECUTE @retcode = sp_validname @publication

    IF @@ERROR <> 0 OR @retcode <> 0
    RETURN (1)

    SELECT @pubid = pubid FROM syspublications WHERE name = @publication

    IF @pubid IS NULL
        BEGIN
            RAISERROR (15001, 11, -1, @publication)
            RETURN (1)
        END
    ELSE

    /*
    ** Check to see that the article exists in sysarticles.
    ** Fetch the article identification number.
    */

    IF @article IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The article')
            RETURN (1)
        END

    EXECUTE @retcode = sp_validname @article

    IF @retcode <> 0
    RETURN (1)

    SELECT @artid = artid
      FROM sysarticles
     WHERE name = @article
       AND pubid = @pubid

    IF @artid IS NULL
        BEGIN
            RAISERROR (15001, 11, -1, @article)
            RETURN (1)
        END

    /*
    ** Parameter Check:  @subscriber.
    ** Check to make sure we have a valid subscriber.
    */

    IF @subscriber IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The subscriber')
            RETURN (1)
        END

    EXECUTE @retcode = sp_validname @subscriber

    IF @retcode <> 0
    RETURN (1)

    SELECT @srvid = srvid
      FROM master..sysservers
     WHERE srvname = @subscriber
       AND (srvstatus & @subscriber_bit) <> 0

    IF @srvid IS NULL
        BEGIN
            RAISERROR (14010, 16, -1)
           RETURN (1)
        END

    /*
    ** Check to see if you have a subscription on this publication/article.
    */

    IF NOT EXISTS (SELECT *
                     FROM syssubscriptions
                    WHERE artid = @artid
                      AND srvid = @srvid)
        BEGIN
            RAISERROR (14050, 11, -1)
            RETURN(1)
        END

    /*
    ** Parameter Check:  @property.
    ** Check to make sure that @property is a valid property in
    ** sysarticles.
    */

    IF LOWER(@property) NOT IN ('sync_type', 'dest_db')
        BEGIN
            RAISERROR (14051, 16, -1)
            RETURN (1)
        END

    /*
    ** Change the property.
    */

    IF LOWER(@property) = 'sync_type'
        BEGIN

            /*
            ** Check to make sure that we have a valid sync_type.
            */

            IF LOWER(@value) NOT IN ('manual', 'automatic', 'none')
                BEGIN
                    RAISERROR (14052, 16, -1)
                    RETURN (1)
                END

            /*
            ** Determine the integer value for the sync_type.
            */

        IF LOWER(@value) = 'automatic'
        SELECT @synctypeid = @automatic
        ELSE IF LOWER(@value) = 'manual'
        SELECT @synctypeid = @manual
        ELSE
        SELECT @synctypeid = @none

            /*
            ** Update the subscription with the new sync_type.
            */

            UPDATE syssubscriptions
               SET sync_type = @synctypeid
             WHERE artid = @artid
               AND srvid = @srvid

            IF @@ERROR <> 0
                BEGIN
                    RAISERROR (14053, 16, -1)
                    RETURN (1)
                END

        END

    IF LOWER(@property) = 'dest_db'
        BEGIN

            /*
            ** Check to make sure that we have a valid dest_db.
            */

            EXECUTE @retcode = sp_validname @value

            IF @@ERROR <> 0 OR @retcode <> 0
        RETURN (1)

            /*
            ** Update the subscription with the new destination database.
            */

            IF EXISTS (SELECT *
                         FROM syssubscriptions
                        WHERE artid = @artid
                          AND srvid = @srvid
                          AND status = @inactive)

                BEGIN

                    UPDATE syssubscriptions
                       SET dest_db = @value
                     WHERE artid = @artid
                       AND srvid = @srvid

                    IF @@ERROR <> 0
                        BEGIN
                            RAISERROR (14053, 16, -1)
                            RETURN (1)
                        END
                END

            ELSE
                BEGIN
                    RAISERROR (14007, 16, -1)
                    RETURN (1)
                END
        END

    /*
    ** Return succeed.
    */

    RAISERROR (14054, 10, -1)
    RETURN (0)
go

print ''
print 'Creating procedure sp_helparticle.'
go

CREATE PROCEDURE sp_helparticle (
        @publication varchar(30),         /* The publication name */
        @article varchar(30) = '%',       /* The article name */
    @returnfilter bit = 1             /* Return filter flag */
        ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    DECLARE @pubid int
    DECLARE @retcode int
    DECLARE @subscriber_bit smallint

    /*
    ** Initializations.
    */

    SELECT @subscriber_bit = 4

    IF @publication IS NOT NULL
        SELECT @pubid = pubid FROM syspublications WHERE name = @publication

    /*
    ** Create a temporary table to hold all information.
    */

    CREATE TABLE #tab1 (
        artid           int,
        columns         varbinary(32),
        creation_script varchar(127) NULL,
        del_cmd         varchar(255) NULL,
        description     varchar(255) NULL,
        dest_table      varchar(30)  NULL,
        old_filter      int          NULL,
        ins_cmd         varchar(255) NULL,
        name            varchar(30),
        objid           int,
        pubid           int,
        status          tinyint,
        sync_objid      int,
        type            tinyint,
        upd_cmd         varchar(255) NULL,
        source_table    varchar(61)  NULL,      /* converted from objid */
        filter          varchar(61)  NULL,      /* converted from old_filter */
        sync_object     varchar(61)  NULL,      /* converted from sync_objid */
        vpartition      bit          NOT NULL,   /* computed */
    pre_creation_cmd tinyint,
    filter_clause   text         NULL
    )

    CREATE UNIQUE INDEX idx1 ON #tab1 (name, pubid)

    /*
    ** Parameter Check:  @publication.
    ** Check to make sure that there are some articles
    ** to display.
    */

    IF @publication IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The publication')
            RETURN (1)
        END

    EXECUTE @retcode = sp_validname @publication

    IF @retcode <> 0
    RETURN (1)

    IF NOT EXISTS (SELECT * FROM syspublications WHERE name = @publication)
        BEGIN
            RAISERROR (15001, 11, -1, @publication)
            RETURN (1)
        END

    /*
    ** Parameter Check:  @article.
    ** Check to make sure that the article exists, that it conforms
    ** to the rules for identifiers, and that it isn't NULL.
    */

    IF @article IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The article')
            RETURN (1)
        END

    IF @article <> '%'
        BEGIN

            EXECUTE @retcode = sp_validname @article

            IF @retcode <> 0
        RETURN (1)

            IF NOT EXISTS (SELECT *
                             FROM sysarticles
                            WHERE name = @article
                              AND pubid IN (SELECT pubid
                                              FROM syspublications
                                             WHERE name = @publication))
                BEGIN
                    RAISERROR (15001, 11, -1, @article)
                    RETURN (1)
                END

        END

    /*
    ** If local user show all articles.
    */
    IF @@REMSERVER IS NULL
        BEGIN
        IF @returnfilter = 1
        BEGIN
            INSERT INTO #tab1 (artid, columns, creation_script, del_cmd,
                               description, dest_table, old_filter,
                               ins_cmd, name, objid, pubid, status,
                               sync_objid, type, upd_cmd, source_table,
                               filter, vpartition, pre_creation_cmd,
                   filter_clause)
             (SELECT artid, columns, creation_script, del_cmd, a.description,
                     dest_table, filter, ins_cmd, a.name, objid, a.pubid,
                     a.status, sync_objid, type, upd_cmd, NULL, NULL, 0,
             a.pre_creation_cmd, a.filter_clause
                FROM sysarticles a, syspublications b
               WHERE a.name LIKE @article
                 AND a.pubid = b.pubid
                 AND b.name = @publication)
        END
        ELSE
        BEGIN
            INSERT INTO #tab1 (artid, columns, creation_script, del_cmd,
                               description, dest_table, old_filter,
                               ins_cmd, name, objid, pubid, status,
                               sync_objid, type, upd_cmd, source_table,
                               filter, vpartition, pre_creation_cmd,
                   filter_clause)
             (SELECT artid, columns, creation_script, del_cmd, a.description,
                     dest_table, filter, ins_cmd, a.name, objid, a.pubid,
                     a.status, sync_objid, type, upd_cmd, NULL, NULL, 0,
             a.pre_creation_cmd, NULL
                FROM sysarticles a, syspublications b
               WHERE a.name LIKE @article
                 AND a.pubid = b.pubid
                 AND b.name = @publication)
        END
        END

    ELSE
        BEGIN

            /*
            ** Check if remote server is defined as a subscription server.
            */

            IF NOT EXISTS (SELECT *
                             FROM sysservers
                            WHERE srvname = @@REMSERVER
                              AND (srvstatus & @subscriber_bit) <> 0)
                BEGIN
                    RAISERROR (14010, 16, -1)
                    RETURN (1)
                END

            /*
            ** If the publication is public, we can display it.
            */

            IF NOT EXISTS (SELECT *
                             FROM syspublications
                            WHERE name = @publication
                              AND restricted = 0)

                /*
                ** The publication wasn't public, so let's check to see
                ** if there's a restricted publication on which I have
                ** permission.
                */

                IF NOT EXISTS (SELECT *
                                 FROM syssubscriptions a,
                                      sysarticles b,
                                      syspublications c
                                WHERE c.name = @publication
                                  AND c.restricted = 1
                                  AND c.pubid = b.pubid
                                  AND b.name LIKE @article
                                  AND b.artid = a.artid)

                    BEGIN
                        RAISERROR (14011, 16, -1)
                        RETURN (1)
                    END

            /*
            ** Fetch the information into the temporary table.  First, put
            ** in the information about public publications if this publi-
            ** cation is public.  Next, if the publication is restricted,
            ** put in only those articles on which you have been granted
            ** access (sp_addsubscription).
            */

        IF @returnfilter = 1
        BEGIN
            INSERT INTO #tab1 (artid, columns, creation_script, del_cmd,
                               description, dest_table, old_filter,
                   ins_cmd, name, objid, pubid, status,
                               sync_objid, type, upd_cmd, source_table,
                               filter, vpartition, pre_creation_cmd,
                   filter_clause)
             (SELECT a.artid, columns, creation_script, del_cmd, a.description,
                     dest_table, filter, ins_cmd, a.name, objid, a.pubid,
                     a.status, sync_objid, type, upd_cmd, NULL, NULL, 0,
             a.pre_creation_cmd, a.filter_clause
                FROM sysarticles a,
                     syspublications b
               WHERE a.name LIKE @article
                 AND a.pubid = b.pubid
                 AND b.name = @publication
                 AND b.restricted = 0)

            INSERT INTO #tab1 (artid, columns, creation_script, del_cmd,
                               description, dest_table, old_filter,
                               ins_cmd, name, objid, pubid, status,
                               sync_objid, type, upd_cmd, source_table,
                               filter, vpartition, pre_creation_cmd,
                   filter_clause)
             (SELECT a.artid, columns, creation_script, del_cmd, a.description,
                     dest_table, filter, ins_cmd, a.name, objid, a.pubid,
                     a.status, sync_objid, type, upd_cmd, NULL, NULL, 0,
             a.pre_creation_cmd, a.filter_clause
                FROM sysarticles a,
                     syspublications b,
                     syssubscriptions c,
                     master..sysservers d
               WHERE a.name LIKE @article
                 AND a.pubid = b.pubid
                 AND b.name = @publication
                 AND b.restricted = 1
                 AND a.artid = c.artid
                 AND c.srvid = d.srvid
                 AND d.srvname = @@REMSERVER)
        END
        ELSE
               BEGIN
            INSERT INTO #tab1 (artid, columns, creation_script, del_cmd,
                               description, dest_table, old_filter,
                   ins_cmd, name, objid, pubid, status,
                               sync_objid, type, upd_cmd, source_table,
                               filter, vpartition, pre_creation_cmd,
                   filter_clause)
             (SELECT a.artid, columns, creation_script, del_cmd, a.description,
                     dest_table, filter, ins_cmd, a.name, objid, a.pubid,
                     a.status, sync_objid, type, upd_cmd, NULL, NULL, 0,
             a.pre_creation_cmd, NULL
                FROM sysarticles a,
                     syspublications b
               WHERE a.name LIKE @article
                 AND a.pubid = b.pubid
                 AND b.name = @publication
                 AND b.restricted = 0)

            INSERT INTO #tab1 (artid, columns, creation_script, del_cmd,
                               description, dest_table, old_filter,
                               ins_cmd, name, objid, pubid, status,
                               sync_objid, type, upd_cmd, source_table,
                               filter, vpartition, pre_creation_cmd,
                   filter_clause)
             (SELECT a.artid, columns, creation_script, del_cmd, a.description,
                     dest_table, filter, ins_cmd, a.name, objid, a.pubid,
                     a.status, sync_objid, type, upd_cmd, NULL, NULL, 0,
             a.pre_creation_cmd, NULL
                FROM sysarticles a,
                     syspublications b,
                     syssubscriptions c,
                     master..sysservers d
               WHERE a.name LIKE @article
                 AND a.pubid = b.pubid
                 AND b.name = @publication
                 AND b.restricted = 1
                 AND a.artid = c.artid
                 AND c.srvid = d.srvid
                 AND d.srvname = @@REMSERVER)
        END
        END

    UPDATE #tab1
       SET source_table = u.name + '.' + o.name
      FROM #tab1, sysobjects o, sysusers u
     WHERE o.id = #tab1.objid
       AND o.uid = u.uid

    UPDATE #tab1
       SET sync_object = sysusers.name + '.' + sysobjects.name
      FROM sysobjects, sysusers
     WHERE sysobjects.id = sync_objid
       AND sysobjects.uid = sysusers.uid

    UPDATE #tab1 SET filter = (SELECT sysusers.name + '.' + sysobjects.name
                                 FROM sysobjects, sysusers
                                WHERE sysobjects.id = #tab1.old_filter
                                  AND sysobjects.uid = sysusers.uid)
      FROM #tab1

    EXECUTE ('DECLARE hC SCROLL CURSOR FOR SELECT name, pubid FROM #tab1')
    OPEN hC
    FETCH hC INTO @article, @pubid
    WHILE (@@fetch_status <> -1)
        BEGIN
            IF EXISTS (SELECT *
                         FROM sysarticles a, syscolumns b
                        WHERE (CONVERT(bit, SUBSTRING(a.columns, CONVERT(tinyint, 32 - FLOOR((colid-1)/8)), 1) & POWER(2, ((colid-1)%8))) = 0
                           OR CONVERT(bit, SUBSTRING(a.columns, CONVERT(tinyint, 32 - FLOOR((colid-1)/8)), 1) & POWER(2, ((colid-1)%8))) IS NULL)
                          AND a.objid = b.id
                          AND a.name = @article
                          AND a.pubid = @pubid)

                UPDATE #tab1
                   SET vpartition = 1
                 WHERE name = @article
                   AND pubid = @pubid

            FETCH hC INTO @article, @pubid
        END
    CLOSE hC
    DEALLOCATE hC

    IF @returnfilter = 1
        SELECT 'article id'                = artid,
           'article name'              = name,
           'base table'                = source_table,
           'destination table'         = dest_table,
           'synchronization object'    = sync_object,
           'type'                      = type,
           'status'                    = status,
           'filter'                    = filter,
           'description'               = description,
           'insert_command'            = ins_cmd,
           'update_command'            = upd_cmd,
           'delete_command'            = del_cmd,
           'creation script path'      = creation_script,
           'vertical partition'        = vpartition,
           'pre_creation_cmd'       = pre_creation_cmd,
           'filter_clause'           = filter_clause
          FROM #tab1
         ORDER BY 2
    ELSE
        SELECT 'article id'                = artid,
           'article name'              = name,
           'base table'                = source_table,
           'destination table'         = dest_table,
           'synchronization object'    = sync_object,
           'type'                      = type,
           'status'                    = status,
           'filter'                    = filter,
           'description'               = description,
           'insert_command'            = ins_cmd,
           'update_command'            = upd_cmd,
           'delete_command'            = del_cmd,
           'creation script path'      = creation_script,
           'vertical partition'        = vpartition,
           'pre_creation_cmd'       = pre_creation_cmd
          FROM #tab1
         ORDER BY 2
    RETURN (0)
go

dump tran master with no_log
go

print ''
print 'Creating procedure sp_articlecolumn.'
go
CREATE PROCEDURE sp_articlecolumn (
        @publication varchar(30),           /* The publication name */
        @article varchar(30),               /* The article name */
        @column varchar(30) = NULL,         /* The column name */
        @operation varchar(4) = 'add'      /* Add or delete a column */
        ) AS

    /*
    ** Declarations.
    */

    DECLARE @bit tinyint                /* Bit offset */
    DECLARE @byte tinyint               /* Byte offset */
    DECLARE @cnt tinyint, @idx tinyint  /* Loop counter, index */
    DECLARE @columns binary(32)         /* Temporary storage for the converted column */
    DECLARE @mask smallint              /* Bit mask to set the bit on */
    DECLARE @newbyte binary(1)          /* New byte to replace old byte with */
    DECLARE @oldbyte binary(1)          /* Temporary storage for original byte */
    DECLARE @pubid int                  /* Publication identification number */
    DECLARE @retcode int                /* Return code for stored procedures */
    DECLARE @zero binary(32)            /* Constant:  0 */
    DECLARE @artid int
    DECLARE @inactive tinyint
    DECLARE @objid int            /* Article base table id */

    select @inactive = 0

    /*
    ** Security Check
    ** Only the System Administratr (SA) or the Database Owner (dbo) can
    ** perform this procedure.
    */

    IF suser_id() <> 1 AND user_id() <> 1
        BEGIN
            RAISERROR (15000, 14, -1)
            RETURN (1)
        END

    /*
    ** Check to see if the database has been activated for publication.
    */

    IF (SELECT category & 1
          FROM master..sysdatabases
         WHERE name = DB_NAME()) = 0

    BEGIN
            RAISERROR (14013, 16, -1)
        RETURN (1)
    END

    /*
    ** Parameter Check:  @publication.
    ** Make sure that the publication exists and that it conforms to the
    ** rules for identifiers.
    */

    IF @publication IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The publication')
            RETURN (1)
        END

    EXECUTE @retcode = sp_validname @publication

    IF @retcode <> 0
            RETURN (1)

    SELECT @pubid = pubid FROM syspublications WHERE name = @publication

    IF @pubid IS NULL
        BEGIN
            RAISERROR (15001, 11, -1, @publication)
            RETURN (1)
        END
    ELSE

    /*
    ** Parameter Check:  @article.
    ** Check to make sure that the article exists in the publication.
    */

    IF @article IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The article')
            RETURN (1)
        END

    EXECUTE @retcode = sp_validname @article

    IF @@ERROR <> 0 OR @retcode <> 0
    RETURN (1)


    /*
    ** Make sure the article exists.
    */
    SELECT @artid = artid FROM sysarticles
       WHERE pubid = @pubid AND name = @article
    IF @artid IS NULL
        BEGIN
            RAISERROR (15001, 11, -1, @article)
            RETURN (1)
        END

    /*
    ** Only unsubscribed articles may be modified.
    */
    IF EXISTS (SELECT * FROM syssubscriptions WHERE artid = @artid
       AND status <> @inactive)
        BEGIN
            RAISERROR (14092, 11, -1)
            RETURN (1)
        END

    /*
    ** Parameter Check:  @column.
    ** Check to make sure that the column exists and conforms to the rules
    ** for identifiers.
    */

    IF @column IS NOT NULL
        BEGIN
            EXECUTE @retcode = sp_validname @column
            IF @@ERROR <> 0 OR @retcode <> 0
        RETURN (1)
        END

    /*
    ** Parameter Check:  @operation.
    ** The operation can be either 'add' or 'drop'.
    */

    IF LOWER(@operation) NOT IN ('add', 'drop')
        BEGIN
            RAISERROR (14019, 16, -1)
            RETURN (1)
        END

    BEGIN TRANSACTION articlecolumn

    /*
    ** Make sure that the columns column is not NULL.
    */

    SELECT @zero = 0x00

    SELECT @columns = columns
      FROM sysarticles
     WHERE artid = @artid

    IF @columns IS NULL
        UPDATE sysarticles
           SET columns = @zero
         WHERE artid = @artid

    SELECT @objid = (SELECT objid FROM sysarticles WHERE artid = @artid)

    /*
    ** If no columns are specified, or if NULL is specified, set all
    ** the bits in the 'columns' column so all columns will be included.
    */

    IF @column IS NULL

        BEGIN

            /*
            ** Fetch the number of columns affected.
            */

            SELECT @cnt = COUNT(*), @idx = 1
              FROM syscolumns
             WHERE id = @objid

            SELECT @columns = @zero

            WHILE @idx <= @cnt
                BEGIN

                    SELECT @byte = CONVERT(tinyint, 32 - FLOOR((@idx-1)/8))
                    SELECT @bit = (@idx-1) % 8

                    IF LOWER(@operation) = 'add'
                        SELECT @mask = POWER(2, @bit)
                    ELSE
                        SELECT @mask = ~POWER(2, @bit)

                    SELECT @oldbyte = SUBSTRING(@columns, @byte, 1)
                    IF @oldbyte IS NULL SELECT @oldbyte = 0x00

                    IF LOWER(@operation) = 'add'
                        SELECT @newbyte = CONVERT(binary(1), ASCII(@oldbyte) | @mask)
                    ELSE
                        SELECT @newbyte = CONVERT(binary(1), ASCII(@oldbyte) & @mask)

                    SELECT @columns = CONVERT(binary(32), STUFF(@columns, @byte, 1, @newbyte))
                    SELECT @idx = @idx + 1


                END

            IF LOWER(@operation) = 'drop'
           BEGIN
               /* Update Text\Image column status as not published */
              EXECUTE @retcode = sp_articletextcol @artid, NULL,
                 'publish', @operation
              IF (@@error <> 0 OR @retcode <> 0)
              BEGIN
                  ROLLBACK TRANSACTION articlecolumn
                  RAISERROR (14020, 16, -1)
                  RETURN (1)
              END
           END

            UPDATE sysarticles
               SET columns = @columns
             WHERE name = @article
               AND pubid = @pubid

            IF LOWER(@operation) = 'add'
           BEGIN
               /* Update Text\Image column status as published */
              EXECUTE @retcode = sp_articletextcol @artid, NULL,
                 'publish', @operation
              IF (@@error <> 0 OR @retcode <> 0)
              BEGIN
                  ROLLBACK TRANSACTION articlecolumn
                  RAISERROR (14020, 16, -1)
                  RETURN (1)
              END
           END
        END

    ELSE

        BEGIN
                IF EXISTS (SELECT *
                             FROM sysarticles
                            WHERE name = @article
                              AND pubid = @pubid
                              AND columns IS NULL)

                        UPDATE sysarticles
                          SET columns = @zero
                        WHERE name = @article
                          AND pubid = @pubid

                DECLARE @columnid tinyint   /* Columnid-1 = bit to set */

                /*
                ** Get the column id for this column.  We'll use the column id
                ** to determine the bit in the 'columns' column.  The bit we want
                ** is equal to the columnid - 1.
                */

                SELECT @columnid = colid
                  FROM syscolumns
                 WHERE id = @objid AND name = @column

                IF ((@@error <> 0) OR (@columnid IS NULL))
                    BEGIN
                        ROLLBACK TRANSACTION articlecolumn
                        RAISERROR (14020, 16, -1)
                        RETURN (1)
                    END

                /*
                ** Obtain the byte offset and the bit offset, then set the
                ** mask column for the bit we want to turn on.
                */

                SELECT @byte = CONVERT(tinyint, 32 - FLOOR((@columnid-1)/8.0))
                SELECT @bit = (@columnid-1) % 8

                IF LOWER(@operation) = 'add'
                    SELECT @mask = POWER(2, @bit)
                ELSE
                    SELECT @mask = ~POWER(2, @bit)

                /*
                ** Save the columns column in a temporary local variable so we
                ** can twiddle the bit and then put it back into the table.
                */

                SELECT @columns = columns
                  FROM sysarticles
                 WHERE name = @article
                   AND pubid = @pubid

                /*
                ** Fish out the byte we're interested in and save it in a
                ** a temporary local variable.  If it's NULL, just set it
                ** to 0.  Then apply the bitwise operator OR to twiddle the
                ** bit in the old byte and save it in another temporary
                ** local variable @newbyte.
                */

                SELECT @oldbyte = SUBSTRING(@columns, @byte, 1)
                IF @oldbyte IS NULL SELECT @oldbyte = 0x00

                IF LOWER(@operation) = 'add'
                    SELECT @newbyte = CONVERT(binary(1), ASCII(@oldbyte) | @mask)
                ELSE
                    SELECT @newbyte = CONVERT(binary(1), ASCII(@oldbyte) & @mask)

                /*
                ** Stuff the new byte into the varbinary column to replace the
                ** old byte.
                */

                SELECT @columns = CONVERT(varbinary(32), STUFF(@columns+@zero, @byte, 1, @newbyte))

                IF LOWER(@operation) = 'drop'
               BEGIN
                   /* Update Text\Image column status as not published */
                  EXECUTE @retcode = sp_articletextcol @artid, @columnid,
                     'publish', @operation
                  IF (@@error <> 0 OR @retcode <> 0)
                  BEGIN
                     ROLLBACK TRANSACTION articlecolumn
                     RAISERROR (14021, 16, -1)
                     RETURN (1)
                  END
               END

                /*
                ** Update the sysarticles table.  Set the bit to 1 for the
                ** selected column.
                */

                UPDATE sysarticles
                   SET columns = @columns
                 WHERE name = @article
                   AND pubid = @pubid

                IF @@error <> 0
                    BEGIN
                        ROLLBACK TRANSACTION articlecolumn
                        RAISERROR (14021, 16, -1)
                        RETURN (1)
                    END

                IF LOWER(@operation) = 'add'
               BEGIN
                   /* Update Text\Image column status as not published */
                  EXECUTE @retcode = sp_articletextcol @artid, @columnid,
                     'publish', @operation
                  IF (@@error <> 0 OR @retcode <> 0)
                  BEGIN
                     ROLLBACK TRANSACTION articlecolumn
                     RAISERROR (14021, 16, -1)
                     RETURN (1)
                  END
               END
        END
    /*
    ** Force the article cache to be refreshed with the new definition.
    */
    EXECUTE sp_replflush

    COMMIT TRANSACTION articlecolumn
go

print ''
print 'Creating procedure sp_helparticlecolumns.'
go
CREATE PROCEDURE sp_helparticlecolumns (
    @publication varchar(30),            /* The publication name */
    @article    varchar(30)              /* The article name */
    ) AS

    /*
    ** Declarations.
    */

    DECLARE @columns binary(32)
    DECLARE @pubid int
    DECLARE @retcode int

    /*
    ** Parameter Check: @article.
    ** The @article name must conform to the rules for identifiers.
    */

    IF @article IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The article')
            RETURN (1)
        END

    EXECUTE @retcode = sp_validname @article

    IF @retcode <> 0
    RETURN (1)

    /*
    ** Parameter Check: @publication.
    ** The @publication name must conform to the rules for identifiers.
    */

    IF @publication IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The publication')
            RETURN (1)
        END

    EXECUTE @retcode = sp_validname @publication

    IF @retcode <> 0
    RETURN (1)

    /*
    ** Get the pubid.
    */

    SELECT @pubid = pubid FROM syspublications WHERE name = @publication

    IF @pubid IS NULL
        BEGIN
            RAISERROR (14027, 11, -1, 'The publication')
            RETURN (1)
        END

    /*
    ** Parameter Check:  @article, @publication.
    ** Check to make sure that the article exists in this publication.
    */

    IF NOT EXISTS (SELECT *
                     FROM sysarticles
                    WHERE pubid = @pubid
                      AND name = @article)
        BEGIN
            RAISERROR (15001, 11, -1, 'The article')
            RETURN (1)
        END

    IF @@REMSERVER IS NOT NULL

        /*
        ** Is the publication/article restricted?
        */

        IF EXISTS (SELECT *
                     FROM syspublications
                    WHERE pubid = @pubid
                      AND restricted = 1)

            /*
            ** We have a restricted publication.  Does the subscriber
            ** have access to it?
            */

            IF NOT EXISTS (SELECT *
                             FROM sysarticles a,
                                  syssubscriptions b,
                                  master..sysservers c
                            WHERE c.srvname = @@REMSERVER
                              AND c.srvid = b.srvid
                              AND b.artid = a.artid
                              AND a.name = @article
                              AND a.pubid = @pubid)
                BEGIN
                    RAISERROR (14011, 16, -1)
                    RETURN (1)
                END

    SELECT @columns = columns
      FROM sysarticles
     WHERE name = @article
       AND pubid = @pubid

    SELECT 'column id' = colid,
           'column'    = name,
       'published' = CONVERT(bit, SUBSTRING(@columns, CONVERT(tinyint, 32 - FLOOR((colid-1)/8)), 1) & POWER(2, ((colid-1)%8)))
      FROM syscolumns
     WHERE id = (SELECT objid
                   FROM sysarticles
                  WHERE name = @article
                    AND pubid = @pubid)
go

print ''
print 'Creating procedure sp_helppublication.'
go

CREATE PROCEDURE sp_helppublication (
        @publication varchar(30) = '%'     /* The publication name */
        ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    DECLARE @retcode int
    DECLARE @subscriber_bit smallint

    /*
    ** Initializations.
    */

    SELECT @subscriber_bit = 4

    /*
    ** If local user show all publications.
    */
    IF @@REMSERVER IS NULL
        BEGIN

            /*
            ** Parameter Check:  @publication.
            ** Check to make sure that there are some publications
            ** to display.
            */

            IF @publication IS NULL
                BEGIN
                    RAISERROR (14043, 16, -1, 'The publication')
                    RETURN (1)
                END

            IF @publication <> '%'
                BEGIN

                    EXECUTE @retcode = sp_validname @publication

                    IF @retcode <> 0
            RETURN (1)
                END

            IF @publication <> '%' AND NOT EXISTS (SELECT *
                                                     FROM syspublications
                                                    WHERE name = @publication)
                BEGIN
                    RAISERROR (15001, 11, -1, @publication)
                RETURN (1)
            END

            SELECT 'pubid'                  = pubid,
                   'name'                   = name,
                   'restricted'             = restricted,
                   'status'                 = status,
                   'task'                   = taskid,
                   'replication frequency'  = repl_freq,
                   'synchronization method' = sync_method,
                   'description'            = description
              FROM syspublications
             WHERE name LIKE @publication
             ORDER BY name

            IF @@ERROR <> 0 RETURN (1)

        END

    ELSE
        BEGIN

            /*
            ** Check if remote server is defined as a subscription server.
            */

            IF NOT EXISTS (SELECT *
                             FROM sysservers
                            WHERE srvname = @@REMSERVER
                              AND (srvstatus & @subscriber_bit) <> 0)
                BEGIN
                    RAISERROR (14010, 16, -1)
                    RETURN (1)
                END

            /*
            ** Parameter Check:  @publication.
            ** Check to make sure that there are some publications
            ** to display.
            */

            IF @publication IS NULL
                BEGIN
                    RAISERROR (14043, 16, -1, 'The publication')
                    RETURN (1)
                END

            IF @publication <> '%'
                BEGIN

                    EXECUTE @retcode = sp_validname @publication

                    IF @retcode <> 0
            RETURN (1)
                END

            IF @publication <> '%'
                AND NOT EXISTS (SELECT *
                                  FROM syspublications
                             WHERE restricted = 0
                                   AND name = @publication)
                AND NOT EXISTS (SELECT *
                              FROM syspublications a,
                                       syssubscriptions b,
                                       sysarticles c,
                                       master..sysservers d
                         WHERE a.restricted = 1
                                   AND a.name = @publication
                               AND a.pubid = c.pubid
                                   AND c.artid = b.artid
                           AND b.srvid = d.srvid
                           AND d.srvname =  @@REMSERVER)
                BEGIN
                    RAISERROR (14011, 16, -1)
                    RETURN (1)
                END

            /*
            ** Display all public publications and all restricted publications
            ** allowed for the server.
            */

            SELECT 'pubid'                  = pubid,
                   'name'                   = name,
                   'restricted'             = restricted,
                   'status'                 = status,
                   'task'                   = taskid,
                   'replication frequency'  = repl_freq,
                   'synchronization method' = sync_method,
                   'description'            = description
              FROM syspublications
             WHERE restricted = 0
               AND name LIKE @publication
             UNION
            SELECT a.pubid, a.name, a.restricted, a.status, a.taskid, a.repl_freq, a.sync_method, a.description
              FROM syspublications a,
                   syssubscriptions b,
                   sysarticles c,
                   master..sysservers d
             WHERE a.restricted = 1
               AND a.name LIKE @publication
               AND a.pubid = c.pubid
               AND c.artid = b.artid
               AND b.srvid = d.srvid
               AND d.srvname =  @@REMSERVER
             ORDER BY name

            IF @@ERROR <> 0 RETURN (1)
        END

    RETURN (0)
go

print ''
print 'Creating procedure sp_helppublicationsync.'
go

CREATE PROCEDURE sp_helppublicationsync (
        @publication varchar(30)     /* The publication name */
        ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    DECLARE @retcode int
    DECLARE @subscriber_bit smallint
    DECLARE @taskid int
    DECLARE @distributor varchar(30)
    DECLARE @distproc varchar (255)

    /*
    ** Initializations.
    */

    SELECT @subscriber_bit = 4

    /*
    ** If local user show all publications.
    */
    IF @@REMSERVER IS NULL
        BEGIN

            /*
            ** Parameter Check:  @publication.
            ** Check to make sure that there are some publications
            ** to display.
            */

            IF @publication IS NULL
                BEGIN
                    RAISERROR (14043, 16, -1, 'The publication')
                    RETURN (1)
                END

            IF @publication = '%'
                BEGIN
                    RAISERROR (14003, 16, -1, 'The publication')
                    RETURN (1)
                END

            EXECUTE @retcode = sp_validname @publication
            IF @@ERROR <> 0 OR @retcode <> 0 RETURN (1)

            IF NOT EXISTS (SELECT * FROM syspublications
               WHERE name = @publication)
               BEGIN
                    RAISERROR (15001, 11, -1, @publication)
                RETURN (1)
           END

        END

    ELSE
        BEGIN

            /*
            ** Check if remote server is defined as a subscription server.
            */

            IF NOT EXISTS (SELECT *
                             FROM sysservers
                            WHERE srvname = @@REMSERVER
                              AND (srvstatus & @subscriber_bit) <> 0)
                BEGIN
                    RAISERROR (14010, 16, -1)
                    RETURN (1)
                END

            /*
            ** Parameter Check:  @publication.
            ** Check to make sure that there are some publications
            ** to display.
            */

            IF @publication IS NULL
                BEGIN
                    RAISERROR (14043, 16, -1, 'The publication')
                    RETURN (1)
                END

            IF @publication = '%'
                BEGIN
                    RAISERROR (14003, 16, -1, 'The publication')
                    RETURN (1)
                END

            IF  NOT EXISTS (SELECT *
                                  FROM syspublications
                             WHERE restricted = 0
                                   AND name = @publication)
                AND NOT EXISTS (SELECT *
                              FROM syspublications a,
                                       syssubscriptions b,
                                       sysarticles c,
                                       master..sysservers d
                         WHERE a.restricted = 1
                                   AND a.name = @publication
                               AND a.pubid = c.pubid
                                   AND c.artid = b.artid
                           AND b.srvid = d.srvid
                           AND d.srvname =  @@REMSERVER)
                BEGIN
                    RAISERROR (14011, 16, -1)
                    RETURN (1)
                END

        END

    /*
    ** Get the publication sync task id
    */
    SELECT @taskid = taskid FROM syspublications WHERE name LIKE @publication
    IF @@ERROR <> 0 RETURN (1)

    /*
    ** Get distribution server information for remote RPC
    ** task verification.
    */
    EXEC @retcode = sp_helpdistributor @distributor = @distributor OUTPUT
    IF @@error <> 0 OR @retcode <> 0
        BEGIN
        RAISERROR (14071, 16, -1)
            RETURN (1)
    END

    /*
    ** The @taskid must exists in the systasks table.
    */
    SELECT @distproc = RTRIM(@distributor) + '.msdb..sp_verifytaskid'
    EXECUTE @retcode = @distproc @taskid = @taskid, @subsystem = 'Sync'
    IF @@ERROR <> 0 or @retcode <> 0
        BEGIN
            RAISERROR (14002, 16, -1, @taskid)
            RETURN (1)
        END

    /*
    ** Return sync task information
    */
    SELECT @distproc = RTRIM(@distributor) + '.msdb..sp_helptask'
    EXECUTE @retcode = @distproc @taskid = @taskid, @mode = 'full'
    IF @@ERROR <> 0 or @retcode <> 0
       RETURN (1)
go

print ''
print 'Creating procedure sp_helpreplicationdb.'
go

CREATE PROCEDURE sp_helpreplicationdb
        @dbname varchar (30) = '%', @type varchar(30) = 'pub'
    AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    DECLARE @retcode int, @typebit int

    if (lower(@type) like 'pub%')
       select @typebit = 1
    else if (lower(@type) like 'sub%')
       select @typebit = 2
    else
    begin
       raiserror(14091,-1,-1)
       return 1
    end

    /*
    ** Parameter Check:  @dbname.
    ** Check to make sure that the database name conforms to the rules
    ** for identifiers.
    */


    IF @dbname <> '%'
       BEGIN
          EXECUTE @retcode = sp_validname @dbname

          IF @@ERROR <> 0 OR @retcode <> 0
        RETURN (1)
       END

    /*
    ** Show databases with this option enabled.
    */

    SELECT name
      FROM sysdatabases
     WHERE name LIKE @dbname
       AND (category & @typebit) <> 0
go


print ''
print 'Creating procedure sp_enumdsn.'
go
CREATE PROCEDURE sp_enumdsn
    AS

    SET NOCOUNT ON

    DECLARE @distributor varchar(30)
    DECLARE @distproc varchar (255)
    DECLARE @retcode int

    /*
    ** Get distribution server information for remote RPC
    ** subscription calls.
    */

    EXEC @retcode = sp_helpdistributor @distributor = @distributor OUTPUT
    IF @@error <> 0 OR @retcode <> 0
        BEGIN
        RAISERROR (14071, 16, -1)
            RETURN (1)
    END

    /*
    ** Call xp_enumdsn
    */
    SELECT @distproc = RTRIM(@distributor) + '.master..xp_enumdsn'
    EXEC @retcode = @distproc
    IF @@error <> 0
        BEGIN
        RAISERROR (14071, 16, -1)
        RETURN (1)
    END

go

print ''
print 'Creating procedure sp_enumfullsubscribers.'
go

CREATE PROCEDURE sp_enumfullsubscribers (
        @publication varchar(30) = '%'      /* The publication name */
    ) AS

    /*
    ** Declarations.
    */

    DECLARE @retcode int

    /*
    ** Parameter Check: @publication.
    ** Check to make sure that the publication exists and that it conforms
    ** to the rules for identifiers.
    */

    IF @publication IS NOT NULL
        BEGIN

            IF @publication <> '%'
                BEGIN

                    EXECUTE @retcode = sp_validname @publication

                    IF @retcode <> 0
            RETURN (1)
                END

            IF NOT EXISTS (SELECT * FROM syspublications WHERE name LIKE @publication)
                BEGIN
                    RAISERROR (15001, 11, -1, @publication)
                    RETURN (1)
                END

        END

    ELSE
        BEGIN
            RAISERROR (14043, 16, -1, 'The publication')
            RETURN (1)
        END

    /*
    ** Select all subscribers who subscribe to all articles in the desired
    ** publication.
    */

    SELECT DISTINCT 'subscriber' = sv.srvname
      FROM syspublications p,
           sysarticles s,
           syssubscriptions ss,
           master..sysservers sv
     WHERE p.name LIKE @publication
       AND p.pubid = s.pubid
       AND s.artid = ss.artid
       AND ss.srvid = sv.srvid
       AND NOT EXISTS (SELECT *
                         FROM sysarticles s2
                        WHERE s2.pubid = p.pubid
                          AND NOT EXISTS (SELECT *
                                            FROM syssubscriptions ss2,
                                                 master..sysservers sv2
                                           WHERE s2.artid = ss2.artid
                                             AND ss2.srvid = sv2.srvid
                                             AND sv2.srvid = sv.srvid))
go

print ''
print 'Creating procedure sp_helpsubscriberinfo'
go

CREATE PROCEDURE sp_helpsubscriberinfo
        @subscriber varchar (30)
    AS

    SET NOCOUNT ON

    DECLARE @distributor varchar(30)
    DECLARE @distribdb varchar(30)
    DECLARE @distproc varchar (255)
    DECLARE @retcode int

    /*
    ** Check if subscriber is valid
    */

    IF @subscriber IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The subscriber')
            RETURN (1)
        END

    EXECUTE @retcode = sp_validname @subscriber

    IF @retcode <> 0
    RETURN (1)

    IF NOT EXISTS (SELECT *
                     FROM master..sysservers
                    WHERE srvname = @subscriber)

        BEGIN
        RAISERROR (14209, 16, -1, @subscriber)
            RETURN (1)
        END

    /*
    ** Get distribution server information for remote RPC
    ** subscription calls.
    */

    EXEC @retcode = sp_helpdistributor @distributor = @distributor OUTPUT,
                                       @distribdb   = @distribdb OUTPUT

    IF @@error <> 0 OR @retcode <> 0 OR @distribdb IS NULL OR @distributor IS NULL
        BEGIN
        RAISERROR (14071, 16, -1)
            RETURN (1)
    END

    /*
    ** Retrieve MSsubscriber_info
    */

    SELECT @distproc = RTRIM(@distributor) + '.' +
        RTRIM(@distribdb) + '..sp_MShelp_subscriber_info '

    EXEC @retcode = @distproc @@SERVERNAME, @subscriber

    IF @@error <> 0
        BEGIN
        RAISERROR (14071, 16, -1)
        RETURN (1)
    END

    IF @retcode <> 0
        BEGIN
        RAISERROR (14085, 16, -1)
        RETURN (1)
    END

go

print ''
print 'Creating procedure sp_helpsubscription.'
go

CREATE PROCEDURE sp_helpsubscription
    @publication varchar (30) = '%',    /* The publication name */
    @article varchar (30) = '%',        /* The article name */
    @subscriber varchar (30) = '%'      /* The subscriber name */
    AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    DECLARE @retcode int
    DECLARE @subscriber_bit smallint


    /*
    ** Initializations.
    */
    SELECT @subscriber_bit = 4

    /*
    ** Parameter Check:  @subscriber.
    ** If remote server, limit the view to the remote server's subscriptions.
    ** Make sure that the name isn't NULL.
    */

    IF @@REMSERVER IS NOT NULL SELECT @subscriber = @@REMSERVER

    IF @subscriber IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The subscriber')
            RETURN (1)
        END

    /*
    ** Parameter Check:  @subscriber.
    ** Check if remote server is defined as a subscription server, and
    ** that the name conforms to the rules for identifiers.
    */

    IF @subscriber <> '%'
        BEGIN

            EXECUTE @retcode = sp_validname @subscriber

            IF @retcode <> 0
        RETURN (1)

            IF NOT EXISTS (SELECT *
                             FROM sysservers
                            WHERE srvname = @subscriber
                              AND (srvstatus & @subscriber_bit) <> 0)
                BEGIN
                    RAISERROR (14010, 16, -1)
                    RETURN (1)
                END
        END

    /*
    ** Parameter Check:  @publication.
    ** If the publication name is specified, check to make sure that it
    ** conforms to the rules for identifiers and that the publication
    ** actually exists.  Disallow NULL.
    */

    IF @publication IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The publication')
            RETURN (1)
        END

    IF @publication <> '%'
        BEGIN

            EXECUTE @retcode = sp_validname @publication

            IF @retcode <> 0
        RETURN (1)

            IF NOT EXISTS (SELECT * FROM syspublications WHERE name LIKE @publication)
                BEGIN
                   IF @publication = '%'
                       RAISERROR (14008, 11, -1)
                   ELSE
                       RAISERROR (15001, 11, -1, @publication)
                   RETURN (1)
                END

        END

    /*
    ** Parameter Check:  @article.
    ** If the article name is specified, check to make sure that it
    ** conforms to the rules for identifiers and that the article
    ** actually exists.  Disallow NULL.
    */

    IF @article IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The article')
            RETURN (1)
        END

    IF @article <> '%'
        BEGIN

            EXECUTE @retcode = sp_validname @article

            IF @retcode <> 0
        RETURN (1)

            IF NOT EXISTS (SELECT *
                             FROM sysarticles
                            WHERE name = @article
                              AND pubid IN (SELECT pubid
                                              FROM syspublications
                                             WHERE name LIKE @publication))
                BEGIN
                    RAISERROR (15001, 11, -1, @article)
                    RETURN (1)
                END

        END

    /*
    ** Get subscriptions
    */

    SELECT 'subscriber'           = ss.srvname,
           'publication'          = pub.name,
           'article'              = art.name,
           'destination database' = sub.dest_db,
           'subscription status'  = sub.status,
           'synchronization type' = sub.sync_type
      FROM syssubscriptions sub,
           sysservers ss,
           syspublications pub,
           sysarticles art
     WHERE ss.srvname LIKE @subscriber
       AND sub.srvid = ss.srvid
       AND pub.name LIKE @publication
       AND art.name LIKE @article
       AND art.pubid = pub.pubid
       AND sub.artid = art.artid
     ORDER BY subscriber, publication, article
go

print ''
print 'Creating procedure sp_replica.'
go

CREATE PROCEDURE sp_replica (
        @tabname varchar(92),     /* The table being replicated */
        @replicated varchar(5)    /* True or false */
        ) AS

    /*
    ** Declarations.
    */

    DECLARE @db varchar(30)
    DECLARE @id int
    DECLARE @object varchar(30)
    DECLARE @owner varchar(30)
    DECLARE @replica_bit int
    DECLARE @retcode int
    DECLARE @site varchar(30)

    /*
    ** Initializations.
    */

    SELECT @replica_bit = 256

    /*
    ** Parameter Check: @tabname.
    ** Check to make sure that the table exists and that it conforms to the
    ** rules for identifiers.
    */

    IF @tabname IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The table')
            RETURN (1)
        END

    EXECUTE sp_namecrack @tabname,
                         @site OUTPUT,
                         @db OUTPUT,
                         @owner OUTPUT,
                         @object OUTPUT

    EXECUTE @retcode = sp_validname @object

    IF @@ERROR <> 0 OR @retcode <> 0
    return(1)

    IF @owner IS NOT NULL
        BEGIN

            EXECUTE @retcode = sp_validname @owner

            IF @@ERROR <> 0 OR @retcode <> 0
        RETURN (1)
        END

    IF @db IS NOT NULL
        BEGIN

            EXECUTE @retcode = sp_validname @db

            IF @@ERROR <> 0 OR @retcode <> 0
        RETURN (1)

            IF @db <> DB_NAME()
                BEGIN
                    RAISERROR (14004, 16, -1, @object)
                    RETURN (1)
                END

        END

    SELECT @id = OBJECT_ID (@tabname)
    IF @id IS NULL
        BEGIN
            RAISERROR (15001, 11, -1, @tabname)
            RETURN (1)
        END

    IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = @id AND type = 'U')
        BEGIN
            RAISERROR (14028, 11, -1)
            RETURN (1)
        END

    /*
** Parameter Check: @replicated.
    ** Check to make sure that the value is {true | false}.
    */

    IF LOWER(@replicated) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14081, 16, -1)
            RETURN (1)
        END

    /*
    ** Set the category bit on or off depending upon @replicated.
    */

    IF LOWER(@replicated) IN ('true')    /* Turn on bit */
    UPDATE sysobjects
           SET category = category | @replica_bit
         WHERE id = @id
           AND type = 'U'
    ELSE                /* Turn off bit */
     UPDATE sysobjects
           SET category = category & ~@replica_bit
         WHERE id = @id
           AND type = 'U'

    IF @@ERROR <> 0
        BEGIN
            RAISERROR (14083, 16, -1)
            RETURN (1)
        END

    IF LOWER(@replicated) IN ('true')
        PRINT 'The object was successfully marked as a replicated object.'
    ELSE
        PRINT 'The object was successfully unmarked as a replicated object.'

    RETURN (0)
go

print ''
print 'Creating procedure sp_articlefilter.'
go
create procedure sp_articlefilter
    @publication varchar(30),           /* publication name */
    @article varchar(30),             /* article name */
    @filter_name varchar (92) = NULL,     /* name of filter procedure*/
    @filter_clause text = ''               /* article's filter clause */

as

    declare @pubid smallint
    declare @table_name varchar (30)
    declare @user_name varchar (30)
    declare @qualified_table_name varchar (61)
    declare @filter_id int
    declare @type tinyint
    declare @previous_proc varchar (30)
    declare @retcode int
    declare @site varchar(30)
    declare @db varchar(30)
    declare @owner varchar(30)
    declare @object varchar(30)
    declare @artid int
    declare @inactive tinyint


    select @inactive = 0

    /*
    ** Security Check.
    ** Only the System Administrator (SA) or the Database Owner (dbo) can
    ** add an article view.
    */
    if suser_id() <> 1 and user_id() <> 1
       begin
              RAISERROR (15000, 14, -1)
          return (1)
           end

    /*
    ** Parameter Check:  @publication.
    ** Make sure that the publication exists and that it conforms to the
    ** rules for identifiers.
    */
    if @publication is null
           begin
              RAISERROR (14043, 16, -1, 'The publication')
              return (1)
           END

    execute @retcode = sp_validname @publication
    if @retcode <> 0
            RETURN (1)

    select @pubid = pubid from syspublications where name = @publication

        if @pubid is null
           begin
            RAISERROR (15001, 11, -1, @publication)
            return (1)
           end

    /*
    ** Parameter Check:  @article.
    ** Check to make sure that the article exists in the publication.
    */

    if @article is null
       begin
              RAISERROR (14043, 16, -1, 'The article')
              return (1)
           end

        execute @retcode = sp_validname @article
        if @retcode <> 0
       return (1)

    /*
    ** Get the article information.
    */
    select @artid = art.artid, @table_name = so.name, @type = art.type,
       @filter_id = art.filter, @user_name = USER_NAME(so.uid)
       from sysarticles art, sysobjects so
       where art.pubid = @pubid
       and art.name = @article
       and art.objid = so.id

    /*
    ** Fail if there is no article information.
    */
    if @artid is null
       begin
              RAISERROR (15001, 11, -1, @article)
              return (1)
       end

        /*
        ** Only unsubscribed articles may be modified.
        */
        if exists (select * from syssubscriptions where artid = @artid
          and status <> @inactive)
          begin
         RAISERROR (14092, 11, -1)
             RETURN (1)
          end

    /*
    ** Make sure a valid @filter_name was provided and it is
    ** a valid name.
    */
    if datalength(@filter_clause) > 1
       begin
             /*
        ** Make sure a valid @filter_name was provided and it is
        ** a valid name.
        */
        if @filter_name is null
           begin
              RAISERROR (14043, 16, -1, 'The filter_name')
              return (1)
           end

        execute sp_namecrack @filter_name,
           @site OUTPUT,
           @db OUTPUT,
           @owner OUTPUT,
           @object OUTPUT

        execute @retcode = sp_validname @object
        if @retcode <> 0
           return (1)
       end

    /*
    ** If the article has a generated filter (not manually created), then
    ** drop the current filter before creating the new one.
    */
    if ((@type & 0x3) <> 0x3) and @filter_id <> 0
       begin
           select @previous_proc = object_name (@filter_id)
          if @previous_proc is not null and
             exists (select * from sysobjects where name = @previous_proc
                and type = 'RF')
         begin
                exec ('drop procedure ' + @previous_proc)
            if @@error <> 0
               return (1)
         end

       end

    /*
    ** make an owner qualified table name for these operations name
    */

    select @qualified_table_name = @user_name + '.' + @table_name

    /*
    ** If there is a @filter_clause, create the new filter and
    ** update the article filter id and filter_clause.
    **/
    if datalength(@filter_clause) > 1
       begin
        exec ('create procedure ' + @object +
            ' for replication as ' +
            'if exists (select * from ' + @qualified_table_name +
            ' where ' + @filter_clause +
            ') return 1 else return 0')
        if @@error <> 0
           return (1)

        select @filter_id = id  from sysobjects where name = @object
            and type = 'RF'
        if @filter_id is null or @filter_id = 0
           begin
              RAISERROR (15001, 11, -1, @object)
              return (1)
           end

        /*
        ** Update article
        */
        update sysarticles set filter = @filter_id,
           filter_clause = @filter_clause
           where pubid = @pubid
              and name = @article
       end
    else
        /*
        ** Clear the filter id and filter_clause.
        */
        update sysarticles set filter = 0,
           filter_clause = NULL
           where pubid = @pubid
              and name = @article

        /*
        ** Force the article cache to be refreshed with the new definition.
        */
        EXECUTE sp_replflush
go

print ''
print 'Creating procedure sp_articletextcol.'
go
CREATE PROCEDURE sp_articletextcol (
    @artid int,
    @colid tinyint = NULL,
    @type varchar(10),      /* 'publish', 'nonsqlsub' */
    @operation varchar(5))  /* 'add', 'drop' */
    AS

    /*
    ** Declarations.
    */
    DECLARE @cmd char(255)
    DECLARE @cmd1 char(255)
    DECLARE @columns binary(32)    /* Temporary storage for the converted column */
    DECLARE @tabid int        /* Article base table id */
    DECLARE @retcode int
    DECLARE @status bit
    DECLARE @image tinyint        /* Constant: 0x22 */
    DECLARE @text tinyint        /* Constant: 0x23 */
    DECLARE @publish tinyint        /* Constant: 0x10 */
    DECLARE @nonsqlsub tinyint        /* Constant: 0x20 */

    /* Constants */
    SELECT @image = 0x22
    SELECT @text = 0x23
    SELECT @publish = 0x10
    SELECT @nonsqlsub = 0x20

    /*
    ** Security Check
    ** Only the System Administratr (SA) or the Database Owner (dbo)
    ** can execute this procedure.
    */
    IF suser_id() <> 1 AND user_id() <> 1
        BEGIN
            RAISERROR (14093, 14, -1)
            RETURN (1)
        END

    SELECT @tabid = objid FROM sysarticles WHERE artid = @artid

    SELECT @cmd = @cmd + 'DECLARE hCarttextcol CURSOR FOR'

    IF @colid IS NULL
       BEGIN
       SELECT @cmd = @cmd + ' SELECT colid FROM syscolumns, sysarticles'
       SELECT @cmd = @cmd + ' WHERE artid = ' + CONVERT(varchar(10), @artid)
       SELECT @cmd = @cmd + ' AND id = ' + CONVERT(varchar(10), @tabid)
       SELECT @cmd = @cmd + ' AND (syscolumns.type = 0x22 OR syscolumns.type = 0x23)'
       SELECT @cmd1 = @cmd1 + ' AND CONVERT(bit, SUBSTRING(columns,'
       SELECT @cmd1 = @cmd1 + ' CONVERT(tinyint, 32 - FLOOR((colid-1)/8)),'
       SELECT @cmd1 = @cmd1 + ' 1) & POWER(2, ((colid-1)%8))) = 1'
       EXECUTE (@cmd + @cmd1)
       END
    ELSE
       BEGIN
       SELECT @cmd = @cmd + ' SELECT colid FROM syscolumns WHERE id ='
       SELECT @cmd = @cmd + ' ' + CONVERT(varchar(10), @tabid)
       SELECT @cmd = @cmd + ' AND colid =' + CONVERT(varchar(10), @colid)
       SELECT @cmd = @cmd + ' AND (type = 0x22 OR type = 0x23)'
       EXECUTE (@cmd)
       END


    /* Process each Text\Image column in the article */
    OPEN hCarttextcol
    FETCH hCarttextcol INTO @colid
    WHILE (@@fetch_status <> -1)
    BEGIN

       IF LOWER(@operation) = 'add'
          BEGIN
          IF LOWER(@type) = 'publish'
             UPDATE syscolumns
                SET status = status | @publish
            WHERE id = @tabid
                  AND colid = @colid
      ELSE
         UPDATE syscolumns
                SET status = status | @nonsqlsub
            WHERE id = @tabid
                  AND colid = @colid
      END
       ELSE /* drop */
          BEGIN
      /*
      ** Is there another non-sql server subscription on the column?
      ** Or another article publishing the column?
      */
      EXEC @retcode = sp_textcolstatus @artid, @tabid, @colid,
         @type, @status OUTPUT

      IF @@error <> 0 OR @retcode <> 0
         BEGIN
            CLOSE hCarttextcol
        DEALLOCATE hCarttextcol
        RETURN (1)
         END

      IF (@status = 0)
         BEGIN
         IF LOWER(@type) = 'publish'
            /* Clear 'publish' bit */
                UPDATE syscolumns
                   SET status = status & ~@publish
               WHERE id = @tabid
                     AND colid = @colid
          ELSE
            /* Clear 'non-sql server subscription' bit */
                UPDATE syscolumns
                   SET status = status & ~@nonsqlsub
               WHERE id = @tabid
                     AND colid = @colid
             END
          END
       FETCH hCarttextcol INTO @colid
    END

    CLOSE hCarttextcol
    DEALLOCATE hCarttextcol

GO

print ''
print 'Creating procedure sp_textcolstatus.'
go
CREATE PROCEDURE sp_textcolstatus (
    @artid int,
    @tabid int,
    @colid int,
    @type varchar (10),      /* 'publish', 'nonsqlsub' */
    @status bit OUTPUT)
    AS

    /*
    ** Declarations.
    */
    DECLARE @cmd char(255)
    DECLARE @artid2 int
    DECLARE @columns binary(32)
    DECLARE @replicate tinyint        /* Constant: 0x10 */
    DECLARE @nonsqlsub tinyint        /* Constant: 0x20 */
    DECLARE @image tinyint        /* Constant: 0x22 */
    DECLARE @text tinyint        /* Constant: 0x23 */

    /* Constants */
    SELECT @image = 0x22
    SELECT @text = 0x23

    SELECT @status = 0

    /*
    ** Security Check
    ** Only the System Administratr (SA) or the Database Owner (dbo)
    ** can execute this procedure.
    */
    IF suser_id() <> 1 AND user_id() <> 1
        BEGIN
            RAISERROR (14093, 14, -1)
            RETURN (1)
        END

    IF LOWER(@type) = 'nonsqlsub'
    BEGIN
    /*
    ** Check all active or subscribed articles for the TEXT/IMAGE column.
    */
    SELECT @cmd = @cmd + 'DECLARE hC4 CURSOR FOR '
    SELECT @cmd = @cmd + ' SELECT sub.artid FROM sysarticles art, syssubscriptions sub'
    SELECT @cmd = @cmd + '  WHERE art.objid = ' + CONVERT(varchar(10), @tabid)
    SELECT @cmd = @cmd + '  AND art.artid <> ' + CONVERT(varchar(10), @artid)
    SELECT @cmd = @cmd + '  AND sub.artid = art.artid'
    SELECT @cmd = @cmd + '  AND sub.status = 1 OR sub.status = 2'
    END
    ELSE
    BEGIN
    /*
    ** Check all articles for the TEXT/IMAGE column.
    */
    SELECT @cmd = @cmd + 'DECLARE hC4 CURSOR FOR '
    SELECT @cmd = @cmd + ' SELECT artid FROM sysarticles '
    SELECT @cmd = @cmd + '  WHERE objid = ' + CONVERT(varchar(10), @tabid)
    SELECT @cmd = @cmd + '  AND artid <> ' + CONVERT(varchar(10), @artid)
    END

    EXECUTE (@cmd)
    OPEN hC4
    FETCH hC4 INTO @artid2
    WHILE (@@fetch_status <> -1)
    BEGIN
       SELECT @columns = columns FROM sysarticles WHERE artid = @artid2

       IF EXISTS (SELECT * FROM syscolumns
          WHERE id = @tabid
          AND colid = @colid
      AND CONVERT(bit, SUBSTRING(@columns, CONVERT(tinyint,
         32 - FLOOR((colid-1)/8)), 1) & POWER(2, ((colid-1)%8))) = 1
      AND (type = @image OR type = @text))
          BEGIN
         SELECT @status = 1
         GOTO CLEANUP
          END

       FETCH hC4 INTO @artid2
    END

CLEANUP:
    CLOSE hC4
    DEALLOCATE hC4

    RETURN (0)
GO

print ''
print 'Creating procedure sp_articleview.'
go
create procedure sp_articleview
    @publication varchar(30),        /* Publication name */
    @article varchar(30),          /* Article name */
    @view_name varchar (92) = NULL,  /* View name */
    @filter_clause text = ''            /* Article's filter clause */

as
    declare @pubid smallint
    declare @table_name varchar (30)
    declare @user_id int
    declare @user_name varchar (30)
    declare @qualified_table_name varchar (61)
    declare @columns varbinary (32)
    declare @name varchar (30)
    declare @col_clause1 varchar (255)
    declare @col_clause2 varchar (255)
    declare @retcode int
    declare @view_id int
    declare @type tinyint
    declare @table_id int
    declare @previous_view varchar (30)
    declare @colid int
    declare @site varchar(30)
    declare @db varchar(30)
    declare @owner varchar(30)
    declare @object varchar(30)
    declare @artid int
    declare @inactive tinyint


    select @inactive = 0

    /*
    ** Security Check.
    ** Only the System Administrator (SA) or the Database Owner (dbo) can
    ** add an article view.
    */
    if suser_id() <> 1 and user_id() <> 1
       begin
              RAISERROR (15000, 14, -1)
          return (1)
           end

    /*
    ** Parameter Check:  @publication.
    ** Make sure that the publication exists and that it conforms to the
    ** rules for identifiers.
    */
    if @publication is null
           begin
              RAISERROR (14043, 16, -1, 'The publication')
              return (1)
           END

    execute @retcode = sp_validname @publication
    if @retcode <> 0
            RETURN (1)

    select @pubid = pubid from syspublications where name = @publication
        if @pubid is null
           begin
            RAISERROR (15001, 11, -1, @publication)
            return (1)
           end

    /*
    ** Parameter Check:  @article.
    ** Check to make sure that the article exists in the publication.
    */

    if @article is null
       begin
              RAISERROR (14043, 16, -1, 'The article')
              return (1)
           end

        execute @retcode = sp_validname @article
        if @retcode <> 0
       return (1)

    /*
    ** Get the article information.
    */
    select @artid = art.artid, @table_name = so.name,
       @user_id = uid, @user_name = USER_NAME(so.uid),
       @columns = art.columns, @type = art.type,
       @view_id = art.sync_objid, @table_id = art.objid
       from sysarticles art, sysobjects so
       where art.pubid = @pubid
       and art.name = @article
       and art.objid = so.id

    /*
    ** Fail if there is no article information.
    */
    if @artid is null
       begin
              RAISERROR (15001, 11, -1, @article)
              return (1)
       end

        /*
        ** Only unsubscribed articles may be modified.
        */
        if exists (select * from syssubscriptions where artid = @artid
          and status <> @inactive)
          begin
         RAISERROR (14092, 11, -1)
             RETURN (1)
          end


    /*
    ** Create a table of all the articles columns.
    */
    create table #tmp (colid int, name varchar(30), published bit)
    if @@error <> 0
       return (1)

    create unique index ind1 on #tmp (colid)
    if @@error <> 0
       begin
        drop table #tmp
        return (1)
       end

    insert into #tmp select colid, name,
        convert(bit, substring(@columns, convert(tinyint,
            32 - floor((colid-1)/8)), 1) & POWER(2, ((colid-1)%8)))
        from syscolumns
        where id = (select id from sysobjects where name = @table_name and
           uid = @user_id and type = 'U')

    /* Break out the specified view name and get the non-ownerqual'd name, then validate that. */
    execute sp_namecrack @view_name, @site OUTPUT, @db OUTPUT, @owner OUTPUT, @object OUTPUT
    execute @retcode = sp_validname @object
    if @retcode <> 0
        return (1)

    /* If no non-published columns, we'll select all and avoid the 510-byte limit on column strings. */
    if not exists (select * from #tmp where published = 0) begin
        select @col_clause1 = null
        select @col_clause2 = null
        goto CreateView
    end

    /*
    ** Construct the column list based on all published columns in the
    ** article.
    */
    execute ('declare hC scroll cursor for select colid, name from #tmp
        where published = 1')
    open hC
    fetch hC into @colid, @name
    while (@@fetch_status <> -1)
        begin

        if @col_clause1 is null or
        ((datalength(@name) + datalength(@col_clause1) + 2) < 255)
        if @col_clause1 is null
            select @col_clause1 = @name
        else
            select @col_clause1 = @col_clause1 + ', ' + @name

        else if @col_clause2 is null or
        ((datalength(@name) + datalength(@col_clause2) + 2) < 255)
        begin
            if @col_clause2 is null
                select @col_clause2 = ','+ @name
            else
                select @col_clause2 = @col_clause2 + ', ' +
                    @name
        end
        else
            /*
        ** The procedure only support ~510 bytes for the column list
        */
        begin
            RAISERROR (14039, 16, -1)
            close hC
            deallocate hC
            drop table #tmp
            return (1)
        end
        fetch hC into @colid, @name
        end

    close hC
    deallocate hC

CreateView:
    /*
    ** If the article has a generated view (not manually created), then
    ** drop the current view before creating the new one.
    */
    if ((@type & 0x5) <> 0x5) and @view_id <> 0
       and @view_id <> @table_id
       begin
           select @previous_view = object_name (@view_id)
          if @previous_view is not null and
                 exists (select * from sysobjects where name = @previous_view
                and type = 'V')
             exec ('drop view ' + @previous_view)
       end

    /*
    ** If a view is going to be created. Make sure a valid @view_name
    ** was provided.
    */
    if @col_clause1 is not null or @col_clause2 is not null
       begin
          if @view_name is null
             begin
                RAISERROR (14043, 16, -1, 'The view_name')
                return (1)
             end
        end

    /*
    ** make an owner qualified table name for these operations name
    */

    select @qualified_table_name = @user_name + '.' + @table_name

    /*
    ** Construct and execute the view creation command.
    */
    if @col_clause2 is not null
       begin
        if datalength(@filter_clause) > 1
            exec ('create view ' + @object + ' as select ' +
                @col_clause1 + @col_clause2 + ' from ' +
                @qualified_table_name + ' where ' + @filter_clause)
        else
            exec ('create view ' + @object + ' as select ' +
                @col_clause1 + @col_clause2 + ' from ' +
                @qualified_table_name)
        if @@error <> 0
           return (1)
       end
    else if @col_clause1 is not null
      begin

        if datalength(@filter_clause) > 1
            exec ('create view ' + @object + ' as select ' +
                @col_clause1 + ' from ' +  @qualified_table_name +
                ' where ' + @filter_clause)
        else
            exec ('create view ' + @object + ' as select ' +
                @col_clause1 + ' from ' +  @qualified_table_name)
        if @@error <> 0
           return (1)
       end
    else
        begin
            if datalength(@filter_clause) > 1
                exec ('create view ' + @object + ' as select * from ' +
                    @qualified_table_name + ' where ' + @filter_clause)
            if @@error <> 0
               return (1)

        end
    /*
    ** Update the article's sync_objid with the new view or the base
    ** table id.
    */
    if @col_clause1 is null and datalength(@filter_clause) = 1
       select @view_id = object_id(@qualified_table_name)
    else
       begin
          select @view_id = id from sysobjects where name = @object and
             type = 'V'
          if @view_id is null or @view_id = 0
             begin
                RAISERROR (15001, 11, -1, @object)
                return (1)
             end
       end

    /* Update article definition */
    update sysarticles set sync_objid = @view_id where
       pubid = @pubid and
       name = @article

    /*
    ** Set new sync_objid and @filter_clause value
    */
    if datalength(@filter_clause) > 1
       update sysarticles set sync_objid = @view_id,
          filter_clause = @filter_clause
          where pubid = @pubid
          and name = @article
    else
       update sysarticles set sync_objid = @view_id,
          filter_clause = NULL
          where pubid = @pubid
          and name = @article

       drop table #tmp

       /*
       ** Force the article cache to be refreshed with the new definition.
       */
       EXECUTE sp_replflush
go

dump tran master with no_log
go

print ''
print 'Creating procedure sp_addarticle.'
go
CREATE PROCEDURE sp_addarticle
    @publication varchar(30),               /* publication name */
    @article varchar(30),             /* article name */
    @source_table varchar (92),         /* table name */
    @destination_table varchar (30) = NULL, /* destination table name */
    @vertical_partition char(5) = 'false',  /* vertical partition */
    @type varchar (30) = 'logbased',        /* article type */
    @filter varchar (92) = NULL,        /* stored procedure used to filter table */
    @sync_object varchar (92) = NULL,        /* view or table used for synchronization */
    @ins_cmd varchar (255) = 'SQL',        /* insert format string */
    @del_cmd varchar (255) = 'SQL',        /* delete format string */
    @upd_cmd varchar (255) = 'SQL',        /* update format string */
    @creation_script varchar (127) = NULL,  /* article schema script */
    @description varchar (255) = NULL,        /* article description */
    @pre_creation_cmd varchar(10) = 'drop', /* 'none', 'drop', 'delete', 'truncate' */
    @filter_clause text    = ''            /* where clause */
    AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    DECLARE @accessid smallint
    DECLARE @db varchar(30)
    DECLARE @filterid int
    DECLARE @object varchar(30)
    DECLARE @owner varchar(30)
    DECLARE @pubid int
    DECLARE @publish_bit smallint
    DECLARE @retcode int
    DECLARE @site varchar(30)
    DECLARE @syncid int
    DECLARE @tabid int
    DECLARE @typeid smallint
    DECLARE @pkkey varchar (30)
    DECLARE @i int
    DECLARE @indid int
    DECLARE @precmdid int

    SELECT @publish_bit = 32

    /*
    ** Security Check.
    ** Only the System Administrator (SA) or the Database Owner (dbo) can
    ** add an article to a publication.
    */
    IF suser_id() <> 1 AND user_id() <> 1
    BEGIN
            RAISERROR (15000, 14, -1)
        RETURN (1)
        END

    /*
    ** Parameter Check: @article.
    ** The @article name cannot be NULL and must conform to the rules
    ** for identifiers.
    */

    IF @article IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The article')
            RETURN (1)
        END

    EXECUTE @retcode = sp_validname @article

    IF @retcode <> 0
    return(1)

    if LOWER(@article) = 'all'
        BEGIN
            RAISERROR (14032, 16, -1, @article)
            RETURN (1)
        END

    /*
    ** Parameter Check: @publication.
    ** The @publication name cannot be NULL and must conform to the rules
    ** for identifiers.
    */

    IF @publication IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The publication')
            RETURN (1)
        END

    EXECUTE @retcode = sp_validname @publication

    IF @retcode <> 0
    RETURN (1)

    /*
    ** Parameter Check: @source_table.
    ** Check to see that the @source_table is local, that it conforms
    ** to the rules for identifiers, and that it is a table, and not
    ** a view or another database object.
    */

    IF @source_table IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The source table')
            RETURN (1)
        END

    EXECUTE sp_namecrack @source_table,
                         @site OUTPUT,
                         @db OUTPUT,
                         @owner OUTPUT,
                         @object OUTPUT

    IF @source_table LIKE '%.%.%' AND @db <> DB_NAME()
       BEGIN
          RAISERROR (14004, 16, -1, 'The source table')
      RETURN (1)
       END

    EXECUTE @retcode = sp_validname @object

    IF @retcode <> 0
    RETURN (1)

    /*
    **  Get the id of the @source_table
    */

    SELECT @tabid = id FROM sysobjects WHERE id = OBJECT_ID(@source_table)

    IF @tabid IS NULL
        BEGIN
            RAISERROR (14027, 11, -1, 'The source table')
            RETURN (1)
        END

    /*
    ** Make sure that the table name specified is a table and not a view.
    */

    IF NOT EXISTS (SELECT *
                     FROM sysobjects
            WHERE id = (SELECT OBJECT_ID(@source_table))
              AND type = 'U')
    BEGIN
            RAISERROR (14028, 16, -1)
            RETURN (1)
        END

    /*
    ** Parameter Check:  @destination_table.
    ** If the destination table is not specified, assume it's the same
    ** as the source table.  Make sure that the table name is not qualified.
    */

    IF @destination_table LIKE '%.%.%'
    BEGIN
            RAISERROR (14001, 16, -1)
        RETURN (1)
    END

    IF @destination_table LIKE '%.%'
    BEGIN
            RAISERROR (14044, 16, -1, 'destination table')
        RETURN (1)
    END

    IF @destination_table IS NULL
        SELECT @destination_table = @source_table

    EXECUTE sp_namecrack @destination_table,
                         @site OUTPUT,
                         @db OUTPUT,
                         @owner OUTPUT,
                         @object OUTPUT

    EXECUTE @retcode = sp_validname @object

    IF @retcode <> 0
    RETURN (1)

    /*
    ** Parameter Check: @vertical_partition
    ** Check to make sure that the vertical partition is either TRUE or FALSE.
    */

    SELECT @vertical_partition = LOWER(@vertical_partition)
    IF @vertical_partition NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14029, 16, -1)
            RETURN (1)
        END

    /*
    ** Parameter Check: @filter
    ** Make sure that the filter is a valid stored procedure.
    */
    IF @filter IS NOT NULL
        BEGIN

            EXECUTE sp_namecrack @filter,
                                 @site OUTPUT,
                                 @db OUTPUT,
                                 @owner OUTPUT,
                                 @object OUTPUT

        IF @filter LIKE '%.%.%' AND @db <> DB_NAME()
               BEGIN
                  RAISERROR (14004, 16, -1, 'The filter')
          RETURN (1)
           END


            EXECUTE @retcode = sp_validname @object

            IF @retcode <> 0
        RETURN (1)

        /*
        ** Get the id of the @filter
        */
            select @filterid = id from sysobjects where
        id = OBJECT_ID(@filter) and type = 'RF'
            IF @filterid IS NULL
            BEGIN
                    RAISERROR (14027, 11, -1, 'The filter')
                    RETURN (1)
                END
        END
    ELSE
        select @filterid = 0

    /*
    ** Get the pubid.
    */

    SELECT @pubid = pubid FROM syspublications WHERE name = @publication

    IF @pubid IS NULL
        BEGIN
            RAISERROR (14027, 11, -1, 'The publication')
            RETURN (1)
        END

    /*
    ** Parameter Check:  @article, @publication.
    ** Check if the article already exists in this publication.
    */

    IF EXISTS (SELECT *
                 FROM sysarticles
                WHERE pubid = @pubid
                  AND name = @article)
        BEGIN
            RAISERROR (14030, 16, -1, @article, @publication)
            RETURN (1)
        END

    /*
    ** Set the typeid.  The default type is logbased.  Anything else is
    ** currently undefined (reserved for future use).
    **
    **      @typeid     type
    **      =======     ========
    **            1     logbased
    **          3     logbased manualfilter
    **          5     logbased manualview
    **          7     logbased manualboth
    */

    IF LOWER(@type) NOT IN ('logbased', 'logbased manualfilter', 'logbased manualview', 'logbased manualboth')
       BEGIN
            RAISERROR (14023, 16, -1)
            RETURN (1)
       END

    IF LOWER(@type) = 'logbased'
       SELECT @typeid = 1
    ELSE IF LOWER(@type) = 'logbased manualfilter'
       SELECT @typeid = 3
    ELSE IF LOWER(@type) = 'logbased manualview'
       SELECT @typeid = 5
    ELSE IF LOWER(@type) = 'logbased manualboth'
       SELECT @typeid = 7

    /*
    ** Set the precmdid.  The default type is 'drop'.
    **
    **      @precmdid   pre_creation_cmd
    **      =========   ================
    **            0     none
    **          1     drop
    **          2     delete
    **          3     truncate
    */
    IF LOWER(@pre_creation_cmd) NOT IN ('none', 'drop', 'delete', 'truncate')
       BEGIN
          RAISERROR (14061, 16, -1)
          RETURN (1)
       END

    /*
    ** Determine the integer value for the pre_creation_cmd.
    */
    IF LOWER(@pre_creation_cmd) = 'none'
       SELECT @precmdid = 0
    ELSE IF LOWER(@pre_creation_cmd) = 'drop'
       SELECT @precmdid = 1
    ELSE IF LOWER(@pre_creation_cmd) = 'delete'
       SELECT @precmdid = 2
    ELSE IF LOWER(@pre_creation_cmd) = 'truncate'
       SELECT @precmdid = 3

    IF @sync_object IS NULL
        select @syncid = @tabid
    ELSE
        BEGIN

        /*
            ** Parameter Check: @sync_object.
        ** Check to see that the sync_object is local and that it
            ** conforms to the rules for identifiers.
        */

            EXECUTE sp_namecrack @sync_object,
                                 @site OUTPUT,
                                 @db OUTPUT,
                                 @owner OUTPUT,
                                 @object OUTPUT

        IF @sync_object LIKE '%.%.%' AND @db <> DB_NAME()
               BEGIN
                  RAISERROR (14004, 16, -1, 'The synchronization object')
          RETURN (1)
           END


            EXECUTE @retcode = sp_validname @object

            IF @retcode <> 0
        RETURN (1)

        /*
        **  Get the id of the @sync_object
        */

        SELECT @syncid = id FROM sysobjects WHERE id = OBJECT_ID(@sync_object)

        IF @syncid IS NULL
        BEGIN
                    RAISERROR (14027, 11, -1, 'The synchronization object')
            RETURN (1)
        END

        /*
        ** Make sure the sync object specified is a table or a view.
        */

        IF NOT EXISTS (SELECT * FROM sysobjects
                    WHERE id = (SELECT OBJECT_ID(@sync_object))
                    AND (type = 'U' or
                         type = 'V'))
        BEGIN
                    RAISERROR (14031, 16, -1)
            RETURN (1)
        END
    END

    /*
    ** Make sure there is a primary key on the source table.
    */
    IF NOT EXISTS (SELECT * FROM sysconstraints WHERE id = @tabid and
        (status & 0x1) <> 0)     /* PK status */
       BEGIN
          RAISERROR (14088, 16, -1, @source_table)
      RETURN (1)
       END

    /*
    **  Add article to sysarticles and update sysobjects category bit.
    */
    BEGIN TRAN sp_addarticle
        INSERT sysarticles (columns, creation_script, del_cmd, description,
                            dest_table, filter, filter_clause, ins_cmd, name,
                objid, pre_creation_cmd, pubid,
                            status, sync_objid, type, upd_cmd)
        VALUES (0, @creation_script, @del_cmd, @description, @destination_table,
                @filterid, @filter_clause, @ins_cmd, @article, @tabid,
        @precmdid, @pubid, 0, @syncid, @typeid, @upd_cmd)

        IF @@ERROR <> 0
            BEGIN
            ROLLBACK TRAN sp_addarticle
            RETURN (1)
            END

        UPDATE sysobjects
           SET category = category | @publish_bit
         WHERE id = (SELECT objid
                       FROM sysarticles
                      WHERE name = @article
                        AND pubid = @pubid)

        IF @@ERROR <> 0
            BEGIN
            ROLLBACK TRAN sp_addarticle
        RETURN (1)
        END

        /*
        ** Set all bits to '1' in the columns column to include all columns.
        */

        IF @vertical_partition = 'false'
       BEGIN
          EXECUTE @retcode  = sp_articlecolumn @publication, @article
          IF @@ERROR <> 0 OR @retcode <> 0
             BEGIN
             ROLLBACK TRAN sp_addarticle
            RETURN (1)
             END
       END
        /*
    ** Set all bits to '1' for all columns in the primary key.
    */
        ELSE
       BEGIN
        SELECT @indid = indid FROM sysindexes
            WHERE id = @tabid
            AND (status & 2048) <> 0    /* PK index */
        /*
        **  First we'll figure out what the keys are.
        */
        SELECT @i = 1

        WHILE (@i <= 16)
           BEGIN
              SELECT @pkkey = INDEX_COL(@source_table, @indid, @i)
              if @pkkey is NULL
                 goto DONE

              EXECUTE @retcode  = sp_articlecolumn @publication,
                @article, @pkkey, 'add'
              IF @@ERROR <> 0 OR @retcode <> 0
                 BEGIN
                ROLLBACK TRAN sp_addarticle
                RETURN (1)
             END

            select @i = @i + 1
           END
       END

DONE:
    COMMIT TRAN sp_addarticle
go

print ''
print 'Creating procedure sp_addpublisher.'
go

CREATE PROCEDURE sp_addpublisher (
    @publisher varchar (30),      /* publisher server name */
    @type varchar (5) = NULL      /* NULL or 'dist' */
        ) AS

    /*
    ** Declarations.
    */

    DECLARE @distaccount varchar(255)
    DECLARE @proc varchar (255)
    DECLARE @retcode int
    DECLARE @privilege varchar (30)

    /*
    ** Parameter Check:  @publisher.
    ** Check to make sure that the publisher is not NULL and that it
    ** conforms to the rules for identifiers.
    */

    IF @publisher IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The publisher')
            RETURN (1)
        END

    EXECUTE @retcode = sp_validname @publisher

    IF @@ERROR <> 0 OR @retcode <> 0
    RETURN (1)


    /*
    ** Perform special logic if defining a publisher for a distribution
    ** server.
    */
    IF LOWER(@type) = 'dist'
    BEGIN
        /* Check if publisher is already defined. */
        IF EXISTS (SELECT *
             FROM master..sysservers
            WHERE srvname = @publisher
            AND srvstatus &  16 <> 0)

        BEGIN
            RAISERROR (14074, 16, -1, @publisher)
            RETURN (1)
        END

        IF NOT EXISTS (SELECT *
                 FROM master..sysservers
                WHERE srvname = @publisher)

        /* Add the server if it does not exist. */
        BEGIN
            EXECUTE @retcode = sp_addserver @publisher
            IF @@error <> 0 OR @retcode <> 0
            BEGIN
                RAISERROR (14075, 16, -1)
                RETURN (1)
            END
        END

        /*
        ** Set the server option to indicate that this is a
        ** distribution publisher.
        */
        EXECUTE @retcode = sp_serveroption @publisher, 'dpub', true
        IF @@error <> 0 OR @retcode <> 0
        BEGIN
            RAISERROR (14075, 16, -1)
            RETURN (1)
        END

        /* Add remotelogin enabling the 'sa' of the publisher to
        ** RPC for distribution information.
        */
        IF EXISTS (SELECT *
                  FROM sysremotelogins srl,
                   sysservers ss
                 WHERE ss.srvname = @publisher
               AND srl.remoteserverid = ss.srvid
               AND srl.remoteusername = 'sa'
           AND suid = 16383)     /* 'repl_subscriber' */
            BEGIN
           EXECUTE @retcode = sp_dropremotelogin @publisher, repl_subscriber, sa
           IF @@error <> 0 OR @retcode <> 0
           BEGIN
            RAISERROR (14075, 16, -1)
            RETURN (1)
           END
        END

        EXECUTE @retcode = sp_addremotelogin @publisher, sa, sa
        IF @@error <> 0 OR @retcode <> 0
           BEGIN
              RAISERROR (14075, 16, -1)
              RETURN (1)
           END

        EXECUTE @retcode = sp_remoteoption @publisher, sa, sa, trusted, true
        IF @@error <> 0 OR @retcode <> 0
               BEGIN
              RAISERROR (14075, 16, -1)
              RETURN (1)
           END

            /* Add remotelogin enabling the 'probe' of the publisher to
        ** RPC for distribution counter information.
        */
        IF NOT EXISTS (SELECT *
                  FROM sysremotelogins srl,
                   sysservers ss
                 WHERE ss.srvname = @publisher
               AND srl.remoteserverid = ss.srvid
               AND srl.remoteusername = 'probe'
           AND srl.suid = 10)    /* 'probe' */
        BEGIN
           EXECUTE @retcode = sp_addremotelogin @publisher, probe, probe
           IF @@error <> 0 OR @retcode <> 0
           BEGIN
            RAISERROR (14075, 16, -1)
            RETURN (1)
           END
        END

               RETURN (0)
    END

    /*
    ** Check to make sure that the publisher doesn't already exist.
    */
    IF EXISTS (SELECT * FROM master..sysservers
              WHERE srvname = @publisher
                 AND srvstatus & 2 <> 0)
    BEGIN
        RAISERROR (14074, 16, -1, @publisher)
        RETURN (1)
    END

    /*
    ** The server may already be listed in master..sysservers, but might
    ** not be marked as a publisher yet.  If it's not in
    ** master..sysservers, let's add it first.
    */

    IF NOT EXISTS (SELECT *
                     FROM master..sysservers
                    WHERE srvname = @publisher)

        BEGIN
            EXECUTE @retcode = sp_addserver @publisher
            IF @@error <> 0 OR @retcode <> 0
                BEGIN
                    RAISERROR (14075, 16, -1)
                    RETURN (1)
                END
        END

    /*
    ** Fetch the publisher's distributor account.
    */

    SELECT @proc = RTRIM(@publisher) + '.master..sp_helpdistributor '
    EXECUTE @retcode = @proc @account = @distaccount OUTPUT
    IF @@error <> 0 OR @retcode <> 0 OR @distaccount IS NULL
        BEGIN
            RAISERROR (14071, 16, -1)
            RETURN (1)
        END

    /*
    ** Set the server option to indicate that this is a publisher.
    */
    EXECUTE @retcode = sp_serveroption @publisher, 'pub', true
    IF @@error <> 0 OR @retcode <> 0
        BEGIN
            RAISERROR (14075, 16, -1)
            RETURN (1)
        END

    /*
    ** If @distaccount = 'LocalSystem' assume 'admin' privilege
    */
    IF @distaccount = 'LocalSystem'
       RETURN (0)

    /*
    ** Check if @distaccount has admin or repl privilege already.
    */
    EXECUTE @retcode = master.dbo.xp_logininfo @distaccount, 'all',
       @privilege = @privilege output
    IF @@error <> 0 OR @retcode <> 0
        BEGIN
            RAISERROR (14076, 10, -1, @distaccount)
            RETURN (0)
        END

    IF @privilege = 'admin' OR @privilege = 'repl'
       RETURN  (0)

    /*
    ** Grant replication privilege to the distributor NT account.
    */
    EXECUTE @retcode = master.dbo.xp_grantlogin @distaccount, repl
    IF @@error <> 0 OR @retcode <> 0
        BEGIN
            RAISERROR (14076, 10, -1, @distaccount)
            RETURN (0)
        END

go

print ''
print 'Creating procedure sp_addsubscriber.'
go

CREATE PROCEDURE sp_addsubscriber (
    @subscriber varchar (30),
    @type tinyint = 0,
    @login varchar (30) = NULL,
    @password varchar (30) = NULL,
    @commit_batch_size int = 100,
    @status_batch_size int = 100,
    @flush_frequency int = 0,
    @frequency_type int = 4,
    @frequency_interval int = 1,
    @frequency_relative_interval int = 1,
    @frequency_recurrence_factor int = 0,
    @frequency_subday int = 4,
    @frequency_subday_interval int = 5,
    @active_start_time_of_day int = 0,
    @active_end_time_of_day int = 235959,
    @active_start_date int = 0,
    @active_end_date int = 99991231,
    @description varchar (255) = NULL
        ) AS

    DECLARE @distributor varchar(30)
    DECLARE @distribdb varchar(30)
    DECLARE @distproc varchar (255)
    DECLARE @retcode int
    DECLARE @dsn_subscriber tinyint

    select @dsn_subscriber = 1    /* Const: subscriber type 'dsn' */

    /*
    ** Parameter Check:  @subscriber.
    ** Check to make sure that the subscriber doesn't already exist, and
    ** that the name is a valid non-null identifier.
    */

    IF @subscriber IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The subscriber')
            RETURN (1)
        END

    EXECUTE @retcode = sp_validname @subscriber

    IF @@ERROR <> 0 OR @retcode <> 0
    RETURN (1)

    if LOWER(@subscriber) = 'all'
        BEGIN
            RAISERROR (14032, 16, -1, @subscriber)
            RETURN (1)
        END

    IF EXISTS (SELECT *
                 FROM master..sysservers
                WHERE srvname = @subscriber
                  AND srvstatus & 4 <> 0)

        BEGIN
        RAISERROR (14040, 16, -1, @subscriber)
            RETURN (1)
        END


    /*
    **  If no MSsubscriber_info parameters skip RPC code.
    */

    IF @frequency_type = -1
        GOTO ADDSUB

    /*
    ** Get distribution server information for remote RPC
    ** subscription calls.
    */

    EXEC @retcode = sp_helpdistributor @distributor = @distributor OUTPUT,
                                       @distribdb   = @distribdb   OUTPUT

    IF @@error <> 0
        BEGIN
         RAISERROR (14071, 16, -1)
             RETURN (1)
     END

    IF @retcode <> 0 OR @distribdb IS NULL OR @distributor IS NULL
        BEGIN
            RAISERROR (14071, 16, -1)
            RETURN (1)
        END

    /*
    ** Add subscriber to distribution sysservers, if distribution
    ** server is remote.
    */

    If @distributor <> @@SERVERNAME
        BEGIN

        SELECT @distproc = RTRIM(@distributor) + '.master..sp_addserver '
        EXEC @distproc @server = @subscriber, @duplicate_ok = 'duplicate_ok'

        /*
        ** Assume distributor already existed if execute failed. Check
        ** @@error for non-procedure errors.
        */
        IF @@error <> 0
                BEGIN
                RAISERROR (14042, 16, -1)
                RETURN (1)
            END
    END

    /*
    ** Insert information into MSsubscriber_info
    */
    SELECT @distproc = RTRIM(@distributor) + '.' + RTRIM(@distribdb) + '..sp_MSadd_subscriber_info '
    EXEC @retcode = @distproc
         @@SERVERNAME,
         @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,
     @description = @description

    IF @@error <> 0 OR @retcode <> 0
        BEGIN
        RAISERROR (14042, 16, -1)
        RETURN (1)
    END

ADDSUB:

    /*
    ** The server may already be listed in master..sysservers, but might
    ** not be marked as a subscriber yet.  If it's not in
    ** master..sysservers, let's add it first.
    */

    IF NOT EXISTS (SELECT *
                         FROM master..sysservers
                        WHERE srvname = @subscriber)

    EXECUTE @retcode = sp_addserver @subscriber

        IF @@error <> 0 OR @retcode <> 0
            BEGIN
            RAISERROR (14042, 16, -1)
                RETURN (1)
            END

    /*
    ** Set the server option to indicate this is a subscriber.
    */

    EXECUTE @retcode = sp_serveroption @subscriber, 'sub', true

    IF @@error <> 0 OR @retcode <> 0
        BEGIN
       RAISERROR (14042, 16, -1)
           RETURN (1)
        END

    /*
    ** Set the server option to indicate this is a DSN subscriber.
    */
    if @type = @dsn_subscriber
       BEGIN
          EXECUTE @retcode = sp_serveroption @subscriber, 'dsn', true
          IF @@error <> 0 OR @retcode <> 0
             BEGIN
             RAISERROR (14042, 16, -1)
                 RETURN (1)
             END
       END

     /*
     ** Setup remotelogin for subscribing server 'sa' account, if
     ** one does not already exist.
     **/

     IF EXISTS (SELECT *
                  FROM sysremotelogins srl,
                   sysservers ss
                 WHERE ss.srvname = @subscriber
               AND srl.remoteserverid = ss.srvid
               AND (srl.remoteusername = 'sa'
                OR (srl.remoteusername IS NULL AND srl.suid = -1)))
     BEGIN
                RETURN (0)
     END

     EXECUTE @retcode = sp_addremotelogin @subscriber, repl_subscriber, sa

     IF @@error <> 0 OR @retcode <> 0
        BEGIN
        RAISERROR (14042, 16, -1)
        RETURN (1)
    END

    EXECUTE @retcode = sp_remoteoption @subscriber, repl_subscriber, sa, trusted, true

    IF @@error <> 0 OR @retcode <> 0
        BEGIN
            RAISERROR (14042, 16, -1)
            RETURN (1)
        END
go

/*
** Create replication stored procedures.
** Part 1:  create codependent procedures.
*/

print ''
print 'Creating procedure sp_hcchangesubstatus1.'
go
CREATE PROCEDURE sp_hcchangesubstatus1
        @publication varchar(30) = '%',
        @article varchar(30) = '%',
        @subscriber varchar(30) = '%'
        AS

    DECLARE hCsubstatus CURSOR FOR
        SELECT sub.artid,
               art.objid,
               sub.srvid,
               ss.srvname,
               sub.dest_db,
               sub.status,
           ss.srvstatus,
           pub.repl_freq
          FROM syssubscriptions sub,
               sysarticles art,
               syspublications pub,
               sysservers ss
         WHERE pub.name LIKE @publication
           AND art.name LIKE @article
           AND ss.srvname LIKE @subscriber
           AND sub.srvid = ss.srvid
           AND sub.artid = art.artid
           AND art.pubid = pub.pubid
     FOR READ ONLY

go

print ''
print 'Creating procedure sp_hcchangesubstatus2.'
go
CREATE PROCEDURE sp_hcchangesubstatus2
        @publication varchar(30) = '%',
        @article varchar(30) = '%',
        @subscriber varchar(30) = '%',
        @previous_status varchar(30),
        @prevstatid tinyint
        AS

    DECLARE hCsubstatus CURSOR FOR
        SELECT sub.artid,
               art.objid,
               sub.srvid,
               ss.srvname,
               sub.dest_db,
               sub.status,
           ss.srvstatus,
           pub.repl_freq
          FROM syssubscriptions sub,
               sysarticles art,
               syspublications pub,
               sysservers ss
         WHERE pub.name LIKE @publication
           AND art.name LIKE @article
           AND ss.srvname LIKE @subscriber
           AND sub.srvid = ss.srvid
           AND sub.artid = art.artid
           AND art.pubid = pub.pubid
            AND sub.status = @prevstatid
     FOR READ ONLY
go

print ''
print 'Creating procedure sp_changesubstatus.'
go

CREATE PROCEDURE sp_changesubstatus (
    @publication varchar (30) = '%',    /* publication name */
    @article varchar (30) = '%',        /* article name */
    @subscriber varchar(30) = '%',      /* subscriber name */
    @status varchar(30),                /* subscription status */
    @previous_status varchar(30)=NULL   /* previous subscription status */
    ) AS

    SET NOCOUNT ON

    DECLARE @inactive tinyint
    DECLARE @subscribed tinyint
    DECLARE @active tinyint
    DECLARE @public tinyint
    DECLARE @replicate_bit smallint
    DECLARE @subscriber_bit smallint
    DECLARE @msg varchar(255)
    DECLARE @prevstatid tinyint
    DECLARE @artid int
    DECLARE @tabid int
    DECLARE @srvid smallint
    DECLARE @statusid tinyint
    DECLARE @distributor varchar(30)
    DECLARE @distribdb varchar(30)
    DECLARE @distproc varchar (255)
    DECLARE @pub_db varchar(30)
    DECLARE @dest_db varchar (30)
    DECLARE @sub_name varchar (30)
    DECLARE @sub_status tinyint
    DECLARE @sub_ts binary (8)
    DECLARE @sub_type smallint
    DECLARE @cmd0 varchar (255)
    DECLARE @cmd1 varchar (255)
    DECLARE @cmd2 varchar (255)
    DECLARE @cmd3 varchar (255)
    DECLARE @retcode int
    DECLARE @dsn_bit smallint
    DECLARE @repl_freq tinyint

    /*
    ** Initializations.
    */

    SELECT @inactive = 0        /* Const: subscription status 'inactive' */
    SELECT @subscribed = 1      /* Const: subscription status 'subscribed' */
    SELECT @active = 2          /* Const: subscription status 'active' */
    SELECT @public = 0          /* Const: publication status 'public' */
    SELECT @replicate_bit = 64  /* Const: replication bit in sysobjects=0x40 */
    SELECT @subscriber_bit = 4  /* Const: subscription server status */
    SELECT @pub_db = DB_NAME()
    SELECT @dsn_bit = 32

    /*
    ** Parameter Check:  @publication
    ** Check to make sure that the publication exists, that it's not NULL,
    ** and that it conforms to the rules for identifiers.
    */

    IF @publication IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The publication')
            RETURN (1)
        END

    IF @publication <> '%'
        BEGIN
            EXECUTE @retcode = sp_validname @publication
            IF @@ERROR <> 0 OR @retcode <> 0
        RETURN (1)
        END

    IF NOT EXISTS (SELECT * FROM syspublications WHERE name LIKE @publication)
        BEGIN
        IF @publication = '%'
                RAISERROR (14008, 11, -1)
        ELSE
                RAISERROR (15001, 11, -1, @publication)
        RETURN (1)
        END

    /*
    ** Parameter Check:  @article
    ** Check to make sure that the article exists, that it's not null,
    ** and that it conforms to the rules for identifiers.
    */

    IF @article IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The article')
            RETURN (1)
        END

    IF @article <> '%'
        BEGIN
            EXECUTE @retcode = sp_validname @article
            IF @@ERROR <> 0 OR @retcode <> 0
        RETURN (1)
        END

    IF NOT EXISTS (SELECT *
                     FROM sysarticles a,
                          syspublications b
                WHERE a.name LIKE @article
                      AND a.pubid = b.pubid
                      AND b.name LIKE @publication)

        BEGIN
        IF @article = '%'
                RAISERROR (14009, 11, -1, @publication)
        ELSE
                RAISERROR (15001, 11, -1, @article)
        RETURN (1)
        END

    /*
    ** Parameter Check:  @subscriber
    ** Check to make sure that the subscriber exists, that it is not NULL,
    ** and that it conforms to the rules for identifiers.
    */

    IF @subscriber IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The subscriber')
            RETURN (1)
        END

    IF @subscriber <> '%'
        BEGIN
            EXECUTE @retcode = sp_validname @subscriber
            IF @@ERROR <> 0 OR @retcode <> 0
        RETURN (1)
        END

    IF NOT EXISTS (SELECT *
                     FROM master..sysservers
                    WHERE srvname LIKE @subscriber
                      AND (srvstatus & 4) <> 0)

        BEGIN
            IF @subscriber ='%'
                RAISERROR (14064, 11, -1)
        ELSE
                RAISERROR (14063, 11, -1)
            RETURN (1)
        END

    /*
    ** Parameter Check: @status.
    ** Set the @statusid according to the @status value.  Values can be
    ** any of the following:
    **
    **      status      statusid
    **      =========   ========
    **      inactive           0
    **      subscribed         1
    **      active             2
    */

    IF LOWER(@status) NOT IN ('active', 'subscribed', 'inactive')
        BEGIN
            RAISERROR (14065, 16, -1)
        RETURN (1)
        END

    IF LOWER(@status) IN ('active')
        SELECT @statusid = @active
    ELSE IF LOWER(@status) IN ('subscribed')
        SELECT @statusid = @subscribed
    ELSE
        SELECT @statusid = @inactive

    /*
    ** Parameter Check: @previous_status.
    ** Set the @prevstatid according to the @previous_status value.
    ** Values can be any of the following:
    **
    **      previous_status      prevstatid
    **      ===============      ==========
    **      inactive                      0
    **      subscribed                    1
    **      active                        2
    */

    IF @previous_status IS NOT NULL
        BEGIN
            IF LOWER(@previous_status) NOT IN ('active', 'subscribed', 'inactive')
                BEGIN
                    RAISERROR (14066, 16, -1)
                RETURN (1)
                END

            IF LOWER(@status) = LOWER(@previous_status)
                BEGIN
                    RAISERROR (14067, 16, -1)
                RETURN (1)
                END

            IF LOWER(@previous_status) IN ('active')
                SELECT @prevstatid = @active
            ELSE IF LOWER(@previous_status) IN ('subscribed')
                SELECT @prevstatid = @subscribed
            ELSE
               SELECT @prevstatid = @inactive
        END

    /*
    ** Get distribution server information for remote RPC
    ** subscription calls.
    */

    EXEC @retcode = sp_helpdistributor @distributor = @distributor OUTPUT,
                                       @distribdb = @distribdb OUTPUT

    IF @@ERROR <> 0
        BEGIN
            RAISERROR (14071, 16, -1)
            RETURN (1)
     END

    IF @retcode <> 0 OR @distribdb IS NULL OR @distributor IS NULL
        BEGIN
            RAISERROR (14071, 16, -1)
            RETURN (1)
        END

    BEGIN TRANSACTION changesubstatus

        /*
        ** Declare cursor containing subscriptions to be updated.
        */

        IF @previous_status IS NOT NULL
            EXECUTE sp_hcchangesubstatus2 @publication, @article, @subscriber, @previous_status, @prevstatid
        ELSE
            EXECUTE sp_hcchangesubstatus1 @publication, @article, @subscriber

        OPEN hCsubstatus

        FETCH hCsubstatus INTO @artid, @tabid, @srvid, @sub_name, @dest_db,
       @sub_status, @sub_type, @repl_freq

        WHILE (@@fetch_status <> -1)
            BEGIN
            /*
            ** If current status is same as new status, do nothing.
            */

            IF @sub_status = @statusid
                BEGIN
                    FETCH hCsubstatus INTO @artid, @tabid, @srvid, @sub_name,
               @dest_db, @sub_status, @sub_type, @repl_freq
                CONTINUE
                END

        /*
        ** Update syssubscription status
        */
            UPDATE syssubscriptions
                   SET status = @statusid
                   FROM syssubscriptions sub,
                       sysarticles art,
                       syspublications pub
                   WHERE pub.name LIKE @publication
                        AND art.artid = @artid
                        AND sub.srvid = @srvid
                        AND sub.artid = @artid
                        AND art.pubid = pub.pubid
        if @@ERROR <> 0
                       BEGIN
                       CLOSE hCsubstatus
                       DEALLOCATE hsubstatus
                       ROLLBACK TRANSACTION changesubstatus
                        RAISERROR (14053, 16, -1)
                           RETURN (1)
                       END

        /*
        ** Get timestamp of subscription.
        */
        SELECT @sub_ts = timestamp FROM
               syssubscriptions sub,
                       sysarticles art,
                       syspublications pub
                   WHERE pub.name LIKE @publication
                        AND art.artid = @artid
                        AND sub.srvid = @srvid
                        AND sub.artid = @artid
                        AND art.pubid = pub.pubid
        IF @sub_ts IS NULL
                    BEGIN
                       CLOSE hCsubstatus
                       DEALLOCATE hCsubstatus
                       ROLLBACK TRANSACTION changesubstatus
                        RAISERROR (14053, 16, -1)
                           RETURN (1)
                       END

            /*
            ** If activating subscription, update sysarticles, sysobjects and
            ** MSjob_subscriptions.
            */
                IF @statusid = @active
                    BEGIN

                    /*
                    ** Update status of article to show it has been activated.
                    */
                    UPDATE sysarticles SET status = status | 1 WHERE artid = @artid
                    IF @@ERROR <> 0
                        BEGIN
                        CLOSE hCsubstatus
                        DEALLOCATE hCsubstatus
                        ROLLBACK TRANSACTION changesubstatus
                                RAISERROR (14069, 16, -1)
                            RETURN (1)
                        END

                        /*
                        ** Turn the replication flag on for this object in the
                        ** sysobjects table (make it logbased).
                        */

                    if @repl_freq = 0
                      BEGIN
                        UPDATE sysobjects
                               SET category = category | @replicate_bit
                         WHERE id = (SELECT objid
                                           FROM sysarticles
                                          WHERE artid = @artid)
                      END

                        IF @@ERROR <> 0
                            BEGIN
                            CLOSE hCsubstatus
                            DEALLOCATE hCsubstatus
                            ROLLBACK TRANSACTION changesubstatus
                                    RAISERROR (14068, 16, -1)
                                RETURN (1)
                            END

                    END

        /*
        ** Update status of all Text\Image columns if
        ** subscriber is non-SQL Server.
        */
        IF (@sub_type & @dsn_bit) <> 0
           BEGIN
                      IF @statusid = @subscribed OR @statusid = @active
                BEGIN
                 EXEC @retcode = sp_articletextcol @artid, NULL,
                'nonsqlsub', 'add'
                         IF @@ERROR <> 0 OR @retcode <> 0
                            BEGIN
                           CLOSE hCsubstatus
                           DEALLOCATE hCsubstatus
                           ROLLBACK TRANSACTION changesubstatus
                                   RAISERROR (14068, 16, -1)
                               RETURN (1)
                            END
                END
             ELSE IF @statusid = @inactive
                BEGIN
                 EXEC @retcode = sp_articletextcol @artid, NULL,
                'nonsqlsub', 'drop'
                 IF @@ERROR <> 0 OR @retcode <> 0
                BEGIN
                   CLOSE hCsubstatus
                   DEALLOCATE hCsubstatus
                   ROLLBACK TRANSACTION changesubstatus
                   RAISERROR (14068, 16, -1)
                   RETURN (1)
                END
                END
           END

                   /*
            ** If deactivating subscription, update sysarticles, sysobjects and
            ** MSjob_subscriptions.
            */

                IF @statusid <> @active AND @sub_status = @active
                    BEGIN
                /*
            ** Set the article status to 'inactive' if there are
            ** no other active subscriptions on it.
            */
            IF NOT EXISTS (SELECT * FROM syssubscriptions WHERE
               artid = @artid AND status = @active)
               BEGIN
                              UPDATE sysarticles SET status = status & ~1 WHERE
                     artid = @artid
                              IF @@ERROR <> 0
                                  BEGIN
                             CLOSE hCsubstatus
                             DEALLOCATE hCsubstatus
                             ROLLBACK TRANSACTION changesubstatus
                                     RAISERROR (14069, 16, -1)
                                     RETURN (1)
                              END
               END

                /*
            ** Set the object replication bits  to 'inactive' if
            ** there are no other active subscriptions on the
            ** table.
            */
            IF NOT EXISTS (SELECT * FROM syssubscriptions WHERE
               artid IN (SELECT sa.artid FROM sysarticles sa, syspublications sp WHERE
                sa.objid = @tabid and sa.pubid = sp.pubid and sp.repl_freq = 0) AND status = @active)
               BEGIN
                          UPDATE sysobjects
                                 SET category = category & ~@replicate_bit
                                 WHERE id = (SELECT objid
                                      FROM sysarticles WHERE artid= @artid)
                   IF @@ERROR <> 0
                      BEGIN
                             CLOSE hCsubstatus
                             DEALLOCATE hCsubstatus
                     ROLLBACK TRANSACTION changesubstatus
                     RAISERROR (14068, 16, -1)
                     RETURN (1)
                  END
               END

                    END

                /*
                ** Add the active subscription to the distributor's
                ** subscriptions table if changing status from @inactive
                */
                IF @sub_status = @inactive
                    BEGIN
                    SELECT @distproc = RTRIM(@distributor) + '.' + RTRIM(@distribdb) + '..sp_MSadd_subscription '
                    EXEC @retcode = @distproc @@SERVERNAME, @pub_db, @sub_name, @artid, @dest_db, @statusid, @sub_ts
                    IF @@ERROR <> 0 OR @retcode <> 0
                        BEGIN
                               CLOSE hCsubstatus
                               DEALLOCATE hCsubstatus
                        ROLLBACK TRANSACTION changesubstatus
                                RAISERROR (14070, 16, -1)
                        RETURN (1)
                        END
                    END
        ELSE
            BEGIN
               /*
               ** Drop the deactivated subscription from the distributor's
               ** subscriptions table.
               */
                       IF @statusid = @inactive
                          BEGIN
                         SELECT @distproc = RTRIM(@distributor) + '.' + RTRIM(@distribdb) + '..sp_MSdrop_subscription '
                         EXEC @retcode = @distproc @@SERVERNAME, @pub_db, @sub_name,  @artid, @dest_db
                         IF @@ERROR <> 0 OR @retcode <> 0
                            BEGIN
                           CLOSE hCsubstatus
                                  DEALLOCATE hCsubstatus
                           ROLLBACK TRANSACTION changesubstatus
                                   RAISERROR (14070, 16, -1)
                           RETURN (1)
                            END
              END
                       /*
                       ** Update subscription status and timestamp in distributor's
                       ** subscriptions table.
                       */
                ELSE
                  BEGIN
                               SELECT @distproc = RTRIM(@distributor) + '.' + RTRIM(@distribdb) + '..sp_MSupdate_subscription '
                             EXEC @retcode = @distproc @@SERVERNAME, @pub_db, @sub_name, @artid, @statusid, @sub_ts
                             IF @@ERROR <> 0 OR @retcode <> 0
                                BEGIN
                           CLOSE hCsubstatus
                           DEALLOCATE hCsubstatus
                               ROLLBACK TRANSACTION changesubstatus
                                   RAISERROR (14070, 16, -1)
                               RETURN (1)
                               END
                  END
            END

            /*
        ** Set internal object replication bit  to 'inactive' if
        ** there are no other active subscriptions on the
        ** table.
        */
        IF @statusid = @inactive AND @sub_status = @active AND
           NOT EXISTS (SELECT * FROM syssubscriptions WHERE
              artid IN (SELECT artid FROM sysarticles WHERE
              objid = @tabid) AND status = @active)
           BEGIN
                      /* Turn off object replication */
                      SELECT @cmd1 = 'exec sp_replstatus ' + CONVERT(varchar(10), @tabid) + ', 0'
                      EXEC (@cmd1)
                   END

                /* Turn on object replication */
            IF @statusid = @active
           BEGIN
                     SELECT @cmd1 = 'exec sp_replstatus ' + CONVERT(varchar(10), @tabid) + ', 1'
                     EXEC (@cmd1)
           END

                /*
                ** Get next row.
                */
                FETCH hCsubstatus INTO @artid, @tabid, @srvid, @sub_name, @dest_db,
           @sub_status, @sub_type, @repl_freq

            END

        CLOSE hCsubstatus
        DEALLOCATE hCsubstatus

    /*
    ** Force the article cache to be refreshed.
    */
    EXECUTE sp_replflush
    COMMIT TRANSACTION changesubstatus
go

print ''
print 'Creating procedure sp_addsubscription.'
go

CREATE PROCEDURE sp_addsubscription (
    @publication varchar (30),        /* publication name */
    @article varchar (30) = 'all',        /* article name */
    @subscriber varchar(30),        /* subscriber name */
    @destination_db varchar (30) = NULL,    /* destination database */
    @sync_type varchar (15) = 'automatic',    /* subscription sync type */
    @status varchar(30) = NULL              /* subscription status */
    ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    DECLARE @artid int
    DECLARE @pre_creation_cmd tinyint
    DECLARE @none tinyint
    DECLARE @automatic tinyint
    DECLARE @cmd varchar(255)
    DECLARE @cmd2 varchar(255)
    DECLARE @inactive tinyint
    DECLARE @manual tinyint
    DECLARE @pubid int
    DECLARE @retcode int
    DECLARE @srvid smallint
    DECLARE @srvstatus smallint
    DECLARE @subscriber_bit smallint
    DECLARE @sync_typeid tinyint
    DECLARE @dsn_bit smallint
    DECLARE @truncate tinyint
    DECLARE @sync_method tinyint
    DECLARE @char_bcp tinyint

    /*
    ** Initializations.
    */

    SELECT @none = 2            /* Const: synchronization type 'none' */
    SELECT @automatic = 1       /* Const: synchronization type 'automatic' */
    SELECT @manual = 0          /* Const: synchronization type 'manual' */
    SELECT @inactive = 0        /* Const: subscription status 'inactive' */
    SELECT @subscriber_bit = 4  /* Const: subscription server status */
    SELECT @dsn_bit = 32      /* Const: ODBC DSN server status */
    SELECT @truncate = 3    /* Const: truncate pre-creation command */
    SELECT @char_bcp = 1    /* Const: character bcp sync method */

    /*
    ** Security Check.
    ** Only the System Administrator (SA) or the Database Owner (dbo) can
    ** add an article to a publication.
    */

    IF suser_id() <> 1 AND user_id() <> 1
    BEGIN
            RAISERROR (15000, 14, -1)
        RETURN (1)
        END

    /*
    ** Parameter Check: @subscriber.
    ** Check if the server exists and that it is a subscription server.
    */

    IF @subscriber IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The subscriber')
            RETURN (1)
        END

    EXECUTE @retcode = sp_validname @subscriber

    IF @retcode <> 0
    RETURN (1)

    SELECT @srvid = srvid, @srvstatus = srvstatus
      FROM master..sysservers
     WHERE srvname = @subscriber
       AND (srvstatus & @subscriber_bit) <> 0

    IF @srvid IS NULL
        BEGIN
            RAISERROR (14010, 16, -1)
           RETURN (1)
        END

    /*
    ** Parameter Check: @publication.
    ** Check to make sure that the publication exists and that it conforms
    ** to the rules for identifiers.
    */

    IF @publication IS NOT NULL
        BEGIN

            EXECUTE @retcode = sp_validname @publication

            IF @retcode <> 0
        RETURN (1)

            IF NOT EXISTS (SELECT * FROM syspublications WHERE name = @publication)
                BEGIN
                    RAISERROR (15001, 11, -1, @publication)
                    RETURN (1)
                END

        END

    SELECT @pubid = pubid, @sync_method = sync_method
       FROM syspublications WHERE name = @publication

    IF @pubid IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The publication')
            RETURN (1)
        END

    /*
    ** If the subscriber is an ODBC DSN, only allow subscriptions to
    ** publications with a character mode bcp sync_method.
    */
    IF (@srvstatus & @dsn_bit) <> 0 AND @sync_method <> @char_bcp
       BEGIN
            RAISERROR (14095, 16, -1, @publication, @subscriber)
            RETURN (1)
        END

    /*
    ** Parameter Check:  @article
    ** Check to make sure that the article exists, is not NULL, and
    ** conforms to the rules for identifiers.
    */

    IF LOWER(@article) = 'all'
        /*
    ** Get all articles in the publication that are not subscribed to
    ** by the @subscriber
    */
        BEGIN
            SELECT @cmd = ''
            SELECT @cmd = @cmd + 'DECLARE hCx CURSOR FOR '
            SELECT @cmd = @cmd + ' SELECT DISTINCT a.name '
            SELECT @cmd = @cmd + '   FROM sysarticles a, syspublications b '
            SELECT @cmd = @cmd + '  WHERE a.pubid = b.pubid '
            SELECT @cmd = @cmd + '    AND b.name = ''' + @publication + ''''
        SELECT @cmd2 = ' AND NOT EXISTS (SELECT * from syssubscriptions s '
        SELECT @cmd2 = @cmd2 + ' WHERE s.artid = a.artid AND s.status <> 0 AND s.srvid = '
        SELECT @cmd2 = @cmd2 + CONVERT(varchar(10), @srvid) + ')' + ' FOR READ ONLY'
            EXECUTE (@cmd + @cmd2)
            OPEN hCx
            FETCH hCx INTO @article
            WHILE (@@fetch_status <> -1)
                BEGIN
                    EXECUTE sp_addsubscription @publication    = @publication,
                                               @article        = @article,
                                   @subscriber     = @subscriber,
                                               @destination_db = @destination_db,
                                               @sync_type      = @sync_type
                    FETCH hCx INTO @article
                END
            CLOSE hCx
            DEALLOCATE hCx
            RETURN (0)
        END

    SELECT @artid = artid, @pre_creation_cmd = pre_creation_cmd
      FROM sysarticles
     WHERE name = @article
       AND pubid = @pubid

    IF @article IS NOT NULL
        BEGIN
            EXECUTE @retcode = sp_validname @article
            IF @retcode <> 0
        RETURN (1)

        IF NOT EXISTS (SELECT *
                             FROM sysarticles
                            WHERE artid = @artid
                              AND pubid = @pubid)
                BEGIN
                    RAISERROR (15001, 11, -1, @article)
                    RETURN (1)
                END
        END

    IF @artid IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The article')
            RETURN (1)
        END

    /*
    ** If the subscriber is an ODBC DSN, do not allow subscriptions to
    ** articles with a "truncate" pre_creation_cmd.
    */
    IF (@srvstatus & @dsn_bit) <> 0 AND @pre_creation_cmd = @truncate
       BEGIN
            RAISERROR (14094, 16, -1, @article, @subscriber)
            RETURN (1)
        END

   /*
   ** Parameter Check: @sync_type.
   ** Set sync_typeid based on the @sync_type specified.
   **
   **   sync_typeid     sync_type
   **   ===========     =========
   **             0     manual
   **             1     automatic
   **             2     none
   */

   IF LOWER(@sync_type) NOT IN ('automatic', 'manual', 'none')
       BEGIN
           RAISERROR (14052, 16, -1)
           RETURN (1)
       END

   IF LOWER(@sync_type) = 'automatic'
    SELECT @sync_typeid = @automatic
   ELSE IF LOWER(@sync_type) =  'manual'
        SELECT @sync_typeid = @manual
   ELSE
        SELECT @sync_typeid = @none


    /*
    ** If it's an automatic sync, ensure that none of the articles
    ** being subscribed to have the 'owner qualified' status bit set
    */

    IF @sync_typeid = @automatic
    BEGIN
        IF EXISTS ( SELECT * FROM sysarticles
                    WHERE artid = @artid AND pubid = @pubid AND
                    4 = (status & 4) )
        BEGIN
            RAISERROR( 14098, 16, -1 )
            RETURN (1)
        END

    END

    /*
    ** Parameter Check: @destination_db.
    ** Set @destination_db to current database if not specified.  Make
    ** sure that the @destination_db conforms to the rules for identifiers.
    */

    IF @destination_db IS NULL SELECT @destination_db = DB_NAME()

    EXECUTE @retcode = sp_validname @destination_db

    IF @retcode <> 0
    RETURN (1)

    /*
    ** Bug 12850:
    ** Make sure that the creation_script is specified if pre_creation_cmd is "drop"
    ** Note that at this point, @article cannot be 'all'.
    */
    if exists (select * from sysarticles where
            name = @article AND
            pre_creation_cmd = 1 AND
            creation_script is null)
        BEGIN
            RAISERROR (14096, 16, -1)
            RETURN (1)
        END


    /*
    ** Add subscription to syssubscriptions
    */
    BEGIN TRAN addsubscription

    /*
    ** If no subscription exists, add it to syssubscriptions.
    */
    IF NOT EXISTS (SELECT *
                     FROM syssubscriptions
                    WHERE srvid = @srvid
                      AND artid = @artid)
        BEGIN
       INSERT syssubscriptions (artid,
                                    srvid,
                                    dest_db,
                                    status,
                                    sync_type,
                                    timestamp)
       VALUES (@artid,
                   @srvid,
                   @destination_db,
                   @inactive,
                   @sync_typeid,
                   NULL)

       IF @@ERROR <> 0
           BEGIN
               ROLLBACK TRAN  addsubscription
                   RAISERROR (14057, 16, -1)
               RETURN (1)
           END
        END
    ELSE
        BEGIN
           /*
       ** If is a 'restricted' publication the subscription may already
       ** exist.
       */
       IF EXISTS (SELECT * FROM syspublications WHERE pubid = @pubid
                 AND restricted = 0)
           BEGIN
              ROLLBACK TRAN  addsubscription
              RAISERROR (14058, 16, -1)
              RETURN (1)
           END
    END

    /*
    ** If the @status was not provided determine the default value.
    ** If the @sync_type = 'none' then the subscription defaults to 'active'.
    ** Else the subscription defaults to 'subscribed'.
    */
    IF @status IS NULL
       BEGIN
          IF @sync_typeid = @none
         SELECT @status = 'active'
      ELSE
         SELECT @status = 'subscribed'
    END

    /*
    ** Set publication subscription status.
    */
    EXEC @retcode = sp_changesubstatus
    @publication = @publication,
    @article     = @article,
    @subscriber  = @subscriber,
    @status      = @status

    IF @@error <> 0 OR @retcode <> 0
    BEGIN
       ROLLBACK TRAN  addsubscription
           RAISERROR (14057, 16, -1)
       RETURN (1)
    END

    COMMIT TRAN addsubscription
go

print ''
print 'Creating procedure sp_changearticle.'
GO
CREATE PROCEDURE sp_changearticle (
    @publication varchar(30) = NULL,        /* Publication name */
    @article varchar(30) = NULL,            /* Article name */
    @property varchar(20) = NULL,           /* The property to change */
    @value varchar(255) = NULL              /* The new property value */
    ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    DECLARE @artid int
    DECLARE @cmd1 varchar(255)
    DECLARE @cmd2 varchar(255)
    DECLARE @db varchar(30)
    DECLARE @filter int
    DECLARE @object varchar(30)
    DECLARE @owner varchar(30)
    DECLARE @pubid int
    DECLARE @retcode int
    DECLARE @site varchar(30)
    DECLARE @sync_objid int
    DECLARE @typeid tinyint
    DECLARE @precmdid tinyint
    DECLARE @inactive tinyint


    select @inactive = 0

    /*
    ** Security Check
    ** Only the System Administrator (SA) or the Database Owner (dbo) can
    ** perform this procedure.
    */

    IF suser_id() <> 1 AND user_id() <> 1
        BEGIN
            RAISERROR (15000, 14, -1)
            RETURN (1)
        END

    /*
    ** Check to see if the database has been activated for publication.
    */

    IF (SELECT category & 1
          FROM master..sysdatabases
         WHERE name = DB_NAME()) = 0

    BEGIN
            RAISERROR (14013, 16, -1)
        RETURN (1)
    END

    /*
    ** Parameter Check:  @property.
    ** If the @property parameter is NULL, print the options.
    */

    IF @property IS NULL
        BEGIN
            CREATE TABLE #tab1 (properties varchar(30))
            INSERT INTO #tab1 VALUES ('name')
            INSERT INTO #tab1 VALUES ('description')
            INSERT INTO #tab1 VALUES ('sync_object')
            INSERT INTO #tab1 VALUES ('type')
            INSERT INTO #tab1 VALUES ('ins_cmd')
            INSERT INTO #tab1 VALUES ('del_cmd')
            INSERT INTO #tab1 VALUES ('upd_cmd')
            INSERT INTO #tab1 VALUES ('filter')
            INSERT INTO #tab1 VALUES ('dest_table')
            INSERT INTO #tab1 VALUES ('creation_script')
            INSERT INTO #tab1 VALUES ('pre_creation_cmd')
            INSERT INTO #tab1 VALUES ('status')
            PRINT ''
            SELECT * FROM #tab1
            RETURN (0)
        END

    /*
    ** Parameter Check:  @property.
    ** Check to make sure that @property is a valid property in
    ** sysarticles.
    */
    IF @property IS NULL OR LOWER(@property) NOT IN ('name',
                                                     'description',
                                                     'sync_object',
                                                     'type',
                                                     'ins_cmd',
                                                     'del_cmd',
                                                     'upd_cmd',
                                                     'filter',
                                                     'dest_table',
                                                     'creation_script',
                                                     'pre_creation_cmd',
                                                     'status')
        BEGIN
            RAISERROR (14022, 16, -1)
            RETURN (1)
        END


    /*
    ** Parameter Check:  @publication.
    ** Make sure that the publication exists.
    */

    IF @publication IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The publication')
            RETURN (1)
        END

    EXECUTE @retcode = sp_validname @publication

    IF @@ERROR <> 0 OR @retcode <> 0
    RETURN (1)

    SELECT @pubid = pubid FROM syspublications WHERE name = @publication

    IF @pubid IS NULL
        BEGIN
            RAISERROR (15001, 11, -1, @publication)
            RETURN (1)
        END
    ELSE

    /*
    ** Check to see that the article exists in sysarticles.
    ** Fetch the article identification number.
    */

    IF @article IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The article')
            RETURN (1)
        END

    EXECUTE @retcode = sp_validname @article

    IF @retcode <> 0
    RETURN (1)

    SELECT @artid = artid
      FROM sysarticles
     WHERE name = @article
       AND pubid = @pubid
    IF @artid IS NULL
        BEGIN
            RAISERROR (15001, 11, -1, @article)
            RETURN (1)
        END

    /*
    ** Only unsubscribed articles may be modified.
    */
    IF EXISTS (SELECT * FROM syssubscriptions WHERE artid = @artid
       AND status <> @inactive)
        BEGIN
            RAISERROR (14092, 11, -1)
            RETURN (1)
        END

    /*
    ** Change the property.
    */

    IF LOWER(@property) IN ('name', 'description', 'ins_cmd', 'del_cmd', 'upd_cmd', 'dest_table', 'creation_script')
        BEGIN

            IF LOWER(@property) = 'name'
                BEGIN

                    IF @value IS NULL
                        BEGIN
                            RAISERROR (14043, 16, -1, 'The article')
                            RETURN (1)
                        END

                    EXECUTE @retcode = sp_validname @value

                    IF @@ERROR <> 0 OR @retcode <> 0
            RETURN (1)

                    IF EXISTS (SELECT *
                                 FROM syspublications a, sysarticles b
                                WHERE b.name = @value
                                  AND a.pubid = b.pubid
                                  AND a.name = @publication)
                        BEGIN
                            RAISERROR (14016, 16, -1, @value)
                            RETURN (1)
                        END

                END

            /*
            ** Check the validity of the destination table.  NULL should
            ** get converted to the source table name.  Destination table
            ** names can be owner qualified, but not database qualified.
            */

            IF LOWER(@property) = 'dest_table'
                BEGIN
                    IF @value IS NULL
                        SELECT @value = object_name(objid)
                          FROM sysarticles
                         WHERE artid = @artid
                           AND pubid = @pubid
                    IF @value LIKE '%.%.%' OR @value LIKE '%.%'
                        BEGIN
                            EXECUTE sp_namecrack @value,
                                                 @site OUTPUT,
                                                 @db OUTPUT,
                                                 @owner OUTPUT,
                                                 @object OUTPUT
                            IF @db IS NOT NULL
                                BEGIN
                                    RAISERROR (14079, 16, -1)
                                    RETURN (1)
                                END
                            EXECUTE @retcode = sp_validname @object
                            IF @@ERROR <> 0 OR @retcode <> 0
                RETURN (1)
                        END
                    ELSE
                        BEGIN
                            EXECUTE @retcode = sp_validname @value
                            IF @@ERROR <> 0 OR @retcode <> 0
                RETURN (1)
                        END
                END

            SELECT @cmd1 = 'UPDATE sysarticles '
        IF @value IS NULL
        BEGIN
                SELECT @cmd1 = @cmd1 + '   SET ' + @property + ' = NULL'
            SELECT @cmd2 = ' WHERE artid = ' + STR(@artid)
            SELECT @cmd2 = @cmd2 + '   AND pubid = ' + STR(@pubid)
            EXECUTE (@cmd1 + @cmd2)
        END
        ELSE
        BEGIN
            SELECT @cmd1 = @cmd1 + '   SET ' + @property + ' = '''
            SELECT @cmd2 = ''' WHERE artid = ' + STR(@artid)
            SELECT @cmd2 = @cmd2 + '   AND pubid = ' + STR(@pubid)
            EXECUTE (@cmd1 + @value + @cmd2)
        END
            IF @@ERROR <> 0 RETURN (1)
        END

    IF LOWER(@property) = 'sync_object'
        BEGIN

            /*
            ** Check for a valid synchronization object.
            */

            IF @value IS NULL
                BEGIN
                    RAISERROR (14043, 16, -1, 'The synchronization object')
                    RETURN (1)
                END

            IF @value LIKE '%.%.%' OR @value LIKE '%.%'
                BEGIN
                    EXECUTE sp_namecrack @value,
                                         @site OUTPUT,
                                         @db OUTPUT,
                                         @owner OUTPUT,
                                         @object OUTPUT

                    EXECUTE @retcode = sp_validname @object

                    IF @@ERROR <> 0 OR @retcode <> 0
            RETURN (1)

                END

            ELSE
                BEGIN

                    EXECUTE @retcode = sp_validname @value

                    IF @@ERROR <> 0 OR @retcode <> 0
            RETURN (1)
                END

            SELECT @sync_objid = OBJECT_ID(@value)
            IF @sync_objid IS NULL
                BEGIN
                    RAISERROR (15001, 11, -1, @value)
                    RETURN (1)
                END

            IF NOT EXISTS (SELECT *
                             FROM sysobjects
                            WHERE type IN ('U', 'V')
                              AND id = @sync_objid)

                BEGIN
                    RAISERROR (14031, 16, -1)
                    RETURN (1)
                END

            /*
            ** Update the article with the new synchronization object.
            */

            UPDATE sysarticles
               SET sync_objid = @sync_objid
             WHERE artid = @artid
               AND pubid = @pubid

            IF @@ERROR <> 0 RETURN (1)

        END

    IF LOWER(@property) = 'type'
        BEGIN

            /*
            ** Check to make sure that we have a valid type.
            */

        IF LOWER(@value) NOT IN ('logbased', 'logbased manualfilter', 'logbased manualview', 'logbased manualboth')
                BEGIN
                    RAISERROR (14023, 16, -1)
                    RETURN (1)
                END

            /*
            ** Determine the integer value for the type.
            */
        IF LOWER(@value) = 'logbased'
        SELECT @typeid = 1
        ELSE IF LOWER(@value) = 'logbased manualfilter'
        SELECT @typeid = 3
        ELSE IF LOWER(@value) = 'logbased manualview'
        SELECT @typeid = 5
        ELSE IF LOWER(@value) = 'logbased manualboth'
        SELECT @typeid = 7

            /*
            ** Update the article with the new type.
            */

            UPDATE sysarticles
               SET type = @typeid
             WHERE artid = @artid
               AND pubid = @pubid

            IF @@ERROR <> 0 RETURN (1)

        END

    IF LOWER(@property) = 'filter'
        BEGIN

            /*
            ** Check for a valid filter value.
            */

            IF @value IS NOT NULL
                BEGIN

                    IF @value LIKE '%.%.%' OR @value LIKE '%.%'
                        BEGIN
                            EXECUTE sp_namecrack @value,
                                                 @site OUTPUT,
                                                 @db OUTPUT,
                                                 @owner OUTPUT,
                                                 @object OUTPUT

                            EXECUTE @retcode = sp_validname @object

                            IF @@ERROR <> 0 OR @retcode <> 0
                RETURN (1)

                        END

                    ELSE
                        BEGIN

                            EXECUTE @retcode = sp_validname @value

                            IF @@ERROR <> 0 OR @retcode <> 0
                RETURN (1)
                        END
                END

            SELECT @filter = OBJECT_ID(@value)

            IF @value IS NOT NULL
                BEGIN

                    IF @filter IS NULL
                        BEGIN
                            RAISERROR (15001, 11, -1, @value)
                            RETURN (1)
                        END

                    IF NOT EXISTS (SELECT *
                                     FROM sysobjects
                                    WHERE type = 'RF'
                                      AND id = @filter)

                        BEGIN
                            RAISERROR (14049, 16, -1)
                            RETURN (1)
                        END

                END

            IF @value IS NULL SELECT @filter = 0

            /*
            ** Update the article with the new filter.
            */

            UPDATE sysarticles
               SET filter = @filter
             WHERE artid = @artid
               AND pubid = @pubid

            IF @@ERROR <> 0 RETURN (1)

        END

    IF LOWER(@property) = 'pre_creation_cmd'
        BEGIN

            /*
            ** Check to make sure that we have a valid pre_creation_cmd.
            */

            IF LOWER(@value) NOT IN ('none', 'drop', 'delete', 'truncate')
                BEGIN
                    RAISERROR (14061, 16, -1)
                    RETURN (1)
                END

            /*
            ** Determine the integer value for the type.
            */

            IF LOWER(@value) = 'none'
                SELECT @precmdid = 0
            ELSE IF LOWER(@value) = 'drop'
                SELECT @precmdid = 1
            ELSE IF LOWER(@value) = 'delete'
                SELECT @precmdid = 2
            ELSE IF LOWER(@value) = 'truncate'
                SELECT @precmdid = 3

            /*
            ** Update the article with the new pre_creation_cmd.
            */
            UPDATE sysarticles
               SET pre_creation_cmd = @precmdid
             WHERE artid = @artid
               AND pubid = @pubid

            IF @@ERROR <> 0 RETURN (1)

        END

    IF LOWER(@property) = 'status'
        BEGIN
            /*
            ** Check to make sure that we have a valid type.
            */

            IF LOWER(@value) NOT IN ('not owner qualified', 'owner qualified', 'no column names', 'include column names' )
                BEGIN
                    RAISERROR (14097, 16, -1)
                    RETURN (1)
                END

            /*
            ** Determine the integer value for the type.
            */
        IF LOWER(@value) = 'not owner qualified'
            UPDATE sysarticles
            SET status = status & ~4
            WHERE artid = @artid
                          AND pubid = @pubid

        ELSE IF LOWER(@value) = 'owner qualified'
            UPDATE sysarticles
            SET status = status | 4
            WHERE artid = @artid
                          AND pubid = @pubid

        ELSE IF LOWER(@value) = 'no column names'
            UPDATE sysarticles
            SET status = status & ~8
            WHERE artid = @artid
                          AND pubid = @pubid

        ELSE IF LOWER(@value) = 'include column names'
            UPDATE sysarticles
            SET status = status | 8
            WHERE artid = @artid
                          AND pubid = @pubid

        IF @@ERROR <> 0 RETURN (1)

    END

    /*
    ** Force the article cache to be refreshed with the new definition.
    */
    EXECUTE sp_replflush

    /*
    ** Return succeed.
    */

    RAISERROR (14025, 10, -1)
    RETURN (0)
go

print ''
print 'Creating procedure sp_changesubscriber.'
go
CREATE PROCEDURE sp_changesubscriber (
    @subscriber varchar (30),
    @type tinyint = 0,
    @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,
    @description varchar (255) = NULL
        ) AS

    DECLARE @distributor varchar(30)
    DECLARE @distribdb varchar(30)
    DECLARE @distproc varchar (255)
    DECLARE @msg varchar(255)
    DECLARE @retcode int

    /*
    ** Check to make sure that the subscriber doesn't already exist.
    */
    IF NOT EXISTS (SELECT *
                 FROM master..sysservers
                WHERE srvname = @subscriber)
        BEGIN
            SELECT @msg = 'The server ''' + @subscriber +
        ''' is not a valid subscriber.'
            PRINT @msg
            RETURN (0)
        END

    /*
    ** Get distribution server information for remote RPC
    ** subscription calls.
    */
    EXEC @retcode = sp_helpdistributor @distributor = @distributor OUTPUT,
        @distribdb = @distribdb OUTPUT
    IF @@ERROR <> 0 OR @retcode <> 0
        BEGIN
             PRINT 'Unable to update distribution database MSsubscriberinfo table.'
             RETURN (1)
     END

    /*
    ** Update MSsubscriber_info
    */
    SELECT @distproc = RTRIM(@distributor) + '.' +
        RTRIM(@distribdb) + '..sp_MSupdate_subscriber_info '
    EXEC @retcode = @distproc
        @@SERVERNAME,
        @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,
    @description = @description
    IF @@ERROR <> 0 OR @retcode <> 0
        BEGIN
            SELECT @msg = 'The server ''' + @subscriber +
        ''' is not a valid subscriber.'
            PRINT @msg
        RETURN (1)
    END
go

print ''
print 'Creating procedure sp_distcounters'
go
CREATE PROCEDURE sp_distcounters
    AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */
    DECLARE @distributor varchar(30)
    DECLARE @distribdb varchar(30)
    DECLARE @distproc varchar (255)
    DECLARE @retcode int

    /*
    ** Get distribution server information for remote RPC
    ** subscription calls.  If no distribution information, assume
    ** replication is not being used.
    */
    EXEC @retcode = sp_helpdistributor @distributor = @distributor OUTPUT,
                                       @distribdb = @distribdb OUTPUT
    IF @@error <> 0 OR @retcode <> 0 OR @distributor IS NULL OR
       @distribdb IS NULL
    RETURN (1)

    /*
    ** Request counters from Distribution Server
    */
    SELECT @distproc = RTRIM(@distributor) + '.' + RTRIM(@distribdb) +
    '..sp_MSdistribution_counters '
    EXEC @retcode = @distproc @@SERVERNAME
go

print ''
print 'Creating procedure sp_droparticle.'
go

CREATE PROCEDURE sp_droparticle(
        @publication varchar(30),     /* The publication name */
        @article varchar(30)          /* The article name */

        ) AS

    /*
    ** Declarations.
    */

    DECLARE @cmd varchar(255)
    DECLARE @objid int
    DECLARE @pubid int
    DECLARE @publish_bit smallint
    DECLARE @retcode int
    DECLARE @filter_name varchar (30)
    DECLARE @view_name varchar (30)
    DECLARE @type tinyint

    /*
    ** Initializations.
    */

    SELECT @publish_bit = 32    /* Const: publishing server bit */

    /*
    ** Security Check.
    ** Only the System Administrator (SA) or the Database Owner (dbo) can
    ** drop an article from a publication.
    */
    IF suser_id() <> 1 AND user_id() <> 1
    BEGIN
            RAISERROR (15000, 14, -1)
        RETURN (1)
        END

    /*
    ** Get the @pubid.
    */

    SELECT @pubid = pubid FROM syspublications WHERE name = @publication

    /*
    ** Parameter Check:  @article.
    ** If the @article is 'all', drop all articles for the specified
    ** publication (@publication).
    */

    IF LOWER(@article) = 'all'
        BEGIN
            SELECT @cmd = ''
            SELECT @cmd = @cmd + 'DECLARE hC SCROLL CURSOR FOR '
            SELECT @cmd = @cmd + ' SELECT DISTINCT name '
            SELECT @cmd = @cmd + '   FROM sysarticles '
            SELECT @cmd = @cmd + '  WHERE pubid = ' + CONVERT(varchar(10), @pubid)
            EXECUTE (@cmd)
            OPEN hC
            FETCH hC INTO @article
            WHILE (@@fetch_status <> -1)
                BEGIN
                    EXECUTE sp_droparticle @publication, @article
                    FETCH hC INTO @article
                END
            CLOSE hC
            DEALLOCATE hC
            RETURN (0)
        END

    /*
    ** Parameter Check: @article.
    ** The @article name must conform to the rules for identifiers.
    */

    IF @article IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The article')
            RETURN (1)
        END

    EXECUTE @retcode = sp_validname @article

    IF @retcode <> 0
    RETURN (1)

    /*
    ** Parameter Check: @publication.
    ** The @publication name must conform to the rules for identifiers.
    */

    IF @publication IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The publication')
            RETURN (1)
        END

    EXECUTE @retcode = sp_validname @publication

    IF @retcode <> 0
    RETURN (1)

    IF NOT EXISTS (SELECT * FROM syspublications WHERE name = @publication)
        BEGIN
            RAISERROR (15001, 11, -1, @publication)
            RETURN (1)
        END

    /*
    ** Ascertain the existence of the article.
    */

    IF NOT EXISTS (SELECT *
                     FROM sysarticles
                    WHERE name = @article
                      AND pubid = @pubid)
        BEGIN
            RAISERROR (15001, 11, -1, @article)
        RETURN (1)
        END

    /*
    ** Check to make sure that there are no subscriptions on the article.
    */

    IF EXISTS (SELECT *
                 FROM syssubscriptions, sysarticles
                WHERE sysarticles.name = @article
                  AND sysarticles.pubid = @pubid
                  AND sysarticles.artid = syssubscriptions.artid)
        BEGIN
            RAISERROR (14046, 16, -1)
            RETURN (1)
        END

    /*
    **  Delete article from sysarticles and clear publish bit in
    **  sysobjects.
    */

    BEGIN TRAN droparticle

        /*
        ** Retrieve the object id of the underlying table.
        */

        SELECT @objid = objid, @type = type
          FROM sysarticles
         WHERE name = @article
           AND pubid = @pubid

        /*
        ** If this article is the only one that references this object,
        ** then we can safely turn off the publish bit in sysobjects.
        */

        IF NOT EXISTS (SELECT *
                         FROM sysarticles
                        WHERE objid = @objid
                          AND NOT (name = @article AND pubid = @pubid))
            BEGIN
                UPDATE sysobjects SET category = category & ~@publish_bit
             WHERE id = (SELECT objid
                       FROM sysarticles
                              WHERE name = @article
                                AND pubid = @pubid)

            IF @@ERROR <> 0
                BEGIN
                ROLLBACK TRAN
                        RAISERROR (14047, 16, -1, 'the article')
                RETURN (1)
                END
            END

        /*
        ** Drop article view if not logbased manualview (type = 5)
        */
    IF (@type & 5) = 1
       BEGIN
        SELECT @view_name = sysobjects.name
            FROM sysarticles, sysobjects
         WHERE sysarticles.name = @article
           AND pubid = @pubid
           AND sync_objid = sysobjects.id
           AND sysobjects.type = 'V'
       END

        /*
        ** Drop article filter if not logbased manualfilter (type = 3)
        */
    IF (@type & 3) = 1
       BEGIN
        SELECT @filter_name = sysobjects.name
            FROM sysarticles, sysobjects
         WHERE sysarticles.name = @article
           AND pubid = @pubid
           AND filter = sysobjects.id
           AND sysobjects.type = 'RF'

       END

    /*
    ** Drop all article columns.  This is done to force all Text\Image
    ** column status to be updated.
    */
    EXECUTE @retcode  = sp_articlecolumn @publication, @article,
       @operation = 'drop'
        IF @@ERROR <> 0 OR @retcode <> 0
       BEGIN
          ROLLBACK TRAN droparticle
          RETURN (1)
       END

        /*
        ** Remove the row from sysarticles.
        */
        DELETE
          FROM sysarticles
         WHERE name = @article
           AND pubid = @pubid

    IF @@ERROR <> 0
        BEGIN
          ROLLBACK TRAN
                RAISERROR (14047, 16, -1, 'the article')
        RETURN (1)
        END

    COMMIT TRAN droparticle

    IF @view_name IS NOT NULL
       exec ('drop view ' + @view_name)

    IF @filter_name IS NOT NULL
       exec ('drop procedure ' + @filter_name)

    /*
    ** Force the article cache to be refreshed.
    */
    EXECUTE sp_replflush
go

print ''
print 'Creating procedure sp_droppublication.'
go
CREATE PROCEDURE sp_droppublication(
        @publication varchar(30)       /* The publication name */
        ) AS

    /*
    ** Declarations.
    */

    DECLARE @article varchar(30)
    DECLARE @cmd varchar(255)
    DECLARE @retcode int
    DECLARE @taskid int
    DECLARE @distributor varchar(30)
    DECLARE @distribdb varchar(30)
    DECLARE @distproc varchar (255)

    /*
    ** Only the System Administrator (SA) or the Database Owner (dbo) can drop
    ** a publication.
    */

    IF suser_id() <> 1 AND user_id() <> 1
    BEGIN
            RAISERROR (15000, 14, -1)
        RETURN (1)
        END

    /*
    ** Parameter Check:  @publication.
    ** If the @publication is 'all', drop all publications.  Otherwise,
    ** make sure the @publication is a valid non-null identifier.
    */

    IF LOWER(@publication) = 'all'
        BEGIN
            SELECT @cmd = ''
            SELECT @cmd = @cmd + 'DECLARE hC1 SCROLL CURSOR FOR '
            SELECT @cmd = @cmd + ' SELECT DISTINCT name '
            SELECT @cmd = @cmd + '   FROM syspublications '
            SELECT @cmd = @cmd + '  WHERE pubid NOT IN '
            SELECT @cmd = @cmd + '(SELECT pubid FROM sysarticles WHERE artid IN '
            SELECT @cmd = @cmd + '(SELECT artid FROM syssubscriptions))'
            EXECUTE (@cmd)
            OPEN hC1
            FETCH hC1 INTO @publication
            WHILE (@@fetch_status <> -1)
                BEGIN
                    EXECUTE sp_droppublication @publication
                    FETCH hC1 INTO @publication
                END
            CLOSE hC1
            DEALLOCATE hC1
            RETURN (0)
        END

    IF @publication IS NULL
        BEGIN
            RAISERROR (14003, 16, -1)
            RETURN (1)
        END

    EXECUTE @retcode = sp_validname @publication

    IF @retcode <> 0
    RETURN (1)

    /*
    ** Ascertain the existence of the publication and get the taskid.
    */
    SELECT @taskid = taskid
                     FROM syspublications
                    WHERE name = @publication
    IF @taskid IS NULL
        BEGIN
            RAISERROR (15001, 11, -1, @publication)
        RETURN (1)
        END

    /*
    ** Check to make sure that there are no subscriptions on the publication.
    */

    IF EXISTS (SELECT *
                 FROM syssubscriptions a, sysarticles b, syspublications c
                WHERE c.name = @publication
                  AND c.pubid = b.pubid
                  AND b.artid = a.artid)
        BEGIN
            RAISERROR (14005, 16, -1)
            RETURN (1)
        END

    /*
    ** Delete all articles from the publication.
    */

    SELECT @cmd = ''
    SELECT @cmd = @cmd + 'DECLARE hC2 SCROLL CURSOR FOR '
    SELECT @cmd = @cmd + ' SELECT DISTINCT name '
    SELECT @cmd = @cmd + '   FROM sysarticles '
    SELECT @cmd = @cmd + '  WHERE pubid = (SELECT pubid '
    SELECT @cmd = @cmd + '   FROM syspublications '
    SELECT @cmd = @cmd + '  WHERE name = ''' + @publication + ''')'
    EXECUTE (@cmd)
    OPEN hC2
    FETCH hC2 INTO @article
    WHILE (@@fetch_status <> -1)
        BEGIN
            EXECUTE sp_droparticle @publication, @article
            FETCH hC2 INTO @article
        END
    CLOSE hC2
    DEALLOCATE hC2

    /*
    ** Delete publication from syspublications.
    */

    DELETE
      FROM syspublications
     WHERE name = @publication

    IF @@ERROR <> 0
        BEGIN
            RAISERROR (14006, 16, -1)
        RETURN (1)
        END

    /*
    ** Get distribution server information for remote RPC call.
    */
    EXEC @retcode = sp_helpdistributor @distributor = @distributor OUTPUT,
                                       @distribdb = @distribdb OUTPUT

    IF @@ERROR <> 0 OR  @retcode <> 0
        BEGIN
            RAISERROR (14071, 16, -1)
            RETURN (1)
        END

    /*
    ** Delete sync task of Publication.
    */
    SELECT @distproc = RTRIM(@distributor) + '.msdb..sp_droptask'
    EXECUTE @distproc @id = @taskid
go


print ''
print 'Creating procedure sp_droppublisher.'
go
CREATE PROCEDURE sp_droppublisher (
    @publisher varchar (30),         /* publisher server name */
    @type varchar (5) = NULL      /* NULL or 'dist' */
        ) AS

    DECLARE @distaccount varchar(127)
    DECLARE @proc varchar (255)
    DECLARE @retcode int
    DECLARE @privilege varchar (30)

    /*
    ** Parameter Check:  @publisher.
    ** Check to make sure that the publisher exists, that the name isn't
    ** NULL, and that the name conforms to the rules for identifiers.
    */

    IF @publisher IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The publisher')
            RETURN (1)
        END

    EXECUTE @retcode = sp_validname @publisher

    IF @retcode <> 0
    RETURN (1)

    /*
    ** Perform special logic if dropping a publisher for a distribution
    ** server.
    */
    IF LOWER(@type) = 'dist'
        BEGIN
        IF NOT EXISTS (SELECT *
        FROM master..sysservers
                WHERE srvname = @publisher
                AND srvstatus & 16 <> 0)

        BEGIN
        RAISERROR (14080, 11, -1)
            RETURN (1)
        END

        EXECUTE @retcode = sp_serveroption @publisher, 'dpub', false
        IF @@ERROR <> 0 OR @retcode <> 0 RETURN (1)

            IF EXISTS (SELECT * FROM master..sysremotelogins
           WHERE remoteserverid = (SELECT srvid FROM master..sysservers
           WHERE srvname = @publisher)
           AND remoteusername = 'sa'
           AND suid = 1)     /* 'sa' */
        BEGIN
           EXECUTE @retcode = sp_dropremotelogin @publisher, sa, sa
           IF @@ERROR <> 0 OR @retcode <> 0 RETURN (1)
        END

            IF EXISTS (SELECT * FROM master..sysremotelogins
           WHERE remoteserverid = (SELECT srvid FROM master..sysservers
           WHERE srvname = @publisher)
           AND remoteusername = 'probe'
           AND suid = 10)     /* 'probe' */
        BEGIN
           EXECUTE @retcode = sp_dropremotelogin @publisher, probe, probe
           IF @@ERROR <> 0 OR @retcode <> 0 RETURN (1)
        END

        RETURN (0)
    END

    /*
    ** Make sure the server is defined as a 'publisher'.
    */
    IF NOT EXISTS (SELECT *
                     FROM master..sysservers
                    WHERE srvname = @publisher
                      AND srvstatus & 2 <> 0)

        BEGIN
            RAISERROR (14080, 11, -1)
            RETURN (1)
        END

    /*
    ** Turn off the server option to indicate that this is a publisher.
    */
    EXECUTE @retcode = sp_serveroption @publisher, 'pub', false
    IF @@ERROR <> 0 OR @retcode <> 0 RETURN (1)

    /*
    ** Fetch the publisher's distributor account.
    */

    SELECT @proc = RTRIM(@publisher) + '.master..sp_helpdistributor '
    EXEC @retcode = @proc @account = @distaccount OUTPUT
    IF @@ERROR <> 0 OR @retcode <> 0
        BEGIN
            RAISERROR (14071, 16, -1)
            RETURN (1)
        END

    /*
    ** If @distaccount = 'LocalSystem' assume 'admin' privilege
    */
    IF @distaccount = 'LocalSystem'
       RETURN (0)

    /*
    ** If @distaccount has 'admin' privilege, do not revoke
    */
    EXECUTE @retcode = master.dbo.xp_logininfo @distaccount, 'all',
       @privilege = @privilege output
    IF @@error <> 0 OR @retcode <> 0 RETURN (1)

    IF @privilege = 'admin'
       RETURN  (0)

    /*
    ** Revoke replication privilege to the distributor NT account.
    */
    EXEC @retcode = master.dbo.xp_revokelogin @distaccount
    IF @@ERROR <> 0 OR @retcode <> 0 RETURN (1)
go

print ''
print 'Creating procedure sp_dropsubscriber.'
go
CREATE PROCEDURE sp_dropsubscriber (
    @subscriber varchar (30)        /* The name of the subscriber */
        ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    DECLARE @retcode int

    /*
    ** Security Check.
    ** Only the System Administrator (SA) or the Database Owner (dbo) can
    ** drop a subscriber
    */
    IF suser_id() <> 1 AND user_id() <> 1
    BEGIN
            RAISERROR (15000, 14, -1)
        RETURN (1)
        END

    /*
    ** Parameter Check:  @subscriber.
    ** Check to make sure that the subscriber exists.
    */

    IF @subscriber IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The subscriber')
            RETURN (1)
        END

    EXECUTE @retcode = sp_validname @subscriber

    IF @retcode <> 0
    RETURN (1)

    IF NOT EXISTS (SELECT *
                     FROM master..sysservers
                    WHERE srvname = @subscriber
                      AND srvstatus & 4 <> 0)

        BEGIN
            RAISERROR (14048, 16, -1, @subscriber)
            RETURN (1)
        END

    /*
    ** Drop the remote logins associated with this server.
    */

    IF EXISTS (SELECT * FROM master..sysremotelogins
    WHERE remoteserverid = (SELECT srvid FROM master..sysservers
           WHERE srvname = @subscriber)
       AND remoteusername = 'sa'
       AND suid = 16383)     /* 'repl_subscriber' */
    BEGIN
        EXECUTE @retcode = sp_dropremotelogin @subscriber,
        'repl_subscriber', 'sa'
        IF @@ERROR <> 0
               BEGIN
           ROLLBACK TRANSACTION
                   RAISERROR (14047, 16, -1, @subscriber)
                   RETURN (1)
               END

        IF @retcode <> 0
        BEGIN
           ROLLBACK TRANSACTION
           RETURN (1)
        END
    END

    /*
    ** Turn off the subscriber server option.
    */
    EXECUTE @retcode = sp_serveroption @subscriber, 'sub', false
    IF @@ERROR <> 0
        BEGIN
           ROLLBACK TRANSACTION
           RAISERROR (14047, 16, -1, @subscriber)
           RETURN (1)
        END

    IF @retcode <> 0
        BEGIN
           ROLLBACK TRANSACTION
           RETURN (@retcode)
        END

    RAISERROR (14062, 10, -1)
go

print ''
print 'Creating procedure sp_dropsubscription.'
go
CREATE PROCEDURE sp_dropsubscription (
    @publication varchar (30) = NULL,   /* The publication name */
    @article varchar (30) = NULL,       /* The article name */
    @subscriber varchar (30)            /* The subscriber name */
    ) AS

    /*
    ** Declarations.
    */

    DECLARE @subscriber_bit smallint
    DECLARE @cmd varchar(255)
    DECLARE @srvid smallint
    DECLARE @artid int
    DECLARE @retcode int
    DECLARE @active tinyint

    /*
    ** Initializations.
    */
    SET NOCOUNT ON
    SELECT @subscriber_bit = 4  /* Const: subscription server status */
    SELECT @active = 2          /* Const: subscription status 'active' */

    /*
    ** Security Check.
    ** Only the System Administrator (SA) or the Database Owner (dbo) can
    ** add an article to a publication.
    */

    IF suser_id() <> 1 AND user_id() <> 1
    BEGIN
            RAISERROR (15000, 14, -1)
        RETURN (1)
        END

    /*
    ** If the @subscriber is 'all', the user wants to cancel all subscriptions
    ** to the specified article(s).
    */

    IF LOWER(@subscriber) = 'all'
        BEGIN
            SELECT @cmd = ''
            SELECT @cmd = @cmd + 'DECLARE hC1 SCROLL CURSOR FOR '
            SELECT @cmd = @cmd + ' SELECT DISTINCT srvname '
            SELECT @cmd = @cmd + '   FROM master..sysservers a, syssubscriptions b '
            SELECT @cmd = @cmd + '  WHERE srvstatus & ' + CONVERT(char(1), @subscriber_bit) + ' <> 0 '
            SELECT @cmd = @cmd + '    AND a.srvid = b.srvid '
            EXECUTE (@cmd)
            OPEN hC1
            FETCH hC1 INTO @subscriber
            WHILE (@@fetch_status <> -1)
                BEGIN
                    EXECUTE sp_dropsubscription @publication = 'all',
                                    @subscriber  = @subscriber
                    FETCH hC1 INTO @subscriber
                END
            CLOSE hC1
            DEALLOCATE hC1
            RETURN (0)
        END

    /*
    ** Parameter Check: @subscriber.
    ** Check if the server exists and that it is a subscription server.
    */

    IF @subscriber IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The subscriber')
            RETURN (1)
        END

    EXECUTE @retcode = sp_validname @subscriber

    IF @retcode <> 0
    RETURN (1)

    SELECT @srvid = srvid
      FROM sysservers
     WHERE srvname = @subscriber
       AND (srvstatus & @subscriber_bit) <> 0

    IF @srvid IS NULL
        BEGIN
            RAISERROR (14010, 16, -1)
            RETURN (1)
        END

    /*
    ** If the @publication is 'all', the user wants to cancel all subscriptions
    ** for all publications associated with the specified @subscriber.
    */

    IF LOWER(@publication) = 'all'
        BEGIN
            SELECT @cmd = ''
            SELECT @cmd = @cmd + 'DECLARE hC2 SCROLL CURSOR FOR '
            SELECT @cmd = @cmd + ' SELECT DISTINCT a.name '
            SELECT @cmd = @cmd + '   FROM syspublications a, '
            SELECT @cmd = @cmd + '        sysarticles b, '
            SELECT @cmd = @cmd + '        syssubscriptions c '
            SELECT @cmd = @cmd + '  WHERE c.srvid = ' + CONVERT(char(10), @srvid)
            SELECT @cmd = @cmd + '    AND a.pubid = b.pubid '
            SELECT @cmd = @cmd + '    AND b.artid = c.artid '
            EXECUTE (@cmd)
            OPEN hC2
            FETCH hC2 INTO @publication
            WHILE (@@fetch_status <> -1)
                BEGIN
                    EXECUTE sp_dropsubscription @publication = @publication,
                                                @article     = 'all',
                                    @subscriber  = @subscriber
                    FETCH hC2 INTO @publication
                END
            CLOSE hC2
            DEALLOCATE hC2
            RETURN (0)
        END

    /*
    ** Parameter Check: @publication.
    ** Check to make sure that the publication exists and that it conforms
    ** to the rules for identifiers.
    */

    IF @publication IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The publication')
            RETURN (1)
        END

    EXECUTE @retcode = sp_validname @publication

    IF @retcode <> 0
    RETURN (1)

    IF NOT EXISTS (SELECT * FROM syspublications WHERE name = @publication)
        BEGIN
            RAISERROR (15001, 11, -1, @publication)
            RETURN (1)
        END

    /*
    ** If the @article is 'all', the user wants to cancel all
    ** subscriptions on this publisher associated with the given @subscriber
    ** and @publication.
    */

    IF LOWER(@article) = 'all'
        BEGIN
            SELECT @cmd = ''
            SELECT @cmd = @cmd + 'DECLARE hC3 SCROLL CURSOR FOR '
            SELECT @cmd = @cmd + 'SELECT DISTINCT art.name '
            SELECT @cmd = @cmd + 'FROM sysarticles art, '
            SELECT @cmd = @cmd + 'syssubscriptions sub, '
            SELECT @cmd = @cmd + 'syspublications pub '
            SELECT @cmd = @cmd + 'WHERE sub.srvid = ' + CONVERT(char(10), @srvid)
            SELECT @cmd = @cmd + 'AND sub.artid = art.artid '
            SELECT @cmd = @cmd + 'AND art.pubid = pub.pubid '
            SELECT @cmd = @cmd + 'AND pub.name = ''' + @publication + ''''
            EXECUTE (@cmd)
            OPEN hC3
            FETCH hC3 INTO @article
            WHILE (@@fetch_status <> -1)
                BEGIN
                    EXECUTE sp_dropsubscription @publication,
                                                @article,
                                    @subscriber
                    FETCH hC3 INTO @article
                END
            CLOSE hC3
            DEALLOCATE hC3
            RETURN (0)
        END

    /*
    ** Parameter Check: @article
    ** Check if the article exists.
    */

    IF @article IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The article')
            RETURN (1)
        END

    EXECUTE @retcode = sp_validname @article

    IF @retcode <> 0
    RETURN (1)

    SELECT @artid = artid
      FROM sysarticles art, syspublications pub
     WHERE pub.name = @publication
       AND art.name = @article
       AND art.pubid = pub.pubid

    IF @artid IS NULL
        BEGIN
            RAISERROR (15001, 11, -1, @article)
        RETURN (1)
        END

    /*
    ** Check if the subscription exists.
    */

    IF NOT EXISTS (SELECT *
                     FROM syssubscriptions
                    WHERE srvid = @srvid
                      AND artid = @artid)
    BEGIN
            RAISERROR (14055, 11, -1, @article, @publication, @subscriber)
            RETURN (1)
    END

    BEGIN TRANSACTION dropsubscription

        /*
        ** Change the status of the subscription to 'inactive'.
        */

        EXECUTE @retcode = sp_changesubstatus @publication,
                                              @article,
                                      @subscriber,
                                              @status = 'inactive'
        IF @@ERROR <> 0 OR @retcode <> 0
        BEGIN
            ROLLBACK TRANSACTION dropsubscription
            RETURN (1)
        END
        /*
        ** Remove subscription from syssubscriptions.
        */

    DELETE syssubscriptions
     WHERE artid = @artid
       AND srvid = @srvid

    IF @@ERROR <> 0
        BEGIN
        ROLLBACK TRANSACTION dropsubscription
        RAISERROR (14055, 16, -1, @article, @publication, @subscriber)
        RETURN (1)
        END

    COMMIT TRANSACTION dropsubscription


go

print ''
print 'Creating procedure sp_dsninfo.'
go
CREATE PROCEDURE sp_dsninfo
        @dsn varchar (30),
    @infotype varchar (30) = NULL,
    @login varchar (30) = NULL,
    @password varchar (30) = NULL
    AS

    SET NOCOUNT ON

    DECLARE @distributor varchar(30)
    DECLARE @distproc varchar (255)
    DECLARE @retcode int

    /*
    ** Get distribution server information for remote RPC
    ** subscription calls.
    */

    EXEC @retcode = sp_helpdistributor @distributor = @distributor OUTPUT
    IF @@error <> 0 OR @retcode <> 0
        BEGIN
        RAISERROR (14071, 16, -1)
            RETURN (1)
    END

    /*
    ** Call xp_dsninfo
    */
    SELECT @distproc = RTRIM(@distributor) + '.master..xp_dsninfo '
    EXEC @retcode = @distproc @dsn, @infotype, @login, @password
    IF @@error <> 0
        BEGIN
        RAISERROR (14071, 16, -1)
        RETURN (1)
    END

go

print ''
print 'Creating procedure sp_publishdb.'
go
CREATE PROCEDURE sp_publishdb @dbname varchar(30),@value varchar (5)
    AS

    DECLARE @retcode int
    DECLARE @distributor varchar(30)
    DECLARE @distribdb varchar(30)
    DECLARE @distproc varchar (255)
    DECLARE @taskname varchar (40)

    /*
    ** Construct Log Reader task name.
    */
    select @taskname = @@SERVERNAME + '_' + @dbname

    /*
    ** Get distribution server information for remote RPC call.
    */
    EXECUTE @retcode = sp_helpdistributor @distributor = @distributor OUTPUT,
       @distribdb   = @distribdb OUTPUT

    IF @@ERROR <> 0 or @retcode <> 0
        BEGIN
           IF LOWER(@value) = 'true'
         RAISERROR (14036, 16, -1)
       ELSE
         RAISERROR (14038, 16, -1)
       RETURN (1)
    END

    /*
    ** Enable the database for publishing.
    */
    IF @value = 'true'
    begin
        /*
        ** Check if the database has already been enabled.
        */
        IF EXISTS (SELECT * FROM sysdatabases
            WHERE name = @dbname
            AND (category & 1) <> 0)
            BEGIN
                RAISERROR (14035, 10, -1, @dbname)
                RETURN (0)
            END

        /*
        ** Schedule Log Reader task for the database
        */
        SELECT @distproc = RTRIM(@distributor) + '.msdb..sp_addtask'
        EXECUTE @retcode = @distproc
        @taskname,
        @subsystem = 'LogReader',
        @server = @@SERVERNAME,
        @databasename = @dbname,
        @freqtype = 64,        /* Auto-Start */
        @retryattempts = 1440,    /* Every minute for 24 hours */
        @retrydelay = 1,
        @command = '-b100 -c100 -i1000 -t100',
        @enabled = 1,
        @loghistcompletionlevel = 0
        IF @@ERROR <> 0 or @retcode <> 0
            BEGIN
                RETURN (1)
            END

        /*
            ** Add the repl_subscriber user account to the database.
        */
        IF NOT EXISTS (SELECT * FROM sysusers WHERE name = 'repl_subscriber')
            EXECUTE sp_adduser 'repl_subscriber'
    END

else    /* Disable the database for publishing. */
    begin
          /*
        ** Check if the database is enabled for publishing.
            */
        IF NOT EXISTS (SELECT * FROM sysdatabases
            WHERE name = @dbname
            AND (category & 1) <> 0)
            BEGIN
                RAISERROR (14013, 10, -1)
                return (0)
            END
            /*
            ** Delete logreader task, continue if drop is not successful
            */
            SELECT @distproc = RTRIM(@distributor) + '.msdb..sp_droptask'
            EXECUTE @distproc @name = @taskname

        /*
            ** Remove all subscriptions in the database.
               */
        EXEC sp_dropsubscription @publication = 'all',
            @article = 'all', @subscriber = 'all'

            /*
            ** Remove all publications and articles in the database.
            */
        EXEC sp_droppublication @publication = 'all'

        /*
        ** Remove all published database jobs from the distribution
        ** database.
        */
            SELECT @distproc = RTRIM(@distributor) + '.' +
                RTRIM(@distribdb) + '..sp_MSremove_published_jobs '
            EXEC @distproc @@SERVERNAME, @dbname

        /*
        ** Remove the repl_subscriber user account from the database.
        */

        IF EXISTS (SELECT * FROM sysusers WHERE name = 'repl_subscriber')
           exec sp_dropuser 'repl_subscriber'

        /*
        ** Publishing shutdown, remove all xacts pending distribution
        */

        /* ensure we can get in as logreader */

        EXEC sp_replflush

        /* unmark all xacts marked for replication */

        EXEC ( "USE " + @dbname + " exec sp_repldone 0, 0, NULL, 0, 0, 1" )
        EXEC ( "USE " + @dbname + " checkpoint" )

        /* release our hold on the db as logreader */

        EXEC sp_replflush
    end

    return(0)
go

print ''
print 'Creating procedure sp_subscribe.'
go
CREATE PROCEDURE sp_subscribe (
    @publication varchar(30),          /* publication name */
    @article varchar(30) = 'all',          /* article name */
    @destination_db varchar (30) = NULL,  /* subscriber database */
    @sync_type varchar (15) = 'automatic' /* subscription sync type */
    ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    DECLARE @artid int
    DECLARE @none tinyint
    DECLARE @automatic tinyint
    DECLARE @cmd varchar(255)
    DECLARE @inactive tinyint
    DECLARE @manual tinyint
    DECLARE @pubid int
    DECLARE @restricted bit
    DECLARE @retcode int
    DECLARE @srvid smallint
    DECLARE @sync_typeid smallint
    DECLARE @subscriber_bit smallint

    /*
    ** Security Check
    ** Only the System Administratr (SA) or the Database Owner (dbo) or
    ** Replication subscriber (repl_subscriber) can subscribe to an article.
    */

    IF suser_id() <> 1 AND user_id() <> 1 AND user_id() <> 16383
        BEGIN
            RAISERROR (14093, 14, -1)
            RETURN (1)
        END

    /*
    ** Initializations.
    */

    SELECT @none = 2            /* Const: synchronization type 'none' */
    SELECT @automatic = 1       /* Const: synchronization type 'automatic' */
    SELECT @manual = 0          /* Const: synchronization type 'manual' */
    SELECT @restricted = 1      /* Const: security option 'restricted' */
    SELECT @inactive = 0        /* Const: subscription status 'inactive' */
    SELECT @subscriber_bit = 4  /* Const: subscription server status */

    /*
    ** Check to make sure that this procedure is not being executed on the
    ** publisher (locally).
    */

    IF @@REMSERVER IS NULL
        BEGIN
            RAISERROR (14073, 16, -1)
            RETURN (1)
        END

    SELECT @srvid = srvid
      FROM master..sysservers
     WHERE srvname = @@REMSERVER
       AND (srvstatus & @subscriber_bit) <> 0

    IF @srvid IS NULL
        BEGIN
            RAISERROR (14010, 16, -1)
           RETURN (1)
        END

    /*
    ** Parameter Check: @publication.
    ** Check to make sure that the publication exists and that it conforms
    ** to the rules for identifiers.
    */

    IF @publication IS NOT NULL
        BEGIN

            EXECUTE @retcode = sp_validname @publication

            IF @retcode <> 0
        RETURN (1)

            IF NOT EXISTS (SELECT * FROM syspublications WHERE name = @publication)
                BEGIN
                    RAISERROR (15001, 11, -1, @publication)
                    RETURN (1)
                END

        END

    SELECT @pubid = pubid FROM syspublications WHERE name = @publication

    IF @pubid IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The publication')
            RETURN (1)
        END

    /*
    ** Parameter Check:  @article
    ** Check to make sure that the article exists, is not NULL, and
    ** conforms to the rules for identifiers.
    */

    IF LOWER(@article) = 'all'
        /*
    ** Get all articles in the publication that are not subscribed to
    ** by the @subscriber
    */
        BEGIN

            IF EXISTS (SELECT *
                         FROM syspublications
                        WHERE name = @publication
                          AND restricted = 1)

                BEGIN

                    SELECT DISTINCT 'name' = a.name
                      INTO #tab1
                      FROM sysarticles a,
                           syspublications b,
                           syssubscriptions c,
                           master..sysservers d
                     WHERE a.pubid = b.pubid
                       AND b.name = @publication
                       AND a.artid = c.artid
                       AND c.srvid = d.srvid
               AND c.status = @inactive
                       AND d.srvname = @@REMSERVER

                    CREATE UNIQUE INDEX idx1 ON #tab1 (name)
                    SELECT @cmd = 'DECLARE hCx SCROLL CURSOR FOR SELECT name FROM #tab1'

                END

            ELSE

                BEGIN

                    SELECT DISTINCT 'name' = a.name
                      INTO #tab2
                      FROM sysarticles a, syspublications b
                     WHERE a.pubid = b.pubid
                       AND b.name = @publication
               AND NOT EXISTS (SELECT * from syssubscriptions s
                  WHERE s.artid = a.artid
                 AND s.srvid = @srvid)

                    CREATE UNIQUE INDEX idx1 ON #tab2 (name)
                    SELECT @cmd = 'DECLARE hCx SCROLL CURSOR FOR SELECT name FROM #tab2'

                END

            /*
            SELECT @cmd = ''
            SELECT @cmd = @cmd + 'DECLARE hCx SCROLL CURSOR FOR '
            SELECT @cmd = @cmd + ' SELECT DISTINCT a.name '
            SELECT @cmd = @cmd + '   FROM sysarticles a, syspublications b '
            SELECT @cmd = @cmd + '  WHERE a.pubid = b.pubid '
            SELECT @cmd = @cmd + '    AND b.name = ''' + @publication + ''''
            */

            EXECUTE (@cmd)
            OPEN hCx
            FETCH hCx INTO @article
            WHILE (@@fetch_status <> -1)
                BEGIN
                    EXECUTE sp_subscribe @publication    = @publication,
                                         @article        = @article,
                                         @destination_db = @destination_db,
                                         @sync_type      = @sync_type
                    FETCH hCx INTO @article
                END
            CLOSE hCx
            DEALLOCATE hCx
            RETURN (0)
        END

    IF @article IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The article')
            RETURN (1)
        END

    SELECT @artid = artid
      FROM sysarticles
     WHERE name = @article
       AND pubid = @pubid

    EXECUTE @retcode = sp_validname @article
    IF @retcode <> 0
    RETURN (1)

    IF NOT EXISTS (SELECT *
                     FROM sysarticles
                    WHERE artid = @artid
                      AND pubid = @pubid)
        BEGIN
            RAISERROR (15001, 11, -1, @article)
            RETURN (1)
        END

    IF @artid IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The article')
            RETURN (1)
        END

    /*
    ** Parameter Check: @sync_type.
    ** Set sync_typeid based on the @sync_type specified.
    **
    **   sync_typeid     sync_type
    **   ===========     =========
    **             0     manual
    **             1     automatic
    **             2     none
    */

    IF LOWER(@sync_type) NOT IN ('automatic', 'manual', 'none')
        BEGIN
            RAISERROR (14052, 16, -1)
            RETURN (1)
        END

    IF LOWER(@sync_type) IN ('automatic')
        SELECT @sync_typeid = @automatic
    ELSE IF LOWER(@sync_type) IN ('manual')
        SELECT @sync_typeid = @manual
    ELSE
        SELECT @sync_typeid = @none

    /*
    ** If it's an automatic sync, ensure that none of the articles
    ** being subscribed to have the 'owner qualified' status bit set
    */

    IF @sync_typeid = @automatic
    BEGIN
        IF EXISTS ( SELECT * FROM sysarticles
                    WHERE artid = @artid AND pubid = @pubid AND
                    4 = (status & 4) )
        BEGIN
            RAISERROR( 14098, 16, -1 )
            RETURN (1)
        END

    END


    /*
    ** Parameter Check: @destination_db.
    ** Set @destination_db to current database if not specified.  Make
    ** sure that the @destination_db conforms to the rules for identifiers.
    */

    IF @destination_db IS NULL SELECT @destination_db = DB_NAME()

    EXECUTE @retcode = sp_validname @destination_db

    IF @retcode <> 0
    RETURN (1)

    /*
    ** Bug 12850:
    ** Make sure that the creation_script is specified if pre_creation_cmd is "drop"
    ** Note that at this point, @article cannot be all.
    */

    if exists (select * from sysarticles where
            name = @article AND
            pre_creation_cmd = 1 AND
            creation_script is null)
        BEGIN
            RAISERROR (14096, 16, -1)
            RETURN (1)
        END


    BEGIN TRAN subscribe

        /*
        ** If 'public' publication, add the subscription.  Anyone can subscribe
        ** to a 'public' publication without intervention from the SA or DBO
        ** of the publishing server.
        */

        IF EXISTS (SELECT *
                 FROM syspublications
                WHERE pubid = @pubid
                  AND restricted = 0)
            BEGIN

                /*
                ** If the subscription already exists, don't add it.
                */

                IF EXISTS (SELECT *
                               FROM syssubscriptions
                            WHERE artid = @artid
                              AND srvid = @srvid)
                    BEGIN
                        ROLLBACK TRAN subscribe
                        RAISERROR (14058, 16, -1)
                        RETURN (1)
                    END

                /*
                ** The subscription doesn't exist, so let's add it to
                ** syssubscriptions.
                */

                INSERT syssubscriptions VALUES (@artid,
                                    @srvid,
                                    @destination_db,
                                    @inactive,
                                    @sync_typeid,
                                    NULL)

                IF @@ERROR <> 0
                BEGIN
                ROLLBACK TRAN subsribe
                        RAISERROR (14057, 16, -1)
                RETURN (1)
                END

            END

        /*
        ** If 'restricted' publication, update the subscription.  A restricted
        ** subscription must already exist in syssubscriptions.  It must be
        ** created by the SA or DBO on the publishing server using
        ** sp_addsubscription.  All we need to do is set the status to
        ** 'subscribe'.
        */

        ELSE
            BEGIN

            /*
            ** First, make sure that the SA or DBO on the publishing
            ** server has created the subscription on the restricted
            ** publication.
            */

            IF NOT EXISTS (SELECT *
                     FROM syssubscriptions sub,
                                      syspublications pub,
                          sysarticles art
                    WHERE art.artid = @artid
                      AND pub.pubid = @pubid
                      AND sub.srvid = @srvid
                      AND sub.artid = art.artid
                      AND pub.restricted = @restricted)
                      /* AND sub.sync_type = @sync_typeid) */
                BEGIN
                ROLLBACK TRAN subscribe
                        RAISERROR (14072, 16, -1)
                RETURN (1)
                END

            /*
            ** A subscription exists for the restricted publication.
            */

            UPDATE syssubscriptions
               SET dest_db   = @destination_db,
                   sync_type = @sync_typeid
             WHERE srvid = @srvid
               AND artid = @artid

            IF @@ERROR <> 0
                BEGIN
                    ROLLBACK TRAN subscribe
                        RAISERROR (14057, 16, -1)
                RETURN (1)
                END
            END

        /*
        ** If a sync is required then update the subscription status to
    ** 'subscribed'. Else, set the subscription status to 'active'.
        */
        if @sync_typeid = @none
        EXEC @retcode = sp_changesubstatus
            @publication = @publication,
                        @article     = @article,
                        @subscriber  = @@REMSERVER,
                        @status      = 'active'
    else
        EXEC @retcode = sp_changesubstatus
            @publication = @publication,
                        @article     = @article,
                        @subscriber  = @@REMSERVER,
                        @status      = 'subscribed'

        IF @@ERROR <> 0 OR @retcode <> 0
            BEGIN
               ROLLBACK TRAN subscribe
                   RAISERROR (14057, 16, -1)
               RETURN (1)
            END

    COMMIT TRAN subscribe
go

print ''
print 'Creating procedure sp_MSuninstall_publishing.'
go
CREATE PROCEDURE sp_MSuninstall_publishing

AS

    DECLARE @cmd varchar(255)
    DECLARE @name varchar(30)
    DECLARE @distname varchar(30)
    DECLARE @distdb varchar(30)
    DECLARE @proc varchar(255)

    /*
    ** Unpublish all published databases.
    */
        SELECT @cmd = ''
        SELECT @cmd = @cmd + 'DECLARE hC CURSOR FOR '
        SELECT @cmd = @cmd + 'SELECT name FROM master..sysdatabases '
        SELECT @cmd = @cmd + 'WHERE (category & 1) <> 0'
        EXECUTE (@cmd)
        OPEN hC
        FETCH hC INTO @name
        WHILE (@@fetch_status <> -1)
            BEGIN
            SELECT @name
                EXECUTE sp_dboption @name, published, FALSE
                FETCH hC INTO @name
            END
        CLOSE hC
        DEALLOCATE hC

    /*
    ** Drop all subscribers
    */
        SELECT @cmd = ''
        SELECT @cmd = @cmd + 'DECLARE hC CURSOR FOR '
        SELECT @cmd = @cmd + 'SELECT srvname FROM master..sysservers '
        SELECT @cmd = @cmd + 'WHERE (srvstatus & 4) <> 0'
        EXECUTE (@cmd)
        OPEN hC
        FETCH hC INTO @name
        WHILE (@@fetch_status <> -1)
            BEGIN
                EXECUTE sp_dropsubscriber @name
                FETCH hC INTO @name
            END
        CLOSE hC
        DEALLOCATE hC

    /*
    ** Remove the publish option on the server
    */
    EXECUTE sp_serveroption @@SERVERNAME, pub, FALSE

    /*
    ** Remove the publish option on the server
    */
    IF @distname IS NOT NULL
        EXECUTE sp_serveroption @@SERVERNAME, pub, FALSE

    /*
    ** Get the distribution server name
    */
        SELECT @distname = srvname FROM master..sysservers
        WHERE (srvstatus & 8) <> 0

    /*
    ** If this is a distribution server drop the distribution database
    ** and remove distribution registry entry
    */
    IF @distname = @@SERVERNAME
    BEGIN
        /*
        ** If there are distribution publishers, do not drop the
        ** distribution database
        */
        IF EXISTS (SELECT * FROM master..sysservers where
            (srvstatus & 16) <> 0)
        BEGIN
           RETURN (0)
        END

        /*
        ** Get the distribution database name from the Registry
        */
        SELECT @proc = 'master..xp_regread '
        EXECUTE @proc 'HKEY_LOCAL_MACHINE',
              'SOFTWARE\Microsoft\MSSQLServer\Replication',
              'DistributionDB',
            @param = @distdb OUTPUT

        /*
        ** Drop the distribution database
        */
        EXECUTE ('DROP DATABASE ' + @distdb)

        /*
        ** Clear the distribution registry values
        */
        EXECUTE master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
                                   'SOFTWARE\Microsoft\MSSQLServer\Replication',
                                   'DistributionDB',REG_SZ, ''
    END

    /*
    ** Remove the distribution option on the server
    */
    IF @distname IS NOT NULL
        EXECUTE sp_serveroption @distname, dist, FALSE

       /*
       ** Drop all remote servers that have the DSN option set
       */
        SELECT @cmd = ''
        SELECT @cmd = @cmd + 'DECLARE hC CURSOR FOR '
        SELECT @cmd = @cmd + 'SELECT srvname FROM master..sysservers '
        SELECT @cmd = @cmd + 'WHERE (srvstatus & 32) <> 0'
        EXECUTE (@cmd)
        OPEN hC
        FETCH hC INTO @name
        WHILE (@@fetch_status <> -1)
            BEGIN
                EXECUTE sp_dropserver @name
                FETCH hC INTO @name
            END
        CLOSE hC
        DEALLOCATE hC
GO

print ''
print 'Creating procedure sp_unsubscribe.'
go
CREATE PROCEDURE sp_unsubscribe (
    @publication varchar(30) = NULL,       /* publication name */
    @article varchar(30) = NULL            /* article name */
    ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    DECLARE @cmd varchar(255)
    DECLARE @pubid int
    DECLARE @artid int
    DECLARE @public tinyint
    DECLARE @srvid smallint
    DECLARE @subscriber_bit smallint
    DECLARE @retcode int
    DECLARE @active tinyint

    /*
    ** Security Check
    ** Only the System Administratr (SA) or the Database Owner (dbo) or
    ** Replication subscriber (repl_subscriber) can unsubscribe from an article.
    */

    IF suser_id() <> 1 AND user_id() <> 1 AND user_id() <> 16383
        BEGIN
            RAISERROR (14093, 14, -1)
            RETURN (1)
        END

    /*
    ** Initializations.
    */

    SELECT @public = 0          /* Const: security = 'public'. */
    SELECT @subscriber_bit = 4  /* Const: subscription server status */
    SELECT @active = 2          /* Const: subscription status 'active' */

    /*
    ** Check to make sure that this procedure is not being executed on the
    ** publisher (locally).
    */

    IF @@REMSERVER IS NULL
        BEGIN
            RAISERROR (14073, 16, -1)
            RETURN (1)
        END

    /*
    ** Check if the server exists and that it is a subscription server.
    */

    SELECT @srvid = srvid
                 FROM sysservers
                    WHERE srvname = @@REMSERVER
                      AND (srvstatus & @subscriber_bit) <> 0
    IF @srvid IS NULL
        BEGIN
            RAISERROR (14010, 16, -1)
        RETURN (1)
        END

    /*
    ** If the @publication is 'all' the user wants to cancel all
    ** @publication @article subscriptions.
    */

    IF LOWER(@publication) = 'all'
        BEGIN

            SELECT DISTINCT a.name
              INTO #unsubscribe1
              FROM syspublications a,
                   sysarticles b,
                   syssubscriptions c
             WHERE c.srvid = @srvid
               AND c.artid = b.artid
               AND b.pubid = a.pubid

            CREATE UNIQUE INDEX idx1 ON #unsubscribe1 (name)

            SELECT @cmd = 'DECLARE hC1 SCROLL CURSOR FOR SELECT * FROM #unsubscribe1'
            EXECUTE (@cmd)

            OPEN hC1
            FETCH hC1 INTO @publication

            WHILE (@@fetch_status <> -1)
                BEGIN
                    EXECUTE sp_unsubscribe @publication, 'all'
                    FETCH hC1 INTO @publication
                END
            CLOSE hC1
            DEALLOCATE hC1
            RETURN (0)
        END

    /*
    ** Parameter Check:  @publication.
    ** Check to make sure that the publication exists.
    */

    IF @publication IS NOT NULL
        BEGIN

            EXECUTE @retcode = sp_validname @publication

            IF @retcode <> 0
        RETURN (1)

            IF NOT EXISTS (SELECT * FROM syspublications WHERE name = @publication)
                BEGIN
                    RAISERROR (15001, 11, -1, @publication)
                    RETURN (1)
                END

        END

    SELECT @pubid = pubid FROM syspublications WHERE name = @publication

    IF @pubid IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The publication')
            RETURN (1)
        END

    /*
    ** If the @article is 'all', the user wants to cancel all subscriptions
    ** for the publication.
    */

    IF LOWER(@article) = 'all'
        BEGIN

            SELECT a.name
              INTO #unsubscribe2
              FROM sysarticles a,
                   syssubscriptions b
             WHERE a.pubid = @pubid
               AND b.srvid = @srvid
               AND b.artid = a.artid

            CREATE UNIQUE INDEX idx1 ON #unsubscribe2 (name)

            SELECT @cmd = 'DECLARE hC2 SCROLL CURSOR FOR SELECT * FROM #unsubscribe2'
            EXECUTE (@cmd)

            OPEN hC2
            FETCH hC2 INTO @article

            WHILE (@@fetch_status <> -1)
                BEGIN
                    EXECUTE sp_unsubscribe @publication, @article
                    FETCH hC2 INTO @article
                END
            CLOSE hC2
            DEALLOCATE hC2
            RETURN (0)
        END

    /*
    ** Parameter Check:  @article.
    ** Check to make sure that the article exists.
    */

    SELECT @artid = artid
      FROM sysarticles
     WHERE name = @article
       AND pubid = @pubid

    IF @article IS NOT NULL
        BEGIN
            EXECUTE @retcode = sp_validname @article
            IF @retcode <> 0
        RETURN (1)

        IF NOT EXISTS (SELECT *
                             FROM sysarticles
                            WHERE artid = @artid
                              AND pubid = @pubid)
                BEGIN
                    RAISERROR (15001, 11, -1, @article)
                    RETURN (1)
                END
        END

    IF @artid IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, 'The article')
            RETURN (1)
        END

    IF NOT EXISTS (SELECT *
                     FROM syssubscriptions a,
                          sysarticles b,
                          syspublications c
                    WHERE a.srvid = @srvid
                      AND a.artid = b.artid
                      AND b.pubid = c.pubid
                      AND c.pubid = @pubid
                      AND a.status <> 0)
        BEGIN
            RAISERROR (14050, 11, -1)
            RETURN (1)
        END

    BEGIN TRAN unsubscribe

        /*
        ** Change the status of the subscription to 'inactive', then delete
        ** subscription row.  If the subscription is a public subscription,
        ** it can safely be deleted.
        */

        /*
        ** Change the status of the subscription to 'inactive'.
        */

        EXECUTE @retcode  = sp_changesubstatus @publication = @publication,
                                   @article     = @article,
                                               @subscriber  = @@REMSERVER,
                                               @status      = 'inactive'

        IF @@ERROR <> 0 OR @retcode <> 0
            BEGIN
                ROLLBACK TRAN unsubscribe
                RAISERROR (14056, 16, -1)
                RETURN (1)
            END

        /*
        ** If the publication is 'public', delete the subscription.
        */

        IF EXISTS (SELECT *
                     FROM syspublications
                    WHERE pubid = @pubid
                      AND restricted = @public)
            BEGIN

                DELETE syssubscriptions
                 WHERE srvid = @srvid
                   AND artid = @artid

            IF @@ERROR <> 0
                BEGIN
                    ROLLBACK TRAN unsubscribe
                        RAISERROR (14056, 16, -1)
                    RETURN (1)
                END
            END

    COMMIT TRAN unsubscribe

go

print ''
print 'Creating procedure sp_create_distribution_tables.'
go
CREATE PROCEDURE sp_create_distribution_tables
AS

BEGIN
   IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'MSjobs' and type = 'U')
   BEGIN
      /****************************************************************************/
      PRINT ''
      PRINT 'Creating Table:  dbo.MSjobs'
      PRINT ''
      /****************************************************************************/
      CREATE TABLE MSjobs
      (
      publisher_id smallint,
      publisher_db varchar (  30 ) ,
      job_id int,
      type tinyint, /* 0- sql cmd 1-noop cmd 2-sql script 3/4-bcp cmd 5-manual sync*/
      xactid_page int,
      xactid_row smallint,
      xactid_ts binary (  8 ) ,
      entry_time datetime
      )

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

      PRINT ''
      PRINT 'Creating Clustered Index: ucMSjobs'
      PRINT ''
      CREATE UNIQUE CLUSTERED INDEX ucMSjobs ON dbo.MSjobs
         (publisher_db, publisher_id, job_id)
   END

   IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'MSjob_commands' and type = 'U')
   BEGIN
      /****************************************************************************/
      PRINT ''
      PRINT 'Creating Table:  dbo.MSjob_commands'
      PRINT ''
      /****************************************************************************/
      CREATE TABLE MSjob_commands (
      publisher_id smallint,
      publisher_db varchar (  30 ) ,
      job_id int,
      command_id int,
      art_id int,
      incomplete bit,
      command varchar (  255 ) NULL
      )
      /* Set category bit to reflect MS objects */
      UPDATE sysobjects SET category = category | 2 WHERE sysstat & 0xf = 3 AND
         name = 'MSjob_commands'

      PRINT ''
      PRINT 'Creating Clustered Index: ucMSjob_commands'
      PRINT ''
      CREATE UNIQUE CLUSTERED INDEX ucMSjob_commands ON dbo.MSjob_commands
         (publisher_db, publisher_id, job_id, command_id)
   END

   IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'MSsubscriber_jobs' and type = 'U')
   BEGIN
      /****************************************************************************/
      PRINT ''
      PRINT 'Creating Table:  dbo.MSsubscriber_jobs'
      PRINT ''
      /****************************************************************************/
      CREATE TABLE MSsubscriber_jobs
      (
      publisher_id smallint,
      publisher_db varchar (30),
      job_id int,
      subscriber_id smallint,
      subscriber_db varchar (  30 ) ,
      command_id int
      )

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

      PRINT ''
      PRINT 'Creating Clusteredd Index: ucMSsubscriber_jobs'
      PRINT ''
      CREATE UNIQUE CLUSTERED INDEX ucMSsubscriber_jobs ON dbo.MSsubscriber_jobs
         (publisher_db, publisher_id, subscriber_db, subscriber_id, job_id, command_id)

      PRINT ''
      PRINT 'Creating NonClustered Index: ncMSsubscriber_jobs'
      PRINT ''
      CREATE NONCLUSTERED INDEX ncMSsubscriber_jobs ON dbo.MSsubscriber_jobs
         (publisher_db, publisher_id, job_id)
   END


   IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'MSsubscriber_status' and type = 'U')
   BEGIN
      /****************************************************************************/
      PRINT ''
      PRINT 'Creating Table:  dbo.MSsubscriber_status'
      PRINT ''
      /****************************************************************************/
      CREATE TABLE MSsubscriber_status
      (
      publisher_id smallint,
      publisher_db varchar (  30 ) ,
      job_id int,
      subscriber_id smallint,
      subscriber_db varchar (  30 ) ,
      completion_time datetime,
      delivery_latency int,
      delivered_jobs int,
      delivery_rate int,
      status int
      )

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

      PRINT ''
      PRINT 'Creating Clustered Index: ucMSsubscriber_status'
      PRINT ''
      CREATE UNIQUE CLUSTERED INDEX ucMSsubscriber_status on dbo.MSsubscriber_status
       (publisher_db, publisher_id, subscriber_db, subscriber_id, job_id)

   END

   IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'MSsubscriber_info' and type = 'U')
   BEGIN
      /****************************************************************************/
      PRINT ''
      PRINT 'Creating Table:  dbo.MSsubscriber_info'
      PRINT ''
      /****************************************************************************/
      CREATE TABLE MSsubscriber_info
      (
      publisher  varchar (30),
      subscriber  varchar (30),
      type tinyint,           /* 0: MS SQL Server 1: ODBC Data Source */
      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,
      retrydelay int,
      description varchar(255) NULL,
      )

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

      PRINT ''
      PRINT 'Creating Clustered Index: ucMSsubscriber_info'
      PRINT ''
      CREATE UNIQUE CLUSTERED INDEX ucMSsubscriber_info ON dbo.MSsubscriber_info
     (publisher, subscriber)
   END
   ELSE
   BEGIN
      IF NOT EXISTS (select * from syscolumns
        where name = 'description'
        and id=object_id('MSsubscriber_info'))
      BEGIN
         /****************************************************************************/
         PRINT ''
         PRINT 'Alter Table:  dbo.MSsubscriber_info'
         PRINT ''
         /****************************************************************************/
         ALTER TABLE MSsubscriber_info ADD description varchar (255) NULL
     UPDATE MSsubscriber_info SET description = 'SQL Server 6.0'
      END
   END

   IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'MSjob_subscriptions' and type = 'U')
   BEGIN
      /****************************************************************************/
      PRINT ''
      PRINT 'Creating Table:  dbo.MSjob_subscriptions'
      PRINT ''
      /****************************************************************************/
      CREATE TABLE MSjob_subscriptions
      (
      publisher varchar ( 30 ),
      publisher_id smallint,
      publisher_db varchar (  30 ) ,
      subscriber varchar ( 30 ),
      subscriber_id smallint,
      art_id int,
      subscriber_db varchar (  30 ) ,
      status tinyint,
      ts binary (8)
      )

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

      PRINT ''
      PRINT 'Creating Clustered Index: ucMSsubscriptions'
      PRINT ''
      CREATE UNIQUE CLUSTERED INDEX ucMSsubscriptions ON dbo.MSjob_subscriptions
     (publisher_db, publisher_id, art_id, subscriber_id)
   END
END
GO



if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_repldone')
    exec sp_dropextendedproc 'sp_repldone'
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_repltrans')
    exec sp_dropextendedproc 'sp_repltrans'
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_replcmds')
    exec sp_dropextendedproc 'sp_replcmds'
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_replcounters')
    exec sp_dropextendedproc 'sp_replcounters'
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_replflush')
    exec sp_dropextendedproc 'sp_replflush'
go

if exists (select * from sysobjects
        where sysstat & 0xf = 4
            and name = 'sp_replstatus')
    exec sp_dropextendedproc 'sp_replstatus'
go

/*
** Add extended stored procedures for replication support.
*/
sp_addextendedproc  'sp_repldone', 'repldone extended procedure'
go

sp_addextendedproc  'sp_repltrans', 'repltrans extended procedure'
go

sp_addextendedproc  'sp_replcounters', 'replcounters extended procedure'
go

sp_addextendedproc  'sp_replcmds', 'replcmds extended procedure'
go

sp_addextendedproc  'sp_replflush', 'replflush extended procedure'
go

sp_addextendedproc  'sp_replstatus', 'replstatus extended procedure'
go

print ''
print 'Adding logins and users for replication.'
print ''
go
/*
**  Add the login and user named 'repl_subscriber'.  These are used when a
**  replication publisher.  Use the maximum user id for the login id.
*/
if not exists (select * from syslogins where name = 'repl_subscriber')
    insert into syslogins (suid,status,accdate,totcpu,totio,spacelimit,
            timelimit,resultlimit,dbname,name,password,language)
    values (16383, 9, getdate(), 0, 0, 0, 0, 0,'master','repl_subscriber',
            pwdencrypt(convert(char(30),@@dbts)),NULL)
go

if not exists (select * from sysusers where name = 'repl_subscriber')
    exec sp_adduser 'repl_subscriber'
go

/*
**  Add the login and user named 'repl_publisher'.  This is used when a
**  replication subscriber.
*/
if not exists (select * from syslogins where name = 'repl_publisher')
    insert into syslogins (suid,status,accdate,totcpu,totio,spacelimit,
            timelimit,resultlimit,dbname,name,password,language)
    values (16382, 9, getdate(), 0, 0, 0, 0, 0,'master','repl_publisher',
            pwdencrypt(convert(char(30),@@dbts)),NULL)
go

/*
** Add xp_enum_dsn extended procedure
*/
sp_addextendedproc 'xp_dsninfo','xpsql60.dll'
go

/*
** Add xp_enum_dsn extended procedure
*/
sp_addextendedproc 'xp_enumdsn','xpsql60.dll'
go

grant execute on sp_addarticle to public
go
grant execute on sp_addpublication to public
go
grant execute on sp_addsubscription to public
go
grant execute on sp_articlecolumn to public
go
grant execute on sp_articlefilter to public
go
grant execute on sp_articletextcol to public
go
grant execute on sp_articleview to public
go
grant execute on sp_changearticle to public
go
grant execute on sp_changepublication to public
go
grant execute on sp_changesubscription to public
go
grant execute on sp_distcounters to public
go
grant execute on sp_droparticle to public
go
grant execute on sp_droppublication to public
go
grant execute on sp_dropsubscription to public
go
grant execute on sp_helparticle to public
go
grant execute on sp_helparticlecolumns to public
go
grant execute on sp_helpdistributor to public
go
grant execute on sp_helppublication to public
go
grant execute on sp_helppublicationsync to public
go
grant execute on sp_helpreplicationdb to public
go
grant execute on sp_helpsubscription to public
go
grant execute on sp_helpsubscriberinfo to public
go
grant execute on sp_replcounters to probe
go
grant execute on sp_replstatus to public
go
grant execute on sp_subscribe to public
go
grant execute on sp_textcolstatus to public
go
grant execute on sp_unsubscribe to public
go

sp_configure 'allow updates',0
go
reconfigure with override
go

print ''
print 'Checking objects created by instrepl.sql.'
go

--obsolete   exec sp_check_objects 'repl'
exec sp_MS_upd_sysobj_category 2  --set sysobjects.category | 2 based on crdate.
go

print ''
print 'instrepl.sql completed successfully.'
go

dump tran master with no_log
go
checkpoint
go
-- - -----