use master
go

dump tran master with no_log
go

exec dbo.sp_configure 'allow updates',1
go
reconfigure with override
go

set ANSI_NULLS off

exec sp_MS_upd_sysobj_category 1
go

--------------------------------------------------------------------------------
--.	System objects (replsys.sql)
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--. sp_MSispulldistributionjobnamegenerated
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSispulldistributionjobnamegenerated')
drop procedure sp_MSispulldistributionjobnamegenerated
go

raiserror('Creating procedure sp_MSispulldistributionjobnamegenerated', 0,1) with nowait
go
--
-- Name: sp_MSispulldistributionjobnamegenerated
--
-- Descriptions: This procedure checks whether a given job id corresponds to
--               a pull distribution agent job with a name generated by replication. 
--               If so, a return value of zero will be returned, otherwise
--               a value of one will be returned to the caller.
-- 
-- Notes: 1) For this procedure to be effective, it must match the current
--           pull distribution agent name generation algorithm. Note that the 
--           algorithm used for matching auto-generated job name is not 
--           exact. (See sp_addpullsubscription_agent replsys.sql)
--        2) This procedure is supposed to be executed at the subscriber
--           database. The subscriber server name and subscriber database
--           name for contructing the generated job name are obtained via
--           the serverproperty('ServerName') and the db_name() functions. 
--
-- Parameters: @publisher     sysname
--             @publisher_db  sysname
--             @publication   sysname (This can be null for shared agent)
--             @job_id        uniqueidentifier
--
-- Returns: 0 - If the specified jobid corresponds to a snapshot job with
--              a generated name.
--          1 - Otherwise
--
-- Security: This is an internal system procedure.
--
create procedure dbo.sp_MSispulldistributionjobnamegenerated (
    @publisher     sysname,
    @publisher_db  sysname,
    @publication   sysname,
    @job_id        uniqueidentifier
    )
as
begin
    set nocount on

    declare @generated_job_name nvarchar(4000)
    declare @generated_job_name_length int
    declare @job_name sysname

    select @job_name = null
    select @job_name = name
      from msdb.dbo.sysjobs_view 
     where job_id = @job_id

    if @job_name is null
    begin
        goto MISMATCH
    end

    -- First of all, make sure that the job is indeed a distribution agent
    -- job (there should be a step with Distribution as the subsystem in the 
    -- job)
    if not exists (select * 
                     from msdb.dbo.sysjobsteps
                    where job_id = @job_id
                      and upper(subsystem collate SQL_Latin1_General_CP1_CS_AS) = N'DISTRIBUTION')
    begin
        goto MISMATCH
    end
                    
    -- Pull distribution agent names are always generated with a guid
    select @generated_job_name = left(@publisher, 21) + N'-'
                               + left(@publisher_db, 21) + N'-'
                               + left(@publication, 21) + N'-'
                               + left(convert(nvarchar(4000),serverproperty('ServerName')), 21) + N'-'
                               + left(db_name(), 21) + N'-'

    select @generated_job_name_length = len(@generated_job_name)

    -- Try matching the prefix of the job name
    if left(@job_name, @generated_job_name_length) = @generated_job_name
    begin
        -- Checks if the tail end of the job name matches a hexadecimal guid 
        -- with embedded dashes
        if upper(right(@job_name, len(@job_name) - @generated_job_name_length) collate SQL_Latin1_General_CP1_CS_AS)
            like N'[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]'
        begin
            return 0
        end
        else
        begin
            goto MISMATCH
        end
    end

MISMATCH:
    if @job_name is not null
    begin
        raiserror(21695, -1, -1, @job_name)
    end
    return 1
end
go
exec sp_MS_marksystemobject 'dbo.sp_MSispulldistributionjobnamegenerated'

--------------------------------------------------------------------------------
--. sp_MSispullmergejobnmaegenerated
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSispullmergejobnamegenerated')
drop procedure sp_MSispullmergejobnamegenerated
go

raiserror('Creating procedure sp_MSispullmergejobnamegenerated', 0,1) with nowait
go
--
-- Name: sp_MSispullmergejobnamegenerated
--
-- Descriptions: This procedure checks whether a given job id corresponds to
--               a pull merge agent job with a name generated by replication. 
--               If so, a return value of zero will be returned, otherwise
--               a value of one will be returned to the caller.
-- 
-- Notes: 1) For this procedure to be effective, it must match the current
--           pull merge agent name generation algorithm. Note that the 
--           algorithm used for matching auto-generated job name is not 
--           exact. (See sp_addmergepullsubscription_agent replsys.sql)
--        2) This procedure is supposed to be executed at the subscriber
--           database. The subscriber server name and subscriber database
--           name for contructing the generated job name are obtained via
--           the serverproperty('ServerName') and the db_name() functions. 
--
-- Parameters: @publisher     sysname
--             @publisher_db  sysname
--             @publication   sysname (This can be null for shared agent)
--             @job_id        uniqueidentifier
--
-- Returns: 0 - If the specified jobid corresponds to a snapshot job with
--              a generated name.
--          1 - Otherwise
--
-- Security: This is an internal system procedure.
--
create procedure dbo.sp_MSispullmergejobnamegenerated (
    @publisher    sysname,
    @publisher_db sysname,
    @publication  sysname,
    @job_id       uniqueidentifier
    )
as
begin
    set nocount on
    
    declare @generated_job_name nvarchar(4000)
    declare @generated_job_name_length int
    declare @job_name sysname

    select @job_name = null
    select @job_name = name
      from msdb.dbo.sysjobs_view
     where job_id = @job_id

    if @job_name = null
    begin
        goto MISMATCH
    end
    
    -- First of all, make sure that the given job is indeed a merge agent
    -- job (There should be a step with Distribution as the subsystem in the 
    -- job)
    if not exists (select * 
                     from msdb.dbo.sysjobsteps
                    where job_id = @job_id
                      and upper(subsystem collate SQL_Latin1_General_CP1_CS_AS) = N'MERGE')
    begin
        goto MISMATCH
    end
    
    -- Pull merge agent names are always generated with a - 0 extension
    select @generated_job_name = left(@publisher, 23) + N'-'
                               + left(@publisher_db, 23) + N'-'
                               + left(@publication, 23) + N'-'
                               + left(convert(nvarchar(4000),serverproperty('ServerName')), 23) + N'-'
                               + left(db_name(), 23) + N'- 0'

    if @job_name = @generated_job_name
    begin
        return 0
    end    

MISMATCH:
    if @job_name is not null
    begin
        raiserror(21695, -1, -1, @job_name)
    end
    return 1
end
go
exec sp_MS_marksystemobject 'dbo.sp_MSispullmergejobnamegenerated'

--------------------------------------------------------------------------------
--. sp_droppullsubscription
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_droppullsubscription')
drop procedure sp_droppullsubscription
go

raiserror('Creating procedure sp_droppullsubscription', 0,1)
go

CREATE PROCEDURE sp_droppullsubscription (
    @publisher sysname,
    @publisher_db sysname,
    @publication sysname,   /* publication name */
    @reserved bit = 0
    ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */


    DECLARE @name nvarchar(255)
    DECLARE @retcode int
    DECLARE @agent_id binary(16)
    DECLARE @publisher_ex sysname          /* Expression used in the cursor */
    DECLARE @publisher_db_ex sysname     /* Expression used in the cursor */
    DECLARE @publication_ex sysname      /* Expression used in the cursor */
    DECLARE @expanded bit
    DECLARE @subscription_type_id int
    DECLARE @count_sub int
    DECLARE @drop_null_pub bit
    DECLARE @drop_push_bit bit
    DECLARE @push int
    DECLARE @implicit_transaction int
    DECLARE @close_cursor_at_commit int
    DECLARE @owner_sid varbinary(85)
    DECLARE @owner_name sysname
    DECLARE @qualified_publication_name nvarchar(512)
    
    /* 
    ** Initialization
    */
    SELECT @expanded = 0
    SELECT @drop_null_pub = 0
    SELECT @push = 0

    /*
    ** Get the original set value off IMPLICIT_TRANSACTIONS and CURSOR_CLOSE_ON_COMMIT
    **      before set these two to off
    */
    select @implicit_transaction = 0
    select @close_cursor_at_commit = 0
    IF (@reserved = 0)
    BEGIN
        SELECT @implicit_transaction = @@options & 2
        SELECT @close_cursor_at_commit = @@options & 4
        SET IMPLICIT_TRANSACTIONS OFF
        SET CURSOR_CLOSE_ON_COMMIT OFF
    END

    /*
    ** Security Check
    */

    EXEC @retcode = dbo.sp_MSreplcheck_subscribe
    IF @@ERROR <> 0 or @retcode <> 0
        RETURN(1)

    SELECT @drop_push_bit = 0   
    
    /*
    ** Check parameter and set expressions used by cursor
    */
    
    /* Publisher */
    IF @publisher IS NULL
    BEGIN
        RAISERROR (14043, 16, -1, '@publisher')
        RETURN (1)
    END

    IF @publisher = 'all'
    BEGIN
        SELECT @publisher_ex = '%'
        SELECT @expanded = 1
    END
    ELSE
    BEGIN
        EXECUTE @retcode = dbo.sp_validname @publisher
        IF @retcode <> 0
        RETURN (1)
        SELECT @publisher_ex = @publisher
    END


    /* Publisher_db */
    IF @publisher_db IS NULL
    BEGIN
            RAISERROR (14043, 16, -1, '@publisher_db')
            RETURN (1)
    END

    IF @publisher_db = 'all'
    BEGIN
        SELECT @publisher_db_ex = '%'
        select @expanded = 1
    END
    ELSE
    BEGIN
        /*
        EXECUTE @retcode = dbo.sp_validname @publisher_db
        IF @retcode <> 0
        RETURN (1)
        */
        SELECT @publisher_db_ex = @publisher_db
    END

    /* 
    ** Publication
    ** '' is not a valid name but it may be in the publication name in the table. 
    */
    IF @publication IS NULL OR @publication = ''
    BEGIN
        SELECT @drop_null_pub = 1
    END
    ELSE IF @publication = 'all'
    BEGIN
        SELECT @publication_ex = '%'
        SELECT @expanded = 1
        SELECT @drop_null_pub = 1
    END
    ELSE
    BEGIN
        EXECUTE @retcode = dbo.sp_validname @publication
        IF @retcode <> 0
        RETURN (1)
        SELECT @publication_ex = @publication
    END

    /*
    ** Check to see if the  subscription table exists
    */
    IF NOT EXISTS (SELECT * FROM sysobjects 
                WHERE type = 'U' AND
                      name = 'MSreplication_subscriptions')
    BEGIN
        IF @expanded = 0
        BEGIN
            RAISERROR(14135, 11, -1,  @publisher, 
                @publisher_db, @publication)
            RETURN(1)
        END
        ELSE
            RETURN(0)
    END

    IF @expanded = 0 
    BEGIN 
        /*
        **
        ** Check to see if the subscription entry exists
        */
        IF  NOT EXISTS (SELECT * FROM  MSreplication_subscriptions 
                    WHERE UPPER(publisher) = UPPER(@publisher) AND
                      publisher_db = @publisher_db AND
                      publication = @publication)     
        BEGIN
            RAISERROR(14135, 11, -1, @publisher, @publisher_db, @publication)
            RETURN(1)
        END

        /* 
        ** Make sure the subscription is not push type if @drop_push_bit = 0
        */
        IF @drop_push_bit = 0
        BEGIN
            IF EXISTS (SELECT * FROM MSreplication_subscriptions 
                WHERE UPPER(publisher) = UPPER(@publisher) AND
                  publisher_db = @publisher_db AND
                  publication = @publication AND
                  subscription_type = @push AND
                  @drop_push_bit = 0)
            BEGIN
                RAISERROR(20017, 16, -1)
                RETURN(1)
            END
        END

    END
    ELSE
    /* 
    ** Open a cursor and call recursively if
    ** parameters are expanded.
    */
    BEGIN

        /*      
        **  Note: Any expression check on null value is false
        ** @subscription_type_id is NULL <==> push
        ** @subscription_type_id is NOT NULL <==> non push
        */
        -- Have to use static cursor option because 
        DECLARE hCdroppullsubscription CURSOR STATIC LOCAL FORWARD_ONLY FOR
            SELECT DISTINCT publisher, publisher_db, publication    
                FROM MSreplication_subscriptions
                WHERE ((@publisher_ex = N'%') OR (UPPER(publisher) = UPPER(@publisher_ex))) AND
                                          ((@publisher_db_ex = N'%') OR ( publisher_db = @publisher_db_ex)) AND
                      (publication LIKE @publication_ex OR
                      (@drop_null_pub = 1 AND
                      publication IS NULL)) AND
                      ((@drop_push_bit =0 AND
                      subscription_type <> @push) OR
                      @drop_push_bit = 1)
        FOR READ ONLY
        OPEN hCdroppullsubscription

        FETCH hCdroppullsubscription INTO @publisher, 
            @publisher_db, @publication
        WHILE (@@fetch_status <> -1)
        BEGIN
            EXECUTE @retcode  = dbo.sp_droppullsubscription @publisher = @publisher, 
                @publisher_db = @publisher_db, @publication = @publication, 
                @reserved = 1

            FETCH hCdroppullsubscription INTO @publisher, 
                @publisher_db, @publication
        END

        CLOSE hCdroppullsubscription
        DEALLOCATE hCdroppullsubscription
        RETURN (0)
    END
    
    /*
    ** Only members of the sysadmin group and the creator of the distribution
    ** agent can drop a pull subscription successfully. This behavior matches 
    ** the behavior of the sysjobs_view. DBO of the subscriber database, 
    ** sysadmins (owner is undefined) can drop a subscription if the owner_sid 
    ** is null.
    */

    EXEC sp_MSget_pullsubsagent_owner @publisher = @publisher,
                                      @publisher_db = @publisher_db,
                                      @publication = @publication,
                                      @owner_sid = @owner_sid OUTPUT

    IF (@owner_sid is not null AND
        (SUSER_SID() <> @owner_sid) AND
        (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0))
    BEGIN
        SELECT @owner_name = SUSER_SNAME(@owner_sid)
        SELECT @qualified_publication_name = @publisher + N':' + 
                                             @publisher_db + N':' +
                                             @publication
        RAISERROR(21121,16,-1,@owner_name, @qualified_publication_name) 
        RETURN (1)
    END

    /* 
    ** Get the agent name, it may be dropped later.
    */
    SELECT @agent_id = agent_id FROM MSreplication_subscriptions
                    WHERE UPPER(publisher) = UPPER(@publisher) AND
                          publisher_db  = @publisher_db AND
                          publication = @publication 

    -- If the agent is used by other subscription, don't drop it.
    if (select count(*) from MSreplication_subscriptions where
        agent_id = @agent_id) > 1
        select @agent_id = NULL

    begin tran
    save TRAN droppullsubscription

    /*
    ** Drop the subscription entry and the distribution agent if it exists 
    */


    /* 
    ** If the distribution agent is not used anymore,
    ** drop the agent if it exists
    */

    IF @agent_id IS NOT NULL
    BEGIN
        IF (EXISTS (SELECT * FROM msdb..sysjobs_view WHERE job_id = @agent_id))
        BEGIN
            -- Checks if the job name matches one that is generated
            -- by replication
            EXEC @retcode = dbo.sp_MSispulldistributionjobnamegenerated
                               @publisher = @publisher,
                               @publisher_db = @publisher_db,
                               @publication = @publication,
                               @job_id = @agent_id
            IF @@ERROR <> 0
                GOTO UNDO

            -- Only drop jobs if the name was generated
            IF @retcode = 0
            BEGIN
                EXEC @retcode = msdb.dbo.sp_delete_job @job_id = @agent_id
                IF @@ERROR <> 0 or @retcode <> 0
                    GOTO UNDO

                -- Delete MSreplication_subsciptions table after dropping 
                -- the distribution agent and delay one second
                -- to avoid deadlock with it.
                WAITFOR DELAY '00:00:01'
            END
        END

    END

    /* Call sp_MSunregistersubscription so that the reg entries get deleted */
    declare @subscriber_db sysname
    set @subscriber_db = DB_NAME()
    exec @retcode = dbo.sp_MSunregistersubscription @publisher = @publisher,
                                @publisher_db = @publisher_db,
                                @publication = @publication,
                                @subscriber = @@SERVERNAME,
                                @subscriber_db = @subscriber_db

    IF @retcode<>0 or @@ERROR<>0
        GOTO UNDO
    /*
    -- Delete MSreplication_subsciptions table after dropping the distribution agent\
    -- To avoid deadlock with it.
    --DELETE MSreplication_subscriptions WHERE UPPER(publisher) = UPPER(@publisher) AND
        --publisher_db  = @publisher_db AND
        --publication = @publication 
    
    --IF @@ERROR <> 0 
    --  GOTO UNDO

    */
    
    IF EXISTS(select * from sysobjects where type='U' and name = 'MSsubscription_properties')
    BEGIN
        DELETE FROM MSsubscription_properties 
        WHERE UPPER(publisher) = UPPER(@publisher)  AND
        publisher_db  = @publisher_db AND
        publication = @publication 

        IF @@ERROR <> 0 
            GOTO UNDO

        IF NOT EXISTS (SELECT * FROM MSsubscription_properties)
        BEGIN
            exec @retcode = dbo.sp_MSsub_cleanup_prop_table
            IF @@ERROR <> 0 or @retcode <> 0
                GOTO UNDO
        END
    END
    
    /*
    ** Clean up metadata at subscriber side
    */
    -- Note: sp_subscription_cleanup should be called after deleting the row
    -- in MSreplication_subscriptions.
    exec @retcode = dbo.sp_subscription_cleanup @publisher = @publisher,
                                            @publisher_db  = @publisher_db,
                                            @publication = @publication
    IF @retcode<>0 or @@ERROR<>0
        GOTO UNDO

    --
    -- drop table MSreplication_subscriptions if empty and
    -- not in recursive call
    --
    IF EXISTS (SELECT * 
                 FROM sysobjects 
                WHERE name = 'MSreplication_subscriptions' 
                  AND type = 'U')
    BEGIN
        IF (@reserved = 0 AND 
            NOT EXISTS (SELECT * FROM MSreplication_subscriptions))
        BEGIN
            DROP TABLE MSreplication_subscriptions
            IF @@ERROR <> 0 
                GOTO UNDO
        END
    END

    COMMIT TRAN
    /*
    ** set back the two settings if needed 
    */
    if @reserved = 0
    BEGIN
        IF @implicit_transaction <>0 
            SET IMPLICIT_TRANSACTIONS ON
        IF @close_cursor_at_commit <>0 
            SET CURSOR_CLOSE_ON_COMMIT ON
    END
    RETURN (0)

UNDO:
    IF @@TRANCOUNT > 0
    begin
        ROLLBACK TRAN droppullsubscription
        COMMIT TRAN   
    end
    /*
    ** set back the two settings if needed
    */
    if @reserved = 0
    BEGIN
        IF @implicit_transaction <>0 
            SET IMPLICIT_TRANSACTIONS ON
        IF @close_cursor_at_commit <>0 
            SET CURSOR_CLOSE_ON_COMMIT ON
    END

    return 1
go
exec sp_MS_marksystemobject 'dbo.sp_droppullsubscription'
grant execute on dbo.sp_droppullsubscription to public
--------------------------------------------------------------------------------
--. sp_dropmergepullsubscription
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_dropmergepullsubscription')
drop procedure sp_dropmergepullsubscription
go
raiserror('Creating procedure sp_dropmergepullsubscription', 0,1)
GO
CREATE PROCEDURE sp_dropmergepullsubscription(
    @publication        sysname = NULL,             /* Publication name */
    @publisher          sysname = NULL,             /* Publisher server */
    @publisher_db       sysname = NULL,             /* Publication database */
    @reserved           bit = 0
    )AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    declare @retcode                int
    declare @subscriber_srvid       int
    declare @publisher_srvid        int
    declare @pubid                  uniqueidentifier
    declare @subid                  uniqueidentifier
    declare @partnerid              uniqueidentifier 
    declare @local_server           sysname
    declare @local_db               sysname
    declare @merge_jobid            binary(16)
    declare @cmd                    nvarchar(255)
    declare @pubidstr               nvarchar(38)
    declare @subscriber             sysname
    declare @subscriber_db          sysname
    declare @subscriber_type        int
    declare @local_job              bit
    declare @implicit_transaction   int
    declare @close_cursor_at_commit int
    declare @owner_sid              varbinary(85)
    declare @owner_name             sysname
    declare @qualified_publication_name nvarchar(512)


    select @close_cursor_at_commit = 0
    select @implicit_transaction = 0

    /*
    ** Get original setting values before setting them to false for recursive calling
    */
    IF (@reserved = 0)
    BEGIN
        SELECT @implicit_transaction = @@options & 2
        SELECT @close_cursor_at_commit = @@options & 4
        SET IMPLICIT_TRANSACTIONS OFF
        SET CURSOR_CLOSE_ON_COMMIT OFF
    END

    /* 
    ** Security Check.
    */
    exec @retcode = dbo.sp_MSreplcheck_subscribe
    if @@ERROR <> 0 or @retcode <> 0
        return(1)

     /*
    ** Initializations.
    */              
    set @local_db = DB_NAME()
    set @local_server = @@SERVERNAME
    set @subscriber = @@SERVERNAME   
    set @subscriber_db = DB_NAME()

   /*
    ** Assign parameter values appropriately
    */
            
    select @subscriber_srvid = srvid from master..sysservers where UPPER(srvname) = UPPER(@subscriber) collate database_default
    IF @subscriber_srvid IS NULL
        BEGIN
           RAISERROR (14010, 16, -1)
           RETURN (1)     
        END
        
    IF not exists (select name from sysobjects where name='sysmergesubscriptions')
        BEGIN
            RAISERROR (14055, 16, -1)
            RETURN (1)
        END     

    /*
    ** Parameter Check: @publisher
    ** Check to make sure that the publisher is defined
    */
            IF @publisher IS NULL
            BEGIN
                RAISERROR (14043, 16, -1, '@publisher')
                RETURN (1)
            END

            EXECUTE @retcode = dbo.sp_validname @publisher
            IF @@ERROR <> 0 OR @retcode <> 0
               RETURN (1)

    /*
    ** Parameter Check: @publisher_db
    */
            IF @publisher_db IS NULL
            BEGIN
                RAISERROR (14043, 16, -1, '@publisher_db')
                RETURN (1)
            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, '@publication')
            RETURN (1)
        END

    IF LOWER(@publication) = 'all'
        BEGIN
            declare hC1 CURSOR LOCAL FAST_FORWARD FOR select DISTINCT name FROM sysmergepublications FOR READ ONLY
            
            OPEN hC1
            FETCH hC1 INTO @publication
            if @@fetch_status = -1 
                begin
                    CLOSE hC1
                    DEALLOCATE hC1
                    RETURN (0) --- It's OK to have no publication when 'ALL'
                end
            WHILE (@@fetch_status <> -1)
                BEGIN
                    EXECUTE dbo.sp_dropmergepullsubscription @publication = @publication,
                                      @publisher = @publisher,
                                      @publisher_db = @publisher_db,
                                      @reserved = 1
                    FETCH hC1 INTO @publication
                END
            CLOSE hC1
            DEALLOCATE hC1
            RETURN (0)
        END
        
    IF LOWER(@publisher) = 'all'
        BEGIN
            declare hC4 CURSOR LOCAL FAST_FORWARD FOR select DISTINCT srvname FROM master..sysservers FOR READ ONLY
            
            OPEN hC4
            FETCH hC4 INTO @publisher
            WHILE (@@fetch_status <> -1)
                BEGIN
                    EXECUTE dbo.sp_dropmergepullsubscription @publication = @publication,
                                                @publisher = @publisher,
                                                @publisher_db = @publisher_db,
                                                @reserved = 1
                    FETCH hC4 INTO @publisher
                END
            CLOSE hC4
            DEALLOCATE hC4
            RETURN (0)
        END

    /*
    ** Validate that the publisher is a valid server
    */
    select @publisher_srvid = srvid from master..sysservers where UPPER(srvname) = UPPER(@publisher) collate database_default
    IF @publisher_srvid IS NULL
        BEGIN
            --RAISERROR (14010, 16, -1)
            RETURN (1)
        END


    /* Previously the condition is set as 'AND subid<>pubid' which is fatally errorous */

    IF LOWER(@publisher_db) = 'all'
        BEGIN
            declare hC5 CURSOR LOCAL FAST_FORWARD FOR select DISTINCT db_name FROM sysmergesubscriptions 
                    WHERE subid = pubid and pubid in (select pubid from sysmergepublications 
                            where UPPER(publisher)=UPPER(@publisher) 
                                and name=@publication)
                                
                        FOR READ ONLY
            
            OPEN hC5
            FETCH hC5 INTO @publisher_db
            WHILE (@@fetch_status <> -1)
                BEGIN
                    EXECUTE dbo.sp_dropmergepullsubscription @publication = @publication,
                                      @publisher = @publisher,
                                      @publisher_db = @publisher_db,
                                      @reserved = 1
                    FETCH hC5 INTO @publisher_db
                END
            CLOSE hC5
            DEALLOCATE hC5
            RETURN (0)
        END

    /*
    ** return error if only there is no 'ALL'. Same is true for the rest of error handling.
    */
    if NOT EXISTS (select * FROM sysmergepublications 
    WHERE name = @publication and UPPER(publisher)=UPPER(@publisher) and publisher_db=@publisher_db) 
    BEGIN
        if @reserved = 0
            RAISERROR (20026, 16, -1, @publication)
        RETURN (1)
    END
        
    select @pubid = pubid from sysmergepublications where name = @publication and UPPER(publisher)=UPPER(@publisher) and publisher_db=@publisher_db
    set @pubidstr = '''' + convert(nchar(36), @pubid) + ''''
    if @pubid is null  
        BEGIN
            if @reserved = 0
                RAISERROR (20026, 16, -1, @publication)
            RETURN (1)
        END

    /*
    ** Only members of the sysadmin group and the creator of the distribution
    ** agent can drop a pull subscription successfully. This behavior matches 
    ** the behavior of the sysjobs_view. DBO of the subscriber database, 
    ** sysadmins (owner is undefined) can drop a subscription if the owner_sid 
    ** is null.
    */
    
    EXEC sp_MSget_mergepullsubsagent_owner @publisher = @publisher,
                                           @publisher_db = @publisher_db,
                                           @publication = @publication,
                                           @owner_sid = @owner_sid OUTPUT 
    
    IF (@owner_sid is not null AND
        (SUSER_SID() <> @owner_sid) AND
        (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0))
    BEGIN
        SELECT @owner_name = SUSER_SNAME(@owner_sid)
        SELECT @qualified_publication_name = @publisher + N':' + 
                                             @publisher_db + N':' +
                                             @publication
        RAISERROR(21121,16,-1,@owner_name, @qualified_publication_name) 
        RETURN (1)
    END

    /*
    ** Get subscriptions from either local replicas or global replicas
    */
    select @subid = subs1.subid, @subscriber_type = subs1.subscriber_type, @partnerid = subs2.subid from
        sysmergesubscriptions   subs1,
        sysmergesubscriptions   subs2,
        sysmergepublications        pubs
        where subs1.srvid = @subscriber_srvid
            and subs1.db_name = @subscriber_db
            and subs2.srvid = @publisher_srvid
            and subs2.db_name = @publisher_db
            and subs1.pubid = subs2.subid
            and subs2.pubid = pubs.pubid
            and pubs.name = @publication
            and UPPER(pubs.publisher)=UPPER(@publisher)
            and pubs.publisher_db=@publisher_db

    if @subid IS NULL 
        begin
            if @reserved = 0 
                raiserror (14050, 16, -1)
            RETURN (0)
        end                 

    begin tran
    save TRAN dropmergepullsubscription
    
        /*  
        ** Drop the local merge task
        */
        select @merge_jobid = merge_jobid from MSmerge_replinfo WHERE repid = @subid
        if (@merge_jobid IS NOT NULL)
        BEGIN
            IF EXISTS (SELECT * FROM msdb..sysjobs_view WHERE job_id = @merge_jobid)
            BEGIN

                -- Checks if the job name matches one that is generated
                -- by replication
                EXEC @retcode = dbo.sp_MSispullmergejobnamegenerated
                                    @publisher = @publisher,
                                    @publisher_db = @publisher_db,
                                    @publication = @publication,
                                    @job_id = @merge_jobid
                IF @@ERROR <> 0
                    GOTO FAILURE

                -- Only drop the job if the name was generated
                IF @retcode = 0
                BEGIN
                    EXEC @retcode = msdb.dbo.sp_delete_job @job_id = @merge_jobid
                    IF @@ERROR <> 0 or @retcode <> 0
                        GOTO FAILURE
                END
            END
        END                                 

        if @subid <> @partnerid
        BEGIN
            DELETE MSmerge_replinfo WHERE repid = @subid 
            IF @@ERROR <> 0
                GOTO FAILURE
            delete from sysmergesubscriptions where subid = @subid
            if @@ERROR <> 0
                GOTO FAILURE

            /* Call sp_MSunregistersubscription so that the reg entries get deleted */
            exec @retcode = dbo.sp_MSunregistersubscription @publisher = @publisher,
                                        @publisher_db = @publisher_db,
                                        @publication = @publication,
                                        @subscriber = @@SERVERNAME,
                                        @subscriber_db = @subscriber_db
            IF @retcode<>0 or @@ERROR<>0
                GOTO FAILURE

            exec dbo.sp_MSpublicationcleanup @publisher=@publisher, 
                                            @publisher_db = @publisher_db,
                                            @publication = @publication
            IF @@ERROR <> 0 
                BEGIN
                    RAISERROR (20025, 16, -1, @publication)
                    GOTO FAILURE
                END
        END
        
        IF EXISTS(select * from sysobjects where type='U' and name = 'MSsubscription_properties')
        BEGIN
            DELETE FROM MSsubscription_properties 
            WHERE UPPER(publisher) = UPPER(@publisher)  AND
            publisher_db  = @publisher_db AND
            publication = @publication 
    
            IF @@ERROR <> 0 
                GOTO FAILURE
    
            IF NOT EXISTS (SELECT * FROM MSsubscription_properties)
            BEGIN
                exec @retcode = dbo.sp_MSsub_cleanup_prop_table
                IF @@ERROR <> 0 or @retcode <> 0
                    GOTO FAILURE
            END
        END

    COMMIT TRAN

    /*
    ** Set back original settings
    */
    IF @reserved = 0
    BEGIN
        /* 
        ** If last subscription is dropped and the DB is not enabled for publishing,
        ** then remove the merge system tables
        */
        IF (not exists (select * from sysmergesubscriptions )) 
            AND (select category & 4 FROM master..sysdatabases WHERE name = DB_NAME())=0
        BEGIN
                execute @retcode = dbo.sp_MSdrop_mergesystables
                if @@ERROR <> 0 or @retcode <> 0
                    begin
                        return (1)
                    end
        END 

        IF @implicit_transaction <>0 
            SET IMPLICIT_TRANSACTIONS ON
        IF @close_cursor_at_commit <>0 
            SET CURSOR_CLOSE_ON_COMMIT ON
    END

    RETURN(0)       

FAILURE:
    RAISERROR (14056, 16, -1)
    if @@trancount > 0
    begin
        ROLLBACK TRANSACTION dropmergepullsubscription
        COMMIT TRANSACTION
    end
    /*
    ** Set back original settings
    */  
    IF @reserved = 0
    BEGIN
        IF @implicit_transaction <>0 
            SET IMPLICIT_TRANSACTIONS ON
        IF @close_cursor_at_commit <>0 
            SET CURSOR_CLOSE_ON_COMMIT ON
    END
    return 1
go
exec sp_MS_marksystemobject 'dbo.sp_dropmergepullsubscription'
grant execute on dbo.sp_dropmergepullsubscription to public
--------------------------------------------------------------------------------
--. sp_MSenum_replsqlqueues
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSenum_replsqlqueues')
drop procedure sp_MSenum_replsqlqueues
go
raiserror('Creating procedure sp_MSenum_replsqlqueues', 0,1)
go
create procedure sp_MSenum_replsqlqueues
(
	@curdistdb	sysname = NULL
)
as
begin
	declare @distbit int
	declare @db_name sysname
	declare @cmd nvarchar(1024)

	select @distbit = 16
	--
	-- security check
	--
	if (is_srvrolemember('sysadmin') != 1)
	begin
		--
		-- non SA user
		-- @curdistdb cannot be null for NON-SA user
		--
		if (@curdistdb is null)
		begin
			raiserror (14043, 16, -1, '@curdistdb')
			return 1
		end
		else
		begin
			--
			-- The user has to be DBO of the specified distribution db
			--
			declare @fisdbo bit
					,@securitycmd nvarchar(300)

			select @securitycmd = quotename(@curdistdb) + '.dbo.sp_executesql'
			exec @securitycmd
				N'if is_member(N''db_owner'') = 1 set @fisdbo = 1'
				,N'@fisdbo bit output'
				,@fisdbo output 
			if (@fisdbo != 1)
			begin
				raiserror (21050, 14, 3)
				return 1
			end
		end
	end
	--
	-- create the temp table to store the relevant information
	--
	create table #replqueue (
		publisher sysname collate database_default not null,
		publisher_db sysname collate database_default not null,
		subscriber sysname collate database_default not null,
		subscriber_db sysname collate database_default not null,
		publication sysname collate database_default not null,
		dist_db sysname collate database_default not null
	)
	create unique index ucreplqueue ON #replqueue (subscriber, subscriber_db, publication)

	--
	-- Has the user specified a distribution database
	--
	if (@curdistdb is NULL)
	begin
		--
		-- Go through all the distribution databases
		--
		declare hCdatabase CURSOR LOCAL FAST_FORWARD FOR 
			select name from master.dbo.sysdatabases 
			where
				category & @distbit <> 0 and
				has_dbaccess(name) = 1
			for read only
	end
	else
	begin
		--
		-- User specified Distribution Database
		-- Validate the user specified name
		--
		if exists (select name from master.dbo.sysdatabases 
		where
			name = @curdistdb and
			category & @distbit <> 0 and
			has_dbaccess(name) = 1)
		begin
			declare hCdatabase CURSOR LOCAL FAST_FORWARD FOR
			select @curdistdb
			for read only
		end
		else
		begin
			--
			-- Error : Invalid distribution db specified
			--
			raiserror(20587, 16, 3, N'@curdistdb', N'sp_MSenum_replsqlqueues')
			return 1
		end
	end
	
	--
	-- Enumerate
	--
	open hCdatabase
	fetch next from hCdatabase into @db_name
	while (@@fetch_status <> -1)
	begin
		select @cmd =
		'SET NOCOUNT ON ' +
		'INSERT INTO #replqueue (publisher, publisher_db, subscriber, subscriber_db, publication, dist_db) ' +
		'SELECT c.srvname, a.publisher_db, b.srvname, a.subscriber_db, a.publication, N''' + @db_name + ''' ' +
		'FROM ' + QUOTENAME(@db_name) + '.dbo.MSdistribution_agents as a ' +
			'JOIN master.dbo.sysservers as b ' +
			'ON a.subscriber_id = b.srvid ' +
			'JOIN master.dbo.sysservers as c ' + 
			'ON a.publisher_id = c.srvid ' + 
			'WHERE queue_id = N''mssqlqueue'' '
			
		exec (@cmd)
		fetch next from hCdatabase into @db_name
	end
	close hCdatabase
	deallocate hCdatabase

	--
	-- select the contents of the temp table
	--
	select * from #replqueue

	--
	-- cleanup
	--
	drop table #replqueue
end
go
EXEC dbo.sp_MS_marksystemobject sp_MSenum_replsqlqueues
go
grant execute on dbo.sp_MSenum_replsqlqueues to public
go

--------------------------------------------------------------------------------
--. sp_MSenum_replqueues
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSenum_replqueues')
drop procedure sp_MSenum_replqueues
go
raiserror('Creating procedure sp_MSenum_replqueues', 0,1)
go
create procedure sp_MSenum_replqueues
(
	@curdistdb	sysname = NULL
)
as
begin
	declare @distbit int
	declare @db_name sysname
	declare @cmd nvarchar(1024)

	select @distbit = 16
	--
	-- security check
	--
	if (is_srvrolemember('sysadmin') != 1)
	begin
		--
		-- non SA user
		-- @curdistdb cannot be null for NON-SA user
		--
		if (@curdistdb is null)
		begin
			raiserror (14043, 16, -1, '@curdistdb')
			return 1
		end
		else
		begin
			--
			-- The user has to be DBO of the specified distribution db
			--
			declare @fisdbo bit
					,@securitycmd nvarchar(300)

			select @securitycmd = quotename(@curdistdb) + '.dbo.sp_executesql'
			exec @securitycmd
				N'if is_member(N''db_owner'') = 1 set @fisdbo = 1'
				,N'@fisdbo bit output'
				,@fisdbo output 
			if (@fisdbo != 1)
			begin
				raiserror (21050, 14, 3)
				return 1
			end
		end
	end
	--
	-- create the temp table to store the relevant information
	--
	create table #replqueue (
		queue_server sysname collate database_default not null,
		queue_id sysname collate database_default not null,
		dist_db sysname collate database_default not null
	)
	create unique clustered index ucreplqueue ON #replqueue (queue_id)

	--
	-- Has the user specified a distribution database
	--
	if (@curdistdb is NULL)
	begin
		--
		-- Go through all the distribution databases
		--
		declare hCdatabase CURSOR LOCAL FAST_FORWARD FOR 
			select name from master.dbo.sysdatabases 
			where
				category & @distbit <> 0 and
				has_dbaccess(name) = 1
			for read only
	end
	else
	begin
		--
		-- User specified Distribution Database
		-- Validate the user specified name
		--
		if exists (select name from master.dbo.sysdatabases 
		where
			name = @curdistdb and
			category & @distbit <> 0 and
			has_dbaccess(name) = 1)
		begin
			declare hCdatabase CURSOR LOCAL FAST_FORWARD FOR
			select @curdistdb
			for read only
		end
		else
		begin
			--
			-- Error : Invalid distribution db specified
			--
			raiserror(20587, 16, 4, N'@curdistdb', N'sp_MSenum_replqueues')
			return 1
		end
	end

	--
	-- Enumerate
	--
	open hCdatabase
	fetch next from hCdatabase into @db_name
	while (@@fetch_status <> -1)
	begin
		select @cmd =
		'SET NOCOUNT ON ' +
		'INSERT INTO #replqueue (queue_server, queue_id, dist_db) ' +
		'SELECT queue_server, queue_id, ' +
				'N''' + replace(@db_name, '''', '''''') + ''' ' +
		'FROM ' +
				QUOTENAME(@db_name) + '.dbo.MSdistribution_agents ' +
		'WHERE ' +
				'queue_id IS NOT NULL and queue_id != N''mssqlqueue'''
			
		exec (@cmd)
		fetch next from hCdatabase into @db_name
	end
	close hCdatabase
	deallocate hCdatabase

	--
	-- select the contents of the temp table to present
	-- the formatted queue name
	--
	select 	N'DIRECT=OS:' + queue_server + N'\PRIVATE$\' + queue_id AS 'queue_format'
			,dist_db
	from #replqueue
	
	--
	-- cleanup
	--
	drop table #replqueue
end
go
EXEC dbo.sp_MS_marksystemobject sp_MSenum_replqueues
go
grant execute on dbo.sp_MSenum_replqueues to public
go

--------------------------------------------------------------------------------
--. sp_vupgrade_publisherdb
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_vupgrade_publisherdb')
drop procedure sp_vupgrade_publisherdb
go
raiserror('Creating procedure sp_vupgrade_publisherdb', 0,1)
go
create procedure sp_vupgrade_publisherdb @ver_old int, @ver_retention int
as
begin
/* 
 * Process schema and metadata changes specific to a publishing database. Transactional replication 
 * system tables at the publisher are updated by sp_vupgrade_publisherdb; merge tables are not.
 * Most merge system tables exist in both the publishing and subscribing databases; merge schema 
 * and metadata changes are processed by a separate pass over all databases in sp_vupgrade_mergetables.
 *
 * Match column order of newly created table when adding columns.
 *
 * Setup version upgrade procedure call order:
 *	sp_vupgrade_replication -> sp_vupgrade_publisher -> sp_vupgrade_publisherdb
*/

	set nocount on 

	declare @default_name sysname 

	-- raiserror('sp_vupgrade_publisherdb', 0,1) with nowait

	-- sysarticles 
	if exists( select name from sysobjects where name = 'sysarticles' )
	begin
		
		if exists( select * from syscolumns where id = object_id('dbo.sysarticles') and name = 'schema_option' )
		begin
			-- The following two-phase upgrade operation has to be atomic
			begin transaction
			save transaction sysarticles_upgrade 
    
				-- For table articles which used to have the 
				-- SQLDMOCreationScript_DRI_Primary (0x00000080) schema option enabled,
				-- turn on the SQLDMOCreationScript_PKFKAsConstraints (0x00008000)
				-- option
				update sysarticles 
				   set schema_option = convert(int, schema_option) | convert(int, 0x00008000)
				  from sysobjects so
				where (convert(int, schema_option) & 0x00000080) <> 0x00000000
				  and so.id = sysarticles.objid
				  and so.xtype = 'U'
        
				if @@error <> 0
					goto SYSARTICLE_ERROR

				-- Enable both the SQLDMOCreationScript_DRI_PrimaryKey and
				-- SQLDMOCreationScript_UniqueKeys options (0x00004000) if at least 
				-- one of SQLDMOCreationScript_NonClusteredIndexes (0x00000010) and 
				-- SQLDMOCreationScript_ClusteredIndexes (0x00000040) was enabled
				-- or the table article belongs to a transactional publication  
				update sysarticles 
				   set schema_option = convert(int, schema_option) |
										convert(int, 0x00004080)
				  from sysobjects so,
					   syspublications sp
				where ((sysarticles.pubid = sp.pubid and sp.repl_freq = 0) 
					  or (convert(int, schema_option) & convert(int, 0x00000050) <> 
						  0x00000000)) 
				  and so.id = sysarticles.objid
				  and so.xtype = 'U'

				if @@error <> 0
					goto SYSARTICLE_ERROR

				-- Skip over the error handling code
				goto SYSARTICLE_END
		SYSARTICLE_ERROR:
			rollback transaction sysarticles_upgrade
			commit transaction
			return 1    
		SYSARTICLE_END:
			commit transaction 
		end
	end -- end of sysarticles upgrade

	-- syssubscriptions
	if exists (select name from sysobjects where name='syssubscriptions')
	begin
		if not exists (select * from syscolumns where id = object_id('syssubscriptions') and 
			name = 'queued_reinit')
		begin
			alter table syssubscriptions add queued_reinit bit default 0 not null
		end

	end -- end of syssubscriptions upgrade

	-- syspublications
	if exists (select name from sysobjects where name='syspublications')
	begin

		-- default_access column no longer used
		if exists (select * from syscolumns where id = object_id('syspublications') and
			name = 'default_access')
		begin
			alter table syspublications drop column default_access
		end 

		/*
		 * Queued updating subscriptions option; conflict management settings added later in this proc
		*/
		if not exists (select * from syscolumns where id = object_id('syspublications') and 
			name = 'allow_queued_tran')
		begin
			alter table syspublications add allow_queued_tran bit default 0 not null
		end

		/*
		 * Portable snapshot
		*/
		if not exists (select * from syscolumns where id = object_id('syspublications') and name = 'snapshot_in_defaultfolder')
		begin
			alter table syspublications add snapshot_in_defaultfolder bit default 1 not null
		end
    
		if not exists (select * from syscolumns where id = object_id('syspublications') and name = 'alt_snapshot_folder')
		begin
			alter table syspublications add alt_snapshot_folder nvarchar(255) null
		end

		/*
		 * Snapshot pre/post commands
		*/
		if not exists (select * from syscolumns where id = object_id('syspublications') and name = 'pre_snapshot_script')
		begin
			alter table syspublications add pre_snapshot_script nvarchar(255) null
		end
       
		if not exists (select * from syscolumns where id = object_id('syspublications') and name = 'post_snapshot_script')
		begin
			alter table syspublications add post_snapshot_script nvarchar(255) null
		end

		/*
		 * Snapshot compression
		*/
		if not exists (select * from syscolumns where id = object_id('syspublications') and name = 'compress_snapshot')
		begin
			alter table syspublications add compress_snapshot bit default 0 not null
		end

		/*
		 * Post SQL7.0 FTP configuration stored at publisher
		*/
		if not exists (select * from syscolumns where id = object_id('syspublications') and name = 'ftp_address')
		begin
			alter table syspublications add ftp_address sysname null
		end

		if not exists (select * from syscolumns where id = object_id('syspublications') and name = 'ftp_port')
		begin
			alter table syspublications add ftp_port int not null default 21
		end

		if not exists (select * from syscolumns where id = object_id('syspublications') and name = 'ftp_subdirectory')
		begin
			alter table syspublications add ftp_subdirectory nvarchar(255) null
		end

		if not exists (select * from syscolumns where id = object_id('syspublications') and name = 'ftp_login')
		begin
			alter table syspublications add ftp_login sysname null default N'anonymous'
		end

		if not exists (select * from syscolumns where id = object_id('syspublications') and name = 'ftp_password')
		begin
			alter table syspublications add ftp_password nvarchar(524) null
		end

		-- Since the ftp_address is now required to enable a publication for 
		-- internet, publications that were enabled for internet can not
		-- be upgraded automatically. So the enabled for internet option 
		-- is reset in the upgrade process. 
		if exists (select * from syscolumns where id = object_id('syspublications') and 
		   name = 'enabled_for_internet')
		begin
			update dbo.syspublications 
			   set enabled_for_internet = 0 
		end

		/*
		 * Transformable subscriptions
		*/
		if not exists (select * from syscolumns where id = object_id('syspublications') and 
			name = 'allow_dts')
		begin
			alter table syspublications add allow_dts bit default 0 not null
		end

		/*
		 * Attach & Go 
		*/
		if not exists (select * from syscolumns where id = object_id('syspublications') and 
			name = 'allow_subscription_copy')
		begin
			alter table syspublications add allow_subscription_copy bit default 0 not null
		end

		/*
		 * Transactional conflict management
		*/
		if not exists (select * from syscolumns where id = object_id('syspublications') and 
			name = 'centralized_conflicts')
		begin
			alter table syspublications add centralized_conflicts bit NULL
		end
    
		if not exists (select * from syscolumns where id = object_id('syspublications') and 
			name = 'conflict_retention')
		begin
			alter table syspublications add conflict_retention int NULL
		end

		if not exists (select * from syscolumns where id = object_id('syspublications') and 
			name = 'conflict_policy')
		begin
			alter table syspublications add conflict_policy int NULL
		end

		/*
		 * Attach & Go 
		*/
		if not exists (select * from syscolumns where id = object_id('syspublications') and 
			name = 'queue_type')
		begin
			alter table syspublications add queue_type int NULL
		end

		/*
		 * Active Directory
		*/
		if not exists (select * from syscolumns where id = object_id('syspublications') and 
			name = 'ad_guidname')
		begin
			alter table syspublications add ad_guidname sysname null
		end

		if not exists (select * from syscolumns where id = object_id('syspublications') and 
			name = 'backward_comp_level')
		begin
			alter table syspublications add backward_comp_level int default 10 not null
		end
		
		/*
		 * Schema only articles (views, procs, udfs)
		*/
		if not exists (select * from sysobjects where name = 'sysschemaarticles')
		begin
			create table dbo.sysschemaarticles
			(
				artid               int                 NOT NULL,
				creation_script     nvarchar(255)       NULL,
				description         nvarchar(255)       NULL,
				dest_object         sysname             NOT NULL,
				name                sysname             NOT NULL,
				objid               int                 NOT NULL,
				pubid               int                 NOT NULL,
				pre_creation_cmd    tinyint             NOT NULL,
				status              int                 NOT NULL,
				type                tinyint             NOT NULL,
				schema_option       binary(8)           NULL,
				dest_owner          sysname             NULL
			)
			exec dbo.sp_MS_marksystemobject sysschemaarticles

			if not exists (select * from sysindexes where name = 'unc1sysschemaarticles')
			begin
				create unique nonclustered index unc1sysschemaarticles
					on sysschemaarticles(artid, pubid)
			end  

		end

		if exists (select * from sysobjects where name = 'sysextendedarticlesview')
		begin
            drop view dbo.sysextendedarticlesview
		end
		-- cannot create view directly in stored procedure			
		exec ( 'create view dbo.sysextendedarticlesview
				   as
				   select artid, columns, creation_script, del_cmd, description,
				   dest_table, filter, filter_clause, ins_cmd, name, objid, pubid, pre_creation_cmd,
				   status, sync_objid, type, upd_cmd, schema_option, dest_owner	
				   from sysarticles
				   union all
				   select artid, NULL, creation_script, NULL, description,
				   dest_object, NULL, NULL, NULL, name, objid, pubid, 
				   pre_creation_cmd, status, NULL, type, NULL, 
				   schema_option, dest_owner 
				   from sysschemaarticles' )

		exec dbo.sp_MS_marksystemobject 'sysextendedarticlesview'

		exec sp_vupgrade_syscol_status

	end

	-- sysarticleupdates
	if exists (select name from sysobjects where name='sysarticleupdates')
	begin
		if not exists (select * from syscolumns where id = object_id('sysarticleupdates') and 
			name = 'sync_upd_trig')
		begin
			alter table sysarticleupdates add sync_upd_trig int default 0 NOT NULL
		end

		if not exists (select * from syscolumns where id = object_id('sysarticleupdates') and 
			name = 'conflict_tableid')
		begin
			alter table sysarticleupdates add conflict_tableid int NULL
		end

		if not exists (select * from syscolumns where id = object_id('sysarticleupdates') and 
			name = 'ins_conflict_proc')
		begin
			alter table sysarticleupdates add ins_conflict_proc int NULL
		end

		if not exists (select * from syscolumns where id = object_id('sysarticleupdates') and 
			name = 'identity_support')
		begin
			alter table sysarticleupdates add identity_support bit default 0 NOT NULL
		end

	end

	-- Upgrade dbt->distbackuplsn
	-- Make sure the upgrade is done for 8.0 Beta 2 customers.
	if exists (select * from sysobjects where name = 'sysarticles') and
		not exists (select * from sysobjects where name = 'systranschemas')
	begin
		-- Force to get in even if the logreader is running.
		exec sp_replflush 
		exec sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,
		    @time = 0, @reset = 0, @code = 1
		-- Unmark the connection as the logreader.
		exec sp_replflush
	end

	-- Create new tran tables if the db is enabled for tran publishing.
	if exists (select name from sysobjects where name='syspublications')
	begin
		exec dbo.sp_MScreate_pub_tables
	end

	/*
	 * syspublications
	*/
	if exists (select name from sysobjects where name='syspublications')
	begin
		if not exists (select * from syscolumns where id = Object_Id('syspublications') and name = 'ftp_password' and length = '1048')
		begin
			/*
			 * syspublications ftp_password
			 * no need to upgrade passwords since this column is new in 8.0.
			*/
			declare @dbname sysname
			declare @cmptlevel tinyint
			set @dbname = db_name()
			select @cmptlevel = cmptlevel from master..sysdatabases where name = @dbname collate database_default
			if @cmptlevel < 70
			begin
                                raiserror (15048, -1, -1, 70, 70, 70, 80)
			end
			else
			begin
				exec( 'alter table syspublications alter column ftp_password nvarchar(524)' )
			end
		end

		-- change non-clustered index on syspublications.pubid to clustered 
		-- index
		if exists( select * from sysindexes where name = 'unc1syspublications' and id = object_id('dbo.syspublications') )
		begin
		   drop index dbo.syspublications.unc1syspublications 
		end
		if not exists ( select * from sysindexes where name = 'uc1syspublications' and id = object_id('dbo.syspublications') )
		begin
			create unique clustered index uc1syspublications
				on syspublications (pubid)
		end
	end
end
go
exec dbo.sp_MS_marksystemobject sp_vupgrade_publisherdb
go

--------------------------------------------------------------------------------
--. sp_MSreset_queue
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSreset_queue')
revoke execute on dbo.sp_MSreset_queue from public

--------------------------------------------------------------------------------
--. sp_MSscript_begintrig1
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_begintrig1')
revoke execute on dbo.sp_MSscript_begintrig1 from public

--------------------------------------------------------------------------------
--. sp_MSscript_begintrig2
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_begintrig2')
revoke execute on dbo.sp_MSscript_begintrig2 from public

--------------------------------------------------------------------------------
--. sp_MSscript_endtrig
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_endtrig')
revoke execute on dbo.sp_MSscript_endtrig from public

--------------------------------------------------------------------------------
--. sp_MSscript_multirow_trigger
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_multirow_trigger')
revoke execute on dbo.sp_MSscript_multirow_trigger from public

--------------------------------------------------------------------------------
--. sp_MSscript_params
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_params')
revoke execute on dbo.sp_MSscript_params from public

--------------------------------------------------------------------------------
--. sp_MSscript_pkvar_assignment
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_pkvar_assignment')
revoke execute on dbo.sp_MSscript_pkvar_assignment from public

--------------------------------------------------------------------------------
--. sp_MSscript_procbodystart
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_procbodystart')
revoke execute on dbo.sp_MSscript_procbodystart from public

--------------------------------------------------------------------------------
--. sp_MSscript_singlerow_trigger
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_singlerow_trigger')
revoke execute on dbo.sp_MSscript_singlerow_trigger from public 

--------------------------------------------------------------------------------
--. sp_MSscript_trigger_assignment
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_trigger_assignment')
revoke execute on dbo.sp_MSscript_trigger_assignment from public

--------------------------------------------------------------------------------
--. sp_MSscript_trigger_exec_rpc
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_trigger_exec_rpc')
revoke execute on dbo.sp_MSscript_trigger_exec_rpc  from public

--------------------------------------------------------------------------------
--. sp_MSscript_trigger_fetch_statement
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_trigger_fetch_statement')
revoke execute on dbo.sp_MSscript_trigger_fetch_statement from public

--------------------------------------------------------------------------------
--. sp_MSscript_trigger_update_checks
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_trigger_update_checks')
revoke execute on dbo.sp_MSscript_trigger_update_checks from public

--------------------------------------------------------------------------------
--. sp_MSscript_trigger_updates
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_trigger_updates')
revoke execute on dbo.sp_MSscript_trigger_updates from public

--------------------------------------------------------------------------------
--. sp_MSclearcolumnbit
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSclearcolumnbit')
revoke execute on dbo.sp_MSclearcolumnbit from public

--------------------------------------------------------------------------------
--. sp_MSget_subtypedatasrc
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSget_subtypedatasrc')
revoke execute on dbo.sp_MSget_subtypedatasrc from public

--------------------------------------------------------------------------------
--. sp_MSCheckmergereplication
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSCheckmergereplication')
revoke execute on dbo.sp_MSCheckmergereplication from public

--------------------------------------------------------------------------------
--. sp_MSremove_userscript
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSremove_userscript')
revoke execute on dbo.sp_MSremove_userscript from public

--------------------------------------------------------------------------------
--. sp_MSscript_trigger_variables
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_trigger_variables')
revoke execute on dbo.sp_MSscript_trigger_variables from public

--------------------------------------------------------------------------------
--. sp_MSscript_where_clause
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_where_clause')
revoke execute on dbo.sp_MSscript_where_clause from public

--------------------------------------------------------------------------------
--. sp_MSupdate_mqserver_subdb
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSupdate_mqserver_subdb')
revoke exec on dbo.sp_MSupdate_mqserver_subdb from public
go

--------------------------------------------------------------------------------
--. sp_MSscript_sync_del_trig
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_sync_del_trig')
drop procedure sp_MSscript_sync_del_trig
go
create procedure sp_MSscript_sync_del_trig (
	@objid        int,
	@publisher    sysname,
	@publisher_db sysname,
	@publication  sysname, 
	@trigname     sysname,
	@procname     sysname,
	@proc_owner	  sysname,
	@cftproc	  sysname,
	@agent_id	  int, 
	@identity_col sysname = NULL,
	@ts_col       sysname = NULL,
	@filter_clause nvarchar(4000),
	@primary_key_bitmap  varbinary(4000)
)
as
BEGIN
	declare @colname sysname
			,@cmd          nvarchar(4000)
			,@ins_cmd      nvarchar(4000)
			,@columns      binary(32)
			,@outvars      nvarchar(4000)
			,@rc           int
			,@qualname     nvarchar(512)
			,@fisqueued	   bit

	set nocount on
	--
	-- security check
	--
	exec @rc = sp_MSreplcheck_subscribe
	if @@error <> 0 or @rc <> 0
	begin
		return (1)
	end

	exec sp_MSget_qualified_name @objid, @qualname OUTPUT
	if (LOWER(@cftproc) = N'null')
	begin
		select @fisqueued =  0
				,@cftproc = NULL
	end
	else 
		select @fisqueued =  1

	if @ts_col in ('null','NULL')
		select @ts_col = null

	if @identity_col in ('null','NULL')
		select @identity_col = null

	-- Create temp table
	create table #proctext ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)

	-- 1st preamble common to all synctran procs
	exec dbo.sp_MSscript_begintrig1 @trigname, @objid, @procname, @filter_clause, 'del', @fisqueued

	-- 2nd preamble common to all synctran procs
	exec dbo.sp_MSscript_begintrig2 @publisher, @publisher_db, @publication, @objid, 'del', @agent_id, @fisqueued

	-- script single row handling
	exec @rc = dbo.sp_MSscript_singlerow_trigger @objid,@publisher, @publisher_db, @publication, @procname, @proc_owner, 
	@cftproc, @identity_col, @ts_col, 'del', @primary_key_bitmap

	-- script multi-row handling
	exec @rc = dbo.sp_MSscript_multirow_trigger @objid,@publisher, @publisher_db, @publication, @procname, @proc_owner, 
	@cftproc, @identity_col, @ts_col, 'del', @primary_key_bitmap

	-- script end of trigger
	exec dbo.sp_MSscript_endtrig 

	-- send fragments to client
	select procedure_text from #proctext order by c1 asc
END
go
exec dbo.sp_MS_marksystemobject sp_MSscript_sync_del_trig
go
grant execute on dbo.sp_MSscript_sync_del_trig to public
go

--------------------------------------------------------------------------------
--. sp_MSscript_sync_ins_trig
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_sync_ins_trig')
drop procedure sp_MSscript_sync_ins_trig
go
raiserror('Creating procedure sp_MSscript_sync_ins_trig', 0,1)
go
create procedure sp_MSscript_sync_ins_trig (
	@objid		  int,
	@publisher    sysname,
	@publisher_db sysname,
	@publication  sysname,
	@trigname     sysname,
	@procname     sysname,
	@proc_owner   sysname,
	@cftproc	  sysname,
	@agent_id	  int, 
	@identity_col sysname = NULL,
	@ts_col       sysname = NULL,
	@filter_clause nvarchar(4000),
	@primary_key_bitmap  varbinary(4000)
)
as
BEGIN
	declare @colname      sysname
			,@cmd          nvarchar(4000)
			,@ins_cmd      nvarchar(4000)
			,@columns      binary(32)
			,@outvars      nvarchar(4000)
			,@rc           int
			,@qualname     nvarchar(512)
			,@fisqueued	   bit

    set nocount on
    --
    -- security check
    --
    exec @rc = sp_MSreplcheck_subscribe
    if @@error <> 0 or @rc <> 0
    begin
        return (1)
    end
    exec sp_MSget_qualified_name @objid, @qualname OUTPUT
	if (LOWER(@cftproc) = N'null')
	begin
		select @fisqueued =  0
				,@cftproc = NULL
	end
	else 
		select @fisqueued =  1

	if @ts_col in ('null','NULL')
		select @ts_col = null

	if @identity_col in ('null','NULL')
		select @identity_col = null

    -- Create temp table
	create table #proctext ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)

	-- 1st preamble common to all synctran procs
	exec dbo.sp_MSscript_begintrig1 @trigname, @objid, @procname, @filter_clause, 'ins', @fisqueued
				
	-- 2nd preamble common to all synctran procs
	exec dbo.sp_MSscript_begintrig2 @publisher, @publisher_db, @publication, @objid, 'ins',@agent_id, @fisqueued

	-- script single row handling
	exec @rc = dbo.sp_MSscript_singlerow_trigger @objid,@publisher, @publisher_db, @publication, @procname, @proc_owner, 
		@cftproc, @identity_col, @ts_col, 'ins', @primary_key_bitmap

	-- script multi-row handling
	exec @rc = dbo.sp_MSscript_multirow_trigger @objid, @publisher, @publisher_db, @publication, @procname, @proc_owner, 
		@cftproc, @identity_col, @ts_col, 'ins', @primary_key_bitmap

    -- script end of trigger
	exec dbo.sp_MSscript_endtrig 

	-- send fragments to client
	select procedure_text from #proctext order by c1 asc
END
go
exec dbo.sp_MS_marksystemobject sp_MSscript_sync_ins_trig
go
grant execute on dbo.sp_MSscript_sync_ins_trig to public
go

--------------------------------------------------------------------------------
--. sp_MSscript_sync_upd_trig
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_sync_upd_trig')
drop procedure sp_MSscript_sync_upd_trig
go
raiserror('Creating procedure sp_MSscript_sync_upd_trig', 0,1)
go
create procedure sp_MSscript_sync_upd_trig (
	@objid        int,
	@publisher    sysname,
	@publisher_db sysname,
	@publication  sysname, 
	@trigname     sysname,
	@procname     sysname,
	@proc_owner   sysname,
	@cftproc	  sysname,
	@agent_id	  int, 
	@identity_col sysname = NULL,
	@ts_col       sysname = NULL,
	@filter_clause nvarchar(4000),
	@primary_key_bitmap  varbinary(4000) )
as
BEGIN
	declare @colname      sysname
			,@cmd          nvarchar(4000)
			,@ins_cmd      nvarchar(4000)
			,@columns      binary(32)
			,@outvars      nvarchar(4000)
			,@rc           int
			,@qualname     nvarchar(512)
			,@fisqueued	   bit

    set nocount on
    --
    -- security check
    --
    exec @rc = sp_MSreplcheck_subscribe
    if @@error <> 0 or @rc <> 0
    begin
        return (1)
    end
    exec sp_MSget_qualified_name @objid, @qualname OUTPUT
	if (LOWER(@cftproc) = N'null')
	begin
		select @fisqueued =  0
				,@cftproc = NULL
	end
	else 
		select @fisqueued =  1

	if @ts_col in ('null','NULL')
		select @ts_col = null

	if @identity_col in ('null','NULL')
		select @identity_col = null

    -- Create temp table
	create table #proctext ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)

	-- 1st preamble common to all synctran procs
	exec dbo.sp_MSscript_begintrig1 @trigname, @objid, @procname, @filter_clause, 'upd', @fisqueued
					
	-- prevent updating of unique index when 'row compare' conflict detection, since it would trickle
    -- back as a del/insert, and fail
/*
	if @ts_col is null
	begin
		insert into #proctext(procedure_text) values(N' 
')
--		exec dbo.sp_MSscript_trigger_update_checks @objid, null, null, 'pk', 0
	end
*/

	-- 2nd preamble common to all synctran procs
	exec dbo.sp_MSscript_begintrig2 @publisher, @publisher_db, @publication, @objid, 'upd',@agent_id, @fisqueued

	-- script single row handling
	exec @rc = dbo.sp_MSscript_singlerow_trigger @objid,@publisher, @publisher_db, @publication, @procname, @proc_owner, 
		@cftproc, @identity_col, @ts_col, 'upd', @primary_key_bitmap

	-- script multi-row handling
	exec @rc = dbo.sp_MSscript_multirow_trigger @objid,@publisher, @publisher_db, @publication, @procname, @proc_owner, 
		@cftproc, @identity_col, @ts_col, 'upd', @primary_key_bitmap
    -- script end of trigger
	exec dbo.sp_MSscript_endtrig 

	-- send fragments to client
	select procedure_text from #proctext order by c1 asc
END
go
exec dbo.sp_MS_marksystemobject sp_MSscript_sync_upd_trig
go
grant execute on dbo.sp_MSscript_sync_upd_trig to public
go

--------------------------------------------------------------------------------
--. sp_check_sync_trigger
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_check_sync_trigger')
revoke exec on dbo.sp_check_sync_trigger from public
go
--------------------------------------------------------------------------------
--. sp_check_for_sync_trigger
--------------------------------------------------------------------------------

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_check_for_sync_trigger')
drop procedure sp_check_for_sync_trigger
go
raiserror('Creating procedure sp_check_for_sync_trigger', 0,1)
go
create proc sp_check_for_sync_trigger 
(
    @tabid int, 
    @trigger_op char(10) = NULL OUTPUT
)
as
begin
    declare @ins_trigid int
    declare @upd_trigid int
    declare @del_trigid int
    declare @retcode int

    --
    -- security check
    --
    exec @retcode = sp_MSreplcheck_subscribe
    if @@error <> 0 or @retcode <> 0
    begin
        return (1)
    end

    select @trigger_op = NULL
    
    select @ins_trigid = id from sysobjects where
        type = N'TR' and name like N'trg_MSsync_ins_%' and parent_obj = @tabid
    select @upd_trigid = id from sysobjects where
        type = N'TR' and name like N'trg_MSsync_upd_%' and parent_obj = @tabid
    select @del_trigid = id from sysobjects where
        type = N'TR' and name like N'trg_MSsync_del_%' and parent_obj = @tabid

    if trigger_nestlevel( @ins_trigid ) > 0
    begin
         select @trigger_op = 'ins'
         return 1
    end
    else    -- if nestlevel of insert trigger on same table > 0 then bail
    if trigger_nestlevel( @upd_trigid ) > 0
    begin
         select @trigger_op = 'upd'
         return 1
    end
    else
    if trigger_nestlevel( @del_trigid ) > 0
    begin
         select @trigger_op = 'del'
         return 1
    end
    else
        return 0
end
go
exec dbo.sp_MS_marksystemobject sp_check_for_sync_trigger
go
grant execute on dbo.sp_check_for_sync_trigger to public
go

--------------------------------------------------------------------------------
--. sp_MShelpmergedynamicsnapshotjob
--------------------------------------------------------------------------------

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MShelpmergedynamicsnapshotjob')
   drop procedure sp_MShelpmergedynamicsnapshotjob
go
raiserror('Creating procedure sp_MShelpmergedynamicsnapshotjob', 0,1)
go 
create procedure sp_MShelpmergedynamicsnapshotjob (
    @publication sysname = N'%',
    @dynamic_snapshot_jobname sysname = N'%',
    @dynamic_snapshot_jobid uniqueidentifier = null
    )
as
begin
	declare @retcode int
    set nocount on
	/*
    ** Security Check
    */
    EXEC @retcode = dbo.sp_MSreplcheck_publish
    IF @@ERROR <> 0 or @retcode <> 0
        return (1)
    if not exists (select * from sysobjects where name = 'sysmergepublications')
    begin
        return 0
    end

    select 'id' = j.id,
           'job_name' = j.name,
           'job_id' = j.job_id, 
           'dynamic_filter_login' = j.dynamic_filter_login,
           'dynamic_filter_hostname' = j.dynamic_filter_hostname,
           'dynamic_snapshot_location' = j.dynamic_snapshot_location
      from sysmergepublications p
     inner join MSdynamicsnapshotjobs j
        on p.pubid = j.pubid
     where (p.name = @publication or @publication = N'%')
       and (j.name = @dynamic_snapshot_jobname or @dynamic_snapshot_jobname = N'%')
       and (j.job_id = @dynamic_snapshot_jobid or @dynamic_snapshot_jobid is null) 

    if @@error <> 0
        return (1)
    else
        return (0)

end
go
exec dbo.sp_MS_marksystemobject sp_MShelpmergedynamicsnapshotjob
go
grant execute on dbo.sp_MShelpmergedynamicsnapshotjob to public
go
--------------------------------------------------------------------------------
--. sp_addtabletocontents
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_addtabletocontents')
   drop procedure sp_addtabletocontents
go
raiserror('Creating procedure sp_addtabletocontents', 0,1)
go
create procedure sp_addtabletocontents 
    (@table_name sysname,
     @owner_name sysname = NULL)
AS

    declare @qualified_table_name nvarchar(270)
    declare @tablenick int
    declare @tablenickstr nvarchar(12)
    declare @repl_nick int
    declare @lineage varbinary(249)
    declare @colv varbinary(2000)
    declare @coltrack int
    declare @objid int
    declare @maxcolid int
    declare @retcode int
    declare @gen int

	/*
    ** Security Check
    */
    EXEC @retcode = dbo.sp_MSreplcheck_publish
    IF @@ERROR <> 0 or @retcode <> 0
        return (1)
    
    set nocount on
    create table #temp_cont(rowguid uniqueidentifier)
    execute @retcode = dbo.sp_MSgetreplnick @nickname = @repl_nick output
    if (@@error <> 0) or @retcode <> 0 or @repl_nick IS NULL 
        begin
        RAISERROR (14055, 11, -1)
        RETURN(1)
        end                 

	if @owner_name is NULL
		begin
		select @owner_name = user_name(uid) from sysobjects where name = @table_name
		end
	set @qualified_table_name = QUOTENAME(@owner_name) + '.' + QUOTENAME(@table_name)
	
    set @objid = object_id(@qualified_table_name)
    if @objid is NULL return (1)
    select @gen = max(gen_cur), @tablenick = max(nickname), @coltrack = max(column_tracking) from sysmergearticles where objid = @objid
	if @gen is null
		set @gen = 0
    select @maxcolid = max(colid) from syscolumns where id = @objid

    if @coltrack = 1
		set @colv = { fn INITCOLVS(@maxcolid, @repl_nick) }
    else
        set @colv = NULL
    set @lineage = { fn UPDATELINEAGE(0x0, @repl_nick, 1) }

    set @tablenickstr = convert(nchar, @tablenick)

    exec ('insert into #temp_cont(rowguid) select RowGuidCol from ' + @qualified_table_name + ' where
            RowGuidCol not in (select rowguid from MSmerge_contents where tablenick = 
            ' + @tablenickstr + ')')

    insert into MSmerge_contents (tablenick, rowguid, generation, joinchangegen, lineage, colv1)
        select @tablenick, rowguid, @gen, @gen, @lineage, @colv from #temp_cont

    drop table #temp_cont
GO
exec dbo.sp_MS_marksystemobject sp_addtabletocontents 
go
grant exec on dbo.sp_addtabletocontents to public
go

--------------------------------------------------------------------------------
--. sp_MSaddpubtocontents
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSaddpubtocontents')
   drop procedure sp_MSaddpubtocontents
go
raiserror('Creating procedure sp_MSaddpubtocontents', 0,1)
go
create procedure sp_MSaddpubtocontents 
    (@publication   sysname)
AS
    declare     @pubid  uniqueidentifier
    declare     @tablenick int
    declare     @retcode int
	declare 	@objid int
	declare 	@owner sysname
	declare		@table sysname
	
    set nocount on
	/*
    ** Security Check
    */
    EXEC @retcode = dbo.sp_MSreplcheck_publish
    IF @@ERROR <> 0 or @retcode <> 0
        return (1)

    select @pubid = pubid from sysmergepublications 
        where name = @publication and UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name()
    if @pubid is NULL
        begin
            raiserror (20026, 11, -1, @publication)
            return (1)
        end
    
    select @tablenick = min(nickname) from sysmergearticles where pubid = @pubid

    while @tablenick is not null
        begin
		select @objid = objid from sysmergearticles where pubid = @pubid and
			nickname = @tablenick
		select @owner = user_name(uid) from sysobjects where id = @objid
		set @table = OBJECT_NAME(@objid)
        exec @retcode = dbo.sp_addtabletocontents @table, @owner
        
        IF @@ERROR <> 0 or @retcode <> 0
            return (1)

        select @tablenick = min(nickname) from sysmergearticles where pubid = @pubid and
                nickname > @tablenick
        end
GO
exec dbo.sp_MS_marksystemobject sp_MSaddpubtocontents 
go
grant exec on dbo.sp_MSaddpubtocontents to public
go

--------------------------------------------------------------------------------
--. sp_MScomputemergeunresolvedrefs
--------------------------------------------------------------------------------

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MScomputemergeunresolvedrefs')
   drop procedure sp_MScomputemergeunresolvedrefs
go
raiserror(15339,-1,-1,'sp_MScomputemergeunresolvedrefs')
GO
CREATE PROCEDURE sp_MScomputemergeunresolvedrefs
    @publication sysname, -- Must provide the publication name
    @article     sysname = '%' -- '%' means all articles in the specified publication, otherwise an exact match is performed
AS
    SET NOCOUNT ON 
    DECLARE @pubid uniqueidentifier 

    
    -- Parameter check: @publication

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

    SELECT @pubid = NULL 
    -- Get the pubid of the publication
    SELECT @pubid = pubid 
      FROM sysmergepublications
     WHERE name = @publication
       AND UPPER(publisher) = UPPER(@@SERVERNAME)
       AND publisher_db = DB_NAME()
    
    IF @pubid IS NULL
    BEGIN
        RAISERROR (20026, 11, -1, @publication)
        RETURN (1)    
    END

	if ({ fn ISPALUSER(@pubid) } <> 1)
	begin    
		RAISERROR (14126, 11, -1)
		return (1)

	end	

    SELECT DISTINCT 
           'article' = a.name, 
           'dependent object' = o.name, 
           'dependent object owner' = u.name, 
           'dependent objectid' = o.id 
      FROM dbo.sysmergeextendedarticlesview a
    INNER JOIN sysdepends dep
        ON a.objid = dep.id
       AND a.pubid = @pubid
       AND (@article = '%' OR name = @article)
       AND dep.depid NOT IN (SELECT objid FROM dbo.sysmergeextendedarticlesview
                              WHERE pubid = @pubid 
                                AND (@article = '%' OR name = @article))
    INNER JOIN sysobjects o
        ON dep.depid = o.id
    INNER JOIN sysusers u
        ON u.uid = o.uid          
GO

exec dbo.sp_MS_marksystemobject sp_MScomputemergeunresolvedrefs
go
grant exec on dbo.sp_MScomputemergeunresolvedrefs to public
go

--------------------------------------------------------------------------------
-- procedures from rlrecon.sql regarding security stuff
--------------------------------------------------------------------------------

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSfetchidentityrange')
drop procedure sp_MSfetchidentityrange

raiserror('Creating procedure sp_MSfetchidentityrange', 0,1)
GO

CREATE PROCEDURE sp_MSfetchidentityrange 
@tablename			sysname,
@adjust_only		bit
AS

declare @retcode 			int
declare @objid				int
declare @distributor		sysname
declare @distribdb			sysname
declare @publisher			sysname
declare @publisher_db		sysname
declare @next_seed			bigint
declare @range				bigint
declare @threshold			int
declare @tablenick 			int
declare @distproc			nvarchar(300)
declare @identity_support	int
select @publisher=@@SERVERNAME
select @publisher_db=db_name()

select @objid = object_id(@tablename)

select @identity_support=identity_support, @tablenick = nickname from sysmergearticles where objid=@objid

/*
** do permission checking
*/
exec @retcode=sp_MSreplcheck_connection @tablenick = @tablenick
if @retcode<>0 or @@ERROR<>0 return (1)


exec @retcode = dbo.sp_helpdistributor @rpcsrvname = @distributor OUTPUT, @distribdb   = @distribdb OUTPUT
		IF @@ERROR <> 0 or @retcode <> 0
			return (1)
SELECT @distproc = RTRIM(@distributor) + '.' + RTRIM(@distribdb) + '.dbo.sp_MSfetchAdjustidentityrange'
exec @retcode = @distproc @publisher=@publisher, 
							@publisher_db=@publisher_db, 
							@tablename=@tablename, 
							@adjust_only=1, --do adjust only
							@next_seed=@next_seed OUTPUT,
							@range=@range OUTPUT,
							@threshold=@threshold OUTPUT
IF @@ERROR <> 0 OR @retcode <> 0
	return (1)

--initialize article collection for agents.
if @adjust_only=0
	select @identity_support, @next_seed-range, range, threshold from MSrepl_identity_range where  objid = @objid
GO
exec dbo.sp_MS_marksystemobject sp_MSfetchidentityrange 
go
grant exec on dbo.sp_MSfetchidentityrange to public
go

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MScheckidentityrange')
drop procedure sp_MScheckidentityrange

raiserror('Creating procedure sp_MScheckidentityrange', 0,1)
GO

CREATE PROCEDURE sp_MScheckidentityrange 
	@pubid 				uniqueidentifier,
	@artname			sysname,
	@next_seed			bigint,
	@range				bigint,
	@threshold			int,
	@checkonly			int
AS
	declare @colid				int
	declare @colname			sysname
	declare @retcode			int
	declare @objid				int
	declare @identity_so_far	bigint
	declare @current_max		bigint
	declare @max_identity		bigint
	declare @tablename			sysname
	declare @flag				smallint
	declare @distributor		sysname
	declare @distribdb			sysname
	declare @republisher		bit
	declare @publisher			sysname
	declare @publisher_db		sysname
	declare @distproc			nvarchar(300)
	declare @pub_range			bigint

	/*
	** Check to see if current publication has permission
	*/
	if ({ fn ISPALUSER(@pubid) } <> 1)
	begin	
		RAISERROR (14126, 11, -1)
		return (1)
	end
	
	select @objid = objid from sysmergearticles where pubid=@pubid and name=@artname
	select @flag = 1
	select @republisher = 0
	select @tablename=object_name(@objid)

	if exists (select * from sysmergearticles where objid=@objid and pubid <>@pubid and pubid in (select pubid from 
					sysmergepublications where LOWER(publisher)=LOWER(@@SERVERNAME) and publisher_db=db_name()))
	begin
		select @publisher=@@SERVERNAME
		select @publisher_db=db_name()
		select @republisher = 1
		exec @retcode = dbo.sp_helpdistributor @rpcsrvname = @distributor OUTPUT, @distribdb   = @distribdb OUTPUT
		IF @@ERROR <> 0 or @retcode <> 0
		begin
			raiserror(14071, 16, -1)
			return (1)
			end
	end

	if @checkonly=1
	begin
		if @republisher=0
		begin
			--current_max is defaulted to max_identity value if not republished at subscribers.
			select @current_max = current_max from MSrepl_identity_range where objid=@objid
			select @identity_so_far = ident_current(@tablename)		
			if ident_incr(@tablename) < 0
				select @flag = -1
			if @flag * 100 * (@identity_so_far - (@current_max + 1 - @range))/@range > @threshold
				select 1 --needs bump up
			else
				select 0 --no need to bump up
		end
		else
		begin
			select @current_max=0, @next_seed=0, @threshold=0, @range=0, @pub_range=0, @max_identity=0  --make them non-NULL
			SELECT @distproc = RTRIM(@distributor) + '.' + RTRIM(@distribdb) + '.dbo.sp_MScheck_pub_identity'
			exec  @retcode=	@distproc @publisher=@@SERVERNAME,
									  @publisher_db=@publisher_db,
									  @tablename=@tablename,
									  @range=@range OUTPUT,
									  @max_identity=@max_identity OUTPUT,
									  @next_seed = @next_seed OUTPUT,
									  @pub_range=@pub_range OUTPUT
			if @retcode<>0 or @@ERROR<>0
			begin
				raiserror(21195, 16, -1) 
				return (1)
			end
			if (@max_identity-@next_seed)<@range or (@max_identity-@next_seed)<@pub_range
				select 1
			else
				select 0
		end
	end
	else
	begin
		if @republisher=0
		begin
			--its current_max value is to be set by sp_addmergearticle, if to be republished.
			update MSrepl_identity_range set max_identity=@next_seed + @range, next_seed=@next_seed, current_max=@next_seed + @range -1
				where objid = @objid
			exec sp_MSreseed @objid, @next_seed, @range 
			if @@ERROR <> 0 
				goto FAILURE
		end
		else
		begin
			SELECT @distproc = RTRIM(@distributor) + '.' + RTRIM(@distribdb) + '.dbo.sp_MSfetchAdjustidentityrange'
			exec @retcode = @distproc @publisher=@publisher, 
							@publisher_db=@publisher_db, 
							@tablename=@tablename, 
							@for_publisher = 2,  --for repub case, increment max_identity at distributor side
							@next_seed = @next_seed,
							@range=@range OUTPUT,
							@adjust_only=1 --do adjust only
			if @retcode<>0 or @@ERROR<>0
			begin
				raiserror(21315, 16, -1, @tablename)
				return (1)
			end
		end
	end

	return (0)
FAILURE:
	select 0
	return (1)
go
exec dbo.sp_MS_marksystemobject sp_MScheckidentityrange 
go
grant exec on dbo.sp_MScheckidentityrange to public
go

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MShelpmergearticles')
drop procedure sp_MShelpmergearticles

raiserror('Creating procedure sp_MShelpmergearticles', 0,1)
GO

CREATE PROCEDURE sp_MShelpmergearticles 
	@publication sysname,
	@compatibility_level int = 7000000,
	@pubidin uniqueidentifier = NULL
as
	declare @pubid 					uniqueidentifier
	declare @artid 					uniqueidentifier
	declare @user_name				sysname
	declare @guid_col				sysname
	declare @identity_support 		int
	declare @nickname				int
	declare @identity_so_far		bigint
	declare @next_seed				bigint
	declare @pub_range				bigint
	declare @objid					int
	declare @qualname				nvarchar(270)
	declare @retcode				int
	declare @tablename				sysname
	declare @range					bigint
	declare @current_max			bigint
	declare @threshold				int
	declare @distributor			sysname
	declare @distribdb				sysname
	declare @distproc				nvarchar(300)
	declare @flag					smallint
	declare @c_max					bigint
	declare @n_seed					bigint
	declare @db_name				sysname
	declare @has_joins				int
	declare @article_filter_category int
	declare @haspartfilters			int
	declare @grouppartfilterarticles int
	declare @injoinfilters			int
	declare @nofilters				int
	declare @objid_looper			int
	declare @indexcol				int
	declare @rowcount1				int
	declare @rowcount2				int

	declare @tmp_table TABLE (tablename sysname, user_name sysname,  
			guid_col sysname NULL, next_seed bigint, range bigint, threshold int, artid uniqueidentifier, pubid uniqueidentifier, has_joins int, article_filter_category int, objid int, has_relation_with_joinarticles int default 0, node_visited bit default 0)
	declare @worktable TABLE (objid int NOT NULL, indexcol int)

	set @nofilters = 1
	set @haspartfilters = 2
	set @injoinfilters = 4
	set @grouppartfilterarticles = 8
	
	/*
	** To public.
	*/
	set nocount on
	if (@publication is null)
		begin
		RAISERROR(14003, 16, -1)
		return (1)
		end
	if (not exists(select * from sysobjects where name = 'MSmerge_contents'))
		begin
		RAISERROR(20054 , 16, -1)
		return (1)
		end

	EXECUTE @retcode = dbo.sp_helpdistributor @rpcsrvname = @distributor OUTPUT, @distribdb   = @distribdb OUTPUT
	IF @@ERROR <> 0 or @retcode <> 0
		return (1)

	
	select @db_name = db_name()

	if @pubidin is not NULL
		set @pubid = @pubidin
	else
		select @pubid = pubid from sysmergepublications
			where name = @publication and UPPER(publisher)=UPPER(@@SERVERNAME) and publisher_db=@db_name

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

	/*
	** Check to see if current publication has permission
	*/
	if ({ fn ISPALUSER(@pubid) } <> 1)
	begin	
		RAISERROR (14126, 11, -1)
		return (1)
	end


	select TOP 1 @artid=artid from sysmergearticles where pubid=@pubid order by nickname ASC
	while (@artid is not NULL)
	begin
		select @objid=objid, @nickname=nickname, @identity_support=identity_support from sysmergearticles 
				where pubid=@pubid and artid=@artid
		select @tablename=object_name(@objid)
		select @user_name=user_name(uid) from sysobjects where id=@objid
		select @qualname=QUOTENAME(@user_name) + '.' + QUOTENAME(@tablename)
		select @next_seed=NULL, @range=NULL, @threshold=NULL --null if not being updated later
		if @identity_support=1 and exists (select * from sysmergepublications where
			pubid = @pubid and  UPPER(publisher)=UPPER(@@SERVERNAME) and publisher_db=@db_name)
		begin
			select @current_max=0, @next_seed=0, @threshold=0, @range=0, @pub_range=0  --make them non-NULL
			SELECT @distproc = RTRIM(@distributor) + '.' + RTRIM(@distribdb) + '.dbo.sp_MScheck_pub_identity'
			exec  @retcode=	@distproc @publisher=@@SERVERNAME,
									  @publisher_db=@db_name,
									  @tablename=@tablename,
									  @range=@range OUTPUT,
									  @current_max=@current_max OUTPUT,
									  @threshold=@threshold OUTPUT,
									  @next_seed = @next_seed OUTPUT,
									  @pub_range=@pub_range OUTPUT
			if @retcode<>0 or @@ERROR<>0
				return (1)

			select @identity_so_far = IDENT_CURRENT(@tablename)
			select @flag=1
			if ident_incr(@tablename) < 0
				select @flag = -1

			-- we attempted to adjust publisher side identity range based on its threshodl if needed
			-- however, non-dbo/sysadmin's will not be able to do so.
			-- so this is limited to dbos or sysadmins.
			if @flag * 100 * (@identity_so_far - (@current_max + 1 - @pub_range))/@pub_range > @threshold
					and ((is_srvrolemember('sysadmin') = 1) or (is_member('db_owner') = 1)) 
			begin
				select @c_max=@next_seed + @pub_range - 1
				select @n_seed=@next_seed + @pub_range
				SELECT @distproc = RTRIM(@distributor) + '.' + RTRIM(@distribdb) + '.dbo.sp_MSadjust_pub_identity'
				exec  @retcode=@distproc @publisher=@@SERVERNAME,
										@publisher_db=@db_name,
										@tablename=@tablename,
										@current_max=@c_max,
										@next_seed = @n_seed
				if @retcode<>0 or @@ERROR<>0
					return (1)

				exec @retcode=sp_MSreseed @objid, @next_seed, @pub_range, 1
				if @@ERROR <> 0 or @retcode<>0
				begin
					raiserror(21197, 16, -1)
					return (1)
				end
				select @next_seed=@next_seed + @pub_range

			end
		end
		else if @identity_support=1 and not exists (select * from sysmergepublications where
			pubid = @pubid and  UPPER(publisher)=UPPER(@@SERVERNAME) and publisher_db=@db_name)
		begin
			/* This is the case of a message based subscriber acting like publisher */
			select @range = range, @threshold = threshold, @next_seed = next_seed
				from MSrepl_identity_range where objid = @objid
		end

		if exists (select * from sysmergesubsetfilters where join_nickname = @nickname)
			set @has_joins = 1
		else 
			set @has_joins = 0

		-- reset for subsequent bitwise OR operations.
		set @article_filter_category = 0

		if exists (select * from sysmergesubsetfilters where pubid = @pubid and (join_nickname = @nickname or art_nickname = @nickname))
			set @article_filter_category = (@article_filter_category | @injoinfilters)
		
		if exists (select * from sysmergearticles where pubid = @pubid and nickname = @nickname and datalength(subset_filterclause) > 1)
			set @article_filter_category = (@article_filter_category | @haspartfilters | @grouppartfilterarticles)
		
		if (@article_filter_category = 0)
			set @article_filter_category = @nofilters

		select @guid_col=name from syscolumns where id=@objid and ColumnProperty(@objid, name, 'IsRowGuidCol')=1
		insert @tmp_table values(@tablename, @user_name, @guid_col, 
			@next_seed, @range, @threshold, @artid, @pubid, @has_joins, @article_filter_category, @objid, 0, 0)
		if @@ERROR<>0
		begin
			raiserror(21197, 16, -1)
			return (1)
		end
		select @artid = NULL 
		select TOP 1 @artid=artid from sysmergearticles 
			where pubid=@pubid and nickname>@nickname order by nickname ASC
	end

	-- Find articles that have no relations (in either direction - referring or referenced) with any articles in the join filter category.

	if exists (select * from @tmp_table where (article_filter_category & @injoinfilters) = @injoinfilters)
	begin

		--create unique index #uncworktable on @worktable(objid, indexcol)
		update @tmp_table set node_visited = 1 
		where objid not in (select fkeyid from sysreferences)
		and objid not in (select rkeyid from sysreferences)
		and (article_filter_category & @injoinfilters) = 0

		-- get the min objid from the temp table of all articles
		select @objid_looper = min(objid) from @tmp_table where node_visited = 0
		
		while (@objid_looper is not null)
		begin
			delete from @worktable

			select @indexcol = 0

			insert into @worktable values (@objid_looper, @indexcol)
						
			-- find all the objects referenced by this object and all objects that reference this object.
			insert into @worktable select distinct rkeyid, @indexcol+1 from sysreferences where fkeyid = @objid_looper
											and rkeyid not in (select objid from @worktable)
			select @rowcount1 = @@rowcount
			
			insert into @worktable select distinct fkeyid, @indexcol+1 from sysreferences where rkeyid = @objid_looper
											and fkeyid not in (select objid from @worktable)
			select @rowcount2 = @@rowcount
			
			while (@rowcount1 <> 0 or @rowcount2 <> 0)
			begin
				
				select @indexcol = @indexcol+1

				insert into @worktable select distinct s.rkeyid, @indexcol+1 from sysreferences s, @worktable w, @tmp_table t where s.fkeyid = w.objid and w.objid = t.objid
											and w.indexcol = @indexcol and t.node_visited = 0 and s.rkeyid not in (select objid from @worktable)
				select @rowcount1 = @@rowcount
				
				insert into @worktable select distinct s.fkeyid, @indexcol+1 from sysreferences s, @worktable w, @tmp_table t where s.rkeyid = w.objid and w.objid = t.objid
											and w.indexcol = @indexcol and t.node_visited = 0 and s.fkeyid not in (select objid from @worktable)
				select @rowcount2 = @@rowcount
			end

			if exists (select * from @worktable w, @tmp_table t where w.objid = t.objid and (t.article_filter_category & @injoinfilters) = @injoinfilters)
			begin
				-- all articles in @worktable have a direct or indirect relation with at least one object in the join articles category.
				update @tmp_table set node_visited = 1, has_relation_with_joinarticles = 1 
				from @tmp_table t, @worktable w 
				where w.objid = t.objid 
			end
			else
			begin
				-- none of the articles in @worktable has any relation (direct or indirect) with any article in the join articles category.			
				update @tmp_table set node_visited = 1, has_relation_with_joinarticles = 0 
				from @tmp_table t, @worktable w 
				where w.objid = t.objid 
			end

			-- process more unvisited articles from @tmp_table
			select @objid_looper = min(objid) from @tmp_table where objid > @objid_looper and node_visited = 0
		end
	end
	
	/* If the 7.0 merge agent is making this call then we need to make sure that the CLSID of the sp resolver is the old one and not the new one */
	if @compatibility_level = 7000000
		begin
			begin transaction 
				update sysmergearticles set resolver_clsid = '{6F31CE30-7BE4-11d1-9B0A-00C04FC2DEB3}' where article_resolver = 'Microsoft SQLServer Stored Procedure Resolver'
				select name, t.tablename, t.user_name, a.artid, pre_creation_command, a.pubid, nickname,
					column_tracking, status, resolver_clsid, conflict_script, conflict_table,
					insert_proc, update_proc, select_proc, destination_object, missing_col_count, 
					missing_cols, t.guid_col, 
					article_resolver, resolver_info, subset_filterclause, has_joins, excluded_col_count, 
					excluded_cols, destination_owner, identity_support, t.next_seed, t.range, t.threshold, 
					verify_resolver_signature, allow_interactive_resolver, fast_multicol_updateproc, check_permissions, t.article_filter_category, t.has_relation_with_joinarticles
				from sysmergearticles a, @tmp_table t
				where a.artid=t.artid and a.pubid=t.pubid order by a.nickname
			rollback transaction				
		end
	else
		begin
			select name, t.tablename, t.user_name, a.artid, pre_creation_command, a.pubid, nickname,
				column_tracking, status, resolver_clsid, conflict_script, conflict_table,
				insert_proc, update_proc, select_proc, destination_object, missing_col_count, 
				missing_cols, t.guid_col, 
				article_resolver, resolver_info, subset_filterclause, has_joins, excluded_col_count, 
				excluded_cols, destination_owner, identity_support, t.next_seed, t.range, t.threshold, 
				verify_resolver_signature, allow_interactive_resolver, fast_multicol_updateproc, check_permissions, t.article_filter_category, t.has_relation_with_joinarticles
			from sysmergearticles a, @tmp_table t
			where a.artid=t.artid and a.pubid=t.pubid order by a.nickname
		end			

	return (0)	
go
exec dbo.sp_MS_marksystemobject sp_MShelpmergearticles 
go
grant exec on dbo.sp_MShelpmergearticles to public
go

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MScreateretry')
drop procedure sp_MScreateretry
go
raiserror('Creating procedure sp_MScreateretry', 0,1)
GO

CREATE PROCEDURE sp_MScreateretry 
as
	declare @tname sysname
	declare @pname sysname
	declare @tempname sysname
	declare @guid uniqueidentifier
	declare @guidstr varchar(40)
	declare @retcode smallint

	/*
	** Check to see if current publication has permission
	*/
	if not exists (select * from dbo.sysmergepublications 
							where 1 = {fn ISPALUSER(pubid)})
	begin    
		RAISERROR (14126, 11, -1)
		return (1)
	end

	set @guid = newid()
	exec @retcode=sp_MSguidtostr @guid, @guidstr out
	if @retcode<>0 or @@ERROR<>0 return (1)
	set @tempname = '##retry_' + @guidstr

	exec @retcode = dbo.sp_MSuniquetempname @tempname, @tempname out
	if (@@error <> 0) OR @retcode <> 0
		begin
		RAISERROR(15001, 16, -1, 'sp_MSuniquetempname')
		return (1)
		end

	exec ('create table ' + @tempname + ' (tablenick int NOT NULL, rowguid uniqueidentifier ROWGUIDCOL default newid() not null, errcode int NOT NULL, errtext nvarchar(255) NULL, type tinyint NOT NULL)' )

	if (@@error <> 0)
		begin
		RAISERROR(15001, 16, -1, @tempname)
		return (1)
		end

	set	 @tname = @tempname
	set @tempname = '##insert_' + @guidstr

	exec @retcode = dbo.sp_MSuniquetempname @tempname, @tempname out
	if (@@error <> 0)
		begin
		RAISERROR(15001, 16, -1, 'sp_MSuniquetempname')
		return (1)
		end

	exec @retcode = dbo.sp_MSmaketempinsertproc @tname, @tempname
	if  @@ERROR <>0 or @retcode<>0 return (1)

	select @pname = @tempname
	select @tname, @pname
	return (0)	
go
exec dbo.sp_MS_marksystemobject sp_MScreateretry 
go
grant exec on dbo.sp_MScreateretry to public

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSdropretry')
drop procedure sp_MSdropretry

raiserror('Creating procedure sp_MSdropretry', 0,1)
GO

CREATE PROCEDURE sp_MSdropretry (@tname sysname, @pname sysname)
as
	declare @retcode int
	/*
	** To public
	*/
	if not exists (select * from dbo.sysmergepublications 
							where 1 = {fn ISPALUSER(pubid)})
	begin    
		RAISERROR (14126, 11, -1)
		return (1)
	end
	
	exec ('drop table ' + @tname)
	if @@ERROR <> 0 return(1)
	exec ('drop procedure ' + @pname)
	if @@ERROR <> 0 return(1)
	return (0)	
go
exec dbo.sp_MS_marksystemobject sp_MSdropretry 
go
grant exec on dbo.sp_MSdropretry to public
go


if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSenumretries')
drop procedure sp_MSenumretries

raiserror('Creating procedure sp_MSenumretries', 0,1)
GO

CREATE PROCEDURE sp_MSenumretries
	(@tname nvarchar(386),
	 @maxrows int,
	 @tablenick int,
	 @rowguid uniqueidentifier)
as
	declare @tnstring nvarchar(12)
	declare @rgstring nvarchar(38)
	declare @retcode int

	/*
	** do permission checking
	*/
	exec @retcode=sp_MSreplcheck_connection @tablenick = @tablenick
	if @retcode<>0 or @@ERROR<>0 return (1)

	/*
	** Modify temp table, granted to public.
	*/

	declare @selecttop nvarchar(20)
	if (@maxrows = 0)
		set @selecttop= 'select'
	else
		set @selecttop= 'select top ' + cast(@maxrows as nvarchar(9)) 
	
	if (@tablenick < 1)
	begin
		execute (@selecttop + ' tablenick, rowguidcol, errcode, errtext, type from ' + @tname + 
				 ' order by tablenick, rowguidcol')
		IF @@ERROR <>0 RETURN (1) 		 
	end
	else
	begin
		set @tnstring = convert(nchar, @tablenick)
		set @rgstring = '''' + convert(nchar(36), @rowguid) + ''''
		execute (@selecttop + ' tablenick, rowguidcol, errcode, errtext, type from ' + @tname + 
				' where (tablenick = ' + @tnstring + ' and  rowguidcol > ' + @rgstring + ') or
					 tablenick > ' + @tnstring + ' order by tablenick, rowguidcol' )
		if @@ERROR <> 0 RETURN (1)
	end
	return (0)	
go
exec dbo.sp_MS_marksystemobject sp_MSenumretries
go
grant exec on dbo.sp_MSenumretries to public

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSdeletepushagent')
drop procedure sp_MSdeletepushagent
go
raiserror('Creating procedure sp_MSdeletepushagent', 0,1)
GO

/*
** This procedure is obselete for dropping push agent at distribution database.
** If we were to recover, don't use server id as parameter for RPC into distributor.
** Use server name instead.
*/
CREATE PROCEDURE sp_MSdeletepushagent (
	@publisher		 	sysname,
	@publisher_db	 	sysname,
	@publication 		sysname,
	@subscriber	 		sysname,
	@subscriber_db	 	sysname
	) AS

declare @distributor 	sysname
declare @distribdb		sysname
declare @pubid			uniqueidentifier
declare @distproc		nvarchar(300)
declare @pub_srvid		smallint
declare @sub_srvid		smallint
declare @retcode		smallint

/*
** Do permission checking
*/
exec @retcode=sp_MSreplcheck_publish
if @retcode<>0 or @@ERROR<>0 return (1)


EXECUTE @retcode = dbo.sp_helpdistributor @rpcsrvname = @distributor OUTPUT, @distribdb   = @distribdb OUTPUT
		IF @@ERROR <> 0 or @retcode <> 0
			return (1)
		
select @pub_srvid = srvid from master..sysservers where UPPER(srvname) = UPPER(@publisher) collate database_default
select @sub_srvid = srvid from master..sysservers where UPPER(srvname) = UPPER(@subscriber) collate database_default
select @pubid=pubid from sysmergepublications where name=@publication and UPPER(publisher)=UPPER(@publisher) and publisher_db=@publisher_db

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

EXEC @retcode = @distproc @pub_srvid, @publisher_db, @publication, @sub_srvid, @subscriber_db
	    IF @@ERROR <> 0 OR @retcode <> 0
			return (1)
return (0)
GO
exec dbo.sp_MS_marksystemobject sp_MSdeletepushagent
go
grant exec on dbo.sp_MSdeletepushagent to public
go

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSgetlastrecgen')
drop procedure sp_MSgetlastrecgen

raiserror('Creating procedure sp_MSgetlastrecgen', 0,1)
GO

CREATE PROCEDURE sp_MSgetlastrecgen
	(@repid uniqueidentifier)
as
	declare @pubid 		uniqueidentifier
	declare @pubname 	sysname
	declare @status 	int
	declare @retcode	int

	/*
	** do permission checking
	*/
	exec @retcode=sp_MSreplcheck_connection @repid = @repid
	if @retcode<>0 or @@ERROR<>0 return (1)

	if (not exists(select * from sysobjects where name = 'MSmerge_contents'))
		begin
		RAISERROR(20054 , 16, -1)
		return (1)
		end

	if (@repid is null)
		begin
			RAISERROR(14043, 16, -1, '@repid')
			return (1)
		end
    /*
    ** Check the publication status at the publisher - if it is inactive ( say because of a 
	** metadata cleanup event then return an error so the current merge will quit.
    */
	select @pubid = pubid from sysmergesubscriptions where subid = @repid
	if (@pubid is not null)
		begin
		    EXEC @retcode = dbo.sp_MScheckatpublisher @pubid
		    IF @retcode = 0
		        BEGIN

					select @pubname = name, @status = status from sysmergepublications where pubid = @pubid
					if @status = 0
						begin
							RAISERROR(21505, 16, -1, @pubname)
							return (1)
						end
		        END
		end

	select recgen, recguid from MSmerge_replinfo where repid = @repid
	return (0)
go
exec dbo.sp_MS_marksystemobject sp_MSgetlastrecgen
go
grant exec on dbo.sp_MSgetlastrecgen to public

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSgetlastsentrecgens')
drop procedure sp_MSgetlastsentrecgens

raiserror('Creating procedure sp_MSgetlastsentrecgens', 0,1)
GO

CREATE PROCEDURE sp_MSgetlastsentrecgens
	(@repid uniqueidentifier)
as
	declare @pubid 		uniqueidentifier
	declare @pubname 	sysname
	declare @status 	int
	declare @retcode	int

	if (@repid is null)
		begin
			RAISERROR(14043, 16, -1, '@repid')
			return (1)
		end
	/*
	** do permission checking
	*/
	exec @retcode=sp_MSreplcheck_connection @repid = @repid
	if @retcode<>0 or @@ERROR<>0 return (1)

    /*
    ** Check the publication status at the publisher - if it is inactive ( say because of a 
	** metadata cleanup event then return an error so the current merge will quit.
    */
	select @pubid = pubid from sysmergesubscriptions where subid = @repid
	if (@pubid is not null)
		begin
		    EXEC @retcode = dbo.sp_MScheckatpublisher @pubid
		    IF @retcode = 0
		        BEGIN

					select @pubname = name, @status = status from sysmergepublications where pubid = @pubid
					if @status = 0
						begin
							RAISERROR(21505, 16, -1, @pubname)
							return (1)
						end
		        END
		end

	select sentgen, sentguid, recgen, recguid from MSmerge_replinfo where repid = @repid
	return (0)
go
exec dbo.sp_MS_marksystemobject sp_MSgetlastsentrecgens
go
grant exec on dbo.sp_MSgetlastsentrecgens to public
go

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSgetlastsentgen')
drop procedure sp_MSgetlastsentgen

raiserror('Creating procedure sp_MSgetlastsentgen', 0,1)
GO

CREATE PROCEDURE sp_MSgetlastsentgen
	(@repid uniqueidentifier)
as
	declare @pubid 		uniqueidentifier
	declare @pubname 	sysname
	declare @status 	int
	declare @retcode	int

	/*
	** do permission checking
	*/
	exec @retcode=sp_MSreplcheck_connection @repid = @repid
	if @retcode<>0 or @@ERROR<>0 return (1)

	if (not exists(select * from sysobjects where name = 'MSmerge_contents'))
		begin
		RAISERROR(20054 , 16, -1)
		return (1)
		end

	if (@repid is null)
		begin
			RAISERROR(14043, 16, -1, '@repid')
			return (1)
		end
    /*
    ** Check the publication status at the publisher - if it is inactive ( say because of a 
	** metadata cleanup event then return an error so the current merge will quit.
    */
	select @pubid = pubid from sysmergesubscriptions where subid = @repid
	if (@pubid is not null)
		begin
		    EXEC @retcode = dbo.sp_MScheckatpublisher @pubid
		    IF @retcode = 0
		        BEGIN

					select @pubname = name, @status = status from sysmergepublications where pubid = @pubid
					if @status = 0
						begin
							RAISERROR(21505, 16, -1, @pubname)
							return (1)
						end
		        END
		end

	select sentgen, sentguid from MSmerge_replinfo where repid = @repid
	return (0)
go
exec dbo.sp_MS_marksystemobject sp_MSgetlastsentgen
go
grant exec on dbo.sp_MSgetlastsentgen to public
go

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSenumgenerations')
drop procedure sp_MSenumgenerations

raiserror('Creating procedure sp_MSenumgenerations', 0,1)
GO

CREATE PROCEDURE sp_MSenumgenerations
	(@genstart int, @pubid uniqueidentifier, @return_count_of_generations bit = 0)
as
	declare @retcode 	smallint
	declare @guidnull 	uniqueidentifier
	declare @generation_range TABLE (generation int NOT NULL, guidsrc uniqueidentifier NOT NULL, art_nick int NULL, guidlocal uniqueidentifier NOT NULL, pubid uniqueidentifier NULL, nicknames varbinary(1000) NOT NULL, okaytoskip bit NOT NULL)
	declare @status 	int
	declare @pubname 	sysname
	declare @rowcount	int
	set @guidnull = '00000000-0000-0000-0000-000000000000'

	/*
	** Check to see if current publication has permission
	*/
	if ({ fn ISPALUSER(@pubid) } <> 1)
	begin	
		RAISERROR (14126, 11, -1)
		return (1)
	end

	/*
	** To public
	*/
	
	if (@genstart is null)
		begin
		RAISERROR(14043, 16, -1, '@genstart')
		return (1)
		end
	if (@pubid is null)
		begin
		RAISERROR(14043, 16, -1, '@pubid')
		return (1)
		end
	if (not exists(select * from sysobjects where name = 'MSmerge_contents'))
		begin
		RAISERROR(20054 , 16, -1)
		return (1)
		end
    /*
    ** Check the publication status at the publisher - if it is inactive ( say because of a 
	** metadata cleanup event then return an error so the current merge will quit.
    */
    EXEC @retcode = dbo.sp_MScheckatpublisher @pubid
    IF @retcode = 0
        BEGIN

			select @pubname = name, @status = status from sysmergepublications where pubid = @pubid
			if @status = 0
				begin
					RAISERROR(21505, 16, -1, @pubname)
					return (1)
				end
        END

	insert into @generation_range (generation, guidsrc, art_nick, guidlocal, pubid, nicknames, okaytoskip)
	select DISTINCT generation, guidsrc, art_nick, guidlocal, pubid, nicknames, 0
	from dbo.MSmerge_genhistory 
	where generation >= @genstart 
	and (art_nick = 0 or art_nick is NULL or 
		art_nick in (select nickname from sysmergearticles 
		where pubid = @pubid)) 
	select @rowcount = @@rowcount

	if (@return_count_of_generations = 1)
		select @rowcount

	-- optimizations 
	-- 1. skip all rows that are for incomplete generations for articles that have no joins.
	-- 2. skip all rows for join articles if all the join article rows are incomplete generations.

	update @generation_range set okaytoskip = 1
	where art_nick is not null and art_nick <> 0
	and guidlocal = @guidnull 
	and 
	(
		(	
			-- 1. skip all rows that are for incomplete generations for articles that have no joins.
			not exists (select 1 from sysmergesubsetfilters where join_nickname = art_nick or art_nickname = art_nick)
		)
		or
		(	
			-- 2. skip all rows for join articles if all the rows for join and joined articles (i.e. the articles represented by join_nickname 
			--    and art_nickname in sysmergesubsetfilters) are incomplete generations.

			art_nick in (select join_nickname from sysmergesubsetfilters) 
			and not exists 
				(	
					select 1 from @generation_range b where b.guidlocal <> @guidnull 
					and exists (select 1 from sysmergesubsetfilters where join_nickname = b.art_nick or art_nickname = b.art_nick)
					and b.generation > @genstart
				) 
		)
	)
	
	select generation, guidsrc, art_nick, guidlocal, pubid, nicknames, okaytoskip from @generation_range
	ORDER BY generation ASC

	return (0)
go
exec dbo.sp_MS_marksystemobject sp_MSenumgenerations
go
grant exec on dbo.sp_MSenumgenerations to public
go

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSchecksnapshotstatus')
drop procedure sp_MSchecksnapshotstatus

raiserror('Creating procedure sp_MSchecksnapshotstatus', 0,1)
GO
CREATE PROCEDURE sp_MSchecksnapshotstatus
	@publication		sysname
AS
	declare @db_name		sysname
	declare @retention		int
	declare @snapshot_ready	int
	declare @pubid			uniqueidentifier
	declare @last_snapshot 	datetime
	
	select @snapshot_ready = NULL
	select @db_name = db_name()

	select @snapshot_ready=snapshot_ready, @retention=retention, @pubid=pubid
		from sysmergepublications where name=@publication and publisher=@@SERVERNAME and publisher_db=@db_name
	if @snapshot_ready is NULL
		select @snapshot_ready=snapshot_ready, @retention=retention, @pubid=pubid
			from sysmergepublications where name=@publication
	if @snapshot_ready is NULL
	begin
		raiserror (20026, 11, -1, @publication)
		return (1)
	end

	/*
	** Check to see if current publication has permission
	*/
	if ({ fn ISPALUSER(@pubid) } <> 1)
	begin	
		RAISERROR (14126, 11, -1)
		return (1)
	end
	
	if @snapshot_ready=1 and @retention>0
	begin
		select @last_snapshot=last_validated from sysmergesubscriptions where subid=@pubid
		if dateadd(day, @retention, @last_snapshot)<getdate()
			select @snapshot_ready=3 /* snapshot is obsolete */
	end
	select @snapshot_ready
go
exec dbo.sp_MS_marksystemobject sp_MSchecksnapshotstatus
go
grant exec on dbo.sp_MSchecksnapshotstatus to public


if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MScheckexistsgeneration')
drop procedure sp_MScheckexistsgeneration

raiserror('Creating procedure sp_MScheckexistsgeneration', 0,1)
GO

CREATE PROCEDURE sp_MScheckexistsgeneration
	(@genguid uniqueidentifier, @gen int output, @pubid uniqueidentifier = NULL)
as
	/*
	** Check input parameter
	*/
	if (@genguid is null)
		begin
			RAISERROR(14043, 16, -1, '@genguid')
			return (1)
		end
	/*
	** check permission
	*/
	if @pubid is not NULL
	begin
		if ({ fn ISPALUSER(@pubid) } <> 1)
		begin    
			RAISERROR (14126, 11, -1)
			return (1)

		end
	end
	else
	begin
		if not exists (select * from dbo.sysmergepublications 
								where 1 = {fn ISPALUSER(pubid)})
		begin
			RAISERROR (14126, 11, -1)
			return (1)
		end	
	end	
	/* Normal case : do not qualify by pubid */
	if (@pubid IS NULL)
		select @gen = max(generation) from dbo.MSmerge_genhistory where guidsrc = @genguid and guidlocal <> '00000000-0000-0000-0000-000000000000'
	else		
	/* If we are reinitializing from an alternate publisher, check if the subscription has received generations for the alternate publication */
		select @gen = max(generation) from dbo.MSmerge_genhistory where guidsrc = @genguid and guidlocal <> '00000000-0000-0000-0000-000000000000'
			and ((pubid = @pubid) or (pubid is null))
	IF @@ERROR <>0 return (1)
	return (0)
go
exec dbo.sp_MS_marksystemobject sp_MScheckexistsgeneration
go
grant exec on dbo.sp_MScheckexistsgeneration to public
GO

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSenumreplicas')
drop procedure sp_MSenumreplicas
raiserror('Creating procedure sp_MSenumreplicas', 0,1)
GO

CREATE PROCEDURE sp_MSenumreplicas (@pubid uniqueidentifier)
as
	declare @inactive tinyint

	/*
	** Check to see if current publication has permission
	*/
	if ({ fn ISPALUSER(@pubid) } <> 1)
	begin	
		RAISERROR (14126, 11, -1)
		return (1)
	end

	/*
	** To public
	*/

	select @inactive = 0
	if (not exists(select * from sysobjects where name = 'MSmerge_contents'))
	begin
		RAISERROR(20054 , 16, -1)
		return (1)
	end

	select subs.subid, replinfo.replnickname, subs.subscriber_type, subs.subscription_type, 
		subs.priority, replinfo.schemaversion, replinfo.schemaguid, subs.datasource_type, subs.datasource_path, servers.srvname,  
		subs.db_name, subs.status, subs.partnerid, subs.sync_type, subs.description, subs.pubid, subs.publication, subs.distributor
		from sysmergesubscriptions subs, MSmerge_replinfo replinfo, master..sysservers servers
			where replinfo.repid = subs.subid 
				and subs.srvid = servers.srvid
				and subs.status <> @inactive
				and subs.subscriber_type = 1
				order by convert(binary, subs.subid)
	IF @@ERROR <>0 return (1) 
	return (0)
go
exec dbo.sp_MS_marksystemobject sp_MSenumreplicas 
go
grant exec on dbo.sp_MSenumreplicas to public

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSenumpartialdeletes')
drop procedure sp_MSenumpartialdeletes
go
raiserror('Creating procedure sp_MSenumpartialdeletes', 0,1)
GO

CREATE PROCEDURE sp_MSenumpartialdeletes
	(@maxrows int,
	 @tablenick int,
	 @rowguid uniqueidentifier,
	 @tablenotbelongs nvarchar(255),
	 @bookmark int = NULL,
	 @specified_article_only int = 0)
as

	declare @tnstring 		nvarchar(12)
	declare @rgstring 		nvarchar(38)
	declare @lowrangestr 	nvarchar(12) 
	declare @highrangestr 	nvarchar(12) 
	declare @retcode		int
	
	/*
	** do permission checking
	*/
	exec @retcode=sp_MSreplcheck_connection @tablenick = @tablenick
	if @retcode<>0 or @@ERROR<>0 return (1)

	/*
	** To public.
	*/
	
	declare @selecttop nvarchar(20)
	if (@maxrows = 0)
		set @selecttop= 'select'
	else
		set @selecttop= 'select top ' + cast(@maxrows as nvarchar(9)) 

-- DEBUG	insert into MSmerge_debug
-- DEBUG		(okay, artnick, twhen, comment)
-- DEBUG		values (0, @tablenick, getdate(), 'sp_MSenumpartialdeletes; maxrows=' + convert(nvarchar, @maxrows) 
-- DEBUG		+ ', startguid=' + convert(nchar(36), @rowguid) + ', bookmark=' + convert(nvarchar, @bookmark)
-- DEBUG		+ ', specified_article_only=' + convert(nchar(1), @specified_article_only))

	if (@tablenick < 1)
	begin
		execute (@selecttop + ' tablenick, rowguid, COALESCE (generation, 0), lineage, type, bookmark from ' + @tablenotbelongs + '
					order by tablenick desc, rowguid asc' )
		IF @@ERROR <>0 
		begin
-- DEBUG	insert into MSmerge_debug (okay, twhen, comment)
-- DEBUG		values (1, getdate(), 'sp_MSenumpartialdeletes: select from notbelong failed')
			return (1) 	
		end

-- DEBUG	execute ('insert into MSmerge_debug (okay, artnick, rowguid, generation_new, lineage_new, twhen, comment) ' +
-- DEBUG				@selecttop + ' 0, tablenick, rowguid, COALESCE (generation, 0), lineage, getdate(), ''sp_MSenumpartialdeletes'' 
-- DEBUG				from ' + @tablenotbelongs + ' order by tablenick desc, rowguid asc' )
	end
	else 
	begin
		set @tnstring = convert(nchar, @tablenick)
		set @rgstring = '''' + convert(nchar(36), @rowguid) + ''''

		/* 
		** If a 7.0 SP1 Merge agent is calling this sp, it passed a valid bookmark parameter 
		** Use the bookmark column in the ##belongs_<> table to retrieve the next batch of rows
		*/
		if @bookmark is NOT NULL 
		begin
			set @lowrangestr = convert(nchar, @bookmark)
			set @highrangestr = convert(nchar, (@bookmark + @maxrows))

			if (@specified_article_only = 1)
			begin
				-- don't rely on @highrangestr since it will be equal to @lowrangestr if @maxrows=0.
				execute (@selecttop + ' tablenick, rowguid, COALESCE (generation, 0), lineage, type, bookmark from ' + @tablenotbelongs + '
							where  tablenick = ' + @tnstring + ' and 
							bookmark > ' + @lowrangestr + ' 
							order by rowguid' )

				if @@ERROR<>0
				begin
-- DEBUG			insert into MSmerge_debug (okay, twhen, comment)
-- DEBUG			values (2, getdate(), 'sp_MSenumpartialdeletes: select from notbelong failed')
					return (1)
				end						

-- DEBUG		execute ('insert into MSmerge_debug (okay, artnick, rowguid, generation_new, lineage_new, type, twhen, comment) ' + 
-- DEBUG					@selecttop + ' 0, tablenick, rowguid, COALESCE (generation, 0), lineage, type, getdate(), ''sp_MSenumpartialdeletes: @specified_article_only = 1''
-- DEBUG					from ' + @tablenotbelongs + '
-- DEBUG					where  tablenick = ' + @tnstring + ' and 
-- DEBUG					bookmark > ' + @lowrangestr + ' 
-- DEBUG					order by rowguid' )

			end
			else
			begin
				execute ('select tablenick, rowguid, COALESCE (generation, 0), lineage, type, bookmark from ' + @tablenotbelongs + '
							where  ((tablenick = ' + @tnstring + ' and 
							bookmark > ' + @lowrangestr + ' and bookmark <= ' + @highrangestr + ') or
							tablenick < ' + @tnstring + ') 
							order by tablenick desc, rowguid asc' )

				if @@ERROR<>0
				begin
-- DEBUG			insert into MSmerge_debug (okay, twhen, comment)
-- DEBUG				values (3, getdate(), 'sp_MSenumpartialdeletes: select from notbelong failed')
					return (1)
				end						

-- DEBUG		execute ('insert into MSmerge_debug (okay, artnick, rowguid, generation_new, lineage_new, type, twhen, comment)
-- DEBUG					select 0, tablenick, rowguid, COALESCE (generation, 0), lineage, type, getdate(), ''sp_MSenumpartialdeletes: @specified_article_only <> 1'' 
-- DEBUG					from ' + @tablenotbelongs + '
-- DEBUG					where  ((tablenick = ' + @tnstring + ' and 
-- DEBUG					bookmark > ' + @lowrangestr + ' and bookmark <= ' + @highrangestr + ') or
-- DEBUG					tablenick < ' + @tnstring + ') 
-- DEBUG					order by tablenick desc, rowguid asc' )
			end
		end

		/* 
		** Backward compatibilty mode : If a 7.0 Merge agent is calling this sp, it will pass a NULL bookmark parameter 
		** Use the rowguid and set rowcount to retrieve the next batch of rows
		*/
		else
		begin
			if (@specified_article_only = 1)
			begin
				execute (@selecttop + ' tablenick, rowguid, COALESCE (generation, 0), lineage, type from ' + @tablenotbelongs + '
							where  tablenick = ' + @tnstring + ' and rowguid > ' + @rgstring + '
							order by rowguid' )
				IF @@ERROR <>0 
				begin
-- DEBUG				insert into MSmerge_debug (okay, twhen, comment)
-- DEBUG				values (4, getdate(), 'sp_MSenumpartialdeletes: select from notbelong failed')
					return (1) 	
				end

-- DEBUG				execute ('insert into MSmerge_debug (okay, artnick, rowguid, generation_new, lineage_new, type, twhen, comment) ' + 
-- DEBUG					@selecttop + ' 0, tablenick, rowguid, COALESCE (generation, 0), lineage, type, getdate(), ''sp_MSenumpartialdeletes: @specified_article_only = 0''
-- DEBUG					from ' + @tablenotbelongs + '
-- DEBUG					where  tablenick = ' + @tnstring + ' and 
-- DEBUG					rowguid > ' + @rgstring + ' 
-- DEBUG					order by rowguid' )
			end
			else
			begin
				execute (@selecttop + ' tablenick, rowguid, COALESCE (generation, 0), lineage, type from ' + @tablenotbelongs + '
							where  ((tablenick = ' + @tnstring + ' and 
							rowguid > ' + @rgstring + ') or tablenick < ' + @tnstring + ') 
							order by tablenick desc, rowguid asc' )
				IF @@ERROR <>0 
				begin
-- DEBUG				insert into MSmerge_debug (okay, twhen, comment)
-- DEBUG				values (5, getdate(), 'sp_MSenumpartialdeletes: select from notbelong failed')
					return (1) 	
				end

-- DEBUG				execute ('insert into MSmerge_debug (okay, artnick, rowguid, generation_new, lineage_new, type, twhen, comment) ' + 
-- DEBUG					@selecttop + ' 0, tablenick, rowguid, COALESCE (generation, 0), lineage, type, getdate(), ''sp_MSenumpartialdeletes: @specified_article_only = 0''
-- DEBUG					from ' + @tablenotbelongs + '
-- DEBUG					where  ((tablenick = ' + @tnstring + ' and 
-- DEBUG					rowguid > ' + @rgstring + ') or tablenick < ' + @tnstring + ')
-- DEBUG					order by tablenick desc, rowguid asc' )
			end
		end
	end
	return (0)
go		
exec dbo.sp_MS_marksystemobject sp_MSenumpartialdeletes
go
grant exec on dbo.sp_MSenumpartialdeletes to public
go

if exists (select * from sysobjects
	where type = 'P '
			and name = 'sp_MSaddanonymousreplica')
	drop procedure sp_MSaddanonymousreplica
go
raiserror('Creating procedure sp_MSaddanonymousreplica',0,1)
GO
CREATE PROCEDURE sp_MSaddanonymousreplica
	(@publication 		sysname,
	 @publisher			sysname,
	 @publisherDB		sysname,
	 @anonymous			int,
	 @sync_type			int = 1, /* sync type is automatic by default */
	 @preexists			bit=0 OUTPUT
)
as
set nocount on
declare @retcode 				int
declare @subscription_type 		nvarchar(15)
declare @sync_typestr	 		nvarchar(15)
declare @sub_typeid				int
declare @pubid					uniqueidentifier

/*
** need more than PAL to do this.
*/
exec @retcode = sp_MSreplcheck_subscribe
if @@error<>0 or @retcode<>0
    return (1)

select @preexists = 0

if @anonymous = 1 
begin
	select @subscription_type = 'anonymous'
	select @sub_typeid	= 2 --subscription type value for anonymous
end
else 
begin
	select @subscription_type = 'local'
	select @sub_typeid = 1 --subscription type value for well known pull
end

if @sync_type = 1 select @sync_typestr = 'automatic'
	else select @sync_typestr = 'none'

-- this change is made so that we will try to add pull/anonymous subscriptions, even if
-- there is a already a subscription for that publication, however the subscription type
-- does not match. In this way we can prevent users from using incorrect subscription type
-- through command line or merge control. 

if exists (select * from sysobjects where name='sysmergepublications')
begin
	select @pubid=pubid from sysmergepublications 
		where name=@publication and UPPER(publisher)=UPPER(@publisher) and publisher_db = @publisherDB
	if @pubid is not NULL
	begin
		/* Check if theres is a subscriptions entry that matches the pubid and current subscription with the right type */
		if exists (select * from sysmergesubscriptions where pubid =@pubid  and UPPER(subscriber_server) = @@SERVERNAME and db_name = DB_NAME() and subscription_type=@sub_typeid)
			begin
				select @preexists = 1
				return (0)   -- replica exists. 
			end
		/* 
		** Check if theres is a subscriptions entry that matches the pubid and current subscription - 
		** If there is a match and the types are not the same, then return appropriate error.
		*/
		else if exists (select * from sysmergesubscriptions where pubid =@pubid  and UPPER(subscriber_server) = @@SERVERNAME and db_name = DB_NAME())
			begin
            RAISERROR (21500, 16, -1, @publication)    
			-- replica exists with wrong subscription type 
			select @preexists = 1

			return (1)   
			end
		
	end
end

-- Call this SP to add this replica
exec @retcode = dbo.sp_addmergepullsubscription 
	@publication = @publication,
	@publisher = @publisher,
	@publisher_db=@publisherDB,
	@subscriber_type =@subscription_type,
	@sync_type = @sync_typestr

IF @retcode<>0 or @@ERROR<>0 return (1)
return (0)
GO
exec dbo.sp_MS_marksystemobject sp_MSaddanonymousreplica
go
grant execute on dbo.sp_MSaddanonymousreplica to public
go 

--------------------------------------------------------------------------------
--. sp_helpreplfailovermode
--------------------------------------------------------------------------------

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_helpreplfailovermode')
drop procedure sp_helpreplfailovermode
go
raiserror('Creating procedure sp_helpreplfailovermode', 0,1)
go
create procedure sp_helpreplfailovermode (
	@publisher sysname,
	@publisher_db sysname,
	@publication sysname,
	@failover_mode_id tinyint = 0 output,
	@failover_mode nvarchar(10) = NULL output)
as
BEGIN
	declare	@subfound bit
			,@retcode int

	--
	-- security check
	--
	exec @retcode = sp_MSreplcheck_subscribe
	if @@error <> 0 or @retcode <> 0
	begin
		return (1)
	end
	--
	-- Check if the table MSsubscription_agents exists
	--
	if exists (select * from dbo.sysobjects 
					where name = 'MSsubscription_agents')
	begin
		--
		-- Only valid to get failover_mode, if failover_mode is failover (3,5)
		--
		if exists (select * from dbo.MSsubscription_agents
				where publisher = @publisher and 
					publisher_db = @publisher_db and
					publication = @publication and
					update_mode in (3,5) )
			select @subfound = 1
		else
			select @subfound = 0
	end
	else
		select @subfound = 0
	
	--
	-- Did we find an entry for initialized failover subscription
	--
	if (@subfound = 0)
	begin
		--
		-- Three possibilities : uninitialized subscription, non existent subscription
		-- or a non-mixed mode. Check If we have a PULL uninitialized subscription
		--
		if exists (select * from dbo.sysobjects 
						where name = 'MSreplication_subscriptions')
		begin
			if exists (select * from dbo.MSreplication_subscriptions
							where publisher = @publisher and 
								publisher_db = @publisher_db and
								publication = @publication and
								update_mode in (3,5) )
			begin
				--
				-- unitialized PULL subscription : return the default values
				--
				select @failover_mode_id = 0
				select @subfound = 1
			end
		end

		if (@subfound = 0)
		begin
			raiserror ('sp_helpreplfailovermode: Subscription for [%s].[%s].[%s] either has not been initialized or does not exist or does not support mixed mode',
						16, 1, @publisher, @publisher_db, @publication)
			return 1
		end
	end
	else
	begin
		--
		-- we found our subscription
		--
		select @failover_mode_id = failover_mode
		from dbo.MSsubscription_agents
		where publisher = @publisher and 
			publisher_db = @publisher_db and
			publication = @publication and
			update_mode in (3,5) 
			
		--
		-- initialize to 'immediate' if necessary
		--
		if @failover_mode_id not in (0, 1)
		begin
			raiserror ('sp_helpreplfailovermode: invalid failover_mode value %d for [%s].[%s].[%s], setting to 0 [immediate]', 
				11, 1, @failover_mode_id, @publisher, @publisher_db, @publication)
			update dbo.MSsubscription_agents 
			set failover_mode = 0
			where publisher = @publisher and 
				publisher_db = @publisher_db and
				publication = @publication and
				update_mode in (3,5) 

			select @failover_mode_id = 0
		end
	end

	--
	-- prepare the output
	--
	select @failover_mode = case 
		when @failover_mode_id = 0 then N'immediate'
		when @failover_mode_id = 1 then N'queued'
		end
		
	select N'failover_mode value'  = @failover_mode_id, 
			N'failover_mode' = @failover_mode

	--
	-- all done
	--
	return 0
END
go
exec dbo.sp_MS_marksystemobject sp_helpreplfailovermode
go
grant execute on dbo.sp_helpreplfailovermode to public
go

--------------------------------------------------------------------------------
--. sp_setreplfailovermode
--------------------------------------------------------------------------------

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_setreplfailovermode')
drop procedure sp_setreplfailovermode
go
raiserror('Creating procedure sp_setreplfailovermode', 0,1)
go
create procedure sp_setreplfailovermode (
	@publisher sysname,
	@publisher_db sysname,
	@publication sysname,
	@failover_mode nvarchar (10), 
	@override tinyint = 0)
as
begin
	set nocount on
	declare	@failover_mode_id tinyint, 
			@current_failover_mode_id bit,
			@retcode int,
			@queue_id sysname,
			@fqueue_empty int,
			@update_mode int,
			@queue_server sysname

	--
	-- security check
	--
	exec @retcode = sp_MSreplcheck_subscribe
	if @@error <> 0 or @retcode <> 0
	begin
		return (1)
	end
	--
	-- validate @failover_mode
	--
	if @failover_mode not in (N'immediate', N'sync', N'queued')
	begin
		raiserror (21184, 16, 1, '@failover_mode', 'immediate', 'sync', 'queued')
		return 1			
	end
	else
	begin
		if @failover_mode = 'immediate' or @failover_mode = 'sync'
			select @failover_mode_id = 0
		else if @failover_mode = 'queued'
			select @failover_mode_id = 1
	end

	--
	-- Only valid to get/set failover_mode, 
	-- if update_mode is failover (3,5)
	--
	select	@queue_id = queue_id, 
			@queue_server = queue_server,
			@current_failover_mode_id = failover_mode,
			@update_mode = update_mode
	from MSsubscription_agents
	where UPPER(publisher) = UPPER(@publisher) and 
		publisher_db = @publisher_db and
		publication = @publication and
		update_mode in (3,5)

	-- no such row exists
	if (@current_failover_mode_id is NULL)		
	begin
		raiserror (21185, 16, 1)
		return 1
	end

	-- should have a queue entry
	if (@queue_id is NULL)
	begin
		raiserror(21186, 16, 1, @publisher)
		return 1
	end

	--
	-- do the transition
	--
	if ((@current_failover_mode_id = 0 and @failover_mode_id = 0) or
			(@current_failover_mode_id = 1 and @failover_mode_id = 1))
	begin
		--
		-- Going from immediate to immediate, queued to queued is no-op
		--
		raiserror (21187, 16, 1)
	end
	else if (@current_failover_mode_id = 0 and @failover_mode_id = 1)
	begin
		--
		-- Going from immediate to queued : update MSsubscription_agents
		--
		update MSsubscription_agents
			set failover_mode = @failover_mode_id
			where UPPER(publisher) = UPPER(@publisher) and 
				publisher_db = @publisher_db and
				publication = @publication and
				update_mode in (3,5)

		raiserror (21188, 10, 1, 'immediate', 'queued')
	end
	else if (@current_failover_mode_id = 1 and @failover_mode_id = 0)
	begin
		--
		-- Going from queued to immediate : if override is not set
		-- then check if the queue is empty and then allow if empty.
		-- If override is set, just update MSsubscription_agents
		--
		if (@override = 0)
		begin
			if (@update_mode = 3)
			begin
				--
				-- MSMQ processing
				-- prefix the queue_id with queue server in direct format
				-- and then perform peek in the queue
				--
				select @queue_id = N'DIRECT=OS:' + @queue_server + N'\PRIVATE$\' + @queue_id
				
				exec @retcode = master.dbo.xp_peekqueue @queue_id, @fqueue_empty output, 0
				if (@@error != 0 or @retcode != 0)
				begin
					raiserror('sp_setreplfailovermode(debug): xp_peekqueue execution failed', 16, 1)
					return 1
				end

				if (@fqueue_empty != 1)
				begin
					raiserror(21189, 16, 1, @queue_id)
					return 1
				end
			end
			else
			begin
				--
				-- SQL Queue processing
				--
				if exists (select * from MSreplication_queue 
					where UPPER(publisher) = UPPER(@publisher) and 
							publisher_db = @publisher_db and
							publication = @publication )
				begin
					raiserror(21189, 16, 2, @queue_id)
					return 1
				end
			end
		end
		else
		begin
			raiserror(21190, 10, 1, 'queued', 'immediate')
		end

		--
		-- update MSsubscription_agents
		--
		update MSsubscription_agents
			set failover_mode = @failover_mode_id
			where publisher = @publisher and 
				publisher_db = @publisher_db and
				publication = @publication and
				update_mode in (3,5)

		raiserror (21188, 10, 1, 'queued', 'immediate')
	end

	-- All done
	return 0
end
go
exec dbo.sp_MS_marksystemobject sp_setreplfailovermode
go
grant execute on dbo.sp_helpreplfailovermode to public
go

--------------------------------------------------------------------------------
--. sp_addqueued_artinfo
--------------------------------------------------------------------------------

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_addqueued_artinfo')
drop procedure sp_addqueued_artinfo
go
raiserror('Creating procedure sp_addqueued_artinfo', 0,1)
go
CREATE PROCEDURE sp_addqueued_artinfo (
	@artid				int
	,@article			sysname
	,@publisher 		sysname
	,@publisher_db		sysname
	,@publication		sysname
	,@dest_table		sysname
	,@owner				sysname
	,@cft_table			sysname
	,@columns			binary(32))
AS
BEGIN
	set nocount on
	declare 	@agent_id int
				,@update_mode int
				,@retcode int

	--
	-- security check
	--
	exec @retcode = sp_MSreplcheck_subscribe
	if @@error <> 0 or @retcode <> 0
	begin
		return (1)
	end
	--
	-- Create system table MSsubscription_articles if it does not exist
	--
	IF NOT EXISTS (SELECT * FROM sysobjects WHERE 
		type = 'U' AND name = 'MSsubscription_articles')
	BEGIN
		CREATE TABLE dbo.MSsubscription_articles
		(
			agent_id		int NOT NULL,		-- related entry in MSsubscription_agents
			artid			int NOT NULL,		-- article id
			article			sysname,			-- article name
			dest_table		sysname,			-- destination table
			owner			sysname,
			cft_table		sysname,			-- conflict table
			columns			binary(32)
		)
		IF (@@ERROR != 0)
		begin
			raiserror('Debug:sp_addqueued_artinfo - create MSsubscription_articles failed', 16, 1)
			return (1)
		end

		CREATE UNIQUE CLUSTERED INDEX ucMSsubscription_articles ON dbo.MSsubscription_articles(agent_id, artid)
		IF (@@ERROR != 0)
		begin
			raiserror('Debug:sp_addqueued_artinfo - create index for MSsubscription_articles failed', 16, 1)
			return (1)
		end
		
		EXEC dbo.sp_MS_marksystemobject 'MSsubscription_articles'
	END

	--
	-- insert the row for the given article
	--
	select @agent_id = id, @update_mode = update_mode
	from MSsubscription_agents 
	where UPPER(publisher) = UPPER(@publisher) and 
		publisher_db = @publisher_db and 
		publication = @publication 

	if (@agent_id IS NULL)
	begin
		raiserror('Debug:sp_addqueued_artinfo - agent_id is null for [%s].[%s].[%s]', 
			16, 1, @publisher, @publisher_db, @publication)
		return (1)
	end

	-- If the subscription is read only or immediate, no need to article info.
	if @update_mode in (0,1)
		return 0

	--
	-- If MSMQ Queued mode - check if the subscriber is compliant for MSMQ 2.0
	--
	if (@update_mode in (2,3))
	begin
		if ((platform() & 0x1) != 0x1)
		begin
			--
			-- Win 9X platform
			--
			raiserror(21334, 16, 1, '2.0')
			return (1)
		end

		--
		-- Now we use xp_MSver to detect NT OS version
		-- MSMQ subscription only allowed for platforms that support MSMQ 2.0
		-- version 5.0.2195 or higher
		--
		create table #tosversion ( propid int, propname sysname collate database_default, value int, charvalue nvarchar(255) collate database_default)
		insert into #tosversion (propid, propname, value, charvalue)
			exec master.dbo.xp_msver N'WindowsVersion'

		declare @vervalue int
			,@lobyte tinyint
			,@hibyte tinyint
			,@loword smallint
			,@hiword smallint

		--
		-- low order byte of low order word = OSmajor, high order byte of low order word = OSminor
		-- high order word = OSbuild
		--
		select @vervalue = value from #tosversion where propname = N'WindowsVersion'
		select @loword = (@vervalue & 0xffff)
				,@hiword = (@vervalue / 0x10000) & 0xffff
		select @lobyte = @loword & 0xff
				,@hibyte = (@loword / 100) & 0xff
		drop table #tosversion

		--
		-- check for OS major version
		--
		if (@lobyte < 5)
		begin
			raiserror(21334, 16, 2, '2.0')
			return (1)
		end

		--
		-- check for OS build version
		--
		if (@lobyte = 5 and @hiword < 2195)
		begin
			raiserror(21334, 16, 3, '2.0')
			return (1)
		end
	end

	--
	-- Check for owner - use current user for NULL value
	--
	if (@owner IS NULL or lower(@owner) = N'null' collate database_default)
		select @owner = user_name()

	if exists (select * from MSsubscription_articles where agent_id = @agent_id and artid = @artid)
		delete MSsubscription_articles where agent_id = @agent_id and artid = @artid
		
	insert into MSsubscription_articles(agent_id, artid, article, dest_table, owner, cft_table, columns)
		values (@agent_id, @artid, @article, @dest_table, @owner, @cft_table, @columns)

	IF (@@ERROR != 0)
	begin
		raiserror('Debug:sp_addqueued_artinfo - insert failed', 16, 1)
		return (1)
	end

	--
	-- Do the queue initialization here
	-- this way we can initialize Snapshot/Logbased queued tran from one place
	--
	exec @retcode = dbo.sp_MSreset_queue @publisher, @publisher_db, @publication, @artid
	IF (@retcode != 0 or @@ERROR != 0)
	begin
		raiserror('Debug:sp_addqueued_artinfo - sp_MSreset_queue failed', 16, 1)
		return (1)
	end
	
	--
	-- all done
	--
	return 0	
END
go
exec dbo.sp_MS_marksystemobject sp_addqueued_artinfo
go
grant execute on dbo.sp_addqueued_artinfo to public
go

--------------------------------------------------------------------------------
--. sp_MSreset_queued_reinit
--------------------------------------------------------------------------------

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSreset_queued_reinit')
drop procedure sp_MSreset_queued_reinit
go
raiserror('Creating procedure sp_MSreset_queued_reinit', 0,1)
go
create procedure sp_MSreset_queued_reinit (
	@subscriber      sysname,					-- subscriber server name
	@subscriber_db   sysname,					-- subscribing database name. 
	@artid	int)				   				-- article id
as
begin
	set nocount on
	declare @srvid int
		,@retcode int
		,@publication sysname
	--
	-- PAL security check
	-- Get publication name using artid
	--
	select @publication = p.name
	from syspublications p join sysarticles a on p.pubid = a.pubid
	where a.artid = @artid
	if (@publication is null)
	begin
		return 1
	end
	exec @retcode = dbo.sp_MSreplcheck_pull @publication = @publication
	if @@error <> 0 or @retcode <> 0
	begin
		return (1)
	end
	select @srvid = srvid from master.dbo.sysservers where UPPER(srvname) = UPPER(@subscriber) collate database_default
	--
	-- set the reinit flag
	--
	update dbo.syssubscriptions
	set queued_reinit = 0
	where 
		artid = @artid 
		and srvid = @srvid
		and dest_db = @subscriber_db
	if (@@error != 0)
	    return 1

	-- all done
	return 0
end
go
exec dbo.sp_MS_marksystemobject sp_MSreset_queued_reinit
go
grant execute on dbo.sp_MSreset_queued_reinit to public
go
--------------------------------------------------------------------------------
--. fn_escapecmdshellsymbols
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'FN'
    and name = N'fn_escapecmdshellsymbols' )
drop function system_function_schema.fn_escapecmdshellsymbols
go
raiserror('Creating function fn_escapecmdshellsymbols', 0,1) with nowait
go
--
-- Name: fn_escapecmdshellsymbols
--
-- Descriptions: This function returns an escaped version of a given string
--               with carets ('^') added in front of all the special 
--               command shell symbols. In the W2K online help (Start->
--               Help->Index->Command Reference->Command Symbols...), the 
--               following symbols are listed as special command symbols:
--                 (N'%', N'<', N'>', N'|', N'&', N'(', N')', N'^', N'"')
--
-- Parameter: @command_string nvarchar(4000)
-- 
-- Example: select fn_escapecmdshellsymbols(N'The % quick < brown > dog | jumps & over ( the ) lazy ^ fox.') 
--             will return 
--    
--    The ^% quick ^< brown ^> dog ^| jumps ^& over ^( the ^) lazy ^^ fox
--
-- Security: This function is granted to public
--
create function system_function_schema.fn_escapecmdshellsymbols(
    @command_string nvarchar(4000)
    ) returns nvarchar(4000)
as
begin
    declare @escaped_command_string nvarchar(4000),
            @curr_char nvarchar(1),
            @curr_char_index int    
    select @escaped_command_string = N'',
           @curr_char = N'', 
           @curr_char_index = 1
    while @curr_char_index <= len(@command_string)
    begin
        select @curr_char = substring(@command_string, @curr_char_index, 1) 
        if @curr_char in (N'%', N'<', N'>', N'|', N'&', N'(', N')', N'^', N'"')
        begin
            select @escaped_command_string = @escaped_command_string + N'^'
        end
        select @escaped_command_string = @escaped_command_string + @curr_char
        select @curr_char_index = @curr_char_index + 1 
    end
    return @escaped_command_string
end
go
grant execute on system_function_schema.fn_escapecmdshellsymbols to public

--------------------------------------------------------------------------------
--. fn_escapecmdshellsymbolsremovequotes
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'FN'
    and name = N'fn_escapecmdshellsymbolsremovequotes' )
    drop function system_function_schema.fn_escapecmdshellsymbolsremovequotes

raiserror('Creating function fn_escapecmdshellsymbolsremovequotes', 0,1) with nowait
go
--
-- Name: fn_escapecmdshellsymbolsremovequotes
--
-- Descriptions: The only difference between this function and 
--               fn_escapecmdshellsymbols is that "'s are removed from the 
--               resulting command line.
--
-- Parameter: @command_string nvarchar(4000)
-- 
-- Security: This function is granted to public
--
create function system_function_schema.fn_escapecmdshellsymbolsremovequotes(
    @command_string nvarchar(4000)
    ) returns nvarchar(4000)
as
begin
    declare @escaped_command_string nvarchar(4000),
            @curr_char nvarchar(1),
            @curr_char_index int    
    select @escaped_command_string = N'',
           @curr_char = N'', 
           @curr_char_index = 1
    while @curr_char_index <= len(@command_string)
    begin
        select @curr_char = substring(@command_string, @curr_char_index, 1) 
        if @curr_char in (N'%', N'<', N'>', N'|', N'&', N'(', N')', N'^')
        begin
            select @escaped_command_string = @escaped_command_string + N'^'
        end
        if @curr_char <> '"'
        begin
            select @escaped_command_string = @escaped_command_string + @curr_char
        end
        select @curr_char_index = @curr_char_index + 1 
    end
    return @escaped_command_string
end
go
grant execute on system_function_schema.fn_escapecmdshellsymbolsremovequotes to public

--------------------------------------------------------------------------------
--. sp_MSget_file_existence
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSget_file_existence')
drop procedure sp_MSget_file_existence
go
print ''
print 'Creating procedure sp_MSget_file_existence'
go
create procedure sp_MSget_file_existence (
@filename nvarchar(260),
@exists bit = 0 output
)
AS
    SET NOCOUNT ON

    DECLARE @command nvarchar(512)
    DECLARE @retcode int
	declare @echo_text nvarchar(20)

	select @echo_text = 'file_exists'

    /*
    ** The return code from xp_cmdshell is not a reliable way to check whether the file exists or
    ** not. It is always 0 on Win95 as long as xp_cmdshell succeeds.
    */  

	select @command = N'if exist "' + fn_escapecmdshellsymbolsremovequotes(@filename) collate database_default + N'" echo ' + @echo_text

    create table #text_ret(cmdoutput nvarchar(20) collate database_default null)

    insert into #text_ret exec @retcode = master..xp_cmdshell @command
	if @@error <> 0 or @retcode <> 0
		return 1

    if exists (select * from #text_ret where ltrim(rtrim(cmdoutput)) = @echo_text)
        select @exists = 1
    else 
        select @exists = 0

    drop table #text_ret
go
EXEC dbo.sp_MS_marksystemobject sp_MSget_file_existence
GO

--------------------------------------------------------------------------------
--. sp_replicationoption
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_replicationoption')
drop procedure sp_replicationoption
go
raiserror('Creating procedure sp_replicationoption', 0,1)
go

CREATE PROCEDURE sp_replicationoption (
@optname		sysname,
@value			nvarchar(5),
@security_mode	int = 0,
@login			sysname = 'sa',
@password		sysname = NULL,
@reserved       nvarchar(20) = NULL
)
AS
	DECLARE @optbit bit
	DECLARE @osql_path nvarchar(260)
    DECLARE @osql_cmd1 nvarchar (255)
    DECLARE @osql_cmd_full nvarchar (255)
	DECLARE @osql_for_nt int

	DECLARE @install_path nvarchar (255)

    DECLARE @retcode int
	DECLARE @undo_install nvarchar(20)
	DECLARE @no_scripts nvarchar(10)
    DECLARE @platform_nt binary
    
    SELECT @platform_nt = 0x1

    if	is_srvrolemember('sysadmin') <> 1
        BEGIN
            RAISERROR (15232, 14, -1)
            RETURN (1)
        END
	
	SELECT @no_scripts = 'no_scripts'

	SELECT @undo_install = 'undo_install'

	IF db_name() <> 'master'
	BEGIN
		RAISERROR(5001, 16,-1)
		GOTO FAILURE
	END

	IF LOWER(@optname collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('transactional','merge')
	BEGIN
		RAISERROR(21014, 16, -1)
		GOTO FAILURE
	END
	IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true','false')
	BEGIN
		RAISERROR(14137,16,-1)
		GOTO FAILURE
	END
	
	IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'
		SELECT @optbit = 1
	ELSE
		SELECT @optbit = 0

	/*
	** Check if the option is set as required already
	*/
	IF @reserved <> @undo_install AND EXISTS (SELECT * FROM MSreplication_options
		WHERE	optname  = @optname
		AND		value = @optbit)
	BEGIN
		IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'
			RAISERROR (21015, 10, -1, @optname)		
		ELSE
			RAISERROR (21016, 10, -1, @optname)
		GOTO FAILURE
	END

	/* Install replication */
	IF @optbit = 1
	BEGIN

		IF LOWER(@reserved collate SQL_Latin1_General_CP1_CS_AS) = @no_scripts
			GOTO NO_SCRIPTS

		-- Set the flag for platform
		IF (( platform() & @platform_nt = @platform_nt ))
			SELECT @osql_for_nt = 1
		ELSE
			SELECT @osql_for_nt = 0

		/* 
		** Get installation path -- osql client (TOOLS) path
		*/
		EXECUTE @retcode = master.dbo.sp_MSgettools_path @osql_path OUTPUT
		IF ( @@ERROR <> 0 ) OR ( @retcode <> 0 ) or ( @osql_path is NULL ) or ( @osql_path = '' )
		BEGIN
			GOTO FAILURE       
		END

		/* 
		** Get installation path -- instance specific (INSTALL) directory
		*/
		exec @retcode = master.dbo.sp_MSget_setup_paths
			@sql_path = @install_path output
		IF @@ERROR<> 0 OR @retcode <> 0 or @install_path is NULL or @install_path=''
			BEGIN
				GOTO FAILURE       
			END

		/* 
		** Install replcom.sql and repltran.sql
		*/
		IF @security_mode = 1
		begin
			SELECT @osql_cmd1 = '"' + fn_escapecmdshellsymbolsremovequotes(@osql_path) collate database_default + '\binn\osql" -E ' 
			if serverproperty('instancename') is not null
				SELECT @osql_cmd1 = @osql_cmd1 + ' -S"' + @@SERVERNAME + '" '
		end
		ELSE
			-- cannot specify -S w/ -E for local execution, SID does not map
			SELECT @osql_cmd1 = '"' + fn_escapecmdshellsymbolsremovequotes(@osql_path) collate database_default + '\binn\osql" -U"' + fn_escapecmdshellsymbols(@login) collate database_default + '" -P"' + 
				fn_escapecmdshellsymbols(isnull(@password,'')) collate database_default + '" -S"' + fn_escapecmdshellsymbols(@@SERVERNAME) collate database_default + '" '

		select @osql_cmd1 = @osql_cmd1 + '-l30 -t30 '

		-- Install replcom.sql 
		-- Only apply replcom.sql if it was not applied before.
		-- '-b' option will make osql stop at errors and return error code
		-- We must use this option.
		IF NOT EXISTS (SELECT * FROM MSreplication_options
			WHERE value = 1)
		BEGIN
			-- Initialize the Command
			IF (@osql_for_nt = 1)
				SELECT @osql_cmd_full = '" '
			ELSE
				SELECT @osql_cmd_full = ' '

			SELECT @osql_cmd_full = @osql_cmd_full +
				@osql_cmd1 + 
				' -dmaster' +  ' -b ' +
				' -i' + '"' + fn_escapecmdshellsymbolsremovequotes(@install_path) collate database_default + '\install\replcom.sql"' + 
				' -o' + '"' + fn_escapecmdshellsymbolsremovequotes(@install_path) collate database_default + '\install\replcom.out"' 

			IF (@osql_for_nt = 1)
				SELECT @osql_cmd_full = @osql_cmd_full + ' "'

			EXEC @retcode = master..xp_cmdshell @osql_cmd_full
			IF @@ERROR<> 0 OR @retcode <> 0
			BEGIN
				RAISERROR (14113, 16, -1, @osql_cmd_full, 'replcom.out')
				GOTO UNDO_INSTALL     
			END
		END

		IF LOWER(@optname collate SQL_Latin1_General_CP1_CS_AS) = 'transactional'
		BEGIN
			-- Install repltran.sql
			IF (@osql_for_nt = 1)
				SELECT @osql_cmd_full = '" '
			ELSE
				SELECT @osql_cmd_full = ' '

			SELECT @osql_cmd_full = @osql_cmd_full +
				@osql_cmd1 + 
				' -dmaster' +  ' -b ' +
				' -i' + '"' + fn_escapecmdshellsymbolsremovequotes(@install_path) collate database_default + '\install\repltran.sql"' + 
				' -o' + '"' + fn_escapecmdshellsymbolsremovequotes(@install_path) collate database_default + '\install\repltran.out"' 

			IF (@osql_for_nt = 1)
				SELECT @osql_cmd_full = @osql_cmd_full + ' "'

			EXEC @retcode = master..xp_cmdshell @osql_cmd_full
			IF @@ERROR<> 0 OR @retcode <> 0
			BEGIN
				RAISERROR (14113, 16, -1, @osql_cmd_full, 'repltran.out')
				GOTO UNDO_INSTALL     
			END

		END

		IF LOWER(@optname collate SQL_Latin1_General_CP1_CS_AS) = 'merge'
		BEGIN
			-- Install replmerg.sql 
			IF (@osql_for_nt = 1)
				SELECT @osql_cmd_full = '" '
			ELSE
				SELECT @osql_cmd_full = ' '

			SELECT @osql_cmd_full = @osql_cmd_full +
				@osql_cmd1 + 
				' -dmaster' + ' -b ' +
				' -i' + '"' + fn_escapecmdshellsymbolsremovequotes(@install_path) collate database_default + '\install\replmerg.sql"' + 
				' -o' + '"' + fn_escapecmdshellsymbolsremovequotes(@install_path) collate database_default + '\install\replmerg.out"' 
			
			IF (@osql_for_nt = 1)
				SELECT @osql_cmd_full = @osql_cmd_full + ' "'

			EXEC @retcode = master..xp_cmdshell @osql_cmd_full 
			
			IF  @@ERROR<> 0 OR @retcode <> 0
			BEGIN
				RAISERROR (14113, 16, -1, @osql_cmd_full, 'replmerg.out')
				GOTO UNDO_INSTALL     
			END
		END

NO_SCRIPTS:

		UPDATE MSreplication_options SET value = @optbit
			WHERE optname = @optname
		IF @@ERROR <> 0 
		BEGIN
			GOTO UNDO_INSTALL    
		END

	END
	/* Uninstall replication */
	ELSE
	BEGIN

		/* 
		** Make sure no distributor installed before dropping 
		** replication stored procedures
		*/
		IF EXISTS (SELECT * FROM master..sysservers
              WHERE  srvstatus & 8 <> 0)
		BEGIN
			RAISERROR (21021, 16, -1)
			RETURN(1)
		END

		UPDATE MSreplication_options SET value = @optbit
			WHERE optname = @optname
		IF @@ERROR <> 0 
		BEGIN
			GOTO FAILURE
		END

		/* 
		*********** Do not drop replication stored procs anymore.

		IF LOWER(@optname collate SQL_Latin1_General_CP1_CS_AS) = 'transactional'
		BEGIN
			if exists (select * from sysobjects
				where type = 'P '
						and name = 'sp_MSdrop_repltran')
			begin
				exec @retcode = dbo.sp_MSdrop_repltran
				if @@ERROR = 0 and @retcode = 0
					drop procedure sp_MSdrop_repltran
			end
		END

		IF LOWER(@optname collate SQL_Latin1_General_CP1_CS_AS) = 'merge'
		BEGIN
			if exists (select * from sysobjects
				where type = 'P '
						and name = 'sp_MSdrop_replmerg')
			begin
				exec @retcode = dbo.sp_MSdrop_replmerg
				if @@ERROR = 0 and @retcode = 0
					drop procedure sp_MSdrop_replmerg
			end
		END

		IF NOT EXISTS (SELECT * FROM MSreplication_options
			WHERE value = 1)
		BEGIN
			if exists (select * from sysobjects
				where type = 'P '
						and name = 'sp_MSdrop_replcom')
			begin
				exec @retcode = dbo.sp_MSdrop_replcom
				if @@ERROR = 0 and @retcode = 0
					drop procedure sp_MSdrop_replcom
			end
		END

		*/
	END

	RETURN(0)

UNDO_INSTALL:
	/* This is needed to drop the stored procedures that were created. */
	EXEC dbo.sp_replicationoption @optname =  @optname, 
		@value = 'false', @reserved = @undo_install

FAILURE:
	RETURN(1)
GO
exec dbo.sp_MS_marksystemobject sp_replicationoption
go
grant execute on dbo.sp_replicationoption to public
go

--------------------------------------------------------------------------------
--. sp_vupgrade_replication
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_vupgrade_replication')
drop procedure sp_vupgrade_replication
go
raiserror('Creating procedure sp_vupgrade_replication', 0,1)
GO

create procedure sp_vupgrade_replication ( @login sysname = N'sa', @password sysname = N'', @ver_old int = 517, @force_remove tinyint = 0, @security_mode bit = 0 )
as
begin
/* 
 * Dispatcher proc for handling schema and metadata changes during setup initiated version upgrade 
 * for replication components. Any schema changes to replication system tables may require modifications 
 * here to maintain upgrade path. All modifications called in these procs are within "if exists" checks
 * making them repeatable for debugging and to support incremental upgrades (e.g. Beta 1 to Beta 2 to RTM)
 * 
 * If server is a distributor, run new instdist.sql against all distribution dbs.
 *
 * This proc gets called by setup at the end of an install over an existing version.
*/

	set nocount on 

	declare @dbname sysname
	declare @has_dbaccess bit
	declare @install_path nvarchar(255)
	declare @osql_path nvarchar(260)
	declare @osql_cmd nvarchar(512)
	declare @osql_for_nt int
	declare @retcode int
	declare @platform_nt binary
	declare @db_distbit int
	declare @ver_min			int
	declare @ver_retention	int 

	select @db_distbit = 16
	select @platform_nt = 0x1
	select @ver_retention = 576 --build # on 9/17

	-- raiserror('sp_vupgrade_replication', 0,1) with nowait

	/*
	 * obsolete check; ver check was to prevent repl upgrade from
	 * versions prior to SQL7.0 Beta 3; check is removed by setting @ver_min = -1
	*/
	select @ver_min= -1 -- change if later wish to support a minimum upgrade version
	if ( @ver_old < @ver_min ) or ( @force_remove = 1 )
		exec dbo.sp_removesrvreplication
	else
	begin

		/* 
		 * always need to run instdist.sql to update distribution databases on a distributor
		 * setup must restart in non-single user mode so we can shell out to run instdist.sql scripts
		*/
		if exists( select * from master..sysdatabases where category & @db_distbit = @db_distbit )
		begin

			/* 
			** Get installation path -- osql client (TOOLS) path
			*/
			EXECUTE @retcode = master.dbo.sp_MSgettools_path @osql_path OUTPUT
			IF ( @@ERROR <> 0 ) OR ( @retcode <> 0 ) or ( @osql_path is NULL ) or ( @osql_path = '' )
			BEGIN
				RETURN (1)
			END

			/* 
			** Get installation path -- instance specific (INSTALL) directory
			*/
			exec @retcode = master.dbo.sp_MSget_setup_paths
				@sql_path = @install_path output
			IF @@ERROR<> 0 OR @retcode <> 0 or @install_path is NULL or @install_path=N''
			BEGIN
				RETURN (1)
			END

			-- Set the flag for platform
			if (( platform() & @platform_nt = @platform_nt ))
				select @osql_for_nt = 1
			else
				select @osql_for_nt = 0

			declare cur_distdb CURSOR LOCAL FAST_FORWARD for 
				select name, has_dbaccess(name) from master..sysdatabases 
					where category & @db_distbit = @db_distbit
				for read only
			
			open cur_distdb
			fetch cur_distdb into @dbname, @has_dbaccess
			while ( @@fetch_status <> -1 )
			begin

				-- if distribution database is available upgrade it; if offline error out
				if ( @has_dbaccess = 1 )
				begin
					raiserror( 21374, 0, 1, @dbname) with nowait

					/*
					 * Format osql cmd line appropriate for security mode and OS to run instdist.sql against
					 * each distribution database. Instdist.sql will recompile procs and will also do some
					 * schema and metadata upgrade of changed replication tables. Query timeout increased to
					 * make enough time for alter tables in instdist.sql run for upgrade to complete.
					*/
					if ( @osql_for_nt = 1 )
						select @osql_cmd = N'" "'
					else
						select @osql_cmd = N' "'
					
					-- Cannot specify -S w/ -E for local execution, SID does not map (nofix)
					if ( @security_mode = 1 and @osql_for_nt = 1 )
					begin
						select @osql_cmd = @osql_cmd + fn_escapecmdshellsymbolsremovequotes(@osql_path) collate database_default + '\binn\osql" -E '
						if serverproperty('instancename') is not null
							select @osql_cmd = @osql_cmd + ' -S"' + fn_escapecmdshellsymbols(@@SERVERNAME) collate database_default + '" '
					end
					else
						select @osql_cmd = @osql_cmd + fn_escapecmdshellsymbolsremovequotes(@osql_path) collate database_default + '\binn\osql" -U' + fn_escapecmdshellsymbols(isnull(@login, N'sa')) collate database_default + ' -P' + fn_escapecmdshellsymbols(isnull(@password, N'')) collate database_default + ' -S"' + fn_escapecmdshellsymbols(@@SERVERNAME) collate database_default + '" '

					select @osql_cmd = @osql_cmd + ' -l30 -t120 '
					select @osql_cmd = @osql_cmd + ' -b ' + ' -d' + fn_escapecmdshellsymbols(@dbname) collate database_default
					select @osql_cmd = @osql_cmd +	' -i' + '"' + fn_escapecmdshellsymbolsremovequotes(@install_path) collate database_default + '\install\instdist.sql"' + 
													' -o' + '"' + fn_escapecmdshellsymbolsremovequotes(@install_path) collate database_default + '\install\instdist.out"'			

					if (@osql_for_nt = 1)
						select @osql_cmd = @osql_cmd + ' "'

	 				exec @retcode = master..xp_cmdshell @osql_cmd
					if @retcode <> 0 or @@error <> 0
					begin
						raiserror (14113, 16, -1, @osql_cmd, 'instdist.out')
					end

					/*
					 * Process schema and metadata changes for each distribution database
					*/

					select @dbname = quotename(@dbname)
					exec ('use '+ @dbname + ' exec dbo.sp_vupgrade_distdb')
					if @@error <> 0
						return(1)
				end
				else
				begin
					/* Report informational message stating distribution
					** database is not accessible.
					*/
					raiserror( 21378, 10, 1, @dbname) with nowait
				end
				
				fetch next from cur_distdb into @dbname, @has_dbaccess
			end
			close cur_distdb
			deallocate cur_distdb
		end

		-- vupgrade_publisher runs at 
		exec @retcode = dbo.sp_vupgrade_publisher 
			@ver_old = @ver_old, 
			@ver_retention = @ver_retention
		if @retcode<>0 or @@error<>0
			return (1)
	
		-- Update subscription database schema
		exec @retcode = dbo.sp_vupgrade_subscription_databases
		if @retcode <> 0 or @@error <> 0
			return (1)


	end

	return (0)

end
go

exec dbo.sp_MS_marksystemobject sp_vupgrade_replication
go

--------------------------------------------------------------------------------
--. sp_MScopysnapshot
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MScopysnapshot')
drop procedure sp_MScopysnapshot
go
raiserror('Creating procedure sp_MScopysnapshot', 0,1)
go
CREATE PROCEDURE sp_MScopysnapshot (
    @source_folder           nvarchar(255),
    @destination_folder      nvarchar(255)
    )
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @directory_exists bit    
    DECLARE @ftporuncdir      nvarchar(5)
    DECLARE @pubdir           nvarchar(255)
    DECLARE @timestampdir     nvarchar(255)
    DECLARE @bslashindex      int
    DECLARE @bslashindex2     int
    DECLARE @bslashcounter    int
    DECLARE @command          nvarchar(1000)
    DECLARE @retcode          int
    DECLARE @platform_nt      bit

    IF platform() & 0x1 = 0x1
        SELECT @platform_nt = 1
    ELSE
        SELECT @platform_nt = 0

    -- If @source_folder is NULL then either the snapshot has not been 
    -- generated or it has been cleaned up
    IF @source_folder IS NULL OR @source_folder = N''
    BEGIN
        RAISERROR(21289, 16, -1)
        RETURN (1)
    END

    -- Make sure that the @destination folder is not null
    IF @destination_folder IS NULL OR
       @destination_folder = N''
    BEGIN
        RAISERROR(21287, 16, -1)      
        RETURN (1)
    END

    -- Append backslash to @destination_folder if it is not 
    -- there already
    IF SUBSTRING(@destination_folder, LEN(@destination_folder), 1) <> N'\'
    BEGIN
        SELECT @destination_folder = @destination_folder + N'\'
    END

    -- Check if the destination folder exists 
    EXEC sp_MSget_file_existence @destination_folder, @directory_exists OUTPUT
    IF @directory_exists = 0
    BEGIN
        RAISERROR(21287, 16, -1)      
        RETURN (1)
    END    
    
    -- Parse out the last three components in the source folder 
    -- Note that the source_folder must have a trailing backslash
    SELECT @bslashindex = 1
    SELECT @bslashindex2 = 1
    SELECT @bslashcounter = 0
    WHILE (@bslashindex <> 0)
    BEGIN
        SELECT @bslashindex = CHARINDEX(N'\', @source_folder, @bslashindex + 1)
        SELECT @bslashcounter = @bslashcounter + 1
        IF @bslashcounter > 4
        BEGIN
            SELECT @bslashindex2 = CHARINDEX(N'\', @source_folder, @bslashindex2 + 1)
        END    
    END  

    SELECT @bslashindex = CHARINDEX(N'\', @source_folder, @bslashindex2 + 1)
    SELECT @ftporuncdir = SUBSTRING(@source_folder, @bslashindex2 + 1, @bslashindex - @bslashindex2 - 1)
    SELECT @bslashindex2 = @bslashindex

    SELECT @bslashindex = CHARINDEX(N'\', @source_folder, @bslashindex2 + 1)
    SELECT @pubdir = SUBSTRING(@source_folder, @bslashindex2 + 1, @bslashindex - @bslashindex2 - 1) 
    SELECT @bslashindex2 = @bslashindex

    SELECT @bslashindex = CHARINDEX(N'\', @source_folder, @bslashindex2 + 1)
    SELECT @timestampdir = SUBSTRING(@source_folder, @bslashindex2 + 1, @bslashindex - @bslashindex2 - 1) 
    SELECT @bslashindex2 = @bslashindex
        
    -- Create the subdirectory structure underneath the specified snapshot
    -- folder. Ignore errors for now, we will check whether the directory 
    -- is successfully created later on.

    -- Don't suppress output from xp_cmdshell so user knows what's going on
    -- in case something goes wrong 
    SELECT @destination_folder = @destination_folder + @ftporuncdir + '\'
    SELECT @command = 'mkdir "' + fn_escapecmdshellsymbolsremovequotes(@destination_folder) collate database_default + '"'
    IF (@platform_nt = 1)
        SELECT @command = '" ' + @command + ' "'
    EXEC master..xp_cmdshell @command
    
    SELECT @destination_folder = @destination_folder + @pubdir + '\'
    SELECT @command = 'mkdir "' + fn_escapecmdshellsymbolsremovequotes(@destination_folder) collate  database_default + '"'
    IF (@platform_nt = 1)
        SELECT @command = '" ' + @command + ' "'
    EXEC master..xp_cmdshell @command

    SELECT @destination_folder = @destination_folder + @timestampdir + '\'
    SELECT @command = 'mkdir "' + fn_escapecmdshellsymbolsremovequotes(@destination_folder) collate database_default + '"'
    IF (@platform_nt = 1)
        SELECT @command = '" ' + @command + ' "'
    EXEC master..xp_cmdshell @command

    -- Check if the real destination folder exists
    EXEC sp_MSget_file_existence @destination_folder, @directory_exists OUTPUT
    IF @directory_exists = 0
    BEGIN
        RAISERROR(21288, 16, -1)
        RETURN (1)
    END

    -- Do the actual copying
    SELECT @command = 'copy "' + fn_escapecmdshellsymbolsremovequotes(@source_folder) collate database_default + '*.*" "' + fn_escapecmdshellsymbolsremovequotes(@destination_folder) collate database_default + '"'    
    IF (@platform_nt = 1)
        SELECT @command = '" ' + @command + ' "'
    
    EXEC @retcode = master..xp_cmdshell @command
    
    IF @retcode <> 0
        RETURN (1)
    ELSE
        RETURN (0)
END
go
EXEC dbo.sp_MS_marksystemobject sp_MScopysnapshot
go

--------------------------------------------------------------------------------
--. sp_copymergesnapshot
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_copymergesnapshot')
drop procedure sp_copymergesnapshot
go
raiserror('Creating procedure sp_copymergesnapshot', 0,1)
go
CREATE PROCEDURE sp_copymergesnapshot (
    @publication          sysname,
    @destination_folder   nvarchar(255)
    )
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @retcode       int
    DECLARE @source_folder nvarchar(255)
    SELECT @retcode = 0
    
    EXEC @retcode = dbo.sp_MSreplcheck_publish
    IF @@ERROR <> 0 OR @retcode <> 0
		RETURN (1)

    CREATE TABLE #snapshot_folders
    (
        id              int identity, 
        snapshot_folder nvarchar(255) collate database_default
    )
    IF @@ERROR <> 0
    BEGIN
        RETURN 1
    END

    INSERT INTO #snapshot_folders
    EXEC @retcode = sp_browsemergesnapshotfolder @publication = @publication
    IF @retcode <> 0 OR @@ERROR <> 0
    BEGIN
        GOTO Failure
    END
    
    SELECT @source_folder = (select top 1 snapshot_folder FROM #snapshot_folders ORDER BY id ASC)
    IF @@ERROR <> 0
    BEGIN
        GOTO Failure
    END        
    SET ROWCOUNT 0    

    EXEC @retcode = sp_MScopysnapshot @source_folder,
                                      @destination_folder
    IF @retcode <> 0 OR @@ERROR <> 0 
    BEGIN
        GOTO Failure
    END

    DROP TABLE #snapshot_folders
    RETURN 0
Failure:
    SET ROWCOUNT 0    
    DROP TABLE #snapshot_folders        
    RETURN 1
END
GO
EXEC dbo.sp_MS_marksystemobject sp_copymergesnapshot
go
grant execute on dbo.sp_copymergesnapshot to public
go

--------------------------------------------------------------------------------
--. sp_copysubscription
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_copysubscription')
drop procedure sp_copysubscription
go
print ''
print 'Creating procedure sp_copysubscription'
go
CREATE PROCEDURE sp_copysubscription (
@filename nvarchar(260),
@temp_dir nvarchar(260) = NULL,
-- Directory contains temp files. If not specified, SQL
-- server default data directory will be used.
@overwrite_existing_file bit = 0
)
AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */
	declare @cmd nvarchar(4000)
	declare @retcode int
	declare @data_path nvarchar(260)
	declare @subscriber_srvid 	int
	declare @subscriber_db		sysname
	declare @backup_path nvarchar(260)
	declare @temp_data_path nvarchar(260)
	declare @temp_log_path nvarchar(260)
	declare @retention			int
	declare @retention_date   	datetime
    declare @pubid	 			uniqueidentifier


	/*
	* Initializations
	*/
	select @retcode = 0
	select @subscriber_srvid = 0
	select @subscriber_db = db_name()

	-- We only support single file attach. Check to make sure
	-- there are only 2 files, one data file and one log file
	if (select count(*) from sysfiles) > 2
	begin
		raiserror(21212,16, -1)
		return 1
	end

	/* Make sure all tran sub allows attach */
	declare @publication 				sysname
	declare @publisher					sysname
	declare @tran_found					bit
	declare @merge_found				bit
	select @tran_found = 0
	select @merge_found = 0

	/* 
	** Make sure all merge subscriptions in the current database 
	** have allow_subscription_copy set to TRUE
	** and there are no push subscriptions. 
	*/
	if exists (select * from sysobjects where name = 'MSsubscription_agents')
	begin
		set @publisher = NULL
		-- Not using @publication because share agent case.
		select top 1 @publisher = publisher from MSsubscription_agents where
			allow_subscription_copy = 0
        IF @publisher is not null
	    BEGIN
			RAISERROR(21236, 16, -1, @publisher)
	        RETURN (1)
	    END

		set @publisher = null
		select top 1 @publisher = publisher from MSreplication_subscriptions where
			subscription_type = 0
        IF @publisher is not null
	    BEGIN
			RAISERROR(21237, 16, -1, @publisher)	        
			RETURN (1)
	    END
		if exists (select * from MSsubscription_agents)
			select @tran_found = 1
	end			

	/* 
	** Make sure all merge subscriptions in the current database 
	** have allow_subscription_copy set to TRUE
	** and there are no push subscriptions. 
	*/
	if exists (select * from sysobjects where name = 'sysmergepublications')
		begin
			set @publication = NULL
			select top 1 @publication = name from sysmergepublications where
				allow_subscription_copy = 0
	        IF @publication is not null
		        BEGIN
		            RAISERROR (21204, 16, -1, @publication)    
		            RETURN (1)
		        END

			set @publication = null
			select top 1 @publication = p.name from sysmergepublications p, 
				sysmergesubscriptions s where
				p.pubid = s.pubid and
				s.subid <> s.pubid and 
				db_name = db_name() collate database_default and 
				subscriber_server = convert(nvarchar(4000), SERVERPROPERTY('ServerName')) collate database_default  and
				s.subscription_type = 0
			IF @publication is not null
		    BEGIN
				RAISERROR(21238, 16, -1, @publication)	        
				RETURN (1)
			END

			-- Does db contains subscriptions?
			if exists (select * from sysmergesubscriptions where subid <> pubid and 
				db_name = db_name() collate database_default and 
				subscriber_server = convert(nvarchar(4000), SERVERPROPERTY('ServerName')) collate database_default)
				select @merge_found = 1

			/* Retention check : Make sure that the subscription copy is not too old */
			declare PC CURSOR LOCAL FAST_FORWARD for select DISTINCT p.name, p.pubid, p.retention from sysmergepublications p, sysmergesubscriptions s 
		        where s.subid=p.pubid and s.pubid=p.pubid for read only
		    open PC
		    fetch PC into @publication, @pubid, @retention 
		    
		    WHILE (@@fetch_status <> -1)
		        BEGIN
					/* Compute the retention period cutoff dates per publication */
					select @retention_date = dateadd(day, -@retention, getdate())
			       	if @retention is not NULL and @retention > 0
		        	begin
						if not exists (select coldate from sysmergearticles , MSmerge_genhistory 
											where	nickname = art_nick 
													and coldate > @retention_date 
													and sysmergearticles.pubid = @pubid)
							begin
					            RAISERROR (21306, 16, -1, @publication)
								return (1)
							end					            
		            end
		            fetch PC into @publication, @pubid, @retention
		        END

		end			

	if @tran_found = 0 and @merge_found = 0
	begin
		raiserror(21239, 16 , -1)
		return (1)
	end
	
	-- Security check
	-- Only DBO or sysadmin can do this
	-- The user also must have create db permissions.
	exec @retcode = dbo.sp_MSreplcheck_subscribe
    IF @retcode <> 0 or @@error <> 0
		return 1

	if @overwrite_existing_file is null
		set @overwrite_existing_file = 0

	-- Check to see if the file already exists
	declare @exists bit

	if @overwrite_existing_file = 0
	begin
		exec @retcode = dbo.sp_MSget_file_existence @filename, @exists output
		if @@error <> 0 or @retcode <> 0
			return 1
		if @exists <> 0 
		begin
			raiserror(21214, 16, -1, @filename)
			return 1
		end
	end

	-- Check to see if have write permissions to the file location.
	-- Try create the file
	-- Echo text can be anything.
	select @cmd = 'echo Subscription copy failed. > "' + fn_escapecmdshellsymbolsremovequotes(@filename) collate database_default + '"'
	exec @retcode = master.dbo.xp_cmdshell @cmd, NO_OUTPUT
	if @@error <> 0 or @retcode <> 0
	begin
		raiserror(21247, 16, -1, @filename)
		select @retcode = 1
		goto Cleanup
	end	
	-- File should be created.
	exec @retcode = dbo.sp_MSget_file_existence @filename, @exists output
	if @@error <> 0 or @retcode <> 0
	begin
		select @retcode = 1
		goto Cleanup
	end
	if @exists = 0
	begin
		raiserror(21247,16, -1, @filename)
		select @retcode = 1
		goto Cleanup
	end

	/*
    ** Get the MSSQL DATA path. Note that users can have a SQLDataRoot directory different from SQLPath
    */
	if @temp_dir is null
	begin
		exec @retcode = master.dbo.sp_MSget_setup_paths
			@data_path = @temp_dir output
		IF @retcode <> 0 or @@error <> 0
			return 1
		select @temp_dir = @temp_dir + '\DATA\'
	end
	else
	begin
		-- Check to make sure working dir is valid.
		exec @retcode = dbo.sp_MSget_file_existence @temp_dir, @exists output
		if @@error <> 0 or @retcode <> 0
		begin
			select @retcode = 1
			goto Cleanup
		end
		if @exists = 0
		begin
			raiserror (21037, 16, -1, @temp_dir)		
			select @retcode = 1
			goto Cleanup
		end
		if substring(@temp_dir, len(@temp_dir), 1) <> '\'
			select @temp_dir = @temp_dir + '\'
	end

	-- Get temp db name
	-- Use a guid to avoid name colision.
	declare @dbname sysname
	select @dbname = db_name()
	declare @temp_db_name sysname
    declare @guid_name nvarchar(36)
    select @guid_name =  convert (nvarchar(36), newid())

	select @temp_db_name = 'repl_sub_restore_' + @guid_name

	select @backup_path = @temp_dir + @temp_db_name + '.bak'

	-- Create table used to signal attach or restored process to do different things
	if not exists (select * from sysobjects where name = 'MSreplication_restore_stage')
	begin 
		CREATE TABLE dbo.MSreplication_restore_stage
		(
		stage_id int -- not used for now
		)
		IF @@ERROR <> 0
			return 1
	end
	
	-- First backup the database to the file given
	-- Overwrite the existing file with INIT option.
	BACKUP DATABASE @dbname TO DISK = @backup_path WITH INIT
	if @@error<> 0
	begin
		select @retcode = 1
		goto Cleanup
	end


	-- Restore it to a temporary working database
	-- Get phy data and log file name for the temp db
	select @temp_data_path = @temp_dir + @temp_db_name + '.mdf'
	select @temp_log_path = @temp_dir + @temp_db_name + '.ldf'

	-- Get the command
	select @cmd = 'restore database ' + quotename(@temp_db_name) + ' from disk = ' 
		+ quotename(@backup_path,'''') + ' with replace, move '

	-- Get the logical file name for data file.
	select @cmd = @cmd + quotename(rtrim(name),'''') from sysfiles where
		(status & 0x40) = 0

	-- Use passed in filename as phy data file name for the temp db

	-- Use the passed in file as phy data file for the temp db
	select @cmd = @cmd + ' to ' + quotename(@temp_data_path,'''') + ', move '

	-- Get the logical file name for the log file
	select @cmd = @cmd + quotename(rtrim(name),'''') from sysfiles where
		(status & 0x40) <> 0

	-- Use the passed in file as phy file for the temp db
	select @cmd = @cmd + ' to ' + quotename(@temp_log_path,'''') + ' '
	exec (@cmd)
	if @@error<> 0
	begin
		select @retcode = 1
		goto Cleanup
	end

	-- Once we successfully restored, we delete to back up file to save disk space.
	if @backup_path is not null
	begin
		select @cmd = 'del "' + fn_escapecmdshellsymbolsremovequotes(@backup_path) collate database_default + '"' 
		EXEC master..xp_cmdshell @cmd, NO_OUTPUT
		set @backup_path = null
	end

	-- Prepare the database for detach. 2 things will be done
	-- 1. Set a flag to indicate that this is a prepare sub db for detach
	-- 2. For merge, create table to store sysservers info for later fixing up after attach
	select @cmd = quotename(@temp_db_name) + '.dbo.sp_MSprepare_sub_for_detach'
	
	exec @retcode = @cmd @subscriber_srvid = @subscriber_srvid, @subscriber_db = @subscriber_db
	if @retcode<>0 or @@error<>0
	begin
		select @retcode = 1
		goto Cleanup
	end

	-- Shink the size of the temp db before detach
	DBCC SHRINKDATABASE (@temp_db_name, 10)
	if @@error <> 0
		goto Cleanup

	-- detach the database
	-- Wait for the db to be closed
	WAITFOR DELAY '00:00:00.500'
	exec @retcode = sp_detach_db @temp_db_name
	if @retcode<>0 or @@error<>0
	begin
		select @retcode = 1
		goto Cleanup
	end

	-- Delete the log file to save disk space
	if @temp_log_path is not null
	begin
		select @cmd = 'del "' + fn_escapecmdshellsymbolsremovequotes(@temp_log_path) collate database_default + '"' 
		EXEC master..xp_cmdshell @cmd, NO_OUTPUT
		set @temp_log_path = null
	end

	-- Compress the file
	exec @retcode = master..xp_makecab
		@cabfilename = @filename,
		@compression_mode ='mszip',
		@verbose_level = 0,
		@filename1 = @temp_data_path
	if @retcode<>0 or @@error<>0
	begin
		select @retcode = 1
		goto Cleanup
	end


Cleanup:
	if exists (select * from sysobjects where name = 'MSreplication_restore_stage')
		drop table dbo.MSreplication_restore_stage


	if exists (select * from master..sysdatabases where name = @temp_db_name collate database_default)
	begin
		select @cmd = 'drop database ' + quotename(@temp_db_name)
		exec (@cmd)
	end

	if @backup_path is not null
	begin
		select @cmd = 'del "' + fn_escapecmdshellsymbolsremovequotes(@backup_path) collate database_default + '"' 
		EXEC master..xp_cmdshell @cmd, NO_OUTPUT
	end

	if @temp_data_path is not null
	begin
		select @cmd = 'del "' + fn_escapecmdshellsymbolsremovequotes(@temp_data_path) collate database_default + '"' 
		EXEC master..xp_cmdshell @cmd, NO_OUTPUT
	end

	if @temp_log_path is not null
	begin
		select @cmd = 'del "' + fn_escapecmdshellsymbolsremovequotes(@temp_log_path) collate database_default + '"' 
		EXEC master..xp_cmdshell @cmd, NO_OUTPUT
	end

	if @retcode <> 0
	begin
		select @cmd = 'del "' + fn_escapecmdshellsymbolsremovequotes(@filename) collate database_default + '"' 
		EXEC master..xp_cmdshell @cmd, NO_OUTPUT
	end

	return @retcode
go
EXEC dbo.sp_MS_marksystemobject sp_copysubscription
GO
grant execute on dbo.sp_copysubscription to public
go

--------------------------------------------------------------------------------
--. sp_attachsubscription
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_attachsubscription')
drop procedure sp_attachsubscription
go
print ''
print 'Creating procedure sp_attachsubscription'
go
CREATE PROCEDURE sp_attachsubscription (
@dbname	sysname,
@filename nvarchar(260),
@subscriber_security_mode		int = NULL,						/* 0 standard; 1 integrated */
@subscriber_login				sysname = NULL,
@subscriber_password			sysname = NULL
)
AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */
	declare @cmd nvarchar(4000)
	declare @retcode int
	DECLARE @platform_nt binary
	declare @copy_created bit
    declare @exists bit

	select @platform_nt = 0x1	
	select @retcode = 0
	select @copy_created = 0
    select @exists = 0

	-- Parameter check: @subscriber_security_mode
	if @subscriber_security_mode is null
	begin
		if ( platform() & @platform_nt ) = @platform_nt
			select @subscriber_security_mode = 1
		else
			select @subscriber_security_mode = 0
	end	

	if ( ( platform() & @platform_nt ) <> @platform_nt and @subscriber_security_mode = 1 )
	begin
		RAISERROR(21038, 16, -1)
		RETURN (1)
	end

	if (@subscriber_security_mode = 0) and (@subscriber_login IS NULL or rtrim(@subscriber_login) = '')
		set @subscriber_login = 'sa'

	-- Check to make sure the database does not exists.
	if exists (select * from master..sysdatabases where name = @dbname collate database_default)
	begin
		raiserror(20621, 16, -1, @dbname)
		return (1)
	end

	-- Check to see if users has permissions to create database
	-- permissions() have to be run in master to return create db permission.
	declare @pm int
	exec @retcode = master.dbo.sp_executesql N'select @pm = permissions()', N'@pm int output', @pm output
	if @@error <> 0 or @retcode <> 0
		return 1
	if @pm & 1 = 0
	begin
		raiserror(20618, 16, -1)
		return 1
	end

	-- Decompress the file
	-- We have to copy the file to another location first. 
	-- (cannot use source as destination')
	declare @temp_copy nvarchar(260)
	declare @file_dir nvarchar(260)
	declare @file_name nvarchar(260)
	declare @dir_cmd nvarchar(260)
	-- Set @drive_cmd if needed 
	-- Set temp copy to be the file directory first
	-- Note @file_dir include '\'
	if (charindex('\', @filename, 1) = 0)
	begin
		select @file_dir = ''
		select @file_name = @filename
	end
	else
	begin
		select @file_dir = left(@filename,len(@filename) + 1 - 
			charindex('\', reverse(@filename), 1))
		select @file_name = right(@filename, len(@filename) - len(@file_dir))
	end
	-- Get guid name
    declare @guid_name nvarchar(36)
    select @guid_name =  convert (nvarchar(36), newid())
	select @temp_copy = @file_dir + @guid_name + '.tmp'


	-- copy file
	select @cmd = 'copy "' + fn_escapecmdshellsymbolsremovequotes(@filename) collate database_default + '" "' + fn_escapecmdshellsymbolsremovequotes(@temp_copy) collate database_default + '"'
	exec @retcode = master.dbo.xp_cmdshell @cmd, NO_OUTPUT
	if @@error <> 0 or @retcode <> 0
	begin
		raiserror(21248, 16, -1, @filename)
		select @retcode = 1
		goto Cleanup
	end	

    exec @retcode = dbo.sp_MSget_file_existence @temp_copy, @exists output
    if @@error <> 0 or @retcode <> 0
    begin
        select @retcode = 1
        goto Cleanup
    end
    if @exists = 0
    begin
		raiserror(21247, 16, -1, @temp_copy)        
        select @retcode = 1
        goto Cleanup
    end

	select @copy_created = 1

	-- decompress
	exec @retcode = master.dbo.xp_unpackcab 
		@cabfilename = @temp_copy,
		@destination_folder = @file_dir,
		@verbose_level = 0,
		@destination_file = @file_name,
		@suppress_messages = 1
		
	if @@error <> 0 
	begin
		select @retcode = 1
		goto Cleanup
	end

	if @retcode in (1030,1029,2005)
	begin
		raiserror(20609, 16, -1, @filename)
		select @retcode = 1
		goto Cleanup
	end	
	else if @retcode <> 0
	-- re-issue the command to get errors
	begin
		exec @retcode = master.dbo.xp_unpackcab 
			@cabfilename = @temp_copy,
			@destination_folder = @file_dir,
			@verbose_level = 0,
			@destination_file = @file_name,
			@suppress_messages = 0
		select @retcode = 1
		goto Cleanup
	end

	-- Attach
	exec @retcode = dbo.sp_attach_single_file_db
		@dbname = @dbname,
		@physname = @filename
	if @retcode<>0 or @@error<>0
	begin
		raiserror(21248, 16, -1, @filename)
		select @retcode = 1
		goto Cleanup
	end


	-- Prepare the database for detach. 2 things will be done
	-- 1. Set a flag to indicate that this is a prepare sub db for detach
	-- 2. For merge, create table to store sysservers info for later fixing up after attach
	select @cmd = quotename(@dbname) + '.dbo.sp_MSrestore_sub'
	exec @retcode = @cmd
		@subscriber_security_mode = @subscriber_security_mode,	
		@subscriber_login = @subscriber_login,
		@subscriber_password = @subscriber_password
	if @retcode<>0 or @@error<>0
	begin
		select @retcode = 1
		goto Cleanup
	end

Cleanup:
	if @retcode <> 0
	begin
		-- The files will be deleted if some thing failed. 
		if exists (select * from master..sysdatabases where name = @dbname collate database_default)
			begin
				select @cmd = 'drop database ' + quotename(@dbname)
				exec (@cmd)
			end				
	end

	if @temp_copy is not null
	begin
		-- Restore the original file, ignore errors
		if @retcode <> 0 and @copy_created = 1
		begin
			select @cmd = 'copy "' + fn_escapecmdshellsymbolsremovequotes(@temp_copy) collate database_default + '" "' + fn_escapecmdshellsymbolsremovequotes(@filename) collate database_default + '"'
			exec master.dbo.xp_cmdshell @cmd, NO_OUTPUT
		end

		-- Delete the temp file.
		select @cmd = 'del "' + fn_escapecmdshellsymbolsremovequotes(@temp_copy) collate database_default+ '"' 
		EXEC master..xp_cmdshell @cmd, NO_OUTPUT
	end

	return @retcode
go
EXEC dbo.sp_MS_marksystemobject sp_attachsubscription
GO
grant execute on dbo.sp_attachsubscription to public
go
--------------------------------------------------------------------------------
--. sp_browsesnapshotfolder
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_browsesnapshotfolder')
drop procedure sp_browsesnapshotfolder
go
raiserror('Creating procedure sp_browsesnapshotfolder', 0,1)
go
CREATE PROCEDURE sp_browsesnapshotfolder (
    @publication    sysname,
    @subscriber     sysname = NULL,
    @subscriber_db  sysname = NULL
    )
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @retcode        INT
    DECLARE @publisher      sysname
    DECLARE @publisher_db   sysname
    DECLARE @distributor    sysname
    DECLARE @distributiondb sysname
    DECLARE @distproc       nvarchar(300)
    DECLARE @article_id     INT
    
    SELECT @retcode = 0
    SELECT @publisher = NULL
    SELECT @publisher_db = DB_NAME()
    SELECT @distributor = NULL
    SELECT @distributiondb = NULL
    SELECT @article_id = NULL
    
    EXEC @retcode = dbo.sp_MSreplcheck_publish
    IF @@ERROR <> 0 or @retcode <> 0
        return (1)

    -- Either both of @subscriber and @subscriber_db are NULL or
    -- both of them have to be non-null
    IF ((@subscriber IS NULL OR @subscriber = N'') 
        AND @subscriber_db IS NOT NULL AND @subscriber_db <> N'') OR
       ((@subscriber_db IS NULL OR @subscriber_db = N'') 
        AND @subscriber IS NOT NULL AND @subscriber <> N'')
    BEGIN
        RAISERROR(21148, 16, -1)
    END
    
    IF EXISTS (SELECT * FROM sysobjects WHERE name = N'syspublications') AND
       EXISTS (SELECT * FROM sysobjects WHERE name = N'sysextendedarticlesview')
    BEGIN    
        SELECT @publisher = @@SERVERNAME
    
        -- Get the smallest article id for the publication since 
        -- method for identifying a publication is not readily available 
        -- on the distributor

        SELECT @article_id = MIN(artid) 
          FROM sysextendedarticlesview sa
    INNER JOIN syspublications sp
            ON sa.pubid = sp.pubid
         WHERE sp.name = @publication

        IF @article_id IS NULL
        BEGIN
            RAISERROR(20026, 16, -1, @publication)
        END         

        EXEC @retcode = dbo.sp_helpdistributor @rpcsrvname = @distributor OUTPUT,
                                               @distribdb = @distributiondb OUTPUT
        IF @retcode <> 0
            RETURN @retcode
    
        SELECT @distributor = RTRIM(@distributor)
    
        IF LOWER(@@SERVERNAME) <> LOWER(@distributor)
        BEGIN
            SELECT @distproc = @distributor + '.' + @distributiondb + 
                               '.dbo.sp_MSbrowsesnapshotfolder'
        END
        ELSE
        BEGIN
            SELECT @distproc = @distributiondb + '.dbo.sp_MSbrowsesnapshotfolder'
        END
        EXECUTE @retcode = @distproc
                    @publisher = @publisher, 
                    @publisher_db = @publisher_db,
                    @article_id = @article_id,
                    @subscriber = @subscriber,
                    @subscriber_db = @subscriber_db
        RETURN @retcode
    END
    ELSE
    BEGIN
        RAISERROR(21149, 16, -1, @publisher_db)
        RETURN 1
    END
END
GO
EXEC dbo.sp_MS_marksystemobject sp_browsesnapshotfolder
go
grant execute on dbo.sp_browsesnapshotfolder to public
go
--------------------------------------------------------------------------------
--. sp_copysnapshot
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_copysnapshot')
drop procedure sp_copysnapshot
go

raiserror('Creating procedure sp_copysnapshot', 0,1)
go
CREATE PROCEDURE sp_copysnapshot (
    @publication        sysname,
    @destination_folder nvarchar(255),
    @subscriber         sysname = NULL,
    @subscriber_db      sysname = NULL
    )
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @retcode       int
    DECLARE @source_folder nvarchar(255)
    SELECT @retcode = 0
    
    -- security check, db_owner
    EXEC @retcode = dbo.sp_MSreplcheck_publish
    IF @@ERROR <> 0 or @retcode <> 0
        return (1)

    CREATE TABLE #snapshot_folders
    (
        id              int identity,
        snapshot_folder nvarchar(255) collate database_default
    )
    IF @@ERROR <> 0
    BEGIN
        RETURN 1
    END

    INSERT INTO #snapshot_folders
    EXEC @retcode = sp_browsesnapshotfolder @publication = @publication,
                                            @subscriber = @subscriber,
                                            @subscriber_db = @subscriber_db
    IF @retcode <> 0 OR @@ERROR <> 0
    BEGIN
        GOTO Failure
    END

    SELECT @source_folder = (select top 1 snapshot_folder FROM #snapshot_folders ORDER BY id)
    IF @@ERROR <> 0
    BEGIN
        GOTO Failure
    END        
    SET ROWCOUNT 0

    EXEC @retcode = sp_MScopysnapshot @source_folder,
                                      @destination_folder
    IF @retcode <> 0 OR @@ERROR <> 0 
    BEGIN
        GOTO Failure
    END

    DROP TABLE #snapshot_folders
    RETURN 0
Failure:
    SET ROWCOUNT 0
    DROP TABLE #snapshot_folders        
    RETURN 1
END
GO
EXEC dbo.sp_MS_marksystemobject sp_copysnapshot
go
grant execute on dbo.sp_copysnapshot to public
go

--------------------------------------------------------------------------------
--. sp_getagentoffloadinfo
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_getagentoffloadinfo')
drop procedure sp_getagentoffloadinfo
go
print ''
print 'Creating procedure sp_getagentoffloadinfo'
go
CREATE PROCEDURE sp_getagentoffloadinfo (
    @job_id         VARBINARY(16)
) AS
    SET NOCOUNT ON
    DECLARE @retcode        INT
    DECLARE @distributor    sysname
    DECLARE @distributiondb sysname
    DECLARE @distproc       NVARCHAR(300)

    SELECT @retcode = 0
    SELECT @distributor    = NULL
    SELECT @distributiondb = NULL

    -- security check, db_owner
    EXEC @retcode = dbo.sp_MSreplcheck_publish
    IF @@ERROR <> 0 or @retcode <> 0
        return (1)


    EXEC @retcode = dbo.sp_helpdistributor @rpcsrvname = @distributor OUTPUT,
                                           @distribdb = @distributiondb OUTPUT

    IF @retcode <> 0
        RETURN @retcode

    SELECT @distributor = RTRIM(@distributor)

    SELECT @distproc = @distributor + '.' + @distributiondb + 
                           '.dbo.sp_MSgetagentoffloadinfo'

    EXECUTE @retcode = @distproc 
                @job_id = @job_id

    RETURN @retcode
GO
EXEC dbo.sp_MS_marksystemobject sp_getagentoffloadinfo
GO
grant execute on sp_getagentoffloadinfo to public
go

--------------------------------------------------------------------------------
--.	System objects (replcom.sql)
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
--. sp_adddistributiondb
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_adddistributiondb')
drop procedure sp_adddistributiondb
go
raiserror('Creating procedure sp_adddistributiondb', 0,1)
go

CREATE PROCEDURE sp_adddistributiondb (
    @database sysname,
    @data_folder nvarchar(255) = NULL,
    @data_file nvarchar(255) = NULL,            /* physical file name */
    @data_file_size int = 2,                    /* Default: 2MB */            
    @log_folder nvarchar(255) = NULL,
    @log_file nvarchar(255) = NULL,             /* physical file name */
    @log_file_size int = 0,
    @min_distretention int = 0,                 /* min distribution retention period in hours */
    @max_distretention int = 72,                /* max distribution retention period in hours */
    @history_retention int = 48,                /* history retention period in hours */
    @security_mode int = 0,                     /* distributor login security 0 standard 1 integrated */
    @login sysname = 'sa',                      /* standard login name */
    @password sysname = NULL,                   /* standard login password */
    @createmode int = 0,  /* 0: use create db for attach (recommended), 
                            1: create db or use existing but no attach (this is the old way), 
                            2: create for instdist and detach only */
	@from_scripting bit = 0
    ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */
    DECLARE @data_path nvarchar(512)
    DECLARE @log_path nvarchar(512)
    
    DECLARE @data_path_quoted_for_copy nvarchar(512)
    DECLARE @log_path_quoted_for_copy nvarchar(512)

    DECLARE @logical_data_file nvarchar(255)
    DECLARE @logical_log_file nvarchar(255)
    DECLARE @canneddbdata_file nvarchar(255)
    DECLARE @canneddblog_file nvarchar(255)
    DECLARE @filecopy_cmd nvarchar(255)
    DECLARE @file_exists bit
    DECLARE @data_file_preexists int
    DECLARE @log_file_preexists int
	DECLARE @osql_path nvarchar(260)
    DECLARE @osql_cmd nvarchar(1000)
	DECLARE @osql_for_nt int
    DECLARE @devnum int
    --DECLARE @num_pages int
    DECLARE @retcode int
    DECLARE @reg_key nvarchar(255)
    DECLARE @agentname nvarchar(100)
    DECLARE @command nvarchar (2048)
    DECLARE @distbit int
    DECLARE @install_path nvarchar(255)
    DECLARE @mssql_data_path nvarchar(255)
    DECLARE @on_clause nvarchar(512)
    DECLARE @logon_clause nvarchar(512)
    DECLARE @distproc nvarchar(255)
    DECLARE @major_version int
    DECLARE @db_exists bit
    DECLARE @trunc_log_bit int
    DECLARE @description nvarchar(100)
    DECLARE @category_name sysname
    DECLARE @createmode_attach int
    DECLARE @createmode_noattach int
    DECLARE @createmode_fordetach int

    --DECLARE @filegrowth nvarchar(10)
    DECLARE @data_file_size_str nvarchar(10)
    DECLARE @log_file_size_str nvarchar(10)
    DECLARE @platform_nt binary

    --DECLARE @max_datafile_size int
    --DECLARE @max_logfile_size int
    
	IF @password = N''
		select @password = NULL

    select @platform_nt = 0x1
    --select @filegrowth = N'512KB'

    -- on error, delete the data and log files only if they didn't pre-exist.
    -- by default, assume they pre-exist.
    select @data_file_preexists = 1
    select @log_file_preexists = 1
    select @file_exists = 0

    if (@data_file_size IS NULL) or (@data_file_size = 0)
        select @data_file_size_str = N'512KB'
    else
        select @data_file_size_str = convert(nvarchar(10), @data_file_size)

    if (@log_file_size IS NULL) or (@log_file_size = 0)
        select @log_file_size_str = N'512KB'
    else
        select @log_file_size_str = convert(nvarchar(10), @log_file_size)
    
    --if (@data_file_size > 16)
    --  select @max_datafile_size = @data_file_size
    --else
    --  select @max_datafile_size = 16
    
    --if (@log_file_size > 16)
    --  select @max_logfile_size = @log_file_size
    --else
    --  select @max_logfile_size = 16

    select @createmode_attach = 0, @createmode_noattach = 1, @createmode_fordetach = 2
    SELECT @trunc_log_bit = 8
    SELECT @distbit = 16

    if (@createmode <> @createmode_fordetach)
    begin
    
        /* 
        ** Check if replication components are installed on this server
        */
        exec @retcode = dbo.sp_MS_replication_installed
        if (@retcode <> 1)
        begin
            return (1)
        end
    
        /* 
        ** Check for invalid security modes
        */
        IF @security_mode < 0 OR @security_mode > 1
        BEGIN
            RAISERROR(14109, 16, -1)
            RETURN (1)
        END

        IF ( ( @platform_nt != platform() & @platform_nt ) and @security_mode = 1)
        BEGIN
            RAISERROR(21038, 16, -1)
            RETURN (1)
        END
    
        /* 
        ** Check for invalid retention values 
        */
        IF @min_distretention < 0 OR @max_distretention < 0 
        BEGIN
            RAISERROR(14106, 16, -1)
            RETURN (1)
        END
        IF @min_distretention > @max_distretention
        BEGIN
            RAISERROR(14107, 16, -1) 
            RETURN (1)
        END

        /*
        ** Check to make sure this is a distributor
        */
        IF NOT EXISTS (SELECT * FROM master..sysservers
              WHERE UPPER(datasource) = UPPER(@@SERVERNAME) collate database_default
                 AND srvstatus & 8 <> 0)
        BEGIN
            RAISERROR (14114, 16, -1, @@SERVERNAME)
            RETURN(1)
        END
    
        /*
        ** Check if database is already configured as a distributor database
        */
        IF EXISTS (SELECT * FROM msdb..MSdistributiondbs WHERE name = @database collate database_default)
        BEGIN
            RAISERROR (14119, 16, -1, @database)
            RETURN(1)
        END    
    end

    /* 
    ** Get path to osql client (TOOLS) directory
    */
    EXECUTE @retcode = master.dbo.sp_MSgettools_path @osql_path OUTPUT
    IF ( @retcode <> 0 ) or ( @@ERROR <> 0 ) or ( @osql_path is NULL ) or ( @osql_path = '' )
    BEGIN
        GOTO UNDO       
    END

    /* 
    ** Get path to version specific INSTALL directory
    */
	exec @retcode = master.dbo.sp_MSget_setup_paths
		@sql_path = @install_path output,
		@data_path = @mssql_data_path output
    IF @retcode <> 0 or @install_path is NULL or @install_path='' or @mssql_data_path = ''
    BEGIN
        GOTO UNDO       
    END

    IF @data_folder IS NULL or @data_folder = ''
        select @data_folder = @mssql_data_path + '\DATA'

    IF @log_folder IS NULL or @log_folder = ''
        select @log_folder = @mssql_data_path + '\DATA'

    IF @data_file IS NULL
        SELECT @data_file = @database + '.MDF'

    IF @log_file IS NULL
        SELECT @log_file = @database + '.LDF'

    if substring(@data_folder, len(@data_folder), 1) = '\'
    select @data_folder = substring (@data_folder, 1, len(@data_folder) -1)
    if substring(@log_folder, len(@log_folder), 1) = '\'
    select @log_folder = substring (@log_folder, 1, len(@log_folder) -1)

    SELECT @data_path = @data_folder + '\' + @data_file
    SELECT @log_path = @log_folder + '\' + @log_file

    SELECT @data_path_quoted_for_copy = '"' + fn_escapecmdshellsymbolsremovequotes(@data_folder) collate database_default + '\' + fn_escapecmdshellsymbolsremovequotes(@data_file) collate database_default + '"'
    SELECT @log_path_quoted_for_copy = '"' + fn_escapecmdshellsymbolsremovequotes(@log_folder) collate database_default + '\' + fn_escapecmdshellsymbolsremovequotes(@log_file) collate database_default + '"'

    select @logical_data_file = @database

    /* 
    ** Truncate the logical log file name back to 128 characters
    ** long so the 'CREATE DATABASE' statement won't complain.
    */
    /* LEN(@logical_log_file) = LEN(@database) + LEN('_log') and
       LEN(@logical_log_file) <= 128 implies 
       LEN(@database) <=124 */
    IF (LEN(@database) > 124)
        SELECT @logical_log_file = SUBSTRING(@database, 1, 124) + '_log'  
    ELSE 
        SELECT @logical_log_file = @database + '_log'

    if (@createmode = @createmode_attach)
    begin
        select @canneddbdata_file = @mssql_data_path + '\DATA\DISTMDL.MDF'
        select @canneddblog_file = @mssql_data_path + '\DATA\DISTMDL.LDF'

        exec dbo.sp_MSget_file_existence @canneddbdata_file, @file_exists OUTPUT
        if (@file_exists = 0)
        begin
            /* Fallback to mode where instdist.sql needs to be run */
            select @createmode = @createmode_noattach
        end

        exec dbo.sp_MSget_file_existence @canneddblog_file, @file_exists OUTPUT
        if (@file_exists = 0)
        begin
            /* Fallback to mode where instdist.sql needs to be run */
            select @createmode = @createmode_noattach
        end
    end

    /*
    ** Create the distributor database if it does not exist
    */
    IF NOT EXISTS (SELECT * from master..sysdatabases WHERE name = @database collate database_default) AND (@createmode <> @createmode_attach)
    BEGIN

        -- Note: Use system's default file growth.
        IF @logical_data_file IS NOT NULL AND NOT EXISTS (SELECT * FROM master..sysdevices WHERE name = @logical_data_file collate database_default)
        BEGIN
            SELECT @on_clause = ' ON (NAME =''' + @logical_data_file + ''',FILENAME=''' + REPLACE( @data_path, '''', '''''' ) + 
                ''', SIZE=' + @data_file_size_str + ', MAXSIZE = UNLIMITED)'
        END

        IF @logical_log_file IS NOT NULL AND NOT EXISTS (SELECT * FROM master..sysdevices WHERE name = @logical_log_file collate database_default)
        BEGIN
            SELECT @logon_clause = ' LOG ON (NAME =''' + @logical_log_file + ''',FILENAME=''' + REPLACE( @log_path, '''', '''''' ) + 
                ''', SIZE=' + @log_file_size_str + ', MAXSIZE= UNLIMITED)'          
        END

        /*
        ** Create distributor database
        */
        SELECT @command = 'USE master  CREATE DATABASE ' +  QUOTENAME(@database) + 
            + isnull(@on_clause, ' ') + isnull(@logon_clause, ' ')

        EXEC (@command)
        IF @@ERROR <> 0
            RETURN (1)
        SELECT @db_exists = 0
    END
    ELSE IF NOT EXISTS (SELECT * from master..sysdatabases WHERE name = @database collate database_default) AND (@createmode = @createmode_attach)
    BEGIN
    /* DO THE CREATE DATABASE FOR ATTACH STUFF */
        
        exec dbo.sp_MSget_file_existence @data_path, @data_file_preexists OUTPUT
        if (@data_file_preexists = 1)
        begin
            raiserror(5170, 16, -1, @data_path)
            return 1
        end
    
        SELECT @on_clause = ' ON (NAME = ''' + @logical_data_file + ''', FILENAME=''' + REPLACE( @data_path, '''', '''''' ) + ''')'
        
        exec dbo.sp_MSget_file_existence @log_path, @log_file_preexists OUTPUT
        if (@log_file_preexists = 1)
        begin
            raiserror(5170, 16, -1, @log_path)
            return 1
        end

        SELECT @logon_clause = ' LOG ON (NAME = ''' + @logical_log_file + ''', FILENAME=''' + REPLACE( @log_path, '''', '''''' ) + ''')'

        select @filecopy_cmd = 'copy "' + fn_escapecmdshellsymbolsremovequotes(@canneddbdata_file) collate database_default + '" ' + @data_path_quoted_for_copy
        EXEC @retcode = master..xp_cmdshell @filecopy_cmd, NO_OUTPUT
        IF @retcode <> 0 OR @@ERROR <> 0
        BEGIN
            RAISERROR (14113, 16, -1, @filecopy_cmd, 'instdist.out')
            return (1)
        END

        select @filecopy_cmd = 'copy "' + fn_escapecmdshellsymbolsremovequotes(@canneddblog_file) collate database_default + '" ' + @log_path_quoted_for_copy
        EXEC @retcode = master..xp_cmdshell @filecopy_cmd, NO_OUTPUT
        IF @retcode <> 0 OR @@ERROR <> 0
        BEGIN
            RAISERROR (14113, 16, -1, @filecopy_cmd, 'instdist.out')
            return (1)
        END

        /*
        ** Create distributor database
        */
        SELECT @command = 'USE master  CREATE DATABASE ' +  QUOTENAME(@database) + 
            + @on_clause + @logon_clause + ' FOR ATTACH'

        EXEC (@command)
        IF @@ERROR <> 0
        begin
            RETURN (1)
        end
        dbcc dbreindexall(@database, 240) with no_infomsgs

        SELECT @db_exists = 0
    END
    ELSE
    BEGIN
        SELECT @db_exists = 1
    END

    -- Must make the dist db owned by sa so that the sps in it can select from
    -- security cache tables in tempdb by owership chain rule.
    declare @retcode2 int
    select @retcode2 = 0
    select @distproc = QUOTENAME(@database) + '.dbo.sp_executesql'
    SELECT @command = 
        -- If the db is created by sa or from attach, sa is dbo already.
        -- sp_changedbowner will fail is the new owner is an user in the db already.
        ' if not exists (select * from sysusers where sid = 0x01) ' +
        ' exec @retcode2 = dbo.sp_changedbowner ''sa'''
    EXEC @retcode = @distproc @command, N'@retcode2 int output', @retcode2 output
    IF @retcode <> 0 or @retcode2 <> 0 or @@ERROR <> 0
    BEGIN
        GOTO UNDO
    END

    /* Set the database option truncate log on checkpoint & turn off autoclose which is default of win9x*/
    IF EXISTS (SELECT * FROM master.dbo.sysdatabases WHERE 
        name = @database collate database_default AND
        (status & @trunc_log_bit) = 0 )   -- if its not already marked
    BEGIN
        EXEC @retcode = dbo.sp_dboption @database, 'trunc. log on chkpt.', 'true'
        IF @retcode <> 0 OR @@ERROR <> 0
        BEGIN
            GOTO UNDO
        END
    END

    EXEC @retcode = dbo.sp_dboption @database, 'autoclose', 'false'
        IF @retcode <> 0 OR @@ERROR <> 0
        BEGIN
            GOTO UNDO
        END

    /*
    **
    ** Update sysdatabase category bit
    ** This is to prevent user from dropping the database.
    **/
    if (@createmode <> @createmode_fordetach)
    begin
        UPDATE master..sysdatabases SET category = category | @distbit WHERE name = @database collate database_default
        IF @@ERROR <> 0
        BEGIN
            GOTO UNDO
        END
    end

    /* 
    ** Install instdist.sql
    */

    if (@createmode <> @createmode_attach) OR (@db_exists = 1)
    begin
		if (( platform() & @platform_nt = @platform_nt ))
			select @osql_for_nt = 1
		else
			select @osql_for_nt = 0

        -- Always use integrated security on WINNT since @login passed-in is for remote 
        -- subscriber and may not have enough privilege to apply the script
        IF (@security_mode = 1 or @osql_for_nt = 1) AND NOT (@security_mode = 0 AND @createmode = 2)
		BEGIN
            SELECT @osql_cmd = '" "' + fn_escapecmdshellsymbolsremovequotes(@osql_path) collate database_default + '\binn\osql" -E '  
			if serverproperty('instancename') is not null
				SELECT @osql_cmd = @osql_cmd + ' -S"' + fn_escapecmdshellsymbols(@@SERVERNAME) collate database_default + '" '
		END
        ELSE
		BEGIN
        -- cannot specify -S w/ -E for local execution, SID does not map 
		if (@osql_for_nt = 1)
            SELECT @osql_cmd = '" "' + fn_escapecmdshellsymbolsremovequotes(@osql_path) collate database_default + '\binn\osql" -U"' + fn_escapecmdshellsymbols(@login) collate database_default + '" -P"' + 
                fn_escapecmdshellsymbols(isnull(@password,'')) collate database_default + '" -S"' + fn_escapecmdshellsymbols(@@SERVERNAME) collate database_default + '" '
		else
			SELECT @osql_cmd = '"'   + fn_escapecmdshellsymbolsremovequotes(@osql_path) collate database_default + '\binn\osql" -U"' + fn_escapecmdshellsymbols(@login) collate database_default + '" -P"' + 
                fn_escapecmdshellsymbols(isnull(@password,'')) collate database_default + '" -S"' + fn_escapecmdshellsymbols(@@SERVERNAME) collate database_default + '" '
		END
    
        select @osql_cmd = @osql_cmd + '-l60 -t60 '

        -- We must use -b option to make osql return error code !!
        SELECT @osql_cmd = @osql_cmd + 
            ' -d"' + fn_escapecmdshellsymbols(@database) collate database_default + '" -b ' +
            ' -i' + '"' + fn_escapecmdshellsymbolsremovequotes(@install_path) collate database_default + '\install\instdist.sql"' + 
            ' -o' + '"' + fn_escapecmdshellsymbolsremovequotes(@install_path) collate database_default + '\install\instdist.out"'

		if (@osql_for_nt = 1)
		BEGIN
	        SELECT @osql_cmd = @osql_cmd + ' "'
		END
    
        EXEC @retcode = master..xp_cmdshell @osql_cmd
        IF @retcode <> 0 OR @@ERROR <> 0
        BEGIN
            RAISERROR (14113, 16, -1, @osql_cmd, 'instdist.out')
            GOTO UNDO       
        END
    end
    
    if (@createmode <> @createmode_fordetach)
    begin
        /* Set db_existed bit in MSrepl_version */
        IF @db_exists = 1
        BEGIN
            SELECT @distproc = 'UPDATE ' +
                @database + '..MSrepl_version SET db_existed = 0x1'
    
            EXEC(@distproc)
            IF @@ERROR <> 0
            BEGIN
                GOTO UNDO       
            END
        END

        DELETE msdb.dbo.MSdistributiondbs WHERE name = @database collate database_default
        IF @@ERROR <> 0
        BEGIN
            GOTO UNDO       
        END

        INSERT INTO msdb.dbo.MSdistributiondbs VALUES (
            @database, @min_distretention, @max_distretention, @history_retention
            )
        IF @@ERROR <> 0
        BEGIN
            GOTO UNDO       
        END

        -- This login need db_owner priviledge to call sps in distribution db
        declare @distributor_login sysname
        select @distributor_login = 'distributor_admin'

        select @command = quotename(@database) + '.dbo.sp_MSrepl_dbrole'
        exec @retcode = @command 'db_owner', @distributor_login, 'add'
        IF @@error <> 0 OR @retcode <> 0
            GOTO UNDO

		if @from_scripting = 0
		begin
			/*
			** Create the history cleanup agent.
			*/
			SELECT @agentname = formatmessage (20567, @database)
			SELECT @command =  'EXEC dbo.sp_MShistory_cleanup @history_retention = ' + 
				CONVERT(nvarchar(12), @history_retention)

			IF EXISTS (SELECT * FROM msdb..sysjobs_view WHERE name = @agentname collate database_default and
				UPPER(originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName'))))
			BEGIN
				EXEC @retcode = msdb.dbo.sp_delete_job 
					@job_name = @agentname
				IF @@ERROR <> 0 or @retcode <> 0
				BEGIN
					GOTO UNDO
				END
			END

			set @description = formatmessage(20535)

			-- Get History Cleanup category name (assumes category_id = 12)
			select @category_name = name FROM msdb.dbo.syscategories where category_id = 12

			EXECUTE @retcode = dbo.sp_MSadd_repl_job @agentname,
			@subsystem = 'TSQL',
			@server = @@SERVERNAME,
			@databasename = @database,
			@description = @description,
			@freqtype = 4,    
			@freqsubtype = 4,         
			@freqsubinterval = 10,    /* Number of minutes between runs */ 
			@command = @command,
			@enabled = 1,
			@retryattempts = 0,
			@loghistcompletionlevel = 0,
			@category_name = @category_name
    
			IF @@ERROR <> 0 or @retcode <> 0
			BEGIN
				GOTO UNDO
			END

			/*
			** Create the distribution cleanup agent.
			*/
			SELECT @agentname = formatmessage (20568, @database)
			SELECT @command =  'EXEC dbo.sp_MSdistribution_cleanup @min_distretention = ' + 
				CONVERT(nvarchar(12), @min_distretention) + ', @max_distretention = ' +
				CONVERT(nvarchar(12), @max_distretention)

			IF EXISTS (SELECT * FROM msdb..sysjobs_view WHERE name = @agentname collate database_default and
				UPPER(originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName'))))
			BEGIN
				EXEC @retcode = msdb.dbo.sp_delete_job 
					@job_name = @agentname
				IF @@ERROR <> 0 or @retcode <> 0
				BEGIN
					GOTO UNDO
				END
			END

			set @description = formatmessage(20541)
			-- Get Distribution Cleanup category name (assumes category_id = 11)
			select @category_name = name FROM msdb.dbo.syscategories where category_id = 11
    
			EXECUTE @retcode = msdb.dbo.sp_MSadd_repl_job @agentname,
			@subsystem = 'TSQL',
			@server = @@SERVERNAME,
			@databasename = @database,
			@description = @description,
			@freqtype = 4,    
			@freqsubtype = 4,         
			@freqsubinterval = 10,    /* Number of minutes between runs */ 
			@command = @command,
			@retryattempts = 0,
			@enabled = 0,
			@loghistcompletionlevel = 0,
			@category_name = @category_name,
			-- Start  and end time is 5 min off from the history cleanup, which use the default.
			@activestarttimeofday = 000500,
			@activeendtimeofday   = 000459

			IF @@ERROR <> 0 or @retcode <> 0
			BEGIN
				GOTO UNDO
			end
		end
    end
    else
    begin
        /*detach */
        dbcc detachdb(@database)
    end
    
    RETURN(0)

UNDO:

    IF @db_exists = 0
        EXECUTE dbo.sp_dropdistributiondb @database

    /* Need to do it since sp_dropdistributiondb will fail in some cases */
    UPDATE master..sysdatabases SET category = category & ~@distbit WHERE name = @database collate database_default
    
    DELETE msdb.dbo.MSdistributiondbs where name = @database collate database_default

    /* drop the database and ignore error */
    IF @db_exists = 0 AND
        EXISTS (SELECT * from master..sysdatabases WHERE name = @database collate database_default)
    BEGIN
        SELECT @command = 'USE master  DROP DATABASE ' +  QUOTENAME(@database) 
        EXEC (@command)
    END

    if (@createmode = @createmode_attach)
    begin
        if (@data_file_preexists = 0)
        begin
            select @command = 'del ' + @data_path_quoted_for_copy
            exec master..xp_cmdshell @command
            --ignore errors
        end
        if (@log_file_preexists = 0)
        begin
            select @command = 'del ' + @log_path_quoted_for_copy
            exec master..xp_cmdshell @command
            --ignore errors
        end
    end
        
    RETURN(1)        
GO

--------------------------------------------------------------------------------
--. sp_adddistpublisher
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_adddistpublisher')
drop procedure sp_adddistpublisher
go
raiserror('Creating procedure sp_adddistpublisher', 0,1)
go

CREATE PROCEDURE sp_adddistpublisher (
    @publisher sysname,      /* publisher server name */
    @distribution_db sysname,
    @security_mode int = NULL,
    @login sysname = 'sa',
    @password sysname = NULL,
    @working_directory nvarchar(255),
    @trusted nvarchar(5) = NULL,
    @encrypted_password bit = 0,
    @thirdparty_flag bit = 0
        ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    DECLARE @retcode int
    DECLARE @reg_key nvarchar(255)
    DECLARE @distbit int
    DECLARE @active_value int
    DECLARE @server_added bit
    DECLARE @proc nvarchar(255)
    declare @fExists int
    declare @command nvarchar(255)
    declare @trusted_id bit
    declare @platform_nt binary
	declare @qv_replication varchar(10)
	declare @qv_replication_unlimited integer
	declare @qv_value_replication integer
    declare @enc_password nvarchar(524)

    select @platform_nt = 0x1
	select @qv_replication = '2745196162', @qv_replication_unlimited = 0

    SELECT @distbit = 16
    SELECT @server_added = 0

    /* 
    ** Check if replication components are installed on this server
    */
    exec @retcode = dbo.sp_MS_replication_installed
    if (@retcode <> 1)
    begin
        return (1)
    end

    IF @working_directory IS NULL or ltrim(rtrim(@working_directory)) = ''
    BEGIN
        RAISERROR (14043, 16, -1, '@working_directory')
        return (1)
    END


    /*
    ** 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, '@publisher')
            return (1)
        END

    EXECUTE @retcode = dbo.sp_validname @publisher

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

	IF @password = N''
		select @password = NULL

    /* On REPLICATION_LIMITED server, only local publisher is supported.
     * Note: The login and password registered for local publisher will be used for 
     * local agents to login to distributor, thus local publisher has to be installed first.
     * We choose not to support remote dist publshers on REPLICATION_LIMITED server altogether.
     * On NT, local agents will always use integrated security to log into 
     * distributor
	 * Today, REPLICATION_LIMITED means desktop but we check specific sku entry just in case
	*/
	exec @qv_value_replication = master.dbo.sp_MSinstance_qv @qv_replication	

	if ( @qv_value_replication != @qv_replication_unlimited ) and ( UPPER(@publisher) <> UPPER(@@servername) )
    begin
        -- remote dist publisher is not supported on this server version
        raiserror(21041,16,-1)
        return (1)
    end


    -- Set default security
    IF @security_mode IS NULL
    BEGIN
        IF (UPPER(@publisher) = UPPER(@@SERVERNAME) and ( platform() & @platform_nt = @platform_nt ) )
            SELECT @security_mode = 1
        ELSE
            SELECT @security_mode = 0
    END

    /* 
    ** Check for invalid security mode
    */
    IF @security_mode < 0 OR @security_mode > 1
        BEGIN
            RAISERROR(14109, 16, -1)
            return (1)
        END

    IF (UPPER(@publisher) = UPPER(@@SERVERNAME) and ( @platform_nt != platform() & @platform_nt ) and @security_mode = 1)
    BEGIN
        RAISERROR(21038, 16, -1)
        RETURN (1)
    END

    -- Encrypt the password
    select @enc_password = @password
    IF @encrypted_password = 0
    BEGIN
        EXEC @retcode = master.dbo.xp_repl_encrypt @enc_password OUTPUT
        IF @@error <> 0 OR @retcode <> 0
            return 1
    END

    -- Validate the working directory 
    -- Remove heading and trailing spaces
    select @working_directory = RTRIM(LTRIM(@working_directory))
    
    -- if the last char is '\', remove it.
    if substring(@working_directory, len(@working_directory),1) = '\'
        select @working_directory = substring(@working_directory, 1,
            len(@working_directory)-1)

    -- Don't do validation if it is a UNC path due to security problem.
    -- If the server is started as a service using local system account, we
    -- don't have access to the UNC path.
    if substring(@working_directory, 1,2) <> '\\'
    begin
        select @command = 'dir "' + fn_escapecmdshellsymbolsremovequotes(@working_directory) collate database_default + '"'
        exec @retcode = master..xp_cmdshell @command, 'no_output'
        if @@error <> 0
            return (1)
        if @retcode <> 0 
        begin
            raiserror (21037, 16, -1, @working_directory)
            return (1)
        end
    end

    /*
    ** Parameter Check:  @trusted
    */

    if @trusted is null
    begin
        if UPPER(@publisher) = UPPER(@@servername)
            select @trusted = 'false'
        else
            select @trusted = 'true'
    end

    IF LOWER(@trusted collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@trusted')
            RETURN (1)
        END

    IF LOWER(@trusted collate SQL_Latin1_General_CP1_CS_AS) = 'true' SELECT @trusted_id = 1
    ELSE SELECT @trusted_id = 0

    /*
    ** Check to make sure this is a distributor
    */
    IF NOT EXISTS (SELECT * FROM master..sysservers
              WHERE UPPER(datasource) = UPPER(@@SERVERNAME) collate database_default
                 AND srvstatus & 8 <> 0)
    BEGIN
        RAISERROR (14114, 16, -1, @@SERVERNAME)
        return (1)
    END

    /*
    ** Check if database is configured as a distributor database
    */
    IF NOT EXISTS (SELECT * FROM msdb..MSdistributiondbs WHERE name = @distribution_db collate database_default)
    BEGIN
        RAISERROR (14117, 16, -1, @distribution_db)
        return (1)
    END

    /* Check if publisher is already defined. */
    IF EXISTS (SELECT *
         FROM msdb..MSdistpublishers
        WHERE UPPER(name) = UPPER(@publisher) collate database_default)

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



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

    /* Add the server if it does not exist. */
    BEGIN
        EXECUTE @retcode = dbo.sp_addserver @publisher
        IF @@error <> 0 OR @retcode <> 0
        BEGIN
            RAISERROR (14075, 16, -1)
            GOTO UNDO
        END
        SELECT @server_added = 1
    END
    ELSE
    BEGIN
        SELECT @publisher = fn_getpersistedservernamecasevariation(@publisher) collate database_default
    END
     
    /*
    ** Set the Active  value.
    ** If the @publisher is local, set it to true.
    ** Otherwise, set it to false
    */
    IF UPPER(@publisher) = UPPER(@@SERVERNAME)
        SELECT @active_value = 1
    ELSE
        SELECT @active_value = 0


    DELETE msdb.dbo.MSdistpublishers where UPPER(name) = UPPER(@publisher) collate database_default
    IF @@ERROR <> 0
    BEGIN
        GOTO UNDO       
    END

    INSERT INTO msdb.dbo.MSdistpublishers VALUES (
        @publisher, @distribution_db, @working_directory,
        @security_mode, @login, @enc_password, @active_value, @trusted_id, @thirdparty_flag)
    IF @@ERROR <> 0
    BEGIN
        GOTO UNDO       
    END

    -- Add distributor_admin to distribution_admin non trusted mapping
    exec @fExists = dbo.sp_MSIfExistsRemoteLogin @publisher, null, 
        'distributor_admin'
    if( @fExists = 0 )
    BEGIN

        EXECUTE @retcode = dbo.sp_addremotelogin @publisher, 'distributor_admin', 
            'distributor_admin'
        IF @@error <> 0 OR @retcode <> 0
        BEGIN
            RAISERROR (14075, 16, -1)
            GOTO UNDO
        END
    END

    -- For 6x publisher, we still need the trusted sa to sa.
    -- For 6x publisher upgrading to 7.0, distributor_admin to distributor_admin need to be trusted.
    -- add remotelogin of SA if it doesn't already exist
    -- If there's a mapping for remote login sa already, we cannot map it to distributor_admin
    -- this is the case of server upgraded from 6.5.
    exec @fExists = dbo.sp_MSIfExistsRemoteLogin @publisher, null, 'sa'
    if( @fExists = 0 )
    BEGIN
        EXECUTE @retcode = dbo.sp_addremotelogin @publisher, 'distributor_admin', 'sa'
        IF @@error <> 0 OR @retcode <> 0
        BEGIN
            RAISERROR (14075, 16, -1)
            GOTO UNDO
        END
    END
    if @trusted_id = 1
    begin
        exec @fExists = dbo.sp_MSIfExistsRemoteLogin @publisher, 'distributor_admin', 'sa'
        if( @fExists = 1 )
        BEGIN
            EXECUTE @retcode = dbo.sp_remoteoption @publisher, 'distributor_admin', 'sa', trusted, true
            IF @@error <> 0 OR @retcode <> 0
            BEGIN
                RAISERROR (14075, 16, -1)
                GOTO UNDO
            END
        END

        EXECUTE @retcode = dbo.sp_remoteoption @publisher, 'distributor_admin', 'distributor_admin', trusted, true
        IF @@error <> 0 OR @retcode <> 0
        BEGIN
            RAISERROR (14075, 16, -1)
            GOTO UNDO
        END
    END


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

UNDO:
    -- If the server is marked, drop it
    IF EXISTS (SELECT *
         FROM msdb..MSdistpublishers
        WHERE UPPER(name) = UPPER(@publisher) collate database_default)
        EXEC dbo.sp_dropdistpublisher @publisher

    IF @server_added = 1
        EXEC dbo.sp_dropserver @publisher

    RETURN(1)
GO

--------------------------------------------------------------------------------
--. sp_changedistpublisher
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_changedistpublisher')
drop procedure sp_changedistpublisher
go
raiserror('Creating procedure sp_changedistpublisher', 0,1)
go

CREATE PROCEDURE sp_changedistpublisher (
    @publisher sysname,
    @property sysname     = NULL,     /* The property to change */
    @value nvarchar(255)     = NULL      /* The new property value */
    ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */
    DECLARE @retcode int
    DECLARE @new_database sysname
    DECLARE @new_security_mode int
    DECLARE @new_login sysname
    DECLARE @new_password nvarchar(524)
    DECLARE @distbit int
    DECLARE @new_active int
    DECLARE @new_trusted bit
    DECLARE @command nvarchar(255)
    declare @distribdb sysname
    DECLARE @platform_nt binary

    SELECT @platform_nt = 0x1
    SELECT @distbit = 16
    
    /*
    ** Parameter Check:  @property.
    ** If the @property parameter is NULL, print the options.
    */

    IF @property IS NULL
        BEGIN
            CREATE TABLE #tab1 (properties sysname collate database_default not null)
            INSERT INTO #tab1 VALUES ('distribution_db')
            INSERT INTO #tab1 VALUES ('working_directory')
            INSERT INTO #tab1 VALUES ('security_mode')
            INSERT INTO #tab1 VALUES ('login')
            INSERT INTO #tab1 VALUES ('password')
            INSERT INTO #tab1 VALUES ('active')
            INSERT INTO #tab1 VALUES ('trusted')
            SELECT * FROM #tab1
            RETURN (0)
        END

    /*
    ** Parameter Check:  @property.
    ** Check to make sure that @property is a valid property in
    ** msdb.dbo.MSdistpublishers.
    */
    IF @property IS NULL OR LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) NOT IN 
        ('distribution_db',
         'working_directory',
         'security_mode',
         'login',
         'password',
         'active',
         'trusted')
        BEGIN
            RAISERROR (14115, 16, -1, 
            '''distribution_db'', ''working_directory'', ''security_mode'', ''login'', ''password'', ''active'', or ''trusted''')
            RETURN (1)
        END

    /*
    ** Check to make sure this is a distributor
    */
    IF NOT EXISTS (SELECT * FROM master..sysservers
              WHERE UPPER(datasource) = UPPER(@@SERVERNAME) collate database_default
                 AND srvstatus & 8 <> 0)
    BEGIN
        RAISERROR (14114, 16, -1, @@SERVERNAME)
        RETURN(1)
    END

    -- Get the distribution db name.
    select @distribdb = distribution_db from msdb..MSdistpublishers where
        UPPER(name) = UPPER(@publisher) collate database_default

    /*
    ** Change the property.
    */
    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'distribution_db'
        BEGIN
            IF @value IS NULL
                BEGIN
                    RAISERROR (14043, 16, -1, '@value') 
                    RETURN (1)
                END

            IF @value <> @distribdb and 
                EXISTS (SELECT * FROM msdb.dbo.MSdistpublishers WHERE
                UPPER(name) = UPPER(@publisher) collate database_default and active = 1)
            BEGIN
                RAISERROR (21046, 16, -1)
                RETURN (1)
            END

            /*
            ** Check if database is configured as a distributor database
            */
            IF NOT EXISTS (SELECT * FROM master..sysdatabases
              WHERE name = @value collate database_default
                 AND category & @distbit <> 0)
                BEGIN
                    RAISERROR (14117, 16, -1, @new_database)
                    RETURN(1)
                END

            UPDATE msdb..MSdistpublishers SET distribution_db = @value
                WHERE UPPER(name) = UPPER(@publisher) collate database_default
            IF @@error <> 0 
                BEGIN
                    RETURN (1)
                END
        END
   
    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'working_directory'
        BEGIN
            IF @value IS NULL
                BEGIN
                    RAISERROR (14043, 16, -1, '@value') 
                    RETURN (1)
                END

            
            -- Validate the working directory 
            -- Remove heading and trailing spaces
            select @value = RTRIM(LTRIM(@value))

            -- if the last char is '\', remove it.

            if substring(@value, len(@value),1) = '\'
                select @value = substring(@value, 1,
                    len(@value)-1)

            -- Don't do validation if it is a UNC path due to security problem.
            -- If the server is started as a service using local system account, we
            -- don't have access to the UNC path.
            if substring(@value, 1,2) <> '\\'
            begin
                select @command = 'dir "' + fn_escapecmdshellsymbolsremovequotes(@value) collate database_default + N'"'
                exec @retcode = master..xp_cmdshell @command, 'no_output'
                if @@error <> 0
                    return 1
                if @retcode <> 0 
                begin
                    raiserror (21037, 16, -1, @value)
                    return 1
                end
            end

            UPDATE msdb..MSdistpublishers SET working_directory = @value
                WHERE UPPER(name) = UPPER(@publisher) collate database_default
            IF @@error <> 0 
                BEGIN
                    RETURN (1)
                END
        END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'security_mode'
        BEGIN
            IF @value IS NULL
                BEGIN
                    RAISERROR (14043, 16, -1, '@value') 
                    RETURN (1)
                END

            /*
            ** Set the SecurityMode registry key value
            */
            SELECT @new_security_mode = CONVERT(int, @value)

            /* 
            ** Check for invalid values 
            */
            IF @new_security_mode < 0 OR @new_security_mode > 1
                BEGIN
                    RAISERROR(14109, 16, -1)
                    RETURN (1)
                END

            IF (UPPER(@publisher) = UPPER(@@SERVERNAME) and ( @platform_nt != platform() & @platform_nt ) and @new_security_mode = 1)
            BEGIN
                RAISERROR(21038, 16, -1)
                RETURN (1)
            END

            UPDATE msdb..MSdistpublishers SET security_mode = @new_security_mode
                WHERE UPPER(name) = UPPER(@publisher) collate database_default
            IF @@error <> 0 
                BEGIN
                    RETURN (1)
                END
        END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'login'
        BEGIN
            IF @value IS NULL
                BEGIN
                    RAISERROR (14043, 16, -1, '@value') 
                    RETURN (1)
                END

            /*
            ** Set the Login registry key value
            */
            SELECT @new_login = CONVERT(sysname, @value)

            UPDATE msdb..MSdistpublishers SET login = @new_login
                WHERE UPPER(name) = UPPER(@publisher) collate database_default
            IF @@error <> 0 
                BEGIN
                    RETURN (1)
                END
        END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'password'
        BEGIN
            /*
            ** Set the Password registry key value
            */
            SELECT @new_password = CONVERT(nvarchar(524), @value)

            -- Encrypt the password
            EXEC @retcode = master.dbo.xp_repl_encrypt @new_password OUTPUT
            IF @@error <> 0 OR @retcode <> 0
                RETURN (1)

            UPDATE msdb..MSdistpublishers SET password = @new_password
                WHERE UPPER(name) = UPPER(@publisher) collate database_default
            IF @@error <> 0 
                BEGIN
                    RETURN (1)
                END
         END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'active'
        BEGIN
            /*
            ** Check for a valid  value.
            */

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

            IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'
            begin
                -- Clean up the database in case of the remote publisher is reinstalling publishing.
                SELECT @command = @distribdb + '.dbo.sp_MSdistpublisher_cleanup'
                exec @retcode = @command @publisher
                if @retcode <> 0 or @@error <> 0
                    return 1
                SELECT @new_active = 1
            end
            ELSE
            BEGIN
                SELECT @new_active = 0
            END

            /*
            ** Set the Active registry key value
            */
            UPDATE msdb..MSdistpublishers SET active = @new_active
                WHERE UPPER(name) = UPPER(@publisher) collate database_default
            IF @@error <> 0
                BEGIN
                    RETURN (1)
                END

        END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'trusted'
        BEGIN
            /*
            ** Check for a valid  value.
            */

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

            declare @fExists int

            IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'
            begin
                SELECT @new_trusted = 1
                exec @fExists = dbo.sp_MSIfExistsRemoteLogin @publisher, 'distributor_admin', 'sa'
                if( @fExists = 1 )
                BEGIN
                    EXECUTE @retcode = dbo.sp_remoteoption @publisher, 'distributor_admin', 'sa', trusted, true
                    IF @@error <> 0 OR @retcode <> 0
                    BEGIN
                        RAISERROR (14075, 16, -1)
                        RETURN (1)
                    END
                END


                EXECUTE @retcode = dbo.sp_remoteoption @publisher, 'distributor_admin', 
                    'distributor_admin', trusted, true
                IF @@error <> 0 OR @retcode <> 0
                BEGIN
                    RAISERROR (14075, 16, -1)
                    RETURN (1)
                END
            end
            ELSE
            BEGIN
                SELECT @new_trusted = 0
     
                exec @fExists = dbo.sp_MSIfExistsRemoteLogin @publisher, 'distributor_admin', 'sa'
                if( @fExists = 1 )
                BEGIN
                    EXECUTE @retcode = dbo.sp_remoteoption @publisher, 'distributor_admin', 'sa', trusted, false
                    IF @@error <> 0 OR @retcode <> 0
                    BEGIN
                        RAISERROR (14075, 16, -1)
                        RETURN (1)
                    END
                END


                EXECUTE @retcode = dbo.sp_remoteoption @publisher, 'distributor_admin',
                    'distributor_admin', trusted, 'false'
                IF @@error <> 0 OR @retcode <> 0
                BEGIN
                    RAISERROR (14075, 16, -1)
                    RETURN (1)
                END
            END

            /*
            ** Set the trusted property
            */
            UPDATE msdb..MSdistpublishers SET trusted = @new_trusted
                WHERE UPPER(name) = UPPER(@publisher) collate database_default
            IF @@error <> 0
                BEGIN
                    RETURN (1)
                END
        END

  
    /*
    ** Return succeed.
    */
    RAISERROR (21035, 10, -1, @property)

DONE:    
    RETURN (0)
go

--------------------------------------------------------------------------------
--. sp_MScopyscriptfile
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MScopyscriptfile')
drop procedure sp_MScopyscriptfile
go
create proc sp_MScopyscriptfile (
	@scriptfile nvarchar(4000), 
	@cmd nvarchar(4000) OUTPUT
)
as
declare @directory nvarchar(4000)
declare @filename nvarchar(1024)
declare @subdirectory nvarchar(1024)
declare @retcode int

IF @scriptfile IS NULL
BEGIN
	RAISERROR (14043, 16, -1, '@scriptfile')
	RETURN (1)
END
-- Create the directory on distributor to store script.
exec sp_helpdistributor @directory=@directory OUTPUT
select @subdirectory = convert(nvarchar(64), GetDate(), 121)
select @subdirectory = replace(@subdirectory, N'-', N'')
select @subdirectory = replace(@subdirectory, N' ', N'')
select @subdirectory = replace(@subdirectory, N':', N'')
select @subdirectory = replace(@subdirectory, N'.', N'')
if(right(@directory, 1) = N'\')
	select @directory = @directory + @subdirectory
else
	select @directory = @directory + N'\' + @subdirectory
select @cmd = N'md "' + fn_escapecmdshellsymbolsremovequotes(@directory) collate database_default + '"'
exec @retcode = master..xp_cmdshell @cmd, NO_OUTPUT
if(@retcode <> 0)
begin
	raiserror(21330, 16, -1, @cmd)
	return (1)
end

-- Copy script to distributor
select @cmd = N'copy "' + fn_escapecmdshellsymbolsremovequotes(@scriptfile) collate database_default + N'" "' + fn_escapecmdshellsymbolsremovequotes(@directory) collate database_default + N'"'
exec @retcode = master..xp_cmdshell @cmd, NO_OUTPUT
if(@retcode <> 0)
begin 
	raiserror(21331, 16, -1, @cmd)
	return (1)
end

-- Prepare command, to be posted to log
select @filename = right(@scriptfile, charindex(N'\', reverse(@scriptfile)))
if(left(@filename, 1) = N'\')	
	select @cmd = @directory + @filename
else
	select @cmd = @directory + N'\' + @filename
go
exec sp_MS_marksystemobject sp_MScopyscriptfile
go

--------------------------------------------------------------------------------
--. sp_replproberemoteserver
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_replproberemoteserver')
drop procedure sp_replproberemoteserver
go
raiserror('Creating procedure sp_replproberemoteserver', 0,1)
go
--
-- Name: sp_replproberemoteserver
--
-- Description: This is a lightweight wrapper for calling xp_replproberremsrv
--              with the added nicety of looking up the agent command line
--              using the given jobid. This procedure supports two different
--              modes of operation based on the @no_rpc parameter. If the 
--              @no_rpc parameter is 1, this procedure will not attempt to make
--              rpc call to the Distributor. The local mode is mainly useful 
--              for verifying remote pull subscription agent.
-- 
-- Parameters: @remoteservername sysname (mandatory) 
--             @agent_type nvarchar(16) (optional, default = null) 
--             @agent_jobid uniqueidentifier (optional default = null)
--             @no_rpc bit (optional default = 0)
--
-- Notes: If @job_id is null, only activation verification will be carried
--        out.
--
-- Security: Execute permission of this procedure is granted to public. 
--           Internal security check is performed inside this procedure to
--           make sure that the caller is at least a db_owner of the current 
--           database.
--
-- Result: 'probe_succeeded' 0 or 1
--
-- Returns: 0    - succeeded
--          <> 0 - failed
--
create procedure dbo.sp_replproberemoteserver (
    @remoteservername sysname,
    @agent_type       nvarchar(16) = null,
    @agent_jobid      uniqueidentifier = null,
    @no_rpc           bit = 0
    ) 
as
begin
    set nocount on
    declare @retcode        int
    declare @commandline    nvarchar(3200)
    declare @distributor    sysname
    declare @distributiondb sysname     
    declare @distproc       nvarchar(255)
    declare @succeeded      nvarchar(10)

    select @retcode = 0
    select @commandline = null
    select @succeeded = null

    -- security check, db_owner
    -- Note that this proc can be called from either a publisher database or
    -- a subscriber database
    exec @retcode = dbo.sp_MSreplcheck_publish
    if @@error <> 0 or @retcode <> 0
        return (1)
    
    if @agent_type is null
    begin
        select @agent_type = 'distribution'
    end
    
    -- @remoteservername cannot be null or empty
    select @remoteservername = rtrim(ltrim(@remoteservername))
    if @remoteservername is null or
       @remoteservername = N''
    begin
        raiserror(21263,16,-1,'@remoteservername')
        return 1
    end     
    
    -- @agent_type must be 'distribution' or 'merge'
    if @agent_type not in (N'distribution', N'merge')
    begin
        raiserror(21182,16,-1) 
        return 1
    end

    -- Get Distributor information
    if @no_rpc = 0
    begin
        exec @retcode = dbo.sp_helpdistributor @rpcsrvname = @distributor output,
                                          @distribdb = @distributiondb output
        if @@error <> 0 or @retcode <> 0
            return 1
    end
    else
    begin
        select @distributor = @@servername
    end

    if upper(@@servername) <> upper(@distributor)
    begin
        select @distproc = ltrim(rtrim(@distributor)) + '.' + 
            'master.dbo.sp_replproberemoteserver'
        exec @retcode = @distproc @remoteservername, 
                                  @agent_type, 
                                  @agent_jobid, 
                                  1
        return @retcode
    end        
    else
    begin
        -- If the given @job_id is not null, try to get
        -- the agent command line
        if @agent_jobid is not null 
        begin
            select @commandline = fn_replgetagentcommandlinefromjobid(
                @agent_type,
                @agent_jobid) collate database_default

            if @commandline is null
            begin
                raiserror(21361,6,-1, @agent_type) 
                select 'probe_succeeded' = 0
                return 1
            end

        end 
 
        exec @retcode = master.dbo.xp_replproberemsrv 
            @remoteservername, 
            @agent_type, 
            @succeeded output,
            @commandline 

        if lower(@succeeded collate SQL_Latin1_General_CP1_CS_AS) = N'true'  
            select 'probe_succeeded' = 1
        else
            select 'probe_succeeded' = 0

        return @retcode
    end

    select 'probe_succeeded' = 0

    return 1
end
go
exec sp_MS_marksystemobject sp_replproberemoteserver
go
grant execute on dbo.sp_replproberemoteserver to public
go
if exists (select * from sysobjects
     where type = 'X'
          and name = 'sp_replsetoriginator_internal' )
exec dbo.sp_dropextendedproc 'sp_replsetoriginator_internal'
go
sp_addextendedproc  'sp_replsetoriginator_internal', 'replsetoriginator extended procedure'
go
if exists (select * from sysobjects
     where type = 'X'
          and name = 'sp_replsetoriginator' )
exec dbo.sp_dropextendedproc 'sp_replsetoriginator'
go
if exists (select * from sysobjects
     where type = 'P'
          and name = 'sp_replsetoriginator' )
drop procedure sp_replsetoriginator
go
--
-- Name:    
--          sp_replsetoriginator
--          
-- Description: 
--          wrapper for sp_replsetoriginator_internal with DBO check
--  
-- Security: 
--          DBO only
--
-- Returns:  	0 : success
--		1 : failure          
--      
-- Owner:   
--          qunguo
--
--
raiserror('Creating procedure sp_replsetoriginator',0,1)
go
create proc sp_replsetoriginator (
	@originator_srv sysname, 
	@originator_db sysname
)
as
	declare @retcode int
	/*
    	** Security Check, this proc is only called by distrib.exe, against sub db as DBO
    	*/
    	exec @retcode = dbo.sp_MSreplcheck_subscribe
    	if @@ERROR <> 0 or @retcode <> 0
	begin
        	return(1)
	end

    	exec @retcode = dbo.sp_replsetoriginator_internal @originator_srv, @originator_db
    	if @@ERROR <> 0 or @retcode <> 0
	begin
        	return(1)
	end

	return 0
go
exec sp_MS_marksystemobject sp_replsetoriginator
go
grant execute on dbo.sp_replsetoriginator to public
go

if exists (select * from sysobjects
     where type = 'P'
          and name = 'sp_replsetoriginator_pal' )
drop procedure sp_replsetoriginator_pal
go
--
-- Name:    
--          sp_replsetoriginator_pal
--          
-- Description: 
--          wrapper for sp_replsetoriginator_internal with pull access check
--  
-- Security: 
--          DBO or PAL access
--
-- Returns:  	0 : success
--		1 : failure          
--      
-- Owner:   
--          qunguo
--
--
raiserror('Creating procedure sp_replsetoriginator_pal',0,1)
go
create proc sp_replsetoriginator_pal (
	@originator_srv sysname, 
	@originator_db sysname,
	@publication sysname
)
as
	declare @retcode int
	/*
    	** Security Check, this is called by updatable subscriber in sync-tran proc, hence PAL access
    	*/
	exec @retcode = dbo.sp_MSreplcheck_pull @publication = @publication
	if @@error <> 0 or @retcode <> 0
	begin
		return (1)
	end

    	exec @retcode = dbo.sp_replsetoriginator_internal @originator_srv, @originator_db
    	if @@ERROR <> 0 or @retcode <> 0
	begin
        	return(1)
	end

	return 0
go
exec sp_MS_marksystemobject sp_replsetoriginator_pal
go
grant execute on dbo.sp_replsetoriginator_pal to public
go
if exists (select * from sysobjects
     	where type = 'X'
            and name = 'sp_replincrementlsn' )
	exec dbo.sp_dropextendedproc 'sp_replincrementlsn'
go
if exists (select * from sysobjects
     	where type = 'P'
            and name = 'sp_replincrementlsn' )
	drop procedure sp_replincrementlsn

if exists (select * from sysobjects
     	where type = 'X'
            and name = 'sp_replincrementlsn_internal' )
	exec dbo.sp_dropextendedproc 'sp_replincrementlsn_internal'
go
sp_addextendedproc  'sp_replincrementlsn_internal', 'replincrementlsn_internal extended procedure'
go
--
-- Name:    
--          sp_replincrementlsn
--          
-- Description: 
--          wrapper for sp_replincrementlsn_internal with DBO check
--  
-- Security: 
--          DBO only
--
-- Returns:  	0 : success
--		1 : failure          
--      
-- Owner:   
--          qunguo
--
--
raiserror('Creating procedure sp_replincrementlsn',0,1)
go
create proc sp_replincrementlsn (
	@xact_seqno binary(10) OUTPUT
)
as
	declare @retcode int
	/*
    	** Security Check, this proc is called by snapshot agent or sp_repladd(drop)column, against pub db as DBO
    	*/
    	exec @retcode = dbo.sp_MSreplcheck_publish
    	if @@ERROR <> 0 or @retcode <> 0
	begin
        	return(1)
	end

    	exec @retcode = dbo.sp_replincrementlsn_internal @xact_seqno OUTPUT
    	if @@ERROR <> 0 or @retcode <> 0
	begin
        	return(1)
	end

	return 0
go
exec sp_MS_marksystemobject sp_replincrementlsn
go
grant execute on dbo.sp_replincrementlsn to public
go

if exists (select * from sysobjects
	where type = 'X'
                and name = 'sp_replpostsyncstatus' )
        exec dbo.sp_dropextendedproc 'sp_replpostsyncstatus'

if exists (select * from sysobjects
	where type = 'P'
                 and name = 'sp_replpostsyncstatus' )
        drop procedure sp_replpostsyncstatus

if exists (select * from sysobjects
        where type = 'X'
                 and name = 'sp_replpostsyncstatus_int' )
        exec dbo.sp_dropextendedproc 'sp_replpostsyncstatus_int'
go
sp_addextendedproc  'sp_replpostsyncstatus_int', 'sp_replpostsyncstatus_int extended procedure'
go

--
-- Name:    
--          sp_replpostsyncstatus
--          
-- Description: 
--          wrapper for sp_replpostsyncstatus_int with DBO check
--  
-- Security: 
--          DBO only
--
-- Returns:  	0 : success
--		1 : failure          
--      
-- Owner:   
--          qunguo
--
--
raiserror('Creating procedure sp_replpostsyncstatus',0,1)
go
create proc sp_replpostsyncstatus (
	@pubid int, 
	@artid int, 
	@syncstat int, 
	@xact_seqno binary(10) OUTPUT
)
as
	declare @retcode int
	/*
    	** Security Check, this proc is called by snapshot agent as DBO on publisher
    	*/
    	exec @retcode = dbo.sp_MSreplcheck_publish
    	if @@ERROR <> 0 or @retcode <> 0
	begin
        	return(1)
	end

    	exec @retcode = dbo.sp_replpostsyncstatus_int @pubid, @artid, @syncstat, @xact_seqno OUTPUT
    	if @@ERROR <> 0 or @retcode <> 0
	begin
        	return(1)
	end

	return 0
go
exec sp_MS_marksystemobject sp_replpostsyncstatus
go
grant execute on dbo.sp_replpostsyncstatus to public
go


--------------------------------------------------------------------------------
--.	System objects (repltran.sql)
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MScreate_pub_tables')
drop procedure sp_MScreate_pub_tables

print ''
print 'Creating procedure sp_MScreate_pub_tables'
go
CREATE PROCEDURE sp_MScreate_pub_tables
AS
    DECLARE @fError int
    SELECT @fError = 0

    -- enable 'create tables as pseudo system tables

    -- sp_MS_upd_sysobj_category is obsolete, use sp_MS_marksystemobject instead
    -- exec dbo.sp_MS_upd_sysobj_category 1


    /* 
    ** Msg 226, Level 16, State 9
    ** CREATE TABLE system-table command not allowed within multi-statement transaction.
    */
    /*
    BEGIN TRAN sp_create_central_pub_tables
    */
    
    /* Creating 'sysarticles' */
    IF not exists (select * from sysobjects where name = 'sysarticles')
    BEGIN
        create table dbo.sysarticles
        (
        artid               int                 identity NOT NULL,
        columns             varbinary(32)       NULL,
        creation_script     nvarchar(255)       NULL,
        del_cmd             nvarchar(255)       NULL,
        description         nvarchar(255)       NULL,
        dest_table          sysname             NOT NULL,
        filter              int                 NOT NULL,
        filter_clause       ntext               NULL,
        ins_cmd             nvarchar(255)       NULL,
        name                sysname             NOT NULL,
        objid               int                 NOT NULL,
        pubid               int                 NOT NULL,
        pre_creation_cmd    tinyint             NOT NULL,
        status              tinyint             NOT NULL,
        sync_objid          int                 NOT NULL,
        type                tinyint             NOT NULL,
        upd_cmd             nvarchar(255)       NULL,
        schema_option       binary(8)           NULL,
        dest_owner          sysname             NULL
        -- Note: Please update sysextendedarticlesview whenever
        -- there is a schema change in sysarticles
        )

        EXEC dbo.sp_MS_marksystemobject 'sysarticles'

        IF @@error<>0
        BEGIN
            GOTO ERROR
        END

        create unique nonclustered index unc1sysarticles
            on sysarticles(artid, pubid) 
        
        IF @@error<>0
        BEGIN
            GOTO ERROR
        END

    END

    /* Creating 'sysschemaarticles' */
    IF not exists (select * from sysobjects where name = 'sysschemaarticles')
    BEGIN
        create table dbo.sysschemaarticles
        (
        artid               int                 NOT NULL,
        creation_script     nvarchar(255)       NULL,
        description         nvarchar(255)       NULL,
        dest_object         sysname             NOT NULL,
        name                sysname             NOT NULL,
        objid               int                 NOT NULL,
        pubid               int                 NOT NULL,
        pre_creation_cmd    tinyint             NOT NULL,
        status              int                 NOT NULL,
        type                tinyint             NOT NULL,
        schema_option       binary(8)           NULL,
        dest_owner          sysname             NULL
        )
        
        IF @@error<>0
        BEGIN
            GOTO ERROR
        END

        EXEC dbo.sp_MS_marksystemobject 'sysschemaarticles'

        IF @@error<>0
        BEGIN
            GOTO ERROR
        END

        create unique nonclustered index unc1sysschemaarticles
            on sysschemaarticles(artid, pubid)

        IF @@error<>0
        BEGIN
            GOTO ERROR
        END
        
    END

    /* Creating 'sysextendedarticlesview' */
    IF not exists (select * from sysobjects where name = 'sysextendedarticlesview')
    BEGIN
        exec ('create view dbo.sysextendedarticlesview
               as
               select * from sysarticles
               union all
               select artid, NULL, creation_script, NULL, description,
               dest_object, NULL, NULL, NULL, name, objid, pubid, 
               pre_creation_cmd, status, NULL, type, NULL, 
               schema_option, dest_owner from sysschemaarticles
               go')

        IF @@error<>0
        BEGIN
            GOTO ERROR
        END 

        EXEC dbo.sp_MS_marksystemobject 'sysextendedarticlesview'

        IF @@error<>0
        BEGIN
            GOTO ERROR
        END

    END

    /* Creating 'syspublications' */
    IF NOT EXISTS (select * from sysobjects where name = 'syspublications')
    BEGIN
        CREATE TABLE dbo.syspublications (
        description                 nvarchar(255)   NULL,
        name                        sysname         NOT NULL,
        pubid                       int    identity NOT NULL,
        repl_freq                   tinyint         NOT NULL,
        status                      tinyint         NOT NULL,
        sync_method                 tinyint         NOT NULL,
        snapshot_jobid              binary(16)		NULL,
        independent_agent           bit             NOT NULL,
        immediate_sync              bit             NOT NULL,
        enabled_for_internet        bit             NOT NULL,
        allow_push                  bit             NOT NULL,
        allow_pull                  bit             NOT NULL,
        allow_anonymous             bit             NOT NULL,
        immediate_sync_ready        bit             NOT NULL,
        -- SyncTran
		allow_sync_tran             bit             NOT NULL,
        autogen_sync_procs          bit             NOT NULL,
        retention                   int             NULL,
		-- The following are post 7.0
        allow_queued_tran           bit   default 0	not null, 
        -- portable snapshot support
        snapshot_in_defaultfolder           bit   default 1 NOT NULL,         
        alt_snapshot_folder         nvarchar(255)   NULL,
        -- snapshot pre/post- command
        pre_snapshot_script         nvarchar(255)   NULL,
        post_snapshot_script        nvarchar(255)   NULL,
        -- Snapshot compression
        compress_snapshot           bit   default 0 NOT NULL,          
        -- Post 7.0 Ftp support
        ftp_address                 sysname         NULL,
        ftp_port                    int   default 21 NOT NULL,
        ftp_subdirectory            nvarchar(255)   NULL,
        ftp_login                   sysname         NULL default N'anonymous',
        ftp_password                nvarchar(524)   NULL,
		allow_dts		bit default 0 not null,
		allow_subscription_copy		bit default 0 not null,
		centralized_conflicts		bit				NULL, -- 0 False, 1 True
		conflict_retention			int				NULL, -- 60
		conflict_policy				int 			NULL, -- 1 = PubWins, 2 = SubWins, 3 = Reinit
		queue_type					int				NULL,  -- 1 = MSMQ, 2 = SQL
		ad_guidname					sysname			NULL,
		backward_comp_level	int default 10 not NULL -- default is sphinx
        )

        EXEC dbo.sp_MS_marksystemobject 'syspublications'

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END

        create unique clustered index uc1syspublications
            on syspublications (pubid) 
        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END

        create unique nonclustered index unc2syspublications
            on syspublications (name)

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END

    END

       /* Creating 'syssubscriptions' */
    IF not exists (select * from sysobjects where name = 'syssubscriptions')
    BEGIN

        CREATE TABLE dbo.syssubscriptions
        (
        artid                    int                NOT NULL,
        srvid                    smallint        NOT NULL,
        dest_db                    sysname        NOT NULL,
        status                    tinyint            NOT NULL,
        sync_type                tinyint            NOT NULL,
        login_name                sysname        NOT NULL,
        subscription_type        int                NOT NULL,
        distribution_jobid      binary(16)          NULL,
        timestamp NOT NULL,
        -- SyncTran
        update_mode             tinyint            NOT NULL, -- 0 (read only), 1 (Sync Tran), 
		                                                     -- Queued Tran
															 -- 2 (Queued Tran) , 3 (Failover), 4(sqlqueued), 5(sqlqueued failover)
        loopback_detection      bit NOT NULL,
  		queued_reinit		   bit DEFAULT 0 NOT NULL 
      )

        EXEC dbo.sp_MS_marksystemobject 'syssubscriptions'

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END

        create unique nonclustered index unc1syssubscriptions
            on syssubscriptions (artid, srvid, dest_db)

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END

    END

    -- SyncTran
    /* Creating 'sysarticleupdates' */

    IF not exists (select * from sysobjects where name = 'sysarticleupdates')
    BEGIN

        CREATE TABLE dbo.sysarticleupdates
        (
        artid                  int       NOT NULL,
        pubid                  int       NOT NULL,
        sync_ins_proc          int       NOT NULL,     -- ID of sproc handling Insert Sync Transactions
        sync_upd_proc          int       NOT NULL,     -- ID of sproc handling Update Sync Transactions
        sync_del_proc          int       NOT NULL,     -- ID of sproc handling Delete Sync Transactions
        autogen                bit       NOT NULL,
		sync_upd_trig          int       NOT NULL,     -- Note 7.0 upgrade issue
		conflict_tableid	   int		 NULL,		   -- ID of conflict table for this article
		ins_conflict_proc	   int		 NULL,		   -- ID of sproc to log conflicts
		identity_support	   bit default 0 not null  -- Whether or not do auto identity range 
		)     

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END


        -- mark the index as a system object
        exec dbo.sp_MS_marksystemobject 'sysarticleupdates'

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END

        create unique nonclustered index unc1sysarticleupdates
            on sysarticleupdates (artid, pubid)

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END
    END
    -- end SyncTran


    IF not exists (select * from sysobjects where name = 'MSpub_identity_range')
    BEGIN
        CREATE TABLE dbo.MSpub_identity_range
        (
			objid int not null,
			range bigint not null,
			pub_range bigint not null,
            current_pub_range bigint not null,
			threshold int not null,
			last_seed bigint null -- It will be not when uninitialized.
		)     

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END


        -- mark the index as a system object
        exec dbo.sp_MS_marksystemobject 'MSpub_identity_range'

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END

        create unique nonclustered index unc1MSpub_identity_range
            on MSpub_identity_range (objid)

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END
    END

    IF not exists (select * from sysobjects where name = 'systranschemas' and uid = 1)
    BEGIN
        CREATE TABLE dbo.systranschemas
        (
			tabid int not null,
			startlsn binary(10) not null,
			endlsn binary(10) not null
	)     

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END

        -- mark the index as a system object
        exec dbo.sp_MS_marksystemobject 'systranschemas'

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END

        create unique clustered index uncsystranschemas
            on systranschemas (startlsn)

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END
    END

CLEANUP:

    -- disable 'create tables as pseudo system tables
    -- sp_MS_upd_sysobj_category is obsolete, use sp_MS_marksystemobject instead
    -- exec dbo.sp_MS_upd_sysobj_category 2
    RETURN( @fError )

ERROR:

    select @fError = 1
    GOTO CLEANUP

GO
 
EXEC dbo.sp_MS_marksystemobject sp_MScreate_pub_tables
GO
--------------------------------------------------------------------------------
--. sp_articlesynctranprocs
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_articlesynctranprocs')
revoke exec on dbo.sp_articlesynctranprocs from public

--------------------------------------------------------------------------------
--. sp_gettypestring
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_gettypestring')
revoke exec on dbo.sp_gettypestring from public

--------------------------------------------------------------------------------
--. sp_MSgettranconflictname
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSgettranconflictname')
revoke execute on dbo.sp_MSgettranconflictname from public

--------------------------------------------------------------------------------
--. sp_MSmakeconflicttable
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSmakeconflicttable')
revoke execute on dbo.sp_MSmakeconflicttable from public

--------------------------------------------------------------------------------
--. sp_MSmaketrancftproc
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSmaketrancftproc')
revoke execute on dbo.sp_MSmaketrancftproc from public

--------------------------------------------------------------------------------
--. sp_MSmark_proc_norepl
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSmark_proc_norepl')
revoke execute on dbo.sp_MSmark_proc_norepl from public

--------------------------------------------------------------------------------
--. sp_MSscript_beginproc
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_beginproc')
revoke execute on dbo.sp_MSscript_beginproc  from public

--------------------------------------------------------------------------------
--. sp_MSscript_compensating_send
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_compensating_send')
revoke execute on dbo.sp_MSscript_compensating_send from public

--------------------------------------------------------------------------------
--. sp_MSscript_delete_pubwins
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_delete_pubwins')
revoke execute on dbo.sp_MSscript_delete_pubwins from public

--------------------------------------------------------------------------------
--. sp_MSscript_delete_statement
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_delete_statement')
revoke execute on dbo.sp_MSscript_delete_statement from public

--------------------------------------------------------------------------------
--. sp_MSscript_delete_subwins
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_delete_subwins')
revoke execute on dbo.sp_MSscript_delete_subwins from public

--------------------------------------------------------------------------------
--. sp_MSscript_ExecutionMode_stmt
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_ExecutionMode_stmt')
revoke execute on dbo.sp_MSscript_ExecutionMode_stmt from public

--------------------------------------------------------------------------------
--. sp_MSscript_insert_statement
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_insert_statement')
revoke execute on dbo.sp_MSscript_insert_statement from public

--------------------------------------------------------------------------------
--. sp_MSscript_security
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_security')
revoke execute on dbo.sp_MSscript_security  from public

--------------------------------------------------------------------------------
--. sp_MSscript_update_statement
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_update_statement')
revoke execute on dbo.sp_MSscript_update_statement from public

--------------------------------------------------------------------------------
--. sp_scriptpkwhereclause
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_scriptpkwhereclause')
revoke exec on dbo.sp_scriptpkwhereclause from public

--------------------------------------------------------------------------------
--. sp_scriptreconwhereclause
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_scriptreconwhereclause')
revoke exec on dbo.sp_scriptreconwhereclause from public

--------------------------------------------------------------------------------
--. sp_scriptupdateparams
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_scriptupdateparams')
revoke exec on dbo.sp_scriptupdateparams from public
go
--------------------------------------------------------------------------------
--. sp_MSscript_ExecutionMode_stmt
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_ExecutionMode_stmt')
drop procedure sp_MSscript_ExecutionMode_stmt
go
raiserror('Creating procedure sp_MSscript_ExecutionMode_stmt', 0,1)
go
create procedure sp_MSscript_ExecutionMode_stmt (
	@publication sysname, 
	@article     sysname,
	@proctype	int = 0)		-- 0 insert, 1 delete, 2 update
as
begin
	declare @cmd		nvarchar(4000)
			,@artid		int
			,@pubid		int
			,@queued_pub bit

	select @pubid = pubid, @queued_pub = allow_queued_tran 
		from syspublications where name = @publication
	select @artid = artid from sysarticles where name = @article and pubid = @pubid

	--
	-- For queued execution check if we are in the phase of reinitialization
	--
	select @cmd = N'
	' + N'--
	' + N'-- Check if we are in the process of Reinitialization
	' + N'-- if yes then return
	' + N'--' 
	insert into #proctext(procedure_text) values( @cmd )

	select @cmd = N'
	exec @retcode = dbo.sp_MSgetarticlereinitvalue @orig_server, @orig_db, ' + 
	cast(@artid as nvarchar(5)) + N', @reinit output
	if (@retcode != 0 or @@error != 0)
		return -1 '
	insert into #proctext(procedure_text) values( @cmd )

	select @cmd = N'
	if (@reinit = 1) -- Resync state
	begin
		if (@execution_mode = @immediate)
			return -2'
	if (@queued_pub = 1)
	begin
		select @cmd = @cmd + N'
		else
			return 4 -- Queued Resync state'
	end
	select @cmd = @cmd + N'
	end
	'
	insert into #proctext(procedure_text) values( @cmd )
	
	--
	-- set loopback detection for immediate
	--
	select @cmd = N'
	if (@execution_mode = @immediate)
	begin
		' + N'--
		' + N'-- For immediate
		' + N'-- enable loopback detection
		' + N'--
		exec @retcode = dbo.sp_replsetoriginator_pal @orig_server, @orig_db, ''' 
			+ master.dbo.fn_MSgensqescstr(@publication) collate database_default + N'''
		if (@retcode != 0 or @@error != 0) 
			return -1
	end'
	insert into #proctext(procedure_text) values( @cmd )

	/********* no need to disable since we never enable
	if (@queued_pub = 1)
	begin
		--
		-- disable loopback for queued
		--
		select @cmd = N'
	else if (@execution_mode in (@QFirstPass, @QSubWins))
	begin
		' + N'--
		' + N'-- For queued
		' + N'-- disable loopback detection
		' + N'--
		exec @retcode = dbo.sp_replsetoriginator N''anull'', N''anull''
		if (@retcode != 0 or @@error != 0) 
			return -1
	end'
		insert into #proctext(procedure_text) values( @cmd )
	end
	**********/

	if (@queued_pub = 1)
	begin
		--
		-- Queued reinitialization mode execution
		--
		select @cmd = N'
	else if (@execution_mode = @QReinit)
	begin
		' + N'--
		' + N'-- For Queued reinitialization
		' + N'-- Set the Queue and subscription for reinit
		' + N'--'
		insert into #proctext(procedure_text) values( @cmd )

		select @cmd = N'
		exec @retcode = dbo.sp_reinitsubscription @publication = ''' 
			+ master.dbo.fn_MSgensqescstr(@publication) collate database_default + N''', @article = ''' +  
			master.dbo.fn_MSgensqescstr(@article) collate database_default + N''', @subscriber = @orig_server, @destination_db = @orig_db
		if (@retcode != 0 or @@error != 0)
			return -1
		else
			return 0
	end'

		insert into #proctext(procedure_text) values( @cmd )
	end

	--
	-- all done
	--
	return 0
end 
go
EXEC dbo.sp_MS_marksystemobject sp_MSscript_ExecutionMode_stmt
GO

--------------------------------------------------------------------------------
--. sp_MSscript_pub_upd_trig
--------------------------------------------------------------------------------

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_pub_upd_trig')
drop procedure sp_MSscript_pub_upd_trig
go
raiserror('Creating procedure sp_MSscript_pub_upd_trig', 0,1)
go
create procedure sp_MSscript_pub_upd_trig
(
    @publication sysname, 
    @article     sysname,
    @procname    sysname
)
as
begin
    declare @cmd       nvarchar(4000)
    declare @qualname  nvarchar(512)
    declare @objid	   int
    declare @columns   binary(32)
                ,@retcode int

    set nocount on
    --
    -- security check
    --
    exec @retcode = dbo.sp_MSreplcheck_publish
    if @@error <> 0 or @retcode <> 0
    begin
        return (1)
    end

    -- Create temp table
