	drop table #guid_name_for_active_directory
	if @returnstring is NULL
		select @returnstring = N''
	raiserror(21363, 16, -1, @publication, @returnstring)
	return (1) 
FAILURE:
    RAISERROR (14018, 16, -1)
    /* UNDONE : This code is specific to 6.X nested transaction semantics */
    if @@TRANCOUNT > 0
    begin
        ROLLBACK TRANSACTION sp_addmergepublication
        COMMIT TRANSACTION
    end
    return (1)
go
exec dbo.sp_MS_marksystemobject sp_addmergepublication 
go

grant execute on dbo.sp_addmergepublication to public
go

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

CREATE PROCEDURE sp_changemergepublication (
    @publication sysname,              	/* Publication name */
    @property sysname = NULL,          	/* The property to change */
    @value nvarchar(255) = NULL,        /* The new property value */
    @force_invalidate_snapshot bit = 0,	/* Force invalidate existing snapshot */
    @force_reinit_subscription bit = 0	/* Force reinit subscription */
    ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */
	declare @max_concurrent_merge	int
    declare @cmd                nvarchar(255)
    declare @pubid              uniqueidentifier
    declare @pubidstr           nvarchar(38)
    declare @retcode            int
    declare @retention          int
    declare @statusid           tinyint
    declare @sync_modeid        tinyint
    declare @distributor        sysname
    declare @distproc           nvarchar(300)
    declare @value_bit          bit
    declare @snapshot_ready 	tinyint
    declare @subscribed         int
    declare @dbname             sysname
    declare @distribdb          sysname
    declare @alt_snapshot_folder nvarchar(255)
    declare @enabled_for_internet bit
    declare @ftp_address        sysname
    declare @enc_ftp_password   nvarchar(524)
    declare @snapshot_in_defaultfolder bit
	declare @dynamic_filters_id	int
    declare @schemaversion      int 
    declare @schemaguid         uniqueidentifier
    declare @schematype         int
    declare @schematext         nvarchar(2000)
	declare @artid				uniqueidentifier
	declare @in_ActiveD			bit
	declare @ad_guidname		sysname
	declare @db_name			sysname
    declare @compress_snapshot  bit
    declare @numeric_value      int

    /*
    ** Initializations
    */
    select @subscribed = 1
    select @snapshot_ready = 1
	select @db_name=db_name()
    /*
    ** Security Check
    */

    EXEC @retcode = dbo.sp_MSreplcheck_publish
    IF @@ERROR <> 0 or @retcode <> 0
        return (1)

    /* 
    ** Check if current DB is enabled for publication/subscription
    */
    
	/* make sure current database is enabled for merge replication */
    exec @retcode=dbo.sp_MSCheckmergereplication
    if @@ERROR<>0 or @retcode<>0
    	return (1)
            
    /*
    ** 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)
            INSERT INTO #tab1 VALUES ('description')
            INSERT INTO #tab1 VALUES ('status')
            INSERT INTO #tab1 VALUES ('retention')
            INSERT INTO #tab1 VALUES ('sync_mode')
            INSERT INTO #tab1 VALUES ('allow_push')
            INSERT INTO #tab1 VALUES ('allow_pull')
            INSERT INTO #tab1 VALUES ('allow_anonymous')
            INSERT INTO #tab1 VALUES ('enabled_for_internet')
            INSERT INTO #tab1 VALUES ('centralized_conflicts')
            INSERT INTO #tab1 VALUES ('snapshot_ready')
            INSERT INTO #tab1 VALUES ('snapshot_in_defaultfolder')
            INSERT INTO #tab1 VALUES ('alt_snapshot_folder')
            INSERT INTO #tab1 VALUES ('pre_snapshot_script')
            INSERT INTO #tab1 VALUES ('post_snapshot_script')
            INSERT INTO #tab1 VALUES ('compress_snapshot')
            INSERT INTO #tab1 VALUES ('ftp_address')
            INSERT INTO #tab1 VALUES ('ftp_port')
            INSERT INTO #tab1 VALUES ('ftp_subdirectory')
            INSERT INTO #tab1 VALUES ('ftp_login')
            INSERT INTO #tab1 VALUES ('ftp_password')
            INSERT INTO #tab1 VALUES ('conflict_retention')
            INSERT INTO #tab1 VALUES ('allow_subscription_copy')
            INSERT INTO #tab1 VALUES ('allow_synctoalternate')
            INSERT INTO #tab1 VALUES ('validate_subscriber_info')
            INSERT INTO #tab1 VALUES ('publish_to_ActiveDirectory')
            INSERT INTO #tab1 VALUES ('dynamic_filters')
            INSERT INTO #tab1 VALUES ('max_concurrent_merge')
            INSERT INTO #tab1 VALUES ('max_concurrent_dynamic_snapshots')
            select * FROM #tab1
            RETURN (0)
        END

    if @value is NULL and LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) not in 
                                                  ('description', 
                                                   'alt_snapshot_folder',
                                                   'pre_snapshot_script',
                                                   'post_snapshot_script',
                                                   'ftp_address',
                                                   'ftp_subdirectory',
                                                   'ftp_login',
                                                   'ftp_password',
                                                   'max_concurrent_merge')
        begin
            RAISERROR (20081, 16, -1, @property)
            RETURN (1)
        end

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

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

    select @pubid = pubid, 
           @ad_guidname=ad_guidname, --with value NULL if this publication is not in AD.
           @snapshot_ready=snapshot_ready,
    	   @dynamic_filters_id=dynamic_filters,
           @sync_modeid = sync_mode, 
           @alt_snapshot_folder = alt_snapshot_folder,
           @enabled_for_internet = enabled_for_internet,
           @ftp_address = ftp_address,
           @snapshot_in_defaultfolder = snapshot_in_defaultfolder,
           @compress_snapshot = compress_snapshot,
           @in_ActiveD = case when ad_guidname is NULL then 0 else 1 end 
      FROM sysmergepublications 
     WHERE name = @publication  and UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name()
    set @pubidstr = '''' + convert(nchar(36), @pubid) + ''''

    if @pubid IS NULL
        BEGIN
            RAISERROR (20026, 16, -1, @publication)
            RETURN (1)
        END
    else

    /*
    ** Parameter Check:  @property.
    ** Check to make sure that @property is a valid property in
    ** sysmergepublications.
    */
    if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('description', 'status', 'retention', 'sync_mode', 'allow_push', 
        'allow_pull', 'allow_anonymous', 'enabled_for_internet', 'centralized_conflicts', 'snapshot_ready', 
        'snapshot_in_defaultfolder', 'alt_snapshot_folder', 'pre_snapshot_script', 'post_snapshot_script', 
        'compress_snapshot', 'ftp_address', 'ftp_port', 'ftp_subdirectory','ftp_login',
        'ftp_password', 'conflict_retention', 'allow_subscription_copy', 'allow_synctoalternate',
        'validate_subscriber_info','publish_to_activedirectory','dynamic_filters','max_concurrent_merge', 'max_concurrent_dynamic_snapshots') 
        BEGIN
            RAISERROR (21053, 16, -1)
            RETURN (1)
        END


    /*
    ** Parameter Check:
    ** If sync_method of the publication is character mode (an indication that it supports
    ** third party Subscribers), pre/post-snapshot setting must be null   
    **
    */
    IF @sync_modeid = 1 
    BEGIN
        IF (LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'pre_snapshot_script' OR
            LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'post_snapshot_script') AND
            @value IS NOT NULL AND @value <> ''
        BEGIN
            RAISERROR (21151, 16, -1)
            RETURN (1)
        END   
    END

    /*
    ** Parameter Check:
    ** If the Publication's alt_snapshot_folder setting is null 
    ** snapshot compression cannot be enabled
    */
    IF ((@alt_snapshot_folder IS NULL OR @alt_snapshot_folder = '')) 
        AND LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'compress_snapshot'
        AND LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'   
    BEGIN
        RAISERROR (21157, 16, -1)        
        RETURN (1)
    END

    /* 
    ** Parameter Check:
    ** If enabled_for_internet is set to true, the publication must have a non-null
    ** ftp_address.
    */
    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'enabled_for_internet' AND
       LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = N'true' AND 
       (@ftp_address IS NULL OR @ftp_address = N'')
    BEGIN
        RAISERROR(21158, 16, -1)
        RETURN (1)
    END

    /*
    ** .. and ftp_address cannot be null if the publication is enabled for 
    ** internet.
    */
/*
    IF @enabled_for_internet = 1 AND
      (LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'ftp_address'
        AND (@value IS NULL OR @value = N''))
    BEGIN
        RAISERROR(21158, 16, -1)
        RETURN (1)
    END
*/
    /*
    ** .. and 'alternate snapshot folder' is not null and
    ** 'snapshot in default folder' is false
    **
    */
    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'enabled_for_internet' AND
        LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = N'true' AND
       (@alt_snapshot_folder IS NULL OR @alt_snapshot_folder = N'')
    BEGIN
        RAISERROR(21159, 16, -1)
        RETURN (1)
    END 

/*
    IF @enabled_for_internet = 1 AND
       (LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'alt_snapshot_folder' AND
        (LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) IS NULL OR LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = N''))
    BEGIN
        RAISERROR(21159, 16, -1)
        RETURN (1)
    END  
*/

	if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS)='validate_subscriber_info'
	begin
		if exists (select * from sysmergesubscriptions where pubid=@pubid and subid<>@pubid and status<>0)
			begin
				raiserror(21501, 16, -1)
				return (1)
			end	
	end

    /*
    ** Parameter Check:
    ** 'ftp_port' cannot be null
    */
    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'ftp_port' AND @value IS NULL
    BEGIN
        RAISERROR (14043, 16, -1, @property)
        RETURN (1)
    END

    BEGIN TRAN changemergepublication    
    save tran changemergepublication

	/*
	** Changing of the following properties would require a snapshot rerun, if snapshot is ready
	*/
	if (LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) like 'ftp%' OR LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) in ('sync_mode', 'snapshot_in_defaultfolder',
			'alt_snapshot_folder', 'pre_snapshot_script', 'post_snapshot_script','compress_snapshot'))
	and @snapshot_ready>0
	begin
		if @force_invalidate_snapshot = 0
		begin
			raiserror(20607, 16, -1)
			goto UNDO
		end
		update sysmergepublications set snapshot_ready=2 where pubid=@pubid and snapshot_ready=1
		if @@ERROR<>0	GOTO UNDO
	end

	if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS)='dynamic_filters'
	begin
		if @snapshot_ready>0
		begin
			if @force_invalidate_snapshot = 0
			begin
				raiserror(20607, 16, -1)
				goto UNDO
			end
			if @force_reinit_subscription = 0
			begin
				raiserror(20608, 16, -1)
				goto UNDO
			end
			update sysmergepublications set snapshot_ready=2 where pubid=@pubid
		end

		if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true' 
        	select @dynamic_filters_id = 1
	    else if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'false' 
       		select @dynamic_filters_id = 0
       	else
       		begin
       			raiserror(14137, 16, -1)
       			goto UNDO
       		end

		update sysmergepublications set dynamic_filters=@dynamic_filters_id where pubid=@pubid
		if @@ERROR<>0
			goto UNDO
		exec @retcode = sp_MSreinitmergepublication @publication
		if @retcode<>0 or @@ERROR<>0
			goto UNDO
	end
	
	if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS)='validate_subscriber_info'
	begin
		if @value is not NULL
		begin
			if @dynamic_filters_id = 0
				begin
					raiserror(21313, 16, -1)
					GOTO UNDO
				end
			exec ('select ' + @value)
			if @@ERROR<>0
				begin
					raiserror(21299, 16, -1, @value)
					GOTO UNDO
				end
		end

		UPDATE sysmergepublications  SET validate_subscriber_info = @value WHERE pubid = @pubid
		if @@ERROR <> 0 GOTO UNDO
	end

    if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) IN ('description')
        BEGIN
            UPDATE sysmergepublications  SET description = @value WHERE pubid = @pubid
            if @@ERROR <> 0 GOTO UNDO
        END

  
    if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'status'
        BEGIN

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

            if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('active', 'inactive')
                BEGIN
                    RAISERROR (14012, 16, -1)
                    GOTO UNDO
                END

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

            if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'active'
                select @statusid = 1
            else
                select @statusid = 0

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

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

            if @@ERROR <> 0 GOTO UNDO
        END


    if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'retention'
        BEGIN
        /*
        ** Update the publication with the new replication frequency.
        */

        select @retention = CONVERT(int, @value)
        if @@error <>0 goto UNDO

        if @retention is NULL 
        	select @retention = 0

       	if @retention < 0
       		begin
       			raiserror(20050, 16, -1, 0)
       			GOTO UNDO
       		end

        UPDATE sysmergepublications set retention = @retention WHERE pubid = @pubid
        if @@ERROR <> 0 
            GOTO UNDO

		select @schemaversion = schemaversion from sysmergeschemachange
        if (@schemaversion is NULL)
        	set @schemaversion = 1
        else
            select @schemaversion = 1 + max(schemaversion) from sysmergeschemachange
            
        set @schemaguid = newid()
        set @artid = newid()
        set @schematype = 9 /* change retention */
        select @schematext = 'exec dbo.sp_MSchange_retention '+ '''' + convert(nchar(36),@pubid) + '''' + ',' + '''' + @value + ''''          
        exec @retcode=sp_MSinsertschemachange @pubid, @artid, @schemaversion, @schemaguid, @schematype, @schematext
            if @@ERROR<>0 or @retcode<>0 goto UNDO    


        END

    if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'conflict_retention'
        BEGIN
        /*
        ** Update the publication with the new conflict_retention value
        */

        select @retention = CONVERT(int, @value)
        if @@ERROR<>0
        	GOTO UNDO

        if @value is NULL 
        	select @retention = 0

       	if @retention < 0
       		begin
       			raiserror(20050, 16, -1, 0)
       			GOTO UNDO
       		end

        UPDATE sysmergepublications
            SET conflict_retention = @retention
            WHERE pubid = @pubid

        if @@ERROR <> 0 
            GOTO UNDO

        END

    if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'max_concurrent_merge'
        BEGIN
        /*
        ** Update the publication property <max_concurrent_merge> with a new value
        */

        select @max_concurrent_merge = CONVERT(int, @value)
		if @max_concurrent_merge < 0	
		begin
			raiserror(21402, 16, -1, '@value')
			GOTO UNDO
		end

        UPDATE sysmergepublications
            SET max_concurrent_merge = @max_concurrent_merge
            WHERE pubid = @pubid

        if @@ERROR <> 0 
            GOTO UNDO

        END


    if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'sync_mode'
        BEGIN

            /*
            ** Check for a valid synchronization method.
            */
		    if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS)='portable' select @value='character'

            if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('native', 'character', 'bcp native', 'bcp character')
                begin
                raiserror (20076, 16, -1)
                GOTO UNDO
                end

            /*
            ** Determine the integer value for the sync_mode.
            */
            if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) IN ('native', 'bcp native')
                select @sync_modeid = 0
            else if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) IN ('character', 'bcp character')
                select @sync_modeid = 1

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

            UPDATE sysmergepublications
               SET sync_mode = @sync_modeid
             WHERE pubid = @pubid

            if @@ERROR <> 0 GOTO UNDO
        END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'alt_snapshot_folder'
        BEGIN
            -- If the alt_snapshot_folder is set to '' or NULL,
            -- set the compress_snapshot bit to 0 and disable internet 
            -- support
  
            IF @value IS NULL OR @value = N''
            BEGIN
                UPDATE sysmergepublications
                   SET alt_snapshot_folder = @value,
                       compress_snapshot = 0,
                       enabled_for_internet = 0
                 WHERE pubid = @pubid
            END
            ELSE
            BEGIN
                UPDATE sysmergepublications
                   SET alt_snapshot_folder = @value
                 WHERE pubid = @pubid
            END
            IF @@error <> 0
            BEGIN
               GOTO UNDO
            END
        END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'pre_snapshot_script'
        BEGIN
            UPDATE sysmergepublications
               SET pre_snapshot_script = @value
             WHERE pubid = @pubid
            IF @@error <> 0
            BEGIN
               GOTO UNDO
            END
        END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'post_snapshot_script'
        BEGIN
            UPDATE sysmergepublications
               SET post_snapshot_script = @value
             WHERE pubid = @pubid
            IF @@error <> 0
            BEGIN
               GOTO UNDO
            END
        END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'ftp_address'
        BEGIN
            IF @value IS NULL OR @value = N''    
            BEGIN
                UPDATE sysmergepublications
                   SET ftp_address = @value,
                       enabled_for_internet = 0
                 WHERE pubid = @pubid
                IF @@error <> 0
                BEGIN
                    GOTO UNDO
                END
            END
            ELSE
            BEGIN
                UPDATE sysmergepublications
                   SET ftp_address = @value
                 WHERE pubid = @pubid
                IF @@error <> 0
                BEGIN
                    GOTO UNDO
                END
            END
        END
            
    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'ftp_port'
        BEGIN
            UPDATE sysmergepublications
               SET ftp_port = CONVERT(int, @value)
             WHERE pubid = @pubid
            IF @@error <> 0
            BEGIN
               GOTO UNDO
            END
        END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'ftp_subdirectory'
        BEGIN
            UPDATE sysmergepublications
               SET ftp_subdirectory = @value
             WHERE pubid = @pubid
            IF @@error <> 0
            BEGIN
               GOTO UNDO
            END
        END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'ftp_login'
        BEGIN
            UPDATE sysmergepublications
               SET ftp_login = @value
             WHERE pubid = @pubid
            IF @@error <> 0
            BEGIN
               GOTO UNDO
            END
        END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'ftp_password'
        BEGIN
            SELECT @enc_ftp_password = NULL
            IF @value IS NOT NULL
            BEGIN
                SELECT @enc_ftp_password = @value
                EXEC @retcode = master.dbo.xp_repl_encrypt @enc_ftp_password OUTPUT
                IF @retcode <> 0
                BEGIN
                    GOTO UNDO
                END
            END

            UPDATE sysmergepublications
               SET ftp_password = @enc_ftp_password
             WHERE pubid = @pubid
            IF @@error <> 0
            BEGIN
               GOTO UNDO
            END
        END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'max_concurrent_dynamic_snapshots'
        BEGIN
            SELECT @numeric_value = CONVERT(int, @value)
            IF @@ERROR<>0 OR @numeric_value < 0 OR @numeric_value IS NULL
            BEGIN
                RAISERROR(21403, 16, -1)
                GOTO UNDO
            END

            UPDATE sysmergepublications
               SET max_concurrent_dynamic_snapshots = @numeric_value
             WHERE pubid = @pubid
            IF @@error <> 0
            BEGIN 
                GOTO UNDO
            END 
        END            

   if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'publish_to_activedirectory'
   BEGIN
        if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14137, 16, -1)
            GOTO UNDO
        END

	    /* Is AD supported? */
	    DECLARE @retval  INT
	    EXECUTE @retval = master.dbo.xp_MSADEnabled
	    if (@retval <> 0)
	    begin
			RAISERROR(21254, 16, -1, @publication)
			RETURN (1)
	    end

   		if @in_ActiveD=0 and LOWER(@value collate SQL_Latin1_General_CP1_CS_AS)='true'
   		BEGIN
			create table #guid_name_for_active_directory(ad_guidname sysname collate database_default null)
			if @@ERROR<>0
			begin
				raiserror(21363, 16, -1, @publication, N'')
				goto UNDO			
			end
			insert into #guid_name_for_active_directory exec @retcode=master.dbo.sp_ActiveDirectory_Obj 'CREATE', 'PUBLICATION', @publication, @db_name
			if @retcode <> 0 or @@ERROR<>0
	    	begin
				declare @errorstring nvarchar(512)
				select @errorstring = (select TOP 1 ad_guidname from #guid_name_for_active_directory) 
	    		drop table #guid_name_for_active_directory
	    		if @errorstring is NULL
	    			select @errorstring=N''
	    		raiserror(21363, 16, -1, @publication, @errorstring)
    			GOTO UNDO
	   		end
	    	select TOP 1 @ad_guidname = ad_guidname from #guid_name_for_active_directory
    		if @ad_guidname is not NULL
	    	begin
    			update sysmergepublications set ad_guidname=@ad_guidname where pubid=@pubid
    			if @@ERROR<>0
    			begin
    				drop table #guid_name_for_active_directory
    				raiserror(21363, 16, -1, @publication, N'')
	    		    goto UNDO
    			end
    		end
    		drop table #guid_name_for_active_directory
   		END
   		else if @in_ActiveD=1 and LOWER(@value collate SQL_Latin1_General_CP1_CS_AS)='false'
   		BEGIN
			exec @retcode=master.dbo.sp_ActiveDirectory_Obj 'DELETE', 'PUBLICATION', @publication, @db_name, @ad_guidname
			if @@ERROR<>0 or @retcode<>0
			begin
				raiserror(21369, 16, -1, @publication)	
				goto UNDO
			end
			update sysmergepublications set ad_guidname=NULL where pubid=@pubid
			if @@ERROR<>0
			begin
				raiserror(21369, 16, -1, @publication)	
				goto UNDO
			end
   		END
   END

    if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) IN ('allow_push', 'allow_pull', 'allow_anonymous', 'enabled_for_internet',
        'centralized_conflicts', 'snapshot_ready', 'snapshot_in_defaultfolder', 'compress_snapshot', 
        'allow_subscription_copy', 'allow_synctoalternate')
    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)
            GOTO UNDO
        END

        /*
        ** set value bit
        */
        if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'
            select @value_bit = 1
        else 
            select @value_bit = 0


        if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'allow_anonymous'
        BEGIN
            /* Update the allow_anonymous column */
            UPDATE sysmergepublications 
                SET allow_anonymous = @value_bit
                WHERE pubid = @pubid
            if @@error <> 0
            BEGIN
               GOTO UNDO
            END

        END

        if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'allow_push'
        BEGIN
            /* Update the allow_push column */
            UPDATE sysmergepublications 
                SET allow_push = @value_bit
                WHERE pubid = @pubid
            if @@error <> 0
            BEGIN
               GOTO UNDO
            END
        END

        if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'allow_pull'
        BEGIN

            /* Update the allow_pull column */
            UPDATE sysmergepublications 
                SET allow_pull = @value_bit
                WHERE pubid = @pubid
            if @@error <> 0
            BEGIN
               GOTO UNDO
            END
        END

        if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'centralized_conflicts'
        BEGIN
            /* Update the centralized_conflicts column */
            UPDATE sysmergepublications 
                SET centralized_conflicts = @value_bit
                WHERE pubid = @pubid
            if @@error <> 0
            BEGIN
               GOTO UNDO
            END

            if @value_bit=0 and @snapshot_ready>0
            begin            
			-- changing to decentralized conflict logging will require reinitialization but no snapshot rerun
				if @force_reinit_subscription = 0
				begin
					raiserror(20608, 16, -1)
					goto UNDO
				end
				exec @retcode = sp_MSreinitmergepublication @publication
				if @@ERROR<>0 or @retcode<>0
					goto UNDO
            end
        END

        if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'enabled_for_internet'
        BEGIN
            UPDATE sysmergepublications 
               SET enabled_for_internet = @value_bit
             WHERE pubid = @pubid

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

        if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'snapshot_ready'
        BEGIN
            /* Update the allow_anonymous column */
            UPDATE sysmergepublications 
                SET snapshot_ready = @value_bit
                WHERE pubid = @pubid
            if @@error <> 0
            BEGIN
               GOTO UNDO
            END

        END

        IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'snapshot_in_defaultfolder'
        BEGIN
            -- snapshot_in_defaultfolder = 1 is only meaningful when
            -- alt_snapshot_folder is non-null, otherwise 
            -- a copy of the snapshot files is always kept
            -- at the publisher's working directory 
    
            UPDATE sysmergepublications 
               SET snapshot_in_defaultfolder = @value_bit
             WHERE pubid = @pubid
            IF @@error <> 0
            BEGIN
                GOTO UNDO
            END                

            -- Bump up the compatibility level if we are
            -- setting snapshot_in_defaultfolder to 0
            -- and compression is enabled. i.e. only 
            -- a compressed snapshot will be generated
            -- Note that alt_snapshot_folder is implicitly
            -- specified for the publication
            IF @value_bit = 0 and @compress_snapshot = 1 
            BEGIN
                EXEC @retcode = sp_MSBumpupCompLevel @pubid, 40
                IF @@ERROR<>0 OR @retcode<>0
                    GOTO UNDO
            END

        END
        
        IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'compress_snapshot'
        BEGIN

            UPDATE sysmergepublications
               SET compress_snapshot = @value_bit
             WHERE pubid = @pubid
            IF @@error <> 0
            BEGIN
                GOTO UNDO
            END

            -- See comment for 'snapshot_in_defaultfolder'
            IF @value_bit = 1 and @snapshot_in_defaultfolder = 0
            BEGIN
                EXEC @retcode = sp_MSBumpupCompLevel @pubid, 40
                IF @@ERROR<>0 OR @retcode<>0
                    GOTO UNDO
            END
        END

        if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'allow_subscription_copy'
        BEGIN
            /* Update the allow_subscription_copy column */
            UPDATE sysmergepublications 
                SET allow_subscription_copy = @value_bit
                WHERE pubid = @pubid
            if @@error <> 0
            BEGIN
               GOTO UNDO
            END
        END
        if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'allow_synctoalternate'
        BEGIN
            /* Update the allow_synctoalternate column */
            UPDATE sysmergepublications 
                SET allow_synctoalternate = @value_bit
                WHERE pubid = @pubid
            if @@error <> 0
            BEGIN
               GOTO UNDO
            END
        END
    END

    /*
    ** Update merge publication property at distributor side if necessaray 
    */
    
    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) IN ('description','allow_push', 'allow_pull', 'allow_anonymous','retention', 'allow_subscription_copy')
	BEGIN

        IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) IN ('allow_push', 'allow_pull', 'allow_anonymous','allow_subscription_copy')
            
        /* Translate values */
        BEGIN            
            IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'
                SELECT @value = '1'
            ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'false'
                    SELECT @value = '0'
        END
        /*
        ** Get distribution server information for remote RPC call.
        */
        EXECUTE @retcode = dbo.sp_helpdistributor @rpcsrvname = @distributor OUTPUT,
           @distribdb   = @distribdb OUTPUT
        IF @@ERROR <> 0 or @retcode <> 0
            BEGIN
                GOTO UNDO
            END

        SELECT @dbname =  DB_NAME()
        
        SELECT @distproc = RTRIM(@distributor) + '.' + @distribdb + 
            '.dbo.sp_MSchange_publication'
    
        EXECUTE @retcode = @distproc
            @publisher = @@SERVERNAME,
            @publisher_db = @dbname,
            @publication = @publication,
            @property = @property,
            @value = @value

        IF @@ERROR <> 0 OR @retcode <> 0
        BEGIN
            GOTO UNDO
        END
	END

    COMMIT TRAN

	--update its registration in active directory
	if @in_ActiveD=1 and LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) IN ('description','allow_pull', 'allow_anonymous')
	begin
		create table #guid_name_for_ADupdate(ad_guidname sysname collate database_default null)
		if @@ERROR<>0
		begin
            goto FAILURE
		end
		insert into #guid_name_for_ADupdate exec @retcode = master.dbo.sp_ActiveDirectory_Obj N'UPDATE', N'PUBLICATION', @publication, @db_name, @ad_guidname
		if @@ERROR<>0 or @retcode<>0
    	begin
    		goto FAILURE 					
	   	end
    	select TOP 1 @ad_guidname = ad_guidname from #guid_name_for_ADupdate
    	if @ad_guidname is not NULL
    	begin
    		update sysmergepublications set ad_guidname=@ad_guidname where pubid=@pubid
    		if @@ERROR<>0
    		    goto FAILURE 					
    	end
	    drop table #guid_name_for_ADupdate
    end

    /*
    ** Return succeed.
    */
    
    RAISERROR (14077, 10, -1)
    RETURN (0)
FAILURE:
	drop table #guid_name_for_ADupdate
    raiserror(21371, 10, -1, @publication)
    return (1)   

UNDO: 
    if @@TRANCOUNT > 0
    begin
        ROLLBACK TRANSACTION changemergepublication
        COMMIT TRANSACTION
    end
GO
exec dbo.sp_MS_marksystemobject sp_changemergepublication 
go

grant execute on dbo.sp_changemergepublication to public
go

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

CREATE PROCEDURE sp_helpmergepublication (
    @publication    sysname = '%',    /* The publication name */
    @found          int         = NULL  OUTPUT,
    @publication_id uniqueidentifier = NULL OUTPUT,
    @reserved       nvarchar(20) = NULL
    ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    declare @retcode            int
    declare @no_row             bit
    declare @our_srvid          int
    declare @has_subscription   bit

    /*
    ** Initializations.
    */
    select @has_subscription = 0

    if @found is NULL 
    BEGIN
        select @no_row=0
    END
    else
    BEGIN
        select @no_row=1
    END
    select @found       = 0
    select @our_srvid = max(srvid) from master.dbo.sysservers where UPPER(srvname) = UPPER(@@SERVERNAME) collate database_default

    
    /*
    ** Running sp_help is OK from everywhere, whether enabled for publishing or not
    */
    
    IF not exists (select * from sysobjects where name='sysmergesubscriptions')
        RETURN (0)


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

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

    if @publication <> '%'
    begin
        -- do a pal role check
        declare @pubid uniqueidentifier
        select @pubid = pubid from dbo.sysmergepublications 
            where UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name() and name = @publication
        if @pubid is not NULL
        begin
            if {fn ISPALUSER(@pubid)} <> 1
            begin
                declare @pubidstr nvarchar(36)
                declare @role sysname
                
                exec dbo.sp_MSguidtostr @pubid, @pubidstr output
                set @role = 'MSmerge_' + @pubidstr
                if not exists (select * from dbo.sysusers where issqlrole=1 and name=@role)
                begin
                    RAISERROR (21423, 16, -1, @publication)
                    return 1
                end
            end
        end
    end
    IF LOWER(@reserved collate SQL_Latin1_General_CP1_CS_AS) = 'internal'
        GOTO SelectPubs
    
    if  NOT EXISTS (select * FROM sysmergepublications pub, sysmergesubscriptions sub
        WHERE pub.name like @publication  
            and UPPER(pub.publisher)=UPPER(@@servername) 
            and pub.publisher_db=db_name() 
            and sub.pubid = pub.pubid  
            and sub.srvid = @our_srvid 
            and sub.db_name = db_name())
        BEGIN
            select @found = 0
            RETURN (0) 
        END
    else
        BEGIN
            select @found = 1
            select @publication_id = pubid FROM sysmergepublications 
                WHERE name = @publication and UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name()
            if exists (select * from sysmergesubscriptions where pubid<>subid and pubid in 
                (select pubid from sysmergepublications where name like @publication and UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name()))
                select @has_subscription = 1
            if @no_row <> 0
                RETURN(0)
        END

SelectPubs:

    CREATE TABLE #tab1 (
        id                      int                 identity NOT NULL,
        name                    sysname             collate database_default not null,
        description             nvarchar(255)       collate database_default null,
        status                  tinyint             NOT NULL,
        retention               int                 NULL,
        sync_mode               tinyint             NULL,
        allow_push              int                 NOT NULL,
        allow_pull              int                 NOT NULL,
        allow_anonymous         int                 NOT NULL,
        centralized_conflicts   int                 NOT NULL,
        priority                float(8)            NOT NULL,
        snapshot_ready          tinyint             NOT NULL,
        publication_type        int                 NULL,
        pubid                   uniqueidentifier    NOT NULL,
        snapshot_jobid          binary(16)          NULL,
        enabled_for_internet    int                 NULL,
        dynamic_filters         int                 NULL,
        has_subscription        bit                 NULL,
        -- Portable snapshot support
        snapshot_in_defaultfolder       bit default 1       NOT NULL,
        alt_snapshot_folder     nvarchar(255)       collate database_default null,
        -- Pre/post-snapshot commands
        pre_snapshot_script     nvarchar(255)       collate database_default null,
        post_snapshot_script    nvarchar(255)       collate database_default null,
        -- Snapshot compression
        compress_snapshot       bit default 0       NOT NULL,
        -- Post 7.0 FTP support
        ftp_address             sysname             collate database_default null,
        ftp_port                int                 NOT NULL,
        ftp_subdirectory        nvarchar(255)       collate database_default null,
        ftp_login               sysname             collate database_default null,
        conflict_retention		int					NULL,
        keep_partition_changes	int					NULL,
        allow_subscription_copy int                 NULL,
        allow_synctoalternate 	int                 NULL,
        validate_subscriber_info nvarchar(500)		collate database_default null,
        backward_comp_level		int					not null default 10, --defaulted to 70 server
        publish_to_activedirectory			bit					null,
        max_concurrent_merge	int					NULL,
        max_concurrent_dynamic_snapshots int  NOT NULL default 0
        )

    /* This is valid at all sites - used for decentralized conflicts */
    IF LOWER(@reserved collate SQL_Latin1_General_CP1_CS_AS) = 'internal'
    begin
        INSERT into #tab1(name, description, status, retention, sync_mode, 
                allow_push, allow_pull, allow_anonymous, centralized_conflicts, 
                priority, snapshot_ready, publication_type, pubid, snapshot_jobid, 
                enabled_for_internet, 
				dynamic_filters, snapshot_in_defaultfolder, alt_snapshot_folder, pre_snapshot_script,
                post_snapshot_script, compress_snapshot, ftp_address, ftp_port, 
                ftp_subdirectory, ftp_login, conflict_retention, 
                keep_partition_changes, allow_subscription_copy, allow_synctoalternate, 
                validate_subscriber_info,backward_comp_level, publish_to_activedirectory, max_concurrent_merge, max_concurrent_dynamic_snapshots)
        select  pubs.name, pubs.description, pubs.status, pubs.retention, pubs.sync_mode, 
                pubs.allow_push, pubs.allow_pull, pubs.allow_anonymous, pubs.centralized_conflicts, 
                subs.priority, pubs.snapshot_ready, pubs.publication_type, pubs.pubid, replinfo.snapshot_jobid,
                pubs.enabled_for_internet, 
				pubs.dynamic_filters,
                pubs.snapshot_in_defaultfolder,
                pubs.alt_snapshot_folder,
                pubs.pre_snapshot_script,
                pubs.post_snapshot_script,
                pubs.compress_snapshot,
                pubs.ftp_address,
                pubs.ftp_port,
                pubs.ftp_subdirectory,
                pubs.ftp_login,
                pubs.conflict_retention,
                pubs.keep_before_values,
                pubs.allow_subscription_copy,
                pubs.allow_synctoalternate,
                pubs.validate_subscriber_info,
                pubs.backward_comp_level,
                case when pubs.ad_guidname is NULL then 0 else 1 end,
                pubs.max_concurrent_merge,
                pubs.max_concurrent_dynamic_snapshots
        FROM    sysmergesubscriptions   subs,
                sysmergepublications    pubs,
                MSmerge_replinfo        replinfo
                WHERE pubs.name LIKE @publication
                    AND UPPER(pubs.publisher)=UPPER(@@servername) 
                    AND pubs.publisher_db=db_name()
                    AND subs.subid = pubs.pubid
                    AND replinfo.repid = pubs.pubid
                    AND subs.subscriber_type = 1
                    and (1 = {fn ISPALUSER(pubs.pubid)} or
					        1 = is_member('replmonitor'))
        ORDER BY name
    end
    /* This is valid only at publishers and republishers */
    else
    begin
        INSERT into #tab1(name, description, status, retention, sync_mode, 
                allow_push, allow_pull, allow_anonymous, centralized_conflicts, 
                priority, snapshot_ready, publication_type, pubid, snapshot_jobid, 
                enabled_for_internet,
				dynamic_filters, has_subscription,
                snapshot_in_defaultfolder, alt_snapshot_folder, pre_snapshot_script, 
                post_snapshot_script, compress_snapshot, ftp_address,
                ftp_port, ftp_subdirectory, ftp_login, conflict_retention, 
                keep_partition_changes, allow_subscription_copy, allow_synctoalternate, 
                validate_subscriber_info, backward_comp_level, publish_to_activedirectory,max_concurrent_merge,max_concurrent_dynamic_snapshots)
        select  pubs.name, pubs.description, pubs.status, pubs.retention, pubs.sync_mode, 
                pubs.allow_push, pubs.allow_pull, pubs.allow_anonymous, pubs.centralized_conflicts, 
                subs.priority, pubs.snapshot_ready, pubs.publication_type, pubs.pubid, replinfo.snapshot_jobid,
                pubs.enabled_for_internet,
				pubs.dynamic_filters, case when exists (select * from sysmergesubscriptions where pubid<>subid and pubid in 
							                (select in_pubs.pubid from sysmergepublications in_pubs where in_pubs.name = pubs.name 
							                	and UPPER(in_pubs.publisher)=UPPER(@@servername) and in_pubs.publisher_db=db_name()))
							          then 1
							          else 0 end,
                pubs.snapshot_in_defaultfolder, pubs.alt_snapshot_folder,
                pubs.pre_snapshot_script, pubs.post_snapshot_script,
                pubs.compress_snapshot, pubs.ftp_address,
                pubs.ftp_port, pubs.ftp_subdirectory,
                pubs.ftp_login,
                pubs.conflict_retention,
                pubs.keep_before_values,
                pubs.allow_subscription_copy,
                pubs.allow_synctoalternate,
                pubs.validate_subscriber_info,
                pubs.backward_comp_level,
				case when pubs.ad_guidname is NULL then 0 else 1 end ,
				pubs.max_concurrent_merge,
                pubs.max_concurrent_dynamic_snapshots
        FROM    sysmergesubscriptions   subs,
                sysmergepublications    pubs,
                MSmerge_replinfo        replinfo
                WHERE pubs.name LIKE @publication
                     and UPPER(pubs.publisher)=UPPER(@@servername) 
                     and pubs.publisher_db=db_name()
                    AND subs.subid = pubs.pubid
                    AND replinfo.repid = pubs.pubid
                    AND subs.subscriber_type = 1
                    AND subs.srvid = @our_srvid 
                    AND subs.db_name = db_name()
        ORDER BY name
    end
    if @@ERROR <> 0 
        RETURN (1)

	update #tab1 set snapshot_ready=0 where snapshot_ready>1
	if @@ERROR<>0
		RETURN (1)
		
    select * FROM #tab1
    	where 1 = {fn ISPALUSER(pubid)} or
			  1 = is_member('replmonitor')

    RETURN (0)
go
exec dbo.sp_MS_marksystemobject sp_helpmergepublication 
go

grant execute on dbo.sp_helpmergepublication to public
go


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

CREATE PROCEDURE sp_dropmergepublication(
        @publication sysname,      /* The publication name */
        @ignore_distributor bit = 0,
        @reserved bit = 0
        ) AS

    set nocount on
    /*
    ** Declarations.
    */
	declare @ad_guidname		sysname
    declare @pubid              uniqueidentifier
    declare @article            sysname
    declare @cmd                nvarchar(255)
    declare @retcode            int
    declare @distproc           nvarchar(300)
    declare @distributor        sysname
    declare @distribdb          sysname
    declare @working_dir        varchar(255)
    declare @working_dir_drive  varchar(255)
    declare @pub_dir            nvarchar(255)
    declare @db_name            sysname
	declare @implicit_transaction	int
	declare @close_cursor_at_commit int
    declare @dynamic_filters        bit
    declare @alt_snapshot_folder nvarchar(255)
    declare @pub_alt_snapshot_folder nvarchar(255)

	select @close_cursor_at_commit = 0
	select @implicit_transaction = 0
	/*
	** Save setting values first before changing them
	*/
	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

	set @ad_guidname = NULL
    /*
    ** Initializations.
    */  
    select @db_name = db_name()

    /*
    ** Security Check
    */
    EXEC @retcode = dbo.sp_MSreplcheck_publish
    IF @@ERROR <> 0 or @retcode <> 0
        return (1)

	/* make sure current database is enabled for merge replication */
    exec @retcode=dbo.sp_MSCheckmergereplication
    if @@ERROR<>0 or @retcode<>0
    	return (1)

    if LOWER(@publication) = 'all'
        BEGIN

            declare hC1 CURSOR LOCAL FAST_FORWARD FOR select DISTINCT name 
                FROM sysmergepublications where UPPER(publisher)=UPPER(@@SERVERNAME) and publisher_db=db_name() 
                    FOR READ ONLY
            
            OPEN hC1
            FETCH hC1 INTO @publication
            WHILE (@@fetch_status <> -1)
                BEGIN
                    EXECUTE dbo.sp_dropmergepublication @publication=@publication,
                        @ignore_distributor = @ignore_distributor,
                        @reserved = 1

                    FETCH hC1 INTO @publication
                END
            CLOSE hC1
            DEALLOCATE hC1
            RETURN (0)
        END

    if @publication IS NULL
        BEGIN
            RAISERROR (14003, 16, -1)
            RETURN (1)
        END
    /*
    ** Get the @pubid.
    */
    if NOT EXISTS (select * FROM sysmergepublications 
        WHERE name = @publication  and UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name())
        BEGIN
            RAISERROR (20026, 16, -1, @publication)
            RETURN (1)
        END
    select @pubid = pubid,
           @dynamic_filters = @dynamic_filters,
           @alt_snapshot_folder = alt_snapshot_folder 
      FROM sysmergepublications 
        WHERE name = @publication  and UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name()

	select @ad_guidname=ad_guidname FROM sysmergepublications 
	        WHERE name = @publication  and UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name()	
    
    /* 
    ** Ignore external publications
    */
    if exists (select * from sysmergesubscriptions where subid=@pubid 
        and pubid=@pubid and db_name<>db_name())
    RETURN (0)

    /*
    ** Check to make sure that there are push or pull subscriptions on the publication.
    */
    if EXISTS (select * FROM sysmergesubscriptions  subs, 
                    sysmergepublications                pubs, 
                    MSmerge_replinfo                    repinfo
                WHERE   pubs.name = @publication
                    AND UPPER(pubs.publisher)=UPPER(@@servername) 
                    AND pubs.publisher_db=db_name()
                    AND subs.pubid = pubs.pubid
                    AND subs.status <> 2  -- Having a deleted subscription row is fine
                    AND repinfo.repid <> @pubid
                    AND subs.subid <> subs.partnerid)
        BEGIN
            RAISERROR (14005, 16, -1)
            RETURN (1)
        END

    -- drop the database role that contains users who have access to the publication
    -- this has to be done outside the transaction since role operations cannot be
    -- done in transaction
    declare @role sysname
    declare @pubidstr nvarchar(40)
    declare @member sysname
    
    exec dbo.sp_MSguidtostr @pubid, @pubidstr output
    set @role = 'MSmerge_' + @pubidstr

    if exists (select * from dbo.sysusers where issqlrole=1 and name=@role)
    begin
        declare role_members cursor fast_forward
        for select u.name as MemberName
    			from dbo.sysusers u, dbo.sysusers g, dbo.sysmembers m
    			where g.name = @role
    				and g.uid = m.groupuid
    				and g.issqlrole = 1
    				and u.uid = m.memberuid
    				
        open role_members 
        fetch role_members into @member
        while @@fetch_status <> -1
        begin
            exec @retcode = dbo.sp_droprolemember @role, @member
            if (@retcode <> 0 or @@error <> 0)
            BEGIN
                close role_members
                deallocate role_members
                RAISERROR (14005, 16, -1)
                RETURN (1)
            END
            fetch role_members into @member
        end
        close role_members
        deallocate role_members
        
        exec @retcode = dbo.sp_droprole @role
        if (@retcode <> 0 or @@error <> 0)
        BEGIN
            RAISERROR (14005, 16, -1)
            RETURN (1)
        END
    end
    

    begin tran
    save TRANSACTION dropmergepublication

    /*
    ** delete on-demand user script, if any
    */
	if exists (select * from sysmergeschemachange where schematype=46 and pubid=@pubid)
		begin
			exec @retcode=sp_MSremove_userscript @pubid, 1
			--we may not want to do error checking here
		end
	
    /*
    ** Delete all articles from the publication.
    */

    update sysmergepublications set snapshot_ready=0 where pubid=@pubid --so that articles can be dropped
    if @@ERROR<>0
    	goto FAILURE
    EXECUTE @retcode = dbo.sp_dropmergearticle @publication = @publication, @article = 'all',
        @ignore_distributor = @ignore_distributor

    if @@ERROR <> 0 OR @retcode <> 0
        begin
            RAISERROR (20040, 16, -1, @publication)
            goto FAILURE
        end

    /*
    ** Delete sync task of Publication.
    */
    execute @retcode = dbo.sp_MSdropmergepub_snapshot @publication = @publication,
        @ignore_distributor = @ignore_distributor

    if @@ERROR <> 0 OR @retcode <> 0
        begin
            RAISERROR (20010, 16, -1, @publication)
            goto FAILURE
        end

    /*
    ** Remove my own subscription from sysmergesubscriptions.
    */
    if exists (select * from sysmergesubscriptions where subid = @pubid)
        begin
            DELETE from sysmergesubscriptions WHERE subid = @pubid
            if @@ERROR <> 0
                goto FAILURE
        end         

    if exists (select * from MSmerge_replinfo where repid = @pubid)
        begin
            DELETE from MSmerge_replinfo WHERE repid = @pubid
            if @@ERROR <> 0
                goto FAILURE
        end             

    /*
    ** if @ignore_distributor = 1, we are in bruteforce cleanup mode, don't do RPC.
    */
    if @ignore_distributor = 0
    begin
        /*
        ** Get distribution server information for remote RPC call.
        */
        EXEC @retcode = dbo.sp_helpdistributor @rpcsrvname = @distributor OUTPUT,
                                           @distribdb = @distribdb OUTPUT,
                                           @directory = @working_dir OUTPUT
        IF @@ERROR <> 0 OR  @retcode <> 0
            BEGIN
                RAISERROR (14071, 16, -1)
                goto FAILURE
            END

        /*
        ** Drop the publication info from the distributor
        */
        select @distproc = RTRIM(@distributor) + '.' + @distribdb + 
            '.dbo.sp_MSdrop_publication'
        EXECUTE @retcode = @distproc
            @publisher = @@SERVERNAME,
            @publisher_db = @db_name,
            @publication = @publication

        if @@ERROR <> 0 OR @retcode <> 0
        begin
            goto FAILURE
        end

        /*
        ** If alternate snapshot folder is specified for this publication,
        ** try to remove the publication's snapshot folder underneath the
        ** the alternate snapshot location in Distributor's context
        */
        if @alt_snapshot_folder is not null and
           @alt_snapshot_folder <> N''
        begin

            /* Append publication specific folder name */
            if substring(@alt_snapshot_folder,len(@alt_snapshot_folder),1)<>
                N'\' 
            begin
                select @alt_snapshot_folder = @alt_snapshot_folder + N'\'
            end 

            -- UNC version
            select @pub_alt_snapshot_folder = @alt_snapshot_folder + N'unc\' + fn_replcomposepublicationsnapshotfolder(@@servername,@db_name,@publication) collate database_default
            select @distproc = fn_replquotename(RTRIM(@distributor)) collate database_default + '.'  + fn_replquotename(@distribdb) collate database_default + 
                '.dbo.sp_MSreplremoveuncdir'
            -- Ignore errors as the snapshot folder may not exist at all
            EXECUTE @distproc
                @dir = @pub_alt_snapshot_folder

            -- FTP-enabled version
            select @pub_alt_snapshot_folder = @alt_snapshot_folder + N'ftp\' + fn_replcomposepublicationsnapshotfolder(@@servername,@db_name,@publication) collate database_default
            select @distproc = fn_replquotename(RTRIM(@distributor)) collate database_default + '.'  + fn_replquotename(@distribdb) collate database_default + 
                '.dbo.sp_MSreplremoveuncdir'
            -- Ignore errors as the snapshot folder may not exist at all
            EXECUTE @distproc
                @dir = @pub_alt_snapshot_folder
        end         
    end

    /* Remove all dynamic snapshot jobs of this publication */
    exec @retcode = sp_MSdropmergedynamicsnapshotjob 
        @publication = @publication,
        @ignore_distributor = @ignore_distributor

    if @@ERROR <> 0 OR @retcode <> 0
    begin
        goto FAILURE
    end

    /*
    ** Execute the cleanup routine for the publication
    */
	exec dbo.sp_MSpublicationcleanup @publisher=@@SERVERNAME, 
			@publisher_db = @db_name,
			@publication = @publication
    
	if @@ERROR <> 0
        goto FAILURE

	DECLARE @retval  INT
	EXECUTE @retval = master.dbo.xp_MSADEnabled
    if @ad_guidname is not NULL 
    begin
    	if @retval = 0
		begin
    		exec @retcode=master.dbo.sp_ActiveDirectory_Obj 'DELETE', 'PUBLICATION', @publication, @db_name, @ad_guidname
			if @@ERROR<>0 or @retcode<>0
			begin
				raiserror(21369, 16, -1, @publication)
				goto FAILURE
			end
    	end
    	else
    	begin
			RAISERROR(21254, 16, -1, @publication)
    		GOTO FAILURE
    	end
    end

    COMMIT TRANSACTION
    /*
	** 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 (0)

FAILURE:            
    RAISERROR (14006, 16, -1)
    /* UNDONE : This code is specific to 6.X nested transaction semantics */
    if @@TRANCOUNT > 0
    begin
        ROLLBACK TRANSACTION dropmergepublication
        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 dbo.sp_MS_marksystemobject sp_dropmergepublication
go

grant execute on dbo.sp_dropmergepublication to public
go

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

CREATE PROCEDURE sp_mergearticlecolumn (
        @publication sysname,           /* The publication name */
        @article sysname,               /* The article name */
        @column sysname = NULL,         /* The column name */
        @operation nvarchar(4) = 'add',      /* Add or delete a column */
        @schema_replication nvarchar(5) = 'false',	/* reserved for internal use */
        @force_invalidate_snapshot bit = 0,	/* Force invalidate existing snapshot */
		@force_reinit_subscription bit = 0	/* Force reinit subscription */
        ) AS

	SET NOCOUNT ON
    /*
    ** Declarations.
    */
	declare @mergepublish	int
	declare @iscomputed		int
	declare @xtype			int
    declare @sync_mode		int
	declare @index_cnt		int
    declare @v_unique_constraint			int
	declare @v_unique_index			int
	declare @indid			int
    declare @in_partition 	bit
	declare @colid	int
    DECLARE @cnt int, @idx int  /* Loop counter, index */
    DECLARE @columnid smallint   /* Columnid-1 = bit to set */
    DECLARE @columns binary(128)         /* Temporary storage for the converted column */
    DECLARE @pubid uniqueidentifier                  /* Publication identification number */
    DECLARE @retcode int                /* Return code for stored procedures */
    DECLARE @artid uniqueidentifier
	declare @object_view 	sysname
    declare @filter_clause nvarchar(1000)
    DECLARE @objid int            /* Article base table id */    
	declare @tmp_artid	uniqueidentifier
	declare @tmp_object	sysname
    declare @publisher	sysname
    declare @publisher_db	sysname
	declare @pkkey		sysname
	declare @conflict_table	sysname
	declare @status_value	int
	declare @column_list	nvarchar(4000)
	declare @ins_conflict_proc sysname
	declare @qual_source_object	nvarchar(270)
	declare @qual_object_view	nvarchar(270)
	declare @qual_tmp_object	nvarchar(270)
	declare @source_object	nvarchar(300)
	declare @v_foreign_key int
	declare @quoted_source_object nvarchar(270)
	select @publisher = @@SERVERNAME
	select @publisher_db = db_name()
    /*
    ** Security Check
    */
    exec @retcode = dbo.sp_MSreplcheck_publish
    if @@ERROR <> 0 or @retcode <> 0
        return(1)

	select @mergepublish = 0x4000
	select @v_unique_index 	 = 2 		-- status in sysindexes
	select @v_foreign_key = 3			-- status in sysconstraints
	select @v_unique_constraint 	 = 4096 --status in sysindexes

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

    IF ( (SELECT category & 4 FROM master..sysdatabases WHERE name = DB_NAME() collate database_default) = 0 )
    BEGIN
        RAISERROR (14013, 16, -1)
        RETURN (1)
    END

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

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

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

    SELECT @pubid = pubid, @sync_mode = sync_mode FROM sysmergepublications WHERE name = @publication 
    													and LOWER(publisher)=LOWER(@publisher)
												    	and publisher_db = @publisher_db
    																
    IF @pubid IS NULL
        BEGIN
            RAISERROR (20026, 11, -1, @publication)
            RETURN (1)
        END

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

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

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

    /*
    ** Make sure the article exists.
    */
    SELECT @artid = artid FROM sysmergearticles
       WHERE pubid = @pubid AND name = @article
    IF @artid IS NULL
        BEGIN
            RAISERROR (20027, 16, -1, @article)
            RETURN (1)
        END
    
	/*
    ** Parameter Check:  @column.
    ** Check to make sure that the column exists and conforms to the rules
    ** for identifiers.
    */

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

    /*
    ** Parameter Check:  @operation.
    ** The operation can be either 'add' or 'drop'.
    */
    IF LOWER(@operation collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('add', 'drop')
        BEGIN
            RAISERROR (14019, 16, -1)
            RETURN (1)
        END
        
	/*
	** column name can not be null for 'drop' operation. OK for 'add' operation
	*/
	IF LOWER(@operation collate SQL_Latin1_General_CP1_CS_AS)='drop' and @column is NULL
		BEGIN
			RAISERROR(14043, 16, -1, '@column')
			RETURN (1)
		END	
	/*
	** Can not drop non-identity, non-timestamp, non-computed columns that are not nullable and have no default value
	*/
    SELECT @status_value=status, @objid = objid, 
    	@source_object = object_name(objid) FROM sysmergearticles WHERE artid = @artid
	select @colid=colid from syscolumns where id=@objid and name=@column
	if not exists (select * from syscolumns where id = @objid and name=@column and (isnullable=1 
					OR type_name(xtype)='timestamp' OR iscomputed=1))
		and not exists (select * from sysconstraints where id=@objid and colid=@colid and status & 5 = 5)
			and LOWER(@operation collate SQL_Latin1_General_CP1_CS_AS)='drop' and LOWER(@schema_replication collate SQL_Latin1_General_CP1_CS_AS)='false'
			and ColumnProperty(@objid, @column, 'IsIdentity') <> 1
		BEGIN
			RAISERROR(21165, 16, -1, @column)
			return (1)
		END
		
	
	if LOWER(@operation collate SQL_Latin1_General_CP1_CS_AS) = 'drop'
	begin		
		select @indid = indid from sysindexes where id = @objid and (status & 2048) <> 0    /* PK index */
		select @index_cnt = 1
		while (@index_cnt <= 16)
			begin
				select @pkkey = INDEX_COL(@source_object, @indid, @index_cnt)
				if @pkkey is NULL
					break
				if @pkkey=@column
					begin
						raiserror(21250, 16, -1, @column)
						return (1)
					end
				select @index_cnt = @index_cnt + 1
			end
	
		/*
		** Check for unique index defined on this column - to disallow such a column from being dropped
		*/	
		if exists (select * from sysindexes where id=@objid 
					and (status & @v_unique_index = @v_unique_index 
						or status & @v_unique_constraint = @v_unique_constraint))
		begin
			declare @keys varbinary(816)
			declare @i 		int
			declare #check_unique CURSOR LOCAL FAST_FORWARD for 
			select indid from sysindexes where id=@objid 
				and (status & @v_unique_index = @v_unique_index 
					or status & @v_unique_constraint = @v_unique_constraint)
			open #check_unique
			fetch #check_unique into @indid
			while (@@fetch_status<>-1)
			begin
				SELECT @i = 1
				WHILE (@i <= 16)
				BEGIN
        		        SELECT @pkkey = INDEX_COL(@source_object, @indid, @i)
            		    if @pkkey is NULL
                		    break
						if @pkkey=@column
    		            BEGIN
    		            	if LOWER(@schema_replication collate SQL_Latin1_General_CP1_CS_AS)='true'
								raiserror(21265, 16, -1, @column, @source_object)
							else
								raiserror(21347, 16, -1, @column)							
							close #check_unique
							deallocate #check_unique
							return (1)
						END
	        	        select @i = @i + 1
				END	
				fetch #check_unique into @indid
			end
			close #check_unique
			deallocate #check_unique
		end

		/*
		** Check for foreign key constraints
		*/
		if exists (select * from sysconstraints where status & @v_foreign_key=@v_foreign_key and id=@objid)
		begin
			if @column in (select name from syscolumns where id=@objid and colid in
				(select colid from sysconstraints where status & @v_foreign_key=@v_foreign_key and id=@objid))
			begin
				raiserror(21513, 16, -1, @column)
				return (1)
			end
		end
	end

    begin tran
    save TRANSACTION articlecolumn

    /*
    ** Make sure that the column <columns> is not NULL - if NULL set to 0x00.
    */
    SELECT @columns = columns FROM sysmergearticles WHERE artid = @artid and pubid=@pubid
    IF @columns IS NULL
        UPDATE sysmergearticles SET columns = 0x00 WHERE artid = @artid and pubid=@pubid
	
    /*
    ** If no columns are specified, or if NULL is specified, set all
    ** the bits in the 'columns' column so all columns will be included, 
    */
    IF @column IS NULL
    BEGIN
    	SELECT @cnt = max(colid), @idx = 1 FROM syscolumns WHERE id = @objid 
    	SELECT @columns = NULL
		WHILE @idx <= @cnt
		BEGIN
			/* to make sure column holes will not be included */
			if exists (select * from syscolumns where colid=@idx and id=@objid and 
				(@sync_mode=0 OR (iscomputed<>1 and type_name(xtype) <>'timestamp')))
				begin
					exec sp_MSsetbit @bm=@columns OUTPUT, @coltoadd=@idx, @toset = 1
					if @@ERROR<>0 or @retcode<>0
						goto FAILURE
					update syscolumns set colstat=colstat | @mergepublish where id=@objid and colid=@idx
					if @@ERROR<>0
						goto FAILURE

				end
			SELECT @idx = @idx + 1
		END
		UPDATE sysmergearticles SET columns = @columns WHERE name = @article AND pubid = @pubid
		if @sync_mode=1 and exists (select * from syscolumns where id=@objid and (iscomputed=1 or type_name(xtype)='timestamp'))
			UPDATE sysmergearticles SET vertical_partition = 1 WHERE name = @article AND pubid = @pubid
	END
    ELSE
	BEGIN
		/* if @column is NULL, meanning all columns are in, do not bump up version to Shiloh. */
		if LOWER(@schema_replication collate SQL_Latin1_General_CP1_CS_AS)='false'
			raiserror(21351, 10, -1, @publication)
		else
			raiserror(21352, 10, -1, @publication)
		exec @retcode = sp_MSBumpupCompLevel @pubid, 40
		if @@ERROR<>0 or @retcode<>0
			GOTO FAILURE

		SELECT @columnid = colid, @iscomputed=iscomputed, @xtype=xtype  
			FROM syscolumns WHERE id = @objid AND name = @column
		IF ((@@error <> 0) OR (@columnid IS NULL))
		BEGIN
			RAISERROR (21166, 16, -1, @column)
			GOTO FAILURE
		END

		/*
		** for character mode publications, we do not allow adding computed column or timestamp columns
		** into the vertical parititioning. 
		*/
		if @sync_mode=1 and (@iscomputed = 1 or type_name(@xtype) ='timestamp') and LOWER(@operation collate SQL_Latin1_General_CP1_CS_AS) = 'add'
		begin
			if LOWER(@schema_replication collate SQL_Latin1_General_CP1_CS_AS)='false'
				begin
					raiserror(21269, 16, -1)
					GOTO FAILURE
				end
			else
			begin	
		    	if @@TRANCOUNT >0 
    			begin
        			ROLLBACK TRANSACTION articlecolumn
        			COMMIT TRAN
        		end
        		return (0)
        	end
		end
		
		if ColumnProperty(@objid, @column, 'isrowguidcol') = 1 and LOWER(@operation collate SQL_Latin1_General_CP1_CS_AS) = 'drop'
		begin
			RAISERROR(21162, 16, -1)
			GOTO FAILURE
		end

		exec @in_partition = sp_MStestbit @bm=@columns, @coltotest=@columnid

		if @in_partition=1 and LOWER(@operation collate SQL_Latin1_General_CP1_CS_AS) = 'add' and LOWER(@schema_replication collate SQL_Latin1_General_CP1_CS_AS)='false'
		begin
			RAISERROR(21335, 10, -1, @column)
			GOTO FAILURE
		end
		
		if @in_partition=0 and LOWER(@operation collate SQL_Latin1_General_CP1_CS_AS) = 'drop' and LOWER(@schema_replication collate SQL_Latin1_General_CP1_CS_AS)='false'
		begin
			RAISERROR(21336, 10, -1, @column)
			GOTO FAILURE
		end

		SELECT @columns = columns, @filter_clause=subset_filterclause, @ins_conflict_proc=ins_conflict_proc, @conflict_table=conflict_table FROM sysmergearticles WHERE name = @article AND pubid = @pubid
		IF LOWER(@operation collate SQL_Latin1_General_CP1_CS_AS) = 'add'
			begin
				exec @retcode = sp_MSsetbit @bm = @columns OUTPUT, @coltoadd=@columnid, @toset=1
				if @@ERROR<>0 or @retcode<>0 
					GOTO FAILURE
				update syscolumns set colstat=colstat | @mergepublish where id=@objid and colid=@colid
				if @@ERROR<>0
					goto FAILURE
			end
		ELSE
			begin
				
				exec @retcode = sp_MSsetbit @bm = @columns OUTPUT, @coltoadd=@columnid, @toset=0
				if @@ERROR<>0 or @retcode<>0 
					GOTO FAILURE
				if @columns = 0x00
					begin
						raiserror(21345, 16, -1)
						goto FAILURE
					end
				exec @retcode = sp_MSclearcolumnbit @pubid, @artid, @column
				if @@ERROR<>0 or @retcode<>0
					goto FAILURE
			end

		/*
		** Set vertical_partitioning flag so that publication view would be re-generated even
		** if there is not subsetfilters nor join filters
		*/
		UPDATE sysmergearticles  SET columns = @columns, vertical_partition=1 
			WHERE name = @article AND pubid = @pubid
		IF @@ERROR <> 0
		BEGIN
			RAISERROR (14021, 16, -1)
			GOTO FAILURE
		END

		select @column_list = NULL
		
		/*
		** check to see if that column can be dropped based on current article's filter clause
		** and if the article is involved in any join_filter_clauses - to make sure the drop of
		** one column does not cripple any such joins
		*/ 
		if ((@filter_clause is not NULL and @filter_clause <>'' ) or 
			exists (select * from sysmergesubsetfilters where pubid=@pubid and 
			(artid=@artid or join_articlename=@article))) and LOWER(@operation collate SQL_Latin1_General_CP1_CS_AS) = 'drop'
		begin
			exec @retcode = sp_MSgetcolumnlist @pubid, @column_list OUTPUT, @objid
			if @@ERROR<>0 or @retcode<>0
				GOTO FAILURE
			select @object_view='TEMP_VIEW_' + @source_object  --@source_object is not quoted
            select @qual_object_view=quotename(@object_view)

            exec @retcode = sp_MSget_qualified_name @objid, @qual_source_object OUTPUT
            if @@ERROR<>0 or @retcode<>0
            	goto FAILURE
            select @quoted_source_object=QUOTENAME(@source_object)
            
			exec ('create view ' + @qual_object_view + ' as select ' + @column_list + ' from ' + @qual_source_object)
			if @@ERROR<>0
				GOTO FAILURE
			if @filter_clause is not NULL and @filter_clause <>'' 
			begin
				exec ('declare @test int select @test=1 from ' + @qual_object_view + ' ' + @quoted_source_object + ' where ' + @filter_clause)
				if @@ERROR<>0
					begin
						exec('drop view ' + @qual_object_view)
						raiserror(21256, 16, -1, @filter_clause, @source_object)
						GOTO FAILURE
					end
			end			
		end
		
		/*
		** Check to make sure dropping a column will not breaking any other articles that using current article as join_article
		*/
		if exists (select * from sysmergesubsetfilters where pubid=@pubid and 
			(artid=@artid or join_articlename=@article)) and LOWER(@operation collate SQL_Latin1_General_CP1_CS_AS) = 'drop'
		begin
			declare @join_articlename sysname
			declare per_article CURSOR LOCAL FAST_FORWARD FOR 
				select artid, join_filterclause, join_articlename from sysmergesubsetfilters 
					where pubid=@pubid and (join_articlename=@article or artid=@artid)
			for READ ONLY
			open per_article
			fetch per_article into @tmp_artid, @filter_clause, @join_articlename
			while (@@fetch_status<>-1)
			begin
				if @artid<>@tmp_artid
					select @tmp_object=object_name(objid) from sysmergearticles where pubid=@pubid and artid=@tmp_artid
				else
					select @tmp_object=object_name(objid) from sysmergearticles where pubid=@pubid and name=@join_articlename
				select @qual_tmp_object=QUOTENAME(@tmp_object)
				if @tmp_object is not NULl and @tmp_object<>''
					begin
						exec ('declare @test int select @test=1 from ' + @qual_object_view + ' ' + @quoted_source_object + ', ' + @qual_tmp_object + ' where ' + @filter_clause)					
						if @@ERROR<>0
						begin
							close per_article
							deallocate per_article
							raiserror(21256, 16, -1, @filter_clause, @source_object)
							GOTO FAILURE
						end
					end
				fetch per_article into @tmp_artid, @filter_clause, @join_articlename	
			end
			close per_article
			deallocate per_article		
		end			
	END   

	/*
    ** if snapshot is ready, change it to obsolete to force another snapshot run.	
    ** Note this is the third value of snapshot_ready. 0 for not ready, 1 for OK, 2 for obsolete
    */   
    IF EXISTS (SELECT * FROM sysmergepublications WHERE pubid=@pubid and snapshot_ready>0) 
    		and LOWER(@schema_replication collate SQL_Latin1_General_CP1_CS_AS)='false'
        BEGIN
	        update sysmergearticles set status=1, conflict_table=NULL where pubid=@pubid and artid=@artid and status<>5 and status <>6
	        if @@ERROR<>0
	        	goto FAILURE
		/*
		** Force a re-generation of conflict table and its ins_proc
		*/
	        if object_id(@ins_conflict_proc) is not NULL
	        	begin
	        		set @quoted_source_object= quotename(@ins_conflict_proc)
	        		exec ('drop proc ' + @quoted_source_object)
	        		if @@ERROR<>0
	        			goto FAILURE
	        	end
	        if object_id(@conflict_table) is not NULL
	        	begin
	        		set @quoted_source_object= quotename(@conflict_table)
	        		exec ('drop table ' + @quoted_source_object)
	        		if @@ERROR<>0
	        			goto FAILURE
	        	end
	        /*
			** make sure we know we really want to do this.
			*/
			if @force_invalidate_snapshot = 0
				begin
					raiserror(20607, 16, -1)
					goto FAILURE
				end
			update sysmergepublications set snapshot_ready=2 where pubid=@pubid
			if @@ERROR<>0
				goto FAILURE

			if @force_reinit_subscription = 0 and @status_value<>5 --5 is the value for new_inactive
				begin
					raiserror(20608, 16, -1)
					goto FAILURE
				end

			--do a global re
			
			if @force_reinit_subscription = 1
				begin
					--global reinitialization will bump up backward-comp-level to SP2.
				exec @retcode = sp_MSreinitmergepublication @publication
				if @retcode<>0 or @@ERROR<>0
					goto FAILURE
				end
			/*
			** Even for vertical partitioning on new article - we do not need to bumpup backward-comp-level
			**
			else
				begin	--bump up the backward-comp-level so that only 80 subscribers can use it.
				exec @retcode = sp_MSBumpupCompLevel @pubid, 40
				if @@ERROR<>0 or @retcode<>0
					GOTO FAILURE
				end
			*/
        END

    COMMIT TRANSACTION
    if exists (select * from sysobjects where id = object_id(@qual_object_view))
        begin
            exec ('drop view ' + @qual_object_view)
        end
    return (0)
FAILURE:

    if @@TRANCOUNT >0 
    	begin
        	ROLLBACK TRANSACTION articlecolumn
        	COMMIT TRAN
        end

    if exists (select * from sysobjects where id = object_id(@qual_object_view))
        begin
            exec ('drop view ' + @qual_object_view)
        end

    return (1)
go
 
EXEC dbo.sp_MS_marksystemobject sp_mergearticlecolumn
GO
grant execute on dbo.sp_mergearticlecolumn to public
go

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

CREATE PROCEDURE sp_helpmergearticlecolumn (
    @publication sysname,            /* The publication name */
    @article    sysname              /* The article name */
    ) AS
    SET NOCOUNT ON

    /*
    ** Declarations.
    */
	declare @colid		int
	declare @colmax		int
	declare @colname	sysname
	declare @published	bit
    declare @columns 	binary(128)
    declare @pubid 		uniqueidentifier
    declare @retcode 	int
    declare @objid		int
	declare @publisher	sysname
    declare @publisher_db	sysname

	select @publisher = @@SERVERNAME
	select @publisher_db = db_name()

    /*
    ** Parameter Check: @publication.
    ** The @publication name must conform to the rules for identifiers.
    */
    IF @publication IS NULL
    BEGIN
        RAISERROR (14043, 16, -1, '@publication')
        RETURN (1)
    END

    EXECUTE @retcode = dbo.sp_validname @publication
    IF @retcode <> 0 or @@ERROR<>0
    	RETURN (1)
    
    SELECT @pubid = pubid FROM sysmergepublications WHERE name = @publication 
    													  and LOWER(publisher)=LOWER(@publisher)
    													  and publisher_db = @publisher_db
    IF @pubid IS NULL
    BEGIN
    	RAISERROR (20026, 11, -1, @publication)
        RETURN (1)
    END

	-- Security check. (Done after @pubid retrieval, because this param is needed here.)
	if  1 <> {fn ISPALUSER(@pubid)} and
		(1 <> is_member('replmonitor') or is_member('replmonitor') is null) 
	begin
		raiserror(15247,-1,-1)
		return 1
	end

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

    IF @article IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@article')
            RETURN (1)
        END
    EXECUTE @retcode = dbo.sp_validname @article
    IF @retcode <> 0 or @@ERROR<>0
	    RETURN (1)

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

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

    SELECT @columns = columns, @objid=objid FROM sysmergearticles
		WHERE name = @article AND pubid = @pubid

	create table #tmp (column_id int, column_name sysname collate database_default, published bit)
	select TOP 1 @colid = colid from syscolumns where id = @objid order by colid ASC
	select TOP 1 @colmax = colid from syscolumns where id = @objid order by colid DESC
	while (@colid <= @colmax)
	begin
	if exists (select * from syscolumns where id = @objid and colid = @colid)
		begin
			select @colname = name from syscolumns where id=@objid and colid=@colid
			exec @retcode = sp_MStestbit @bm=@columns, @coltotest=@colid
			if @retcode<>0
				select @published=1
			else
				select @published=0
			insert into #tmp values(@colid, @colname, @published)
		end
		select @colid=@colid + 1
	end
	select * from #tmp
	drop table #tmp
    go
 
EXEC dbo.sp_MS_marksystemobject sp_helpmergearticlecolumn
GO
grant execute on dbo.sp_helpmergearticlecolumn to public
go
        
raiserror('Creating procedure sp_MSreinitmergepublication', 0, 1)
GO

create procedure sp_MSreinitmergepublication
    @publication    sysname,
    @upload_first	bit = 0
AS
    declare @pubid				uniqueidentifier
	declare @schematext 		nvarchar(4000)
	declare @schemaversion 		int
	declare @schemaguid			uniqueidentifier
	declare @schematype			int
	declare @publisher			sysname
	declare @retcode			int
	declare @publisher_db		sysname

	set nocount on
        
	select @publisher=@@servername
	select @publisher_db = db_name()
	
    select @pubid=pubid from sysmergepublications 
    	where name=@publication and UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name()

	raiserror(21353,10, -1,@publication)
	exec @retcode=sp_MSBumpupCompLevel @pubid, 30 --for 7.0 SP2, which has limited support for this
	if @@ERROR<>0 or @retcode<>0		
		return (1)
	select @schematext = 'exec sp_MSreinit_hub '+ QUOTENAME(@publisher) + ', ' + QUOTENAME(@publisher_db) + ', ' + QUOTENAME(@publication) + ', ' + convert(nvarchar, @upload_first)
	select @schemaversion = schemaversion from sysmergeschemachange
	if (@schemaversion is NULL)
		set @schemaversion = 1
	else
		select @schemaversion = 1 + max(schemaversion) from sysmergeschemachange        
	set @schemaguid = newid()
	if @upload_first = 0
		set @schematype = 12 /* reinit_all */
	else
		set @schematype = 14 /* reinit_with_upload*/
	exec @retcode=sp_MSinsertschemachange @pubid, @schemaguid, @schemaversion, @schemaguid, @schematype, @schematext
	if @@ERROR<>0 or @retcode<>0 
		return (1)
		
	update MSmerge_replinfo set schemaversion=0, recgen = NULL, recguid=NULL, sentgen=NULL, sentguid = NULL
		where repid in (select subid from sysmergesubscriptions where subid<>pubid and pubid=@pubid and subscription_type=0)
	if @@ERROR<>0 
		return (1)

	-- set all article status to inactive as a starting point - which sort or cleanup the newly added status
	-- 5 and 6.

	if @upload_first=0 --do not reset status is upload_first is true. Otherwise, 
					   --creating triggers would fail.	
	begin
		update sysmergearticles set status=1 where pubid=@pubid 
		if @@ERROR<>0
			return (1)
	end
	update MSmerge_replinfo set schemaversion=-1, recgen = NULL, recguid=NULL, sentgen=NULL, sentguid = NULL
		where repid in (select subid from sysmergesubscriptions where subid<>pubid and pubid=@pubid and subscription_type>0)
		
	if @@ERROR<>0 
		return (1)

GO
exec dbo.sp_MS_marksystemobject sp_MSreinitmergepublication
go  


raiserror('Creating procedure sp_MSreinit_hub', 0, 1)
GO
create procedure sp_MSreinit_hub
	@publisher		sysname,
	@publisher_db	sysname,
    @publication    sysname,
    @upload_first	bit
AS
	declare @retcode			int
    declare @pubid				uniqueidentifier
    declare @hub_pubname		sysname
    declare @hub_publisher		sysname
    declare @hub_publisher_db	sysname
    declare @hub_pubid			uniqueidentifier
	declare @schematext 		nvarchar(4000)
	declare @schemaversion 		int
	declare @schemaguid			uniqueidentifier
	declare @schematype			int

	/* 
    ** Security Check.
    ** We use login_name stored in syssubscriptions to manage security 
    */
	exec @retcode= dbo.sp_MSreplcheck_subscribe
    if @@ERROR <> 0 or @retcode <> 0
        return(1)
	
    select @pubid=pubid from sysmergepublications 
    	where name=@publication and UPPER(publisher)=UPPER(@publisher) and publisher_db=@publisher_db
	BEGIN TRAN
	SAVE TRAN reinithub
	
	/* Find all publications that are being republished at the subscriber */
	declare reinit_hub CURSOR LOCAL FAST_FORWARD FOR select DISTINCT p.pubid, p.name, p.publisher, p.publisher_db FROM sysmergepublications p
		where UPPER(p.publisher)=UPPER(@@SERVERNAME) and p.publisher_db=db_name()
			and exists (select * from sysmergearticles where (objid in 
                	(select objid from sysmergearticles where pubid=@pubid)) and (objid in 
                	(select objid from sysmergearticles where pubid=p.pubid))) and p.pubid<>@pubid
	FOR READ ONLY
	open reinit_hub
	fetch reinit_hub into @hub_pubid, @hub_pubname, @hub_publisher, @hub_publisher_db
	while (@@fetch_status<>-1)
	begin
		if @upload_first=1
		begin
			update sysmergesubscriptions set status=5 where pubid=@hub_pubid
			if @@ERROR<>0
				goto FAILURE
		end
		select @schematext = 'exec sp_MSreinit_hub '+ QUOTENAME(@hub_publisher) + ', ' + QUOTENAME(@hub_publisher_db) + ', ' + QUOTENAME(@hub_pubname) + ', ' + convert(nvarchar, @upload_first)
		select @schemaversion = schemaversion from sysmergeschemachange
		if (@schemaversion is NULL)
			set @schemaversion = 1
		else
			select @schemaversion = 1 + max(schemaversion) from sysmergeschemachange        
		set @schemaguid = newid()
		if @upload_first=0
			set @schematype = 12 /* reinit_all */
		else
			set @schematype = 14 /*reinitwithupload */
		exec @retcode=sp_MSinsertschemachange @hub_pubid, @schemaguid, @schemaversion, @schemaguid, @schematype, @schematext
		if @@ERROR<>0 or @retcode<>0
		begin
			select @retcode = 1
			GOTO FAILURE
		end
		raiserror(21354, 10, -1, @hub_pubname)
		exec @retcode=sp_MSBumpupCompLevel @hub_pubid, 40 
		if @@ERROR<>0 or @retcode<>0		
			GOTO FAILURE

		update sysmergepublications set snapshot_ready=2 where pubid=@hub_pubid
		if @@ERROR<>0
			goto FAILURE
		fetch next from reinit_hub into @hub_pubid, @hub_pubname, @hub_publisher, @hub_publisher_db
	end
	close reinit_hub
	deallocate reinit_hub
	COMMIT TRAN
	return (0)
FAILURE:
	close reinit_hub
	deallocate reinit_hub
	raiserror('Error occurred when applying reinit-all command at subscribers', 16, -1)
	if @@TRANCOUNT >0 
    begin
		ROLLBACK TRANSACTION reinithub
        COMMIT TRAN
	end
	return (0)
GO

exec dbo.sp_MS_marksystemobject sp_MSreinit_hub
go  

grant execute on dbo.sp_MSreinit_hub to public
go

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

create procedure sp_reinitmergesubscription
    @publication    sysname  = 'all',
    @subscriber     sysname  = 'all',
    @subscriber_db  sysname  = 'all',
    @upload_first	nvarchar(5) = 'FALSE'
AS
    declare @pubid                  uniqueidentifier
    declare @subid                  uniqueidentifier
    declare @subscription_type		int
    declare @reinit_bit				int
    declare @publisher				sysname
    declare @publisher_db			sysname
    declare @distribdb				sysname
	declare @distributor			sysname
	declare @distproc				nvarchar(300)
	declare @retcode				int
	declare @uploadbit				bit
	declare	@pubname				sysname
	declare @subsrvid				int
	declare @subdb					sysname 
	declare @subname				sysname
    /* 
    ** Security Check.
    ** We use login_name stored in syssubscriptions to manage security 
    */
	exec @retcode = dbo.sp_MSreplcheck_publish
    if @@ERROR <> 0 or @retcode <> 0
        return(1)

	set nocount on
    /*
    ** Replace 'all' with '%'
    */

	/* make sure current database is enabled for merge replication */
    exec @retcode=dbo.sp_MSCheckmergereplication
    if @@ERROR<>0 or @retcode<>0
    	return (1)

    if LOWER(@publication) = 'all'
        SELECT @publication = '%'

    if LOWER(@subscriber) = 'all'
        SELECT @subscriber = '%'

    if LOWER(@subscriber_db) = 'all'
        SELECT @subscriber_db = '%'

	select @publisher = @@SERVERNAME
	select @publisher_db=db_name()
	
    /*
    ** At publisher side, publication name is unique
    */
    IF NOT EXISTS (SELECT * FROM sysmergepublications 
        WHERE name LIKE @publication)
        BEGIN
        IF @publication = '%'
                RAISERROR (14008, 11, -1)
        ELSE
                RAISERROR (20026, 11, -1, @publication)
        RETURN (1)
        END

	/* if snapshot has not been ran yet, there is no point doing reinitialization */
	if not exists (select * from sysmergepublications where name like @publication and snapshot_ready>0)
		return (0)
        
	if LOWER(@upload_first collate SQL_Latin1_General_CP1_CS_AS) = 'true'
    	select @uploadbit  = 1
    else
    	select @uploadbit  = 0

    EXECUTE @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_MSrefresh_anonymous '

	if @subscriber = '%' and @subscriber_db = '%'
	begin
		exec @retcode = @distproc @publication, @publisher, @publisher_db 
		if @@ERROR<>0 or @retcode<>0
				return (1)
				
		declare reinit_all CURSOR LOCAL FAST_FORWARD FOR 
			select name from sysmergepublications where LOWER(publisher)=LOWER(@@SERVERNAME) and 
				publisher_db=db_name() and snapshot_ready>0 and name like @publication
		For READ only
		open reinit_all
		fetch reinit_all into @pubname
		while (@@fetch_status<>-1)
		begin
			exec @retcode = sp_MSreinitmergepublication @pubname, @uploadbit
			if @@ERROR<>0 or @retcode<>0
			begin
				close reinit_all
				deallocate reinit_all
				return (1)
			end
			
			fetch next from reinit_all into @pubname
		end
		close reinit_all
		deallocate reinit_all
	end

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

	BEGIN TRAN
	SAVE TRAN reinitsubscription
	
    Declare SYN_CUR CURSOR LOCAL FAST_FORWARD FOR 
        select subs.subid, subs.subscription_type, pubs.publisher, pubs.publisher_db, pubs.name, 
        		subs.srvid, subs.db_name
            from sysmergepublications pubs, sysmergesubscriptions subs
                where pubs.name LIKE  @publication 
                     and UPPER(pubs.publisher)=UPPER(@@servername) 
                     and pubs.publisher_db=db_name()
                    AND pubs.pubid=subs.pubid
                    AND subs.pubid<>subs.subid
                    AND subs.status <>0 --for unsynced subscription, there is no need to reinit.
                    AND ((@subscriber_db = N'%') or (db_name = @subscriber_db))
                    AND srvid in (select srvid from master..sysservers where ((@subscriber = N'%') or (UPPER(srvname) = UPPER(@subscriber) collate database_default)))
    FOR READ ONLY
    open SYN_CUR
    fetch SYN_CUR into @subid, @subscription_type, @publisher, @publisher_db, @pubname, @subsrvid, @subdb
    while (@@fetch_status<>-1)
    BEGIN  
	-- Security check 
        if not exists (select * from sysmergesubscriptions subs1 where
		(suser_sname(suser_sid()) = subs1.login_name OR 
		is_member('db_owner')=1 OR 
		is_srvrolemember('sysadmin') = 1) and
		subid = @subid)
		continue		

		select @subname = srvname from master..sysservers where srvid = @subsrvid
	
			if @subscription_type = 0 
				 update MSmerge_replinfo set schemaversion=0, recgen = NULL, recguid=NULL, sentgen=NULL, sentguid = NULL
					  where repid=@subid and schemaversion is NOT NULL
			else
       			 update MSmerge_replinfo set schemaversion= -1, recgen = NULL, recguid=NULL, sentgen=NULL, sentguid = NULL
        	 		  where repid=@subid and schemaversion is NOT NULL
		
		if @@rowcount <> 0 and @uploadbit = 1
		begin
			update sysmergesubscriptions set status = 5 where subid=@subid
			if @@ERROR<>0
				goto Failure
		end

		-- If subscriber was preventing us from cleaning up metadata, set the status
		update sysmergesubscriptions set status = 8 where subid = @subid and status = 7
		if @@rowcount <> 0
			exec sp_MSquiescecheck
			
	    -- 0 for push and -1 for pull
		exec @distproc @publisher, @publisher_db, @pubname, @subname, @subdb, 1
	    if @@ERROR<>0
		BEGIN
			goto Failure
		END        	
		fetch next from SYN_CUR into @subid, @subscription_type, @publisher, 
        	@publisher_db, @pubname, @subsrvid, @subdb        
    END
    close SYN_CUR
    deallocate SYN_CUR
    commit TRAN
    return (0)
Failure:
    close SYN_CUR
    deallocate SYN_CUR
    if @@TRANCOUNT > 0
    begin
        ROLLBACK TRANSACTION reinitsubscription
        COMMIT TRANSACTION
    end
    return (1)

GO

exec dbo.sp_MS_marksystemobject sp_reinitmergesubscription
go  

grant execute on dbo.sp_reinitmergesubscription to public
go

raiserror('Creating procedure sp_MSpublicationview', 0,1)
GO
CREATE PROCEDURE sp_MSpublicationview(
    @publication sysname,
    @force_flag int = 0,
    @max_network_optimization bit = 0
    ) AS

    declare     @pubid              uniqueidentifier
    declare     @artid              uniqueidentifier
    declare     @join_articlename       nvarchar(270)
    declare     @join_viewname      nvarchar(270)
    declare     @join_before_view  	nvarchar(270)
    declare		@before_name		nvarchar(270)
    declare		@before_viewname	nvarchar(270)
    declare 	@unqual_sourcename	nvarchar(270)
    declare     @article            nvarchar(270)
    declare     @art_nick           int
    declare     @join_nick          int
    declare     @join_filterclause  nvarchar(4000)
    declare     @bool_filterclause  nvarchar(4000)
    declare     @view_rule          nvarchar(4000)
    declare		@before_view_rule	nvarchar(4000)
    declare		@before_objid		int
    declare     @article_level      int
    declare     @progress           int
    declare     @art                int
    declare     @viewname           nvarchar(270)
    declare     @procname           nvarchar(300)
    declare     @source_objid       int
    declare     @source_object      nvarchar(270)
    declare     @sync_objid         int
    declare 	@bitset				int
    declare     @permanent          int
    declare     @temporary          int
    declare     @filter_id          int
    declare     @filter_id_str      nvarchar(10)
	declare 	@guidstr nvarchar(40)
	declare 	@pubidstr nvarchar(40)
    declare     @rgcol              nvarchar(270)
    declare     @view_type          int
    declare     @belongsname        nvarchar(270)
    declare     @join_nickstr       nvarchar(10)
    declare     @unqual_jointable   nvarchar(270)  
    declare     @hasguid            int
    declare 	@vertical_partition int
    declare     @join_unique_key    int
    declare     @simple_join_view   int
    declare     @join_filterid      int
    declare     @allhaveguids       int
    declare     @command            nvarchar(4000)
    declare     @objid              int
    declare     @owner              nvarchar(270)
    declare		@table				nvarchar(270)
    declare     @quoted_obj         nvarchar(290)
    declare     @quoted_rowguid     nvarchar(290)
    declare     @before_rowguidname	sysname
	declare 	@snapshot_ready		int
	declare 	@columns			varbinary(128)
	declare		@column_list		nvarchar(4000)
	declare		@prefixed_column_list		nvarchar(4000)
	declare 	@colname			nvarchar(270)
	declare 	@colid				int
    declare     @dynamic_filters    bit
    declare		@alias_for_sourceobject	sysname
    declare		@retcode			int
    declare 	@dbname 			sysname
    declare		@allcolsreturned	bit
    declare		@tempcollistphase	int
    declare		@viewexeccmd		nvarchar(200)
    declare		@beforeviewexeccmd	nvarchar(200)
    
	-- Security check
	exec @retcode= dbo.sp_MSreplcheck_publish
	if @@error <> 0 or @retcode <> 0 return (1)

	set @tempcollistphase= 100
	set @viewexeccmd= 'select cmdtext from #viewcmd where phase<>' 
						+ cast(@tempcollistphase as nvarchar(5)) 
						+ ' order by phase,step'
	set @beforeviewexeccmd= 'select cmdtext from #beforeviewcmd order by phase,step'

    set @progress       = 1
    set @article_level  = 0
    set @permanent      = 1
    set @temporary      = 2
    set @allhaveguids   = 1
    set @before_rowguidname = NULL

    /*
    ** Only legal publisher can run this stored procedure
    */
    set nocount on
	/* make sure current database is enabled for merge replication */
    exec @retcode=dbo.sp_MSCheckmergereplication
    if @@ERROR<>0 or @retcode<>0
    	return (1)

    set @dbname= db_name()

    select @pubid = pubid, @snapshot_ready = snapshot_ready, @dynamic_filters = dynamic_filters 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 @table=object_name(objid) from sysmergearticles where pubid=@pubid and (columns is NULL or columns = 0x00)
	if @table is not NULL
	begin
		raiserror(21318, 16, -1, @table)
		return (1)
	end

    -- If snapshot is already ready, views are good.  Don't drop and recreate as someone
    -- might be using them.
    
	if @snapshot_ready = 1 and @force_flag = 0
		return (0)
		
	exec @retcode = dbo.sp_MSguidtostr @pubid, @pubidstr out
	if @@ERROR <>0 OR @retcode <>0 return (1)

	create table #viewcmd (phase int, step int identity(1,1), cmdtext nvarchar(4000))
	if @@error<>0 goto FAILURE
	create table #beforeviewcmd (phase int, step int identity(1,1), cmdtext nvarchar(4000))
	if @@error<>0 goto FAILURE

    create table #art(indexcol int identity NOT NULL, art_nick int NOT NULL, article_level int NOT NULL)
    if @@ERROR <> 0
    begin
        goto FAILURE
    end

    while @progress > 0
    BEGIN
        /*
        ** Select articles that have either a boolean_filter or at least one join filter 
        ** into a temp table in an optimized order.
        */
        insert into #art(art_nick, article_level) select nickname, @article_level from sysmergearticles 
            where pubid=@pubid and nickname not in (select art_nick from #art)
                and nickname not in 
                (select  art_nickname from sysmergesubsetfilters
                    where pubid=@pubid and join_nickname not in 
                        (select art_nick from #art))
        /*
        ** NOTENOTE: add error checking here.
        */

        set @progress = @@rowcount
        select @article_level = @article_level + 1
    END

    /* Drop the old views and reset sync_objid */
    select @art_nick = min(nickname) from sysmergearticles where pubid = @pubid and objid<>sync_objid
    while @art_nick is not null
    begin
        /* Drop the old view */
        select @viewname = OBJECT_NAME (sync_objid), @before_viewname = OBJECT_NAME(before_view_objid)
        	from sysmergearticles where
            pubid = @pubid and nickname = @art_nick
        if @viewname IS NOT NULL
        begin
            select @quoted_obj = QUOTENAME(@viewname)
            exec ('drop view ' + @quoted_obj)
        end
        if @before_viewname IS NOT NULL
        begin
            select @quoted_obj = QUOTENAME(@before_viewname)
        	exec ('drop view ' + @quoted_obj)
        end
        /* Update the row in sysmergearticles */
        update sysmergearticles set view_type = 0, sync_objid = objid where 
            pubid = @pubid and nickname = @art_nick
        if @@ERROR <> 0 goto FAILURE

        /* Find the next one */
        select @art_nick = min(nickname) from sysmergearticles where pubid = @pubid and objid<>sync_objid
    end
        
    set @art = 0
    select @art=min(indexcol) from #art where indexcol>@art

    while (@art is not null)
    begin
    	delete from #viewcmd
    	delete from #beforeviewcmd
    	
        select @art_nick=art_nick, @article_level = article_level from #art 
                where indexcol = @art
        select @article = name, @artid = artid, @columns = columns, @source_objid = objid,
        	@sync_objid = sync_objid, @procname = view_sel_proc, @before_objid = before_image_objid from sysmergearticles 
                where nickname=@art_nick and pubid = @pubid
                
		-- Need to run sp_MSgetcolumnlist already, to initialize @allcolsreturned.
		-- Just use an otherwise unused @phase value, which will be updated later on.
		insert into #viewcmd(phase, cmdtext) exec dbo.sp_MSgetcolumnlist 
													@pubid=@pubid,
													@column_list=@column_list output,
													@source_objid=@source_objid,
													@phase=@tempcollistphase,
													@allcolsreturned=@allcolsreturned output

		set @before_name = OBJECT_NAME(@before_objid)
		if @before_name is not null
		begin
			select @before_rowguidname=name from syscolumns where id=@source_objid and columnproperty(@source_objid, name , 'isrowguidcol')=1
			exec @retcode = dbo.sp_MSguidtostr @pubid, @guidstr out
			set @before_viewname = @before_name + '_v_' + @guidstr
		end
		else
			set @before_viewname = NULL
        
		exec @retcode = dbo.sp_MSguidtostr @artid, @guidstr out
		if @@ERROR <>0 OR @retcode <>0 return (1)

        select @source_object = QUOTENAME(user_name(uid)) + '.' + QUOTENAME(name) from sysobjects 
                where id = @source_objid 
        select @unqual_sourcename = QUOTENAME(OBJECT_NAME(@source_objid))
        
        select @bool_filterclause=subset_filterclause, @vertical_partition=vertical_partition 
        	from sysmergearticles where name = @article and pubid = @pubid

		-- verify the syntax of boolean filter, if added with vertical-partition to true
		-- in this case, the filter clause can contain columns that do not exist in the partition.
        if len(@bool_filterclause) > 0
    	begin
			/*
			-- let server return appropriate error message 
			exec ('select ' + @column_list + ' into #temptable_publicationview from ' + @source_object + 
				'declare @test int select @test=1 from #temptable_publicationview ' + @unqual_sourcename + ' where ' + @bool_filterclause)
			if @@ERROR<>0
			begin
				raiserror(21256, 16, -1, @bool_filterclause, @source_object)
				return (1)
			end
			*/
        	select @bool_filterclause = ' (' + @bool_filterclause + ') '
    	end
                
        set @rgcol = NULL
        select @rgcol = QUOTENAME(name) from syscolumns where id = @source_objid and
                ColumnProperty(id, name, 'isrowguidcol') = 1
        if @rgcol is not NULL
            set @hasguid = 1
        else 
        begin
            set @hasguid = 0
            set @allhaveguids = 0
        end

        /*
        ** Process non looping articles that have either a boolean or a join_filter.
        */
        if ( @article_level > 0 OR (len(@bool_filterclause) > 0) ) 
        begin
           /*
            ** If the article has a previously generated view, then drop the view before 
            ** creating the new one.
            */
            set @viewname = NULL
            select @viewname =  name from sysobjects where id = @sync_objid and
                ObjectProperty (id, 'IsView') = 1  and
                ObjectProperty (id, 'IsMSShipped') = 1 
            if @viewname IS NOT NULL
            begin
                select @quoted_obj = QUOTENAME(@viewname)
                exec ('drop view ' + @quoted_obj)
                if @@ERROR<>0 return (1)
            end
                /*
                ** Any join filter(s)? If any, process join filter(s)
            	*/
            if (@article_level > 0) 
            begin
                declare pub1 CURSOR LOCAL FAST_FORWARD FOR select join_filterclause, join_nickname, join_articlename,
                    join_unique_key, join_filterid from sysmergesubsetfilters where pubid=@pubid and artid=@artid
                FOR READ ONLY
                open pub1                                       
                fetch pub1 into @join_filterclause, @join_nick, @join_articlename, @join_unique_key, @join_filterid
				select @join_filterclause=' ( ' + @join_filterclause + ') '
                select @unqual_jointable = QUOTENAME(name) from sysobjects 
                    where id = (select objid from sysmergearticles where name=@join_articlename and pubid=@pubid) 
                
                if @max_network_optimization = 0
			        select @join_viewname = object_name(sync_objid), @join_before_view = object_name(before_image_objid)
                	from sysmergearticles where nickname = @join_nick and pubid = @pubid
                else
					select @join_viewname = object_name(sync_objid), 
					@join_before_view = object_name(case when before_view_objid is not null then before_view_objid else before_image_objid end)
                	from sysmergearticles where nickname = @join_nick and pubid = @pubid
					
                select @join_viewname = QUOTENAME(@join_viewname)

            	if 1=@hasguid
            	begin
	                if (@join_unique_key = 1 and (@bool_filterclause is null or len(@bool_filterclause) = 0) and
	                    not exists (select * from sysmergesubsetfilters where pubid=@pubid and artid=@artid and join_filterid <> @join_filterid))
	                begin
	                    set @simple_join_view = 1

						set @view_rule= 'select '
						insert into #viewcmd(phase,cmdtext) values (3, @view_rule)

						-- sp_MSgetcolumnlist was called at the beginning of the article loop.
						-- Thus, we now just set the right phase.
						update #viewcmd set phase= 4 where phase=@tempcollistphase

						set @view_rule= ' from ' + @source_object + ' ' + @unqual_sourcename + ' , ' +  @join_viewname + ' ' + @unqual_jointable + ' where (' + @join_filterclause
	                    				+ ') and ({ fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''') } = 1)'
	 					insert into #viewcmd(phase,cmdtext) values (5, @view_rule)
	                end
	                else
	                begin
	                    set @simple_join_view = 0

						/* Alias the source object with the unqualified name and use that to select the rowguidcol */                   
	                    set @view_rule = 'select ' + @unqual_sourcename + '.rowguidcol from ' + @source_object  + ' ' + @unqual_sourcename + ' , ' +  @join_viewname + ' ' + @unqual_jointable + ' where (' + @join_filterclause
	                    				+ ') and ({ fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''') } = 1)'
	                    insert into #viewcmd(phase, cmdtext) values(3, @view_rule)
	                end
                end

                if @before_name is not null
                begin
                	set @before_view_rule = 'select * from ' + @before_name + ' ' +  @unqual_sourcename + ' where (exists (select * from ' +
                   	 						@join_viewname + ' ' + @unqual_jointable + ' where ' + @join_filterclause + ') or exists (select * from ' +
                   	 						@join_before_view + ' ' + @unqual_jointable + ' where ' + @join_filterclause + ') '
                    insert into #beforeviewcmd(phase, cmdtext) values(3, @before_view_rule)
                end
                
                fetch next from pub1 into @join_filterclause, @join_nick, @join_articlename, @join_unique_key, @join_filterid
                WHILE (@@fetch_status <> -1)
                begin
					select @join_filterclause=' ( ' + @join_filterclause + ') '
                    select @unqual_jointable = quotename(name) from sysobjects 
                        where id = ( select objid from sysmergearticles where name=@join_articlename and pubid=@pubid) 
       
					if @max_network_optimization = 0
		                select @join_viewname = object_name(sync_objid), @join_before_view = object_name(before_image_objid)
                			from sysmergearticles where nickname = @join_nick and pubid = @pubid
                	else
                		select @join_viewname = object_name(sync_objid), 
               					@join_before_view = object_name(case when before_view_objid is not null then before_view_objid else before_image_objid end)
                					from sysmergearticles where nickname = @join_nick and pubid = @pubid
                	
                    select @join_viewname = QUOTENAME(@join_viewname)
                	if 1=@hasguid
                	begin
	                    set @view_rule = ' union select ' + @source_object + '.rowguidcol from ' + @source_object + ', ' +  @join_viewname + ' ' + @unqual_jointable + ' where (' + @join_filterclause
	                    				+ ') and ({ fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''') } = 1)'
	                    insert into #viewcmd(phase, cmdtext) values(5, @view_rule)
                    end

                   	if @before_name is not null
                   	begin
                		set @before_view_rule = ' or exists (select * from ' + @join_viewname + ' ' 
						                   	 	+ @unqual_jointable + ' where ' + @join_filterclause + ') or exists (select * from '
						                   	 	+ @join_before_view + ' ' + @unqual_jointable + ' where ' + @join_filterclause + ') '
                    	insert into #beforeviewcmd(phase, cmdtext) values(3, @before_view_rule)
                 	end
                 	
                    fetch next from pub1 into @join_filterclause, @join_nick, @join_articlename, @join_unique_key, @join_filterid
                end 
                close pub1
                deallocate pub1
                        
                if len(@bool_filterclause) > 0
            	begin
                	if 1=@hasguid
                	begin
	                    set @view_rule = ' union select ' + @source_object + '.rowguidcol from '+ @source_object + ' where ('+ @bool_filterclause
	                    				+ ') and ({ fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''') } = 1)'
	                    insert into #viewcmd(phase, cmdtext) values(5, @view_rule)
                    end

                   	if @before_name is not null
                   	begin
                		set @before_view_rule = ' or ' + @bool_filterclause
                    	insert into #beforeviewcmd(phase,cmdtext) values(3,@before_view_rule)
                	end
                end

            	if 1=@hasguid
            	begin
	                -- Now do the actual view rule as a semi-join, if not a simple join on unique key
	                if (@simple_join_view = 0)
	                begin
	                    /* 
	                    ** Generate a unique alias for the outer select to make sure that it does not generate an
	                    ** ambiguous reference with table names used in the join_filter clause 
	                    */
						set @alias_for_sourceobject = 'alias_' + @guidstr

						set @view_rule= 'select '
						insert into #viewcmd(phase, cmdtext) values (2, @view_rule)

						-- Here we cannot reuse the original call to sp_MSgetcolumnlist, because
						-- it was done with default value for @guid_alias.
						insert into #viewcmd(phase, cmdtext) 
							exec dbo.sp_MSgetcolumnlist 
										@pubid=@pubid,
										@column_list=@prefixed_column_list output,
										@source_objid=@source_objid,
										@guid_alias=@alias_for_sourceobject,
										@phase=2

						set @view_rule= ' from ' + @source_object + ' ' + @alias_for_sourceobject + ' where rowguidcol in ('
	                    insert into #viewcmd(phase,cmdtext) values(2,@view_rule)

	                    set @view_rule= ') and ({ fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''') } = 1)'
	                    insert into #viewcmd(phase, cmdtext) values(6, @view_rule)
	                end
                end
            end
            else  /* boolean filter only */
        	begin
            	if 1=@hasguid
            	begin
	        		delete from #viewcmd where phase<>@tempcollistphase
	        		delete from #beforeviewcmd
	        		
					set @view_rule= 'select '
					insert into #viewcmd(phase,cmdtext) values (2, @view_rule)

					-- sp_MSgetcolumnlist was called at the beginning of the article loop.
					-- Thus, we now just set the right phase.
					update #viewcmd set phase= 3 where phase=@tempcollistphase

	                select @view_rule= ' from '+ @source_object + ' ' + @unqual_sourcename + ' where ('+ @bool_filterclause
	                				+ ') and ({ fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''') } = 1)'
					insert into #viewcmd(phase, cmdtext) values (3, @view_rule)
				end

				if @before_name is not null
				begin
					set @before_view_rule = ' select * from ' + @before_name + ' ' + @unqual_sourcename + ' where (' + @bool_filterclause
                    insert into #beforeviewcmd(phase, cmdtext) values(3, @before_view_rule)
				end
			end

            select @viewname = @publication + '_' + @article + '_VIEW'
            exec @retcode = dbo.sp_MSuniqueobjectname @viewname , @viewname output
            if @retcode <> 0 or @@ERROR <> 0 return (1) 

            select @quoted_obj = QUOTENAME(@viewname)
            
            /* If we havent generated rowguidcol yet, use dummy rule that doesnt refer to it */
            if @hasguid = 0
            begin
            	delete from #viewcmd where phase<>@tempcollistphase
            	
				set @view_rule= 'select '
				insert into #viewcmd(phase, cmdtext) values (2, @view_rule)

				-- sp_MSgetcolumnlist was called at the beginning of the article loop.
				-- Thus, we now just set the right phase.
				update #viewcmd set phase= 3 where phase=@tempcollistphase

                set @view_rule= ' from '+ @source_object + ' ' + @unqual_sourcename
                			+ ' where ({ fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''') } = 1)'
				insert into #viewcmd(phase, cmdtext) values (3, @view_rule)
            end

			set @view_rule= 'create view '+ @quoted_obj + ' as '
			insert into #viewcmd(phase,cmdtext) values(1,@view_rule)

			exec @retcode= master.dbo.xp_execresultset
								@cmd= @viewexeccmd,
								@dbname= @dbname
			if @@error<>0 or @retcode<>0 return 1

            /* grant select permission on sync view to public - security check is performed inside the view */ 
			exec ('grant select on ' + @quoted_obj + ' to public')
			if @@ERROR<>0
				return (1)

            /* Mark view as system object */                        
            execute sp_MS_marksystemobject @quoted_obj
            if @@ERROR<>0
            	return (1)
            if @hasguid = 1
            begin
                select @procname=view_sel_proc from sysmergearticles where pubid=@pubid and artid=@artid
		        if object_id(@procname) is not NULL
                begin
                    set @quoted_obj= quotename(@procname)
                    exec ('drop procedure ' + @quoted_obj)
                    update sysmergearticles set view_sel_proc = NULL where artid = @artid and pubid = @pubid 
                end
                else
                begin
					set @procname = 'sel_' + substring(@guidstr, 1, 16) + substring(@pubidstr, 1, 16)
                    exec @retcode = dbo.sp_MSuniqueobjectname @procname , @procname output
                    if @retcode <> 0 or @@ERROR <> 0
                            return (1)
                end
                select @owner = user_name(uid) from sysobjects 
                    where name = @viewname 
                exec dbo.sp_MSmakeviewproc @viewname, @owner, @procname, @rgcol, @source_objid
                if @retcode<>0 or @@ERROR<>0
                	return (1)
                update sysmergearticles set view_sel_proc = @procname where pubid=@pubid and artid=@artid
            end
            select @quoted_obj = QUOTENAME(@viewname)
            update sysmergearticles set sync_objid = OBJECT_ID (@quoted_obj), view_type = @permanent
                where artid = @artid and pubid = @pubid 
			if @before_name is not null and @before_view_rule is not null
			begin
				set @quoted_obj= quotename(@before_viewname)

				exec @retcode = sp_MScreatebeforetable @source_objid
				if @@ERROR <>0 OR @retcode <>0 return (1)

				if object_id(@before_viewname) is not NULL
				begin
					exec ('drop view ' + @quoted_obj)			
				end
				
				set @before_view_rule= ') and ({ fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''') } = 1)'

                insert into #beforeviewcmd(phase,cmdtext) values(5,@before_view_rule)
                set @before_view_rule= 'create view ' + @quoted_obj + ' as '

                insert into #beforeviewcmd(phase,cmdtext) values(2,@before_view_rule)

				exec @retcode= master.dbo.xp_execresultset
									@cmd= @beforeviewexeccmd,
									@dbname= @dbname
				if @@error<>0 or @retcode<>0 return 1
			
				if @before_rowguidname is not NULL
				begin
					set @quoted_rowguid= quotename(@before_rowguidname)
					exec ('grant select (' + @quoted_rowguid + ') on '+ @quoted_obj + ' to public')
					if @@ERROR<>0
						return (1)
				end

				exec ('grant select (generation) on '+ @quoted_obj + ' to public')
				if @@ERROR<>0
					return (1)

				execute sp_MS_marksystemobject @before_viewname
	            if @@ERROR<>0
    	        	return (1)
	            update sysmergearticles set before_view_objid = OBJECT_ID (@before_viewname)
	            	where artid = @artid and pubid = @pubid
			end
        end
        else 
        begin
            select @sync_objid = @source_objid
            if @vertical_partition=1 and 0=@allcolsreturned
			begin
				select @viewname = @publication + '_' + @article + '_VIEW'
	            exec @retcode = dbo.sp_MSuniqueobjectname @viewname , @viewname output
				select @quoted_obj = QUOTENAME(@viewname)

				set @view_rule= 'create view '+ @quoted_obj + ' as '+ ' select '
				insert into #viewcmd(phase, cmdtext) values (1, @view_rule)

				-- sp_MSgetcolumnlist was called at the beginning of the article loop.
				-- Thus, we now just set the right phase.
				update #viewcmd set phase= 2 where phase=@tempcollistphase

				set @view_rule= ' from '+ @source_object + ' ' + @unqual_sourcename
                				+ ' where ({ fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''') } = 1)'
				insert into #viewcmd(phase, cmdtext) values (2, @view_rule)

				exec @retcode= master.dbo.xp_execresultset
									@cmd= @viewexeccmd,
									@dbname= @dbname
				if @@error<>0 or @retcode<>0 return 1
				
        	    execute sp_MS_marksystemobject @quoted_obj
            	if @@ERROR<>0
            		return (1)

                /* grant select permission on sync view to public - security check is performed inside the view */ 
				exec ('grant select on ' + @quoted_obj + ' to public')
				if @@ERROR<>0 return (1)
				
            	select @sync_objid=object_id(@viewname)
				update sysmergearticles set view_sel_proc = @procname, sync_objid=@sync_objid
		            where artid = @artid and pubid = @pubid 
			end
            else if @dynamic_filters = 1
            begin
                /* This article doesn't have any vertical or horizontal filters but if the publication is enabled for dynamic filtering, 
                    we still want to generate a dummy view so that logins in the publication access list can generate a dynamic snapshot. */
                select @viewname = @publication + '_' + @article + '_VIEW'
                exec @retcode = dbo.sp_MSuniqueobjectname @viewname, @viewname output
                select @quoted_obj = QUOTENAME(@viewname)
                set @view_rule = ' select  * from ' + @source_object  + ' where ({ fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''') } = 1)'     
            	exec ('create view '+ @quoted_obj + ' as '+ @view_rule)
            	if @@ERROR<>0
            		return (1)
        	    execute sp_MS_marksystemobject @quoted_obj
            	if @@ERROR<>0
            		return (1)
                exec ('grant select on ' + @quoted_obj + ' to public')
    	        if @@ERROR<>0
                	return (1)
            	select @sync_objid=object_id(@viewname)
				update sysmergearticles set view_sel_proc = @procname, sync_objid=@sync_objid, view_type = @permanent
	            where artid = @artid and pubid = @pubid 
            end

        	if @hasguid = 1
            begin
	            /* still make the select proc, although it selects directly from table */
		        if object_id(@procname) is not NULL
				begin
					set @quoted_obj= quotename(@procname)
					exec ('drop proc ' + @quoted_obj)
					update sysmergearticles set view_sel_proc = NULL where artid = @artid and pubid = @pubid 
				end
				
				set @procname = 'sel_' + substring(@guidstr, 1, 16) + substring(@pubidstr, 1, 16)
	            exec @retcode = dbo.sp_MSuniqueobjectname @procname , @procname output
	            if @retcode <> 0 or @@ERROR <> 0 return (1) 
	            select @owner = user_name(uid), @viewname = name from sysobjects 
	                where id = @sync_objid
	            exec dbo.sp_MSmakeviewproc @viewname, @owner, @procname, @rgcol, @source_objid
				update sysmergearticles set view_sel_proc = @procname where pubid=@pubid and artid=@artid
	        end
		end
		select @art=min(indexcol) from #art where indexcol>@art
	end

    /* If there are looping articles, we must use a dynamic publication since no views on temp tables */
    update sysmergearticles set view_type = @temporary
        where pubid=@pubid and nickname not in (select art_nick from #art)
    if @@rowcount > 0
    begin
        if not exists (select * from sysmergepublications where dynamic_filters = 1 and pubid = @pubid)
        begin
            declare @repl_nick int
            /* treat these articles as if the publication were dynamic */
            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                 

            select @art_nick = min(nickname) from sysmergearticles where
                pubid = @pubid and view_type = @temporary
            while @art_nick is not null
            begin
                /* Loop over articles with circular filters.  Create dummy view and add rows to contents */
                select @article = name, @artid = artid, @source_objid = objid, @sync_objid = sync_objid, @procname = view_sel_proc from sysmergearticles 
                    where nickname=@art_nick and pubid = @pubid
                select @source_object = QUOTENAME(user_name(uid)) + '.' + QUOTENAME(name) from sysobjects 
                    where id = @source_objid 

                set @viewname = NULL
                select @viewname =  name from sysobjects where id = @sync_objid and
                    ObjectProperty (id, 'IsView') = 1  and
                    ObjectProperty (id, 'IsMSShipped') = 1 
                if @viewname IS NOT NULL
                begin
                    select @quoted_obj = QUOTENAME(@viewname)
                    exec ('drop view ' + @quoted_obj)
                    if @@ERROR<>0 return (1)
                end
                select @viewname = 'SYNC_' + @publication + '_' + @article 
                exec @retcode = dbo.sp_MSuniqueobjectname @viewname , @viewname output
                if @retcode <> 0 or @@ERROR <> 0 return (1) 
                select @quoted_obj = QUOTENAME(@viewname)
                exec ('create view ' + @quoted_obj + ' as select * from ' + @source_object + ' 
                        where 1 = 0 ')
                if @@ERROR<>0 return (1)
                update sysmergearticles set sync_objid = OBJECT_ID (@viewname),
                    view_sel_proc = NULL where artid = @artid and pubid = @pubid 
                if @@ERROR<>0 return (1)

				select @owner = user_name(uid) from sysobjects where id = @source_objid
				set @table = OBJECT_NAME(@source_objid)
        		exec @retcode = dbo.sp_addtabletocontents @table, @owner
                IF @@ERROR <> 0 or @retcode <> 0 return (1)
                
                select @art_nick = min(nickname) from sysmergearticles where
                    pubid = @pubid and view_type = @temporary and nickname > @art_nick
            end
        end
    end
        
    drop table #art
    if @allhaveguids = 1
    begin
        /* create the filter expand procs now */
        set @filter_id = 0
        select @filter_id = min(join_filterid) from sysmergesubsetfilters where
                pubid = @pubid and join_filterid > @filter_id
        while @filter_id is not null
        begin
            set @filter_id_str = convert(nvarchar(10), @filter_id)
            select @procname = expand_proc
                from sysmergesubsetfilters where pubid = @pubid and join_filterid = @filter_id

            /* drop old proc, or generate a new procname */
	        if object_id(@procname) is not NULL
	        begin
	        	set @quoted_obj= quotename(@procname)
                exec ('drop procedure ' + @quoted_obj)
            end
            else
            begin
                set @procname = 'expand_' + @filter_id_str
                exec @retcode = dbo.sp_MSuniqueobjectname @procname, @procname output
                if @retcode <>0 return (1)
                update sysmergesubsetfilters set expand_proc = @procname where  pubid = @pubid and join_filterid = @filter_id
            end
            set @command = 'exec dbo.sp_MSmakeexpandproc ' + quotename(@publication) + ' , ' + @filter_id_str + ', ' + quotename(@procname)
            exec @retcode = master..xp_execresultset @command, @dbname
            if @retcode <> 0 return (1)
			exec dbo.sp_MS_marksystemobject @procname
            if @@ERROR<>0
            	return (1)
            set @quoted_obj= quotename(@procname)
            exec ('grant execute on ' + @quoted_obj + ' to public ')
            select @filter_id = min(join_filterid) from sysmergesubsetfilters where
                pubid = @pubid and join_filterid > @filter_id
        end
    end

    return (0)

FAILURE: 
    return (1)
go
exec dbo.sp_MS_marksystemobject sp_MSpublicationview
go
grant execute on dbo.sp_MSpublicationview to public
go

dump tran master with no_log
go

raiserror('Creating procedure sp_addmergesubscription', 0,1)
GO
CREATE PROCEDURE sp_addmergesubscription (
    @publication                    sysname,                    /* Publication name */
    @subscriber                     sysname = NULL,             /* Subscriber server */
    @subscriber_db                  sysname = NULL,             /* Subscription database */
    @subscription_type              nvarchar(15) = 'push',          /* Subscription type - push, pull */ 
    @subscriber_type                nvarchar(15) = 'local',         /* Subscriber type */ 
    @subscription_priority          real        = NULL,             /* Subscription priority */
    @sync_type                      nvarchar(15) = 'automatic',     /* subscription sync type */
    @frequency_type                 int = NULL,            
    @frequency_interval             int = NULL,        
    @frequency_relative_interval    int = NULL,
    @frequency_recurrence_factor    int = NULL,
    @frequency_subday               int = NULL,            
    @frequency_subday_interval      int = NULL,
    @active_start_time_of_day       int = NULL,
    @active_end_time_of_day         int = NULL,
    @active_start_date              int = NULL,
    @active_end_date                int = NULL,
    @optional_command_line          nvarchar(4000) = NULL,
    @description                    nvarchar(255) = NULL,
    @enabled_for_syncmgr            nvarchar(5) = 'false', /* Enabled for SYNCMGR: true or false */
    -- Agent offload
    @offloadagent                   bit = 0,
    @offloadserver                  sysname = NULL,
	@use_interactive_resolver		nvarchar(5) = 'false',
	@merge_job_name					sysname = NULL
    ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */
    declare @retcode                int
    declare @subnickname            int
    declare @subscriber_srvid       int 
    declare @publisher_srvid        int 
    declare @priority               real
    declare @subid                  uniqueidentifier
    declare @pubid                  uniqueidentifier    /* Publication id */
    declare @subscriber_typeid      smallint
    declare @merge_jobid            binary(16)          /* Scheduler jobid for the merge agent */
    declare @subscription_type_id   int   
    declare @distproc               nvarchar(300)
    declare @command                nvarchar(255)
    declare @inactive               tinyint
    declare @subscriber_bit         smallint
    declare @global                 tinyint     /* subscriber type is global */
    declare @push                   tinyint     /* subscription type is push */
    declare @partnerid              uniqueidentifier    /* Partner replica identifier */
    declare @sync_typeid            tinyint
    declare @nosync                 tinyint     
    declare @automatic              tinyint     
    declare @distributor            sysname
    declare @distribdb              sysname
    declare @publisher              sysname
    declare @publisher_db           sysname
    declare @found                  int
    declare @datasource_type        int
    declare @datasource_path        sysname
    DECLARE @platform_nt            binary
    declare @datasrctype	        int
    declare @datasrcpath		    sysname
	declare @use_interactive_bit	bit

    
	/* make sure current database is enabled for merge replication */
    exec @retcode=dbo.sp_MSCheckmergereplication
    if @@ERROR<>0 or @retcode<>0
    	return (1)

    /*
    ** Initializations.
    */
    set @datasource_type = 0    /* Default SQL Server */
    set @datasource_path = NULL 
	set @datasrctype = 0    /* Default SQL Server */
    set @datasrcpath = NULL 
    set @platform_nt = 0x1  
    SET @nosync             = 2       /* Const: synchronization type 'none' */
    SET @automatic          = 1       /* Const: synchronization type 'automatic' */
    set @inactive           = 0
    SET @subscriber_bit     = 4  
    set @global             = 1
    set @push               = 0
    set @pubid              = NULL         
    set @publisher          = @@SERVERNAME
    set @publisher_db       = DB_NAME()
    select @found           = 1    /* Any non-NULL value is fine */
    /*
    ** Parameter Check: @subscription_type.
    ** Set subscriber_typeid based on the @subscription_type specified.
    **
    **   subscription_type    subscription_type
    **   =================    ===============
    **             0            push
    **             1            pull
    */
    if LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('push', 'pull')
        BEGIN
            RAISERROR (14128, 16, -1)
            RETURN (1)
        END
    IF LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) = 'push'
        set @subscription_type_id = 0
    else 
        set @subscription_type_id = 1


    /*
    ** Parameter check: @offloadagent
    ** If @offloadagent = 1 then @subscription_type_id must be 0 
    */
    IF (@offloadagent = 1) AND (@subscription_type <> N'push') 
    BEGIN
        RAISERROR(21138, 16, -1)
        RETURN (1)
    END 

    /*
    ** Security Check.
    */

    IF @subscription_type_id = 0 
    BEGIN
        exec @retcode = dbo.sp_MSreplcheck_publish
        if @@ERROR <> 0 or @retcode <> 0
            return(1)
    END
    ELSE
    BEGIN
        exec @retcode = dbo.sp_MSreplcheck_pull @publication
        if @@ERROR <> 0 or @retcode <> 0
        begin
            return(1)
        end
    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

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

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

    IF @subscriber = 'all'
        BEGIN
            RAISERROR (14136, 16, -1)
            RETURN (1)
        END

    /*
    ** Get distribution server information for remote RPC call.
    */
    EXECUTE @retcode = dbo.sp_helpdistributor @rpcsrvname = @distributor OUTPUT,
         @distribdb   = @distribdb OUTPUT
    IF @@ERROR <> 0 or @retcode <> 0
        BEGIN
        GOTO FAILURE
        END

        SELECT @distproc = RTRIM(@distributor) + '.' + RTRIM(@distribdb) + 
            '.dbo.sp_MShelp_subscriber_info '
    exec @distproc @publisher, @subscriber, @found output
    if (@found <> 1) 
        BEGIN
            RAISERROR (14085, 16, -1)
            RETURN (1)
        END

	exec @retcode = dbo.sp_MSget_subtypedatasrc @subscriber, @distributor, @distribdb, @datasrctype OUTPUT, @datasrcpath OUTPUT
	if @retcode<>0
		return (1)
	if (@datasrctype = 2) or (@datasrctype = 4) or (@datasrctype = 5)
	BEGIN
		select @datasource_type = @datasrctype
		select @datasource_path = @datasrcpath
	END
	
    EXECUTE @retcode = dbo.sp_validname @subscriber
    IF @@ERROR <> 0 OR @retcode <> 0
       RETURN (1)

    /*
    ** Parameter Check: @subscriber_db
    */
    IF @subscriber_db IS NULL
    BEGIN
        RAISERROR (14043, 16, -1, '@subscriber_db')
        RETURN (1)
    END

    IF @subscriber_db = 'all'
    BEGIN
        RAISERROR (14136, 16, -1)
        RETURN (1)
    END

    /*
    **  Check to see if system tables exist. If not create them. Since under current
    **  design every database is qualified for subscribing.
    */
    
    IF not exists (select name from sysobjects where name='sysmergesubscriptions')
        BEGIN
            execute @retcode = dbo.sp_MScreate_mergesystables
                if @@ERROR <> 0 or @retcode <> 0
                    begin
                        return (1)
                    end
        END 
        
    /*
    ** Parameter Check: @publication.
    ** Check to make sure that the publication exists and that it conforms
    ** to the rules for identifiers.
    */
    if NOT EXISTS (select * FROM sysmergepublications 
        WHERE name = @publication and UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name())
        BEGIN
            RAISERROR (20026, 16, -1, @publication)
            RETURN (1)
        END

    if @pubid IS NULL
        select @pubid = pubid FROM sysmergepublications 
            WHERE name = @publication and UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name()
    if @partnerid IS NULL
        begin
            select @partnerid = subid FROM sysmergesubscriptions 
                WHERE srvid = @publisher_srvid and db_name = @publisher_db and pubid = @pubid
        end                     

    /*
    ** Parameter Check: @subscriber_type.
    ** Set subscriber_typeid based on the @subscriber_type specified.
    **
    **   subscriber_type     subscriber_type
    **   =================    ===============
    **             1            global
    **             2            local
    **             3            anonymous
    **                          Type 'republisher' is taken out for B3. We may want to add this back later.
    */
    if LOWER(@subscriber_type collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('local', 'global')
        BEGIN
            RAISERROR (20037, 16, -1)
            RETURN (1)
        END

    if LOWER(@subscriber_type collate SQL_Latin1_General_CP1_CS_AS) IN ('global')
        set @subscriber_typeid = 1
    else if LOWER(@subscriber_type collate SQL_Latin1_General_CP1_CS_AS) IN ('local')
        set @subscriber_typeid = 2

   	/*
    ** Parameter Check: @use_interactive_resolver  
    */
    if LOWER(@use_interactive_resolver collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@use_interactive_resolver')
            RETURN (1)
        END
    if LOWER(@use_interactive_resolver collate SQL_Latin1_General_CP1_CS_AS) = 'true'
        set @use_interactive_bit = 1
    else 
        set @use_interactive_bit = 0

	
    /* 
    ** Assign priority appropriately - choose 0.99 times the minimum priority
    ** of the global replicas.
    */
    if (@subscription_priority >= 100.0 or @subscription_priority < 0.0)
        BEGIN
            RAISERROR (20088, 16, -1)
            RETURN (1)
        END
    if (@subscription_priority IS NULL)
        begin
            select @priority = 0.99 * min(priority) from sysmergesubscriptions where subscriber_type = 1
            if (@priority IS NOT NULL)
                select @subscription_priority = @priority
            if (@subscription_priority IS NULL) 
                select @subscription_priority = 0.0
        end
    /*
    ** For local and anonymous subscriptions the priority is 0.0
    */
    if LOWER(@subscriber_type collate SQL_Latin1_General_CP1_CS_AS) IN ('local', 'anonymous')
        select @subscription_priority = 0.0
    
    /*
    ** Validate that the subscriber is a valid server
    */
    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 exists (select * from sysobjects where name= 'syssubscriptions')
    begin
        if exists (select name from sysmergeextendedarticlesview where pubid=@pubid and objid in
            (select objid from sysextendedarticlesview where artid in
                (select artid from syssubscriptions where dest_db=@subscriber_db and srvid=@subscriber_srvid)))
        begin
            RAISERROR(21280, 16, -1, @publication, @subscriber_db)
            RETURN (1)
        end
    end


    /* 
    ** Make sure that @offloadserver doesn't contain invalid characters
    */
    exec @retcode = sp_MSreplcheckoffloadserver @offloadserver
    if @retcode<>0 or @@error<>0
        return (1)

    /*
    ** Making it possible for a deleted subscription to come back.
    ** UNDONE : This disallows second pull subscription from being added unless the previous 
    ** subscription was initial synced.
    */
    if EXISTS (select db_name, srvid
            FROM sysmergesubscriptions
            WHERE db_name = @subscriber_db
            AND srvid = @subscriber_srvid                          
            AND pubid = @pubid AND status <>2) --We can definitely add back subscriptions that were deleted.
        BEGIN
            RAISERROR (14058, 16, -1)
            RETURN (1)
        END
        
    IF EXISTS (select db_name, srvid FROM sysmergesubscriptions 
        WHERE db_name = @subscriber_db AND srvid = @subscriber_srvid AND pubid = @pubid AND status = 2) 
            BEGIN
            	select @subid = subid from sysmergesubscriptions 
            		WHERE db_name = @subscriber_db AND srvid = @subscriber_srvid AND pubid = @pubid
				delete  from sysmergesubscriptions where subid = @subid
				delete from MSmerge_replinfo where repid = @subid
            END
	select @subid = newid()    
	
   /*
   ** Parameter Check: @sync_type.
   ** Set sync_typeid based on the @sync_type specified.
   **
   **   sync_typeid     sync_type
   **   ===========     =========
   **             1     automatic
   **             2     none
   */


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


    /*
    ** If current publication contains an article without rowguidcol, do not allow no-sync subscription
    */
   IF LOWER(@sync_type collate SQL_Latin1_General_CP1_CS_AS) = 'automatic'
   BEGIN
        SET @sync_typeid = @automatic
   END
   ELSE
   BEGIN
        if exists (select * from sysmergearticles a where pubid=@pubid and 
            not exists (select * from syscolumns c where c.id = a.objid and ColumnProperty(c.id, c.name, 'IsRowGuidCol') = 1))
            
            BEGIN
                Raiserror(20086, 16, -1, @publication)
                RETURN (1)
            END
        else 
            SET @sync_typeid = @nosync
   END


    /*
    ** UNDONE: Validate that the publisher is of type "republisher"
    */
    begin tran
    save TRAN addmergesubscription
        /* Generate a guid for the Subscriber ID */
    
        /* Look for existing nickname from any other subscription */
        exec @retcode = dbo.sp_MSgetreplnick @subscriber, @subscriber_db , NULL,  @subnickname out
        if (@@error <> 0) or @retcode <> 0 
			GOTO FAILURE
            
        /* Generate a new replica nickname from the @subid */
        if (@subnickname is null)
        begin
            EXECUTE dbo.sp_MSgenreplnickname @subid, @subnickname output
            if @@ERROR<>0 
                GOTO FAILURE
        end
            
        /*
        ** The subscription doesn't exist, so let's add it to sysmergesubscriptions 
        */
        INSERT sysmergesubscriptions (subid, 
                                      partnerid,
                                      datasource_type, 
                                      datasource_path, 
                                      srvid, 
                                      db_name, 
                                      pubid,
                                      status, 
                                      subscriber_type,
                                      subscription_type,
                                      priority, 
                                      sync_type, 
                                      description,
                                      login_name,
                                      subscriber_server,
                                      use_interactive_resolver,
                                      publication,
                                      distributor)
                        VALUES (@subid,
                            @partnerid,
                            @datasource_type,
                            @datasource_path,
                            @subscriber_srvid,
                            @subscriber_db,
                            @pubid,
                            @inactive,
                            @subscriber_typeid,
                            @subscription_type_id,
                            @subscription_priority,
                            @sync_typeid,
                            @description,
                            suser_sname(suser_sid()),
                            @subscriber,
                            @use_interactive_bit,
                            @publication,
                            @distributor)          
        if @@ERROR <> 0
            BEGIN
                GOTO FAILURE
            END
                
        /*
        ** Get distribution server information for remote RPC call.
        */
        EXECUTE @retcode = dbo.sp_helpdistributor @rpcsrvname = @distributor OUTPUT,
           @distribdb   = @distribdb OUTPUT
        IF @@ERROR <> 0 or @retcode <> 0
            BEGIN
                GOTO FAILURE
            END

        SELECT @distproc = RTRIM(@distributor) + '.' + RTRIM(@distribdb) + 
            '.dbo.sp_MSadd_merge_subscription'
        EXEC @retcode = @distproc 
            @publisher = @@SERVERNAME, 
            @publisher_db = @publisher_db, 
            @publication = @publication,
            @subscriber = @subscriber, 
            @subscriber_db = @subscriber_db, 
            @subscription_type = @subscription_type_id,
            @sync_type = @sync_typeid, 
            @status = @inactive,
            @frequency_type = @frequency_type,
            @frequency_interval = @frequency_interval,
            @frequency_relative_interval = @frequency_relative_interval,
            @frequency_recurrence_factor = @frequency_recurrence_factor,
            @frequency_subday = @frequency_subday,
            @frequency_subday_interval = @frequency_subday_interval,
            @active_start_time_of_day = @active_start_time_of_day,
            @active_end_time_of_day = @active_end_time_of_day,
            @active_start_date = @active_start_date,
            @active_end_date = @active_end_date,
            @optional_command_line = @optional_command_line,
            @merge_jobid = @merge_jobid OUTPUT,
            @offloadagent = @offloadagent,
            @offloadserver = @offloadserver,
			@agent_name = @merge_job_name  
            
        IF @@ERROR <> 0 OR @retcode <> 0
            begin   
                goto FAILURE
            end
            
        /*
        **  Add row for subscription in MSmerge_replinfo.
        */
        insert MSmerge_replinfo(repid, replnickname, merge_jobid)
                values (@subid, @subnickname, @merge_jobid)
        if @@ERROR <> 0
        	BEGIN
                GOTO FAILURE
            END

        /* Conditional support for MobileSync */
        if LOWER(@enabled_for_syncmgr collate SQL_Latin1_General_CP1_CS_AS) = 'true'
        BEGIN

            /* MobileSync Support */
            declare @distributor_server                 sysname
            declare @distributor_security_mode          int
            declare @distributor_login                  sysname
            declare @distributor_password               nvarchar(524)

            /* 
            ** The registry entry needs to be created only for push subscriptions -  
            ** i.e - need not be called when a pull subscription is created at the 
            ** subscriber and sp_addmergesubscription is being called then.
            */
            
            IF @subscription_type_id = 0 
            BEGIN
                EXECUTE @retcode = dbo.sp_helpdistributor
                    @distributor = @distributor_server OUTPUT               /* Distributor RPC server name */
                IF @@ERROR <> 0 or @retcode <> 0
                    BEGIN
                        GOTO FAILURE
                    END

                -- Always use integrated security on winNT
                if (@platform_nt = platform() & @platform_nt )
                    begin
                        set @distributor_security_mode = 1
                    end
                -- For Win9x the dist publisher and distributor are the same machine                
                else
                    begin
                        select  @distributor_security_mode = 0,
                            @distributor_login  = login,
                            @distributor_password = password
                        from msdb..MSdistpublishers where UPPER(name) = UPPER(@@servername) collate database_default
                    end

                /* Call sp_MSregistersubscription so that the subscription can be synchronized via Onestop etc. */
                exec @retcode = dbo.sp_MSregistersubscription @replication_type = 2,
                                    @publisher = @@SERVERNAME,
                                    @publisher_db = @publisher_db,
                                    @publication = @publication,
                                    @subscriber = @subscriber,
                                    @subscriber_db = @subscriber_db,
                                    @distributor = @distributor,
                                    @distributor_security_mode = @distributor_security_mode,
                                    @distributor_login = @distributor_login,
                                    @distributor_password = @distributor_password,
                                    @subscription_id = @subid,
                                    @subscription_type = @subscription_type_id,
									@use_interactive_resolver = @use_interactive_bit
                IF @@error <> 0 OR @retcode <> 0
                    BEGIN
                        GOTO FAILURE
                    END

            END                                    
        END     
    COMMIT TRAN
    return (0)

FAILURE:
    RAISERROR (14057, 16, -1)
    /* UNDONE : This code is specific to 6.X nested transaction semantics */
    if @@TRANCOUNT > 0
    begin
        ROLLBACK TRANSACTION addmergesubscription
        COMMIT TRANSACTION
    end
    RETURN (1)

go
exec dbo.sp_MS_marksystemobject sp_addmergesubscription 
go

grant execute on dbo.sp_addmergesubscription to public
go

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

CREATE PROCEDURE sp_MSretrieve_mergepublication 
@publication sysname
AS
declare @retcode int
/*
** Security Check
*/
exec @retcode = dbo.sp_MSreplcheck_publish
if @@ERROR <> 0 or @retcode <> 0
	return(1)

select	'name' = name, 
		'database ' = db_name(), 
		'publisher' = @@SERVERNAME, 
		'type' = 'Merge', 
		'description ' = description, 
		'status ' = status,  
		'allow known pull subscription' = allow_pull, 
		'allow immediate-updating subscription ' = 0,
		'allow anonymous ' = allow_anonymous,  
		'allow queued-updating subscription ' = 0, 
		'allow snapshot files FTP downloading' = enabled_for_internet, 
		'third party' = sync_mode 
	from sysmergepublications
	where name = @publication
go

EXEC dbo.sp_MS_marksystemobject sp_MSretrieve_mergepublication
GO

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

CREATE PROCEDURE sp_changemergesubscription (
    @publication        sysname = NULL, /* Publication name */
    @subscriber         sysname = NULL,  /* Subscriber server */
    @subscriber_db      sysname = NULL,  /* Subscription database */
    @property           sysname = NULL, /* The property to change */
    @value              nvarchar(255) = NULL,    /* The new property value */
	@force_reinit_subscription bit = 0	/* Force reinit subscription */
    ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */
    declare @subscriber_bit         smallint
    declare @subscriber_srvid       int
    declare @publisher_srvid        int
    declare @retcode                int
    declare @pubid                  uniqueidentifier
    declare @subid                  uniqueidentifier
    declare @partnerid              uniqueidentifier
    declare @sync_typeid            tinyint
    declare @nosync                 tinyint
    declare @automatic              tinyint
     
    declare @artid                  uniqueidentifier
    declare @schematype             int
    declare @schemaversion          int
    declare @schemaguid             uniqueidentifier
    declare @db_name                sysname
    declare @subscriber_type        int
    declare @schematext             nvarchar(2000)
    declare @publisher              sysname
    declare @publisher_db           sysname
	declare @use_interactive_bit	bit 
	declare @enabled_for_syncmgr	int
	declare @regkey					nvarchar(1000)
	declare @snapshot_ready			int
     

    /*
    ** Initializations.
    */
    SET @nosync         = 2     /* Const: synchronization type 'none' */
    SET @automatic      = 1     /* Const: synchronization type 'automatic' */
    set @publisher      = @@SERVERNAME
    set @publisher_db   = DB_NAME()

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


    /*
    **  Check to see if current database is doing publishing/subscribing
    */
    IF not exists (select name from sysobjects where name='sysmergesubscriptions')
        BEGIN
            RAISERROR (14055, 16, -1)
            RETURN (1)
        END

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

    IF @property IS NULL
        BEGIN
            CREATE TABLE #tab1 (properties sysname collate database_default)
            INSERT INTO #tab1 VALUES ('sync_type')
            INSERT INTO #tab1 VALUES ('priority')
            INSERT INTO #tab1 VALUES ('description') 
			INSERT INTO #tab1 VALUES ('use_interactive_resolver') 
            select * FROM #tab1
            RETURN (0)
        END

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

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

    select @pubid = pubid, @snapshot_ready=snapshot_ready 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

    /*
    ** 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

    /*
    ** Parameter Check:  @subscriber.
    ** Check to make sure we have a valid subscriber.
    */
    IF @subscriber IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@subscriber')
            RETURN (1)
        END
    /*
    ** Validate that the subscriber is a valid server
    */
    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

    /*
    ** Check to see if you have a local / global subscription on this publication
    */
    set @subid = NULL
    select @subid = subs1.subid, 
     
    @pubid = pubs.pubid, /* identified from publication name */
    @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(@@servername) 
            and pubs.publisher_db=db_name()

    if @subid IS NULL 
        begin
            RAISERROR (14050, 11, -1)
            RETURN(1)
        end                 

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

    
    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('sync_type', 'priority', 'description', 'use_interactive_resolver')
        BEGIN
            RAISERROR (20078, 16, -1)
            RETURN (1)
        END


	BEGIN TRANSACTION change_subscription
	save TRAN change_subscription
	IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'use_interactive_resolver'
        BEGIN

            /* Check to make sure that we have a true/false. */

            IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
                BEGIN
		            RAISERROR (14148, 16, -1, 'use_interactive_resolver')
                    goto UNDO	
                END

            /* Determine the bit value. */

	        IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'
		        SET @use_interactive_bit = 1
	        ELSE
		        SET @use_interactive_bit = 0
	
			/* Update the subscription with the new 'use_interactive' value. */

            UPDATE sysmergesubscriptions
               	SET use_interactive_resolver = @use_interactive_bit
             	WHERE subid = @subid
            IF @@ERROR <> 0
                BEGIN
                    RAISERROR (14053, 16, -1)
                    goto UNDO
                END

			/* If the subscription is enable for Sync Manager, then update the reg value */
			exec sp_MSsubscription_enabled_for_syncmgr
				@publisher, @publisher_db, @publication, @subscriber, @subscriber_db, 
				@enabled_for_syncmgr OUT, @regkey OUT
            IF @@ERROR <> 0
                BEGIN
                    RAISERROR (14053, 16, -1)
                    goto UNDO
                END
			if @enabled_for_syncmgr = 1
				begin
					EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
			                               @regkey,
			                               'UseInteractiveResolver',
			                               'REG_DWORD',
			                                @use_interactive_bit
					if @retcode <> 0 OR @@ERROR <> 0
		                BEGIN
		                    RAISERROR (14053, 16, -1)
		                    goto UNDO
		                END
				end
        END

    /*
    ** Change the property.
    */
    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'sync_type'
        BEGIN 
            /*
            ** Check to make sure that we have a valid sync_type.
            */
            IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('automatic', 'none')
                BEGIN
                    RAISERROR (14052, 16, -1)
                    goto UNDO
                END

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

            IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'automatic'
                SET @sync_typeid = @automatic
            ELSE
            BEGIN

		    /*
    		**  If current publication contains an article without rowguidcol, do not allow no-sync subscription
	    	*/	
			if exists (select * from sysmergearticles a where pubid = @pubid and 
                    not exists (select * from syscolumns c where c.id=a.objid and columnproperty (c.id, c.name, 'isrowguidcol')=1))
                begin
                    Raiserror(20086, 16, -1, @publication)
                    goto UNDO
                end
                else 
                	SET @sync_typeid = @nosync
            END

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

            UPDATE sysmergesubscriptions
                SET sync_type = @sync_typeid
                WHERE subid = @subid
            IF @@ERROR <> 0
                BEGIN
                    RAISERROR (14053, 16, -1)
                    goto UNDO
                END

			if @snapshot_ready>0
			begin
				--if there is one article in this publication with pre_command of other than 'drop',
				--do not allow changing subscription property.
				if exists (select * from sysmergearticles where pubid=@pubid and pre_creation_command<>1)
					begin
	                    RAISERROR (21420, 16, -1, @property)
    	                goto UNDO				
					end
				/*
				** make sure we know we really want to do this.
				*/
				if @force_reinit_subscription = 0
					begin
						raiserror(20608, 16, -1)
						goto UNDO
					end
				exec @retcode=sp_MSreinitmergepublication @publication
				if @@ERROR<>0 or @retcode<>0
					GOTO UNDO 
			end
        END
        
    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'description'
        BEGIN
        UPDATE sysmergesubscriptions
                SET description = @value
                WHERE subid = @subid
            IF @@ERROR <> 0
                BEGIN
                    RAISERROR (14053, 16, -1) 
              
                    goto UNDO
                END

        END
        
     
    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'priority'
    BEGIN

    select @db_name = db_name from sysmergesubscriptions
        where (pubid=@pubid) and (subid=@pubid)
        IF @db_name <> db_name()
        BEGIN
            RAISERROR (20047, 16, -1)
            goto UNDO
        END  

    /* Only the original publisher can change priority of a global subscriptions */

        IF @subscriber_type<>1  
            BEGIN
                RAISERROR (20044, 16, -1)  /* Local subscriber does not have priority*/
                goto UNDO
            END

        IF convert(real, @value)>100.0
            BEGIN
                RAISERROR (20049, 16, -1)  /* Don't accept priority greater than 100 */
                goto UNDO
            END
        
		exec dbo.sp_MSchange_priority @subid,  @value
        if @@ERROR<>0 goto UNDO
	   	/* Insert the sp_MSchange_priority schema change only if the publication's snapshot is ready */
		if (@snapshot_ready > 0)
			begin
		        select @schemaversion = schemaversion from sysmergeschemachange
		        if (@schemaversion is NULL)
		        set @schemaversion = 1
		        else
		            select @schemaversion = 1 + max(schemaversion) from sysmergeschemachange
		            
		         
		        set @schemaguid = newid()
		        set @artid = newid()
		        set @schematype = 8 /* change priority */
		        select @schematext = 'exec dbo.sp_MSchange_priority '+ '''' + convert(nchar(36),@subid) + '''' + ',' + '''' + @value + '''' 
		        exec @retcode=sp_MSinsertschemachange @pubid, @artid, @schemaversion, @schemaguid, @schematype, @schematext
				if @@ERROR<>0 or @retcode<>0 goto UNDO
			end				    
    END

	COMMIT TRANSACTION

    /*
    ** Return succeed. It is not an error message.
    */

    RAISERROR (14054, 10, -1)
    RETURN (0)
UNDO:
    if @@TRANCOUNT > 0
    begin
        ROLLBACK TRANSACTION change_subscription
        COMMIT TRANSACTION
    end
	return (1)
go

exec dbo.sp_MS_marksystemobject sp_changemergesubscription 
go

grant execute on dbo.sp_changemergesubscription to public
go

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

CREATE PROCEDURE sp_helpmergesubscription(
    @publication            sysname = '%',      /* Publication name */
    @subscriber             sysname = '%',      /* Subscriber server */
    @subscriber_db          sysname = '%',      /* Subscription database */
    @publisher              sysname = '%',      /* Publisher server */
    @publisher_db           sysname = '%',      /* Publisher database */
    @subscription_type      nvarchar(15) = 'both', /* Subscription type - push or pull */ 
    @found int = NULL OUTPUT
    )AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    declare @db                 sysname
    declare @retcode            int
    declare @subscriber_bit     smallint
    declare @srvid              int
    declare @pubid              uniqueidentifier
    declare @subid              uniqueidentifier
    declare @partnerid          uniqueidentifier
    declare @cursor_open        int
    declare @no_row             bit
    declare @subscription_type_id int

    declare @distributor    sysname
    declare @distributiondb sysname
    declare @distproc       nvarchar(300)
    declare @dbname         sysname

    select @distributor = null
    select @distributiondb = null
    select @distproc = null
    select @dbname = null    

    /*
    ** Initializations.
    */
    set @subscriber_bit     = 4
    set @cursor_open        = 0                 

    /*
    ** Initializations of @now_row.
    */
    IF @found is NULL
    BEGIN
        SELECT @no_row=0
    END
    ELSE
    BEGIN
        SELECT @no_row=1
    END

    select @db=db_name() -- so that it can appear in dynamic query

    /*
    **  Calling sp_help* is all right whether current database is enabled for pub/sub or not
    */

    IF not exists (select * from sysobjects where name='sysmergesubscriptions')
        RETURN (0)
    
    /* Security check */
    EXEC @retcode = dbo.sp_MSreplcheck_pull @publication = @publication, 
        @raise_fatal_error = 0
    if @@ERROR <> 0 or @retcode <> 0
        return(1)

    /*
    ** Parameter Check: @subscription_type.
    ** Set subscription_typeid based on the @subscription_type specified.
    **
    **   subscription_type    subscription_type
    **   =================    ===============
    **             0            push
    **             1            pull
    **            2         both
    */
    if LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('push', 'pull', 'both')
        BEGIN
            RAISERROR (20079, 16, -1)
            RETURN (1)
        END
    IF LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) = 'both'
        set @subscription_type_id = 2
    else IF LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) = 'push'
        set @subscription_type_id = 0
    else 
        set @subscription_type_id = 1

    /*
    ** Parameter Check: @publisher
    ** Check to make sure that the publisher is defined
    */
    IF @publisher <> '%'
    BEGIN
        EXECUTE @retcode = dbo.sp_validname @publisher
        IF @@ERROR <> 0 OR @retcode <> 0
            RETURN (1)
    END

    /*
    ** Parameter Check:  @subscriber.
    ** If remote server, limit the view to the remote server's subscriptions.
    ** Make sure that the name isn't NULL.
    */
    if @subscriber IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@subscriber')
            RETURN (1)
        END

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

    if @subscriber <> '%'
        BEGIN
            EXECUTE @retcode = dbo.sp_validname @subscriber

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

            if NOT EXISTS (select *
                            FROM master..sysservers
                            WHERE UPPER(srvname) = UPPER(@subscriber) collate database_default
                            AND (srvstatus & @subscriber_bit) <> 0)
                BEGIN
                    --RAISERROR (14010, 16, -1)
                    RETURN (1)
                END
        END

    /*
    ** Parameter Check:  @publication.
    ** If the publication name is specified, check to make sure that it
    ** conforms to the rules for identifiers and that the publication
    ** actually exists.  Disallow NULL.
    */
    if @publication IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@publication')
            RETURN (1)
        END

    /*
    ** Get subscriptions
    */
    if @publication <>'%'
    begin
        execute @retcode = dbo.sp_MSsubsetpublication @publication
        if @@ERROR <> 0 or @retcode<>0
                Return (1)
    end
    
    create table #helpsubscription
                (
                    publication             sysname     collate database_default not null,
                    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,
                    status                  int         NOT NULL,
                    subscriber_type         int         NOT NULL,
                    subscription_type       int         NOT NULL,
                    priority                float(8)    NOT NULL,
                    sync_type               tinyint     NOT NULL,
                    description             nvarchar(255) collate database_default null,
                    merge_jobid             binary(16)  NULL,
                    full_publication        tinyint 	NULL,
                    use_interactive_resolver 	int		NULL
                )


    /* 
    ** Performance Optimization: Eliminate the 'LIKE' clause for publication name.
    **                           Empirical evidence shows almost 50% speed improvement when
    **                           opening the cursor if publication name is provided.
    */
    IF (@publication <> '%')
        insert into #helpsubscription select distinct pubs.name, servers2.srvname, subs2.db_name, servers1.srvname, subs1.db_name, 
                        subs1.status, subs1.subscriber_type, subs1.subscription_type, subs1.priority, 
                        subs1.sync_type, subs1.description, replinfo.merge_jobid, pubs.publication_type,
                        subs1.use_interactive_resolver

                FROM  sysmergesubscriptions     subs1,
                      sysmergesubscriptions     subs2,
                      MSmerge_replinfo      replinfo,
                      master..sysservers        servers1,
                      master..sysservers        servers2,
                      sysmergepublications  pubs
                    where subs1.subid <> subs2.subid 
                        and subs1.status <> 2 
                        and subs2.subid = subs1.partnerid
                        and pubs.pubid = subs1.pubid
                        and pubs.pubid = subs2.pubid
                        and servers1.srvid = subs1.srvid
                        and servers2.srvid = subs2.srvid
                        and pubs.name =  @publication 
                        and replinfo.repid = subs1.subid
                        and (suser_sname(suser_sid()) = subs1.login_name OR is_member('db_owner')=1 OR is_srvrolemember('sysadmin') = 1)
                        and ((@subscriber_db = N'%') or (subs1.db_name = @subscriber_db collate database_default))
                        and ((@publisher_db = N'%') or (subs2.db_name = @publisher_db collate database_default))
                        and ((@subscriber = N'%') or (UPPER(servers1.srvname) = UPPER(@subscriber) collate database_default)) 
                        and ((@publisher = N'%') or (UPPER(servers2.srvname) = UPPER(@publisher) collate database_default))
                        and (subs1.subscription_type = @subscription_type_id or @subscription_type_id = 2)
    ELSE
        insert into #helpsubscription select distinct pubs.name, servers2.srvname, subs2.db_name, servers1.srvname, subs1.db_name, 
                        subs1.status, subs1.subscriber_type, subs1.subscription_type, subs1.priority, 
                        subs1.sync_type, subs1.description, replinfo.merge_jobid, pubs.publication_type,
                        subs1.use_interactive_resolver

                FROM  sysmergesubscriptions     subs1,
                      sysmergesubscriptions     subs2,
                      MSmerge_replinfo      replinfo,
                      master..sysservers        servers1,
                      master..sysservers        servers2,
                      sysmergepublications  pubs
                    where subs1.subid <> subs2.subid 
                        and subs1.status <> 2 
                        and subs2.subid = subs1.partnerid
                        and pubs.pubid = subs1.pubid
                        and pubs.pubid = subs2.pubid
                        and servers1.srvid = subs1.srvid
                        and servers2.srvid = subs2.srvid
                        and replinfo.repid = subs1.subid
                        and (suser_sname(suser_sid()) = subs1.login_name OR is_member('db_owner')=1 OR is_srvrolemember('sysadmin') = 1)
                        and ((@subscriber_db = N'%') or (subs1.db_name = @subscriber_db collate database_default))
                        and ((@publisher_db = N'%') or (subs2.db_name = @publisher_db collate database_default)) 
                        and ((@subscriber = N'%') or (UPPER(servers1.srvname) = UPPER(@subscriber) collate database_default)) 
                        and ((@publisher = N'%') or (UPPER(servers2.srvname) = UPPER(@publisher) collate database_default))
                        and (subs1.subscription_type = @subscription_type_id or @subscription_type_id = 2)
        

    if exists (select * from #helpsubscription)
        select @found = 1
    else
        select @found = 0 
        
    if @no_row = 1
        goto DONE

    CREATE TABLE #merge_agent_properties
    (
        job_id                VARBINARY(16) NOT NULL,
        offload_enabled       bit NULL,
        offload_server        sysname collate database_default null
    )

    EXEC @retcode = dbo.sp_helpdistributor @rpcsrvname = @distributor OUTPUT,
                                           @distribdb = @distributiondb OUTPUT
    IF @retcode <> 0
        GOTO DONE

    SELECT @distributor = RTRIM(@distributor)

    -- Get distribution agent properties 
    IF LOWER(@@SERVERNAME) <> LOWER(@distributor)
    BEGIN
        SELECT @distproc = @distributor + '.' + @distributiondb + 
                           '.dbo.sp_MSenum_merge_agent_properties'
    END    
    ELSE
    BEGIN
        SELECT @distproc = @distributiondb + 
                           '.dbo.sp_MSenum_merge_agent_properties'
    END

    SELECT @dbname = db_name()

    INSERT INTO #merge_agent_properties
      EXEC @retcode = @distproc @publisher = @@SERVERNAME, 
                                @publisher_db = @dbname, 
                                @publication = @publication            

    IF LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) = 'push' or LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) = 'both'
        begin
            select 'subscription_name'  = subscriber + ':' + subscriber_db,
                hs.publication, hs.publisher, hs.publisher_db, 
                hs.subscriber, hs.subscriber_db, hs.status, hs.subscriber_type,
                hs.subscription_type, hs.priority, hs.sync_type, hs.description,
                hs.merge_jobid, hs.full_publication,
                ap.offload_enabled, ap.offload_server,
                hs.use_interactive_resolver
                from #helpsubscription hs
                left outer join #merge_agent_properties ap
                on hs.merge_jobid = ap.job_id
                order by hs.publisher, hs.publisher_db, hs.publication, hs.subscriber, hs.subscriber_db
        end
    else 
        begin
            select 'subscription_name' = hs.publisher + ':' + hs.publisher_db + ':' + hs.publication, 
                hs.publication, hs.publisher, hs.publisher_db, 
                hs.subscriber, hs.subscriber_db, hs.status, hs.subscriber_type,
                hs.subscription_type, hs.priority, hs.sync_type, hs.description,
                hs.merge_jobid, hs.full_publication,
                ap.offload_enabled, ap.offload_server,                 
                hs.use_interactive_resolver
                from #helpsubscription hs
                left outer join #merge_agent_properties ap
                on hs.merge_jobid = ap.job_id
                order by hs.publisher, hs.publisher_db, hs.publication, hs.subscriber, hs.subscriber_db
        end

    drop table #merge_agent_properties
    select @retcode = 0
DONE:
    if (@cursor_open = 1)
        begin
            close #cursor
            deallocate #cursor
        end         
    drop table #helpsubscription
    return @retcode 
go
exec dbo.sp_MS_marksystemobject sp_helpmergesubscription
go

grant execute on dbo.sp_helpmergesubscription to public
go

raiserror('Creating procedure sp_dropmergesubscription', 0,1)
GO
CREATE PROCEDURE sp_dropmergesubscription(
    @publication        sysname = NULL,             /* Publication name */
    @subscriber         sysname = NULL,             /* Subscriber server */
    @subscriber_db      sysname = NULL,             /* Subscription database */
    @subscription_type  nvarchar(15) = 'both',          /* Subscription type - push, pull, both */ 
    @ignore_distributor bit = 0,
    @reserved 			bit = 0
    )AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */
    declare @retcode                int
    declare @subscriber_bit         smallint
    declare @subscriber_type        smallint
    declare @subscriber_srvid       int
    declare @publisher_srvid        int
    declare @pubid                  uniqueidentifier
    declare @subid                  uniqueidentifier
    declare @partnerid              uniqueidentifier
    declare @subscription_type_id   int   
    declare @found_subscription     int   
    declare @local_server           sysname
    declare @local_db               sysname
    declare @cmd                    nvarchar(290)
    declare @distributor            sysname
    declare @distribdb              sysname
    declare @distproc               nvarchar(300)
    declare @pubidstr               nvarchar(38)
    declare @publisher              sysname
    declare @publisher_db           sysname

    declare @implicit_transaction	int
	declare @close_cursor_at_commit int

	select @close_cursor_at_commit = 0
	select @implicit_transaction = 0

	-- Security check
	if 1 <> is_member('db_owner')
	begin
		RAISERROR (15247, 11, -1)
		return 1
	end

	/*
	** Save setting values first before changing them
	*/
	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

     /*
    ** Initializations.
    */
    set @subscriber_bit     = 4
    set @subscription_type_id = -1
    set @found_subscription = 0                     
    set @local_db           = DB_NAME()
    set @local_server       = @@SERVERNAME
    set @publisher          = @@SERVERNAME
    set @publisher_db       = DB_NAME()

    /*
    **  Check to see if current database is enabled for publishing/subscribing
    */
    IF not exists (select name from sysobjects where name='sysmergesubscriptions')
        BEGIN
            RAISERROR (14055, 16, -1)
            RETURN (1)
        END

    /*
    ** Parameter Check: @subscription_type.
    ** Set subscription_typeid based on the @subscription_type specified.
    **
    **   subscription_type    subscription_type
    **   =================    ===============
    **             0            push
    **             1            pull
    */
    if LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('both', 'push', 'pull')
        BEGIN
            RAISERROR (14128, 16, -1)
            RETURN (1)
        END
    IF LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) = 'both'
        begin
            EXECUTE @retcode = dbo.sp_dropmergesubscription @publication = @publication,
                            @subscriber = @subscriber,
                            @subscriber_db = @subscriber_db,
                            @subscription_type = 'push',
                            @ignore_distributor = @ignore_distributor,
                            @reserved = 1
			if @retcode<>0 or @@ERROR<>0
				return (1)
            EXECUTE @retcode = dbo.sp_dropmergesubscription @publication = @publication,
                            @subscriber = @subscriber,
                            @subscriber_db = @subscriber_db,
                            @subscription_type = 'pull',
                            @ignore_distributor = @ignore_distributor,
							@reserved = 1
			if @retcode<>0 or @@ERROR<>0
				return (1)
			return (0)
        end 
    IF LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) = 'push'
        set @subscription_type_id = 0
    else 
        set @subscription_type_id = 1

    /*
    ** Parameter validation (different for push and pull modes)
    */

    IF LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) = 'push'
        begin
            /*
            ** Assign parameter values appropriately
            */
            if @publisher IS NULL
                set @publisher = @@SERVERNAME
            if (@publisher_db IS NULL)
                set @publisher_db = DB_NAME()

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

            /*
            ** Parameter Check: @subscriber_db
            */
            IF @subscriber_db IS NULL
            BEGIN
                select @subscriber_db = 'all'
            END
                
        end
    else
        begin
            /*
            ** Assign parameter values appropriately
            */
            if @subscriber IS NULL
                set @subscriber = @@SERVERNAME
            if @subscriber_db IS NULL
                set @subscriber_db = DB_NAME()

            /*
            ** 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

        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 
                where UPPER(publisher)=UPPER(@@SERVERNAME) and publisher_db=db_name() 
                FOR READ ONLY
            
            OPEN hC1
            FETCH hC1 INTO @publication
            WHILE (@@fetch_status <> -1)
                BEGIN
                    EXECUTE dbo.sp_dropmergesubscription @publication = @publication,
                                                @subscriber = @subscriber,
                                                @subscriber_db = @subscriber_db,
                                                @subscription_type = @subscription_type,
                                                @ignore_distributor = @ignore_distributor,
												@reserved = 1
                    FETCH hC1 INTO @publication
                END
            CLOSE hC1
            DEALLOCATE hC1
            RETURN (0)
        END
        
    if NOT EXISTS (select * FROM sysmergepublications 
        WHERE name = @publication and UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name())
        BEGIN
            RAISERROR (20026, 16, -1, @publication)
            RETURN (1)
        END
    select @pubid = pubid from sysmergepublications 
        where name = @publication and UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name()
    set @pubidstr = '''' + convert(nchar(36), @pubid) + ''''
    if @pubid is null
        BEGIN
            RAISERROR (20026, 16, -1, @publication)
            RETURN (1)
        END

       
    IF LOWER(@subscriber) = 'all'
        BEGIN
            declare hC2 CURSOR LOCAL FAST_FORWARD FOR select DISTINCT srvname 
            FROM master..sysservers 
            WHERE (srvstatus & 4 <> 0) FOR READ ONLY
    
            OPEN hC2
            FETCH hC2 INTO @subscriber
            WHILE (@@fetch_status <> -1)
                BEGIN
                    EXECUTE dbo.sp_dropmergesubscription @publication = @publication,
                                                @subscriber = @subscriber,
                                                @subscriber_db = @subscriber_db,
                                                @subscription_type = @subscription_type,
                                                @ignore_distributor = @ignore_distributor,
												@reserved = 1
                    FETCH hC2 INTO @subscriber
                END
            CLOSE hC2
            DEALLOCATE hC2
            RETURN (0)
        END

    /*
    ** Validate that the subscriber is a valid server
    */
    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

    /*
    ** NOTE: remove this batch
    */
    IF LOWER(@subscriber_db) = 'all'
        BEGIN
                declare hC3 CURSOR LOCAL FAST_FORWARD FOR select DISTINCT db_name FROM sysmergesubscriptions
                WHERE srvid =  @subscriber_srvid 
                    AND subid <> pubid 
                    AND sysmergesubscriptions.pubid = @pubid
                    AND sysmergesubscriptions.subscription_type = @subscription_type_id
                FOR READ ONLY       

            OPEN hC3
            FETCH hC3 INTO @subscriber_db
            WHILE (@@fetch_status <> -1)
                BEGIN
                    EXECUTE dbo.sp_dropmergesubscription @publication = @publication,
                                                @subscriber = @subscriber,
                                                @subscriber_db = @subscriber_db,
                                                @subscription_type = @subscription_type,
                                                @ignore_distributor = @ignore_distributor,
												@reserved = 1

                    FETCH hC3 INTO @subscriber_db
                END
            CLOSE hC3
            DEALLOCATE hC3
            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

    select @pubid=pubid from sysmergepublications where name=@publication and UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name()
    if @pubid is NULL return (0)
    
    if exists (select * from sysmergesubscriptions where subid=@pubid 
        and pubid=@pubid and db_name<>db_name())
    RETURN (0)
    
    /*
    ** Get subscriptions from either local replicas or global replicas
    */
    select @subid = subs1.subid, @partnerid = subs2.subid, @subscriber_type = subs1.subscriber_type 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(@@servername)
            and pubs.publisher_db=db_name()
            and subs1.subscription_type = @subscription_type_id
            and (suser_sname(suser_sid()) = subs1.login_name OR is_member('db_owner')=1 OR is_srvrolemember('sysadmin') = 1)

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

    begin tran
    save TRAN dropmergesubscription

        /*
        ** Do not drop the subscription corresponding to the loopback subscription 
        */
        if (@subid <> @partnerid) 
            begin
                /*
                ** global/republisher subscriptions have to stay for a while even after being
                ** dropped so that they won't regain lives for themselves. They would be cleanup eventually.
                */
                
                if (@subscriber_type<>1) 
                    begin
                        delete from sysmergesubscriptions where subid = @subid
                        IF @@ERROR <> 0
                            GOTO FAILURE        
                        delete MSmerge_replinfo WHERE repid = @subid 
                        IF @@ERROR <> 0
                            GOTO FAILURE
                    end
                else
                    begin
                        update sysmergesubscriptions set status=2 where subid=@subid
                        IF @@ERROR<>0
                            GOTO FAILURE
                    end 

                /* 
                ** The MobileSync registry entry needs to be dropped only for push subscriptions -  
                ** i.e - need not be called when a pull subscription is created at the 
                ** subscriber and sp_addmergesubscription is being called then.
                */
                IF LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) = 'push'
                begin
                    /* Call sp_MSunregistersubscription so that the reg entries get deleted */
                    exec @retcode = dbo.sp_MSunregistersubscription @publisher = @@SERVERNAME,
                                    @publisher_db = @publisher_db,
                                    @publication = @publication,
                                    @subscriber = @subscriber,
                                    @subscriber_db = @subscriber_db
                    IF @retcode<>0 or @@ERROR<>0
                        GOTO FAILURE

                END             
            end     

        /*
        ** if @ignore_distributor = 1, we are in bruteforce cleanup mode, don't do RPC.
        */
        if @ignore_distributor = 0
        begin
            /*
            ** Get distribution server information for remote RPC call.
            */
            EXECUTE @retcode = dbo.sp_helpdistributor @rpcsrvname = @distributor OUTPUT,
               @distribdb   = @distribdb OUTPUT
            IF @@ERROR <> 0 or @retcode <> 0
                BEGIN
                    GOTO FAILURE
                END

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

            EXEC @retcode = @distproc 
                @@SERVERNAME, 
                @publisher_db, 
                @publication,
                @subscriber, 
                @subscriber_db,
                @subscription_type
            IF @@ERROR <> 0 OR @retcode <> 0
                begin   
                    goto FAILURE
                end 
        end 

        /* 
        ** 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() collate database_default)=0
            BEGIN
                execute @retcode = dbo.sp_MSdrop_mergesystables
                if @@ERROR <> 0 or @retcode <> 0
                    begin
                        return (1)
                    end
            END         

    COMMIT TRAN
    /*
	** 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(0)       

FAILURE:
    /* UNDONE : This code is specific to 6.X nested transaction semantics */
    RAISERROR (14056, 16, -1)
    if @@TRANCOUNT > 0
    begin
        ROLLBACK TRANSACTION dropmergesubscription
        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 dbo.sp_MS_marksystemobject sp_dropmergesubscription
go

grant execute on dbo.sp_dropmergesubscription to public
go

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

CREATE PROCEDURE sp_MShelpvalidationdate(
@publication		sysname,
@subscriber			sysname,
@subscriber_db		sysname
)AS
declare @pubid					uniqueidentifier
declare @subscriber_srvid		int

-- Security check
if 1 <> is_member('db_owner')
begin    
	RAISERROR (15247, 11, -1)
	return (1)
end

select @pubid=pubid from sysmergepublications where name=@publication and UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name()
if @pubid is NULL return (0)

select @subscriber_srvid = srvid from master..sysservers where UPPER(srvname) = UPPER(@subscriber) collate database_default
select last_validated, attempted_validate from sysmergesubscriptions where pubid=@pubid and db_name=@subscriber_db and srvid=@subscriber_srvid

return (0)
GO
exec dbo.sp_MS_marksystemobject sp_MShelpvalidationdate
go


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

CREATE PROCEDURE sp_MSmergepublishdb(
      @value     sysname,
      @ignore_distributor bit = 0
    ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */
    declare @command        nvarchar(255)
    declare @description    nvarchar(500)
    declare @cmptlevel      tinyint
    declare @db_name        sysname 
    declare @retcode        int
    declare @distributor    sysname
    declare @distribdb      sysname
    declare @category_name  sysname
    declare @agentname      sysname
	declare @working_directory nvarchar(255)
    /*
    ** Initialization
    */

    select @db_name = DB_NAME()


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

    /*
    ** if @ignore_distributor = 1, we are in bruteforce cleanup mode, don't do RPC.
    */
    if @ignore_distributor = 0
    begin
        /*
        ** Test to see if the distributor is installed and online.
        */
        EXECUTE @retcode = dbo.sp_helpdistributor @rpcsrvname = @distributor OUTPUT, @directory=@working_directory OUTPUT,
           @distribdb   = @distribdb OUTPUT

        IF @@ERROR <> 0 or @retcode <> 0 or @distributor IS NULL or @distribdb IS NULL
        BEGIN
            IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'
                RAISERROR (20028, 16, -1)
            ELSE
                RAISERROR (20029, 16, -1)
            RETURN (1)
        END
    end

    /*
    ** Enable the database for publishing.
    */
    IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'
        BEGIN
            select @cmptlevel = cmptlevel from master..sysdatabases where name=db_name() collate database_default
            if @cmptlevel<70 OR @cmptlevel is NULL 
                begin
                    RAISERROR(20061, 16, -1)
                    goto FAILURE
                end
            execute @retcode = dbo.sp_MScreate_mergesystables
            if @@ERROR <> 0 or @retcode <> 0
                begin
                    goto FAILURE
                end

		    END

    ELSE    /* Disable the database for publishing. */
        BEGIN 
           
          /*
          ** Remove all the registration entries for subscriptions
          */
            if not exists(select * from sysobjects where name = 'sysmergesubscriptions')
                goto FAILURE
            exec @retcode = dbo.sp_dropmergesubscription @publication = 'all', 
                                            @subscriber = 'all', 
                                            @subscriber_db = 'all', 
                                            @subscription_type = 'both',
                                            @ignore_distributor = @ignore_distributor
            IF @@ERROR <> 0 or @retcode <> 0 
                begin
                    goto FAILURE
                end
            /*
            ** Remove all publications and articles in the database.
            */
            EXEC @retcode = dbo.sp_dropmergepublication @publication = 'all',
                @ignore_distributor = @ignore_distributor
            IF @@ERROR <> 0 or @retcode <> 0 
                begin
                    -- sp_dropmergepublication will raiserror 
                    goto FAILURE
                end
                
            If NOT EXISTS (select * from sysmergepublications)
                BEGIN
                    execute @retcode = dbo.sp_MSdrop_mergesystables
                    if @@ERROR <> 0 or @retcode <> 0
                        begin
                            goto FAILURE
                        end
                END
    
    END
        
        
        return 0
FAILURE:
    
    return (1)  

GO
exec dbo.sp_MS_marksystemobject sp_MSmergepublishdb
go

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

CREATE PROCEDURE sp_enumcustomresolvers
--	@distributor parameter will be removed in the next version.   
    @distributor     sysname = NULL
    AS
    SET NOCOUNT ON

    declare @distributor_rpc sysname
    declare @return_status int
    declare @distproc nvarchar(150)
    declare @retcode int

    select @return_status = 0
 
   
    /*
    ** Get the distributor 
    ** Use local RPC if @distributor == @servername. This is used by UI
    ** before installing a distributor.
    */
    if @distributor = @@servername
        select @distributor_rpc = @@servername
    else
    begin
        EXEC @return_status = dbo.sp_helpdistributor @rpcsrvname = @distributor_rpc OUTPUT
        IF @@error <> 0 OR @return_status <> 0 OR @distributor_rpc IS NULL
        BEGIN
            RAISERROR (20036, 16, -1)
            RETURN (1)
        END
    end

    declare @key_exists int
    select @key_exists = 0
    create table #keyexists (keyexists int) 
    select @distproc = RTRIM(@distributor_rpc) + '.master..xp_regread'
    insert into #keyexists exec @distproc 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Microsoft SQL Server\80\Replication\ArticleResolver'
    select @key_exists = keyexists from #keyexists
    if (@key_exists = 1)
        begin
            select @distproc = RTRIM(@distributor_rpc) + '.master..xp_regenumvalues'
            exec @distproc 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Microsoft SQL Server\80\Replication\ArticleResolver'    
            if @@ERROR<>0 return (1)
        end
    drop table #keyexists
    RETURN (0)
GO
exec dbo.sp_MS_marksystemobject sp_enumcustomresolvers
go

grant execute on dbo.sp_enumcustomresolvers to public
go

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

create procedure sp_changemergefilter(
    @publication            sysname,
    @article                sysname,
    @filtername             sysname,
    @property               sysname,
    @value                  nvarchar(2000),
	@force_invalidate_snapshot bit = 0,	/* Force invalidate existing snapshot */
	@force_reinit_subscription bit = 0	/* Force reinit subscription */
    )AS

    set nocount on

	declare @pre_command		int
	declare @qual_object		nvarchar(132)
	declare @qual_join_object	nvarchar(132)
	declare @join_articlename	nvarchar(4000)
    declare @db_name        sysname
    declare @pubid          uniqueidentifier
    declare @artid          uniqueidentifier
    declare @object			sysname
    declare @join_object	sysname
    declare @retcode        int
    declare @join_filterid  int
    declare @join_objid     int
    declare @join_nickname  int
    declare @snapshot_ready int

    /*
    ** Security Check.
    ** Only the System Administrator (SA) or the Database Owner (dbo) can
    ** call this procedure
    */
    exec @retcode = dbo.sp_MSreplcheck_publish
    if @@ERROR <> 0 or @retcode <> 0
        return(1)

    /*
    ** Parameter Check: @join_articlename.
    ** The join_articlename cannot be NULL 
    */
    if @filtername is NULL
        begin
            raiserror (14043, 11, -1, '@filtername')
            return (1)
        end

    if @value is NULL or @value = ''
        begin
            raiserror (14043, 11, -1, '@value')
            return (1)
        end

    /*
    ** Parameter Check: @publication.
    ** The @publication id cannot be NULL and must conform to the rules
    ** for identifiers.
    */
    if @publication is NULL
        begin
            raiserror (14043, 11, -1, '@publication')
            return (1)
        end
    /*
    ** Get the pubid and make sure the publication exists
    */
    select @pubid = pubid, @snapshot_ready=snapshot_ready from sysmergepublications where 
        name = @publication and UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name()
    if @pubid is NULL
        begin
            raiserror (20026, 16, -1, @publication)
            return (1)
        end

    select @db_name = db_name from sysmergesubscriptions
        where (pubid=@pubid) and (subid=@pubid)
        IF @db_name <> db_name()
        BEGIN
            RAISERROR (20047, 16, -1)
            RETURN (1)
        END

    /*
    ** Parameter Check: @article.
    ** Check to see that the @article is valid and does exist 
    */
    if @article is NULL
        begin
            raiserror (20045, 16, -1)
            return (1)
        end
        
    select @artid = artid, @object=object_name(objid), @pre_command=pre_creation_command from sysmergearticles where name = @article and pubid = @pubid  
    if @artid is NULL
        begin
            raiserror (20046, 16, -1)
            return (1)
        end

    
    select @join_filterid=join_filterid, @join_articlename=join_articlename from sysmergesubsetfilters
        where pubid=@pubid and artid=@artid and filtername=@filtername

    if @join_filterid is null
        begin
            raiserror (21362, 16, -1, @filtername) 
            return (1)
        end

     IF @property IS NULL
        BEGIN
            CREATE TABLE #temp (properties sysname collate database_default)
            INSERT INTO #temp VALUES ('filtername')
            INSERT INTO #temp VALUES ('join_filterclause')
            INSERT INTO #temp VALUES ('join_articlename')
            INSERT INTO #temp VALUES ('join_unique_key')
            select * FROM #tab1
            RETURN (0)
        END 


    if @value is null
        begin
	        RAISERROR (14043, 16, -1, @property)
            return (1)
        end

	begin TRAN
	save TRAN change_filter

	if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) in ('join_filterclause','join_articlename','join_unique_key')
		and @snapshot_ready>0
	begin
			-- 1 means'drop': which is the only option that support reintialization
			if @pre_command<>1 				
				begin
					raiserror(21419, 16, -1, @filtername, @article)
					goto FAILURE			
				end
			/*
			** make sure we know we really want to do this.
			*/
			if @force_invalidate_snapshot = 0
				begin
					raiserror(20607, 16, -1)
					goto FAILURE
				end
			if @force_reinit_subscription = 0
				begin
					raiserror(20608, 16, -1)
					goto FAILURE
				end

			update sysmergepublications set snapshot_ready=2 where pubid=@pubid
			if @@ERROR<>0
				goto FAILURE
			exec @retcode = sp_MSreinitmergepublication @publication
			if @retcode<>0 or @@ERROR<>0
				goto FAILURE

	end
	
    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS)='join_filterclause'
        BEGIN
		/* check the validity of join_filterclause */
		select @join_object=object_name(objid) from sysmergearticles where name=@join_articlename and pubid=@pubid
		select @qual_object=QUOTENAME(@object)
		select @qual_join_object=QUOTENAME(@join_object)
		exec ('declare @test int select @test=1 from ' + @qual_object + ', ' + @qual_join_object + ' where ' + @value)
		if @@ERROR<>0
			begin
				raiserror(21256, 16, -1, @value, @object)
				goto FAILURE
			end
		update sysmergesubsetfilters set join_filterclause=@value
                where join_filterid=@join_filterid
		execute @retcode = dbo.sp_MSsubsetpublication @publication
		if @@ERROR <> 0 or @retcode<>0
			goto FAILURE
        END

       IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS)='join_unique_key'
		BEGIN
			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'
				update sysmergesubsetfilters set join_unique_key=1 where join_filterid=@join_filterid
			else
				update sysmergesubsetfilters set join_unique_key=0 where join_filterid=@join_filterid
			if @@ERROR <> 0 or @retcode<>0
				goto FAILURE
		END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS)='filtername'
        BEGIN
            update sysmergesubsetfilters set filtername=@value
                where join_filterid=@join_filterid
			if @@ERROR<>0
				goto FAILURE
        END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS)='join_articlename'
        BEGIN
            select @join_objid = objid from sysmergearticles where name = @value and pubid = @pubid
            
            IF @join_objid is NULL
            BEGIN
                raiserror (14027, 11, -1, @value)
                goto FAILURE
            END

            select @join_nickname = nickname from sysmergearticles where pubid = @pubid AND objid = @join_objid 
            if @join_nickname is NULL
            begin
                raiserror (20001, 11, -1, @article, @publication)
                goto FAILURE
            end
            
            update sysmergesubsetfilters set join_articlename=@value, join_nickname=@join_nickname
                where join_filterid=@join_filterid
			if @@ERROR<>0
				goto FAILURE
        END
        
	Commit TRAN
    return(0)

FAILURE:
    if @@TRANCOUNT > 0
    begin
        ROLLBACK TRANSACTION change_filter
        COMMIT TRANSACTION
    end
    RAISERROR (20038, 16, -1, @article, @publication)
    return(1)
 
go
exec dbo.sp_MS_marksystemobject sp_changemergefilter
go

grant execute on dbo.sp_changemergefilter to public
go

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

create procedure sp_addmergefilter(
    @publication            sysname,            /* publication name */
    @article                sysname,            /* article name */
    @filtername             sysname,            /* join filter name */
    @join_articlename       sysname,            /* Name of the table being joined to the base table */
    @join_filterclause      nvarchar(2000),     /* filter clause qualifying the join */             
    @join_unique_key        int = 0,
    @force_invalidate_snapshot bit = 0,			/* Force invalidate existing snapshot */
	@force_reinit_subscription bit = 0			/* Force reinit subscription */
    )AS

    set nocount on

    /*
    ** Declarations.
    */
    declare @snapshot_ready		int
    declare @db_name            sysname
    declare @pubid              uniqueidentifier                
    declare @artid              uniqueidentifier
    declare @art_nickname       int
    declare @join_nickname      int
    declare @db                 sysname
	declare @qual_object		nvarchar(150)
	declare @qual_join_object	nvarchar(150)
	declare @qual_object_view	nvarchar(150)
	declare @qual_join_object_view nvarchar(150)
	
    declare @object             sysname
    declare @vertical			int
    declare @join_vertical		int
    declare @join_object		nvarchar(140)
	declare @object_view		nvarchar(140)
	declare @join_object_view	nvarchar(140)
    declare @owner              sysname
    declare @retcode            int
    declare @join_objid         int
    declare @objid				int
    declare @status             int
    declare @column_list		nvarchar(4000)
    
	/* make sure current database is enabled for merge replication */
    exec @retcode=dbo.sp_MSCheckmergereplication
    if @@ERROR<>0 or @retcode<>0
    	return (1)

    /*
    ** Security Check.
    ** Only the System Administrator (SA) or the Database Owner (dbo) can
    ** add an article to a publication.
    */
    exec @retcode = dbo.sp_MSreplcheck_publish
    if @@ERROR <> 0 or @retcode <> 0
        return(1)

    /*
    ** Parameter Check: @filtername.
    ** The join_filter_name cannot be NULL 
    */
    if @filtername is NULL
        begin
            raiserror (14043, 11, -1, @filtername)
            return (1)
        end

    if @join_filterclause is NULL or @join_filterclause = ''
        begin
            raiserror (14043, 11, -1, '@join_filterclause')
            return (1)
        end

    /*
    ** Parameter Check: @publication.
    ** The @publication id cannot be NULL and must conform to the rules
    ** for identifiers.
    */
    if @publication is NULL
        begin
            raiserror (14003, 16, -1)
            return (1)
        end

    
    /*
    ** Get the pubid and make sure the publication exists
    */
    select @pubid = pubid, @snapshot_ready=snapshot_ready from sysmergepublications 
        where name = @publication and UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name()
    if @pubid is NULL
        begin
            raiserror (20026, 16, -1, @publication)
            return (1)
        end

	if @snapshot_ready>0
		begin
			/*
			** make sure we know we really want to do this.
			*/
			if @force_invalidate_snapshot = 0
				begin
					raiserror(21366, 16, -1, @filtername)
					goto FAILURE
				end
			if @force_reinit_subscription = 0
				begin
					raiserror(21367, 16, -1, @filtername)
					goto FAILURE
				end

			update sysmergepublications set snapshot_ready=2 where pubid=@pubid
			if @@ERROR<>0
				goto FAILURE
			exec @retcode = sp_MSreinitmergepublication @publication
			if @retcode<>0 or @@ERROR<>0
				goto FAILURE

		end

    select @db_name = db_name from sysmergesubscriptions
        where (pubid=@pubid) and (subid=@pubid) 
        IF @db_name <> db_name()
        BEGIN
            RAISERROR (20047, 16, -1)
            RETURN (1)
        END        
    /*
    ** Parameter Check: @article.
    ** Check to see that the @article is valid and does exist 
    */
    if @article is NULL
        begin
            raiserror (20045, 16, -1)
            return (1)
        end
        
    select @artid = artid, @objid=objid, @vertical=vertical_partition, @art_nickname = nickname from sysmergearticles where name = @article and pubid = @pubid  
    if @artid is NULL
        begin
            raiserror (20046, 16, -1)
            return (1)
        end
        
    /*
    **  Get the id of the @join_articlename
    */
    select @join_objid = objid, @join_vertical= vertical_partition from sysmergearticles where name=@join_articlename and pubid = @pubid
    
    IF @join_objid is NULL
        BEGIN
            raiserror (14027, 11, -1, @join_articlename)
            return (1)
        END
        
	/* check the validity of join_filterclause */
	select @object = object_name(@objid)

	select @join_object = object_name(@join_objid)
	
	select @object_view = @object
	select @join_object_view = @join_object
			
	/* is current article vertically partitioned */
	if @vertical=1
	begin
		  select @object_view = 'OBJECT_VIEW_' + @object

		  select @qual_object_view=QUOTENAME(@object_view)
		  select @qual_object=QUOTENAME(@object)
		  
		  exec @retcode = sp_MSgetcolumnlist @pubid, @column_list OUTPUT, @objid
		  exec ('create view ' + @qual_object_view + ' as select ' + @column_list + ' from ' + @qual_object)
		  if @@ERROR<>0
		  	goto FAILURE
		  
	end

	/* is join article vertically partitioned */
	if @join_vertical=1
	begin
		  select @join_object_view = 'JOIN_OBJECT_VIEW_' + @join_object
		  select @qual_join_object_view = QUOTENAME(@join_object_view)
		  select @qual_join_object=QUOTENAME(@join_object)
		  
		  exec @retcode = sp_MSgetcolumnlist @pubid, @column_list OUTPUT, @join_objid
		  exec ('create view ' + @qual_join_object_view + ' as select ' + @column_list + ' from ' + @qual_join_object)
		  if @@ERROR<>0
		  begin
		  	exec('drop view ' + @qual_object_view)
		  	goto FAILURE
		  end
		 
	end

	select @qual_object_view=QUOTENAME(@object_view)
	select @qual_join_object_view = QUOTENAME(@join_object_view)
	select @qual_join_object=QUOTENAME(@join_object)
	select @qual_object=QUOTENAME(@object)

	exec ('declare @test int select @test=1 from ' + @qual_object_view + ' ' + @qual_object + ', ' + @qual_join_object_view + ' ' + @qual_join_object + ' where ' + @join_filterclause)
	if @@ERROR<>0
		begin
			if @vertical=1
				exec ('drop view ' + @qual_object_view)
			if @join_vertical=1
			 	exec ('drop view ' + @qual_join_object_view)
			raiserror(21256, 16, -1, @join_filterclause, @article)
			return (1)
		end
		
	if @vertical=1
		exec ('drop view ' + @qual_object_view)
	if @join_vertical=1
	 	exec ('drop view ' + @qual_join_object_view)
	
    select @join_nickname = nickname from sysmergearticles where pubid = @pubid AND objid = @join_objid 
    if @join_nickname is NULL
        begin
            raiserror (20001, 11, -1, @article, @publication)
            return (1)
        end

    IF NOT EXISTS (select * from sysmergearticles where pubid=@pubid AND nickname = @join_nickname)
        BEGIN
            RAISERROR (20046, 16, -1) /* Only the original publisher can do so */
            RETURN (1)
        END
    
    /*
    ** Make sure that the table name specified is a table and not a view.
    */
    
    if NOT exists (select * from sysobjects
        where id = @join_objid AND type = 'U')
        begin
            raiserror (14028, 16, -1)
            return (1)
        end

    /*
    **  Add the join filter to sysmergesubsetfilters if it is not already there
    */

    IF exists (select * from sysmergesubsetfilters 
    where filtername=@filtername and  pubid=@pubid and artid=@artid) 
        begin
            raiserror (20002, 16, -1, @filtername, @article, @publication)
            return (1)
        end
    
    insert INTO sysmergesubsetfilters(filtername, pubid, artid, art_nickname, join_articlename, join_nickname, join_unique_key, join_filterclause)
        values(@filtername, @pubid, @artid, @art_nickname, @join_articlename, @join_nickname, @join_unique_key, @join_filterclause)                 
    if @@error <> 0
        begin
            goto FAILURE
        end
    execute @retcode = dbo.sp_MSsubsetpublication @publication
    if @@ERROR <> 0 or @retcode <>0
        goto FAILURE
    
    return (0)
    
FAILURE:
    RAISERROR (20038, 16, -1, @article, @publication)
    return (1)

go
exec dbo.sp_MS_marksystemobject sp_addmergefilter
go

grant execute on dbo.sp_addmergefilter to public
go

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

create procedure sp_dropmergefilter
    @publication            sysname,            /* publication name */
    @article                sysname,            /* article name */
    @filtername             sysname,             /* Name of the table being joined to the base table */
    @force_invalidate_snapshot bit = 0
    AS

    set nocount on

    /*
    ** Declarations.
    */
    declare @pubid                  uniqueidentifier                
    declare @artid                  uniqueidentifier
    declare @join_objid             int
    declare @retcode                int
    declare @join_filterid          int
    declare @db_name                sysname
    declare @allow_anonymous		int
    declare @snapshot_ready			tinyint

	/* make sure current database is enabled for merge replication */
    exec @retcode=dbo.sp_MSCheckmergereplication
    if @@ERROR<>0 or @retcode<>0
    	return (1)

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

    /*
    ** Parameter Check: @publication.
    ** The @publication id cannot be NULL and must conform to the rules
    ** for identifiers.
    */
    
    if @publication is NULL
        begin
            raiserror (14003, 16, -1)
            return (1)
        end

    /*
    ** Get the pubid, and check if this publication exists.
    */
    select @pubid = pubid, @snapshot_ready=snapshot_ready, @allow_anonymous=allow_anonymous from sysmergepublications 
        where name = @publication and UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name()
    if @pubid is NULL
        begin
            raiserror (20026, 16, -1, @publication )
            return (1)
        end

    select @db_name = db_name from sysmergesubscriptions
        where (pubid=@pubid) and (subid=@pubid) 
        IF @db_name <> db_name()
        BEGIN
            RAISERROR (20047, 16, -1)
            RETURN (1)
        END
    
    /*
    ** Parameter Check: @article.
    ** Check to see that the @article is valid, and if it exists
    */
    if @article is NULL
        begin
            raiserror (20045, 16, -1)
            return (1)
        end

	if @snapshot_ready>0 and (@allow_anonymous=1 or 
		exists (select * from sysmergesubscriptions where pubid=@pubid and subid<>pubid and status=1))
        BEGIN
            RAISERROR (21372, 16, -1, @filtername, @publication)
            RETURN (1)
        END

     if @snapshot_ready>0
		begin
			if @force_invalidate_snapshot = 0
				begin
					raiserror(21382, 16, -1, @filtername)
					return (1)
				end
			update sysmergepublications set snapshot_ready=2 where pubid=@pubid
			if @@ERROR<>0
				return (1)
		end
   
       
    select @artid = artid from sysmergearticles where name = @article and pubid = @pubid  
    if @artid is NULL
        begin
            raiserror (20046, 16, -1)
            return (1)
        end
    

    select @join_filterid = join_filterid from sysmergesubsetfilters
        where pubid = @pubid AND artid= @artid AND filtername=@filtername  
    
    /*
    **  Remove the join filter from sysmergesubsetfilters
    */
    delete from sysmergesubsetfilters
        where join_filterid = @join_filterid
    if @@error <> 0
        begin
            goto FAILURE
        end

    /*
    ** set the pub type to subset or full as appropriate
    */
    exec @retcode=sp_MSsubsetpublication @publication
    if @@ERROR <> 0 or @retcode<>0
        begin
            goto FAILURE
        end

    return(0)
FAILURE:
    RAISERROR (20039, 16, -1, @article, @publication)
    return (1)
go

exec dbo.sp_MS_marksystemobject sp_dropmergefilter
go
grant execute on dbo.sp_dropmergefilter to public
go

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

create procedure sp_helpmergefilter
    @publication            sysname,        /* publication name */
    @article                sysname = '%',          /* article name */ 
    @filtername             sysname = '%'
    AS

    set nocount on

    /*
    ** Declarations.
    */
    declare @pubid                  uniqueidentifier                
    declare @artid                  uniqueidentifier
    declare @retcode                int

    /* 
    ** No security checking is needed for sp_help??
    */


    /*
    **  Calling sp_help* is all right whether current database is enabled for pub/sub or not
    */
    IF not exists (select * from sysobjects where name='sysmergesubscriptions')
        RETURN (0)
        
    /*
    ** Parameter Check: @publication.
    ** The @publication id cannot be NULL and must conform to the rules
    ** for identifiers.
    */
    if @publication is NULL
        begin
            raiserror (14003, 16, -1)
            return (1)
        end
    /*
    ** Get the pubid and check if the publication does exist
    */
    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, 16, -1, @publication)
            return (1)
        end

    /*
    ** Parameter Check: @article.
    ** If an @article is specified, make sure it exists
    */
    select @artid = artid from sysmergeextendedarticlesview where name = @article and pubid = @pubid  
    if @artid is NULL and (@article <> '%' or @filtername <> '%')
        begin
            raiserror (20046, 16, -1)
            return (1)
        end

    /*
    **  Return the join filters from sysmergesubsetfilters
    */

IF @filtername <> '%'
    select distinct join_filterid, filtername, 'join article name' = j_a.name, join_filterclause, f.join_unique_key,
            'base table owner' = b_u.name, 'base table name' = b_o.name, 'join table owner' = j_u.name, 'join table name' = j_o.name,
            'article name' = b_a.name
        from sysmergesubsetfilters f, sysobjects j_o, sysobjects b_o, sysusers j_u,
            sysusers b_u, sysmergeextendedarticlesview j_a, sysmergeextendedarticlesview b_a
        where   f.pubid = @pubid AND b_a.pubid = @pubid
            AND f.filtername = @filtername
            AND f.artid = @artid 
            AND f.artid = b_a.artid AND b_o.id = b_a.objid
            AND b_u.uid = b_o.uid
            AND f.join_nickname = j_a.nickname AND j_o.id = j_a.objid and j_a.pubid = @pubid
            AND j_u.uid = j_o.uid
           ORDER BY j_o.name, b_o.name
 ELSE
    begin
    if @artid is not null
    select distinct join_filterid, filtername, 'join article name' = j_a.name, join_filterclause, f.join_unique_key,
            'base table owner' = b_u.name, 'base table name' = b_o.name, 'join table owner' = j_u.name, 'join table name' = j_o.name,
            'article name' = b_a.name
        from sysmergesubsetfilters f, sysobjects j_o, sysobjects b_o, sysusers j_u,
            sysusers b_u, sysmergeextendedarticlesview j_a, sysmergeextendedarticlesview b_a
        where   f.pubid = @pubid AND b_a.pubid = @pubid
            AND f.artid = @artid 
            AND f.artid = b_a.artid AND b_o.id = b_a.objid
            AND b_u.uid = b_o.uid
            AND f.join_nickname = j_a.nickname AND j_o.id = j_a.objid and j_a.pubid = @pubid
            AND j_u.uid = j_o.uid
           ORDER BY j_o.name, b_o.name
    else
    select distinct join_filterid, filtername, 'join article name' = j_a.name, join_filterclause, f.join_unique_key,
            'base table owner' = b_u.name, 'base table name' = b_o.name, 'join table owner' = j_u.name, 'join table name' = j_o.name,
            'article name' = b_a.name
        from sysmergesubsetfilters f, sysobjects j_o, sysobjects b_o, sysusers j_u,
            sysusers b_u, sysmergeextendedarticlesview j_a, sysmergeextendedarticlesview b_a
        where   f.pubid = @pubid AND b_a.pubid = @pubid
            AND f.artid = b_a.artid AND b_o.id = b_a.objid
            AND b_u.uid = b_o.uid
            AND f.join_nickname = j_a.nickname AND j_o.id = j_a.objid and j_a.pubid = @pubid
            AND j_u.uid = j_o.uid
           ORDER BY j_o.name, b_o.name
    end
    return(0)
go
exec dbo.sp_MS_marksystemobject sp_helpmergefilter
go

grant execute on dbo.sp_helpmergefilter to public
go

raiserror('Creating procedure sp_MSscript_dri', 0,1)
go
create procedure sp_MSscript_dri
    (@publication   sysname, 
    @article        sysname)
AS

declare @pubid  uniqueidentifier
    
select @pubid = pubid from sysmergepublications where name = @publication and UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name()
        
-- Security check
if (1 <> {fn ISPALUSER(@pubid)})
begin    
	RAISERROR (15247, 11, -1)
	return (1)
end

select rkeyid, fkeyid from sysreferences 
        where fkeyid in (select objid from sysmergearticles where pubid = @pubid and name = @article) 
            and rkeyid not in (select objid from sysmergearticles   where pubid = @pubid)
go
exec dbo.sp_MS_marksystemobject sp_MSscript_dri
go
grant execute on dbo.sp_MSscript_dri to public
go


raiserror('Creating procedure sp_MSenumpubreferences', 0,1)
GO
create procedure sp_MSenumpubreferences (@publication sysname)
as
    declare @pubid uniqueidentifier
    declare @retcode int
    
    /* 
    ** Security Check.
    */
    exec @retcode = dbo.sp_MSreplcheck_publish
    if @@ERROR <> 0 or @retcode <> 0
        return(1)

	/* make sure current database is enabled for merge replication */
    exec @retcode=dbo.sp_MSCheckmergereplication
    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, 16, -1, @publication)
            RETURN (1)
        END
    select distinct ReferencingObject = object_name(rkeyid), ArticleObject  = object_name(fkeyid) 
        from sysreferences r, sysmergearticles
        where r.fkeyid in (select objid from sysmergearticles where pubid = @pubid) 
            and r.rkeyid not in (select objid from sysmergearticles where pubid = @pubid)

    select distinct ReferencedObject = object_name(fkeyid), ArticleObject = object_name(rkeyid) from sysreferences r
        where r.rkeyid in (select objid from sysmergearticles
            where pubid = @pubid) and
        r.fkeyid not in (select objid from sysmergearticles
            where pubid = @pubid)       

    -- patch. We need to check if the publication role is already added.
    -- this has to be done here before the snapshot agent calls sp_Mspublicationview
    declare @role sysname
    exec @retcode = sp_MSrepl_FixPALRole @pubid, @role output
    if (@role is NULL or @retcode <> 0 or @@error <> 0)
    begin
        -- add appropriate error message here
        return 1
    end

    -- we also need to fix the pal role for other publications which share an article
    -- with the current publication and the role for those publications are not
    -- present yet
    declare @pubidother uniqueidentifier
    declare pubs_cursor cursor LOCAL FAST_FORWARD
    for (select a.pubid from dbo.sysmergearticles a, dbo.sysmergepublications p
            where a.pubid <> @pubid and a.pubid = p.pubid 
                and LOWER(p.publisher) collate database_default = LOWER(@@servername) collate database_default and p.publisher_db = DB_NAME() and artid in 
                 (select artid from dbo.sysmergearticles where pubid = @pubid)) 
    open pubs_cursor
    fetch pubs_cursor into @pubidother
    while (@@fetch_status <> -1)
    begin
        exec @retcode = sp_MSrepl_FixPALRole @pubidother, @role output
        if (@role is NULL or @retcode <> 0 or @@error <> 0)
        begin
            return 1
        end
        fetch pubs_cursor into @pubidother
    end
    close pubs_cursor
    deallocate pubs_cursor
    
    return (0)
go

exec dbo.sp_MS_marksystemobject sp_MSenumpubreferences
go

grant execute on dbo.sp_MSenumpubreferences to public
go
 
raiserror('Creating procedure sp_MSsubsetpublication', 0,1)
GO

create procedure sp_MSsubsetpublication (@publication sysname)
as
    declare @pubid          uniqueidentifier
    declare @false          bit
    declare @true           bit
    declare @boolean_filter bit
    declare @join_filter    bit
    declare @full           int
    declare @subset         int
    declare @unsynced       int

    /*
    ** Initializations
    */
    select @true        = 1
    select @false       = 0
    select @full        = 0     /* Const: publication type 'full' */
    select @subset      = 1     /* Const: publication type 'subset' */
    select @unsynced    = 1
    
    if not exists (select * from sysobjects where name = 'sysmergepublications')
        BEGIN
            RAISERROR (20054, 16, -1)
            RETURN (1)
        END 

    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, 16, -1, @publication)
            RETURN (1)
        END

    /* 
    ** Set the publication_type to subset if the publication has either a boolean or a join filter
    */
    if exists (select * from sysmergearticles where pubid = @pubid and len(subset_filterclause) > 0)
        set @boolean_filter = @true
    if exists (select * from sysmergesubsetfilters where pubid = @pubid)
        set @join_filter = @true

    /*
    ** For subset publications set the article status to be unsynced so that the triggers can be regenerated.
    */
    if (@boolean_filter = 1 OR @join_filter = 1)        
        begin
            update sysmergepublications set publication_type = @subset where pubid = @pubid
            if @@ERROR <> 0 return (1)
            update sysmergearticles set status = @unsynced where pubid = @pubid
            if @@ERROR <> 0 return (1)
        end         
    else
        update sysmergepublications set publication_type = @full where pubid = @pubid               
    if @@ERROR <> 0 return (1)
        
    return(0)           
go

exec dbo.sp_MS_marksystemobject sp_MSsubsetpublication
go
raiserror('Creating procedure sp_MSindexcolfrombin', 0,1)
GO

create procedure sp_MSindexcolfrombin
    @object_id      int,
    @col_index      int,
    @colids_bin     varbinary(256),
    @colname        sysname output,
    @columns		varbinary(128) = NULL
    AS
    /* Declare variables */
    declare @start_byte int
    declare @colid int
	declare @retcode int
    set @colid = unicode( substring( convert( nvarchar(128),@colids_bin ), @col_index, 1 ) )
	select @colname = QUOTENAME(name) from syscolumns where id = @object_id and colid = @colid

	/* vertical partitioning is ON */
	if @columns is not NULL
	begin
		/* see if this column is currently in the vertical partitioning */
		exec @retcode = sp_MStestbit @bm=@columns, @coltotest=@colid
		if @retcode=0
	    	select @colname = NULL
	end
    return (0)
GO
exec dbo.sp_MS_marksystemobject sp_MSindexcolfrombin
go

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

create procedure sp_MSmakejoinfilter
    @publication    sysname,
    @article        sysname,
    @base_objid     int,
    @join_objid     int,
    @join_unique    int
    AS

    /* Declare additional variables */
    declare     @table_name nvarchar(140)
    declare     @join_table nvarchar(140)
    declare     @join_article sysname
    declare     @filt_name  sysname
    declare     @basecol    sysname
    declare     @joincol    sysname
    declare     @keycnt     int
    declare     @basekeys   varbinary(32)
    declare     @joinkeys   varbinary(32)
    declare 	@base_columns	varbinary(128)
    declare 	@join_columns	varbinary(128)
    declare     @keyindex   int
    declare     @filtclause nvarchar(3000)
    declare     @filtpiece  nvarchar(500)
    declare     @qual_jointable nvarchar(270)
    declare     @retcode    smallint
    declare     @pubid      uniqueidentifier
    declare 	@first_piece bit

    select @pubid = pubid from sysmergepublications where name = @publication and UPPER(publisher)=UPPER(@@SERVERNAME) and publisher_db=db_name()

    select @base_columns=0x00 -- so that no base column name will be returned if following query does not find a match
    select @join_columns=0x00 -- same as above
    
	select @base_columns=columns from sysmergearticles where pubid=@pubid and objid=@base_objid
	select @join_columns=columns from sysmergearticles where pubid=@pubid and objid=@join_objid
	
    /* Are we adding join filter on referencing table (@join_unique = 1) or on unique key table ? */
    if @join_unique = 1
    /* Select the keycnt, fkeys, rkeys, and filter name from sysreferences */
        select @keycnt = keycnt, @basekeys = forkeys, @joinkeys = refkeys, @filt_name = object_name(constid)
            from sysreferences where fkeyid = @base_objid and rkeyid = @join_objid
    else
        select @keycnt = keycnt, @basekeys = refkeys, @joinkeys = forkeys, @filt_name = object_name(constid)
            from sysreferences where rkeyid = @base_objid and fkeyid = @join_objid
    
    /* Set up object names - we use them as correlation values */
    set @table_name = QUOTENAME(object_name(@base_objid))
    set @join_table = QUOTENAME(object_name(@join_objid))
    select @join_article = name from sysmergearticles where objid = @join_objid and pubid=@pubid

    -- set @qual_jointable = @join_owner + '.' + @join_table

    /* Loop over keys, building up our join filter clause */
    set @keyindex = 1
    set @first_piece = 0
    while @keyindex <= @keycnt
        begin
        /* Get the column names */
        
		/* also pass in vertical partitioning binary to excluded columns that are not in the current partition */
        exec dbo.sp_MSindexcolfrombin @base_objid, @keyindex, @basekeys, @basecol output, @base_columns
        if @@ERROR<>0 return (1)
        exec dbo.sp_MSindexcolfrombin @join_objid, @keyindex, @joinkeys, @joincol output, @join_columns
        if @@ERROR<>0 return (1)

		if @basecol is not NULL and @joincol is not NULL
		begin
        	/* Make the piece of predicate pertaining to this key column */
	        set @filtpiece = @table_name + '.' + @basecol + ' = ' + @join_table + '.' + @joincol
    	    
        	/* If first time through, initialize clause, else add to it */
	        if @first_piece=0
	        begin
	        	set @first_piece=1
    	        set @filtclause = @filtpiece
			end
        	else
            	set @filtclause = @filtclause + ' and ' + @filtpiece
        end
        /* move on to the next key */
        set @keyindex = @keyindex + 1
        end

	/* no filter generated due to vertical partitioning */
	if @first_piece>0
	begin
	    /* Add the join filter */
    	exec @retcode = dbo.sp_addmergefilter @publication, @article, @filt_name, @join_article, @filtclause, @join_unique
	    if @@ERROR<>0 or @retcode<>0 return (1)
	end

    return (0)
    GO

exec dbo.sp_MS_marksystemobject sp_MSmakejoinfilter
go

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

create procedure sp_MSmakeexpandproc
    @pubname        sysname,
    @filterid       int,
    @procname       sysname
    AS
    /* Declare additional variables */
    declare @pubid  uniqueidentifier
    declare @base_nick int
    declare @join_nick int
    declare @base_nickstr nvarchar(10)
    declare @join_nickstr nvarchar(10)
    declare @filterid_str nvarchar(10)
    declare @base_objid int
    declare @join_objid int
    declare @base_table nvarchar(270)
    declare	@before_viewname	nvarchar(270)
    declare @join_table nvarchar(270)
    declare @base_owner nvarchar(270)
    declare @join_owner nvarchar(270)
    declare @join_clause nvarchar(4000)
    declare @retcode    int
    declare @must_check int
    declare @view_type  int
    declare @guidcolname    nvarchar(270)
    declare @joinguidname 	nvarchar(270)
    declare @view_objid int
    declare @view_name  nvarchar(270)
    declare @cmd_piece  nvarchar(4000)

	-- Security check
	if 1 <> is_member('db_owner')
	begin
		RAISERROR (15247, 11, -1)
		return 1
	end

    select @pubid = pubid from sysmergepublications where name = @pubname and UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name()

    -- this procedure is to be called by xp_execresultset, so
    -- we create a temp table, put command pieces into it, and select them out
    
    -- create temp table to select the command text out of
    create table #tempcmd (step int identity NOT NULL, cmdtext nvarchar(4000) collate database_default null)

    /* Figure out base table, join table for this join filter */
    select @base_nick = art_nickname, @join_nick = join_nickname,
        @join_clause = join_filterclause
        from sysmergesubsetfilters where pubid = @pubid and join_filterid = @filterid
    select @base_objid = objid, @view_type = view_type, @view_objid = sync_objid from sysmergearticles where pubid = @pubid and nickname = @base_nick
    select @join_objid = objid, @before_viewname = object_name(before_view_objid)  from sysmergearticles where pubid = @pubid and nickname = @join_nick
    select @base_table = QUOTENAME(name), @base_owner = QUOTENAME(user_name(uid)) from sysobjects where id = @base_objid
    select @join_table = QUOTENAME(name), @join_owner = QUOTENAME(user_name(uid)) from sysobjects where id = @join_objid
    select @guidcolname = name from syscolumns where id = @base_objid and 
            columnproperty (id, name, 'isrowguidcol')=1
    select @joinguidname = name from syscolumns where id = @join_objid and 
            columnproperty (id, name, 'isrowguidcol')=1
    select @view_name = object_name(@view_objid)
    -- Quote the viewname.  It is made from pub name which may have odd characters.
    set @view_name = QUOTENAME(@view_name)
    set @base_nickstr = convert(nvarchar(10), @base_nick)
    set @join_nickstr = convert(nvarchar(10), @join_nick)
    set @filterid_str = convert(nvarchar(10), @filterid)
    
    set @cmd_piece = 'create procedure dbo.' + @procname + ' 
        @belong int 
        AS 
		declare @command nvarchar(4000), @retcode int  '
    insert into #tempcmd(cmdtext) values (@cmd_piece)
    set @cmd_piece = '
		if @belong = 1
        begin
            /* Do a bulk insert to expand #belong */
            select @command = N''update #belong set flag = ' + @filterid_str + ' where flag < ' + @filterid_str + '''
            exec @retcode = sp_executesql @command
            if @@error <> 0 or @retcode <> 0 return 1'
    insert into #tempcmd(cmdtext) values (@cmd_piece)
    set @cmd_piece =  'insert into #belong (tablenick, rowguid, flag, skipexpand) select distinct ' + @base_nickstr +
            ', ' + @base_table + '.rowguidcol, 0, 0 from ' + @base_owner + '.' + @base_table + ', ' +
            @join_owner + '.' + @join_table + ', #belong b where (' + @join_clause + ') and ' + 
            @join_table + '.rowguidcol = b.rowguid and b.tablenick = ' + @join_nickstr + ' and skipexpand = 0 '
       
    insert into #tempcmd(cmdtext) values (@cmd_piece)
    set @cmd_piece = '  
        /* Delete duplicates */
			select @command = N''delete from #belong where skipexpand = 1 and rowguid in
				(select rowguid from #belong where flag = 0)
			delete from #belong where flag = 0 and rowguid in
	            (select rowguid from #belong where flag <> 0)''
			exec @retcode = sp_executesql @command
			if @@error <> 0 or @retcode <> 0 return 1
        end '
    insert into #tempcmd(cmdtext) values (@cmd_piece)
        
    /* Will we have to check rows that we add to #notbelong? */
    if exists (select * from sysmergearticles where pubid = @pubid and nickname = @join_nick and
        len(subset_filterclause) > 0)
        set @must_check = 1
    else if exists (select * from sysmergesubsetfilters where pubid = @pubid and join_filterid = @filterid and
        join_unique_key <> 1)
        set @must_check = 1
    else if not exists (select * from sysmergesubsetfilters where pubid = @pubid and  art_nickname = @base_nick
            and join_filterid <> @filterid)
        set @must_check = 0
    
    set @cmd_piece = '
        else 
        begin
			select @command = N''update #notbelong set flag = ' + @filterid_str + ' where flag < ' + @filterid_str + ''' 
			exec @retcode = sp_executesql @command
			if @@error <> 0 or @retcode <> 0 return 1'
    insert into #tempcmd(cmdtext) values (@cmd_piece)
    
    set @cmd_piece = ' /* Do a bulk insert to expand #notbelong */
			insert into #notbelong (tablenick, rowguid, flag) select distinct ' + @base_nickstr +
				', ' + @base_table + '.rowguidcol, -1 from ' + @base_owner + '.' + @base_table + ', ' +
				@join_owner + '.' + @join_table + ', #notbelong nb where (' + @join_clause + ') and ' + 
				@join_table + '.rowguidcol = nb.rowguid and nb.tablenick = ' + @join_nickstr
				           
    insert into #tempcmd(cmdtext) values (@cmd_piece)
	/* Remove duplicates */
		
	set @cmd_piece = 'select @command = N''delete from #notbelong where flag = -1 and rowguid in
		(select rowguid from #notbelong where flag <> -1)''
		exec @retcode = sp_executesql @command
		if @@error <> 0 or @retcode <> 0 return 1'
	insert into #tempcmd(cmdtext) values (@cmd_piece)
	
	if @before_viewname  is not null
	begin
		set @cmd_piece = '
			insert into #notbelong (tablenick, rowguid, flag) select distinct ' + @base_nickstr +
	            ', ' + @base_table + '.rowguidcol, -1 from ' + @base_owner + '.' + @base_table + ', ' +
				@before_viewname + ' ' + @join_table + ', #notbelong nb where (' + @join_clause + ') and ' + 
				@join_table + '.' + @joinguidname + ' = nb.rowguid and nb.tablenick = ' + @join_nickstr 
		insert into #tempcmd(cmdtext) values (@cmd_piece)
			/* Remove duplicates */
		
		set @cmd_piece = 'select @command = N''delete from #notbelong where flag = -1 and rowguid in
                (select rowguid from #notbelong where flag <> -1)''
            exec @retcode = sp_executesql @command
			if @@error <> 0 or @retcode <> 0 return 1'
		insert into #tempcmd(cmdtext) values (@cmd_piece)
	end
    
    if @must_check = 0
    begin
        set @cmd_piece = ' 
			select @command = N''update #notbelong set flag = 0 where flag = -1 ''
			exec @retcode = sp_executesql @command
			if @@error <> 0 or @retcode <> 0 return 1
        end '
        insert into #tempcmd(cmdtext) values (@cmd_piece)
    end
    else if @view_type = 1
    begin
        set @cmd_piece =  '     -- We can do our check with a bulk delete, bulk update
            select @command = N''delete from #notbelong where flag = -1 and rowguid in
                (select ' + @guidcolname + ' from ' + @view_name + ')
            update #notbelong set flag = 0 where flag = -1''
            exec @retcode = sp_executesql @command
			if @@error <> 0 or @retcode <> 0 return 1	
        end '
        insert into #tempcmd(cmdtext) values (@cmd_piece)
    end
    -- else we don't bother expanding #notbelong for this filter since there are cyclic
    -- join filters and this is not a unique key join.  The cursored calls to sp_belongs
    -- are unacceptably slow, and there would still be cases where orphaned rows could occur.

-- Now we select out the command text pieces in proper order so that our caller,
-- xp_execresultset will execute the command that creates the stored procedure.

select cmdtext from #tempcmd order by step

GO
exec dbo.sp_MS_marksystemobject sp_MSmakeexpandproc
go
grant execute on dbo.sp_MSmakeexpandproc to public
go
raiserror('Creating procedure sp_MSdrop_expired_mergesubscription', 0,1)
GO

create procedure sp_MSdrop_expired_mergesubscription
AS
/*
** This stored procedure is to periodically check the status of all the subscriptions 
** of every merge publication. If any of them is out-of-date, i.e., has lost contact
** with publisher for a certain length of time, we can declare the death of that replica
** and cleanup their traces at the publisher side
*/
declare @subscription_type  int
declare @sub_type           nvarchar(5)
declare @publication        sysname
declare @pubid              uniqueidentifier
declare @subid              uniqueidentifier
declare @status             tinyint
declare @subscriber         sysname
declare @subscriber_id      int
declare @subscriber_db      sysname
declare @publisher         	sysname
declare @publisher_db       sysname
declare @retention          int  -- in days         
declare @retcode            smallint
declare @last_history_logged       datetime
declare @twice_retention_period   	datetime
declare @min_valid_day    			datetime
declare @distribdb 					sysname
declare @rpcsrvname 				sysname
declare @distributor 				sysname
declare @distproc 					nvarchar(4000)

/*
** Security Check
*/
    EXEC @retcode = dbo.sp_MSreplcheck_publish
    IF @@ERROR <> 0 or @retcode <> 0
        return (1)

/* we do not check return code here because we can tolerate any failure here */
exec @retcode = dbo.sp_MScleanup_conflict_table
if @@ERROR<>0 or @retcode<>0
	return (1)

-- Get distributor information for RPC
exec @retcode = sp_helpdistributor @distributor = @distributor output,
                                   @distribdb = @distribdb output,
                                   @rpcsrvname = @rpcsrvname output
if @@error <> 0 or @retcode <> 0
    return (1)

select @distproc = rtrim(@rpcsrvname) + N'.' + @distribdb + N'.' + N'dbo.sp_MSgetlasthistorytimestamp'


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 and p.snapshot_ready=1 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 @twice_retention_period = dateadd(day, -@retention * 2, getdate())
			select @min_valid_day = dateadd(day, -@retention, getdate())
        	if @retention is not NULL and @retention > 0
        	begin
        		-- this is to reomve any user script (file and directory) when following two points fulfills
        		-- 1. a snapshot has been run to pick it up
        		-- 2. the snapshot is too old that no new subscription need this file
        		exec @retcode = sp_MSremove_userscript @pubid
        		if @@ERROR<>0 or @retcode<>0
        		begin
					close PC
					deallocate PC
    				return (1)
        		end
           	 	declare SC CURSOR LOCAL FAST_FORWARD for select srvid, db_name, subid, status, subscription_type from sysmergesubscriptions where 
            	    pubid = @pubid and pubid<>subid for read only
           		open SC
            	fetch SC into @subscriber_id, @subscriber_db, @subid, @status, @subscription_type
            	WHILE (@@fetch_status <> -1)
                	BEGIN
                    	select @subscriber=srvname from master..sysservers where srvid=@subscriber_id
	                    if @subscription_type = 0 select @sub_type = 'push' else select @sub_type = 'pull'
						select @publisher = publisher, @publisher_db = publisher_db from sysmergepublications where pubid = @pubid

					    exec @retcode = @distproc 
						    @publisher = @publisher,
						    @publisher_db = @publisher_db,
						    @publication = @publication,
						    @subscriber = @subscriber,
						    @subscriber_db = @subscriber_db,
					    	@last_history_logged = @last_history_logged OUTPUT
                   		if @retcode <>0 or @@ERROR<>0
                           	goto FAILURE
	
 	                   if @status <> 2 and @last_history_logged < @min_valid_day
    	                begin
        	                exec @retcode = dbo.sp_dropmergesubscription  
            	                    @publication = @publication,
                	                @subscriber = @subscriber,
                    	            @subscriber_db = @subscriber_db,
                        	        @subscription_type = @sub_type  
                       		if @retcode <>0 or @@ERROR<>0
                            	goto FAILURE
							raiserror(14157, 10, -1, @subscriber, @publication) 
						end

						/* 
						** Remove global subscriptions after twice the retention period has passed
						** This is to prevent global subscribers from being resurrected.
						*/
                    	if @last_history_logged < @twice_retention_period 
                    	begin
                        	delete from sysmergesubscriptions where subid = @subid --delete the row in sysmergesubscription
               		        if @@ERROR<>0
	                            goto FAILURE
    	                    delete from MSmerge_replinfo where repid = @subid
        	                if @@ERROR<>0
            	                goto FAILURE
                	    end     
	                    fetch SC into @subscriber_id, @subscriber_db, @subid, @status, @subscription_type
    	            END
        	    CLOSE SC
            	DEALLOCATE SC
            end
            fetch PC into @publication, @pubid, @retention
        END
    CLOSE PC
    DEALLOCATE PC
	return (0)
    
FAILURE:
    close SC
    deallocate SC
    close PC
    deallocate PC
    return (1)

GO
exec dbo.sp_MS_marksystemobject sp_MSdrop_expired_mergesubscription
go

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

create procedure sp_MScleanup_metadata
@pubid			uniqueidentifier
AS
declare @retcode 				int
declare @truncate_flag  		int 
declare @pubid2					uniqueidentifier
declare @artid                  uniqueidentifier
declare @schematype             int
declare @schemaversion          int
declare @schemaguid             uniqueidentifier
declare @schematext             nvarchar(2000)
declare @rcount					int

set nocount on
/*
** Security Check
*/
EXEC @retcode = dbo.sp_MSreplcheck_publish
IF @@ERROR <> 0 or @retcode <> 0
	return (1)
	
if sessionproperty('replication_agent') <> 0
	begin
	-- called from Merge Agent.  If we are republishing, must add this command for
	-- any publications which are republishing data from the original publication.

	declare #per_publication CURSOR LOCAL FAST_FORWARD for select pubid
		from sysmergepublications p where LOWER(publisher)=LOWER(@@SERVERNAME) and 
				publisher_db=db_name() and 
			exists (select * from sysmergearticles a1, sysmergearticles a2 where
					a1.pubid = p.pubid and a2.pubid = @pubid and a1.objid = a2.objid)
	open #per_publication		
	fetch #per_publication into @pubid2
	while @@fetch_status<>-1
		begin
       	select @schemaversion = 1 + max(schemaversion) from sysmergeschemachange
        if (@schemaversion is NULL)
   	    	set @schemaversion = 1
        set @schemaguid = newid()
   	    set @artid = newid()
       	set @schematype = 16 /* metadata cleanup */
       	select @schematext = 'exec dbo.sp_MScleanup_metadata '+ '''' + convert(nchar(36),@pubid2) + '''' 
		exec @retcode=sp_MSinsertschemachange @pubid2, @artid, @schemaversion, @schemaguid, @schematype, @schematext
        if @@ERROR<>0 or @retcode<>0 
            begin
			close #per_publication
			deallocate #per_publication
			return (1)
          
    	    end
	
		fetch next from #per_publication into @pubid2
		end
	close #per_publication
	deallocate #per_publication
  	end
else
  	begin
  	-- should be nested call from sp_mergecleanupmetadata and we should be the
  	-- publisher of this publication
	if @pubid is not null and not exists (select * from sysmergepublications where
		LOWER(publisher)=LOWER(@@SERVERNAME) and publisher_db=db_name() and pubid = @pubid)
		begin
		return (1)
		end
  	end

-- Figure out whether there are any publications which prevent us from truncating the tables
if @pubid is null
	begin
	if exists (select * from sysmergepublications where
		LOWER(publisher)<> LOWER(@@SERVERNAME) or publisher_db<>db_name())
		set @truncate_flag = 0
	else
		set @truncate_flag = 1
	end
else
	begin
	if exists (select * from sysmergepublications p where p.pubid <> @pubid and
		(LOWER(publisher)<> LOWER(@@SERVERNAME) or publisher_db<>db_name() or
		not exists (select * from sysmergearticles a1, sysmergearticles a2 where
			a1.pubid = p.pubid and a2.pubid = @pubid and a1.objid = a2.objid)))
		set @truncate_flag = 0
	else
		set @truncate_flag = 1	
	end


if @pubid is not null
	begin
		/* If there are any inprocess generations inform user that queisce did not succeed - reinit required  */
		if exists (select top 1 guidsrc from dbo.MSmerge_genhistory h
			where	guidlocal='00000000-0000-0000-0000-000000000000'  -- incomplete gen
				and generation not in (select gen_cur from sysmergearticles)  -- not a local incomplete gen
				and coldate in (select login_time from master..sysprocesses)  -- not a gen that currently receives replica updates from another db
				and h.art_nick in (select nickname from sysmergearticles where pubid = @pubid)) -- generations relevant to current publication 
			begin
				declare @dbname sysname
				set @dbname = db_name()
				RAISERROR(21504, 16, -1, @@servername, @dbname)
				return (1)
			end
	end
if @truncate_flag = 1
	begin
	truncate table MSmerge_contents
	truncate table MSmerge_tombstone
	truncate table MSmerge_genhistory
	update sysmergearticles set gen_cur = NULL
	update MSmerge_replinfo set recgen = NULL, recguid = NULL, sentgen = NULL, sentguid = NULL 
	end
else
	begin
	-- set up temp table of article nicknames that we delete for
	create table #artnicks (nickname int)
	if @pubid is null
		begin
		-- insert pubids for all local publications
		insert into #artnicks select distinct nickname from sysmergearticles where
			pubid in (select pubid from sysmergepublications where
				LOWER(publisher)=LOWER(@@SERVERNAME) and publisher_db=db_name())
		end
	else
		begin
		insert into #artnicks select distinct nickname from sysmergearticles where
			pubid = @pubid
		end
	
		
	-- do deletions in batches of 5000 to avoid excessive log growth
	
	set rowcount 5000
	set @rcount = 1
	while @rcount > 0
		begin
		set @rcount = 0

		-- update our cumulative count of rows deleted in this pass with each table
		delete MSmerge_contents WITH (PAGLOCK) from MSmerge_contents where tablenick in (select nickname from #artnicks)
		set @rcount = @@rowcount + @rcount
		delete MSmerge_tombstone WITH (PAGLOCK) from MSmerge_tombstone where tablenick in (select nickname from #artnicks)
		set @rcount = @@rowcount + @rcount
		delete MSmerge_genhistory WITH (PAGLOCK) from MSmerge_genhistory where art_nick in (select nickname from #artnicks)
		set @rcount = @@rowcount + @rcount
		end
	
	update sysmergearticles set gen_cur = NULL where nickname in (select nickname from #artnicks)

	if @pubid is not null
		update MSmerge_replinfo set recgen = NULL, recguid = NULL, sentgen = NULL, sentguid = NULL 
			where repid in ( select subid from sysmergesubscriptions where pubid = @pubid)
	else
		update MSmerge_replinfo set recgen = NULL, recguid = NULL, sentgen = NULL, sentguid = NULL 
			where repid in ( select subid from sysmergesubscriptions s, sysmergepublications p
				where s.pubid = p.pubid and LOWER(p.publisher)=LOWER(@@SERVERNAME) and p.publisher_db=db_name())
	
	drop table #artnicks
	dbcc dbreindex ( MSmerge_contents )
	end

execute @retcode = dbo.sp_MSmakegeneration 
if @@ERROR <> 0 or @retcode <> 0
	return (1)
return 0
 
GO
exec dbo.sp_MS_marksystemobject sp_MScleanup_metadata
go
grant execute on dbo.sp_MScleanup_metadata to public
go


raiserror('Creating procedure sp_helpmergecleanupwait', 0,1)
GO
create procedure sp_helpmergecleanupwait
AS
    declare @retcode int
    
    /*
    ** Security Check
    */
    EXEC @retcode = dbo.sp_MSreplcheck_publish
    IF @@ERROR <> 0 or @retcode <> 0
    	return (1)
	
	-- Tell user if we don't think we are waiting!
	if not exists (select * from sysmergesubscriptions where status = 7)
		raiserror(21507, 0,1)

	if exists (select * from sysmergepublications p, sysmergesubscriptions s
			where LOWER(publisher)=LOWER(@@SERVERNAME) and publisher_db=db_name() and
				p.pubid = s.pubid and s.status = 7 and s.subid <> s.pubid)
		begin
		raiserror(21509, 0,1)
		select publication, srvid, db_name from sysmergesubscriptions where status = 7
		end
	else if exists (select * from MSmerge_genhistory, master..sysprocesses where guidlocal = '00000000-0000-0000-0000-000000000000' and
			coldate = login_time)
		begin
		raiserror(21508, 0,1)
		select hostname, program_name from master..sysprocesses where login_time in
			(select coldate from MSmerge_genhistory where guidlocal = '00000000-0000-0000-0000-000000000000')
		end
	else
		exec sp_MSquiescecheck
	
GO

grant execute on sp_helpmergecleanupwait to public
GO


exec dbo.sp_MS_marksystemobject sp_helpmergecleanupwait
go
raiserror('Creating procedure sp_MSquiescecheck', 0,1)
GO

create procedure sp_MSquiescecheck
AS
	declare @mysrvid		int
	declare @pubid			uniqueidentifier
	declare @retcode		int

	set nocount on

	-- If we are not waiting to quiesce, do nothing more
	if not exists (select * from sysmergesubscriptions where status = 7)
		return 0

	-- If we publish to any republishers who are still waiting to
	-- quiesce (status = 7) then just return for now.
	if exists (select * from sysmergepublications p, sysmergesubscriptions s
			where LOWER(publisher)=LOWER(@@SERVERNAME) and publisher_db=db_name() and
				p.pubid = s.pubid and s.status = 7 and s.subid <> s.pubid)
		return 0

	-- Set publication status to inactive for all publications I have that are participating
	-- in the metadata cleanup.
	update sysmergepublications set status = 0 where LOWER(publisher)=LOWER(@@SERVERNAME) and
		publisher_db=db_name() and pubid in
			(select a1.pubid from sysmergearticles a1, sysmergearticles a2, sysmergesubscriptions s
				where a1.objid = a2.objid and a2.pubid = s.pubid and s.status = 7)
				
	-- If there are any open generations with more than 0 rows, we must still wait
	if exists (select * from MSmerge_genhistory gh where guidlocal = '00000000-0000-0000-0000-000000000000' and
				generation not in (select gen_cur from sysmergearticles) 
					and coldate in (select login_time from master..sysprocesses)) 
		return 0

	

	-- Am I the top-level publisher or just a republisher?
	set @mysrvid = 0
	select @mysrvid = srvid from master..sysservers where srvname = @@SERVERNAME
	if exists (select * from sysmergesubscriptions s where srvid = @mysrvid and
		db_name = db_name() and status = 7 and subid <> pubid)
		begin
		-- I am not the top level publisher.  Just set my status & let it propagate up.
		update sysmergesubscriptions set status = 8 where
			srvid = @mysrvid and db_name = db_name() and status = 7
		return 0
		end
	else
		begin
		-- I am the top level publisher!  Start getting rid of that old metadata!
		select @pubid = pubid from sysmergesubscriptions where srvid = @mysrvid and
			db_name = db_name() and status = 7
		exec @retcode = dbo.sp_MScompletecleanup @pubid
		return @retcode
		end
			
GO
exec dbo.sp_MS_marksystemobject sp_MSquiescecheck
go


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

create procedure sp_mergepreparecleanup
	@publication	sysname
AS
declare @pubid			uniqueidentifier
declare @retcode		int

declare @schematype             int
declare @schematext             nvarchar(2000)
/*
** Security Check
*/
EXEC @retcode = dbo.sp_MSreplcheck_publish
IF @@ERROR <> 0 or @retcode <> 0
	return (1)

set nocount on

/* Validate the publication  */
select @pubid = pubid from sysmergepublications 
		where name = @publication and 
		LOWER(publisher)=LOWER(@@SERVERNAME) and publisher_db=db_name()
		
if @pubid is NULL
		begin
   		raiserror (20026, 11, -1, @publication)
		return (1)
	    end
if exists (select * from sysmergearticles a1, sysmergearticles a2
	 where a1.pubid = @pubid and a2.objid = a1.objid and
		a2.pubid not in (select pubid from sysmergepublications where
		LOWER(publisher)=LOWER(@@SERVERNAME) and publisher_db=db_name()))
		begin
		raiserror(21272, 16, -1, @publication)
		return (1)
		end

exec @retcode=sp_MSBumpupCompLevel @pubid, 40 
if @@ERROR<>0 or @retcode<>0		
	return (1)
		
-- stop local updates by creating the quiesce triggers
exec @retcode = sp_MSquiescetriggerson

-- use publisher row in sysmergesubscriptions to store datetime we started quiesce sequence
update sysmergesubscriptions set last_sync_date = getdate() where pubid = @pubid and subid = @pubid

-- insert a schema change that will cause subscribers and republishers to prepare for cleanup
set @schematype = 17 /* metadata cleanup */
set @schematext = 'exec dbo.sp_MSpreparecleanup '
exec sp_MSpropagateschematorepubs @pubid, @schematext, @schematype
   	
GO

exec dbo.sp_MS_marksystemobject sp_mergepreparecleanup
go
grant execute on dbo.sp_mergepreparecleanup to public
go

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

create procedure sp_MSpreparecleanup
	@pubid			uniqueidentifier
AS
declare @retcode 		int
declare @schematype             int
declare @schematext             nvarchar(2000)

-- security check. this proc is called on the subscriber. Hence needs dbo sysadmin check
EXEC @retcode = dbo.sp_MSreplcheck_subscribe
IF @@ERROR <> 0 or @retcode <> 0
	return (1)
	
-- This should only be invoked by the merge agent as it processes schema changes
if sessionproperty('replication_agent') = 0
	return (1)

set nocount on

exec @retcode=sp_MSBumpupCompLevel @pubid, 40 
if @@ERROR<>0 or @retcode<>0		
	return (1)

-- stop local updates by creating the quiesce triggers
exec @retcode = sp_MSquiescetriggerson

-- If we are a republisher, propagate the schema change on to our subscribers
set @schematype = 17 /* prepare cleanup */
select @schematext = 'exec dbo.sp_MSpreparecleanup '
exec sp_MSpropagateschematorepubs @pubid, @schematext, @schematype
return (0)

GO

exec dbo.sp_MS_marksystemobject sp_MSpreparecleanup
go
grant execute on dbo.sp_MSpreparecleanup to public
go

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

create procedure sp_MSquiescetriggerson
AS
declare @cmd nvarchar(4000)

if not exists (select * from sysobjects where xtype = N'TR' and name = 'MS_mergequiescetrigger')
	begin
	set @cmd = 'create trigger MS_mergequiescetrigger on MSmerge_contents for UPDATE, INSERT, DELETE 
	NOT FOR REPLICATION AS
	if sessionproperty(''replication_agent'') = 0
		begin
		-- Raise an appropriate error
		RAISERROR(21510, 11, -1)
		rollback tran
		end'
	execute (@cmd)
	end

if not exists (select * from sysobjects where xtype = N'TR' and name = 'MS_mergequiescetrigger2')
begin
	set @cmd = 'create trigger MS_mergequiescetrigger2 on MSmerge_tombstone for UPDATE, INSERT, DELETE 
	NOT FOR REPLICATION AS
	if sessionproperty(''replication_agent'') = 0
		begin
		-- Raise an appropriate error
		RAISERROR(21510, 11, -1)
		rollback tran
		end'
	execute (@cmd)	
	end
GO
exec dbo.sp_MS_marksystemobject sp_MSquiescetriggerson
go


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

create procedure sp_MSquiescetriggersoff
AS
	if exists (select * from sysobjects where xtype = N'TR' and name = 'MS_mergequiescetrigger')
		drop trigger MS_mergequiescetrigger
	if exists (select * from sysobjects where xtype = N'TR' and name = 'MS_mergequiescetrigger2')
		drop trigger MS_mergequiescetrigger2

GO
exec dbo.sp_MS_marksystemobject sp_MSquiescetriggersoff
go
raiserror('Creating procedure sp_MSquiesceforcleanup', 0,1)
GO
create procedure sp_MSquiesceforcleanup	
	@pubid 	uniqueidentifier
AS
	declare @schematype 	int
	declare @schematext		nvarchar(4000)
	declare @mysrvid				int
	declare @retcode int

	set nocount on

	-- security check
	-- this proc is called on the subscriber through a schema change
	exec @retcode = dbo.sp_MSreplcheck_subscribe
	if @retcode <> 0 or @@error <> 0
	    return 1

	set @mysrvid = 0
	select @mysrvid = srvid from master..sysservers where srvname = @@SERVERNAME

	-- @pubid is normally the publication I subscribe to, get the pubid's of what I republish
	create table #mypubids(pubid uniqueidentifier)

	insert into #mypubids (pubid) select distinct p.pubid from sysmergepublications p,
			sysmergearticles a1, sysmergearticles a2
		where a1.pubid = @pubid and a1.objid = a2.objid and p.pubid = a2.pubid and
			 LOWER(p.publisher)=LOWER(@@SERVERNAME) and p.publisher_db=db_name()

	-- If I don't republish, then I am done and ready
	if @@ROWCOUNT = 0
		begin
		drop table #mypubids
		
		-- REPLICA_STATUS_ReadyForCleanup 		= 0x0008
		update sysmergesubscriptions set status = 8 where pubid = @pubid and
			db_name = db_name() and srvid = @mysrvid
		
		return 0
		end

	-- set my status to QuiesceRequested
    -- REPLICA_STATUS_QuieseRequested	= 0x0007,
	update sysmergesubscriptions set status = 7 where pubid = @pubid and
			db_name = db_name() and srvid = @mysrvid
	update sysmergesubscriptions set status = 7 where pubid in (select pubid from #mypubids) and
		db_name = db_name() and srvid = @mysrvid

	-- update status for my republishing subscribers
	update sysmergesubscriptions set status = 7 where pubid in (select pubid from #mypubids)
		and subid in (select s1.subid from sysmergesubscriptions s1, sysmergesubscriptions s2
				where s1.pubid in (select pubid from #mypubids) and s1.srvid = s2.srvid and
				s1.db_name = s2.db_name and s2.subid = s2.pubid and s2.status = 1)

	-- check if any of my subscribers republish
	if @@ROWCOUNT = 0
		begin
		-- If none of my subscribers are republishers, then I just
		-- set my publications' status to unavailable and set
		-- set my replica status as ReadyForCleanup

		update sysmergepublications set status = 0 where pubid in (select pubid from #mypubids)
		-- I may have ongoing merges call the quiescecheck routine to try and set my status
		exec @retcode = sp_MSquiescecheck					
		end
	else
		begin
		-- propagate the schema change on to my subscribers
		set @schematype = 19  -- SCHEMA_TYPE_QUIESCE			= 19
		set @schematext = 'exec dbo.sp_MSquiesceforcleanup '
		exec sp_MSpropagateschematorepubs @pubid, @schematext, @schematype
		end
