	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
	/* 
    ** 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)
        
	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.
	
	update sysmergearticles set status=1 where pubid=@pubid
	if @@ERROR<>0
		return (1)

	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  

grant execute on dbo.sp_MSreinitmergepublication to public
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_publish
    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) = '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
    ) 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     @retcode            smallint
    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_view        nvarchar(290)
    declare     @before_rowguidname	sysname
    declare     @quoted_pub         nvarchar(290)
    declare     @quoted_proc        nvarchar(290)
	declare 	@snapshot_ready		int
	declare 	@columns			varbinary(128)
	declare		@column_list		nvarchar(4000)
	declare 	@colname			nvarchar(270)
	declare 	@colid				int
    declare     @dynamic_filters    bit
    declare		@alias_for_sourceobject	sysname
    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)

    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 #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_view = QUOTENAME(@viewname)
            exec ('drop view ' + @quoted_view)
        end
        if @before_viewname IS NOT NULL
        begin
            exec ('drop view ' + @before_viewname)
        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
        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
                
        exec @retcode = sp_MSgetcolumnlist @pubid, @column_list OUTPUT, @source_objid
        
		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
        select @quoted_proc = QUOTENAME(@procname)
        
		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_view = QUOTENAME(@viewname)
                    exec ('drop view ' + @quoted_view)
                    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) 
                
                select @join_viewname = object_name(sync_objid), @join_before_view = object_name(before_image_objid)
                	from sysmergearticles where nickname = @join_nick and pubid = @pubid
                select @join_viewname = QUOTENAME(@join_viewname)

                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
                    if @column_list = ' * '
                    	select @column_list = ' ' + @unqual_sourcename + '.* '
                    set @view_rule = 'select ' + @column_list + ' from ' + @source_object + ' ' + @unqual_sourcename + ' , ' +  @join_viewname + ' ' + @unqual_jointable + ' where ' + @join_filterclause
                    /* add security check to the view if this is a dynamically filtered publication */
                    set @view_rule = @view_rule + ' and ((is_srvrolemember(''sysadmin'') = 1) or (is_member(''db_owner'') = 1) or (sessionproperty(''replication_agent'') = 1))'
                    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
                    end
                if @before_name is not null
                	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 + ') '
                   	 	
                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 = name from sysobjects 
                        where id = ( select objid from sysmergearticles where name=@join_articlename and pubid=@pubid) 
                
	                select @join_viewname = object_name(sync_objid), @join_before_view = object_name(before_image_objid)
                		from sysmergearticles where nickname = @join_nick and pubid = @pubid
                    select @join_viewname = QUOTENAME(@join_viewname)
                    set @view_rule = @view_rule + ' union select ' + @source_object + '.rowguidcol from ' + @source_object + ', ' +  @join_viewname + ' ' + @unqual_jointable + ' where ' + @join_filterclause
                   	if @before_name is not null
                		set @before_view_rule = @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 + ') '
                 
                    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
                    set @view_rule = @view_rule + ' union select ' + @source_object + '.rowguidcol from '+ @source_object + ' where '+ @bool_filterclause
                   	if @before_name is not null
                		set @before_view_rule = @before_view_rule + ' or ' + @bool_filterclause
					
                    end
                -- 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 ' + @column_list + ' from ' + @source_object + ' ' + @alias_for_sourceobject + ' where rowguidcol in (' + @view_rule + ')'
					set @view_rule = @view_rule + ' and ((is_srvrolemember(''sysadmin'') = 1) or (is_member(''db_owner'') = 1) or (sessionproperty(''replication_agent'') = 1))'
                    end
                end
            else  /* boolean filter only */
            	begin
                select @view_rule = ' select ' + @column_list + ' from '+ @source_object + ' ' + @unqual_sourcename + ' where '+ @bool_filterclause
				if @before_name is not null
					set @before_view_rule = ' select * from ' + @before_name + ' ' + @unqual_sourcename + ' where ' + @bool_filterclause
                set @view_rule = @view_rule + ' and ((is_srvrolemember(''sysadmin'') = 1) or (is_member(''db_owner'') = 1) or (sessionproperty(''replication_agent'') = 1))'
				end
                           
            select @viewname = @publication + '_' + @article + '_VIEW'
            exec @retcode = dbo.sp_MSuniqueobjectname @viewname , @viewname output
            select @quoted_view = QUOTENAME(@viewname)
            
            if @retcode <> 0 or @@ERROR <> 0 return (1) 
            /* If we havent generated rowguidcol yet, use dummy rule that doesnt refer to it */
            if @hasguid = 0
                set @view_rule = ' select ' + @column_list + ' from '+ @source_object + ' ' + @unqual_sourcename
            exec ('create view '+ @quoted_view + ' as '+ @view_rule)
            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_view + ' to public')
			if @@ERROR<>0
				return (1)
            /* Mark view as system object */                        
            execute sp_MS_marksystemobject @quoted_view
            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
                    exec ('drop procedure ' + @quoted_proc)
                    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_view = QUOTENAME(@viewname)
            update sysmergearticles set sync_objid = OBJECT_ID (@quoted_view), view_type = @permanent
                where artid = @artid and pubid = @pubid 
			if @before_name is not null
				begin
				exec @retcode = sp_MScreatebeforetable @source_objid
				if @@ERROR <>0 OR @retcode <>0 return (1)
				if object_id(@before_viewname) is not NULL
					exec ('drop view ' + @before_viewname)				
				exec ('create view ' + @before_viewname + ' as ' + @before_view_rule)
				if @@ERROR<>0
					return (1)
				if @before_rowguidname is not NULL
					begin
						exec ('grant select (' + @before_rowguidname + ') on '+ @before_viewname + ' to public')
						if @@ERROR<>0
							return (1)
					end
				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 /* end of view creation for this article */
        else 
        begin
            select @sync_objid = @source_objid
            if @vertical_partition=1 and @column_list<> ' * '
				begin
					select @viewname = @publication + '_' + @article + '_VIEW'
		            exec @retcode = dbo.sp_MSuniqueobjectname @viewname , @viewname output
					select @quoted_view = QUOTENAME(@viewname)
					set @view_rule = ' select ' + @column_list + ' from '+ @source_object + ' ' + @unqual_sourcename
                    set @view_rule = @view_rule + ' where ((is_srvrolemember(''sysadmin'') = 1) or (is_member(''db_owner'') = 1) or (sessionproperty(''replication_agent'') = 1))'

	            	exec ('create view '+ @quoted_view + ' as '+ @view_rule)
	            	if @@ERROR<>0
	            		return (1)
	        	    execute sp_MS_marksystemobject @quoted_view
	            	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_view + ' 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_view = QUOTENAME(@viewname)
                    set @view_rule = ' select  * from ' + @source_object  + ' where ((is_srvrolemember(''sysadmin'') = 1) or (is_member(''db_owner'') = 1) or (sessionproperty(''replication_agent'') = 1))'     
	            	exec ('create view '+ @quoted_view + ' as '+ @view_rule)
	            	if @@ERROR<>0
	            		return (1)
	        	    execute sp_MS_marksystemobject @quoted_view
	            	if @@ERROR<>0
	            		return (1)
                    exec ('grant select on ' + @quoted_view + ' 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
					exec ('drop proc ' + @procname)
					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 = @source_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_view = QUOTENAME(@viewname)
                        exec ('drop view ' + @quoted_view)
                        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_view = QUOTENAME(@viewname)
                exec ('create view ' + @quoted_view + ' 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
        declare @dbname sysname
        set @dbname = db_name()
        /* 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 */
            select @quoted_proc = QUOTENAME(@procname)
	        if object_id(@procname) is not NULL
                exec ('drop procedure ' + @quoted_proc)
            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
            select @quoted_proc = QUOTENAME(@procname)
            select @quoted_pub = QUOTENAME(@publication)
            set @command = 'exec dbo.sp_MSmakeexpandproc ' + @quoted_pub + ' , ' + @filter_id_str + ', ' + @quoted_proc
            exec @retcode = master..xp_execresultset @command, @dbname
            if @retcode <> 0 return (1)
			exec dbo.sp_MS_marksystemobject @quoted_proc
            if @@ERROR<>0
            	return (1)
            exec ('grant execute on ' + @quoted_proc + ' 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) NOT IN ('push', 'pull')
        BEGIN
            RAISERROR (14128, 16, -1)
            RETURN (1)
        END
    IF LOWER(@subscription_type) = '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) NOT IN ('local', 'global')
        BEGIN
            RAISERROR (20037, 16, -1)
            RETURN (1)
        END

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

   	/*
    ** Parameter Check: @use_interactive_resolver  
    */
    if LOWER(@use_interactive_resolver) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@use_interactive_resolver')
            RETURN (1)
        END
    if LOWER(@use_interactive_resolver) = '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) 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) 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) = '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) = '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) 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) = 'use_interactive_resolver'
        BEGIN

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

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

            /* Determine the bit value. */

	        IF LOWER(@value) = '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) = 'sync_type'
        BEGIN 
            /*
            ** Check to make sure that we have a valid sync_type.
            */
            IF LOWER(@value) NOT IN ('automatic', 'none')
                BEGIN
                    RAISERROR (14052, 16, -1)
                    goto UNDO
                END

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

            IF LOWER(@value) = '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) = '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) = '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) NOT IN ('push', 'pull', 'both')
        BEGIN
            RAISERROR (20079, 16, -1)
            RETURN (1)
        END
    IF LOWER(@subscription_type) = 'both'
        set @subscription_type_id = 2
    else IF LOWER(@subscription_type) = '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) = 'push' or LOWER(@subscription_type) = '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

	/*
	** 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) NOT IN ('both', 'push', 'pull')
        BEGIN
            RAISERROR (14128, 16, -1)
            RETURN (1)
        END
    IF LOWER(@subscription_type) = '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) = 'push'
        set @subscription_type_id = 0
    else 
        set @subscription_type_id = 1

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

    IF LOWER(@subscription_type) = '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) = '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

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) 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) = 'true'
                RAISERROR (20028, 16, -1)
            ELSE
                RAISERROR (20029, 16, -1)
            RETURN (1)
        END
    end

    /*
    ** Enable the database for publishing.
    */
    IF LOWER(@value) = '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) 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)='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)='join_unique_key'
		BEGIN
			IF LOWER(@value) NOT IN ('true','false')
    			BEGIN
     				 RAISERROR(14137,16,-1)
     				 RETURN(1)
 		    	END
			if LOWER(@value) = '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)='filtername'
        BEGIN
            update sysmergesubsetfilters set filtername=@value
                where join_filterid=@join_filterid
			if @@ERROR<>0
				goto FAILURE
        END

    IF LOWER(@property)='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
		  	goto FAILURE
		 
	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

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()
        
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)           
    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)

    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(nchar(10), @base_nick)
    set @join_nickstr = convert(nchar(10), @join_nick)
    set @filterid_str = convert(nchar(10), @filterid)
    
    set @cmd_piece = 'create procedure ' + @procname + ' 
        @belong int 
        AS 
            '
    insert into #tempcmd(cmdtext) values (@cmd_piece)
    set @cmd_piece = 'if @belong = 1
            begin
            /* Do a bulk insert to expand #belong */
            update #belong set flag = ' + @filterid_str + ' where flag < ' + @filterid_str + ' 
            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 = '  
        if @@ERROR <>0 return (1)
        /* Delete duplicates */
        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)
        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
        update #notbelong set flag = ' + @filterid_str + ' where flag < ' + @filterid_str + ' 
        /* 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 + ' 
            /* Remove duplicates */
            delete from #notbelong where flag = -1 and rowguid in
                (select rowguid from #notbelong where flag <> -1)
            '
	if @before_viewname  is not null
	set @cmd_piece = @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 + ' 
            /* Remove duplicates */
            delete from #notbelong where flag = -1 and rowguid in
                (select rowguid from #notbelong where flag <> -1)
            '

        
    if @must_check = 0
        begin
        insert into #tempcmd(cmdtext) values (@cmd_piece)
        set @cmd_piece = ' update #notbelong set flag = 0 where flag = -1 
            end '
        insert into #tempcmd(cmdtext) values (@cmd_piece)
        end
    else if @view_type = 1
        begin
        insert into #tempcmd(cmdtext) values (@cmd_piece)
        set @cmd_piece =  '     -- We can do our check with a bulk delete, bulk update
            delete from #notbelong where flag = -1 and rowguid in
                (select ' + @guidcolname + ' from ' + @view_name + ')
            update #notbelong set flag = 0 where flag = -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


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 @publisher_id       int
declare @subscriber         sysname
declare @subscriber_id      int
declare @subscriber_db      sysname
declare @publisher_db       sysname
declare @retention          int  -- in days         
declare @retcode            smallint
declare @recgen             int
declare @sentgen            int
declare @max_distretention  int
declare @recent_merge       datetime
declare @minus_retention2   datetime
declare @minus_retention    datetime
declare @send_ts            datetime
declare @receive_ts         datetime

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

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 @minus_retention2 = dateadd(day, -@retention * 2, getdate())
			select @minus_retention = 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 @receive_ts = coldate from MSmerge_genhistory 
    	                	where guidsrc = (select recguid from MSmerge_replinfo where repid = @subid)
            	        select @sentgen=sentgen from MSmerge_replinfo where repid=@subid
                	    select @send_ts = coldate from MSmerge_genhistory where generation=@sentgen
                    	if @receive_ts>@send_ts select @recent_merge = @receive_ts
                 	       else select @recent_merge = @send_ts
                            
 	                   if @status <> 2 and @recent_merge<@minus_retention
    	                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
                    	if @recent_merge<@minus_retention2 
                    	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
	-- 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
grant execute on dbo.sp_MSquiescecheck to public
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)

-- 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
grant execute on dbo.sp_MSquiescetriggerson to public
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

	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
	drop table #mypubids
GO

grant execute on dbo.sp_MSquiesceforcleanup to public
go

exec dbo.sp_MS_marksystemobject sp_MSquiesceforcleanup
go

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

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

set nocount on

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

/* 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
-- Make sure we are at the top-level publisher

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
		
-- If user never id sp_mergepreparecleanup then inform them of their error.
if not exists(select * from sysmergeschemachange where schematype = 17)
	begin
	raiserror(21506, 0, 1)
	return (1)
	end

-- Use sp_MSquiesceforcleanup to propagate schema, etc.
exec @retcode = sp_MSquiesceforcleanup @pubid
if @@ERROR<>0 or @retcode<>0
	return (1)

	
GO
exec dbo.sp_MS_marksystemobject sp_mergecompletecleanup
go

grant execute on dbo.sp_mergecompletecleanup to public
go

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

create procedure sp_MScompletecleanup
@pubid			uniqueidentifier
AS
declare @retcode 		int
declare @otherpubs  int 
declare @pubid2		uniqueidentifier
declare @artid                  uniqueidentifier
declare @schematype             int
declare @schemaversion          int
declare @schemaguid             uniqueidentifier
declare @schematext             nvarchar(2000)
declare @rcount						int
declare @dtprepare				datetime
declare @cutoffgen				int
declare @mysrvid				int
declare @toplevel				int

set nocount on

if exists (select * from sysmergepublications where pubid = @pubid and
		LOWER(publisher)=LOWER(@@SERVERNAME) and publisher_db=db_name())
	set @toplevel = 1
else
	set @toplevel = 0
	
/*
** Security Check
*/
EXEC @retcode = dbo.sp_MSreplcheck_publish
IF @@ERROR <> 0 or @retcode <> 0
	return (1)

-- Figure out whether there are any other publications which make us copy additional rows
set @otherpubs = 0

if exists (select * from sysmergearticles a1 where a1.objid not in
				(select objid from sysmergearticles where pubid = @pubid))
	set @otherpubs = 1

if @otherpubs = 1 or @toplevel = 1
	begin
	-- create some temp tables to preserve part of MSmerge_contents
	create table #contents (tablenick int NOT NULL,
				rowguid			uniqueidentifier rowguidcol   NOT NULL,
				generation		int					NOT NULL,
				partchangegen	int					NULL,
				joinchangegen	int					NULL,
				lineage			varbinary(249)		NOT NULL,
				colv1			varbinary(2048)		NULL)

	create table #tombstone (rowguid			uniqueidentifier rowguidcol NOT NULL,
				tablenick		int				NOT NULL,
				type			tinyint			NOT NULL,
				lineage			varbinary(249)	NOT NULL,
				generation		int				NOT NULL,
				reason			nvarchar(255)	NOT NULL)

	end
	
	-- propagate the schema change on to our subscribers
   	set @schematype = 18 /* metadata cleanup */
    select @schematext = 'exec dbo.sp_MScompletecleanup '
	exec sp_MSpropagateschematorepubs @pubid, @schematext, @schematype

if @toplevel = 1
  	begin				
 	/* Get the timestamp when we did the prepareforcleanup */
	select @dtprepare = last_sync_date from sysmergesubscriptions where pubid = @pubid and
			subid = @pubid
	-- Copy some generations from contents and tombstone into temptables
	select @cutoffgen = min(generation) from MSmerge_genhistory where coldate > @dtprepare
	
	if @otherpubs = 1
		begin
		insert into #contents (tablenick, rowguid, generation, partchangegen, joinchangegen, lineage, colv1)
				select tablenick, rowguid, 0,
						case when partchangegen < @cutoffgen then NULL else 0 end,
						case when joinchangegen < @cutoffgen then NULL else 0 end,
				lineage, colv1 from MSmerge_contents where generation >= @cutoffgen and tablenick
					in (select nickname from sysmergearticles where pubid = @pubid)
		end
	else
		begin
		-- leaving out join to sysmergearticles should help performance
		insert into #contents (tablenick, rowguid, generation, partchangegen, joinchangegen, lineage, colv1)
				select tablenick, rowguid, 0,
						case when partchangegen < @cutoffgen then NULL else 0 end,
						case when joinchangegen < @cutoffgen then NULL else 0 end,
				lineage, colv1 from MSmerge_contents where generation >= @cutoffgen 
		end
				
	if @otherpubs = 1
		begin
		insert into #tombstone (rowguid, tablenick, type, lineage, generation, reason)
			select rowguid, tablenick, type, lineage, 0, reason
			from MSmerge_tombstone where generation >= @cutoffgen and tablenick
				in (select nickname from sysmergearticles where pubid = @pubid)
		end
	else
		begin
		insert into #tombstone (rowguid, tablenick, type, lineage, generation, reason)
			select rowguid, tablenick, type, lineage, 0, reason
			from MSmerge_tombstone where generation >= @cutoffgen	
		end
 	end

if @otherpubs = 1
	begin
	insert into #contents (tablenick, rowguid, generation, partchangegen, joinchangegen, lineage, colv1)
		select * from MSmerge_contents where tablenick not in
			(select nickname from sysmergearticles where pubid = @pubid)
	insert into #tombstone (rowguid, tablenick, type, lineage, generation, reason)
		select * from MSmerge_tombstone where tablenick not in
			(select nickname from sysmergearticles where pubid = @pubid)
	end
	
truncate table MSmerge_contents
truncate table MSmerge_tombstone

if @otherpubs = 0
	begin
	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)
	insert into #artnicks select distinct nickname from sysmergearticles where
			pubid = @pubid
			
	-- do deletions in batches of 5000 to avoid excessive log growth
	set rowcount 5000
	set @rcount = 1
	while @rcount > 0
		begin
		set @rcount = 0
		delete MSmerge_genhistory WITH (PAGLOCK) from MSmerge_genhistory where art_nick in (select nickname from #artnicks)
		set @rcount = @@rowcount
		end
	
	update sysmergearticles set gen_cur = NULL where nickname in (select nickname from #artnicks)

	update MSmerge_replinfo set recgen = NULL, recguid = NULL, sentgen = NULL, sentguid = NULL 
			where repid in ( select subid from sysmergesubscriptions where pubid = @pubid)
	
	drop table #artnicks
	end

-- Remove triggers now so we can reinsert the rows we saved from contents
	execute @retcode = sp_MSquiescetriggersoff

if @toplevel = 1 or @otherpubs = 1
	begin
	-- Put back the rows from #contents and #tombstone
	insert into MSmerge_contents (tablenick, rowguid, generation, partchangegen, joinchangegen, lineage, colv1)
		select * from #contents
	insert into MSmerge_tombstone (rowguid, tablenick, type, lineage, generation, reason)
		select * from #tombstone
	end

-- Mark publications as active but snapshot expired
update sysmergepublications set status = 1, snapshot_ready = 3 where LOWER(publisher)=LOWER(@@SERVERNAME) and publisher_db=db_name() and 
		pubid in (select a1.pubid from sysmergearticles a1, sysmergearticles a2 where
					a2.pubid = @pubid and a1.objid = a2.objid)

-- Set our subscriber status back to 1
set @mysrvid = 0
select @mysrvid = srvid from master..sysservers where srvname = @@SERVERNAME
update sysmergesubscriptions set status = 1 where srvid = @mysrvid and db_name = db_name() and
	pubid in (select a1.pubid from sysmergearticles a1, sysmergearticles a2 where a1.objid = a2.objid 
				and a2.pubid = @pubid)
-- update subscriptions rows that still say status of ReadyForCleanup
update sysmergesubscriptions set status = 1 where status = 8
				
execute @retcode = dbo.sp_MSmakegeneration 
if @@ERROR <> 0 or @retcode <> 0
	return (1)
return 0
 
GO
exec dbo.sp_MS_marksystemobject sp_MScompletecleanup
go

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

create procedure sp_MSpropagateschematorepubs
	@pubid		uniqueidentifier,
	@schema_text	nvarchar(4000),
	@schema_type int
AS
declare @pubid2			uniqueidentifier
declare @artid			uniqueidentifier
declare @fulltext		nvarchar(4000)
declare @retcode		int
declare @schemaversion          int
declare @schemaguid             uniqueidentifier
declare @schematext             nvarchar(2000)

set nocount on

	-- Find republications and add the same schema change
	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()
       	select @fulltext = @schema_text + ' ''' + convert(nchar(36),@pubid2) + '''' 
		exec @retcode=sp_MSinsertschemachange @pubid2, @artid, @schemaversion, @schemaguid, @schema_type, @fulltext
        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
	return (0)
GO
exec dbo.sp_MS_marksystemobject sp_MSpropagateschematorepubs
go


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

create procedure sp_mergecleanupmetadata
	@publication		sysname = '%',
	@reinitialize_subscriber	nvarchar(5) = 'true'
AS
declare @pubid					uniqueidentifier
declare @retcode				int
declare @pubname  				sysname
declare @snapshot_ready 		int
declare @make_generation 		int
declare @artid                  uniqueidentifier
declare @schematype             int
declare @schemaversion          int
declare @schemaguid             uniqueidentifier
declare @schematext             nvarchar(2000)

set @make_generation = 0
/*
** 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)
	

/* Validate the publication name if one is provided */
if @publication <> '%'
	begin
		select @pubid = pubid from sysmergepublications 
			where name = @publication
		if @pubid is NULL
			begin
    			raiserror (20026, 11, -1, @publication)
			    return (1)
		    end
	end

if not exists (select * from sysobjects where name='sysmergearticles')
	return (1)
	
begin TRAN
save TRAN cleanupmetadata
declare #per_publication CURSOR LOCAL FAST_FORWARD for select pubid
	from sysmergepublications where LOWER(publisher)=LOWER(@@SERVERNAME) and publisher_db=db_name() and name like @publication
open #per_publication
fetch #per_publication into @pubid
while @@fetch_status<>-1
begin
	select @pubname=name, @snapshot_ready=snapshot_ready from sysmergepublications where pubid=@pubid
	if @publication <> '%' and exists (select * from sysmergearticles where pubid<>@pubid and objid in
		(select objid from sysmergearticles where pubid=@pubid))
	begin
		raiserror(21272, 16, -1, @pubname)
		goto FAILURE
	end

	/* Only do this when snapshot has been ran before this operation */
	if @snapshot_ready>0
	begin
		/* If there are any inprocess generations defer the cleanup of metadata */
		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
				RAISERROR(21503, 16, -1)
				close #per_publication
				deallocate #per_publication
				goto FAILURE
			end
		if LOWER(@reinitialize_subscriber)='true'		
		begin
			-- backward-comp-level is bump'd up by calling this stored procedure			
			exec @retcode = sp_MSreinitmergepublication @pubname
			if @@ERROR<>0 or @retcode<>0
			begin
				close #per_publication
				deallocate #per_publication
				goto FAILURE
			end

			update sysmergepublications set snapshot_ready=2 where pubid=@pubid
			if @@ERROR<>0
			begin
				close #per_publication
				deallocate #per_publication
				goto FAILURE
			end
		end
		else
		begin
			raiserror(21355, 10, -1,@publication)
			exec @retcode=sp_MSBumpupCompLevel @pubid, 30 --SP2 subscribers support this, but do not propagate
			if @@ERROR<>0 or @retcode<>0
			begin
				close #per_publication
				deallocate #per_publication
				goto FAILURE
			end

			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 = 16 /* metadata cleanup */
        	select @schematext = 'exec dbo.sp_MScleanup_metadata '+ '''' + convert(nchar(36),@pubid) + '''' 
			exec @retcode=sp_MSinsertschemachange @pubid, @artid, @schemaversion, @schemaguid, @schematype, @schematext
            if @@ERROR<>0 or @retcode<>0 
            begin
				close #per_publication
				deallocate #per_publication
				goto FAILURE            
            end
			
		end
	end
	set @make_generation = 1
	fetch next from #per_publication into @pubid
end
close #per_publication
deallocate #per_publication
COMMIT TRAN

if @publication = '%'
	begin
		set @pubid = NULL
	end
else
	begin
		select @pubid = pubid from sysmergepublications 
			where name = @publication
	end

	exec @retcode = dbo.sp_MScleanup_metadata @pubid
	if @@ERROR <> 0 or @retcode <> 0
   	    goto FAILURE
if @make_generation = 1
	begin
		execute @retcode = dbo.sp_MSmakegeneration 
		if @@ERROR <> 0 or @retcode <> 0
       	    goto FAILURE
	end
return (0)
FAILURE:
	if @@TRANCOUNT > 0
    begin
        ROLLBACK TRANSACTION cleanupmetadata
        COMMIT TRANSACTION
    end
	return (1)

GO

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


raiserror('Creating procedure sp_MScleanup_conflict_table', 0,1)
GO
/*
** This stored procedure is to periodically check and cleanup all the conflict entries 
** in conflict tables that has been there longer than the value of conflict_retention in
** days. 
*/

create procedure sp_MScleanup_conflict_table
AS
BEGIN
	declare	@retcode			int
	declare @pubid				uniqueidentifier
	declare	@conflict_retention	int
			,@conflict_table	sysname
			,@cmd				nvarchar(4000)
			,@tranpubid			int

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

	--
	-- merge cleanup
	--
	if exists (select * from sysobjects where name = 'sysmergepublications')
	begin
		declare PC CURSOR LOCAL FAST_FORWARD for select DISTINCT pubid, conflict_retention
			from sysmergepublications where LOWER(publisher)=LOWER(@@SERVERNAME) and publisher_db=db_name() and conflict_retention>0
		open PC
		fetch PC into @pubid, @conflict_retention
		while (@@fetch_status<>-1)
		begin
			exec @retcode = sp_MScleanup_conflict @pubid, @conflict_retention
			if @@ERROR<>0 or @retcode<>0
			begin
				close PC
				deallocate PC
				return (1)
			end
			fetch next from PC into  @pubid, @conflict_retention
		end
		close PC
		deallocate PC
	end

	--
	-- tran cleanup
	--
	if (EXISTS (select * from sysobjects where name = 'syspublications'))
	begin
		--
		-- do for each conflict table in each publication
		--
		declare hCftTab cursor LOCAL FAST_FORWARD for
			select a.pubid, a.conflict_retention, OBJECT_NAME(c.conflict_tableid)
			from (syspublications as a join sysarticles as b on a.pubid = b.pubid)
				join sysarticleupdates as c on c.artid = b.artid and c.pubid = b.pubid
			where a.allow_queued_tran = 1 and a.conflict_retention>0

		open hCftTab
		fetch hCftTab into @tranpubid, @conflict_retention, @conflict_table
		while (@@fetch_status != -1)
		begin
			--
			-- delete the expired messages
			--
			select @cmd = 'delete ' + @conflict_table + 
				' where datediff(dd, getdate(), insertdate) > ' + 
				cast(@conflict_retention as nvarchar(10)) +
				' and pubid = ' + cast(@tranpubid as nvarchar(10))

			execute (@cmd)
			if (@@error != 0)
			begin
				close hCftTab
				deallocate hCftTab
				return 1				
			end
				
			--
			-- Get next conflict table to clean
			--
			fetch hCftTab into @tranpubid, @conflict_retention, @conflict_table
		end

		--
		-- close cursor
		--
		close hCftTab
		deallocate hCftTab
	end

	--
	-- all done 
	--
	return 0
END
GO
exec dbo.sp_MS_marksystemobject sp_MScleanup_conflict_table
go

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

CREATE PROCEDURE sp_validatemergesubscription
	(@publication			sysname,
	 @subscriber			sysname,
	 @subscriber_db			sysname,
	 @level					tinyint
	 ) AS
	 
    declare @subscriber_srvid		int
	declare @retcode				int
	declare @pubid					uniqueidentifier
	/*
	** 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 @subscriber_srvid = srvid from master..sysservers where UPPER(srvname) = UPPER(@subscriber)

	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
	if @level <1 or @level > 3
		begin
			raiserror(21184, 16, -1, '@level', '1', '2','3')
			return (1)
		end

	if not exists (select * from sysmergesubscriptions where pubid=@pubid and db_name=@subscriber_db and srvid=@subscriber_srvid)
		begin
			raiserror(14055, 16, -1)
			return (1)
		end

	update sysmergesubscriptions set validation_level=@level where pubid=@pubid and db_name=@subscriber_db and srvid=@subscriber_srvid
	if @@ERROR<>0
		return (1)
	return (0)

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

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

CREATE PROCEDURE sp_validatemergepublication
	(@publication			sysname,
	 @level					tinyint
	 ) AS
	 
    declare @artid                  uniqueidentifier
    declare @schematype             int
    declare @schemaversion          int
    declare @schemaguid             uniqueidentifier
    declare @schematext				nvarchar
	declare @retcode				int
	declare @pubid					uniqueidentifier
	/*
	** 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
	if @level <1 or @level > 3
		begin
			raiserror(21184, 16, -1, '@level', '1', '2','3')
			return (1)
		end

	raiserror(21356, 10, -1, @publication)
	exec @retcode=sp_MSBumpupCompLevel @pubid, 30 
	if @@ERROR<>0 or @retcode<>0		
		return (1)

	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 = 66 /* publication wide validation */
		select @schematext = convert(nvarchar, @level)		
		exec @retcode=sp_MSinsertschemachange @pubid, @artid, @schemaversion, @schemaguid, @schematype, @schematext
		if @@ERROR<>0 or @retcode<>0
			return (1)
	return (0)
go
exec dbo.sp_MS_marksystemobject sp_validatemergepublication
go
grant exec on dbo.sp_validatemergepublication to public
go

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

create procedure sp_MScleanup_conflict
@pubid					uniqueidentifier,
@conflict_retention	int = NULL
AS
	declare @pubname			sysname
	declare @valid_date			datetime
	declare @conflict_table		sysname
	declare @conflict_id		int
	declare @retention_string		nvarchar(100)
	declare @pubidstr			nvarchar(100)
	declare @retcode			int
	
	--if no conflict_retention value is specified, query local table and find out.
	if @conflict_retention is NULL
		select @conflict_retention=conflict_retention from sysmergepublications where pubid=@pubid

	--do not do any cleanup if conflict retention value is 0
	else if @conflict_retention = 0
		return (0)

	select @pubname=name from sysmergepublications where pubid=@pubid
	select @pubidstr = convert(nvarchar(40), @pubid)
	select @valid_date = dateadd(day, -@conflict_retention, getdate())
	select @retention_string = convert(nvarchar, @conflict_retention)
	/*
	** Security Check
	*/
    EXEC @retcode = dbo.sp_MSreplcheck_publish
    IF @@ERROR <> 0 or @retcode <> 0
        return (1)


	if exists (select name from syscolumns where name='create_time' and id = object_id('MSmerge_delete_conflicts'))
	begin
		delete from MSmerge_delete_conflicts where create_time < @valid_date and pubid=@pubid
		if @@ERROR<>0
			return (1)
	end
	
	declare AC CURSOR LOCAL FAST_FORWARD for select conflict_table from sysmergearticles where pubid=@pubid
	open AC
	fetch AC into @conflict_table
	while (@@fetch_status<>-1)
	begin
		if @conflict_table is NOT null
			begin

			select @conflict_id = object_id(@conflict_table)
			select @conflict_table=QUOTENAME(@conflict_table)
			/*
			** Upgrade conflict table so that it can get cleaned up later on
			*/
			if @conflict_id is not NULL and not exists (select name from syscolumns where name='create_time' and id=@conflict_id)
					begin
						exec ('alter table ' + @conflict_table + ' add create_time datetime not NULL default getdate() ')
						if @@ERROR<>0
							goto FAILURE
					end

			if @conflict_id is not NULL and @retention_string is not NULL and exists (select name from syscolumns where name='create_time' and id=@conflict_id)
					begin
						exec ('delete from ' + @conflict_table + ' where datediff(dd, create_time, getdate()) > ' + @retention_string + ' and pubid = ''' + @pubidstr + '''')
						if @@ERROR<>0
							goto FAILURE
					end
			end
		fetch next from AC into @conflict_table
	end
	close AC
	deallocate AC
	return (0)
FAILURE:
	close AC
	deallocate AC
	return (1)
GO
exec dbo.sp_MS_marksystemobject sp_MScleanup_conflict
go


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

create procedure sp_generatefilters
    @publication                sysname
    AS
    /* Declare  a few variables */
    declare @pubid  uniqueidentifier
    declare @art_name sysname
    declare @object_id  int
    declare @join_objid int
    declare @retcode int
    declare @join_unique int
    declare @distance int
    
    /*
    ** Security Check
    */
    EXEC @retcode = dbo.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 (14043, 16, -1, '@publication')
            return (1)
        end
    /*
    ** Get the pubid and make sure the publication exists
    */
    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 up some temp tables to help keep track of what to process */
    create table #filtered (object_id int NOT NULL, distance int NOT NULL)
    create table #unfiltered (object_id int NOT NULL, art_name sysname collate database_default not null)

    /* Do initial population of temp tables */
    insert into #filtered (object_id, distance) select objid, 0 from sysmergearticles where
        pubid = @pubid and len(subset_filterclause) > 0
    insert into #unfiltered (object_id, art_name) select objid, name from sysmergearticles
        where pubid = @pubid and objid not in (select object_id from #filtered)

    /* remove self-referencing tables from #unfiltered as we should not try to filter them */
    delete from #unfiltered where object_id in
            (select rkeyid from sysreferences where rkeyid = fkeyid)
    select @distance = min(distance) from #filtered f, sysreferences r, #unfiltered u where
        (f.object_id = r.rkeyid and r.fkeyid = u.object_id) or
        (u.object_id = r.rkeyid and r.fkeyid = f.object_id)
        
    /* Look for something in sysreferences to add a join filter for */
    select @join_unique = 1, @object_id = fkeyid, @join_objid = rkeyid, @art_name = art_name
        from sysreferences r, #unfiltered u where r.fkeyid = u.object_id and
        r.rkeyid in (select object_id from #filtered where distance = @distance) 

    if @art_name is null
        select  @join_unique = 0, @object_id = rkeyid, @join_objid = fkeyid, @art_name = art_name
            from sysreferences r, #unfiltered u where r.rkeyid = u.object_id and
            r.fkeyid in (select object_id from #filtered where distance = @distance) 
    while @art_name is not null
        begin
        /* Make the join filter corresponding to this relationship */
        exec @retcode=sp_MSmakejoinfilter @publication, @art_name, @object_id, @join_objid, @join_unique
        if @@ERROR<>0 or @retcode<>0 return (1)
        /* Move row from #unfiltered to #filtered */
        insert into #filtered (object_id, distance) values (@object_id, @distance + 1)
        delete from #unfiltered where object_id = @object_id
        
        /* See if any more that can be added */
        select @distance = min(distance) from #filtered f, sysreferences r, #unfiltered u where
            (f.object_id = r.rkeyid and r.fkeyid = u.object_id) or
            (u.object_id = r.rkeyid and r.fkeyid = f.object_id)
        set @art_name = NULL
        select @join_unique = 1, @object_id = fkeyid, @join_objid = rkeyid, @art_name = art_name
            from sysreferences r, #unfiltered u where r.fkeyid = u.object_id and
            r.rkeyid in (select object_id from #filtered where distance = @distance) 
        if @art_name is null
            select  @join_unique = 0, @object_id = rkeyid, @join_objid = fkeyid, @art_name = art_name
                from sysreferences r, #unfiltered u where r.rkeyid = u.object_id and
                r.fkeyid in (select object_id from #filtered where distance = @distance) 
        end
    return (0)
go
            
exec dbo.sp_MS_marksystemobject sp_generatefilters
go

grant execute on dbo.sp_generatefilters to public
go

/*
** Name :       sp_MShelpmergeconflictcounts
** Description: This sp returns the count of conflicts (from MSmerge_delete_conflicts and 
**              each conflict table) in each publication. Results can optionally be filtered
**				to include only a single publication. Results are always ordered by article
**				name. Only articles with non-zero conflict counts are returned.
** Parameters:  1. Publication Name( sysname; default '%'==ALL PUBLICATIONS)
** Output Result Set has the following structure 
**  ----------------------------------------------------------------------------------
**      Name                Datatype                Description
**  ----------------------------------------------------------------------------------
**  a. article				(sysname)		    Article name
**  b. conflict_table		(sysname)			Associated conflict table
**  c. guidcolname			(sysname)			Article's rowguidcol name
**  d. centralized_conflicts(integer)			Centralized (1) or Decentralized (0) 
**												conflicts specified by the article
**  e. conflict_ucount		(integer)           Count of (update) conflicts in the 
**												conflict table for this article
**  f. conflicts_dcount		(integer)           Count of (delete) conflicts in the 
**												MSmerge_delete_conflicts table for this article
*/
raiserror('Creating procedure sp_MShelpmergeconflictcounts', 0,1)
GO

create procedure sp_MShelpmergeconflictcounts ( 
	@publication_name sysname = '%' ,
    @publisher		sysname = NULL,
	@publisher_db	sysname = NULL)
as 
begin

	set nocount on 

	declare	@aname 		sysname
	declare	@cmd 		nvarchar(2000) 
	declare	@conflict_table sysname 
	declare	@count 		integer
    declare @pubid          uniqueidentifier
    declare @pubidstr       nvarchar(38)

    if @publisher IS NULL
    	select @publisher = @@SERVERNAME
    	
    if @publisher_db IS NULL
	    select @publisher_db = db_name()

	if @publication_name <> '%'
        begin
            /*
            ** Parameter Check:  @publication.
            ** Make sure that the publication exists.
            */
            select @pubid = pubid from sysmergepublications 
            	where name = @publication_name and 
            		LOWER(publisher) = LOWER(@publisher) and
            		publisher_db = @publisher_db
            if @pubid IS NULL
                BEGIN
                    RAISERROR (20026, 16, -1, @publication_name)
                    RETURN (1)
                END
		    set @pubidstr = '''' + convert(nchar(36), @pubid) + '''' 
		end

	-- make sure current db has merge publishing tables (true on both pub and sub)
	if not exists ( select * from sysobjects where name = 'sysmergearticles')
	begin
		raiserror( 18757, 16, -1 )
		return(1)
	end

	-- allow null conflict table name to handle case where there are delete conflicts but no update conflicts
	create table #result_list ( article sysname collate database_default, source_object sysname collate database_default, conflict_table sysname collate database_default null, guidcolname sysname collate database_default, centralized_conflicts integer, conflicts_ucount integer, conflicts_dcount integer )
	create table #conflict_list ( article_name sysname collate database_default, conflicts_ucount integer, conflicts_dcount integer )
	create table #update_list ( article_name sysname collate database_default, conflicts_ucount integer )

	-- get delete counts
	if ( @publication_name = '%' )
		declare hCdcount CURSOR LOCAL FAST_FORWARD fast_forward for 
			select distinct a.name 
				from MSmerge_delete_conflicts d
				inner join sysmergepublications p on p.pubid = d.pubid
				inner join sysmergearticles a on a.pubid = p.pubid and a.nickname = d.tablenick
	else
		declare hCdcount CURSOR LOCAL FAST_FORWARD fast_forward for 
			select distinct a.name 
				from MSmerge_delete_conflicts d
				inner join sysmergepublications p on p.pubid = d.pubid
				inner join sysmergearticles a on a.pubid = p.pubid and a.nickname = d.tablenick
				where p.pubid = @pubid
	open hCdcount
	fetch hCdcount into @aname
	while ( @@fetch_status <> -1 )
	begin

		select @cmd = 'select ''' + @aname + ''', 0, count(*) 
				from MSmerge_delete_conflicts d
					inner join sysmergepublications p on p.pubid = d.pubid
					inner join sysmergearticles a on a.pubid = p.pubid and a.nickname = d.tablenick
				where a.name = N''' + @aname + ''' '

		if ( @publication_name <> '%' ) 
			select @cmd = @cmd + ' and p.pubid = ' + @pubidstr

		insert #conflict_list ( article_name, conflicts_ucount, conflicts_dcount )
			exec ( @cmd )

		fetch hCdcount into @aname
	end
	close hCdcount
	deallocate hCdcount

	-- get update counts
	if ( @publication_name = '%' )
		declare hCucount CURSOR LOCAL FAST_FORWARD fast_forward for 
			select distinct a.name, a.conflict_table
				from sysmergepublications p
				inner join sysmergearticles a on a.pubid = p.pubid
			where a.conflict_table is not null
	else
		declare hCucount CURSOR LOCAL FAST_FORWARD fast_forward for 
			select distinct a.name, a.conflict_table
				from sysmergepublications p
				inner join sysmergearticles a on a.pubid = p.pubid
			where a.conflict_table is not null
			  	and p.pubid = @pubid
	open hCucount
	fetch hCucount into @aname, @conflict_table
	while ( @@fetch_status <> -1 )
	begin
		select @cmd = 'select ''' + @aname + ''', count(*) 
				from ' + QUOTENAME( @conflict_table ) + ' ct
					inner join sysmergepublications p on p.pubid = ct.pubid '

		if ( @publication_name <> '%' ) 
			select @cmd = @cmd + ' where p.name = N''' + @publication_name + ''''

		insert #update_list ( article_name, conflicts_ucount )
			exec( @cmd )

		fetch hCucount into @aname, @conflict_table
	end
	close hCucount
	deallocate hCucount

	update #conflict_list set conflicts_ucount = isnull( ul.conflicts_ucount, 0 )
		from #conflict_list cl
		inner join #update_list ul on ul.article_name = cl.article_name

	delete #update_list 
		from #update_list ul 
		inner join #conflict_list cl on ul.article_name = cl.article_name

	insert #conflict_list
		select *, 0 from #update_list where conflicts_ucount > 0

	drop table #update_list
	select @cmd = 'select distinct t.article_name, '''' + quotename(user_name( o.uid )) + ''.'' + quotename(o.name) + '''',' +
			' a.conflict_table, c.name, p.centralized_conflicts, t.conflicts_ucount, t.conflicts_dcount
			from #conflict_list t
				inner join sysmergearticles a on a.name = t.article_name
				inner join sysmergepublications p on p.pubid = a.pubid
				inner join sysobjects o on o.id = a.objid 
				inner join syscolumns c on c.id = o.id and ColumnProperty (o.id, c.name, ''IsRowGuidCol'') = 1
			where (t.conflicts_ucount > 0 or t.conflicts_dcount > 0) and a.conflict_table is NOT NULL'

	insert #result_list
		exec ( @cmd )		
	if ( @@error <> 0 )
		return (1)

	drop table #conflict_list

	select * from #result_list
	drop table #result_list

return (0)
end
go

exec dbo.sp_MS_marksystemobject sp_MShelpmergeconflictcounts
go

grant execute on dbo.sp_MShelpmergeconflictcounts to public
go


/*
** Name :       sp_MShelpmergeconflictpublications
** Description: This sp returns a list of merge publications in the current database 
**				that may have conflicts.
**				Results are always ordered by publication name.
** Output Result Set has the following structure 
**  ----------------------------------------------------------------------------------
**      Name                Datatype                Description
**  ----------------------------------------------------------------------------------
**  a. publication			(sysname)				Publication name
**	b. merge_pub_id			(uniqueidentifier)		Merge publication id
*/
raiserror('Creating procedure sp_MShelpmergeconflictcpublications', 0,1)
GO

create procedure sp_MShelpmergeconflictpublications
as 
begin

	set nocount on 

	declare @dbname sysname, @cmd nvarchar(200)
	set @dbname = db_name()

	-- make sure current db has merge publishing tables (true on both pub and sub)
	if not exists ( select * from sysobjects where name = 'sysmergepublications')
	begin
		raiserror( 18757, 16, -1 )
		return(1)
	end

	-- unlike tran, all merge publications are updateable, so just return all
	set @cmd = 'select name, pubid, publisher, publisher_db from ' + @dbname + '.dbo.sysmergepublications order by name'
	exec( @cmd )

 	return (0)

end
go

exec dbo.sp_MS_marksystemobject sp_MShelpmergeconflictpublications
go

grant execute on dbo.sp_MShelpmergeconflictpublications to public
go

create procedure sp_MSclearcolumnbit
@pubid			uniqueidentifier,
@artid			uniqueidentifier,
@column_name	sysname
as 

declare @tablename		sysname
declare @colid			int
declare @in_use			bit
declare @objid			int
declare @columns 		varbinary(128)
declare @local_repid	uniqueidentifier
declare @publish		int
set nocount on 

set @publish		= 0x4000
set @in_use=0
set @local_repid	= NULL

select @tablename = object_name(objid), @objid=objid from sysmergearticles where artid=@artid and pubid=@pubid

select @colid=colid from syscolumns where id=@objid and name=@column_name

select top 1 @local_repid=pubid from sysmergearticles where artid=@artid and pubid<>@pubid order by pubid ASC
if @local_repid is NULL
begin
	update syscolumns set colstat = colstat & ~@publish where id=@objid and colid=@colid
	if @@ERROR<>0
		return (1)
	return (0)
end
while @local_repid is not NULL and @in_use=0
begin
	select @columns = columns from sysmergearticles where pubid=@local_repid and artid=@artid
	exec @in_use = sp_MStestbit @bm=@columns, @coltotest=@colid
	set @local_repid	= NULL
	select top 1 @local_repid=pubid from sysmergearticles where artid=@artid and pubid>@local_repid order by pubid ASC
end
if @in_use=0
begin
	update syscolumns set colstat = colstat & ~@publish where id=@objid and colid=@colid
	if @@ERROR<>0
		return (1)
end
return (0)
go

exec dbo.sp_MS_marksystemobject sp_MSclearcolumnbit
go

grant execute on dbo.sp_MSclearcolumnbit to public
go

/*
** Name :       sp_helpmergearticleconflicts
** Description: This sp returns the articles in the publication that have conflicts.
**              Optionally if the publication is not specified, all articles in the 
**              database that have conflicts is returned.
** Parameters:  Publication Name( default NULL)
** Output Result Set has the following columns
**              publication, article, source_object, conflict_table, guidcolname
*/
raiserror('Creating procedure sp_helpmergearticleconflicts', 0,1)
GO
CREATE PROCEDURE sp_helpmergearticleconflicts(
    @publication    sysname = '%',
    @publisher		sysname = NULL,
    @publisher_db	sysname = NULL
    )
as
	set nocount on
    declare @pubid          uniqueidentifier
    declare @cmd            nvarchar(4000)

    declare @retcode		int
	declare @nickname		int
    declare @retcode2 		int
    declare @name			sysname
    declare @source_owner	sysname
    declare @source_object	sysname
    declare @conflict_table	sysname
    declare @guidcolname		sysname
    declare @centralized_conflicts int
	declare @objid			int
	declare @command		nvarchar(200)

    if @publisher IS NULL
    	select @publisher = @@SERVERNAME
    	
    if @publisher_db IS NULL
	    select @publisher_db = db_name()

	if @publication <> '%'
        begin
            /*
            ** Parameter Check:  @publication.
            ** Make sure that the publication exists.
            */
            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, 16, -1, @publication)
                    RETURN (1)
                END
		end

	create table #temp_conflict(article 				sysname collate database_default,
								source_owner			sysname collate database_default,
								source_object			sysname collate database_default,
								conflict_table			sysname collate database_default,
								guidcolname				sysname collate database_default,
								centralized_conflicts	int) 	

	declare #cur_conflict cursor local for 
		select name, objid, conflict_table, pubid, nickname from sysmergearticles 
			where conflict_table is not NULL and pubid in (select pubid from sysmergepublications where name like @publication)
				for read only
	open #cur_conflict
	fetch #cur_conflict into @name, @objid, @conflict_table, @pubid, @nickname
	while (@@fetch_status <> -1)
	begin
		select @source_owner = user_name(uid) from sysobjects where id = @objid
		select @source_object = object_name (@objid)
		select @guidcolname = name from syscolumns where id = @objid and ColumnProperty(@objid, name, 'IsRowGuidCol') = 1
		select @centralized_conflicts = centralized_conflicts from sysmergepublications where pubid = @pubid

		select @command = 'if exists (select * from ' + QUOTENAME(@conflict_table) + ') select @retcode2 = 1
								else select @retcode2 = 0'
		EXEC @retcode = dbo.sp_executesql @command, N'@retcode2 int output', @retcode2 output
		if @retcode <>0
				return (1)
		if @retcode2 = 1 
		begin
			insert into #temp_conflict 
				values (@name, @source_owner, @source_object, @conflict_table, @guidcolname, @centralized_conflicts)
		end
		if EXISTS (select * from MSmerge_delete_conflicts where tablenick = @nickname)
		begin
			insert into #temp_conflict
				values (@name, @source_owner, @source_object, 'MSmerge_delete_conflicts', @guidcolname, @centralized_conflicts)
		end
		fetch #cur_conflict into @name, @objid, @conflict_table, @pubid, @nickname
	end

	select * from #temp_conflict order by article

	drop table #temp_conflict
	close #cur_conflict
	deallocate #cur_conflict
return(0)       
go
exec dbo.sp_MS_marksystemobject sp_helpmergearticleconflicts 
go

/*
** Name :       sp_helpmergeconflictrows
** Description: This sp returns the rows in the conflict_table specified. 
**              Optionally if the publication is specified, all conflicts qualified by the
**              publication are returned. For instance if the Conflict_Customers table 
**              has conflict rows for the 'WA' and the 'CA' publication, passing in 
**              a publication name say 'CA' retrieves conflicts pertaining to the 
**              'CA' publication.
** Parameters:  1. Publication Name( default NULL)
**              2. Conflict Table Name
** Output Result Set has the same structure as the Conflict_<table> i.e the base
** table structure with the following additional columns:
**  ----------------------------------------------------------------------------------
**      Name                Datatype            Description
**  ----------------------------------------------------------------------------------
**  a. origin_datasource    (varchar(255))      Indicates the origin of the conflict                                                         
**  b. conflict_type        (int)               Code indicating type of conflict
**                                              UpdateConflict      = 1
**                                              UploadError         = 2
**                                              DownloadError       = 3
**                                              UpdateDeleteConflict= 4
**                                              ColumnUpdateConflict= 5
**  c. reason_code          (int)               Error code that may be context sensitive 
**  d. reason_text          (varchar(720))      Error description that may be context sensitive                                                          
**  e. pubid                (uniqueidentifier)  Publication identifier
*/
raiserror('Creating procedure sp_helpmergeconflictrows', 0,1)
GO
CREATE PROCEDURE sp_helpmergeconflictrows(
    @publication    sysname = '%',
    @conflict_table sysname,
    @publisher		sysname = NULL,
    @publisher_db	sysname = NULL
    
    )
as 
	set nocount on
    declare @pubid          uniqueidentifier
    declare @cmd            nvarchar(4000)  
    declare @pubidstr       nvarchar(38)
    
    if @publisher IS NULL
    	select @publisher = @@SERVERNAME
    	
    if @publisher_db IS NULL
	    select @publisher_db = db_name()
    select @cmd = 'select * from ' 
    select @cmd = @cmd + QUOTENAME(@conflict_table)
    if @publication <> '%'
        begin
            /*
            ** Parameter Check:  @publication.
            ** Make sure that the publication exists.
            */
            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, 16, -1, @publication)
                    RETURN (1)
                END

		    set @pubidstr = '''' + convert(nchar(36), @pubid) + '''' 
            select @cmd = @cmd + ' where pubid = ' + @pubidstr
            
        end
    exec (@cmd)
    if (@@error <> 0)
        RETURN (1)
    
    return 0
go

exec dbo.sp_MS_marksystemobject sp_helpmergeconflictrows 
go

/*
** Name :       sp_helpmergedeleteconflictrows
** Description: This sp returns the rows in the MSmerge_delete_conflicts specified. 
**              Optionally if the publication is specified, all conflicts qualified by the
**              publication are returned. For instance if the MSmerge_delete_conflicts table 
**              has conflict rows for the 'WA' and the 'CA' publication, passing in 
**              a publication name say 'CA' retrieves conflicts pertaining to the 
**              'CA' publication only.
** Parameters:  1. Publication Name( default NULL)
**              2. Source Object Name
** Output Result Set has the following structure 
**  ----------------------------------------------------------------------------------
**      Name                Datatype                Description
**  ----------------------------------------------------------------------------------
**  a. source_object        (nvarchar(386))     Indicates the source object for the delete conflict                                                         
**  b. rowguid              (uniqueidentifier)  Row identifier for the delete conflict
**  c. origin_datasource    (varchar(255))      Indicates the origin of the conflict                                                         
**  d. conflict_type        (int)               Code indicating type of conflict
**                                              UpdateConflict      = 1
**                                              UploadError         = 2
**                                              DownloadError       = 3
**                                              UpdateDeleteConflict= 4
**                                              ColumnUpdateConflict= 5
**  e. reason_code          (int)               Error code that may be context sensitive 
**  f. reason_text          (varchar(720))      Error description that may be context sensitive                                                          
**  g. pubid                (uniqueidentifier)  Publication identifier
*/
raiserror('Creating procedure sp_helpmergedeleteconflictrows', 0,1)
GO

CREATE PROCEDURE sp_helpmergedeleteconflictrows(
    @publication    sysname = '%',
    @source_object  nvarchar(386) = NULL,
    @publisher		sysname = NULL,
    @publisher_db	sysname = NULL
    )
as
    declare @pubid          uniqueidentifier
    declare @cmd            nvarchar(4000)  
    declare @pubidstr       nvarchar(38)

    if @publisher IS NULL
    	select @publisher = @@SERVERNAME
    	
    if @publisher_db IS NULL
	    select @publisher_db = db_name()
    select @cmd = 'select distinct source_object =  user_name(sysobjects.uid) + '
    select @cmd = @cmd + '''.'''
    select @cmd = @cmd + ' + sysobjects.name, MSmerge_delete_conflicts.rowguid, MSmerge_delete_conflicts.conflict_type, '
    select @cmd = @cmd + ' MSmerge_delete_conflicts.reason_code, MSmerge_delete_conflicts.reason_text, '
    select @cmd = @cmd + ' MSmerge_delete_conflicts.origin_datasource, MSmerge_delete_conflicts.pubid, MSmerge_delete_conflicts.create_time from MSmerge_delete_conflicts, sysmergearticles, sysobjects'  
    select @cmd = @cmd + ' where sysmergearticles.nickname = MSmerge_delete_conflicts.tablenick and sysobjects.id = sysmergearticles.objid '
    if @publication <> '%'
        begin
            /*
            ** Parameter Check:  @publication.
            ** Make sure that the publication exists.
            */
            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, 16, -1, @publication)
                    RETURN (1)
                END
		    set @pubidstr = '''' + convert(nchar(36), @pubid) + '''' 
            select @cmd = @cmd + ' and MSmerge_delete_conflicts.pubid = ' + @pubidstr
        end

    if @source_object IS NOT NULL
        begin
            declare @object         sysname  
            declare @owner          sysname  
            declare @tablenick      int
            declare @tablenickstr   nvarchar(11)
            
            select @object = PARSENAME(@source_object, 1)
            select @owner = PARSENAME(@source_object, 2)
            execute dbo.sp_MStablenickname @owner, @object, @tablenick output
            if @tablenick IS NULL
                BEGIN
                    raiserror (20003, 11, -1, @object)
                    RETURN (1)
                END
            set @tablenickstr = convert(nchar, @tablenick)
            select @cmd = @cmd + ' and MSmerge_delete_conflicts.tablenick = '
            select @cmd = @cmd + @tablenickstr
        end

    exec (@cmd)
    if (@@error <> 0)
        RETURN (1)
    
    return 0
go

exec dbo.sp_MS_marksystemobject sp_helpmergedeleteconflictrows 
go

/*
** Name :       sp_deletemergeconflictrow
** Description: This sp deletes the row matching rowguid and origin_datasource
**              If now rows are left in conflict_table
**                  --Set the conflict_table property of the article(s) to NULL
**                  --Drop the conflict table (optionally)
**              If the conflict_table is specified as NULL, the conflict is assumed
**              to be a delete conflict and the row matching rowguid and origin_datasource
**              and source_object is deleted from the MSmerge_delete_conflicts table. The
**              MSmerge_delete_conflicts table is a system table and is not deleted 
**              from the database even if it is empty.
** Parameters:  
**  ----------------------------------------------------------------------------------
**      Name                Datatype                Description
**  ----------------------------------------------------------------------------------
**  a. conflict_table       (sysname)           Indicates the conflict table name. If
                                                '%' then delete from MSmerge_delete_conflicts
**  b. source_object        (nvarchar(386))     Source table 
**  c. rowguid              (uniqueidentifier)  Row identifier for the delete conflict
**  d. origin_datasource    (varchar(255))      Indicates the origin of the conflict                                                         
**  e. drop_table_if_empty  (varchar(10))       Flag indicating if the Conflict_<table>
**                                              is to be dropped if is empty
*/
raiserror('Creating procedure sp_deletemergeconflictrow', 0,1)
GO

CREATE PROCEDURE sp_deletemergeconflictrow(
    @conflict_table         sysname = '%',
    @source_object          nvarchar(386) = NULL,
    @rowguid                uniqueidentifier,
    @origin_datasource      varchar(255),
    @drop_table_if_empty    varchar(10) = 'false')
as
    declare @retcode        smallint
    declare @cmd            nvarchar(4000)  
    declare @rowguidstr     nvarchar(40)
    declare @object         sysname  
    declare @owner          sysname  
    declare @tablenick      int
    declare @tablenickstr   nvarchar(11)


    set @rowguidstr = convert(nchar(36), @rowguid)

    /* Delete conflict from Conflict_<Table> */
    if @conflict_table <> '%'
        begin
            select @cmd = 'delete from '
            select @cmd = @cmd + quotename(@conflict_table) 
            select @cmd = @cmd + ' where origin_datasource = '''
            select @cmd = @cmd + @origin_datasource 
            select @cmd = @cmd + ''' and rowguidcol = '''
            select @cmd = @cmd + @rowguidstr
            select @cmd = @cmd + ''''
            exec (@cmd)
            if @@ERROR<>0 return (1)

            if LOWER(@drop_table_if_empty) = 'true'
                begin
		            select @cmd = 'if not exists (select 1 from ' 
		            select @cmd = @cmd + quotename(@conflict_table) 
		            select @cmd = @cmd + ')'
		            select @cmd = @cmd + ' update sysmergearticles set ins_conflict_proc = NULL, conflict_table = NULL where conflict_table = ' + quotename(@conflict_table)
		            exec (@cmd)
		            if @@ERROR<>0 
		            	return (1)
		            	
                    select @cmd = 'if not exists (select 1 from ' 
                    select @cmd = @cmd + quotename(@conflict_table)
                    select @cmd = @cmd + ')'
                    select @cmd = @cmd + ' drop table '
                    select @cmd = @cmd + quotename(@conflict_table)
                    select @cmd = @cmd + ''
                    exec (@cmd)
                    if @@ERROR<>0 return (1)
                end
        end
    /* Delete conflict from MSmerge_delete_conflicts */
    else
        begin
        	if @source_object is NULL
        		begin
        			raiserror(14043, 16, -1, '@source_object')
        			return (1)
        		end
            select @object = PARSENAME(@source_object, 1)
            select @owner = PARSENAME(@source_object, 2)
            execute @retcode=sp_MStablenickname @owner, @object, @tablenick output
        
            if @tablenick IS NULL or @@ERROR<>0 or @retcode<>0
                BEGIN
                    raiserror (20003, 11, -1, @object)
                    RETURN (1)
                END

            set @tablenickstr = convert(nchar, @tablenick)

            
            select @cmd = 'delete from MSmerge_delete_conflicts'
            select @cmd = @cmd + ' where origin_datasource = '''
            select @cmd = @cmd + @origin_datasource 
            select @cmd = @cmd + ''' and tablenick = '
            select @cmd = @cmd + @tablenickstr
            select @cmd = @cmd + ' and rowguid = '''
            select @cmd = @cmd + @rowguidstr
            select @cmd = @cmd + ''''
            exec (@cmd)
            if @@ERROR<>0 return (1)

        end

go

exec dbo.sp_MS_marksystemobject sp_deletemergeconflictrow 
go

/*
** Name :       sp_getmergedeletetype
** Description: This sp returns the type of delete
** Parameters:  
**  ----------------------------------------------------------------------------------
**      Name                Datatype                Description
**  ----------------------------------------------------------------------------------
**  a. source_object        (nvarchar(386))     Indicates the source object
**  b. rowguid              (uniqueidentifier)  Row identifier for the delete conflict
**  c. delete_type(OUTPUT)  (int)               Code indicating delete type
**                                              User Delete     -   1
**                                              Partial Delete  -   5                                                       
**                                              System Delete   -   6                                                       
*/
raiserror('Creating procedure sp_getmergedeletetype', 0,1)
GO

CREATE PROCEDURE sp_getmergedeletetype(
    @source_object  nvarchar (386),
    @rowguid        uniqueidentifier,
    @delete_type    int OUTPUT
)
as
    declare @object         sysname  
    declare @owner          sysname  
    declare @tablenick      int

    select @object = PARSENAME(@source_object, 1)
    select @owner = PARSENAME(@source_object, 2)
    execute dbo.sp_MStablenickname @owner, @object, @tablenick output
    if @tablenick IS NULL OR @@ERROR<>0
        BEGIN
            raiserror (20003, 11, -1, @object)
            RETURN (1)
        END

    if @rowguid IS NULL 
        begin
            raiserror (14027, 11, -1, '@rowguid')
            return (1)
        end
        
    select @delete_type = type from MSmerge_tombstone where rowguid = @rowguid and tablenick = @tablenick
go

exec dbo.sp_MS_marksystemobject sp_getmergedeletetype 
go

/*
** Name :       sp_mergedummyupdate
** Description: This sp does a dummy updates on the given row so that it will be
**              resent during the next merge.
* Parameters:   
**  ----------------------------------------------------------------------------------
**      Name                Datatype                Description
**  ----------------------------------------------------------------------------------
**  a. source_object        (nvarchar(386))     Indicates the source object
**  b. rowguid              (uniqueidentifier)  Row identifier for the delete conflict
*/
raiserror('Creating procedure sp_mergedummyupdate', 0,1)
GO

CREATE PROCEDURE sp_mergedummyupdate(
    @source_object  nvarchar (386),
    @rowguid        uniqueidentifier
)
as
    declare @object         sysname  
    declare @owner          sysname  
    declare @tablenick      int
    declare @tablenickstr   nvarchar(11)

    select @object = PARSENAME(@source_object, 1)
    select @owner = PARSENAME(@source_object, 2)
    execute dbo.sp_MStablenickname @owner, @object, @tablenick output
    if @tablenick IS NULL or @@ERROR<>0
        BEGIN
            raiserror (20003, 11, -1, @object)
            RETURN (1)
        END
    set @tablenickstr = convert(nchar, @tablenick)

    declare @rowguidstr nvarchar(40)
    if @rowguid IS NULL 
        begin
            raiserror (14043, 11, -1, '@rowguid')
            return (1)
        end
    set @rowguidstr = convert(nchar(36), @rowguid)

    /* 
    ** If the row does not exist in the base table, call sp_MSdummyupdate with metadata_type = 1 (tombstone)
    */
    exec ('if not exists (select 1 from ' + @source_object + ' where rowguidcol = ''' +
        @rowguidstr + ''' ) exec dbo.sp_MSdummyupdate ''' + @rowguidstr + ''',' + @tablenickstr + ', 1')

    /* 
    ** If the row exists in the base table and MSmerge_contents , call sp_MSdummyupdate with metadata_type = 2 (contents)
    */
    exec ('if exists (select 1 from ' + @source_object + ' where rowguidcol = ''' +
        @rowguidstr + ''' ) and exists (select rowguid from MSmerge_contents where rowguid = ''' +
        @rowguidstr + ''' ) exec dbo.sp_MSdummyupdate ''' + @rowguidstr + ''',' + @tablenickstr + ', 2')

    /* 
    ** If the row exists in the base table, but not in MSmerge_contents , call sp_MSdummyupdate with metadata_type = 3 (contents_deferred)
    */
    exec ('if exists (select 1 from ' + @source_object + ' where rowguidcol = ''' +
        @rowguidstr + ''' ) and not exists (select rowguid from MSmerge_contents where rowguid = ''' +
        @rowguidstr + ''' ) exec dbo.sp_MSdummyupdate ''' + @rowguidstr + ''',' + @tablenickstr + ', 3')


go

exec dbo.sp_MS_marksystemobject sp_mergedummyupdate 
go

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

create procedure sp_addtabletocontents 
    (@table_name sysname,
     @owner_name sysname = NULL)
AS

    declare @qualified_table_name nvarchar(270)
    declare @tablenick int
    declare @tablenickstr nvarchar(12)
    declare @repl_nick int
    declare @lineage varbinary(249)
    declare @colv varbinary(2000)
    declare @coltrack int
    declare @objid int
    declare @maxcolid int
    declare @retcode int
    declare @gen int
    
    set nocount on
    create table #temp_cont(rowguid uniqueidentifier)
    execute @retcode = dbo.sp_MSgetreplnick @nickname = @repl_nick output
    if (@@error <> 0) or @retcode <> 0 or @repl_nick IS NULL 
        begin
        RAISERROR (14055, 11, -1)
        RETURN(1)
        end                 

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

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

    set @tablenickstr = convert(nchar, @tablenick)

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

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

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





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

create procedure sp_MSaddpubtocontents 
    (@publication   sysname)
AS
    declare     @pubid  uniqueidentifier
    declare     @tablenick int
    declare     @retcode int
	declare 	@objid int
	declare 	@owner sysname
	declare		@table sysname
	
    set nocount on
    select @pubid = pubid from sysmergepublications 
        where name = @publication and UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name()
    if @pubid is NULL
        begin
            raiserror (20026, 11, -1, @publication)
            return (1)
        end
    
    select @tablenick = min(nickname) from sysmergearticles where pubid = @pubid

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

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

raiserror(15339,-1,-1,'sp_MSget_subtypedatasrc')
GO
CREATE PROCEDURE sp_MSget_subtypedatasrc
@subscriber				sysname,
@distributor			sysname,
@distribdb				sysname,
@datasrctype			int OUTPUT,
@datasrc				sysname OUTPUT
AS
	SET NOCOUNT ON

	DECLARE @provider_name		sysname
	DECLARE @jet_subscriber     tinyint
    DECLARE @oledb_subscriber   tinyint
	DECLARE @oracle_subscriber  tinyint
	DECLARE @db2universal_subscriber tinyint
	DECLARE @distproc           nvarchar(300)
	DECLARE @cmd 				nvarchar(512)
    
	set @jet_subscriber = 2   
    set @oledb_subscriber = 3 
	set @oracle_subscriber = 5 
	set @db2universal_subscriber = 6
	
	create table #subtype
    (
		type tinyint not null,   
    )

	create table #server_info 
	(
		providername nvarchar(256) collate database_default null,
		datasource nvarchar(3750) collate database_default null,			-- this column is nvarchar(4000) in sysservers, but we want to avoid info msg
												                            -- from create table when row size > 8060 bytes.
	)

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

	SELECT @cmd = 'select type from MSsubscriber_info where UPPER(subscriber) = ''' + UPPER(@subscriber) + ''' and UPPER(publisher) = ''' + @@servername + ''''
	
	insert into #subtype exec @distproc @cmd
	if (@@error <> 0 or @@rowcount <> 1)
	begin
		drop table #subtype
		drop table #server_info
		raiserror(14085, 16, -1)
		return 1
	end
	
	select @datasrctype = type from #subtype
	
	SELECT @cmd = 'select providername, datasource from master..sysservers where UPPER(srvname) = ''' + UPPER(@subscriber) + ''''
	
	insert into #server_info exec @distproc @cmd
	if (@@error <> 0 or @@rowcount <> 1)
	begin
		drop table #subtype
		drop table #server_info
		raiserror(14085, 16, -1)
		return 1
	end
	
	select @datasrc = datasource, @provider_name =  providername from #server_info
	
	/*
	** Jet and Oracle subscribers are actually added to MSsubscriber_info as OLE DB subscribers,
	** since they can be used in transactional replication also.
	** Map the type to Jet or Oracle based on OLE DB provider name.
	*/
	
	if (@datasrctype = @oledb_subscriber) 
	BEGIN
		if (upper(@provider_name) = 'MICROSOFT.JET.OLEDB.4.0')
			select @datasrctype = @jet_subscriber
		else if (upper(@provider_name) = 'MSDAORA')
			select @datasrctype = @oracle_subscriber
		else if (upper(@provider_name) = 'DB2OLEDB')
			select @datasrctype = @db2universal_subscriber
	END
    
	drop table #subtype
	drop table #server_info

	return 0
GO
exec dbo.sp_MS_marksystemobject sp_MSget_subtypedatasrc 
go
grant exec on dbo.sp_MSget_subtypedatasrc to public
go

raiserror('Creating procedure sp_addmergealternatepublisher', 0,1)
GO
CREATE PROCEDURE sp_addmergealternatepublisher (
    @publisher            		sysname,             		/* Publisher server */
    @publisher_db           	sysname,             		/* Publisher database */
    @publication                sysname,                    /* Publication name */
    @alternate_publisher	    sysname,                    /* Alternate publisher */
    @alternate_publisher_db     sysname,                    /* Alternate publisher_db */
    @alternate_publication		sysname,					/* Alternate publication */
    @alternate_distributor		sysname,					/* Alternate publisher's Distributor */
    @friendly_name				nvarchar(255)	= NULL,		/* Friendly Name for the association */
    @reserved       			nvarchar(20) = NULL			/* Reserved param for internal use only */
	) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */
    declare @retcode                int
    declare @pubid					uniqueidentifier
    declare @subid					uniqueidentifier
    declare @alternate_subid		uniqueidentifier
    declare	@description			nvarchar(255)
	declare	@allow_synctoalternate	bit
	declare @at_publisher			bit
    declare @subscriber_type        smallint

	set @at_publisher = 1
 	/*
    ** Security Check
    */
    EXEC @retcode = dbo.sp_MSreplcheck_publish
    IF @@ERROR <> 0 or @retcode <> 0
        return (1)
    
	/*
	** Select the subscription's pubid and subid
	*/
    select @pubid = pubid, @allow_synctoalternate = allow_synctoalternate from sysmergepublications  
    	where name = @publication and publisher_db = @publisher_db and LOWER(publisher) = LOWER(@publisher)
    if @pubid is NULL
        begin
            raiserror (20026, 11, -1, @publication)
            return (1)
        end

	/* If this is an external call, make the AtPublisher and allows syncto alternate  check */
    EXEC @retcode = dbo.sp_MScheckatpublisher @pubid
    IF @@ERROR <> 0 or @retcode <>  0
    	set @at_publisher = 0
    	
    IF LOWER(@reserved) <> 'internal'
    	begin
		    IF @at_publisher = 0
		        BEGIN
		            RAISERROR (21215, 16, -1)
		            RETURN (1)
		        END
		    if @allow_synctoalternate = 0
		        begin
		            raiserror (21502, 11, -1, @publication)
		            return (1)
		        end

    	end
    select @subid = subid from sysmergesubscriptions  
    	where LOWER(subscriber_server) = LOWER(@publisher) and db_name = @publisher_db and pubid = @pubid
	/*
	** Select the alternate publisher's subid 
	*/
    select @alternate_subid = subid, @subscriber_type = subscriber_type from sysmergesubscriptions
        where publication = @alternate_publication
        and LOWER(subscriber_server) = LOWER(@alternate_publisher) and db_name = @alternate_publisher_db
    IF @subid is NULL or @alternate_subid IS NULL
        BEGIN
			RAISERROR(21216, 11, -1,  @alternate_publisher, @alternate_publisher_db, @alternate_publication)
            RETURN (1)
        END
    IF @subscriber_type  <> 1
        BEGIN
			RAISERROR(21216, 11, -1,  @alternate_publisher, @alternate_publisher_db, @alternate_publication)
            RETURN (1)
        END

    IF @friendly_name IS NULL
    	begin
		    select @description = description from sysmergesubscriptions where subid = @alternate_subid
			if @description IS NULL
				begin
					select @description = formatmessage(21217, @alternate_publication, @alternate_publisher)
				end
				
		end	    		
	else
		select @description = @friendly_name


	if not exists (select * from MSmerge_altsyncpartners where subid = @subid and alternate_subid = @alternate_subid)
		begin
			insert into MSmerge_altsyncpartners(subid, alternate_subid, description)
				values(@subid, @alternate_subid, @description)
			if @@ERROR <> 0
			    BEGIN
				    GOTO FAILURE
			    END

			update sysmergesubscriptions set distributor = @alternate_distributor where subid = @alternate_subid
			if @@ERROR <> 0 or @@ROWCOUNT <> 1
			    BEGIN
				    GOTO FAILURE
			    END
		end			    
	if not exists (select * from MSmerge_altsyncpartners where subid = @alternate_subid and alternate_subid = @subid)
		begin
			insert into MSmerge_altsyncpartners(subid, alternate_subid, description)
				values(@alternate_subid, @subid, @description)
			if @@ERROR <> 0
			    BEGIN
				    GOTO FAILURE
			    END
		end			    
    return (0)

FAILURE:

	RAISERROR(21243, 11, -1,  @alternate_publisher, @alternate_publisher_db, @alternate_publication)
    RETURN (1)
    
go
exec dbo.sp_MS_marksystemobject sp_addmergealternatepublisher 
go
grant exec on dbo.sp_addmergealternatepublisher to public
go

raiserror('Creating procedure sp_helpmergealternatepublisher', 0,1)
GO
CREATE PROCEDURE sp_helpmergealternatepublisher (
    @publisher            		sysname,             		/* Publisher server */
    @publisher_db           	sysname,             		/* Publisher database */
    @publication                sysname                     /* Publication name */
    ) AS

    SET NOCOUNT ON
    declare @subid				uniqueidentifier
    declare @pubid				uniqueidentifier
    declare @distributor		sysname
    declare @retcode            int
	declare @MShelpmergealternatepublisher TABLE
    (
    	subid					uniqueidentifier 	not null,
		alternate_publisher 	sysname 			collate database_default not null,   
		alternate_publisher_db 	sysname 			collate database_default not null,   
		alternate_publication 	sysname 			collate database_default not null,   
		alternate_distributor 	sysname 			collate database_default null,   
		friendly_name 			nvarchar(255) 		collate database_default null,   
		enabled					bit 				not null
    )
    
	/*
	** Select the subscription's pubid and subid
	*/
	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
    select @subid = subid, @distributor = distributor from sysmergesubscriptions  where 
    	LOWER(subscriber_server) = LOWER(@publisher) and db_name = @publisher_db and pubid = @pubid
    IF @@ERROR <> 0 
        BEGIN
            GOTO FAILURE
        END
    	
    insert into @MShelpmergealternatepublisher (subid, alternate_publisher, alternate_publisher_db, alternate_publication, alternate_distributor, friendly_name, enabled)
    select distinct subs.subid,
	    subs.subscriber_server, 
    	subs.db_name, 
    	subs.publication,
    	subs.distributor,
    	subs.description,
    	1 from sysmergesubscriptions subs
			   	where subs.subid in (select alternate_subid from MSmerge_altsyncpartners where subid = @subid)
	/* select the parent publisher */	   				
    insert into @MShelpmergealternatepublisher (subid, alternate_publisher, alternate_publisher_db, alternate_publication, alternate_distributor, friendly_name, enabled)
    select distinct  pubs.pubid,
    	pubs.publisher, 
    	pubs.publisher_db, 
    	pubs.name,
    	@distributor,
    	pubs.description,
    	1 from sysmergepublications pubs
   				where pubs.name = @publication and
	   				LOWER(publisher) = LOWER(@publisher) and
	   				publisher_db = @publisher_db 

    insert into @MShelpmergealternatepublisher (subid, alternate_publisher, alternate_publisher_db, alternate_publication, alternate_distributor, friendly_name, enabled)
   	select distinct  subs.subid,
   		subs.subscriber_server, 
		subs.db_name, 
		subs.publication,
		subs.distributor,
		subs.description,
		0
		from sysmergesubscriptions subs
		   	where subs.subscriber_type = 1 -- only publisher and republishers
		   		and subs.subid = subs.pubid -- only publisher and republishers
		   		and subs.subid not in (select subid from @MShelpmergealternatepublisher)
		
    select distinct alternate_publisher,
    	alternate_publisher_db,
    	alternate_publication,
    	alternate_distributor,
    	friendly_name,
    	enabled	
    	from @MShelpmergealternatepublisher 
    order by alternate_publisher, alternate_publisher_db, alternate_publication, alternate_distributor
    
    return (0)
FAILURE:
    
    RETURN (1)
go
exec dbo.sp_MS_marksystemobject sp_helpmergealternatepublisher 
go
grant exec on dbo.sp_helpmergealternatepublisher to public
go


raiserror('Creating procedure sp_dropmergealternatepublisher', 0,1)
GO
CREATE PROCEDURE sp_dropmergealternatepublisher (
    @publisher            		sysname,             		/* Publisher server */
    @publisher_db           	sysname,             		/* Publisher database */
    @publication                sysname,                    /* Publication name */
    @alternate_publisher	    sysname,                    /* Alternate publisher */
    @alternate_publisher_db     sysname,                    /* Alternate publisher_db */
    @alternate_publication		sysname						/* Alternate publication */
    ) AS

    SET NOCOUNT ON
	declare @retcode 				int
    declare @subid					uniqueidentifier
    declare @alternate_subid		uniqueidentifier
	
    /*
    ** Security Check
    */
    EXEC @retcode = dbo.sp_MSreplcheck_publish
    IF @@ERROR <> 0 or @retcode <> 0
        return (1)

	/* Check is there is an invalid attempt to drop the default publiser/publisher_db/publication */
	if (LOWER(@publisher) = LOWER(@alternate_publisher)) AND
		(@publisher_db = @alternate_publisher_db) AND
		(@publication = @alternate_publication)
		begin
			RAISERROR(21252, 11, -1,  @alternate_publisher, @alternate_publisher_db, @alternate_publication)
	        return (1)
		end

    select @subid = subid from sysmergesubscriptions  
    	where LOWER(subscriber_server) = LOWER(@publisher) and db_name = @publisher_db and publication = @publication
	/*
	** Select the alternate publisher's subid 
	*/
    select @alternate_subid = subid from sysmergesubscriptions
        where publication = @alternate_publication
        and LOWER(subscriber_server) = LOWER(@alternate_publisher) and db_name = @alternate_publisher_db
    IF @subid is NULL or @alternate_subid IS NULL
        BEGIN
			RAISERROR(21216, 11, -1,  @alternate_publisher, @alternate_publisher_db, @alternate_publication)
            RETURN (1)
        END


	if exists (select * from MSmerge_altsyncpartners where subid = @subid and alternate_subid = @alternate_subid)
		begin
			delete from MSmerge_altsyncpartners where @subid = subid and alternate_subid = @alternate_subid
			if @@ERROR <> 0 or @@rowcount <> 1
			    BEGIN
				    GOTO FAILURE
			    END
		end			    
    return (0)

FAILURE:
	RAISERROR(21251, 11, -1,  @alternate_publisher, @alternate_publisher_db, @alternate_publication)
    RETURN (1)
go

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

raiserror(15339,-1,-1,'sp_MScomputemergearticlescreationorder')
GO
CREATE PROCEDURE sp_MScomputemergearticlescreationorder
    @publication sysname
AS
    SET NOCOUNT ON
    DECLARE @pubid uniqueidentifier 
    DECLARE @max_level int
    DECLARE @current_level int
    DECLARE @update_level int
    DECLARE @limit int

    SELECT @pubid = NULL
    -- Get the pubid from sysmergepublications 
    SELECT @pubid = pubid 
      FROM sysmergepublications
     WHERE name = @publication
       AND UPPER(publisher) = UPPER(@@SERVERNAME)
       AND publisher_db = DB_NAME()

    IF @@ERROR <> 0
        RETURN (1)

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

    -- Find out the total number of articles in this publication and
    -- compute the maximum tree height based on the number of articles in 
    -- the publication. Here, the tree height is counted from the
    -- leaf-nodes towards the root(s) starting from @max_level
    SELECT @max_level = COUNT(*) + 10,
           @limit = 2 * COUNT(*) + 11 
      FROM sysmergeextendedarticlesview 
     WHERE pubid = @pubid
 
    IF @@ERROR <> 0
    BEGIN
        RETURN (1)
    END
   
    -- The following temp table contains the minimal amount of 
    -- article information that we want to keep around and the current
    -- computed tree level of the article
    CREATE TABLE #article_level_info
    (
        article         sysname collate database_default not null,
        source_objid    INT     NOT NULL,
        tree_level      INT     NOT NULL,
        nickname        INT     NOT NULL,
        major_type      TINYINT NOT NULL  -- 1-view&func, 0-other
    )  
   
    CREATE CLUSTERED INDEX ucarticle_level_info 
        ON #article_level_info(source_objid)

    IF @@ERROR <> 0
    BEGIN
        GOTO Failure
    END

    -- Populate the article level info table. All articles will be
    -- assigned 0 as their initial tree level. Having 
    -- a tree level of 0 means that the algorithm hasn't discovered 
    -- any objects that the article depends on within the publication.

    INSERT INTO #article_level_info 
    SELECT name, objid, 0, ISNULL(nickname, 5*@max_level),
        CASE type
            WHEN 0x40 THEN 1
            WHEN 0x80 THEN 1
            ELSE 0
        END 
      FROM sysmergeextendedarticlesview
     WHERE pubid = @pubid
      
    -- To jump-start the algorithm, update the tree_level of 
    -- all articles with no dependency to @max_level.

    UPDATE #article_level_info 
       SET tree_level = @max_level
     WHERE NOT EXISTS (SELECT * 
                         FROM sysdepends 
                        WHERE source_objid = id)
    IF @@ERROR <> 0
        GOTO Failure

    -- For each increasing tree level starting from @max_level, update the 
    -- the tree_level of articles depending on objects at the current
    -- level to current level + 1
    SELECT @current_level = @max_level
    WHILE 1 = 1
    BEGIN
        SELECT @update_level = @current_level + 1

        UPDATE #article_level_info
           SET tree_level = @update_level
          FROM #article_level_info 
        INNER JOIN sysdepends d
            ON #article_level_info.source_objid = d.id 
        INNER JOIN #article_level_info ali1
            ON (d.depid = ali1.source_objid       
               AND ali1.tree_level = @current_level)
    
        -- Terminate the algorithm if we cannot find any articles 
        -- depending on articles at the current level     
        IF @@ROWCOUNT = 0
            GOTO PHASE1

        IF @@ERROR <> 0
            GOTO Failure

        SELECT @current_level = @current_level + 1

        -- Although there should not be any circular 
        -- dependencies among the articles, the following
        -- check is performed to guarantee that 
        -- the algorithm will terminate even if there 
        -- is circular dependency among the articles
        
        -- Note that with at least one node per level,
        -- the current level can never exceed the total 
        -- number of articles (nodes) unless there is
        -- circular dependency among the articles.
        
        -- @limit is defined to be # of articles + 1
        -- although @limit = # of articles - 1 will be
        -- sufficient. This is to make absolutely sure that 
        -- the algorithm will never terminate too early

        IF @current_level > @limit
            GOTO PHASE1
    END

PHASE1:
    
    -- There may be interdependencies among articles 
    -- that haven't been included in the previous calculations so
    -- we compute the proper order among these articles here.
    SELECT @limit = @max_level - 9
    SELECT @current_level = 0
    WHILE 1 = 1
    BEGIN
        SELECT @update_level = @current_level + 1
        
        UPDATE #article_level_info 
           SET tree_level = @update_level
          FROM #article_level_info
        INNER JOIN sysdepends d
            ON (#article_level_info.source_objid = d.id
                AND #article_level_info.tree_level < @max_level) 
        INNER JOIN #article_level_info ali1
            ON (d.depid = ali1.source_objid
                AND ali1.tree_level = @current_level)
        IF @@ROWCOUNT = 0
            GOTO PHASE2
        
        IF @@ERROR <> 0
            GOTO Failure

        SELECT @current_level = @current_level + 1
        IF @current_level > @limit
            GOTO PHASE2
    END         

PHASE2:

    -- Select the articles out of #article_level_info 
    -- in ascending order of tree_level. This will give
    -- the proper order in which articles can be created
    -- without violating the internal dependencies among
    -- the themselves. Note that this algorithm still allows 
    -- unresolved external references outside the publication.
    -- All this algorithm can guarantee is that all articles will
    -- be created successfully using the resulting order if 
    -- there is no dependent object outside the publication. 

    SELECT article
      FROM #article_level_info
    ORDER BY major_type ASC, tree_level ASC, nickname ASC

    DROP TABLE #article_level_info
    RETURN (0)

Failure:

    DROP TABLE #article_level_info
    RETURN (1)
GO

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

raiserror(15339,-1,-1,'sp_MScomputemergeunresolvedrefs')
GO
CREATE PROCEDURE sp_MScomputemergeunresolvedrefs
    @publication sysname, -- Must provide the publication name
    @article     sysname = '%' -- '%' means all articles in the specified publication, otherwise an exact match is performed
AS
    SET NOCOUNT ON 
    DECLARE @pubid uniqueidentifier 
    
    -- Parameter check: @publication

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

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

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

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

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

CREATE PROCEDURE sp_MSCheckmergereplication
AS
    SET NOCOUNT ON
    declare @category int
    declare @db_name sysname

    select @db_name=db_name()
	select @category=category FROM master..sysdatabases WHERE name = @db_name collate database_default
	if @category & 4 = 0
		begin
			raiserror(21147, 16, -1, @db_name)
			return (1)
		end
	return (0)	
go
exec dbo.sp_MS_marksystemobject sp_MSCheckmergereplication
go

grant execute on dbo.sp_MSCheckmergereplication to public
go

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

CREATE PROCEDURE sp_MSgetpubinfo (
    @publication    sysname,    /* The publication name */
    @publisher		sysname,
    @pubdb			sysname
    ) AS

    SET NOCOUNT ON

	select pubid from sysmergepublications where name = @publication and
			publisher = @publisher and publisher_db = @pubdb
			
go
exec dbo.sp_MS_marksystemobject sp_MSgetpubinfo
go

grant execute on dbo.sp_MSgetpubinfo to public
go

raiserror('Creating procedure sp_MSaddmergedynamicsnapshotjob', 0,1)
go
--
-- Name: sp_MSaddmergedynamicsnapshotjob
--
-- Description: This procedure sets up a SQL Server Agent job for dynamic 
--              snapshot generation and associates a row in 
--              MSdynamicsnapshotjobs for the job to the specified publication.
--
-- Notes: 1) If a local path is specified for the @dynamic_snapshot_location,
--           the local path of the Distribution server will be used. 
--        2) This procedure will not check whether the given path is already in
--           use by another dynamic snapshot generation job. Sharing the same
--           dynamic snapshot location among different dynamic snapshot 
--           generation jobs can lead to file corruption and/or snapshot files 
--           being overwritten.  
--        3) A regular snapshot job must be added for the publication before
--           a dynamic snapshot generation job can be scheduled.
--        4) This procedure will not check for the existence of the given
--           dynamic snapshot location.
--        5) The specified publication must be enabled for dynamic filtering.
--        6) If @dynamic_snapshot_jobname is specified, it must be unique 
--           among all the jobs at the distributor's msdb. If it is left
--           unspecified, a job name will be generated according to the
--           following rule:
--           'dyn_' + (job name for the regular snapshot job) + (guid string)
--           Note that (job name for the regular snapshot job) can be truncated
--           if the resulting name is too long.
-- 
-- Parameters: @publication sysname (mandatory)
--             @dynamic_filter_login sysname (optional, default null)
--             @dynamic_filter_hostname sysname (optional, default null)
--             @dynamic_snapshot_location nvarchar(255) (mandatory)
--             @dynamic_snapshot_jobid (optional, output, default null)
--             @dynamic_snapshot_jobname (optional, output, default null)
--             Scheduling information:
--             @frequency_type int (optional, default 4 == Daily)
--             @frequency_interval int (optional, default 1 == Every day)
--             @frequency_subday int (optional, default 4 (Sub interval = Minute))
--             @frequency_subday_interval int (optional, default 5 == Every five minutes)
--             @frequency_relative_interval int (optional, default 1) 
--             @frequency_recurrence_factor int (optional, default 0) 
--             @active_start_date int (optional, default 0 == Today)
--             @active_end_date int (optional, default 99991231) 
--             @active_start_time_of_day int (optional, default 0 == Now)
--             @active_end_time_of_day int (optional, default 235959)
--
-- Returns: 0 - succeeded
--          1 - failed
--
-- Result set (upon successful completion of the operation):
--          dynamic_snapshot_jobname sysname
--          dynamic_snapshot_jobid uniqueidentifier
-- Security: Only members of the 'sysadmin' server role and members of the
--           'db_owner' database role can invoke this procedure successfully.
--           Security check is performed inside the procedure.
--
create procedure sp_MSaddmergedynamicsnapshotjob (
    @publication sysname,
    @dynamic_filter_login sysname = null,
    @dynamic_filter_hostname sysname = null,
    @dynamic_snapshot_location nvarchar(255), 
    @dynamic_snapshot_jobname sysname = null output,
    @dynamic_snapshot_jobid uniqueidentifier = null output,

    -- Scheduling information
    @frequency_type              int = 4,
    @frequency_interval          int = 1,
    @frequency_subday            int = 4,
    @frequency_subday_interval   int = 5,
    @frequency_relative_interval int = 1, 
    @frequency_recurrence_factor int = 0,
    @active_start_date           int = 0,
    @active_end_date             int = 99991231,
    @active_start_time_of_day    int = 0, 
    @active_end_time_of_day      int = 235959
    )
as
begin
    set nocount on

    declare @retcode int
    declare @pubid uniqueidentifier
    declare @dynamic_filters bit
    declare @snapshot_jobid uniqueidentifier 
    declare @command_line nvarchar(4000)
    declare @distribdb sysname
    declare @rpcsrvname sysname
    declare @distributor sysname
    declare @fjobcreated bit
    declare @distproc nvarchar(4000)
    declare @id int 

    -- Initializations
    select @retcode = 0
    select @pubid = null
    select @dynamic_filters = 0
    select @snapshot_jobid = null
    select @fjobcreated = 0

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

    -- Make sure that caller is a member of 'sysadmins' or 'db_owner'
    exec @retcode = dbo.sp_MSreplcheck_publish
    if @@error <> 0 or @retcode <> 0
        return (1)
    
    -- Check that the given dynammic filter login is in the PAL
    exec @retcode = sp_check_publication_access
        @publication = @publication,
        @given_login = @dynamic_filter_login
    if @retcode <> 0 or @@error <> 0
        return 1

    select @dynamic_snapshot_location = ltrim(@dynamic_snapshot_location)
    -- Specified @dynamic_snapshot_location must be non-empty
    if @dynamic_snapshot_location is null or 
       @dynamic_snapshot_location = N''
    begin
        raiserror(21321, 16, -1)         
        return (1)
    end

    -- Verify that the given publication exists and get the pubid at the
    -- same time
    select @pubid = pubid,
           @dynamic_filters = dynamic_filters
      from dbo.sysmergepublications
     where upper(publisher) = upper(@@servername)
       and publisher_db = db_name()
       and name = @publication

    if @pubid is null
    begin
        raiserror(20026, 16, -1, @publication) 
        return (1)        
    end

    -- The given publication must be enabled for dynamic filtering
    if @dynamic_filters <> 1
    begin
        raiserror(21323, 16, -1)
        return (1)
    end

    -- A regular snapshot job is required before a dynamic snapshot job
    -- can be scheduled
    select @snapshot_jobid = snapshot_jobid from MSmerge_replinfo
     where repid = @pubid

    if @snapshot_jobid is null
    begin
        raiserror(21324, 16, -1)            
        return (1)
    end

    -- The given dynamic snapshot job name cannot be '%' and it cannot match
    -- any of the existing dynamic snapshot job name
    if @dynamic_snapshot_jobname = '%'
    begin
        raiserror(21327, 16, -1)
        return (1)
    end

    if exists (select * 
                 from MSdynamicsnapshotjobs 
                where name = @dynamic_snapshot_jobname)
    begin
        raiserror(21328, 16, -1, @dynamic_snapshot_jobname)
        return (1)
    end    


    -- 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_MSadddynamicsnapshotjobatdistributor'

    exec @retcode = @distproc 
        @regular_snapshot_jobid = @snapshot_jobid,
        @dynamic_filter_login = @dynamic_filter_login,
        @dynamic_filter_hostname = @dynamic_filter_hostname,
        @dynamic_snapshot_location = @dynamic_snapshot_location,
        @dynamic_snapshot_jobname = @dynamic_snapshot_jobname output,
        @dynamic_snapshot_jobid = @dynamic_snapshot_jobid output,
        @freqtype = @frequency_type,
        @freqinterval = @frequency_interval,
        @freqsubtype = @frequency_subday,
        @freqsubinterval = @frequency_subday_interval,
        @freqrelativeinterval = @frequency_relative_interval,
        @freqrecurrencefactor = @frequency_recurrence_factor,
        @activestartdate = @active_start_date,
        @activeenddate = @active_end_date,
        @activestarttimeofday = @active_start_time_of_day,
        @activeendtimeofday = @active_end_time_of_day
    
    if @retcode <> 0 or @@error <> 0
        goto Failure

    select @fjobcreated = 1

    insert MSdynamicsnapshotjobs 
    (name, pubid, job_id, dynamic_filter_login, dynamic_filter_hostname, 
     dynamic_snapshot_location)
    values
    (@dynamic_snapshot_jobname, @pubid, @dynamic_snapshot_jobid, 
     @dynamic_filter_login, @dynamic_filter_hostname, 
     @dynamic_snapshot_location)    
    
    if @@error <> 0
    begin
        goto Failure
    end

    select @id = @@identity    

    select 'id'                       = @id,
           'dynamic_snapshot_jobname' = @dynamic_snapshot_jobname,
           'dynamic_snapshot_jobid' = @dynamic_snapshot_jobid
    return 0

Failure:
    if @fjobcreated = 1
    begin
        select @distproc = rtrim(@rpcsrvname) + N'.' + @distribdb + N'.' + N'dbo.sp_MSdeleterepljob'
        exec @distproc @job_id = @dynamic_snapshot_jobid
    end
    return @retcode
end
go
exec dbo.sp_MS_marksystemobject sp_MSaddmergedynamicsnapshotjob
go

grant execute on dbo.sp_MSaddmergedynamicsnapshotjob to public
go
 

raiserror('Creating procedure sp_MSdropmergedynamicsnapshotjob', 0,1)
go
--
