/*
** Copyright Microsoft, Inc. 1998-2000
** All Rights Reserved.
*/

set nocount on
go

execute dbo.sp_configure 'update',1
go
reconfigure with override
go

set ANSI_NULLS off
go

use master
go

dump tran master with no_log
go



/* 
** Drop the stored procedures in this script using the old dropping SP 
** and then drop itself
*/
if exists (select * from sysobjects
	where type = 'P '
			and name = 'sp_MSdrop_rlcore')
begin
	drop procedure sp_MSdrop_rlcore
end

/*
** Create stored procedures to drop the stored procedures
** created by this script
*/

raiserror('Creating procedure sp_MSdrop_rlcore', 0,1)
GO
create procedure sp_MSdrop_rlcore
as

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

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

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

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

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

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

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

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

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

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

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

	if exists (select * from sysobjects
			where type = 'P'
				and name = 'sp_MSrefcnt')
		drop procedure sp_MSrefcnt
	
	if exists (select * from sysobjects
			where type = 'P'
				and name = 'sp_MSgentablenickname')
		drop procedure sp_MSgentablenickname
	
	if exists (select * from sysobjects
			where type = 'P'
				and name = 'sp_MStablenickname')
		drop procedure sp_MStablenickname
	
	if exists (select * from sysobjects
		where type = 'P'
			and name = 'sp_MStablenamefromnick')
		drop procedure sp_MStablenamefromnick
	
	if exists (select * from sysobjects
			where type = 'P'
				and name = 'sp_MSmakegeneration')
		drop procedure sp_MSmakegeneration
	
	if exists (select * from sysobjects
			where type = 'P'
				and name = 'sp_MSfixlineageversions')
		drop procedure sp_MSfixlineageversions
	
	if exists (select * from sysobjects
			where type = 'P'
				and name = 'sp_MSaddupdatetrigger')
		drop procedure sp_MSaddupdatetrigger
	
	if exists (select * from sysobjects
			where type = 'P'
				and name = 'sp_MSaddmergetriggers')
		drop procedure sp_MSaddmergetriggers
	
	if exists (select * from sysobjects
				where type = 'P' and
				name = 'sp_MSmaptype')
		drop procedure sp_MSmaptype 

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

	if exists( select * from sysobjects where type = 'P ' and name = 'sp_showrowreplicainfo')
	begin
	    drop procedure sp_showrowreplicainfo
	end
go
exec dbo.sp_MS_marksystemobject sp_MSdrop_rlcore
go
exec dbo.sp_MSdrop_rlcore
go

dump tran master with no_log
go

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


create proc sp_MSguidtostr (@guid uniqueidentifier, @mystr nvarchar(32) output)
	as
declare @guidstr nvarchar(36)
set @guidstr = convert(nchar(36), @guid)
set @mystr = substring(@guidstr, 1, 8) + substring(@guidstr, 10, 4) +
	substring(@guidstr, 15, 4)+ substring(@guidstr, 20, 4)+ substring(@guidstr, 25, 12)
go
exec dbo.sp_MS_marksystemobject sp_MSguidtostr
go

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

CREATE PROCEDURE sp_MSgetconflicttablename
@publication sysname,
@source_object nvarchar(520),
@conflict_table sysname = NULL OUTPUT
AS

declare @objid 				int
declare @retcode			int
declare @current_conflict	sysname
declare @object_name		sysname
declare @name_out			sysname
declare @pubid				uniqueidentifier
declare @article			sysname
declare @tablename          sysname
declare @ownername          sysname

select @tablename = null
select @ownername = null

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

select @objid = object_id(@source_object)

-- Raise error if objid is still null at this point
if @objid is null
begin
    declare @db_name sysname
    select @db_name = db_name()
    raiserror(15009,16, -1,@source_object,@db_name)
    return 1  
end
select @article=name, @current_conflict = conflict_table from sysmergearticles where objid = @objid and pubid=@pubid
if @current_conflict is not NULL
	begin
		if @conflict_table is NULL
			select @current_conflict
		else	
			select @conflict_table = @current_conflict
		return (0)
	end

if len(@publication) + len(@article) > 118		 -- SYSNAME minus 'conflict_'
	select @object_name = 'conflict_' + convert(nvarchar(59), @publication) + '_' + convert(nvarchar(59), @article)
else
	select @object_name = 'conflict_' + @publication + '_' + @article
exec @retcode = dbo.sp_MSuniqueobjectname @object_name, @conflict_table OUTPUT

GO

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

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

CREATE PROCEDURE sp_MSuniqueobjectname 
	@name_in sysname,
 	@name_out sysname = NULL output
AS

 	declare @name_out_local sysname
	declare @subschars 		sysname
	declare @curchar 		nchar(1)
	declare @substidx 		int
	declare @pos 			int

	select @subschars = 'abcdefghijklmnopqrstuvwxyz'
	select @name_out_local = @name_in


	select @substidx = 0
	select @pos = 1

	while exists (select * from sysobjects where name = @name_out_local)
		begin
		if @substidx > 25 
			begin
			select @pos = @pos + 1
			select @substidx = 1
			end
		else
			select @substidx = @substidx + 1
		select @curchar = substring(@subschars, @substidx, 1)
		select @name_out_local = stuff(@name_out_local, @pos, 1, @curchar)
		end

	if @name_out IS NULL
		select @name_out_local
	else
		select @name_out = @name_out_local

	return (0)
GO

exec dbo.sp_MS_marksystemobject sp_MSuniqueobjectname 
go


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

CREATE PROCEDURE sp_MSuniquetempname @name_in sysname,
 	@name_out sysname output
AS

	declare @subschars nvarchar(26)
	declare @curchar nchar(1)
	declare @substidx int
	declare @pos int
	declare @saverr int

	select @subschars = 'abcdefghijklmnopqrstuvwxyz'
	select @saverr = @@error
	if (@saverr <> 0)
		goto EXIT_LABEL
	select @name_out = @name_in
	select @saverr = @@error
	if (@saverr <> 0)
		goto EXIT_LABEL

	select @saverr = @@error
	if (@saverr <> 0)
		goto EXIT_LABEL
	select @substidx = 0
	select @pos = 3

	while exists (select * from tempdb..sysobjects where name = @name_out)
		begin
			if @substidx > 25 
				begin
				select @pos = @pos + 1
				select @substidx = 1
				end
			else
				select @substidx = @substidx + 1
			select @curchar = substring(@subschars, @substidx, 1)
			select @saverr = @@error
			if (@saverr <> 0)
				goto EXIT_LABEL
			select @name_out = stuff(@name_out, @pos, 1, @curchar)
			select @saverr = @@error
			if (@saverr <> 0)
				goto EXIT_LABEL
		end
		return (0)
EXIT_LABEL:
	if (@saverr <> 0)
		begin
			RAISERROR(15001, 16, -1, 'sp_MSuniquetempname')
			return (1)
		end
			
GO
exec dbo.sp_MS_marksystemobject sp_MSuniquetempname 
go

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

create procedure sp_MSuniquecolname
	@table_name nvarchar(270),  -- this is a qualified_name 		
	@base_colname sysname, 
	@unique_colname sysname output
as
begin
	
	set nocount on
	
	declare @icol_suffix int
	
	select @icol_suffix = ( max(colorder) + 1 ) from syscolumns 
		where id = object_id( @table_name )

	select @unique_colname = @base_colname 

	while exists( 
			select * from syscolumns 
				where id = object_id( @table_name ) 
				  and name = @unique_colname
	)
	begin
		select @unique_colname = @base_colname + convert( nvarchar(40), @icol_suffix )  	
		select @icol_suffix = @icol_suffix * ( @@spid + 1 )
	end
	
end
go

exec dbo.sp_MS_marksystemobject sp_MSuniquecolname
go

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

SET ANSI_NULLS ON 
SET QUOTED_IDENTIFIER ON
go
create procedure sp_MSaddguidcolumn
	@source_owner	sysname,
	@source_table 	sysname 		/* table name */
as
	declare @rowguidcol 		sysname
	declare @id					int
	declare @qualified_name		nvarchar(270)
	declare @columns			varbinary(128)
	declare @retcode			int
	declare @colid				int
	declare @pubid				uniqueidentifier

	set nocount on
		
	select @qualified_name = QUOTENAME(@source_owner) + '.' + QUOTENAME(@source_table)
	select @id = object_id(@qualified_name)
	
	/* Alter the source table to add a rowguid column */
	begin tran
	exec dbo.sp_MSunmarkreplinfo @source_table, @source_owner
	if @@ERROR <>0 goto UNDO

	if not exists (select * from syscolumns
		where ObjectProperty(object_id(@qualified_name), 'tablehasrowguidcol')=1)
	begin
		exec dbo.sp_MSuniquecolname @qualified_name, 'rowguid', @rowguidcol output
		exec ('alter table ' + @qualified_name + ' add ' + @rowguidcol + ' uniqueidentifier ROWGUIDCOL default newid() not null')
		if @@ERROR<>0 goto UNDO
	end
	exec dbo.sp_MSunmarkreplinfo @source_table, @source_owner, 1
	if @@ERROR<>0 goto UNDO
	
	commit tran
	return (0)
UNDO:
	if @@TRANCOUNT = 1 
		ROLLBACK TRANSACTION 
	else
		COMMIT TRANSACTION
	return (1)	
go

exec dbo.sp_MS_marksystemobject sp_MSaddguidcolumn 
go
grant exec on dbo.sp_MSaddguidcolumn to public
go
SET ANSI_NULLS OFF
GO
raiserror('Creating procedure sp_MSprepare_mergearticle', 0,1)
GO

SET ANSI_NULLS ON 
SET QUOTED_IDENTIFIER ON
go
create procedure sp_MSprepare_mergearticle
	@source_owner	sysname,
	@source_table 	sysname, 		/* table name */
	@publication 	sysname,
	@qualified_tablename 	nvarchar(270)
as
	declare @retcode int
	exec @retcode = sp_MSaddguidcolumn @source_owner, @source_table

	if @@error <> 0 or @retcode <> 0
		return (1)

	exec @retcode = sp_MSaddguidindex @publication, @source_owner, @source_table

	if @@error <> 0 or @retcode <> 0
		return (1)

	exec @retcode = sp_MSaddmergetriggers @qualified_tablename

	if @@error <> 0 or @retcode <> 0
		return (1)
go

exec dbo.sp_MS_marksystemobject sp_MSprepare_mergearticle 
go
grant exec on dbo.sp_MSprepare_mergearticle to public
go
SET ANSI_NULLS OFF
GO
raiserror('Creating procedure sp_MSgetcolumnlist', 0,1)
GO               

create procedure sp_MSgetcolumnlist(
@pubid				uniqueidentifier,
@column_list 		nvarchar(4000) OUTPUT,
@source_objid		int
)
AS
/* 
** if it is not vertically partitioned, then get all columns 
** else get the column list as given in columns of sysmergearticles
*/
declare @bitset		int
declare @columns	varbinary(125)
declare @setcolcnt	int
declare @colcnt		int
declare @colname	nvarchar(140)
declare @colid		int
declare @unqual_sourcename	sysname
declare @qual_sourcename	nvarchar(140)

select @unqual_sourcename=object_name(@source_objid)
select @qual_sourcename = QUOTENAME(@unqual_sourcename)
select @columns = columns from sysmergearticles where objid = @source_objid and pubid=@pubid
select @column_list = ''
select @setcolcnt	= 0
select @colcnt = count(*) from syscolumns where id = @source_objid
declare collist CURSOR LOCAL FAST_FORWARD FOR 
   	select name, colid from syscolumns where id = @source_objid order by colid ASC
FOR READ ONLY
open collist
fetch collist into @colname, @colid
WHILE (@@fetch_status <> -1)
BEGIN
	exec @bitset = sp_MStestbit @columns, @colid
--	if @bitset<>0
	if @bitset<>0 or @columns is null or @columns = ''
	begin
		select @colname = QUOTENAME(@colname) --previously we use rowguidcol to replace 'rowguid'
		if @column_list=''					  --which can cause problems and is not necessary.
			select @column_list = @qual_sourcename + '.' + @colname
		else
			select @column_list = @column_list + ', ' + @qual_sourcename + '.' + @colname	
		select @setcolcnt = @setcolcnt + 1
	end					
fetch next from collist into @colname, @colid			
END
close collist
deallocate collist
if @setcolcnt = @colcnt
	select @column_list = ' * '
GO
exec dbo.sp_MS_marksystemobject sp_MSgetcolumnlist 
go
grant exec on dbo.sp_MSgetcolumnlist to public
go

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

SET ANSI_NULLS ON 
SET QUOTED_IDENTIFIER ON
go
create procedure sp_MSaddguidindex
	@publication 	sysname,
	@source_owner	sysname,
	@source_table 	sysname		
as
	set nocount on
	declare @indexname 	nvarchar(270)
	declare @colname	nvarchar(270)
	declare @retcode int
	declare @qualified_name nvarchar(270)
	declare @colid		int
	declare @columns	varbinary(125)
	declare @id			int
	declare @pubid		uniqueidentifier
  	declare @mergepublish	int


  	select @mergepublish = 0x4000
  	
	select @pubid = pubid from sysmergepublications 
		where name=@publication and publisher=@@SERVERNAME and publisher_db=db_name()
    select @qualified_name = QUOTENAME(@source_owner) + '.' + QUOTENAME(@source_table)
    select @id = object_id(@qualified_name)

    select @colid=colid from syscolumns where id=@id and columnproperty(@id, name, 'isrowguidcol')=1
    update syscolumns set colstat=colstat | @mergepublish where id=@id and colid=@colid
	if @@ERROR<>0
		return (1)

	/* Update the columns bitmaps in all articles at the current publisher that share the same table */
    declare artpubs CURSOR LOCAL FAST_FORWARD FOR 
        select DISTINCT pubid from sysmergearticles where objid=@id and pubid
        	in ( select pubid from sysmergepublications where UPPER(publisher) = UPPER(@@SERVERNAME) 
        		and publisher_db = db_name())
    FOR READ ONLY
    open artpubs
    fetch next from artpubs into @pubid
     
    while (@@fetch_status <> -1)
        begin
            select @columns=columns from sysmergearticles where objid=@id and pubid=@pubid
		    exec @retcode = sp_MSsetbit @columns OUTPUT, @colid
			if @retcode<>0 or @@ERROR<>0
				return (1)
		    update sysmergearticles set columns=@columns where objid=@id and pubid = @pubid
			if @@ERROR<>0
				return (1)
            fetch next from artpubs into @pubid
        end 
    close artpubs
    deallocate artpubs

   
	select @indexname = 'index_' + convert(nvarchar(36), @id)

	/* Make sure index name is unique */
	exec @retcode = dbo.sp_MSuniqueobjectname @indexname, @indexname output
	if @retcode <>0 return (1)

	select @colname=name from syscolumns where id=@id and columnproperty(@id, name, 'isrowguidcol')=1
	if (@colname is null)
		return (1)
    
	/* Alter the source table to add a rowguid column */
	if (not exists (select * from sysindexes
		where id = object_id(@qualified_name) and
			@colname = index_col(@qualified_name, indid, 1)
			and indexproperty(id, name, 'IsUnique') = 1 and 
			index_col(@qualified_name, indid, 2) is null))
    begin
    	select @colname = N'[' + replace(@colname, N']', N']]') + N']'
        exec ('create unique index ' + @indexname  + ' on ' + @qualified_name + N' (' +  @colname + N')')
		if @@ERROR <>0 return (1) 
    end
	return (0)
go


exec dbo.sp_MS_marksystemobject sp_MSaddguidindex
go
grant exec on dbo.sp_MSaddguidindex to public
go
SET ANSI_NULLS OFF

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

create procedure sp_MSrefcnt
	@objid int, @refcnt int output
as
	set @refcnt = 0
	create table #temprefs (objid int NOT NULL)
	insert into #temprefs select distinct rkeyid from sysreferences where fkeyid = @objid

	while (@@rowcount > 0)
		insert into #temprefs select distinct rkeyid from
			sysreferences where fkeyid in (select objid from #temprefs) and
				rkeyid not in (select objid from #temprefs)

	select @refcnt = count(*) from #temprefs
	return (0)
GO

exec dbo.sp_MS_marksystemobject sp_MSrefcnt
go


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

create procedure sp_MSgentablenickname
			@tablenick	int	output, @nickname int, @objid int
as

	declare @refcnt int
	declare @retval int
	
	/* Create a tablenickname from the following formula
	**		1.  Get the refcnt, use it for the high order digits so
	**		    that processing inserts by ascending tablenickname works well
	** 			and processing deletes by descending tablenickname works well.
	**		2.  Use a couple of digits from the nickname so that it is less likely
	**			to collide with tablenickname generated at another merge publisher.
	**		3.  Increment as necessary to make it unique within the publication and database.
	*/
	exec @retval = dbo.sp_MSrefcnt @objid, @refcnt output
	if @retval <> 0 return (1)

	if @nickname < 0
		set @nickname = 0 - @nickname

	/* the biggest value for int is 2147483647, we therefore use another algorithm when
	** @refcnt is bigger than 200
	*/
	if @refcnt < 200
	begin
		set @tablenick = 1000 * ((@refcnt * 10000) + (@nickname % 10000))
		while exists (select * from sysmergearticles where nickname = @tablenick)
			set @tablenick = @tablenick + 1
	end
	else
		select @tablenick=max(nickname) + 1 from sysmergearticles
	return (0)
go

exec dbo.sp_MS_marksystemobject sp_MSgentablenickname
go

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

create procedure sp_MStablenickname 
	@owner		sysname,
	@tablename 	sysname,
	@nick int 	output
as
	declare @qualified_name nvarchar(270)
	if @owner is not null
		select @qualified_name = QUOTENAME(@owner) + '.' + QUOTENAME(@tablename)
	else 
		select @qualified_name = QUOTENAME(@tablename)
		
	select @nick = nickname from sysmergearticles a, sysobjects o
			where a.objid = o.id and o.id = OBJECT_ID(@qualified_name) and
				(user_name(uid) = @owner or @owner is null)
	if @nick is NULL return (1) 			
	return (0)
go

exec dbo.sp_MS_marksystemobject sp_MStablenickname 
go

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

create procedure sp_MStablenamefromnick
	@nick int,
	@tablename nvarchar(270) output,
	@pubid uniqueidentifier = NULL
as
	declare @owner sysname
	declare @table sysname
	if (@pubid is null)
		select @table = name, @owner = user_name(uid) from sysobjects where id in (select 
			objid from sysmergearticles where nickname = @nick)
	else
		select @table = name, @owner = user_name(uid) from sysobjects where id in (select 
			objid from sysmergearticles where nickname = @nick and pubid = @pubid)
			
	select @tablename = QUOTENAME(@owner) + '.' + QUOTENAME(@table)
	if (@table is NULL) or (@owner is NULL) 
    begin
        raiserror(21124, 16, -1, @nick)
        return (1) 
    end
	return (0)
go

exec dbo.sp_MS_marksystemobject sp_MStablenamefromnick
go

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

create procedure sp_MSmakegeneration
	@gencheck int = 0
	as
	declare @gen int
	declare @nick int
	declare @genguid uniqueidentifier
	declare @dt datetime
	declare @dt2 datetime
	declare @art_nick int
	declare @first_ts int
	declare @makenewrow int
	declare @retcode smallint
	declare @guidnull uniqueidentifier
	declare @nickbin varbinary(255)
	declare @maxgendiff_fornewrow int
	declare @count_of_articles int
	
	set nocount on
	set @guidnull = '00000000-0000-0000-0000-000000000000'
	
	/*
	** Check to see if current publication has permission
	*/
	exec @retcode=sp_MSreplcheck_connection
	if @retcode<>0 or @@ERROR<>0 return (1)
	
	set @genguid = newid()
	exec @retcode=sp_MSgetreplnick @nickname = @nick out
	if @retcode<>0 or @@error<>0 return (1)

	-- convert @nick into binary and add a guard byte if needed
	if @nick % 256 = 0
		set @nickbin = convert(binary(4), @nick) + 0x01
	else
		set @nickbin = convert(binary(4), @nick)

	set @dt = getdate()

	-- If someone else is making generations / has just made one, exit so
	-- that we won't deadlock
	select @dt2 = max(coldate) from dbo.MSmerge_genhistory where guidsrc = guidlocal
	if datediff(dd, @dt2, @dt) = 0
		begin
		if 500 > datediff(ms, @dt2, @dt) and 0 < datediff(ms, @dt2, @dt)
			return 0
		end

	-- localize interrupted generations
	exec @retcode= sp_MSlocalizeinterruptedgenerations
	if @retcode<>0 or @@error<>0 return (1)


	-- If @gencheck param is set, look for rows with missing generation numbers and set their
	-- gen to 0
	if @gencheck >= 1
		begin
		update dbo.MSmerge_contents set generation = 0 where generation not in
			(select generation from dbo.MSmerge_genhistory)
		update dbo.MSmerge_tombstone set generation = 0 where generation not in
			(select generation from dbo.MSmerge_genhistory)
		end
	/*
	** If there are no zero generation tombstones or rows, add a dummy row in there. 
	*/
   	if not exists (select * from dbo.MSmerge_genhistory)
		begin
		begin tran
	   	insert into dbo.MSmerge_genhistory (guidsrc, guidlocal, generation, art_nick, nicknames, coldate) values
			(@genguid, @genguid, 1, 0, @nickbin, @dt)
		if (@@error <> 0)
			begin
			goto FAILURE
			end	
		commit tran
		end

	select @art_nick = min(nickname), @count_of_articles = count(*) from sysmergearticles

	-- Calculate how much less than the max generation in MSmerge_genhistory are we willing to have the minimum open generation in MSmerge_genhistory.
	-- Having a number smaller than or roughly equal to the number of articles will cause more aggressive closing of existing open gens (and making new rows) with 0 changes
	-- and hence more generations for merge agents to deal with. Having a very high number will cause less aggressive closing of open gens but will cause the
	-- common gens of replicas to be stuck at lower numbers because of the existence of "holes" at much lower gen values. An optimization that works well
	-- and is a compromise between the two extremes is to have the max of 100 or (2 * @count_of_articles) + 1 as the max diff we allow before deciding to make a new row.
	if ((2 * @count_of_articles) + 1) > 100
		select @maxgendiff_fornewrow = (2 * @count_of_articles) + 1
	else
		select @maxgendiff_fornewrow = 100

	while @art_nick is not null
		begin
		begin tran
		select @gen = max(gen_cur) from sysmergearticles (updlock holdlock) where nickname = @art_nick and gen_cur is not null

		if @gen is null
			begin
			set @genguid = newid()
			insert into dbo.MSmerge_genhistory (guidsrc, guidlocal, generation, art_nick, nicknames, coldate) 
				select @genguid, @guidnull, COALESCE(1 + max(generation), 1), @art_nick, @nickbin, @dt from dbo.MSmerge_genhistory (updlock)
			if (@@error <> 0)
				begin
				goto FAILURE
				end	
			select @gen =  generation from dbo.MSmerge_genhistory where guidsrc = @genguid
			update sysmergearticles set gen_cur = @gen where nickname = @art_nick
			if (@@error <> 0)
				begin
				goto FAILURE
				end	
			end

		-- these updates should be hitting zero rows...
		if exists (select * from dbo.MSmerge_contents (readpast readcommitted) where generation = 0 and tablenick = @art_nick)
			begin
			update dbo.MSmerge_contents set generation = @gen, partchangegen = @gen, joinchangegen = @gen
			 	where generation = 0 and partchangegen = 0 and tablenick = @art_nick
			if (@@error <> 0)
				begin
				goto FAILURE
				end	
			update dbo.MSmerge_contents set generation = @gen, joinchangegen = @gen
			 	where generation = 0 and joinchangegen = 0 and tablenick = @art_nick
			if (@@error <> 0)
				begin
				goto FAILURE
				end	
			update dbo.MSmerge_contents set generation = @gen where generation = 0 and tablenick = @art_nick
			if (@@error <> 0)
				begin
				goto FAILURE
				end
			end
			
		if exists (select * from dbo.MSmerge_tombstone (readpast readcommitted) where generation = 0 and tablenick = @art_nick)
			begin
			update dbo.MSmerge_tombstone set generation = @gen where generation = 0 and tablenick = @art_nick
			if (@@error <> 0)
				begin
				goto FAILURE
				end
			end
		if not exists (select * from dbo.MSmerge_contents where tablenick = @art_nick and
						generation = @gen) and
		   not exists (select * from dbo.MSmerge_tombstone where tablenick = @art_nick and
						generation = @gen)
			begin

			select @dt2 = coldate from dbo.MSmerge_genhistory where generation = @gen
			if datediff(dd, @dt2, @dt) = 0 and not exists (select * from dbo.MSmerge_genhistory
					where generation > @maxgendiff_fornewrow + @gen)
				begin
				set @makenewrow = 0
				end
			else
				begin
				set @makenewrow = 1
				delete from dbo.MSmerge_genhistory where generation = @gen
				if (@@error <> 0)
					begin
					goto FAILURE
					end	
				end
				
			if (@@error <> 0)
				begin
				goto FAILURE
				end	
			end
		else
			begin
			set @makenewrow = 1
			set @genguid = newid()
			update dbo.MSmerge_genhistory set guidsrc = @genguid, guidlocal = @genguid, coldate = @dt
				where generation = @gen
			if (@@error <> 0)
				begin
				goto FAILURE
				end	
			update MSmerge_replinfo set recgen = @gen, recguid = @genguid, 
				sentgen = @gen, sentguid = @genguid where replnickname = @nick
			if (@@error <> 0)
				begin
				goto FAILURE
				end	
			end

		if (@makenewrow = 1)
			begin
			/* reset next generation for this article */
			set @genguid = newid()
			insert into dbo.MSmerge_genhistory (guidsrc, guidlocal, generation, art_nick, nicknames, coldate) 
				select @genguid, @guidnull, COALESCE(1 + max(generation), 1), @art_nick, @nickbin, @dt from dbo.MSmerge_genhistory (updlock)
			if (@@error <> 0)
				begin
				goto FAILURE
				end	
			select @gen =  generation  from dbo.MSmerge_genhistory where guidsrc = @genguid
			update sysmergearticles set gen_cur = @gen where nickname = @art_nick
			if (@@error <> 0)
				begin
				goto FAILURE
				end	
			end
		commit transaction		

		-- set up for next time through the loop
		select @art_nick = min(nickname) from sysmergearticles where nickname > @art_nick
		
		set @dt = getdate()
		end
	return 0
FAILURE:
	/* UNDONE : This code is specific to 6.X nested transaction semantics */
	if @@TRANCOUNT = 1 
		ROLLBACK TRANSACTION makegeneration
	else
		COMMIT TRANSACTION
	return (1)	
go

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

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

create procedure sp_MSfixlineageversions
as
	declare @lNick int -- length of nickname in bytes
	declare @lVer int -- length of version in bytes
	declare @lEntry int -- length of entry in bytes
	declare @cEntries int
	declare @idx int -- @idx is the index of the entry that potentially needs to be bumped up
	set @lNick= 4
	set @lVer= 4
	set @lEntry= @lNick + @lVer
	set @cEntries= 31 -- max number of entries		

	if exists (select * from sysobjects where type = 'U' and name = 'MSmerge_contents')
	begin
		-- starting at the end of the lineage, replace every lower version by the following higher version
		set @idx= @cEntries - 2	-- lineage entries range from 0..30; compare 30 with 29, then 29 with 28, ..., 1 with 0
		while @idx >= 0
		begin
			update dbo.MSmerge_contents set lineage= 
				-- leave everything before "entry[@idx].version" unmodified
				substring(lineage, 1, @idx * @lEntry + @lNick) + 
				-- replace "entry[@idx].version" by "entry[@idx+1].version"
				substring(lineage, (@idx + 1) * @lEntry + @lNick + 1, @lVer) +	
				-- leave everything starting at "entry[@idx+1].nickname" unmodified
				substring(lineage, (@idx + 1) * @lEntry + 1, (@cEntries * @lEntry) - ((@idx + 1) * @lEntry) + 1) -- '+1' is for trailing byte
				where
					-- skip this step right away for shorter lineages
					datalength(lineage) >= @lEntry * (@idx + 2)
					and
					(
						-- either least significant version byte at second entry is higher, the other three are equal
						(substring(lineage, (@idx + 1) * @lEntry + @lNick + 1, 1) > substring(lineage, @idx * @lEntry + @lNick + 1, 1) and
							substring(lineage, (@idx + 1) * @lEntry + @lNick + 2, 3) = substring(lineage, @idx * @lEntry + @lNick + 2, 3))
						or
						-- 2nd least significant version byte at second entry is higher, the most and 2nd most significant bytes are equal
						(substring(lineage, (@idx + 1) * @lEntry + @lNick + 2, 1) > substring(lineage, @idx * @lEntry + @lNick + 2, 1) and
							substring(lineage, (@idx + 1) * @lEntry + @lNick + 3, 2) = substring(lineage, @idx * @lEntry + @lNick + 3, 2))
						or
						-- 3rd least significant version byte at second entry is higher, the most significant byte is equal
						(substring(lineage, (@idx + 1) * @lEntry + @lNick + 3, 1) > substring(lineage, @idx * @lEntry + @lNick + 3, 1) and
							substring(lineage, (@idx + 1) * @lEntry + @lNick + 4, 1) = substring(lineage, @idx * @lEntry + @lNick + 4, 1))
						or
						-- most significant byte at second entry is higher
						substring(lineage, (@idx + 1) * @lEntry + @lNick + 4, 1) > substring(lineage, @idx * @lEntry + @lNick + 4, 1)
					)

			if @@error<>0
			begin
				return (1)
			end

			set @idx= @idx - 1
		end
	end
	if exists (select * from sysobjects where type = 'U' and name = 'MSmerge_tombstone')
	begin
		-- starting at the end of the lineage, replace every lower version by the following higher version
		set @idx= @cEntries - 2	-- lineage entries range from 0..30; compare 30 with 29, then 29 with 28, ..., 1 with 0
		while @idx >= 0
		begin
			update dbo.MSmerge_tombstone set lineage= 
				-- leave everything before "entry[@idx].version" unmodified
				substring(lineage, 1, @idx * @lEntry + @lNick) + 
				-- replace "entry[@idx].version" by "entry[@idx+1].version"
				substring(lineage, (@idx + 1) * @lEntry + @lNick + 1, @lVer) +	
				-- leave everything starting at "entry[@idx+1].nickname" unmodified
				substring(lineage, (@idx + 1) * @lEntry + 1, (@cEntries * @lEntry) - ((@idx + 1) * @lEntry) + 1) -- '+1' is for trailing byte
				where
					-- skip this step right away for shorter lineages
					datalength(lineage) >= @lEntry * (@idx + 2)
					and
					(
						-- either least significant version byte at second entry is higher, the other three are equal
						(substring(lineage, (@idx + 1) * @lEntry + @lNick + 1, 1) > substring(lineage, @idx * @lEntry + @lNick + 1, 1) and
							substring(lineage, (@idx + 1) * @lEntry + @lNick + 2, 3) = substring(lineage, @idx * @lEntry + @lNick + 2, 3))
						or
						-- 2nd least significant version byte at second entry is higher, the most and 2nd most significant bytes are equal
						(substring(lineage, (@idx + 1) * @lEntry + @lNick + 2, 1) > substring(lineage, @idx * @lEntry + @lNick + 2, 1) and
							substring(lineage, (@idx + 1) * @lEntry + @lNick + 3, 2) = substring(lineage, @idx * @lEntry + @lNick + 3, 2))
						or
						-- 3rd least significant version byte at second entry is higher, the most significant byte is equal
						(substring(lineage, (@idx + 1) * @lEntry + @lNick + 3, 1) > substring(lineage, @idx * @lEntry + @lNick + 3, 1) and
							substring(lineage, (@idx + 1) * @lEntry + @lNick + 4, 1) = substring(lineage, @idx * @lEntry + @lNick + 4, 1))
						or
						-- most significant byte at second entry is higher
						substring(lineage, (@idx + 1) * @lEntry + @lNick + 4, 1) > substring(lineage, @idx * @lEntry + @lNick + 4, 1)
					)

			if @@error<>0
			begin
				return (1)
			end

			set @idx= @idx - 1
		end
	end
	return (0)
go

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

dump tran master with no_log
go

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

CREATE PROCEDURE sp_MSaddupdatetrigger 
	@source_table		nvarchar(270),		/* source table name */
	@owner				sysname,			/* Owner name of source table */
	@object 			sysname,			/* Object name */
	@artid				uniqueidentifier,	/* Article id */
	@column_tracking		int,
	@viewname			sysname 			/* name of view on syscontents */
    
AS
	declare @command1 nvarchar(4000)
	declare @command2 nvarchar(4000)
	declare @command3 nvarchar(4000)
    declare @command4 nvarchar(4000)
	declare @inscommand nvarchar(2000)
	declare @tablenick int	
	declare @nickname int
	declare @viewcols int
	declare @trigname sysname
	declare @ext nvarchar(10)
	declare @gstr sysname
	declare @tablenickchar nvarchar(11)
	declare @ccols int
	declare @guidstr	nvarchar(32)
	declare @colid smallint
	declare @colordinal		smallint
	declare @colordstr		varchar(4)

	declare @colname 	sysname
	declare @cur_name	sysname
	declare @colpat nvarchar(130)
	declare @colchar nvarchar(5)
	declare @piece nvarchar(400)

	declare @retcode int
	declare @ifcol nvarchar(4000)
	declare @ccolchar nvarchar(5)
	declare @partchangecnt int
	declare @joinchangecnt int
	declare @partchangecnt2 int
	declare @cvstr1	nvarchar(500)
	declare @cvstr2 nvarchar(500)
	declare @flag smallint
	declare @missingbm varbinary(500)
	declare @missing_cols	varbinary(32)
	declare @mapdownbm varbinary(500)
	declare @mapupbm   varbinary(500)
	declare @missingcolid int
	declare @maxcolid int
	declare @missingbmstr varchar(1000)
	declare @mapdownbmstr varchar(1000)
	declare @mapupbmstr   varchar(1000)
	declare @objid int
	declare @sync_objid int
	declare @partchbm varbinary(500)
	declare @missing_col_count	int
	declare @excluded_col_count int
	declare @joinchbm varbinary(500)
	declare @partchstr varchar(1002)
	declare @joinchstr varchar(1002)
	declare @column_hole bit
	declare @notforrepl_bit bit
	declare @notforrepl_str nvarchar(200)
	declare @owner_is_admin bit
	
	set @notforrepl_bit = 1
	set @ifcol = ''
	set @column_hole = 0
	set @owner_is_admin = 0
	
	select @owner_is_admin=sysadmin from master..syslogins l, sysusers u where l.sid=u.sid and u.name=@owner collate database_default
	select @flag = 0
	set @objid = OBJECT_ID(@source_table)
	select @sync_objid = sync_objid, @missing_cols = missing_cols, @excluded_col_count = excluded_col_count,
									@missing_col_count=missing_col_count 
					from sysmergearticles where artid= @artid and objid=@objid
	select @ccols =  count(*) from syscolumns where id = @objid and iscomputed <> 1 and type_name(xtype) <> 'timestamp'
	/* Figure out if there are any holes in the colid sequence */
	select @maxcolid = max(colid) from syscolumns where id = @objid
	if @ccols <> @maxcolid
		select @column_hole = 1
	/*
	** adjust the number of columns in the original table by adding up missing columns; in both Pub/Sub sides.
	*/
	if @missing_col_count>0
		select @ccols = @ccols + @missing_col_count
	select @ccolchar = convert(nchar, @ccols)
	set @colordinal = 0
	
	execute @retcode=sp_MStablenickname @owner, @object, @tablenick output
	if @@ERROR<>0 or @retcode<>0 return (1)
	set @tablenickchar = convert(nchar, @tablenick)
	set @joinchbm = 0x0
	set @partchbm = 0x0

	-- Check if the update trigger can be made NOT FOR REPLICATION
	if exists (select * from sysmergearticles 
			   where nickname = @tablenick 
			   and
			   (before_image_objid is not null or
			    before_view_objid is not null or
			    datalength (subset_filterclause) > 1
			   ))
	begin
		select @notforrepl_bit = 0
	end
	else if exists (select * from sysmergesubsetfilters where art_nickname = @tablenick or join_nickname = @tablenick)
	begin
		select @notforrepl_bit = 0
	end
	else
	begin
		select @notforrepl_bit = 1
	end
	
	select @notforrepl_str = ' 
	if sessionproperty(''replication_agent'') = 1 and (select trigger_nestlevel()) = 1 -- and master.dbo.fn_isreplmergeagent() = 1
		return '
		
	declare col_cursor CURSOR LOCAL FAST_FORWARD for select name, colid from syscolumns where
		id = @objid  and iscomputed <> 1 and type_name(xtype) <> 'timestamp' order by colid
	FOR READ ONLY
	
	/* Try to set the ifcol pieces of the trigger */
	open col_cursor
	fetch next from col_cursor into @colname, @colid
	while (@@fetch_status <> -1)
		begin
		set @colordinal = @colordinal + 1
		set @colpat = '%' + @colname + '%'
		/* Don't let them update the rowguid column */
		if columnproperty( @objid, @colname , 'isrowguidcol')=1
			set @ifcol = 'if update(' + QUOTENAME(@colname) +	')
			begin
			if @@trancount > 0
				rollback tran
				
			RAISERROR (20062, 16, -1)
			end
				'
		/* does updating this column change membership in a partial replica? */
		select @partchangecnt = count(*) from sysmergearticles 
			where nickname = @tablenick and subset_filterclause like @colpat
		select @partchangecnt2 = count(*) from sysmergesubsetfilters
			where art_nickname = @tablenick and join_filterclause like @colpat
		select @joinchangecnt = count(*) from sysmergesubsetfilters
		 	where join_nickname = @tablenick and join_filterclause like @colpat
		if @partchangecnt > 0 or @partchangecnt2 > 0
			exec dbo.sp_MSsetbit @partchbm out, @colid
		else if @joinchangecnt > 0
			exec dbo.sp_MSsetbit @joinchbm out, @colid
		/* Repeat the loop with next column */
		fetch next from col_cursor into @colname, @colid
		end
	close col_cursor
	deallocate col_cursor

	-- Initialize string for inserting to before_image table
	exec sp_MSgetbeforetableinsert @objid, @inscommand output

	/* Make strings to initialize variables for partchange, joinchange bitmaps */
	exec master..xp_varbintohexstr @partchbm, @partchstr out
	exec master..xp_varbintohexstr @joinchbm, @joinchstr out

	select @mapdownbm =0x00
	select @mapupbm = 0x00
	/*
	** To see if there is a need for map down.
	*/
	if @column_hole<>0
	begin
		set @missingcolid = 1
		while (@missingcolid <= @maxcolid)
			begin
			if not exists (select * from syscolumns where colid = @missingcolid and
						id = OBJECT_ID(@source_table) and iscomputed <> 1 and type_name(xtype) <> 'timestamp')
					exec dbo.sp_MSsetbit @mapdownbm out, @missingcolid
			set @missingcolid = @missingcolid + 1
			end
	end
	set @mapupbm = @missing_cols -- do this at both sides, good for republishing.
	
	exec master..xp_varbintohexstr @mapdownbm, @mapdownbmstr out
	exec master..xp_varbintohexstr @mapupbm, @mapupbmstr out
	
	execute @retcode=sp_MSgetreplnick @nickname = @nickname output
	if @retcode<>0 or @@error<>0 return (1)
	set @ext = 'upd_'

	exec @retcode=sp_MSguidtostr @artid, @guidstr out
	if @retcode<>0 or @@error<>0 return (1)

	set @trigname =  @ext + @guidstr 

	/* Make sure trigger name is unique */
	exec @retcode=sp_MSuniqueobjectname @trigname, @trigname output
	if @retcode<>0 or @@error<>0 return (1)
	if @column_tracking <> 0
		begin
		/* Set cv pieces appropriately */
		set @cvstr1 = ' 
		    set @lineage = { fn UPDATELINEAGE(0x0, @nick) }
		    set @lineage = { fn UPDATELINEAGE(@lineage, @nick) }
		    set @cv = { fn INITCOLVS(@ccols, @nick) }
			if (@@error <> 0)
				begin
				goto FAILURE
				end
		    set @cv = { fn UPDATECOLVBM(@cv, @nick, @bm, @missingbm) }
		'
		set @cvstr2 = '
				colv1 = { fn UPDATECOLVBM(colv1, @nick, @bm, @missingbm) } '
		end
	else
		begin
		set @cvstr1 = '   set @lineage = { fn UPDATELINEAGE(0x0, @nick) }
		    set @lineage = { fn UPDATELINEAGE(@lineage, @nick) }
			set @cv = NULL
 	'
		set @cvstr2 = ' colv1 = NULL '
		end
	/* UNDONE maybe remove null guid checks in SQL SERVER 7.0 */
	select @command1 = 'create trigger ' + @trigname + ' on ' + @source_table +
	' FOR UPDATE AS '

	if (@notforrepl_bit = 1)
		select @command1 = @command1 + @notforrepl_str

	select @command1 = @command1 + ' 
	/* Declare variables */

	declare @article_rows_updated int
	select @article_rows_updated = count(*) from inserted
	declare @contents_rows_updated int, @updateerror int
	declare @bm varbinary(500), @missingbm varbinary(500), @lineage varbinary(255), @cv varbinary(2048)
	declare @tablenick int, @nick int, @ccols int, @partchange int, @joinchange int, @partgen int, @joingen int, @newgen int
	declare	@partchangebm varbinary(500), @joinchangebm varbinary(500)
		
	set nocount on
	set @tablenick = ' + @tablenickchar + '
	select @newgen = gen_cur from sysmergearticles where nickname = @tablenick
	if @newgen is null
		set @newgen = 0
	/* Use intrinsic funtion to set bits for updated columns */
	set @bm = columns_updated()

	/* only do the map down when needed */
	set @missingbm = ' 

	select @command2 = '  

	/* See if the partition might have changed */
	if @partchangebm = 0x0
		set @partchange = 0
	else
		set @partchange= { fn INTERSECTBITMAPS (@bm, @partchangebm) }
	
	/* See if a column used in a join filter changed */
	if @joinchangebm = 0x0
		set @joinchange = 0
	else
		set @joinchange= { fn INTERSECTBITMAPS (@bm, @joinchangebm) }
	'

	if @mapdownbm<>0x00
		select @command2 = @command2 + 
				' execute master..xp_mapdown_bitmap ' + @mapdownbmstr +', @bm output '

	select @command2 = @command2 + '

	exec dbo.sp_MSgetreplnick @nickname = @nick output
	select @ccols = ' + @ccolchar + '
	' + @cvstr1 + '
	if @joinchange = 1
		begin
			' 
	set @command3 = '
			set @joingen = @newgen
			set @partgen = @newgen
		end
	else if @partchange = 1
		begin
			set @partgen = @newgen
			set @joingen = NULL
		end
	else	
		begin
			set @partgen = NULL
			set @joingen = NULL
		end

	update ' + @viewname + ' 
	set lineage = { fn UPDATELINEAGE(lineage, @nick) }, 
		generation = @newgen, 
		joinchangegen = case when (@joinchange = 1) then @newgen else joinchangegen end, 
		partchangegen = case when (@partchange = 1) then @newgen else partchangegen end, 
		' + @cvstr2 + ' 
	FROM inserted as I JOIN ' + @viewname + ' as V 
	ON (I.rowguidcol=V.rowguid)
	and tablenick = @tablenick

	select @updateerror = @@error, @contents_rows_updated = @@rowcount

	 ' + isnull(@inscommand, ' ') + ' 
	
	if @article_rows_updated <> @contents_rows_updated
	begin
		insert into ' + @viewname + ' (tablenick, rowguid, lineage, colv1, generation, partchangegen, joinchangegen) 
			select @tablenick, rowguidcol, @lineage, @cv, @newgen, @partgen, @joingen from inserted where
				rowguidcol not in (select rowguid from ' + @viewname + ' where tablenick = @tablenick)

		if @@error <> 0
			GOTO FAILURE
	end

	return
FAILURE:
				if @@trancount > 0
					rollback tran
				raiserror (20041, 16, -1)
				return
					'
			
	execute (@command1 + @mapupbmstr + '
		set @partchangebm = ' + @partchstr + '
		set @joinchangebm = ' + @joinchstr + '
			' + @ifcol +  
			@command2 + @command3)
	if @@ERROR <> 0 
		begin
			raiserror(20064, 16, -1)
			return (1)
		end
	
    select @command4 = 'sp_MS_marksystemobject ''' + REPLACE(@owner, '''', '''''') + '.' + @trigname + ''''
	if @owner_is_admin=1
		execute (@command4)		
GO

exec dbo.sp_MS_marksystemobject sp_MSaddupdatetrigger
go

raiserror('Creating procedure sp_MSaddmergetriggers', 0,1)
GO
CREATE PROCEDURE sp_MSaddmergetriggers 
    @source_table 		nvarchar(270),				/* was type varchar(92), table name */
    @table_owner		sysname = NULL,
    @column_tracking 	int = NULL				/* Is column tracking on - default is FALSE */
AS
	set nocount on
	declare @command		nvarchar(4000)
	declare @command2 nvarchar(4000)
	declare @inscommand nvarchar(2000)
	declare @ifcoltracking 	nvarchar(255)
	declare @tablenick 		int	
	declare @nickname 		int
	declare @artid 			uniqueidentifier
	declare @guidstr		nvarchar(32)
	declare @owner 			sysname
	declare @site 			sysname
	declare @db 			sysname
	declare @object 		sysname
	declare @updtrigname 		sysname
	declare @instrigname 		sysname
	declare @deltrigname 		sysname
	declare @ext 				nvarchar(10)
	declare @ext2 				nvarchar(10)
	declare @tablenickchar 		nvarchar(11)
	declare @missing_col_count	int
	declare @ccols 			int
	declare @ccolchar 		nvarchar(5)
	declare @retcode 		int
	declare @objid			int
	declare @bitmap 		varbinary(40)
	declare @missing_count	int
	declare @viewname		sysname
	declare @tsview			sysname
	declare @sync_objid		int
    declare @command3       nvarchar(4000)
	declare @notforrepl_str	nvarchar(200)
	declare @notforrepl_bit bit
	declare @owner_is_admin bit
	
	set @notforrepl_bit = 1
	set @owner_is_admin = 0
	set @bitmap = 0x0
	set @missing_count = 0

    -- PARSENAME VARS
	declare  	@UnqualName      sysname  --rightmost name node
	declare  	@QualName1       sysname  
    -- END PARSENAME VARS

	execute @retcode=sp_MSgetreplnick @nickname = @nickname output
	if @retcode<>0 or @@ERROR<>0 return (1)

	select @ext = 'ins_'
	select @ext2 = 'del_'
	if @table_owner is not NULL
		select @source_table=QUOTENAME(@table_owner) + '.' + QUOTENAME(@source_table)
	set @objid =  OBJECT_ID(@source_table)

	-- set up the before image table if one is desired
	exec dbo.sp_MScreatebeforetable @objid

	-- Initialize string for inserting to before_image table
	exec sp_MSgetbeforetableinsert @objid, @inscommand output

	select @ccols =  count(*) from syscolumns where id = @objid  and iscomputed <> 1 and type_name(xtype) <> 'timestamp'
	select @missing_col_count=missing_col_count, @sync_objid=sync_objid from sysmergearticles where objid=@objid
	/*
	** increase the # of columns at subscriber side by adding the missing columns
	** Note that publisher side also needs to increase this value due to the possible column holes.
	*/
	select @ccols = @ccols + @missing_col_count
	select @ccolchar = convert(nchar, @ccols)

    select @UnqualName = PARSENAME(@source_table, 1)
    select @QualName1 = PARSENAME(@source_table, 2)
    if @UnqualName IS NULL
         return 1

    if @QualName1 is NULL
 		select @QualName1 = user_name(uid) from sysobjects where id = object_id(@UnqualName)

	-- fixup for variable length differences.  remove when vars expanded
	-- to new SQL SERVER 7.0 lengths

	select @owner = @QualName1
	select @object = @UnqualName

	--this is to find out if table owner is a system user
	select @owner_is_admin=sysadmin from master..syslogins l, sysusers u where l.sid=u.sid and u.name=@owner collate database_default
		
	execute @retcode=sp_MStablenickname @owner, @object, @tablenick output
	if @retcode<>0 or @@ERROR<>0 return (1)
	select @artid = artid from sysmergearticles 
		where objid = @objid
		
	/* If column tracking wasn't passed in, just figure it out */
	if @column_tracking is null
		select @column_tracking = column_tracking from sysmergearticles 
		where artid = @artid
		
	select @tablenickchar = convert(nchar, @tablenick)
	exec @retcode=sp_MSguidtostr @artid, @guidstr out
	if @retcode<>0 or @@ERROR<>0 return (1)

	-- Check if the triggers can be made NOT FOR REPLICATION
	if exists (select * from sysmergearticles 
			   where nickname = @tablenick 
			   and
			   (before_image_objid is not null or
			    before_view_objid is not null or
			    datalength (subset_filterclause) > 1
			   ))
	begin
		select @notforrepl_bit = 0
	end
	else if exists (select * from sysmergesubsetfilters where art_nickname = @tablenick or join_nickname = @tablenick)
	begin
		select @notforrepl_bit = 0
	end
	else
	begin
		select @notforrepl_bit = 1
	end
	
	select @notforrepl_str = ' 
	if sessionproperty(''replication_agent'') = 1 and (select trigger_nestlevel()) = 1 -- and master.dbo.fn_isreplmergeagent() = 1
		return '

	/* Drop the article's replication triggers if they preexist */
	exec dbo.sp_MSdroparticletriggers @source_table, @table_owner
	if @@ERROR <> 0
		return 1
	
	-- owner name removed
	set @instrigname = @ext + @guidstr 
	set @deltrigname = @ext2 + @guidstr
	set @updtrigname = 'upd_' + @guidstr
	set @viewname = 'ctsv_' + @guidstr
	set @tsview = 'tsvw_' + @guidstr
	
	/* Make sure trigger name is unique */
	exec @retcode=sp_MSuniqueobjectname @instrigname, @instrigname output
	if @retcode<>0 or @@ERROR<>0 return (1)
	exec @retcode=sp_MSuniqueobjectname @deltrigname, @deltrigname output
	if @retcode<>0 or @@ERROR<>0 return (1)
	exec @retcode=sp_MSuniqueobjectname @updtrigname, @updtrigname output
	if @retcode<>0 or @@ERROR<>0 return (1)

	/* Create the view if it doesn't already exist. */
	if not exists (select * from sysobjects where type = 'V' and name = @viewname)
		begin

		exec @retcode=sp_MSuniqueobjectname @viewname, @viewname output
		if @retcode<>0 or @@ERROR<>0 return (1)
		set @command = 'create view dbo.' + @viewname + ' as
			select * from dbo.MSmerge_contents where
				trigger_nestlevel(OBJECT_ID(''' + QUOTENAME(REPLACE(@owner, '''', '''''')) + '.'  + QUOTENAME(@instrigname) + ''')) > 0 or
				trigger_nestlevel(OBJECT_ID(''' + QUOTENAME(REPLACE(@owner, '''', '''''')) + '.'  + QUOTENAME(@updtrigname) + ''')) > 0 or
				trigger_nestlevel(OBJECT_ID(''' + QUOTENAME(REPLACE(@owner, '''', '''''')) + '.'  + QUOTENAME(@deltrigname) + ''')) > 0
				with check option'
		execute (@command)
		if @@ERROR<>0 return (1)
		set @command = 'grant update, insert, select, delete on ' + @viewname + ' to public'
		execute (@command)
		if @@ERROR<>0 return (1)
		execute ('sp_MS_marksystemobject ''' + @viewname + '''')	
		if @@ERROR<>0 return (1)	
		end


	/* Create the view if it doesn't already exist. */
	if not exists (select * from sysobjects where type = 'V' and name = @tsview)
		begin
		exec @retcode=sp_MSuniqueobjectname @tsview, @tsview output
		if @retcode<>0 or @@ERROR<>0 return (1)
		set @command = 'create view dbo. ' + @tsview + ' as
			select * from dbo.MSmerge_tombstone where
				trigger_nestlevel(OBJECT_ID(''' + QUOTENAME(REPLACE(@owner, '''', '''''')) + '.'  + QUOTENAME(@instrigname) + ''')) > 0 or
				trigger_nestlevel(OBJECT_ID(''' + QUOTENAME(REPLACE(@owner, '''', '''''')) + '.'  + QUOTENAME(@updtrigname) + ''')) > 0 or
				trigger_nestlevel(OBJECT_ID(''' + QUOTENAME(REPLACE(@owner, '''', '''''')) + '.'  + QUOTENAME(@deltrigname) + ''')) > 0
				with check option'
		execute (@command)
		if @@ERROR<>0 return (1)
		set @command = 'grant update, insert, select, delete on ' + @tsview + ' to public'
		execute (@command)
		if @@ERROR<>0 return (1)		
		execute ('sp_MS_marksystemobject ''' + @tsview + '''')	
		if @@ERROR<>0 return (1)	
		end
		
	/* If column tracking is on, construct the string to initialize colv's */
	if (@column_tracking <> 0)
		-- select @ifcoltracking = '	execute master..xp_initcolvs @ccols, @nickname, @colv1 output'
		select @ifcoltracking = '	set @colv1 = { fn INITCOLVS(@ccols, @nickname) }'
	else
		select @ifcoltracking = '	set @colv1 = NULL'
		
	/* UNDONE maybe remove null guid checks in SQL SERVER 7.0 */
	select @command = 'create trigger ' + @instrigname + ' on ' + @source_table +
	' for insert as ' + @notforrepl_str + ' 
	/* Declare variables */
	declare @article_rows_inserted int
	select @article_rows_inserted = count(*) from inserted
	declare @tablenick int, @nickname int
	declare @lineage varbinary(255), @colv1 varbinary(2048)
	declare @ccols int, @retcode smallint, @newgen int, @version int, @curversion int
	
	set nocount on
	set @tablenick = ' + @tablenickchar + '
	select @ccols = ' + @ccolchar + '
	set @lineage = 0x0
	set @retcode = 0
	select @newgen = gen_cur from sysmergearticles where nickname = @tablenick
	if @newgen is null
		set @newgen = 0

	execute dbo.sp_MSgetreplnick @nickname = @nickname output
	if (@@error <> 0)
		begin
		goto FAILURE
		end
	set @lineage = { fn UPDATELINEAGE (0x0, @nickname) }
	' + @ifcoltracking + '
	if (@@error <> 0)
		begin
		goto FAILURE
		end

	if exists (select ts.rowguid from ' + @tsview + ' ts, inserted i where ts.tablenick = @tablenick and ts.rowguid = i.rowguidcol)
	begin
		select @version = max(convert(int, substring(lineage, 5, 1)) + 256 * 
			(convert(int, substring(lineage, 6, 1)) +  256 *
	 		(convert(int, substring(lineage, 7, 1)) + 256 *
	  		(convert(int, substring(lineage, 8, 1)))))) from ' + @tsview + ' where tablenick = @tablenick and rowguid in
			(select rowguidcol from inserted)

		if @version is not null
		begin
			-- reset lineage and colv to higher version...
			set @curversion = 0
			while (@curversion <= @version)
				begin
				set @lineage = { fn UPDATELINEAGE (@lineage, @nickname) }
				if (@colv1 IS NOT NULL)
					set @colv1 = { fn UPDATECOLVBM(@colv1, @nickname, 0x01, 0x00) }
				set @curversion = @curversion + 1
				end
			
			delete from ' + @tsview + ' where tablenick = @tablenick and rowguid in
				(select rowguidcol from inserted)
		end
	end
		
	if (@article_rows_inserted = 1)
	begin
		if not exists (select ct.rowguid from ' + @viewname + ' ct, inserted i where ct.tablenick = @tablenick and ct.rowguid = i.rowguidcol)
		begin
			insert into ' + @viewname + ' (tablenick, rowguid, lineage, colv1, generation, joinchangegen) select
				@tablenick, rowguidcol, @lineage, @colv1, @newgen, @newgen from inserted
		end
	end
	else
	begin
		insert into ' + @viewname + ' (tablenick, rowguid, lineage, colv1, generation, joinchangegen) select
			@tablenick, rowguidcol, @lineage, @colv1, @newgen, @newgen from inserted where rowguidcol not in
			(select rowguid from ' + @viewname + ' where tablenick = @tablenick)
	end
	if @@error <> 0 
		goto FAILURE
	return
FAILURE:
				if @@trancount > 0
					rollback tran
				raiserror (20041, 16, -1)
				return
					'
	execute (@command)
	if @@ERROR <> 0 
		begin
			raiserror(21304, 16, -1, @source_table)
			return (1)
		end
	
    select @command3 = 'sp_MS_marksystemobject ''' + REPLACE(@owner, '''', '''''') + '.'  + @instrigname + ''''
	if @owner_is_admin = 1
		execute (@command3)	
	/* Call separate routine to add update trigger */
	exec @retcode=sp_MSaddupdatetrigger @source_table, @owner, @object, @artid, @column_tracking, @viewname
	if @retcode<>0 or @@ERROR<>0 return (1)

	/* Now make the delete trigger */
	-- NOTE: owner name removed

	/* Make sure trigger name is unique */
	exec @retcode = dbo.sp_MSuniqueobjectname @deltrigname, @deltrigname output
	if @retcode<>0 or @@ERROR<>0 return (1)

	set @command = 'create trigger ' + @deltrigname + ' on ' + @source_table +
	' FOR DELETE AS '

	if (@notforrepl_bit = 1)
		select @command = @command + @notforrepl_str

	select @command = @command + '
	/* Declare variables */
	declare @tablenick int, @retcode smallint, @reason nvarchar(255), @nickname int, @lineage varbinary(255), @newgen int
	
	set nocount on
	select @tablenick = ' + @tablenickchar + '
	select @newgen = gen_cur from sysmergearticles where nickname = @tablenick
	if @newgen is null
		set @newgen = 0

	select @reason = ''user delete''
	execute dbo.sp_MSgetreplnick @nickname = @nickname output
	if (@@error <> 0)
		begin
		goto FAILURE
		end
	'
	set @command2 = '
	set @lineage = { fn UPDATELINEAGE(0x0, @nickname) }
	
	insert into ' + @tsview + ' (rowguid, tablenick, type, lineage, generation, reason)
		select rowguidcol, @tablenick, 1, { fn UPDATELINEAGE(COALESCE(c.lineage, @lineage), @nickname) }, @newgen, @reason from 
			deleted d left outer join ' + @viewname + ' c on c.tablenick = @tablenick and c.rowguid = d.rowguidcol
	if @@error <> 0
		GOTO FAILURE
			
	delete from ' + @viewname + ' where tablenick = @tablenick and rowguid in 
		(select rowguidcol from deleted) 
	if @@error <> 0
		GOTO FAILURE
	return
FAILURE:
				if @@trancount > 0
					rollback tran
				raiserror (20041, 16, -1)
				return
				'
	
	execute (@command + @inscommand + @command2)

	if @@ERROR <> 0 
		begin
			raiserror(21304, 16, -1, @source_table)
			return (1)
		end
    select @command3 = 'sp_MS_marksystemobject ''' + REPLACE(@owner, '''', '''''') + '.' + @deltrigname + ''''
	if @owner_is_admin = 1
		execute (@command3)	

	return (0)
GO

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

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

create procedure sp_MSchangeobjectowner(
	@tablename sysname,
	@dest_owner	sysname
)AS
	declare @uid 		smallint
	declare @retcode	int
	
	select @uid=uid from sysobjects where name=@tablename

	--do not bother to call sp_changeobjectowner the object is owned by the @dest_owner itself
	if user_name(@uid)=@dest_owner
		return(0)
	exec @retcode=sp_changeobjectowner @tablename, @dest_owner
	if @@ERROR<>0 or @retcode<>0
	begin
		raiserror(21346, 16, -1, @tablename, @dest_owner)
		return (1)
	end
	return(0)	
go
exec dbo.sp_MS_marksystemobject sp_MSchangeobjectowner 
go
grant exec on dbo.sp_MSchangeobjectowner to public
go

create procedure sp_MShelpdestowner(
@spname sysname
)
AS
declare @comment nvarchar(400)
declare @patindex int
declare @has_destowner	int
select @comment = convert(nvarchar(400), text) from syscomments where id = object_id(@spname)
select @patindex = PATINDEX('%@destowner%', @comment)
if @patindex<>0
	select 1
else
	select 0
GO
exec dbo.sp_MS_marksystemobject sp_MShelpdestowner 
go
grant exec on dbo.sp_MShelpdestowner to public
go


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

create procedure sp_MSfillupmissingcols(@publication sysname, @source_table sysname)
AS
declare @sync_objid 		int
declare @columns			varbinary(125)
declare @missingcolid		int
declare @missingbm			varbinary(125)
declare @excludedcolid		int
declare @excludedbm			varbinary(125)
declare @excludedcolcnt		int
declare @missingcolcnt		int
declare @maxcolid			int
declare @column_tracking	bit
declare @id					int
declare @pubid				uniqueidentifier
declare @missingindex		int

select @id = object_id(@source_table)
select @missingcolcnt = 0
select @excludedcolcnt = 0
select @pubid=pubid from sysmergepublications where name=@publication and publisher=@@SERVERNAME and publisher_db=db_name()
select @columns = columns, @sync_objid=sync_objid from sysmergearticles where objid = @id and pubid=@pubid
select @missingbm = 0x00
select @excludedbm = 0x00
select @missingcolid = 1 --instead of using the minimal colid in syscolumns with the same id, as we used to do 
select TOP 1 @maxcolid = colid from syscolumns where id=@id order by colid DESC
 
while (@missingcolid <= @maxcolid)
	begin
	if exists (select * from syscolumns where colid = @missingcolid and id = @id and 
				name not in (select name from syscolumns where id = @sync_objid))
		begin
		select @excludedcolcnt = @excludedcolcnt + 1
		select @missingindex = count(*) from syscolumns where id=@id and colid<=@missingcolid
		exec dbo.sp_MSsetbit @excludedbm OUTPUT, @missingindex
		end
	set @missingcolid = @missingcolid + 1
	end

UPDATE sysmergearticles set excluded_col_count = @excludedcolcnt, 
							excluded_cols = @excludedbm
		where objid = @id and pubid=@pubid
GO

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

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

create procedure sp_MSmaptype (@type nvarchar(60) output, 
								@len smallint, 
								@prec int, 
								@scale int)
as
	declare @typeout nvarchar(60)

select @typeout = case @type
	when 'binary' then 'varbinary'
	when 'char' then 'varchar'
	when 'nchar' then 'nvarchar'
	when 'datetimn' then 'datetime'
	when 'decimaln' then 'decimal'
	when 'floatn' then 'float'
	when 'intn' then 'int'
	when 'moneyn' then 'money'
	when 'numericn' then 'numeric'
	when 'timestamp' then 'varbinary'
	when 'bit' then 'tinyint'
	else @type  --for user defined data type which may contain space in between
	END
-- append length or scale and precision if needed
if (@typeout = 'varbinary' or @typeout = 'varchar' or @typeout = 'nvarchar')
	begin
	select @type = @typeout + '(' + rtrim(convert(nchar, @len)) + ')'
	return
	end
if (@typeout = 'numeric' or @typeout = 'decimal')
	begin
	select @type = @typeout + '(' + rtrim(convert(nchar, @prec)) + ',' + 
			rtrim(convert(nchar, @scale)) + ')'
	return
	end
select @type = @typeout
go

exec dbo.sp_MS_marksystemobject sp_MSmaptype 
go

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

create procedure sp_MSquerysubtype (@pubid uniqueidentifier,@subscriber sysname, @subscriber_db sysname)
as
	declare @subtype	int
	declare @srvid		int

	SELECT @srvid = srvid FROM master..sysservers WHERE UPPER(srvname) = UPPER(@subscriber) collate database_default
	
	select @subtype = NULL
	select @subtype = subscription_type from sysmergesubscriptions where pubid=@pubid 
		and db_name=@subscriber_db and srvid=@srvid
	if @subtype is NULL
		select @subtype=2
	select @subtype
go

exec dbo.sp_MS_marksystemobject sp_MSquerysubtype 
go

grant exec on dbo.sp_MSquerysubtype to public
go

-- sp_showrowreplicainfo displays row and column version information about a specific row
-- parameters: 
-- 		@ownername: The table owner.
--		@tablename: Name of the table that contains the row.
--		@rowguid: Unique identifier of the row of interest.
--		@show: Determines whether to display row versions, colum versions, or both. Values are 'both', 'row', or 'columns'.
-- output columns for row version information:
--		server_name: Name of the server that made this entry
--		db_name: Name of the database that made this entry.
--		db_nickname: Nickname of the database that made this entry.
--		version: Version of the entry.
--		rowversion_table: Indicates whether lineage is in MSmerge_contents or MSmerge_tombstone.
--		comment: Records problems with this version entry.
-- output columns for column version information:
--		server_name, db_name, db_nickname, version, comment: As above.
--		colname: Name of the column the colv entry stands for.
create procedure sp_showrowreplicainfo
	(@ownername sysname = NULL, @tablename sysname, @rowguid uniqueidentifier, @show nvarchar(20) = 'both')
as 
	set nocount on

	-- some constants
	-- this stored proc is for debugging purposes, thus no need for localizing them
	declare @dbname sysname
	select @dbname= db_name()

	declare @missingcolname sysname
	set @missingcolname= '<Missing column>'

	declare @anonymousname sysname
	set @anonymousname= '<Anonymous subscriber>'

	declare @unknownname sysname
	set @unknownname= '<Unknown server name>'

	declare @mergename sysname
	set @mergename= '<Merge nickname>'

	declare @nondecreasingversion nvarchar(128)
	set @nondecreasingversion= 'Problem found: Non-decreasing version.'

	declare @toohighcolvversion nvarchar(128)
	set @toohighcolvversion= 'Problem found: Version is higher than highest version in lineage.'

	declare @naname sysname
	set @naname= '<n/a>'

	-- check whether given table exists
	if not exists (select * from sysobjects where xtype = 'U' and name = @tablename)
	begin
		raiserror (20507, 16, 1, @tablename, @dbname)
		return 1
	end
	else if @ownername is not null
	begin
		-- check whether table belongs to the given owner
		if not exists (select * from sysobjects where xtype = 'U' and name=@tablename and uid = (select uid from sysusers where name = @ownername))
		begin
			declare @qualifiedtablename nvarchar(257)
			select @qualifiedtablename= @ownername + '.' + @dbname
			raiserror (20507, 16, 1, @qualifiedtablename, @dbname)
			return 1
		end
	end

	-- get tableid and tablenick from tablename
	declare @tableid int
	declare @tablenick int
	set @tableid= object_id(@tablename)
	select @tablenick = (select top 1 nickname from sysmergearticles where objid = @tableid)
	if @tablenick is null
	begin
		raiserror (20027, 16, 1, @tablename)
		return 1
	end

	-- check whether there is an entry for this row in either MSmerge_contents or MSmerge_tombstone
	declare @incontents int
	declare @lineage varbinary(249)
	select @lineage= lineage from MSmerge_contents where rowguid = @rowguid
	if @lineage is not null
	begin
		set @incontents= 1
	end
	else
	begin
		select @lineage= lineage from MSmerge_tombstone where rowguid = @rowguid
		if @lineage is not null
		begin
			set @incontents= 0
		end
		else
		begin
			-- replace the following print by raiserror as soon as there is an approved message
			raiserror(21511,10,1)
			return 0
		end
	end

	-- create temporary table for information about lineage and colv entries
	create table #results (type nchar(7) null, rowversion_table nchar(17) null, server_name sysname null, 
							[db_name] sysname null,  db_nickname int not null, colid int null, colname sysname null, 
							version int not null, comment nvarchar(255) null, id int identity(1,1) not null) 
	
	if lower(@show) in ('both', 'row')
	begin
		-- insert lineage information into temptable
		insert into #results (db_nickname, version) exec master..xp_showlineage @lineage
		update #results set type= 'lineage'
		if (@incontents = 1)
		begin
			update #results set rowversion_table = 'MSmerge_contents'
		end
		else
		begin
			update #results set rowversion_table = 'MSmerge_tombstone'
		end
	end

	if (@incontents = 1) and (lower(@show) in ('both', 'columns'))
	begin
		-- insert colv information into temptable
		declare @colv varbinary(2048)
		select @colv= colv1 from MSmerge_contents where rowguid=@rowguid
		if @colv is not null
		begin
			insert into #results (colid, db_nickname, version) exec master..xp_showcolv @colv
			update #results set type= 'colv' where type is null

			-- translate colids into column names
			if (select top 1 missing_col_count from sysmergearticles where nickname = @tablenick) = 0
			begin
				-- no missing cols: position of entries in colv correspond to colid in sysmergearticles
				update #results set colname= s.name from syscolumns s where #results.colid = s.colid and s.id = @tableid
			end
			else
			begin
				-- missing cols: colv has entries for columns that do not exist in this db
				declare @colname sysname
				declare @ismissing int
				declare @missingsofar int
				declare @colid int
				declare @missingcols varbinary(128)
				select @missingcols= (select top 1 missing_cols from sysmergearticles where nickname = @tablenick)
				set @missingsofar= 0
				select @colid= (select min(colid) from #results where colname is null and colid is not null)
				while @colid is not null
				begin
					-- is this column missing?
					exec @ismissing= sp_MStestbit @missingcols, @colid
					if @ismissing <> 0
					begin
						update #results set colname= @missingcolname, server_name= @naname, [db_name]= @naname
											 where colid = @colid
						set @missingsofar= @missingsofar + 1
					end
					else
					begin
						select @colname= (select name from syscolumns where id = @tableid and colid = (@colid - @missingsofar))
						update #results set colname= @colname where colid = @colid
					end
					
					select @colid= (select min(colid) from #results where colname is null and colid is not null)
				end
			end
		end
	end

	-- transform null comment to empty strings
	update #results set comment= ''

	-- translate nicknames in temptable into real db names; set server names, too
	declare @subid uniqueidentifier
	declare @servername sysname
	declare @srvid int
	declare @replnick int

	update #results set [db_name]= @mergename, server_name= @naname where db_nickname=1

	select @replnick= (select top 1 db_nickname from #results where [db_name] is null)
	while @replnick is not null
	begin
		select @subid= (select s.subid
							from sysmergesubscriptions s, MSmerge_replinfo r 
							where r.repid = s.subid and @replnick = r.replnickname)

		select @dbname= (select [db_name] from sysmergesubscriptions where subid = @subid)
		if @dbname is null
		begin
			set @dbname=@anonymousname
			set @servername= @unknownname
		end
		else
		begin
			select @servername= (select srv.srvname from master..sysservers srv, sysmergesubscriptions sub
							where srv.srvid = sub.srvid and sub.subid = @subid)
		end

		update #results set [db_name]= @dbname, server_name= @servername 
						where db_nickname = @replnick and [db_name] is null
		select @replnick= (select top 1 db_nickname from #results where [db_name] is null)
	end

	-- record increasing lineage versions
	update #results set comment= @nondecreasingversion 
			where type = 'lineage' and 
			exists (select * from #results r where r.version < #results.version and r.id < #results.id)

	-- record colv versions that are higher than highest lineage version
	update #results set comment= @toohighcolvversion 
			where type = 'colv' and 
			not exists (select version from #results r where type = 'lineage' and r.version >= #results.version)
	
	-- deliver results
	if lower(@show) in ('both', 'row')
	begin
		select server_name, [db_name], db_nickname, version, rowversion_table, comment from #results where type = 'lineage' order by id
	end

	if lower(@show) in ('both', 'columns')
	begin
		select server_name, db_name, db_nickname, version, colname, comment from #results where type = 'colv' order by id
	end

	drop table #results
	return 0
go
exec dbo.sp_MS_marksystemobject sp_showrowreplicainfo
go
grant execute on dbo.sp_showrowreplicainfo to public
go


dump tran master with no_log
go


checkpoint
go

set nocount on
go

execute dbo.sp_configure 'update',1
go
reconfigure with override
go

set ANSI_NULLS off
go

dump tran master with no_log
go

use master
go

/* 
** Drop the stored procedures in this script using the old dropping SP 
** and then drop itself
*/
if exists (select * from sysobjects
    where type = 'P '
            and name = 'sp_MSdrop_rladmin')
begin
    drop procedure sp_MSdrop_rladmin
end

/*
** Create stored procedures to drop the stored procedures
** created by this script
*/

raiserror('Creating procedure sp_MSdrop_rladmin', 0,1)
GO
create procedure sp_MSdrop_rladmin
as
	if exists (select * from sysobjects
            where type in ('P ') 
                and name = 'sp_MSaddmergepub_snapshot')
        drop procedure sp_MSaddmergepub_snapshot

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

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

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

    if exists (select * from sysobjects
            where type in ('P ') 
                and name = 'sp_MScheckatpublisher')
        drop procedure sp_MScheckatpublisher    
    
    if exists (select * from sysobjects
            where type in ('P ') 
                and name = 'sp_MSdropmergepub_snapshot')
        drop procedure sp_MSdropmergepub_snapshot
    
    if exists (select * from sysobjects
            where type = 'P'
                and name = 'sp_addmergepublication')
        drop procedure sp_addmergepublication

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    if exists (select * from sysobjects
        where type = 'P ' and name = 'sp_MSscript_dri')
    drop procedure sp_MSscript_dri
    
    if exists (select * from sysobjects
            where type in ('P ') 
                and name = 'sp_MSsubsetpublication')
        drop procedure sp_MSsubsetpublication
    if exists (select * from sysobjects
            where type = 'P'
                and name = 'sp_generatefilters')
        drop procedure sp_generatefilters
    if exists (select * from sysobjects
            where type = 'P'
                and name = 'sp_MSmakejoinfilter')
        drop procedure sp_MSmakejoinfilter
    if exists (select * from sysobjects
            where type = 'P'
                and name = 'sp_MSmakeexpandproc')
        drop procedure sp_MSmakeexpandproc
    if exists (select * from sysobjects
            where type = 'P'
                and name = 'sp_MSindexcolfrombin')
        drop procedure sp_MSindexcolfrombin

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

exec dbo.sp_MS_marksystemobject sp_MSdrop_rladmin
go

EXEC dbo.sp_MSdrop_rladmin
GO

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

CREATE PROCEDURE sp_MSaddmergepub_snapshot (
    @publication            sysname,    
    @freqtype               int = 4 ,           /* 4== Daily */
    @freqinterval           int = 1,            /* Every day */
    @freqsubtype            int = 4,            /* Sub interval = Minute */
    @freqsubinterval        int = 5,            /* Every five minutes */
    @freqrelativeinterval   int = 1, 
    @freqrecurrencefactor   int = 0, 
    @activestartdate        int = 0,            /* 12:00 am - 11:59 pm */
    @activeenddate          int = 99991231 ,    /* No start date */ 
    @activestarttimeofday   int = 0,        
    @activeendtimeofday     int = 235959,       /* No end time */               
    @newtaskid              int = 0 OUTPUT,
    @snapshot_job_name      nvarchar(100) = null
) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */
    declare @retcode                int
    declare @distributor            sysname
    declare @dist_rpcname           sysname
    declare @distribdb              sysname
    declare @distproc               nvarchar(300)
    declare @database               sysname
    declare @newid                  int
    declare @fFoundPublication      int
    declare @task_args              nvarchar(4000)
    declare @pubid                  uniqueidentifier
    declare @snapshot_jobid         binary(16)
    declare @job_existing           bit

    /*
    ** Initializations
    */
    select @fFoundPublication   = 0 
    if (@snapshot_job_name is null) or (@snapshot_job_name = N'')
    begin
        select @job_existing = 0
    end
    else
    begin
        select @job_existing = 1
    end

    EXEC @retcode = dbo.sp_helpmergepublication @publication, @fFoundPublication output, @pubid output

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

    /* If the publication does not exist return error */
    if @fFoundPublication = 0 
    BEGIN
        RAISERROR (21040, 11, -1, @publication)
        RETURN (1)
    END

    /* 
    ** Make sure the publication does not already have a task.
    */
    if EXISTS (select * FROM MSmerge_replinfo WHERE repid = @pubid and snapshot_jobid IS NOT NULL)
    BEGIN
        RAISERROR (14101, 11, -1, @publication)
        RETURN(1)
    END

    /*
    ** Get distributor information
    */
    EXEC @retcode = dbo.sp_helpdistributor @distributor = @distributor OUTPUT, 
        @distribdb = @distribdb OUTPUT,
        @rpcsrvname = @dist_rpcname OUTPUT 
        if @@error <> 0 OR @retcode <> 0 or @distributor IS NULL OR @distribdb IS NULL
        BEGIN
            RAISERROR (14071, 16, -1)
            RETURN (1)
        END

    select @database = DB_NAME()

    select @task_args = '-Publisher ' + QUOTENAME(@@SERVERNAME)
    select @task_args = @task_args + ' -PublisherDB ' + QUOTENAME(@database)
    select @task_args = @task_args + ' -Distributor ' + QUOTENAME(@distributor)
    select @task_args = @task_args + ' -Publication ' + QUOTENAME(@publication)
    select @task_args = @task_args + ' -ReplicationType 2'
    
    /* 
    ** Create task on distributor
    */
    SELECT @distproc = RTRIM(@dist_rpcname) + '.' + @distribdb + '.dbo.sp_MSadd_snapshot_agent'

    EXECUTE @retcode = @distproc 
        @name = @snapshot_job_name,
        @publisher = @@SERVERNAME,
        @publisher_db = @database,
        @publication = @publication,  
        @publication_type = 2,          -- Merge type
        @local_job = 1,  
        @freqtype = @freqtype, 
        @freqinterval = @freqinterval, 
        @freqsubtype = @freqsubtype, 
        @freqsubinterval = @freqsubinterval, 
        @freqrelativeinterval = @freqrelativeinterval, 
        @freqrecurrencefactor = @freqrecurrencefactor, 
        @activestartdate = @activestartdate,
	    @activeenddate =@activeenddate ,         
    	@activestarttimeofday = @activestarttimeofday,         
	    @activeendtimeofday = @activeendtimeofday,     
        @command = @task_args, 
        @snapshot_jobid = @snapshot_jobid OUTPUT,
        @job_existing = @job_existing 

   if @@ERROR <> 0 or @retcode <> 0
        RETURN(1)
    
    SELECT @newtaskid = 1

    UPDATE MSmerge_replinfo set snapshot_jobid = @snapshot_jobid WHERE repid = @pubid

    if @@ERROR <> 0 
        RETURN(1)
            
    return (0)
GO
exec dbo.sp_MS_marksystemobject sp_MSaddmergepub_snapshot
go

grant execute on dbo.sp_MSaddmergepub_snapshot to public
go


GO

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

CREATE PROCEDURE sp_MSdropmergepub_snapshot (
    @publication sysname,
    @ignore_distributor bit = 0
) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */
    declare @retcode            int
    declare @distributor        sysname
    declare @distproc           nvarchar(300)
    declare @snapshot_jobid     binary(16)
    declare @fFoundPublication  int
    declare @pubid              uniqueidentifier
    declare @distribdb          sysname

    /*
    ** Initializations
    */
    select @fFoundPublication = 0

    /* validate the publication */
    /* If the publication is  not exist found return error */
    EXEC @retcode = dbo.sp_helpmergepublication @publication, @fFoundPublication output, @pubid output
    if @@ERROR <> 0 OR @fFoundPublication = 0 OR @retcode <> 0
    BEGIN
        RETURN (1)
    END

    /*
    ** Get taskid. Make sure the snapshot_jobid is not NULL only before using MAX
    ** Otherwise there will be a warnning.
    */
    select @snapshot_jobid = max(snapshot_jobid) FROM MSmerge_replinfo WHERE repid = @pubid
    
    if (@snapshot_jobid IS NOT NULL)        
    begin

    /*
    ** if @ignore_distributor = 1, we are in bruteforce cleanup mode, don't do RPC.
    */
    if @ignore_distributor = 0
        begin
            /*
            ** Get distributor information
            */
            EXEC @retcode = dbo.sp_helpdistributor @rpcsrvname = @distributor OUTPUT,
                @distribdb = @distribdb OUTPUT
                if @@error <> 0 OR @retcode <> 0
                BEGIN
                    RAISERROR (14071, 16, -1)
                    RETURN (1)
                END

            /*
            ** Delete sync agent of Publication.
            */
            declare @dbname sysname
            set @dbname = DB_NAME()
            SELECT @distproc = RTRIM(@distributor) + '.' + @distribdb + '.dbo.sp_MSdrop_snapshot_agent'
            EXECUTE @retcode = @distproc 
                @publisher = @@SERVERNAME,
                @publisher_db = @dbname,
                @publication = @publication

            IF @@ERROR <> 0 or @retcode <> 0
                RETURN(1)
        end


        /* Update publication's taskid */
        UPDATE MSmerge_replinfo set snapshot_jobid = NULL WHERE repid = @pubid
        IF @@ERROR <> 0
            BEGIN
            RAISERROR (20072, 16, -1)
            RETURN (1)
            END

    end
            
    return (0)      
GO 
exec dbo.sp_MS_marksystemobject sp_MSdropmergepub_snapshot 
go

grant execute on dbo.sp_MSdropmergepub_snapshot to public
go

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

create procedure sp_MScheckatpublisher
    @pubid                  uniqueidentifier
    AS
    -- this routine will be called by sp_addmergearticle, sp_changemergearticle
    declare @db_name        sysname
    
    select @db_name =db_name from sysmergesubscriptions
        where (pubid=@pubid) and (subid=@pubid) 
        IF @db_name <> db_name() 
            RETURN 1
        ELSE 
            RETURN 0
            
go
exec dbo.sp_MS_marksystemobject sp_MScheckatpublisher
go

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

create procedure sp_MSaddmergeschemaarticle
    @pubid                              uniqueidentifier,    
    @article                            sysname,
    @source_object                      sysname,
    @type                               tinyint, 
    @description                        nvarchar(255),
    @status                             nvarchar(10),   
    @pre_creation_command               int,
    @creation_script                    nvarchar(255),
    @source_owner                       sysname,
    @destination_owner                  sysname,    
    @schema_option                      binary(8),
    @destination_object                 sysname,
    @qualified_name                     nvarchar(270),
    @publication                        sysname, 
    @snapshot_ready                     int,
    @force_invalidate_snapshot          bit
AS
    SET NOCOUNT ON

    DECLARE @retcode              int
    DECLARE @objid                int    
    DECLARE @artid                uniqueidentifier
    DECLARE @bInTran              bit
    DECLARE @valid_schema_options int
    DECLARE @bump_to_80           bit
        
    SELECT @bInTran = 0
    SELECT @objid = OBJECT_ID(@qualified_name)
    -- We are going to bump the compatibility level anyway
    -- since we are adding schema only articles
    SELECT @bump_to_80 = 1
    RAISERROR(21391, 10, -1, @publication)

    /*
    ** Parameter check: Source object type must match
    ** specified article type.
    **
    */
    -- Type has to be one of 0x20, 0x40, or 0x80 by the time 
    -- this sp is called
    IF @type = 0x20
    BEGIN
        IF NOT EXISTS (SELECT * 
                         FROM sysobjects
                        WHERE id = @objid
                          AND xtype = 'P ')
        BEGIN
            RAISERROR(21219, 16, -1)
            RETURN (1)
        END 
                        
    END
    ELSE IF @type = 0x40
    BEGIN
        IF NOT EXISTS (SELECT *
                         FROM sysobjects
                        WHERE id = @objid
                          AND xtype = 'V ')
        BEGIN
            RAISERROR(21221, 16, -1)
            RETURN (1)
        END
    END
    ELSE IF @type = 0x80
    BEGIN
        IF NOT EXISTS (SELECT *
                         FROM sysobjects
                        WHERE id = @objid
                          AND xtype IN ('FN','TF', 'IF'))
        BEGIN
            RAISERROR(21228, 16, -1)
            RETURN (1)
        END
    END


    /*
    ** Parameter check: @schema_option
    ** For proc and func schema only articles, schema_option can 
    ** only contain the options
    ** 0x0000000000000001, 0x0000000000002000
    ** for schema only articles except view. View articles can contain 
    ** the options 0x0000000000000010, 0x0000000000000020, and 0x0000000000000100 
    ** in addition to the aforementioned options.
    */
    IF @type = 0x40
    BEGIN

        -- Since only the lower 32 bits of @schema_option are
        -- currently used, the following check is sufficient.
        -- Note that @schema_option should have been padded out already.
        DECLARE @schema_option_lodword int
        SELECT @valid_schema_options = 0x2151
        SELECT @schema_option_lodword = fn_replgetbinary8lodword(@schema_option)
        IF (@schema_option_lodword & ~@valid_schema_options) <> 0
        BEGIN
            RAISERROR (21229, 16, -1)
            RETURN (1)
        END
    END
    ELSE IF @schema_option NOT IN (0x0000000000000000,
                                   0x0000000000000001,
                                   0x0000000000002000,
                                   0x0000000000002001)
    BEGIN
        RAISERROR (21222, 16, -1)
        RETURN (1)
    END 


    /*
    ** Parameter check: @pre_creation_command must be
    ** 'drop' (id = 1) or 'none' (id = 0)
    */
    IF @pre_creation_command NOT IN (0, 1)
    BEGIN
        RAISERROR(21223, 16, -1)
        RETURN (1)
    END

    -- Reuse @artid from articles publishing the same object so we can
    -- link these articles together during the reinit/republishing scenario 

    SELECT @artid = NULL
    SELECT @artid = artid 
      FROM sysmergeschemaarticles 
     WHERE objid = @objid

    IF @artid IS NULL
    BEGIN
        SELECT @artid = newid()
    END

    BEGIN TRAN
    SAVE TRANSACTION sp_MSaddmergeschemaarticle
    SELECT @bInTran = 1

    IF @snapshot_ready > 0
    BEGIN
        IF @force_invalidate_snapshot = 0
		BEGIN
            RAISERROR(21364, 16, -1, @article)
			GOTO FAILURE
        END
        RAISERROR(21360, 10, -1, @publication)
		SELECT @bump_to_80 = 1
        UPDATE sysmergepublications 
           SET snapshot_ready=2 
         WHERE pubid=@pubid
        IF @@ERROR<>0
            GOTO FAILURE
    END

    INSERT sysmergeschemaarticles (name, type, objid, artid, description,
        pre_creation_command, pubid, status, creation_script, schema_option,
        destination_object, destination_owner)
    VALUES (@article, @type, @objid, @artid, @description, @pre_creation_command,
        @pubid, 1, @creation_script, @schema_option, @destination_object,
        @destination_owner)

    IF @@ERROR <> 0
        GOTO FAILURE

    -- Mark the source object as a replication object so
    -- user cannot drop it 
    UPDATE sysobjects SET replinfo = replinfo | 0x00000200 WHERE id = @objid   
    
    IF @@ERROR <> 0
        GOTO FAILURE

	IF @bump_to_80=1
	BEGIN
        EXEC @retcode = sp_MSBumpupCompLevel @pubid, 40
        IF @@ERROR<>0 or @retcode<>0
            GOTO FAILURE
    END

    COMMIT TRANSACTION 
    
    RETURN (0)

FAILURE:

    IF @bInTran = 1
    BEGIN
        ROLLBACK TRANSACTION sp_MSaddmergeschemaarticle
        COMMIT TRANSACTION
    END    
    RETURN (1)
go

exec dbo.sp_MS_marksystemobject sp_MSaddmergeschemaarticle


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

create procedure sp_addmergearticle
    @publication            sysname,                            /* publication name */
    @article                sysname,                            /* article name */
    @source_object          sysname,                            /* source object name */
    @type                   sysname = 'table',                  /* article type */
    @description            nvarchar(255)= NULL,                /* article description */
    @column_tracking        nvarchar(10) = 'false',             /* column level tracking */
    @status                 nvarchar(10) = 'unsynced',          /* unsynced, active */
    @pre_creation_cmd       nvarchar(10) = 'drop',              /* 'none', 'drop', 'delete', 'truncate' */
    @creation_script        nvarchar(255)= NULL,                /* article schema script */
    @schema_option          varbinary(8)   = NULL,              /* article schema creation options */
    @subset_filterclause    nvarchar(1000) = '',                /* filter clause */
    @article_resolver       nvarchar(255)= NULL,                 /* custom resolver for article */
    @resolver_info          nvarchar(255) = NULL,                /* custom resolver info */
    @source_owner           sysname = NULL,
    @destination_owner		sysname = NULL,
    @vertical_partition		nvarchar(5) = 'FALSE',				/* vertical partitioning or not */
    @auto_identity_range	nvarchar(5)	= 'FALSE',				/* set it to false for now - change is possible */
    @pub_identity_range		bigint	= NULL,
    @identity_range			bigint = NULL,
    @threshold				int	= NULL,
	@verify_resolver_signature 		int = 0,					/* 0=do not verify signature, 1=verify that signature is from trusted source, more values may be added later */
    @destination_object     		sysname = @source_object,
	@allow_interactive_resolver		nvarchar(5) = 'false',		/* whether article allows interactive resolution or not */
	@fast_multicol_updateproc		nvarchar(5) = 'true',		/* whether update proc should update multiple columns in one update statement or not. if 0, then separate update issued for each column changed. */
	@check_permissions		int = 0, /* bitmap where 0x00 for nochecks, 0x01 for insert check, 0x2 for update check, 0x4 for delete check */
	@force_invalidate_snapshot bit = 0	/* Force invalidate existing snapshot */
    AS

    set nocount on

    /*
    ** Declarations.
    */
	declare @max_range				bigint
    declare @publisher				sysname
    declare @publisher_db			sysname
	declare @already_published		bit
    declare @identity_so_far		bigint
    declare @ver_partition			int
    declare @sp_resolver			sysname
    declare @num_columns            smallint
    declare @pubid                  uniqueidentifier                /* Publication id */
    declare @db                     sysname
	declare @identity_support		int
    declare @object                 sysname
    declare @owner                  sysname
    declare @retcode                int
    declare @objid                  int
    declare @sync_objid             int
    declare @typeid                 smallint
    declare @nickname               int
    declare @merge_pub_object_bit int
    declare @column_tracking_id     int
    declare @cmd                    nvarchar(255)
    declare @statusid               tinyint --1: inactive; 2: active; 5:new_inactive 6:new_active
    declare @next_seed				bigint
    declare @precmdid               int
    declare @resolver_clsid         nvarchar(50)
    declare @resolver_clsid_old     nvarchar(50)
    declare @tablenick              int
    declare @artid                  uniqueidentifier
	declare @i						int
	declare @max_identity			bigint
	declare @colname				sysname
	declare @indid					int
	declare @pkkey					sysname
    declare @distributor            sysname
    declare @distribdb              sysname
    declare @distproc               nvarchar(300)
    declare @dbname                 sysname
    declare @replinfo               int
    declare @db_name                sysname
    declare @subset                 int
    declare @is_publisher			int
    declare @row_size               int
    declare @sp_name				sysname
    declare @sp_owner				sysname
    declare @qualified_name         nvarchar(270)
    declare @snapshot_ready         tinyint
	declare @sync_mode				tinyint
	declare @allow_interactive_bit	bit
	declare @fast_multicol_updateproc_bit bit
    declare @additive_resolver		sysname
    declare @average_resolver		sysname
    declare @mindate_resolver		sysname
    declare @needs_pickup			bit
    declare @maxdate_resolver		sysname
    declare @minimum_resolver		sysname
    declare @maximum_resolver		sysname
    declare @mergetxt_resolver		sysname
    declare @pricolumn_resolver		sysname
	declare @xtype					int
	declare @xprec					int
	declare @initial_setting		bit
	declare @bump_to_80				bit
	declare @gen 					int
	declare @replnick 				int
	declare @genguid 				uniqueidentifier
	declare @guidnull 				uniqueidentifier
	declare @dt 					datetime
	
	/* make sure current database is enabled for merge replication */
    exec @retcode=dbo.sp_MSCheckmergereplication
    if @@ERROR<>0 or @retcode<>0
    	return (1)

    /*
    ** Initializations 
    */
	set @guidnull = '00000000-0000-0000-0000-000000000000'
    select @is_publisher = 0
    select @initial_setting = 0
    select @needs_pickup = 0
    select @bump_to_80 = 0
    select @already_published = 0
    select @publisher = @@SERVERNAME
    select @publisher_db = db_name()
    select @max_identity	= NULL
    select @next_seed		= NULL
    select @statusid        = 0
    select @resolver_clsid  = NULL
    select @subset          = 1     /* Const: publication type 'subset' */
    select @merge_pub_object_bit    = 128
	select @db_name	= db_name()
    select @sp_resolver 		= 'Microsoft SQLServer Stored Procedure Resolver'
    select @additive_resolver 	= 'Microsoft SQL Server Additive Conflict Resolver'
    select @average_resolver 	= 'Microsoft SQL Server Averaging Conflict Resolver'
    select @minimum_resolver 	= 'Microsoft SQL Server Minimum Conflict Resolver'
    select @maximum_resolver 	= 'Microsoft SQL Server Maximum Conflict Resolver'
    select @mindate_resolver 	= 'Microsoft SQL Server DATETIME (Earlier Wins) Conflict Resolver'
    select @maxdate_resolver 	= 'Microsoft SQL Server DATETIME (Later Wins) Conflict Resolver'
    select @mergetxt_resolver 	= 'Microsoft SQL Server Merge Text Columns Conflict Resolver'
    select @pricolumn_resolver 	= 'Microsoft SQL Server Priority Column Resolver'

    if @source_owner is NULL
        begin
            select @source_owner = user_name(uid) from sysobjects where id = object_id(@source_object)
            if @source_owner is NULL  
                begin
                    raiserror (14027, 11, -1, @source_object)
                    return (1)
                end
        end
    select @qualified_name = QUOTENAME(@source_owner) + '.' + QUOTENAME(@source_object)

	if @subset_filterclause <> '' and @subset_filterclause is not NULL
	begin
	/* check the validity of subset_filterclause */
	exec ('declare @test int select @test=1 from ' + @qualified_name + ' where ' + @subset_filterclause)
	if @@ERROR<>0
		begin
			raiserror(21256, 16, -1, @subset_filterclause, @article)
			return (1)
		end
	end
    if @destination_owner is NULL
    	select @destination_owner = 'dbo'
    
    /*
    ** Security Check
    */
    EXEC @retcode = dbo.sp_MSreplcheck_publish
    IF @@ERROR <> 0 or @retcode <> 0
        return (1)

    /*
    ** Pad out the specified schema option to the left
    */
    select @schema_option = fn_replprepadbinary8(@schema_option)

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

    select @pubid = pubid, @snapshot_ready = snapshot_ready, @sync_mode=sync_mode from sysmergepublications 
        where name = @publication and UPPER(publisher)=UPPER(@publisher) and publisher_db=@publisher_db
    if @pubid is NULL
        begin
            raiserror (20026, 16, -1, @publication)
            return (1)
        end

	if lower(@article)='all'
		begin
			raiserror(21401, 16, -1)
			return (1)
		end

    /*
    ** Parameter Check: @type
    ** If the article is added as a 'indexed view schema only' article,
    ** make sure that the source object is a schema-bound view.
    ** Conversely, a schema-bound view cannot be published as a 
    ** 'view schema only' article.
    */
    select @type = lower(@type)

    if @type = N'indexed view schema only' and objectproperty(object_id(@qualified_name), 'IsSchemaBound') <> 1
    begin
        raiserror (21277, 11, -1, @qualified_name)        
        return (1)    
    end
    else if @type = N'view schema only' and objectproperty(object_id(@qualified_name), 'IsSchemaBound') = 1
    begin
        raiserror (21275, 11, -1, @qualified_name)
        return (1)
    end

    /*
    ** Only publisher can call sp_addmergearticle
    */
    EXEC @retcode = dbo.sp_MScheckatpublisher @pubid
    IF @@ERROR <> 0 or @retcode <>  0
        BEGIN
            RAISERROR (20073, 16, -1)
            RETURN (1)
        END
        
    /*
    ** Parameter Check: @article.
    ** Check to see that the @article is local, that it conforms
    ** to the rules for identifiers, and that it is a table, and not
    ** a view or another database object.
    */

    if @article is NULL
        begin
            raiserror (20045, 16, -1)
            return (1)
        end

	exec @retcode = dbo.sp_MSreplcheck_name @article
    if @@ERROR <> 0 or @retcode <> 0
        return(1)
        

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

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


    /*
    ** Set the typeid.  The default type is table.  It can 
    ** be one of following.
    **
    **      @typeid     type
    **      =======     ========
    **         0xa      table
    **        0x20      proc schema only
    **        0x40      view schema only
    **        0x80      func schema only
    **        0x40      indexed view schema only (overloaded)
    */        

    IF LOWER(@type) NOT IN ('table', 'proc schema only', 'view schema only', 'func schema only', 'indexed view schema only')
       BEGIN
            RAISERROR (20074, 16, -1)
            RETURN (1)
       END

    IF LOWER(@type) = N'table'
    BEGIN
       SET @typeid = 0x0a
    END
    ELSE IF LOWER(@type) = N'proc schema only'
    BEGIN
       SET @typeid = 0x20 
    END
    ELSE IF LOWER(@type) = N'view schema only'
    BEGIN
       SET @typeid = 0x40
    END
    ELSE IF LOWER(@type) = N'indexed view schema only'
    BEGIN
       SET @typeid = 0x40
    END
    ELSE IF LOWER(@type) = N'func schema only'
    BEGIN
       SET @typeid = 0x80
    END

    select @sync_objid = OBJECT_ID(@qualified_name)
    if @sync_objid is NULL
        begin
            raiserror (14027, 11, -1, @qualified_name)
            return (1)
        end


    if @typeid in (0x20,0x40,0x80)
    begin
        if exists (select * from syscomments
                    where id = @sync_objid
                      and encrypted = 1)
        begin
            raiserror(21004, 16, -1, @source_object)
            return 1
        end
    end

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

    IF EXISTS (SELECT *
                 FROM sysmergeextendedarticlesview
                WHERE pubid = @pubid
                  AND name = @article)
        BEGIN
			raiserror (21292, 16, -1, @source_object)
            RETURN (1)
        END
        
    /*
    ** At this point, all common parameter validations 
    ** for table and schema only articles have been 
    ** performed, so branch out here to handle schema
    ** only articles as a special case.
    */
    
    IF @typeid in (0x20, 0x40, 0x80)
    BEGIN
    
        IF @destination_object IS NULL OR @destination_object = N''
        BEGIN
            SELECT @destination_object = @source_object
        END
    
        IF @schema_option IS NULL
        BEGIN
            SELECT @schema_option = 0x0000000000000001
        END
        EXEC @retcode = dbo.sp_MSaddmergeschemaarticle 
            @pubid = @pubid,
            @article = @article,
            @source_object = @source_object,
            @type = @typeid,
            @description = @description,
            @status = @status,
            @pre_creation_command = @precmdid,
            @creation_script = @creation_script,
            @source_owner = @source_owner,
            @destination_owner = @destination_owner,
            @schema_option = @schema_option,
            @destination_object = @destination_object,
            @qualified_name = @qualified_name,   
            @publication = @publication,
            @snapshot_ready = @snapshot_ready,
            @force_invalidate_snapshot = @force_invalidate_snapshot
        
       RETURN (@retcode)
    END

    IF @schema_option IS NULL
    BEGIN
        SELECT @schema_option = 0x000000000000CFF1
    END

    /*
    ** If scheme option contains collation or extended properties, 
    ** bump up the compatibility-level
    */    
    -- Since only the lower 32 bits of @schema_option are 
    -- used, the following check is sufficient. Note that @schema_option is
    -- already padded out to the left at the beginning of this procedure.
    declare @schema_option_lodword int
    declare @xprop_schema_option int
    declare @collation_schema_option int
    select @xprop_schema_option = 0x00002000
    select @collation_schema_option = 0x00001000
    select @schema_option_lodword = fn_replgetbinary8lodword(@schema_option)
    if (@schema_option_lodword & @collation_schema_option) <> 0
    begin    
        raiserror(21389, 10, -1, @publication)
        select @bump_to_80 = 1
    end
    if (@schema_option_lodword & @xprop_schema_option) <> 0
    begin   
        raiserror(21390, 10, -1, @publication)
        select @bump_to_80 = 1
    end

    /*
    ** Merge table articles does not really support destination object. It has the same value as source
    */
    select @destination_object = @source_object

/*
    select @row_size=sum(length) from syscolumns where id=OBJECT_ID(@qualified_name)
    if @row_size>6000 
        begin
			RAISERROR (21062, 16, -1, @qualified_name)  
            -- RETURN (1)
        end
*/
	IF LOWER(@vertical_partition) = 'false'
		begin
			select @ver_partition = 0
		end
	else
		begin			
			select @ver_partition = 1
		end
    select @num_columns=count(*) from syscolumns where id = object_id(@qualified_name)

    if @num_columns > 246 and LOWER(@vertical_partition) = 'false'
        begin
            RAISERROR (20068, 16, -1, @qualified_name, 246)
            RETURN (1)
        end

    /*
    **  Get the id of the @qualified_name
    */
    select @objid = id, @replinfo = replinfo from sysobjects where id = OBJECT_ID(@qualified_name)
    if @objid is NULL
        begin
            raiserror (14027, 11, -1, @qualified_name)
            return (1)
        end

    /*
    ** If current publication contains a non-sync subscription, all articles to be added in it
    ** has to contain a rowguidcol.
    */
    if exists (select * from sysmergesubscriptions where pubid = @pubid and sync_type = 2)
        begin
            if not exists (select * from syscolumns c 
                where c.id=@objid and ColumnProperty(c.id, c.name, 'isrowguidcol') = 1)
                begin
                    raiserror(20085 , 16, -1, @article, @publication)
                    return (1)
                end
        end

	/*
	** If you want to have identity support, @range and threshold can not be NULL
	*/
	if LOWER(@auto_identity_range) = 'true' and (@identity_range is NULL or @threshold is NULL or @pub_identity_range is NULL)
		begin
			raiserror(21193, 16, -1)
			return (1)
		end

	if LOWER(@auto_identity_range) = 'false' and (@identity_range is not NULL or @threshold is not NULL or @pub_identity_range is not NULL)
		begin
			raiserror(21282, 16, -1)
			return (1)
		end

	if @threshold<0 OR @threshold>100
		begin
			raiserror(21241, 16, -1)
			return (1)
		end

	if LOWER(@auto_identity_range) = 'true'
		begin
			select @identity_support = 1
			if OBJECTPROPERTY(@objid, 'tablehasidentity') <> 1
			begin
				raiserror(21194, 16, -1)
				return (1)
			end
		
		    if @pub_identity_range <= 1 or @identity_range <= 1
			begin
				raiserror(21232, 16 ,-1)
				return 1
			end

			select @xtype=xtype, @xprec=xprec from syscolumns where id=@objid and columnproperty(id, name, 'IsIdentity')=1
			select @max_range =
					case @xtype when 52 then power((convert(bigint,2)), 8*2-1) - 1 --smallint 
						when 48 then power((convert(bigint,2)), 8-1) - 1 		 --tinyint
						when 56 then power((convert(bigint,2)), 8*4-1) - 1 		 --int
						when 127 then power((convert(bigint,2)), 62) - 1 + power((convert(bigint,2)), 62)  	--bigint
 						else
							power((convert(bigint,2)), 62) -1 + power((convert(bigint,2)), 62)  -- defaulted to bigint
					end

			if (@xtype=108 or @xtype=106) and @xprec<18
				select @max_range = power((convert(bigint,10)), (@xprec+1)) - 1
		
			if @pub_identity_range * 2 + @identity_range > (@max_range - IDENT_CURRENT(@source_object))
				begin
					raiserror(21290, 16, -1)
					return (1)
				end
		end
	else
		select @identity_support = 0			


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

    if NOT exists (select * from sysobjects
        where id = (select OBJECT_ID(@qualified_name)) AND type = 'U')
        begin
            raiserror (20074, 16, -1)
            return (1)
        end

    /*
    ** If the table contains one more columns of type bigint or sql_variant, 
    ** and the publication is not of type native mode, we bump up the backward 
    ** compatibility level.
    */
    if @sync_mode=0 and EXISTS (SELECT * FROM syscolumns c WHERE c.id = @sync_objid
                AND (type_name(c.xtype) = 'bigint' or type_name(c.xtype) = 'sql_variant'))
	begin
		raiserror(21357, 10, -1, @publication)
		select	@bump_to_80 = 1
	end

	/*
	** 7.0 subscribers do not like data type 'timestamp'
	*/
	if EXISTS (select * from syscolumns where id=@sync_objid and type_name(xtype) ='timestamp')
	begin
		raiserror(21358, 10, -1, @publication)
		select @bump_to_80 = 1
	end
        
    /*
    ** Validate the column tracking
    */
    if @column_tracking IS NULL OR LOWER(@column_tracking) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@column_tracking')
            RETURN (1)
        END
    if LOWER(@column_tracking) = 'true' 
        SET @column_tracking_id = 1
    else 
        SET @column_tracking_id = 0

	if @column_tracking_id=0 and @sync_mode = 1 and @ver_partition = 1
		begin
			RAISERROR (21244, 16, -1)
            RETURN (1)
		end

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

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

    /*
    ** Get the pubid.
    */
    SELECT @pubid = pubid FROM sysmergepublications 
        WHERE name = @publication and UPPER(publisher)=UPPER(@publisher) and publisher_db=@publisher_db
    if @pubid is NULL
        begin
            raiserror (20026, 11, -1, @publication)
            return (1)
        end

    execute @retcode = dbo.sp_MSgetreplnick @pubid = @pubid, @nickname = @nickname output
    if (@@error <> 0) or @retcode <> 0 or @nickname IS NULL 
        begin
        RAISERROR (14055, 11, -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 or @distributor is NULL
	BEGIN
		RAISERROR (21337, 16, -1)
		RETURN (1)
	END

    /*
    ** Validate the article resolver
    */
    if @article_resolver IS NOT NULL
        begin
            if @article_resolver = 'default' OR @article_resolver = ''
                begin
                    select @article_resolver = NULL
                    select @resolver_clsid = NULL
                end                 
            else
                begin
                    /*
                    ** Get the distributor info
                    */
                    select @distproc = RTRIM(@distributor) + '.master.dbo.xp_regread'
                    EXECUTE @retcode = @distproc 'HKEY_LOCAL_MACHINE',
                                  'SOFTWARE\Microsoft\Microsoft SQL Server\80\Replication\ArticleResolver',
                                  @article_resolver,
                                  @param = @resolver_clsid  OUTPUT

                    IF @retcode <> 0 or @resolver_clsid IS NULL
                        BEGIN
                          RAISERROR (20020, 16, -1)
                          RETURN (1)
                        END
                end
        end

	/*
	** If article resolver is 'SP resolver', make sure that resolver_info refers to an SP or XP;
	** Also make sure it is stored with owner qualification
	*/
	if  @article_resolver = @sp_resolver
		begin
			if not exists (select * from sysobjects where id = object_id(@resolver_info) and ( type = 'P' or type = 'X'))
				begin
					raiserror(21343, 16, -1, @resolver_info)
					return (1)
				end
				
			select @sp_name = name, @sp_owner=user_name(uid) from sysobjects where id = object_id(@resolver_info)
			select @resolver_info = QUOTENAME(@sp_owner) + '.' + QUOTENAME(@sp_name) 
		end

	/* The following resolvers expect the @resolver_info to be NON NULL */
	if  @article_resolver = @sp_resolver or 
		@article_resolver = @additive_resolver or
		@article_resolver = @average_resolver or
		@article_resolver = @minimum_resolver or
		@article_resolver = @maximum_resolver or
		@article_resolver = @mindate_resolver or
		@article_resolver = @maxdate_resolver or
		@article_resolver = @mergetxt_resolver or
		@article_resolver = @pricolumn_resolver
		begin
		    if @resolver_info IS NULL 
		        begin
        			RAISERROR (21301, 16, -1, @article_resolver)
					return (1)
		        end
		end
	/*
	** If article resolver uses column names, make sure that resolver_info refers to a valid column.
	*/
	if  @article_resolver = @pricolumn_resolver or
		@article_resolver = @additive_resolver or
		@article_resolver = @average_resolver or
		@article_resolver = @minimum_resolver or
		@article_resolver = @maximum_resolver
		begin
			if not exists (select * from syscolumns where id = @objid and name=@resolver_info)
				begin
		            RAISERROR (21501, 16, -1, @article_resolver)
					return (1)
				end
		end
	/*
	** If article resolver is 'mindate/maxdate resolver', make sure that resolver_info refers to a column that is of datatype 'datetime' or smalldatetime
	*/
	if  @article_resolver = @mindate_resolver or
		@article_resolver = @maxdate_resolver
		begin
			if not exists (select * from syscolumns where id = @objid and name=@resolver_info and type_name(xtype)='datetime' or type_name(xtype) = 'smalldatetime' )
				begin
		            RAISERROR (21302, 16, -1, @article_resolver)
					return (1)
				end
		end

	/* The following resolvers expect the article to be column tracked - warn that the default resolver will be used */
	if  @article_resolver = @additive_resolver or
		@article_resolver = @average_resolver or
		@article_resolver = @mergetxt_resolver
		begin
			if @column_tracking_id = 0
				begin
		            RAISERROR (21303, 10, -1, @article, @article_resolver)
				end
				
		end

    if @resolver_info IS NOT NULL and @article_resolver IS NULL
	    begin
    	    RAISERROR (21300, 10, -1, @article)
        	set @resolver_info = NULL
        end

	/* Make sure that coltracking option matches */
	if exists (select * from sysmergearticles where objid = @objid and
			identity_support <> @identity_support)
		begin
			raiserror (21240, 16, -1, @source_object)
			return (1)
		end

	-- Do not allow the table to be published by both merge and queued tran
	if exists (select * from sysobjects where name = 'syspublications')
	begin
		if exists (select * from syspublications p, sysarticles a where 
			p.allow_queued_tran = 1 and
			p.pubid = a.pubid and
			a.objid = @objid)
		begin
			declare @obj_name sysname
			select @obj_name = object_name(@objid)
			raiserror(21266, 16, -1, @obj_name)
			return (1)
		end
	end

	if exists (select * from sysmergearticles where objid=@objid and pubid in(select pubid from sysmergepublications where UPPER(publisher)=UPPER(@publisher) 
        		and publisher_db=@publisher_db))
	select @already_published = 1

	if @already_published = 1 and LOWER(@auto_identity_range) = 'true'
	begin
		raiserror(21359, 10, -1, @publication)
		select @bump_to_80 = 1
		if exists (select * from MSrepl_identity_range where objid=@objid and 
			((pub_range<>@pub_identity_range) or (range <> @identity_range) or (threshold <> @threshold)))
			begin
				raiserror(21291, 16, -1)
				return (1)
			end
	end
	
    /*
    **  Add article to sysmergearticles and update sysobjects category bit.
    */
    begin tran
    save TRAN sp_addmergearticle

	/*
	** We used to prevent an article from being added to a publication whose snapshot
	** has been run already. Now we change this so that it is acceptable by doing reinit.
	*/
		if @snapshot_ready > 0 
		begin
			if @force_invalidate_snapshot = 0
			begin
				raiserror(21364, 16, -1, @article)
				goto FAILURE
			end
			update sysmergepublications set snapshot_ready=2 where pubid=@pubid
			if @@ERROR<>0
				goto FAILURE
		end

	/* 
	** article status 5 or 6 means there is at least one new article after snapshot is ready
	** hence all articles added after that point will be new articles as well, regardless of snapshot_ready value.
	*/
		if @snapshot_ready>0 or exists (select * from sysmergearticles where pubid=@pubid and (status=5 or status=6))
		begin
			select @needs_pickup=1
		end


        /*
        ** the case when @already_publisher=1 has been handled outside of the transaction
        */
        if LOWER(@auto_identity_range) = 'true' and @already_published = 0
		begin
			-- Set the range to negtive if incr of the identity is negtive
	        if IDENT_INCR(@source_object) < 0
    	    begin
        	    select @pub_identity_range = -1 * @pub_identity_range;
            	select @identity_range = -1 * @identity_range;
	        end
			raiserror(21359, 10, -1, @publication)
			select @bump_to_80 = 1
			select @next_seed = next_seed, @max_identity=max_identity from MSrepl_identity_range where objid=@objid
			select @identity_so_far = 0
			if @next_seed is NULL
			begin
				select @initial_setting = 1 -- adjust for existing rows, only for original publisher
				select @is_publisher= 1 --original publisher
				select @identity_so_far = IDENT_CURRENT(@source_object)
				if @identity_so_far is NULL
					begin
						select @next_seed = IDENT_SEED(@source_object)
						select @identity_so_far = @next_seed
					end
				else
					select @next_seed = @identity_so_far

				-- use boundary values by cutting off odds,	
				-- and always give publisher side one more range to allow for existing rows.				
				select @next_seed = (@next_seed/@pub_identity_range) * @pub_identity_range 

				-- to compensate publisher side an extra range in case it loses some slots by rounding up.
				-- which only happens when the identity incremental is a positive value

				if (((@pub_identity_range > 0) and (@identity_so_far > @next_seed))
					OR
					((@pub_identity_range < 0) and (@identity_so_far < @next_seed))) --to make it symmetric both directions
					
					select @next_seed = @next_seed + @pub_identity_range
					
				select @max_identity = @max_range --max range decided by data type of identity column
				
				insert MSrepl_identity_range(objid, next_seed, pub_range, range, max_identity, threshold, current_max)
					values (@objid,@next_seed + @pub_identity_range, @pub_identity_range, @identity_range, @max_identity, @threshold, @next_seed + @pub_identity_range)
			end	
			else
			begin
				select @is_publisher=2 -- republisher
				update MSrepl_identity_range set current_max = @next_seed + @pub_identity_range,
												 pub_range = @pub_identity_range,
												 threshold= @threshold,
												 range = @identity_range
				if @@ERROR<>0
					goto FAILURE
			end
				
	        select @distproc = RTRIM(@distributor) + '.' + @distribdb + '.dbo.sp_MSinsert_identity'
			SELECT @dbname =  DB_NAME()
			exec @retcode = @distproc @publisher = @publisher,
									  @publisher_db = @publisher_db,
									  @identity_support=@identity_support,
									  @tablename=@source_object,
									  @pub_identity_range = @pub_identity_range,
									  @identity_range =@identity_range,
									  @threshold =@threshold,
									  @next_seed = @next_seed,
									  @max_identity=@max_identity
			if @retcode<>0 or @@ERROR<>0
				goto FAILURE
				
			/* This is to change identity column to 'not for replication' if not having been so already */
			select @colname = name from syscolumns  where
				 id = @objid and
				 colstat & 0x0001 <> 0 and -- is identity
				 colstat & 0x0008 = 0 -- No 'not for repl' property
			if @colname is not null
			begin
                exec @retcode  = dbo.sp_replupdateschema @source_object
				-- Mark 'not for repl'
				update syscolumns set colstat = colstat | 0x0008 where
					id = @objid and name = @colname
				-- Single to refresh the object cache.
                exec @retcode  = dbo.sp_replupdateschema @source_object
				IF @@ERROR <> 0 OR @retcode <> 0
					goto FAILURE
			end

		end

        select @artid = artid from sysmergearticles where objid = @objid
        select @statusid = 1  /*default status is inactive */

        if @artid is NULL
            begin
                set @artid = newid()
                if @@ERROR <> 0
                    goto FAILURE
                execute @retcode = dbo.sp_MSgentablenickname @tablenick output, @nickname, @objid
                if @@ERROR <> 0 OR @retcode <> 0
                    goto FAILURE
            end
        /* Clone the article properties if article has already been published (in a different pub) */
        else
            begin
            /*
            ** Parameter Check:  @article, @publication.
            ** Check if the table already exists in this publication.
            */
            if exists (select * from sysmergearticles
                where pubid = @pubid AND artid = @artid)
                begin
                    raiserror (21292, 16, -1, @source_object)
                    goto FAILURE
                end
            
            /* Make sure that coltracking option matches */
            if exists (select * from sysmergearticles where artid = @artid and
                         column_tracking <> @column_tracking_id)
                begin
                    raiserror (20030, 16, -1, @article)
                    goto FAILURE
                end

            /* Reuse the article nickname if article has already been published (in a different pub)*/
            select @tablenick = nickname from sysmergearticles where artid = @artid
            if @tablenick IS NULL
                goto FAILURE
                
            /* Make sure that @resolver_clsid matches the existing resolver_clsid */
            select @resolver_clsid_old = resolver_clsid from sysmergearticles where artid = @artid 
            if ((@resolver_clsid IS NULL AND @resolver_clsid_old IS NOT NULL) OR
                (@resolver_clsid IS NOT NULL AND @resolver_clsid_old IS NULL) OR
                (@resolver_clsid IS NOT NULL AND @resolver_clsid_old IS NOT NULL AND @resolver_clsid_old <> @resolver_clsid))
                begin
                    raiserror (20037, 16, -1, @article)
                    goto FAILURE
                end

            /* Insert to articles, copying some stuff from other article row */
            insert into sysmergearticles (name, type, objid, sync_objid, artid, description,
                    pre_creation_command, pubid, nickname, column_tracking, status,
                    creation_script, article_resolver,
                    resolver_clsid, schema_option, 
                    destination_object, destination_owner, subset_filterclause, view_type, resolver_info, gen_cur, 
                    missing_cols, missing_col_count, excluded_cols, excluded_col_count, identity_support,
                    before_image_objid, before_view_objid, verify_resolver_signature, allow_interactive_resolver, 
                    fast_multicol_updateproc, check_permissions)
                -- use top 1, distinct could return more than one matching row if status different on partitioned articles
                select top 1 @article, type, objid, @sync_objid, @artid, @description, @precmdid,
                    @pubid, nickname, column_tracking, @statusid, @creation_script,
                    article_resolver, resolver_clsid, @schema_option, @destination_object, @destination_owner, @subset_filterclause, 
                    0, resolver_info, gen_cur, 0x00, 0, 0x00,0, identity_support,
                    before_image_objid, before_view_objid, verify_resolver_signature, allow_interactive_resolver, 
                    fast_multicol_updateproc, check_permissions
                    from sysmergearticles where artid = @artid

            /* Jump to end of transaction  */
            goto DONE_TRAN
            end

        /* Add the specific GUID based replication columns to sysmergearticles */
        insert sysmergearticles (name, objid, sync_objid, artid, type, description, pubid, nickname, 
                column_tracking, status, schema_option, pre_creation_command, destination_object, destination_owner, 
                article_resolver, resolver_clsid, subset_filterclause, view_type, resolver_info, columns,
                missing_cols, missing_col_count, excluded_cols, excluded_col_count, identity_support,
                before_image_objid, before_view_objid, verify_resolver_signature, creation_script, allow_interactive_resolver, 
                fast_multicol_updateproc, check_permissions)
        values (@article, @objid, @sync_objid, @artid, @typeid, @description, @pubid, @tablenick, 
                @column_tracking_id, @statusid, @schema_option, @precmdid, @destination_object, @destination_owner, 
                @article_resolver, @resolver_clsid, @subset_filterclause, 0, @resolver_info, NULL,
                 0x00, 0, 0x00,0, @identity_support, NULL, NULL, @verify_resolver_signature, @creation_script, @allow_interactive_bit, 
                 @fast_multicol_updateproc_bit, @check_permissions)
        if @@ERROR <> 0
            goto FAILURE

        exec @retcode = dbo.sp_replupdateschema @qualified_name
        if @@ERROR <> 0 or @retcode <> 0
            goto FAILURE
        update sysobjects set replinfo = (replinfo | @merge_pub_object_bit) where id = @objid
        if @@ERROR <> 0
            goto FAILURE

        /* set up the article's gen-cur */
		set @genguid = newid()
		set @dt = getdate()

		exec @retcode=sp_MSgetreplnick @nickname = @replnick out
		if @retcode<>0 or @@error<>0 
			goto FAILURE

		/*
		** If there are no zero generation tombstones or rows, add a dummy row in there. 
		*/
	   	if not exists (select * from MSmerge_genhistory)
			begin
			begin tran
		   	insert into MSmerge_genhistory (guidsrc, guidlocal, generation, art_nick, nicknames, coldate) values
				(@genguid, @genguid, 1, 0, @replnick, @dt)
			if (@@error <> 0)
				begin
				goto FAILURE
				end	
			commit tran
			end

		/* Make a generation and update the article's gen_cur */
		select @gen = max(gen_cur) from sysmergearticles (updlock holdlock) where nickname = @tablenick and gen_cur is not null
		if @gen is null
			begin
			set @genguid = newid()
			set @dt = getdate()
			insert into MSmerge_genhistory (guidsrc, guidlocal, generation, art_nick, nicknames, coldate) 
				select @genguid, @guidnull, COALESCE(1 + max(generation), 1), @tablenick, @replnick, @dt from MSmerge_genhistory (updlock)
			if (@@error <> 0)
				goto FAILURE
			select @gen =  generation from MSmerge_genhistory where guidsrc = @genguid
			update sysmergearticles set gen_cur = @gen where nickname = @tablenick
			if (@@error <> 0)
				goto FAILURE
			end

        /* If the article status is active then prepare the article for merge replication */
        if @status = 'active'
            begin
                /* Get a holdlock on the underlying table */
                select @cmd = 'select * into #tab1 from '
                select @cmd = @cmd + @qualified_name 
                select @cmd = @cmd + '(TABLOCK HOLDLOCK) where 1 = 2 '
                execute(@cmd)

                /* Add the guid column to the user table */
                execute @retcode = dbo.sp_MSaddguidcolumn @source_owner, @source_object
                if @@ERROR <> 0 OR  @retcode <> 0  -- NOTE: new change
                    goto FAILURE

                /* Create an index on the rowguid column in the user table */
                execute @retcode = dbo.sp_MSaddguidindex @publication, @source_owner, @source_object
                if @@ERROR <> 0 OR @retcode <> 0
                    goto FAILURE

                /* Create the merge triggers on the base table */
                execute @retcode = dbo.sp_MSaddmergetriggers @qualified_name, NULL, @column_tracking_id
                if @@ERROR <> 0 OR @retcode <> 0
                    goto FAILURE 

                /* Create the merge insert/update stored procedures for the base table */
                execute @retcode = dbo.sp_MSsetartprocs @publication, @article
                if @@ERROR <> 0 OR @retcode <> 0
                    goto FAILURE

                /* Set the article status to be active so that Snapshot does not do this again */
                select @statusid = 2 /* Active article */
                update sysmergearticles set status = @statusid where artid = @artid
                if @@ERROR <> 0 
                    goto FAILURE
            end
        
DONE_TRAN:				
			/* identity range control is row level. So one the one is needed for each table */
			if @identity_support=1 and @already_published=0
			begin

				exec @retcode = sp_MSreseed @objid=@objid,
										@next_seed=@next_seed,
										@range = @pub_identity_range,
										@is_publisher=@is_publisher,
										@check_only = 1,
										@initial_setting = @initial_setting,
										@bound_value = @identity_so_far
				if @@ERROR<>0 or @retcode<>0
						goto FAILURE
			end

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

        IF @ver_partition = 0 --meanning no vertical partition needed.
        BEGIN
            EXECUTE @retcode  = dbo.sp_mergearticlecolumn @publication=@publication, @article=@article, @schema_replication='true'            
			IF @@ERROR <> 0 OR @retcode <> 0
            BEGIN
				RAISERROR(21198, 16, -1)
				goto FAILURE
            END
        END

        /*
        **  Set all bits to '1' for all columns in the primary key.
        */
        ELSE
        BEGIN
            SELECT @indid = indid FROM sysindexes WHERE id = @objid AND (status & 2048) <> 0    /* PK index */
            /*
            **  First we'll figure out what the keys are.
            */
            SELECT @i = 1
            WHILE (@i <= 16)
            BEGIN
                SELECT @pkkey = INDEX_COL(@source_object, @indid, @i)
                if @pkkey is NULL
                    break
                EXECUTE @retcode  = dbo.sp_mergearticlecolumn @publication, @article, @pkkey, 'add'
                IF @@ERROR <> 0 OR @retcode <> 0
                BEGIN
					RAISERROR(21198, 16, -1)
					goto FAILURE
				END
                select @i = @i + 1
            END
			/*
			** make sure any existing rowguidcol is in the partition. We can not live without it.
			*/
			select @colname = name from syscolumns where id = @objid 
				and ColumnProperty(@objid, name, 'isrowguidcol') = 1
			if @colname is not NULL
			BEGIN
				EXECUTE @retcode  = dbo.sp_mergearticlecolumn @publication, @article, @colname, 'add'
				if @@error<>0 or @retcode<>0
					goto FAILURE
			END
		END

        exec @retcode = sp_MSfillupmissingcols @publication, @source_object
        if @retcode<>0 or @@ERROR<>0
        	goto FAILURE

        /*
        ** For articles with subset filter clause - set the pub type to subset
        */
        if len(@subset_filterclause) > 0
            begin
                execute @retcode = dbo.sp_MSsubsetpublication @publication
                if @@ERROR <> 0 or @retcode<>0
                    goto FAILURE
            end                     

        SELECT @dbname =  DB_NAME()
        
        SELECT @distproc = RTRIM(@distributor) + '.' + @distribdb + 
            '.dbo.sp_MSadd_article'
        EXECUTE @retcode = @distproc
            @publisher = @@SERVERNAME,
            @publisher_db = @dbname,
            @publication = @publication,
            @article = @article,
            @destination_object = @destination_object,
            @source_owner = @source_owner,
            @source_object = @source_object,
            @description = @description
            -- @article_id = NULL
        IF @@ERROR <> 0 or @retcode <> 0
            BEGIN
                goto FAILURE
            END

		if @bump_to_80=1
			begin
				exec @retcode = sp_MSBumpupCompLevel @pubid, 40
				if @@ERROR<>0 or @retcode<>0
					goto FAILURE
			end
		if @needs_pickup=1
			begin
				declare @needs_pick_value int 
				select @needs_pick_value=5 --new_inactive status
				update sysmergearticles set status=@needs_pick_value where artid = @artid and pubid=@pubid
				if @@ERROR<>0
					goto FAILURE

                /* 
                ** Add the guid column to the user table if needed, cause snapshot_ready>0 would imply
                ** this article has got a rowguid column. No need to add index, triggers, or procedures
                ** as snapshot run will take care of those.
                */
                execute @retcode = dbo.sp_MSaddguidcolumn @source_owner, @source_object
                if @@ERROR <> 0 OR  @retcode <> 0  -- NOTE: new change
                    goto FAILURE
			end

        COMMIT TRAN 

        /* If the article status is active adding the merge triggers to the base table */
         
        return (0)
FAILURE:
        RAISERROR (20009, 16, -1, @article, @publication)
        if @@TRANCOUNT > 0
        begin
            ROLLBACK TRANSACTION sp_addmergearticle
            COMMIT TRANSACTION
        end
        return (1)
go

exec dbo.sp_MS_marksystemobject sp_addmergearticle
go

grant execute on dbo.sp_addmergearticle to public
go

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

CREATE PROCEDURE sp_MSchangemergeschemaarticle (
    @pubid uniqueidentifier,
    @artid uniqueidentifier,
    @property sysname,
    @value nvarchar(2000)
    ) AS
    
    set nocount on

    /*
    ** No need to bump the compatibility level
    ** here as the compatibility level for
    ** a publication with a schema-only article 
    ** is already 80
    */
    declare @schema_option   binary(8)
    declare @precmdid        tinyint
    declare @creation_script nvarchar(255)
    declare @statusid        tinyint
    declare @type            tinyint
    declare @source_object   sysname
    declare @valid_schema_options int

    /* 
    ** Parameter Check: @property
    ** Check to make sure that @property is a valid property in 
    ** sysmergeschemaarticles.
    */
    select @property = lower(@property)
    if @property NOT IN ('description',
                         'pre_creation_command',
                         'creation_script',
                         'status',
                         'schema_option',
                         'destination_owner',
                         'destination_object')
    begin
        raiserror(21224, 16, -1, @property)
        return (1)
    end  

    select @type = type, @source_object = object_name(objid)
      from sysmergeextendedarticlesview
     where pubid = @pubid
       and artid = @artid 

    -- Since all property changes will take the form of 
    -- simple update stataments, no transaction will be 
    -- started. 

    if @property = N'description'
    begin
        update sysmergeschemaarticles 
           set description = @value 
         where pubid = @pubid
           and artid = @artid
            
        if @@error <> 0
            return 1
    end
    else if @property = N'pre_creation_command'
    begin
        /*
        ** The value for the pre_creation_command
        ** property must be either 'none' or 'drop'  
        */
        select @value = lower(@value)
        if @value not in (N'none', N'drop')
        begin
            raiserror(21223, 16, -1)
            return (1)
        end
 
        if @value = N'none'
            select @precmdid = 0
        else if @value = N'drop'
            select @precmdid = 1 
            
        update sysmergeschemaarticles
           set pre_creation_command = @precmdid
         where pubid = @pubid
           and artid = @artid

        if @@error <> 0
            return (1)

    end   
    else if @property = N'creation_script'
    begin
        if @value is NULL or @value = N''
        begin
            /*
            ** Existing schema option must not be 
            ** 0x0000000000000000 if the creation_script 
            ** path has to be set to null
            */

            select @schema_option = schema_option from sysmergeschemaarticles
            where pubid = @pubid and artid = @artid
/*            
            if @schema_option = 0x0000000000000000
            begin
                raiserror(21218, 16, -1)
                return (1)
            end
*/

        end

        update sysmergeschemaarticles 
           set creation_script = @value
         where pubid = @pubid 
           and artid = @artid     

        if @@error <> 0
            return (1)

    end 
    else if @property = N'status'
    begin
        select @value = lower(@value)

        if @value not in ('active', 'unsynced')
        begin
            raiserror(20075, 16, -1)
            return (1)
        end

        if @value = N'unsynced'
            select @statusid = 1
        else if @value = N'active'
            select @statusid = 2

        update sysmergeschemaarticles
           set status = @statusid 
         where pubid = @pubid
           and artid = @artid
        
        if @@error <> 0
            return (1)
    end
    else if @property = N'schema_option'
    begin
       
        if @value is null
        begin
            raiserror(14146, 16,1)
            return (1)
        end

        create table #tab_changeschemaarticle (value varbinary(8) NULL)
        if @@error <> 0
        begin
            return(1)
        end
    
        exec ('insert #tab_changeschemaarticle values (' + 
            @value + ')')    
    
        if @@error <> 0
        begin
            drop table #tab_changeschemaarticle    
            return (1)
        end
    
        select @schema_option = fn_replprepadbinary8(value) 
          from #tab_changeschemaarticle
     
        /*
        ** schema_option can only contain the bits 0x0000000000000001 and
        ** 0x0000000000002000
        ** for schema only articles except view. View articles can contain 
        ** the options 0x0000000000000010, 0x0000000000000020, and 0x0000000000000100 
        ** in addition to the aforementioned options.
        */
        if @type = 0x40
        begin

            -- Since only the lower 32 bits of @schema_option are
            -- currently, the following check is sufficient.
            declare @schema_option_lodword int
            select @valid_schema_options = 0x2151
            select @schema_option_lodword = fn_replgetbinary8lodword(@schema_option)
            if (@schema_option_lodword & ~@valid_schema_options) <> 0
            begin
                raiserror (21229, 16, -1)
                return (1)
            end
        end
        else if @schema_option not in (0x0000000000000000,
                                       0x0000000000000001,
                                       0x0000000000002000,
                                       0x0000000000002001)
        begin
            drop table #tab_changeschemaarticle 
            raiserror (21222, 16, -1)
            return (1)
        end

        if exists (select * from #tab_changeschemaarticle 
                    where value = 0x0000000000000000)
        begin

            select @creation_script = NULL
            select @creation_script = creation_script 
              from sysmergeschemaarticles
             where pubid = @pubid
               and artid = @artid
/*            
            if @creation_script is NULL or 
               @creation_script = N''
            begin
                raiserror(21218, 16, -1) 
                drop table #tab_changeschemaarticle    
                return (1)
            end
*/
        end 
            
        update sysmergeschemaarticles
           set schema_option = fn_replprepadbinary8(tab.value)
          from #tab_changeschemaarticle tab
         where pubid = @pubid 
           and artid = @artid
        
        if @@error <> 0
        begin
            drop table #tab_changeschemaarticle    
            return (1)
        end

        drop table #tab_changeschemaarticle    
            
        if @@error <> 0
            return (1)
    end
    else if @property = N'destination_owner'
    begin
        select @value = rtrim(@value) 
        update sysmergeschemaarticles 
           set destination_owner = @value
         where pubid = @pubid
           and artid = @artid
        
        if @@error <> 0
            return (1)
    end
    else if @property = N'destination_object'
    begin
        select @value = rtrim(@value) 
        if @value is null or @value = ''
        begin
            select @value = @source_object
        end
        update sysmergeschemaarticles
           set destination_object = @value
         where pubid = @pubid
           and artid = @artid        
    end

    return (0)        

go

exec dbo.sp_MS_marksystemobject sp_MSchangemergeschemaarticle
go

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

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

    SET NOCOUNT ON

    /*
    ** Declarations.
    */
    declare @sp_resolver			sysname
    declare @db_name            sysname
    declare @intvalue			bigint
    declare @artid              uniqueidentifier
    declare @pubid              uniqueidentifier
    declare @artidstr           nvarchar(38)
    declare @object_view		sysname
	declare @vertical			int
    declare @pubidstr           nvarchar(38)
    declare @object             sysname
    declare @owner              sysname
    declare @identity_support 	int
    declare @column_list		nvarchar(4000)
    declare @resolver_clsid     nvarchar(50)
    declare @article_resolver   nvarchar(255)
    declare @retcode            int
    declare @statusid           int
    declare @precmdid           tinyint
    declare @regkey             nvarchar(255)
    declare @distributor        sysname
    declare @distribdb        sysname
    declare @distproc           nvarchar(300)
    declare @snapshot_ready		int
    declare @schemaversion      int 
    declare @objid				int
    declare @schemaguid         uniqueidentifier
    declare @schematype         int
    declare @schematext         nvarchar(2000)
    declare @type               tinyint
    declare @allow_interactive_bit	int
    declare @additive_resolver		sysname
    declare @average_resolver		sysname
    declare @mindate_resolver		sysname
    declare @maxdate_resolver		sysname
    declare @minimum_resolver		sysname
    declare @maximum_resolver		sysname
    declare @mergetxt_resolver		sysname
    declare @pricolumn_resolver		sysname
	declare @pre_command			int
	declare @coltrack				int

	declare @qual_object_view		nvarchar(140)
	declare @qual_object			nvarchar(140)
	declare @quoted_object			nvarchar(140)
    declare @bump_to_80             bit
    declare @schema_option          binary(8)
    declare @old_schema_option      binary(8)

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

	select @db_name = db_name()
    select @bump_to_80 = 0

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

    if (select category & 4
          FROM master..sysdatabases
         WHERE name = @db_name collate database_default) = 0

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

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

    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, 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:  @property.
    ** If the @property parameter is NULL, print the options.
    */

    if @property IS NULL
        BEGIN
            CREATE TABLE #tab1 (properties sysname collate database_default)
            INSERT INTO #tab1 VALUES ('description')
            INSERT INTO #tab1 VALUES ('pre_creation_command')
            INSERT INTO #tab1 VALUES ('creation_script')
            INSERT INTO #tab1 VALUES ('column_tracking (table article only)')
            INSERT INTO #tab1 VALUES ('article_resolver (table article only)')
            INSERT INTO #tab1 VALUES ('resolver_info (table article only)')
            INSERT INTO #tab1 VALUES ('status')
            INSERT INTO #tab1 VALUES ('subset_filterclause (table article only)')
            INSERT INTO #tab1 VALUES ('schema_option')
            INSERT INTO #tab1 VALUES ('destination_owner')
   			INSERT INTO #tab1 VALUES ('pub_identity_range (table article only)')
			INSERT INTO #tab1 VALUES ('identity_range (table article only)')
			INSERT INTO #tab1 VALUES ('threshold (table article only)')
			INSERT INTO #tab1 VALUES ('verify_resolver_signature')
			INSERT INTO #tab1 VALUES ('allow_interactive_resolver')
			INSERT INTO #tab1 VALUES ('check_permissions')
            select * FROM #tab1
            RETURN (0)
        END

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

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

    select @artid = artid, 
           @type = type,
           @old_schema_option = schema_option,
           @objid = objid
     FROM sysmergeextendedarticlesview
        WHERE name = @article AND pubid = @pubid
    if @artid IS NULL
        BEGIN
            RAISERROR (20027, 16, -1, @article)
            RETURN (1)
        END

	select @pre_command=pre_creation_command, @identity_support = identity_support from sysmergearticles where pubid=@pubid and artid=@artid
    /*
    ** Handle the changing of schema only articles property
    ** in a different procedure
    */
    if @type in (0x20, 0x40, 0x80)
        begin
            exec @retcode = sp_MSchangemergeschemaarticle @pubid = @pubid,
                                                           @artid = @artid,
                                                           @property = @property,
                                                           @value = @value
            return @retcode
        end             

    /*
    ** Parameter Check:  @property.
    ** Check to make sure that @property is a valid property in
    ** sysmergearticles.
    */
    if @property IS NULL OR LOWER(@property) NOT in ('name',
                                                     'description',
                                                     'pre_creation_command',
                                                     'creation_script',
                                                     'column_tracking',
                                                     'article_resolver',    
                                                     'resolver_info',
                                                     'status',
                                                     'subset_filterclause',
                                                     'schema_option',
                                                     'pub_identity_range',
                                                     'identity_range',
                                                     'threshold',
                                                     'verify_resolver_signature',
                                                     'check_permissions',
                                                     'allow_interactive_resolver',
                                                     'destination_owner')
        BEGIN
            RAISERROR (21259, 16, -1, @property)
            RETURN (1)
        END
        
	-- COMMENT: the following article properties can not be changed at republisher side
	if	LOWER(@property) in ('article_resolver','resolver_info','column_tracking',
					'allow_interactive_resolver','verify_resolver_signature') and 
			exists (select * from sysmergearticles where objid=@objid and pubid not in 
				(select pubid from sysmergepublications where UPPER(publisher)=UPPER(@@servername) 
					and publisher_db=@db_name))
		
		BEGIN
			RAISERROR(21400, 16, -1, @article)
			return (1)
		END

	if @identity_support=0 and LOWER(@property) in ('pub_identity_range','identity_range','threshold')
		begin
			RAISERROR(21257, 16, -1, @property, @article)
			return (1)
		end

	exec @retcode = dbo.sp_helpdistributor @rpcsrvname = @distributor OUTPUT,
           			@distribdb   = @distribdb OUTPUT
	IF @@ERROR <> 0 or @retcode <> 0 or @distributor is NULL
	BEGIN
		RAISERROR (20036, 16, -1)
		RETURN (1)
	END

	SELECT @distproc = RTRIM(@distributor) + '.' + RTRIM(@distribdb) + '.dbo.sp_MSadjust_pub_identity'
	select @objid=objid, @object = object_name(objid), @vertical=vertical_partition from sysmergearticles where artid=@artid and pubid=@pubid
    set @artidstr = '''' + convert(nchar(36), @artid) + '''' 
    set @pubidstr = '''' + convert(nchar(36), @pubid) + '''' 
    select @sp_resolver 		= 'Microsoft SQLServer Stored Procedure Resolver'
    select @additive_resolver 	= 'Microsoft SQL Server Additive Conflict Resolver'
    select @average_resolver 	= 'Microsoft SQL Server Averaging Conflict Resolver'
    select @minimum_resolver 	= 'Microsoft SQL Server Minimum Conflict Resolver'
    select @maximum_resolver 	= 'Microsoft SQL Server Maximum Conflict Resolver'
    select @mindate_resolver 	= 'Microsoft SQL Server DATETIME (Earlier Wins) Conflict Resolver'
    select @maxdate_resolver 	= 'Microsoft SQL Server DATETIME (Later Wins) Conflict Resolver'
    select @mergetxt_resolver 	= 'Microsoft SQL Server Merge Text Columns Conflict Resolver'
    select @pricolumn_resolver 	= 'Microsoft SQL Server Priority Column Resolver'

	BEGIN TRAN
	save TRAN changemergearticle
	/*
	** Changing of the following properties would require a snapshot rerun.and reinit, if snapshot is ready
	*/
	if LOWER(@property) in ('pre_creation_command','creation_script',
                                                     'column_tracking',
                                                     'subset_filterclause',
                                                     'schema_option',
                                                     'destination_owner',
                                                     'check_permissions')
		and	@snapshot_ready > 0
	begin
		if @pre_command<>1 -- 1 means'drop': which is the only option that support reintialization
		begin
			raiserror(21416, 16, -1, @property, @article)
			goto UNDO			
		end
		if @force_invalidate_snapshot = 0
		begin
			raiserror(20607, 16, -1)
			goto UNDO
		end
		if @force_reinit_subscription = 0
		begin
			raiserror(20608, 16, -1)
			goto UNDO
		end
		
		update sysmergepublications set snapshot_ready=2 where pubid=@pubid and snapshot_ready=1
		if @@ERROR<>0	
			GOTO UNDO
		exec @retcode=sp_MSreinitmergepublication @publication
		if @@ERROR<>0 or @retcode<>0
			GOTO UNDO 
	end

    /*
    ** Change the property.
    */
    if LOWER(@property) = 'column_tracking'
        BEGIN
            /*
            ** Check to make sure that we have a valid type.
            */
            if LOWER(@value) NOT IN ('true', 'false')
                BEGIN
                    RAISERROR (14137, 16, -1)
                    goto UNDO
                END

            /*
            ** Update the syssubsetdefintions table with the new column tracking.
            */
            if LOWER(@value) = 'true'
                    update sysmergearticles set column_tracking = 1 where artid=@artid      
            else
                    update sysmergearticles set column_tracking = 0 where artid=@artid
            if @@ERROR <> 0 
                goto UNDO

        END

	
	/* pub_identity_range is not stored at distribution database because it is not interested in this value.*/   
	if LOWER(@property) = 'pub_identity_range'
        BEGIN
			select @intvalue = convert(bigint, @value)
			if @intvalue<0
			begin
				raiserror(21232, 16, -1)
				goto UNDO
			end
			update MSrepl_identity_range set pub_range=@intvalue where objid=@objid
			if @@ERROR<>0
				goto UNDO		
        END
	 
	/* the property of check permissions is a bitmask of which operation do we want to check for*/   
	if LOWER(@property) = 'check_permissions'
        BEGIN
			select @intvalue = convert(int, @value)
			if @intvalue<0
			begin
				raiserror(21232, 16, -1)
				goto UNDO
			end
			update sysmergearticles set check_permissions=@intvalue
			if @@ERROR<>0
				goto UNDO		
        END


	if LOWER(@property) = 'identity_range'
        BEGIN
			select @intvalue = convert(bigint, @value)
			if @intvalue<0
			begin
				raiserror(21232, 16, -1)
				goto UNDO
			end

			update MSrepl_identity_range set range=@intvalue where objid=@objid
			if @@ERROR<>0
				goto UNDO
			exec  @retcode=@distproc @publisher=@@SERVERNAME,
										@publisher_db=@db_name,
										@tablename=@object,
										@range=@intvalue
				if @retcode<>0 or @@ERROR<>0
					goto UNDO

        END

	if LOWER(@property) = 'threshold'
        BEGIN
			select @intvalue = convert(int, @value)
			if @intvalue<0 or @intvalue>100
			begin
				raiserror(21241, 16, -1)
				goto UNDO
			end
			update MSrepl_identity_range set threshold=@intvalue where objid=@objid
			if @@ERROR<>0
				goto UNDO
			exec  @retcode=@distproc @publisher=@@SERVERNAME,
										@publisher_db=@db_name,
										@tablename=@object,
										@threshold=@intvalue
				if @retcode<>0 or @@ERROR<>0
					goto UNDO

        END

     if LOWER(@property)='description'
        BEGIN
        UPDATE sysmergearticles  SET description = @value WHERE artid = @artid and pubid = @pubid
            AND pubid = @pubid
        if @@ERROR <> 0 
                goto UNDO
    END

    if LOWER(@property) ='creation_script'
        BEGIN
            update sysmergearticles set creation_script=@value where artid=@artid and pubid=@pubid
            if @@ERROR <> 0 
                goto UNDO
        END

    if LOWER(@property) = 'subset_filterclause'
        BEGIN
			if @value is not null and @value<>''
			begin
        	/* check the validity of subset_filterclause */
			select @object_view=@object
			select @quoted_object=QUOTENAME(@object)	
			
			exec @retcode = sp_MSget_qualified_name @objid, @qual_object OUTPUT
			if @@ERROR<>0 or @retcode<>0
				goto UNDO
			select @qual_object_view=@qual_object
			
			if @vertical=1
			begin
			  select @object_view='TEMP_VIEW_' + @object
			  select @qual_object_view = QUOTENAME(@object_view)
			  exec @retcode = sp_MSgetcolumnlist @pubid, @column_list OUTPUT, @objid
			  exec ('create view ' + @qual_object_view + ' as select ' + @column_list + ' from ' + @quoted_object)
			  if @@ERROR<>0
			  begin
			  	raiserror(21256, 16, -1, @value, @object)
				goto UNDO
			  end
			end
        	
			exec ('declare @test int select @test=1 from ' + @qual_object_view + ' ' + @quoted_object + ' where ' + @value)
			if @@ERROR<>0
				begin
					if @vertical=1
						exec('drop view ' + @qual_object_view)
					raiserror(21256, 16, -1, @value, @object)
					goto UNDO
				end
			end
			if @vertical=1
				exec('drop view ' + @qual_object_view)

            update sysmergearticles set subset_filterclause = @value where artid=@artid and pubid=@pubid
            if @@ERROR<>0 goto UNDO
            
            /*
            ** set the pub type to subset or full as appropriate
            */
            execute @retcode = dbo.sp_MSsubsetpublication @publication
            if @@ERROR <> 0 OR @retcode <> 0
                goto UNDO
                
        END

    if LOWER(@property) ='article_resolver'
        BEGIN

            if @value IS NULL OR @value = 'default' OR @value = ''
                begin
                    set @article_resolver = NULL
                    set @resolver_clsid = NULL
                end                 
                    
            else
                begin
                    EXECUTE @retcode = master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
                                      'SOFTWARE\Microsoft\Microsoft SQL Server\80\Replication\ArticleResolver',
                                      @value,
                                      @param = @resolver_clsid OUTPUT
                    IF @@ERROR <> 0 or @retcode <> 0 or @resolver_clsid IS NULL
                        BEGIN
                            RAISERROR (20020, 16, -1)
                            goto UNDO
                        END
                end                     
                    
            /*
            ** Update the appropriate column in sysmergearticles with the new article resolver name.
            ** Note this could affect multiple publication if the same table spans publications
            */

            /* NOTE: new change */
                exec @retcode = dbo.sp_MSchangearticleresolver @value, @resolver_clsid, @artid
                if @@ERROR <> 0 OR @retcode <> 0
                    begin
                        goto UNDO                          
                    end  
                
                declare one_pub CURSOR LOCAL FAST_FORWARD FOR 
                    select DISTINCT pubid from sysmergearticles where artid=@artid
                FOR READ ONLY
                open one_pub
                fetch next from one_pub into @pubid
                 
                while (@@fetch_status <> -1)
                    begin
                        select @snapshot_ready=snapshot_ready from sysmergepublications 
					        where pubid = @pubid
					    /* Insert the sp_MSchangearticleresolver 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 @schematype = 8
		                        if @value is not NULL and @value <> ''
		                            select @schematext = 'exec dbo.sp_MSchangearticleresolver ' + '''' + @value + '''' + ',' + '''' + @resolver_clsid + '''' + ',' + '''' + convert(nchar(36), @artid) + ''''
		                        else
		                            select @schematext = 'exec dbo.sp_MSchangearticleresolver NULL, NULL,' + '''' + convert(nchar(36), @artid) + ''''
		                             
		                        exec @retcode = dbo.sp_MSinsertschemachange @pubid, @artid, @schemaversion, @schemaguid, @schematype, @schematext 
	                            if @@ERROR <> 0 OR @retcode <> 0
									goto UNDO
							end		                                
                        fetch next from one_pub into @pubid
                    end 
                close one_pub
                deallocate one_pub

        END /* for property = 'article_resolver' */


    if LOWER(@property) ='resolver_info'
        BEGIN
	        /* allow non-sp's as resolver info; don't change the resolver class */
            select 	@article_resolver = article_resolver, 
            		@resolver_clsid = resolver_clsid from
                    sysmergearticles where artid = @artid

            -- Use empty string for nulls so that schema text won't be null
            if @article_resolver is null set @article_resolver = ''
            if @resolver_clsid is null set @resolver_clsid = ''
            if @value is null set @value = ''

			/* The following resolvers expect the @resolver_info to be NON NULL */
			if  @article_resolver = @sp_resolver or 
				@article_resolver = @additive_resolver or
				@article_resolver = @average_resolver or
				@article_resolver = @minimum_resolver or
				@article_resolver = @maximum_resolver or
				@article_resolver = @mindate_resolver or
				@article_resolver = @maxdate_resolver or
				@article_resolver = @mergetxt_resolver or
				@article_resolver = @pricolumn_resolver
				begin
				    if @value IS NULL or @value = ''
				        begin
	            			RAISERROR (21301, 16, -1, @article_resolver)
							goto UNDO
						end
				end

			/* The SP resolver expect the @resolver_info to be the name of sp */
			if  @article_resolver = @sp_resolver
				begin
					if not exists (select * from sysobjects where id = object_id(@value) and ( type = 'P' or type = 'X'))
						begin
							raiserror(21343, 16, -1, @value)
							goto UNDO
						end
				end
			/*
			** If article resolver is 'mindate/maxdate resolver', make sure that resolver_info refers to a column that is of datatype 'datetime' or smalldatetime
			*/
			if  @article_resolver = @mindate_resolver or
				@article_resolver = @maxdate_resolver
				begin
					if not exists (select * from syscolumns where id = @objid and name=@value and type_name(xtype)='datetime' or type_name(xtype) = 'smalldatetime' )
						begin
				            RAISERROR (21302, 16, -1, @article_resolver)
							goto UNDO
						end
				end

			/* The following resolvers expect the article to be column trcaked - warn that the default resolver migh be used */
			if  @article_resolver = @additive_resolver or
				@article_resolver = @average_resolver
				begin
		            select @coltrack = column_tracking from sysmergearticles where artid = @artid
					if @coltrack = 0
						begin
				            RAISERROR (21303, 10, -1, @article, @article_resolver)
						end
						
				end
				

            /*
            ** Update the appropriate column in sysmergearticles with the new resolver info.
            ** Note this could affect multiple publication if the same table spans publications
            */
                exec @retcode = dbo.sp_MSchangearticleresolver @article_resolver, @resolver_clsid, @artid, @value
                if @@ERROR <> 0 OR @retcode <> 0
                    begin
                        goto UNDO                          
                    end         
                declare one_pub CURSOR LOCAL FAST_FORWARD FOR 
                    select DISTINCT pubid from sysmergearticles where artid=@artid
                FOR READ ONLY
                open one_pub
                fetch next from one_pub into @pubid
                 
                while (@@fetch_status <> -1)
                    begin
                        select @snapshot_ready=snapshot_ready from sysmergepublications 
					        where pubid = @pubid
					    /* Insert the sp_MSchangearticleresolver 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 @schematype = 8
		                        select @schematext = 'exec dbo.sp_MSchangearticleresolver ' + '''' + @article_resolver + '''' + ',' + '''' + @resolver_clsid + '''' + ',' + '''' + convert(nchar(36), @artid) + '''' + ',' + '''' + @value + ''''
		                         
		                        exec @retcode = dbo.sp_MSinsertschemachange @pubid, @artid, @schemaversion, @schemaguid, @schematype, @schematext 
	                            if @@ERROR <> 0 OR @retcode <> 0
	                                begin
	                                	goto UNDO                          
	                                end
							end	                         
                        fetch next from one_pub into @pubid
                    end 
                close one_pub
                deallocate one_pub

        END /* for property = 'resolver_info' */


    if LOWER(@property) = 'pre_creation_command'
        BEGIN

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

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

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

            if LOWER(@value) = 'none'
                select @precmdid = 0
            else if LOWER(@value) = 'drop'
                select @precmdid = 1
            else if LOWER(@value) = 'delete'
                select @precmdid = 2
            else if LOWER(@value) = 'truncate'
                select @precmdid = 3

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

            if @@ERROR <> 0 
                goto UNDO

        END

    if LOWER(@property) = 'status'
        BEGIN

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

            if LOWER(@value) NOT IN ('active', 'unsynced', 'new_inactive', 'new_active')
                BEGIN
                    RAISERROR (20075, 16, -1)
                    goto UNDO
                END

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

            if LOWER(@value) = 'unsynced'
                select @statusid = 1
            else if LOWER(@value) = 'active'
                select @statusid = 2
            else if LOWER(@value) = 'new_inactive'
            	select @statusid = 5
            else if LOWER(@value) = 'new_active'
            	select @statusid = 6

            /*
            ** Update the article with the new type. The same base table might be 
            ** in multiple publications - so qualify  with pubid.
            */
            UPDATE sysmergearticles
                SET status = @statusid
                WHERE artid = @artid and pubid = @pubid

            if @@ERROR <> 0 
                goto UNDO

        END

    IF LOWER(@property) = 'destination_owner'
        BEGIN
            IF @value IS NULL or @value=''
                select @value = 'dbo'                      
            UPDATE sysmergearticles SET destination_owner = @value
                WHERE artid = @artid
                AND pubid = @pubid
            if @@ERROR <> 0
                BEGIN
                	goto UNDO
                END
        END
	
    IF LOWER(@property) = 'schema_option'
        BEGIN
        
            IF @value IS NULL
                BEGIN
                    RAISERROR(14146, 16,1)
                    goto UNDO
                END

            CREATE TABLE #tab_changearticle (value varbinary(8) NULL)
                                 
            IF @@ERROR <> 0 
                BEGIN
                    goto UNDO
                END
            
            DECLARE @insert_command nvarchar(4000)
            EXEC('insert #tab_changearticle values (' + 
                        @value +')') 
            IF @@ERROR <> 0 
                BEGIN
                    goto UNDO
                END
                      
            SELECT @schema_option = fn_replprepadbinary8(value) 
              FROM #tab_changearticle

            DECLARE @schema_option_lodword INT
            DECLARE @old_schema_option_lodword INT
            DECLARE @xprop_schema_option INT
            DECLARE @collation_schema_option INT
            SELECT @xprop_schema_option = 0x00002000
            SELECT @collation_schema_option = 0x00001000
            SELECT @schema_option_lodword = fn_replgetbinary8lodword(@schema_option)
            SELECT @old_schema_option_lodword = CONVERT(INT, SUBSTRING(@old_schema_option, 5, 4))
            
            -- Raise warnings only when we are enabling the Shiloh specific
            -- options
            IF ((@old_schema_option_lodword & @collation_schema_option) = 0)
                AND 
               ((@schema_option_lodword & @collation_schema_option) <> 0)
            BEGIN
                RAISERROR(21389, 10, -1, @publication)
                SELECT @bump_to_80 = 1
            END
            
            IF((@old_schema_option_lodword & @xprop_schema_option) = 0)
              AND 
              ((@schema_option_lodword & @xprop_schema_option) <> 0)
            BEGIN
                RAISERROR(21390, 10, -1, @publication)
                SELECT @bump_to_80 = 1
            END

            UPDATE sysmergearticles 
               SET schema_option = fn_replprepadbinary8(tab.value) from 
                #tab_changearticle tab 
                WHERE artid = @artid
                AND pubid = @pubid
            if @@ERROR <> 0
                BEGIN
                DROP TABLE #tab_changearticle 
                goto UNDO
                END
                
            DROP TABLE #tab_changearticle 
                                 
            IF @@ERROR <> 0 
                BEGIN
                    goto UNDO
                END

        END

	if LOWER(@property) = 'verify_resolver_signature'
        BEGIN
			if @value NOT IN ('1', '0')
                BEGIN
                    raiserror(21344, 16, -1, '"verify_resolver_signature"')
                    goto UNDO
                END

			update sysmergearticles set verify_resolver_signature = convert(int, @value) 
			where artid = @artid and pubid = @pubid
			if @@ERROR<>0
				goto UNDO
        END

	IF LOWER(@property) = 'allow_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, 'allow_interactive_resolver')
                    goto UNDO
                END

            /* Determine the bit value. */

	        IF LOWER(@value) = 'true'
		        SET @allow_interactive_bit = 1
	        ELSE
		        SET @allow_interactive_bit = 0
	
			/* Update the subscription with the new 'allow_interactive_resolver' value. */
			update sysmergearticles set allow_interactive_resolver = @allow_interactive_bit
				where artid = @artid and pubid = @pubid
            IF @@ERROR <> 0
                BEGIN
                    RAISERROR (14053, 16, -1)
                    goto UNDO
                END

        END

    IF @bump_to_80=1
    BEGIN
        EXEC @retcode = sp_MSBumpupCompLevel @pubid, 40
		IF @@ERROR<>0 or @retcode<>0
            GOTO UNDO
    END
    /*
    ** Return succeed.
    */
	COMMIT TRAN
    RETURN (0)
UNDO:
	if @@TRANCOUNT > 0
    begin
        ROLLBACK TRANSACTION changemergearticle
        COMMIT TRANSACTION
    end
	return (1)
go
exec dbo.sp_MS_marksystemobject sp_changemergearticle
go

grant execute on dbo.sp_changemergearticle to public
go




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

/*
** When calling from the wrapper stored procedure, we make sure that there is one and
** only one of the two input SP is NULL. Therefore, we do no checking here.
*/

CREATE PROCEDURE sp_MSadjustmergeidentity 
@publication		sysname = NULL,
@tablename			sysname = NULL
AS

declare @db_name				sysname
declare @pubid					uniqueidentifier
declare @next_seed				bigint
declare @pub_range				bigint
declare @objid					int
declare @qualname				nvarchar(270)
declare @retcode				int
declare @user_name				sysname		
declare @range					bigint
declare @current_max			bigint
declare @threshold				int
declare @distributor			sysname
declare @distribdb				sysname
declare @distproc				nvarchar(300)
declare @flag					smallint
declare @c_max					bigint
declare @n_seed					bigint
declare @nickname				int
declare @identity_support		int
declare @artid					uniqueidentifier
declare @identity_so_far		bigint
declare @tablelevel				bit

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

select @db_name=db_name()
select @pubid = newid()
select @objid = 0

if (@publication is NULL and @tablename is NULL) or 
 	(@publication is not NULL and @tablename is not NULL)
begin
	raiserror('internal error on nullibility', 16, -1)
	return (1)
end

if @tablename is not NULL
begin
	select @tablelevel = 1
	select @objid = id from sysobjects where name=@tablename
end
else
begin
	select @tablelevel = 0
	select @pubid = pubid from sysmergepublications 
		where name = @publication  and UPPER(publisher)=UPPER(@@servername) and publisher_db=@db_name
end

if exists (select * from sysmergearticles where identity_support<>0 and 
	((pubid=@pubid and @tablelevel=0) or (@tablelevel=1 and objid=@objid)))
begin	
	EXECUTE @retcode = dbo.sp_helpdistributor @rpcsrvname = @distributor OUTPUT, @distribdb   = @distribdb OUTPUT
		IF @@ERROR <> 0 or @retcode <> 0
			return (1)

	declare one_article CURSOR LOCAL FAST_FORWARD FOR 
		select DISTINCT artid from sysmergearticles where identity_support<>0 and
			((pubid=@pubid and @tablelevel=0) or (@tablelevel=1 and objid=@objid))
	open one_article
	fetch one_article into @artid
	while (@@fetch_status<>-1)
	begin
		select @objid=objid, @identity_support=identity_support from sysmergearticles 
				where pubid=@pubid and artid=@artid
		select @tablename=object_name(@objid)
		select @user_name=user_name(uid) from sysobjects where id=@objid
		select @qualname=QUOTENAME(@user_name) + '.' + QUOTENAME(@tablename)
		select @next_seed=NULL, @range=NULL, @threshold=NULL --null if not being updated later

		select @current_max=0, @next_seed=0, @threshold=0, @range=0, @pub_range=0  --make them non-NULL
		SELECT @distproc = RTRIM(@distributor) + '.' + RTRIM(@distribdb) + '.dbo.sp_MScheck_pub_identity'
		exec  @retcode=	@distproc @publisher=@@SERVERNAME,
									  @publisher_db=@db_name,
									  @tablename=@tablename,
									  @range=@range OUTPUT,
									  @current_max=@current_max OUTPUT,
									  @threshold=@threshold OUTPUT,
									  @next_seed = @next_seed OUTPUT,
									  @pub_range=@pub_range OUTPUT
		if @retcode<>0 or @@ERROR<>0
			return (1)

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

		if @flag * 100 * (@identity_so_far - (@current_max + 1 - @pub_range))/@pub_range > @threshold
		begin
			select @c_max=@next_seed + @pub_range - 1
			select @n_seed=@next_seed + @pub_range
			SELECT @distproc = RTRIM(@distributor) + '.' + RTRIM(@distribdb) + '.dbo.sp_MSadjust_pub_identity'
			exec  @retcode=@distproc @publisher=@@SERVERNAME,
									@publisher_db=@db_name,
									@tablename=@tablename,
									@current_max=@c_max,
									@next_seed = @n_seed
			if @retcode<>0 or @@ERROR<>0
				return (1)
			exec @retcode=sp_MSreseed @objid, @next_seed, @pub_range, 1
			if @@ERROR <> 0 or @retcode<>0
				begin
					raiserror(21197, 16, -1)
					return (1)
				end
			select @next_seed=@next_seed + @pub_range
		end
	fetch next from one_article into @artid
	end
end
else
begin
	raiserror(21295, 16, -1, @publication)
	return (1)
end
GO
exec dbo.sp_MS_marksystemobject sp_MSadjustmergeidentity 
go
grant exec on sp_MSadjustmergeidentity to public
go

/*
** This SP is called to see if merge publication is still allowed for current database.
** Merge publishing is disallowed if current DB subscribes as local/anonymous subscriber
** 1 means OK, 0 for publication not allowed.
*/
raiserror('Creating procedure sp_helpallowmerge_publication', 0,1)
GO

CREATE PROCEDURE sp_helpallowmerge_publication 
AS
declare @srvid      int
declare @db_name    sysname

/* Select srvid = 0 for the local server name */
select @srvid = 0
select @db_name = db_name()
if exists (select name from sysobjects where name='sysmergesubscriptions')
    if exists (select priority from sysmergesubscriptions where db_name=@db_name and srvid = @srvid and priority=0)
        begin
            select 0
            RETURN (0)
        end
select 1
GO
exec dbo.sp_MS_marksystemobject sp_helpallowmerge_publication 
go

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

CREATE PROCEDURE sp_helpmergearticle (
    @publication sysname = '%',   /* The publication name */
    @article sysname = '%'        /* The article name */
    ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */
    declare @retcode            int
	/*
    ** Create a temporary table to hold all information.
    */
    declare @helpmergearticle TABLE
        (
            id                      int             identity NOT NULL,
            name                    sysname         collate database_default not null,
            source_owner            sysname         collate database_default not null,
            source_object           sysname         collate database_default not null,      /* converted from objid */
            sync_object_owner       sysname         collate database_default null,
            sync_object             sysname         collate database_default null,      /* converted from sync_objid */
            description             nvarchar(255)   collate database_default null,
            status                  tinyint         NULL,
            creation_script         nvarchar(255)   collate database_default null,
            conflict_table          nvarchar(270)   collate database_default null,
            article_resolver        nvarchar(255)   collate database_default null,
            subset_filterclause     nvarchar(1000)  collate database_default null,
            pre_creation_command    tinyint         NULL, 
            schema_option           binary(8)       NULL,
            type                    smallint        NULL,
            column_tracking         int             NULL,
            resolver_info           nvarchar(255)   collate database_default null,
            vertical_partition		bit				NULL,
            destination_owner		sysname			collate database_default null,
            identity_support		int				NULL,
            pub_identity_range		bigint			NULL,
            identity_range			bigint			NULL,
            threshold				int				NULL,
			verify_resolver_signature int			NULL,
            destination_object      sysname         collate database_default not null,
            allow_interactive_resolver	int			NULL,
			fast_multicol_updateproc	int			NULL,
			check_permissions		int				NULL					
        )
        
    /*
    ** Running sp_help is OK from everywhere, whether enabled for publishing or not
    */
    IF not exists (select * from sysobjects where name= 'sysmergesubscriptions')
        RETURN (0)

    /*
    ** Security Check. To public.
    */
    

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

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

       if @publication <> '%'
        BEGIN
            if NOT EXISTS (select pubid 
                                FROM sysmergepublications
                                WHERE name = @publication and UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name())
                BEGIN
                    RAISERROR (20026, 16, -1, @publication)
                    RETURN (1)
                END

        END

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

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

    if @article <> '%'
        BEGIN
            if NOT EXISTS (select *
                           FROM sysmergeextendedarticlesview
                           WHERE name = @article
                           AND pubid IN (select pubid 
                                         FROM sysmergepublications 
                                         WHERE name like @publication  
                                            and UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name()))
                BEGIN
                    RAISERROR (20027, 16, -1, @article)
                    RETURN (1)
                END

        END

    
    INSERT INTO @helpmergearticle 
        (name, 
        source_owner,
        source_object, 
        sync_object_owner,
        sync_object, 
        description, 
        status, 
        creation_script, 
        conflict_table, 
        pre_creation_command, 
        schema_option, 
        type, 
        column_tracking, 
        article_resolver, 
        subset_filterclause,
        resolver_info,
        vertical_partition,
        destination_owner,
        identity_support,
        pub_identity_range,
        identity_range,
        threshold,
		verify_resolver_signature,
        destination_object,
        allow_interactive_resolver,
		fast_multicol_updateproc,
		check_permissions)(
    select  art.name,
            users1.name,
            objects.name, 
            users2.name,
            syncobjects.name, 
            art.description, 
            art.status, 
            art.creation_script, 
            art.conflict_table, 
            art.pre_creation_command,
            art.schema_option, 
            case 
                when objectproperty(art.objid, 'IsSchemaBound') = 1 and art.type <> 0x80 then 0x0100 | convert(smallint, art.type) 
                else convert(smallint, ISNULL(art.type,0x0a)) 
                end,
            art.column_tracking, 
            art.article_resolver, 
            art.subset_filterclause,
            art.resolver_info,
            art.vertical_partition,
            art.destination_owner,
            art.identity_support,
            ABS(ir.pub_range),
            ABS(ir.range),
            ir.threshold,
			art.verify_resolver_signature,
            art.destination_object,
            art.allow_interactive_resolver,
			art.fast_multicol_updateproc,
			art.check_permissions
    FROM    sysmergeextendedarticlesview art left outer join MSrepl_identity_range ir on art.objid=ir.objid 
            inner join sysmergepublications pubs on art.pubid = pubs.pubid
            inner join sysobjects objects on objects.id = art.objid
            left outer join sysobjects syncobjects on art.sync_objid = syncobjects.id 
            inner join sysusers users1 on objects.uid = users1.uid
            left outer join sysusers users2 on syncobjects.uid = users2.uid
            WHERE art.name LIKE @article
                AND pubs.name LIKE @publication
                AND UPPER(pubs.publisher) = UPPER(@@servername)
                AND pubs.publisher_db = db_name())
            order by art.nickname desc

    select * from @helpmergearticle 
    RETURN (0)
go
exec dbo.sp_MS_marksystemobject sp_helpmergearticle 
go

grant execute on dbo.sp_helpmergearticle to public
go

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

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

    set nocount on
    /*
    ** Declarations.
    */

    declare @db_name                sysname
    declare @cmd                    nvarchar(255)
    declare @artid                  uniqueidentifier
    declare @snapshot_ready			int
    declare @objid                  int
    declare @pubid                  uniqueidentifier
    declare @pubidstr               nvarchar(38)
    declare @merge_pub_object_bit   int
    declare @unpublish_bit          int
    declare @retcode                int
    declare @replinfo               int
    declare @dbname                 sysname
    declare @distributor            sysname
    declare @distribdb              sysname
    declare @distproc               nvarchar(300)
    declare @object_name            sysname
    declare @uid                    smallint
    declare @owner                  sysname
    declare @qualified_name         nvarchar(270)
	declare @filterid				int
	declare @proc_name				sysname
    declare @implicit_transaction	int
	declare @close_cursor_at_commit int
	declare @sync_objid 	int
	declare @view_type		int
    declare @type           tinyint
	declare @allow_anonymous	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.
    */
    -- merge uses bit 8 in replinfo
    select @merge_pub_object_bit    = 128  
    select @unpublish_bit = ~@merge_pub_object_bit


    /*
    ** 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, @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
	/*
	** Once snapshot is ready, do not allow dropping an article
	*/
	if @snapshot_ready>0 and (@allow_anonymous=1 or 
			exists (select * from sysmergesubscriptions where pubid=@pubid and subid<>pubid and status=1))
		begin
			RAISERROR (21338, 16, -1, @article, @publication)
			RETURN (1)
		end


	if @snapshot_ready>0
		begin
			if @force_invalidate_snapshot = 0
				begin
					raiserror(21379, 16, -1, @article, @publication)
					return (1)
				end
			update sysmergepublications set snapshot_ready=2 where pubid=@pubid
			if @@ERROR<>0
				return (1)
		end
	
    
    set @pubidstr = '''' + convert(nchar(36), @pubid) + ''''

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

    if LOWER(@article) = 'all'
        BEGIN
            declare hC CURSOR LOCAL FAST_FORWARD FOR select DISTINCT name FROM sysmergeextendedarticlesview WHERE pubid=@pubid FOR READ ONLY
            
            OPEN hC
            FETCH hC INTO @article
            WHILE (@@fetch_status <> -1)
                BEGIN
                    EXECUTE dbo.sp_dropmergearticle @publication, @article,
                        @ignore_distributor = @ignore_distributor,
                        @reserved = 1
                    FETCH hC INTO @article
                END
            CLOSE hC
            DEALLOCATE hC
            RETURN (0)
        END

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

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

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

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

    /*
    ** Ascertain the existence of the article.
    */
    select @type = NULL
    select @type = type ,
		   @artid = artid
	  from sysmergeextendedarticlesview 
     where name = @article
       and pubid = @pubid

    if @type is NULL
        begin
            raiserror (20027, 16, -1, @article)
            return (1)
        end

	-- if all articles are to be dropped, ignore this checking.
	if @reserved=0 and exists (select * from sysmergesubsetfilters where pubid=@pubid and join_articlename=@article)
		begin
			raiserror(21421, 16, -1, @article)
			return (1)
		end

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

    begin tran 
    save TRAN dropmergearticle
        /*
        ** Remove the corresponding rows from sysmergeschemachange
        */
        DELETE FROM sysmergeschemachange WHERE artid = @artid AND pubid = @pubid
        if @@ERROR <> 0
            goto FAILURE
        /*
        ** Removing a schema only article is a lot simpler than 
        ** removing a table article so a different code path is created
        ** to handle this.  
        */
        if @type in (0x20, 0x40, 0x80)
            begin
            /*
            ** Retrieve the object id of the underlying proc or view object.
            */
            select @objid = objid
        	    from sysmergeschemaarticles where name = @article and pubid = @pubid
                
            /*
            ** Remove the corresponding record in sysmergeschemaarticles
            */
            delete sysmergeschemaarticles where name = @article and pubid = @pubid

            /*
            ** If this is the last schema only article for the underlying 
            ** view or proc object, unmark the 0x200 bit in sysobject.replinfo  
            */
            if not exists (select * from sysmergeschemaarticles 
                            where objid = @objid)
                begin
                select @merge_pub_object_bit = 512
                select @unpublish_bit = ~(@merge_pub_object_bit)
                if not exists (select * from sysobjects 
                                where name = 'sysschemaarticles')
                    begin   
                    update sysobjects set replinfo = (@unpublish_bit & replinfo) where id = @objid
                    end    
                else if  not exists (select * from sysschemaarticles 
                                      where objid = @objid) 
                    begin   
                    update sysobjects set replinfo = (@unpublish_bit & replinfo) where id = @objid
                    end    
                end
            end
        else 
            begin
            /*
            ** Retrieve the object id of the underlying table.
            */
            select @sync_objid = sync_objid, @view_type = view_type, @artid = artid, @objid = objid
        	    from sysmergearticles where name = @article AND pubid = @pubid
            select @replinfo = replinfo, @object_name=name, @owner= user_name(uid) from sysobjects where id = @objid
        
            /*
            ** If this is the last article that refers to the base table, drop the 
            ** triggers and stored procs 
            */
            if NOT exists (select * from sysmergearticles WHERE artid = @artid AND pubid <> @pubid)
                begin
                /*
                ** Cleanup the triggers and stored procs
                */
                EXECUTE @retcode = dbo.sp_MSarticlecleanup @artid = @artid, @pubid = @pubid
                if @@ERROR <> 0 OR @retcode <> 0
                    BEGIN
                        GOTO FAILURE
                    END
            
                /*
                ** Clear the replication bit in sysobjects. Now merge and transactional level
                ** uses different replication bit, checking transactional level is not needed.
                */

                select @qualified_name = (QUOTENAME(@owner) + '.' + QUOTENAME(@object_name))
                exec @retcode = dbo.sp_replupdateschema @qualified_name
                if @@ERROR <> 0 OR @retcode <> 0
                    BEGIN
                        GOTO FAILURE
                    END
            
                update sysobjects set replinfo = (replinfo & @unpublish_bit) where id = @objid

                IF @@ERROR <> 0
                    goto FAILURE
                end             
		    else
			    begin
			    /* Always drop the article proc's they are not shared among publications */
	            EXECUTE @retcode = dbo.sp_MSdroparticleprocs @artid = @artid, @pubid = @pubid
                if @@ERROR <> 0 OR @retcode <> 0
                    begin
                        goto FAILURE
                    end
			    /* If the article's has a temporary ( view type = 2) or a permanent view (view_type = 1 ) drop the sync object */
			    if (@view_type = 1 OR @view_type = 2)
				    begin
				    declare @viewname sysname
				    select @viewname = sysobjects.name from sysobjects where 
					    ObjectProperty (sysobjects.id, 'IsView') = 1 
					    and ObjectProperty (sysobjects.id, 'IsMSShipped') = 1 
					    and sysobjects.id = @sync_objid
				    if @viewname IS NOT NULL
					    begin
						    set @viewname = QUOTENAME(@viewname)
						    exec ('drop view ' + @viewname)
						    if @@ERROR<>0  GOTO FAILURE
					    end
				    end
			    end
            /*
            ** Remove the row from sysmergearticles.
            */
            DELETE FROM sysmergearticles WHERE artid = @artid AND pubid = @pubid
            if @@ERROR <> 0
                BEGIN
                    GOTO FAILURE
                END


            /* delete all the filter components that are defined upon the designated article */
		    select @filterid = min(join_filterid) from sysmergesubsetfilters where
			    artid = @artid AND pubid = @pubid
		    while (@filterid is not null)
			    begin
			    select @proc_name = expand_proc from sysmergesubsetfilters where
				    artid = @artid AND pubid = @pubid and join_filterid = @filterid

			    if (@proc_name IS NOT NULL) and exists (select * from sysobjects where
				    name = @proc_name and type = 'P')
				    begin
					    exec ('drop proc ' + @proc_name)
			            IF @@ERROR <> 0
            			goto FAILURE
				    end
			    delete from sysmergesubsetfilters where
				    artid = @artid AND pubid = @pubid and join_filterid = @filterid
        	    IF @@ERROR <> 0
          	 	    goto FAILURE
			    select @filterid = min(join_filterid) from sysmergesubsetfilters where
				    artid = @artid AND pubid = @pubid
			    end


            /*
            ** set the pub type to subset or full as appropriate
            */
            execute @retcode = dbo.sp_MSsubsetpublication @publication
            if @@ERROR <> 0 or @retcode <> 0 
                RETURN (1)
                
            /*
            ** 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 @dbname =  DB_NAME()
        
                SELECT @distproc = RTRIM(@distributor) + '.' + @distribdb + 
                    '.dbo.sp_MSdrop_article'
                EXECUTE @retcode = @distproc
                    @publisher = @@SERVERNAME,
                    @publisher_db = @dbname,
                    @publication = @publication,
                    @article = @article
                      
                IF @@ERROR <> 0 or @retcode <> 0
                    BEGIN
                        goto FAILURE
                    END
                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:
    RAISERROR (14047, 16, -1, @article)
    if @@TRANCOUNT > 0
    begin
        ROLLBACK TRANSACTION dropmergearticle
        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_dropmergearticle
go

grant execute on dbo.sp_dropmergearticle to public
go


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

create procedure sp_addmergepublication (
    @publication            sysname,                /* Publication name */
    @description            nvarchar(255)= NULL,            /* Publication description */
    @retention              int         = 14,           /* Retention period of 14 days */
    @sync_mode              nvarchar(10) = 'native',    /* (bcp)native, (bcp)character */
    @allow_push             nvarchar(5)  = 'true',      /* Pulication allows push subscriptions */
    @allow_pull             nvarchar(5)  = 'true',      /* Pulication allows pull subscriptions*/
    @allow_anonymous        nvarchar(5)  = 'false',     /* Pulication allows anonymous subscriptions */
    @enabled_for_internet   nvarchar(5)     = 'false',  /* Pulication is enabled for internet */
    @centralized_conflicts  nvarchar(5)  = 'true',      /* Conflict records stored at publisher : true or false */
    @dynamic_filters        nvarchar(5) = 'false',      /* Will publication be filtered on dynamic clause? */
    @snapshot_in_defaultfolder      nvarchar(5) = 'true',       /* Will keep a copy of the snapshot files to the default location if an alternate folder is specified */
    @alt_snapshot_folder    nvarchar(255) = NULL,       /* Alternate folder for putting the snapshot file for this publication */
    @pre_snapshot_script    nvarchar(255) = NULL,		/* Pre snapshot commands */
    @post_snapshot_script   nvarchar(255) = NULL,    	/* Post snapshot commands */
    @compress_snapshot      nvarchar(5) = 'false',		/* Snapshot compression */
    @ftp_address            sysname = NULL,				/* Post 7.0 FTP Properties */
    @ftp_port               int = 21,					/* Post 7.0 FTP Properties */
    @ftp_subdirectory       nvarchar(255) = NULL,		/* Post 7.0 FTP Properties */
    @ftp_login              sysname = N'anonymous',		/* Post 7.0 FTP Properties */
    @ftp_password           sysname = NULL,				/* Post 7.0 FTP Properties */
    @conflict_retention		int = 14,					/* Conflict retention period */
    @keep_partition_changes nvarchar(5) = 'false',		/* Optimized Partition Updates/Deletes */
    @allow_subscription_copy	nvarchar(5) = 'false',	/* Allow the subscription to be copied */
    @allow_synctoalternate		nvarchar(5) = 'false',	/* Allow the subscription to be synchronize to alternate partners */
    @validate_subscriber_info	nvarchar(500) = NULL,	/* Should we validate that subscriber is using right params? */
    @add_to_active_directory	nvarchar(5) = 'false',
	@max_concurrent_merge	int = 0, 					/* value of 0 means no such limit exists */
    @max_concurrent_dynamic_snapshots int = 0           /* Maximum number of concurrent dynamic snapshot sessions */
    ) as

    set nocount on

    /*
    ** Declarations.
    */
    
    declare @retcode                    int         /* return code value for procedure execution */
    declare @push                       tinyint     /* subscription type is push */
    declare @statid                     tinyint     /* status id based on @status */
    declare @sync_modeid                tinyint     /* sync mode id based on @sync_mode */
    declare @global                     tinyint     /* subscriber type of loop-back subscription */
    declare @db_name                    sysname     /* database name */
    declare @srvid                      int         /* Server ID */
    declare @nickname                   int         /* replica nickname */
    declare @tranpublish_bit            smallint    /* online publish bit (flag) in sysdatabases */
    declare @mergepublish_bit           smallint    /* merge publish bit (flag) in sysdatabases */
    declare @found                      int         /* flag indicating if publication is found */
    declare @pubid                      uniqueidentifier    /* Publication identifier */
    declare @allow_push_id              bit
    declare @allow_pull_id              bit
    declare @allow_anonymous_id         bit
    declare @dynamic_filters_id         bit         
    declare @allow_subscription_copy_id bit         
    declare @allow_synctoalternate_id 	bit         
    declare @enabled_for_internet_id    bit
    declare @centralized_conflicts_id   bit
    declare @priority                   real        
    declare @automatic                  tinyint     
    declare @false                      bit
    declare @true                       bit
    declare @distributor                sysname
    declare @distproc                   nvarchar(300)
    declare @distribdb                  sysname
    declare @distpubid                  int
    declare @full                       int
    declare @snapshot_in_defaultfolder_bit      bit
    declare @compress_snapshot_bit      bit
    declare @keep_before_values_int	   	int
    declare @enc_ftp_password           nvarchar(524)
    declare @ad_guidname				sysname
    declare @schemaversion      		int 
    declare @schemaguid         		uniqueidentifier
    declare @schematype         		int
    declare @schematext         		nvarchar(2000)
	declare @artid						uniqueidentifier
	declare @distservername				sysname
    declare @backward_comp_level		int
    
	/* make sure current database is enabled for merge replication */
    exec @retcode=dbo.sp_MSCheckmergereplication
    if @@ERROR<>0 or @retcode<>0
    	return (1)

    /*
    ** Initializations
    */
    select @backward_comp_level	= 10 --that of 7.0 RTM
    
    select @mergepublish_bit    = 4 
    select @tranpublish_bit     = 1
    select @priority            = 100.0
    select @automatic           = 1     /* Const: synchronization type 'automatic' */
    select @true                = 1
    select @false               = 0
    select @full                = 0     /* Const: publication type 'full' */

    /*
    ** Set the status to Active (1)
    */
    select @statid      = 1
    select @global      = 1
    select @push        = 0
    select @db_name     = DB_NAME()
    select @ad_guidname = NULL

    /*
    ** Security Check
    */

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

    /*
    ** Parameter Check: @publication.
    ** The @publication name must conform to the rules for identifiers,
    ** and must not be the keyword 'all'.
    */
    if @publication is NULL
        begin
            raiserror (14043, 16, -1, '@publication')
            return (1)
        end

	exec @retcode = dbo.sp_MSreplcheck_name @publication
    if @@ERROR <> 0 or @retcode <> 0
        return(1)
       
    if LOWER (@publication) = 'all'
        begin
            raiserror (14034, 16, -1)
            return (1)
        end
        
	if @max_concurrent_merge<0
		begin
			raiserror(21402, 16, -1, '@max_concurrent_merge')
			return (1)
		end

    /*
    ** Parameter Check: @retention.
    */
        
    if @retention is not NULL and @retention<0 
        begin
            raiserror(20050, 16, -1, 0)
            return(1)
        end

    if @retention is NULL
    	select @retention = 0

    /*
    ** Parameter Check: @conflict_retention.
    */
        
    if @conflict_retention is not NULL and @conflict_retention<0 
        begin
            raiserror(20050, 16, -1, 0)
            return(1)
        end

	/*
	** if it is NULL, use the default value of 14 days.
	*/
	if @conflict_retention is NULL 
		select @conflict_retention = 14

    /*
    ** Parameter Check: @sync_mode.
    ** Make sure that the sync_mode is one of the following:
    **
    **  id  sync_mode
    **  ==  ==========
    **   0  (bcp)native
    **   1  (bcp)character
    */
    
    if LOWER(@sync_mode)='portable' select @sync_mode='character'

    if LOWER(@sync_mode) is NULL OR LOWER(@sync_mode) NOT IN ('bcp native', 'bcp character', 'native', 'character')
        begin
            raiserror (20076, 16, -1)
            return (1)
        end

    if LOWER(@sync_mode) = 'native' or LOWER(@sync_mode)='bcp native' 
        select @sync_modeid = 0
    else 
        select @sync_modeid = 1

    /*
    ** Parameter Check:  @allow_push.
    */

    if @allow_push IS NULL OR LOWER(@allow_push) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@allow_push')
            RETURN (1)
        END

    if LOWER(@allow_push) = 'true' 
        select @allow_push_id = 1
    else 
        select @allow_push_id = 0

    /*
    ** Parameter Check:  @allow_pull.
    */

    if @allow_pull IS NULL OR LOWER(@allow_pull) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@allow_pull')
            RETURN (1)
        END
    if LOWER(@allow_pull) = 'true' 
        select @allow_pull_id = 1
    else 
        select @allow_pull_id = 0

    /*
    ** Parameter Check:  @allow_anonymous.
    */

    if @allow_anonymous IS NULL OR LOWER(@allow_anonymous) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@allow_anonymous')
            RETURN (1)
        END
    if LOWER(@allow_anonymous) = 'true' 
        select @allow_anonymous_id = 1
    else 
        select @allow_anonymous_id = 0

    /*
    ** Parameter Check:  @enabled_for_internet.
    */

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

    IF LOWER(@enabled_for_internet) = 'true' 
        SELECT @enabled_for_internet_id = 1
    ELSE 
        SELECT @enabled_for_internet_id = 0

    /*
    ** Parameter Check:  @centralized_conflicts.
    */

    if @centralized_conflicts IS NULL OR LOWER(@centralized_conflicts) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@centralized_conflicts')
            RETURN (1)
        END
    if LOWER(@centralized_conflicts) = 'true' 
        select @centralized_conflicts_id = 1
    else 
    	begin
    		raiserror(21349, 10, -1, @publication)
    		select @backward_comp_level = 30	-- that of Sphinx SP2, in which decentrailzed logging will be supported.
        	select @centralized_conflicts_id = 0
        end

    /*
    ** Parameter Check:  @dynamic_filter.
    */

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

    IF LOWER(@dynamic_filters) = 'true' 
        SELECT @dynamic_filters_id = 1
    ELSE 
        SELECT @dynamic_filters_id = 0

	if @validate_subscriber_info is not NULL
		begin
			if @dynamic_filters_id = 0
				begin
					raiserror(21313, 16, -1)
					return (1)
				end
			exec ('select ' + @validate_subscriber_info)
			if @@ERROR<>0
				begin
					raiserror(21299, 16, -1, @validate_subscriber_info)
					return (1)
				end
		end

    -- Portable snapshot
    IF @snapshot_in_defaultfolder IS NULL OR LOWER(@snapshot_in_defaultfolder) NOT IN ('true', 'false')
    BEGIN
        RAISERROR (14148, 16, -1, '@snapshot_in_defaultfolder')
        RETURN (1)
    END
    
    IF LOWER(@snapshot_in_defaultfolder) = 'true'
    BEGIN
        SELECT @snapshot_in_defaultfolder_bit = 1
    END
    ELSE
    BEGIN
        SELECT @snapshot_in_defaultfolder_bit = 0
    END

    -- Pre/Post snapshot commands
    -- If @sync_method is character mode bcp, this would indicate that
    -- this publication may support non-SQL Server subscribers. In this 
    -- case, pre- and post- snapshot commands are not allowed.
    IF @sync_modeid = 1 AND 
        ((@pre_snapshot_script IS NOT NULL AND @pre_snapshot_script <> N'' ) OR
         (@post_snapshot_script IS NOT NULL AND @post_snapshot_script <> N''))
    BEGIN
        RAISERROR (21151, 16, -1)
        RETURN (1)
    END

    -- Parameter check - @compress_snapshot
    -- @compress_snapshot can be 1 if @alt_snapshot_folder is non-null
    IF @compress_snapshot IS NULL OR LOWER(@compress_snapshot) NOT IN ('true', 'false')
    BEGIN
        RAISERROR (14148, 16, -1, '@compress_snapshot')
        RETURN (1)
    END
    
    IF LOWER(@compress_snapshot) = 'true'
    BEGIN
        SELECT @compress_snapshot_bit = 1
    END
    ELSE
    BEGIN
        SELECT @compress_snapshot_bit = 0
    END


    -- Only bump up the compatibility level if only a compressed snapshot
    -- is generated at the alternate snapshot folder
    if @snapshot_in_defaultfolder_bit = 0 and
       @compress_snapshot_bit = 1
    begin
    	raiserror(21350, 10, -1, @publication)
    	select @backward_comp_level = 40 -- this is supported starting from 7.5
    end

    -- Snapshot compression can only be enabled if an alternate 
    -- snapshot generation folder exists.
    IF (@compress_snapshot_bit = 1 AND 
        (@alt_snapshot_folder IS NULL OR @alt_snapshot_folder = N''))
    BEGIN
        RAISERROR (21157, 16, -1)
        RETURN (1)
    END    

    -- Parameter check: ftp_address
    -- If the publication is enabled for internet, ftp_address cannot be null
    IF @enabled_for_internet_id = 1 AND (@ftp_address IS NULL OR @ftp_address = N'')
    BEGIN
        RAISERROR (21158, 16, -1)
        RETURN (1)
    END     


    -- Parameter check: enabled_for_internet
    -- If a publication is enabled for internet, it must have an alternate 
    -- snapshot folder defined.
    IF LOWER(@enabled_for_internet) = N'true' AND 
        (@alt_snapshot_folder = N'' OR
        (@alt_snapshot_folder IS NULL))
    BEGIN
        RAISERROR (21159, 16, -1)
        RETURN (1)
    END 

    -- Parameter check: ftp_port
    IF @ftp_port IS NULL
    BEGIN
        RAISERROR (21160, 16, -1)
    END


    -- Encrypt ftp password before putting it into the sysmergepublications
    -- table if one is provided
    SELECT @enc_ftp_password = NULL
    IF @ftp_password IS NOT NULL
    BEGIN
        SELECT @enc_ftp_password = @ftp_password 
        EXEC @retcode = master.dbo.xp_repl_encrypt @enc_ftp_password OUTPUT
        IF @retcode <> 0
        BEGIN
            RETURN (1)
        END
    END    

    /*
    ** Parameter Check:  @keep_partition_changes.
    */

    if LOWER(@keep_partition_changes) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@keep_partition_changes')
            RETURN (1)
        END

    if LOWER(@keep_partition_changes) = 'true' 
    	begin
        	set @keep_before_values_int = 1
        end
    else 
        set @keep_before_values_int = 0

    /*
    ** Parameter Check:  @allow_subscription_copy_id.
    */

    if LOWER(@allow_subscription_copy) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@allow_subscription_copy')
            RETURN (1)
        END
        
    IF LOWER(@allow_subscription_copy) = 'true' 
    	SELECT @allow_subscription_copy_id = 1
    ELSE 
    	SELECT @allow_subscription_copy_id = 0

    /*
    ** Parameter Check:  @allow_synctoalternate_id.
    */

    if LOWER(@allow_synctoalternate) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@allow_synctoalternate')
            RETURN (1)
        END
        
    IF LOWER(@allow_synctoalternate) = 'true' 
    	SELECT @allow_synctoalternate_id = 1
    ELSE 
    	SELECT @allow_synctoalternate_id = 0

    /*
    ** Parameter Check:  @add_to_active_directory.
    */

    if LOWER(@add_to_active_directory) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@add_to_active_directory')
            RETURN (1)
        END

   /* Is AD supported? */
   DECLARE @retval  INT
   EXECUTE @retval = master.dbo.xp_MSADEnabled
   if (@retval <> 0) and LOWER(@add_to_active_directory)='true'
   begin
		RAISERROR(21253, 16, -1)
		RETURN (1)
   end


    /*
    ** Parameter Check: @max_concurrent_dynamic_snapshots
    */
    
    if @max_concurrent_dynamic_snapshots < 0 or @max_concurrent_dynamic_snapshots is null
    begin
        raiserror(21403, 16, -1)
        return (1)
    end
		
    /*
    ** Check to see if the publication name is already used.
    ** 1. check merge pubs
    ** 2. check online publications
    */
    if exists (select * from sysmergepublications 
        where name = @publication  and UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name())
    begin
        RAISERROR (20025, 16, -1, @publication)
        RETURN (1)
    end

    if (select category & @tranpublish_bit from master..sysdatabases where name = @db_name collate database_default) <> 0
    begin
        EXEC @retcode = dbo.sp_helppublication @publication, @found output

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

        if @found <> 0 
        BEGIN
            RAISERROR (20025, 16, -1, @publication)
            RETURN (1)
        END
    end


    /*
    **  Add the publication as the designmaster of the replica set.
    */

    /* Generate a guid for the publication ID */
    set @pubid = newid()
        
    /* Select the server's ID as 0 since this is the LOCAL server */
    select @srvid = 0

    /* Look for existing nickname from any other subscription */
    exec @retcode=sp_MSgetreplnick NULL, NULL , NULL,  @nickname out
    if (@@error <> 0) or @retcode <> 0 
        begin
	        RETURN(1)
        end                 
            
    /* Generate a new replica nickname from the @pubid */
    if (@nickname is null)
    begin
        execute @retcode = dbo.sp_MSgenreplnickname @pubid, @nickname output
        IF @@ERROR <>0 OR @retcode <> 0
        BEGIN
            RAISERROR (20077, 16, -1)
            RETURN (1)
        END
    end
    else
        select @priority=max(priority) from sysmergesubscriptions where db_name=@db_name and srvid = @srvid
    /*
    ** A change in design.
    */
    if @priority = 0 
        begin
            RAISERROR(21087, 16, -1)
            return (1)
        end

    /*
    ** Get distributor information
    */
    EXEC @retcode = dbo.sp_helpdistributor @rpcsrvname = @distributor OUT , @distributor = @distservername  OUTPUT, 
        @distribdb = @distribdb OUTPUT
        if @@error <> 0 OR @retcode <> 0 or @distributor IS NULL OR @distribdb IS NULL
        BEGIN
            RAISERROR (14071, 16, -1)
            RETURN (1)
        END
    
    /*
    **  add an entry into sysmergepublications
    */
    begin tran    
    save tran sp_addmergepublication
        /* Add row in the publications table */
		if @backward_comp_level > 10
		begin
			exec sp_MSBumpupCompLevel @pubid, @backward_comp_level
			if @@ERROR<>0
				goto FAILURE
        end
        	
        insert sysmergepublications
            (pubid, 
            name, 
            description, 
            designmasterid, 
            retention, 
            parentid, 
            sync_mode, 
            allow_push, 
            allow_pull, 
            allow_anonymous, 
            centralized_conflicts,
            status,
            snapshot_ready,
            enabled_for_internet,
            publication_type,
            dynamic_filters,
            snapshot_in_defaultfolder,
            alt_snapshot_folder,
            pre_snapshot_script,
            post_snapshot_script,
            compress_snapshot,
            ftp_address,
            ftp_port,
            ftp_subdirectory,
            ftp_login,
            ftp_password,
            conflict_retention,
            keep_before_values,
            allow_subscription_copy,
            allow_synctoalternate,
            validate_subscriber_info,
            ad_guidname,
            backward_comp_level,
            max_concurrent_merge,
            max_concurrent_dynamic_snapshots)
        values
            (@pubid, 
            @publication, 
            @description, 
            @pubid, 
            @retention, 
            @pubid, 
            @sync_modeid, 
            @allow_push_id, 
            @allow_pull_id, 
            @allow_anonymous_id, 
            @centralized_conflicts_id,
            @statid,
            @false,
            @enabled_for_internet_id,
            @full,
            @dynamic_filters_id,
            @snapshot_in_defaultfolder_bit,
            @alt_snapshot_folder,
            @pre_snapshot_script,
            @post_snapshot_script,
            @compress_snapshot_bit,
            @ftp_address,
            @ftp_port,
            @ftp_subdirectory,
            @ftp_login,
            @enc_ftp_password,
            @conflict_retention,
            @keep_before_values_int,
            @allow_subscription_copy_id,
            @allow_synctoalternate_id,
            @validate_subscriber_info,
            @ad_guidname, 
            @backward_comp_level,
            @max_concurrent_merge,
            @max_concurrent_dynamic_snapshots)
        if @@ERROR <> 0
            begin
                goto FAILURE
            end

        /* Add row to represent reciprocal subscription */
        insert sysmergesubscriptions(subid, partnerid, datasource_type, srvid, db_name, status, priority, pubid, subscriber_type, subscription_type, sync_type, login_name, subscriber_server, publication, distributor, last_validated)
            values (@pubid, @pubid, 0, @srvid, @db_name, @statid, @priority, @pubid, @global, @push, @automatic, suser_sname(suser_sid()), @@servername, @publication, @distservername, getdate())

        if @@ERROR <> 0
            begin
                goto FAILURE
            end
        /*
        **  Add row for merge publication to MSmerge_replinfo.
        */
        insert MSmerge_replinfo(repid, replnickname)
            values (@pubid, @nickname)
        if @@ERROR <> 0
            begin
                goto FAILURE
            end


        /*
        ** Add the publication to the distributor side
        */
        SELECT @distpubid = @nickname

        select @distproc = RTRIM(@distributor) + '.' + @distribdb + 
            '.dbo.sp_MSadd_publication'
        EXECUTE @retcode = @distproc
            @publisher = @@SERVERNAME,
            @publisher_db = @db_name,
            @publication = @publication,
            --@publication_id = NULL,
            @publication_type = 2,          -- 0 = Trans, 1 = Snapshot, 2 = Merge
            @independent_agent = @true,
            @immediate_sync = @true,
            @allow_push = @allow_push_id,
            @allow_pull = @allow_pull_id,
            @allow_anonymous = @allow_anonymous_id,
            --@snapshot_agent = NULL,
            --@logreader_agent = NULL,
            @description = @description,
            @retention = @retention,
			@allow_subscription_copy = @allow_subscription_copy_id

        IF @@ERROR <> 0 or @retcode <> 0
            BEGIN
                GOTO FAILURE
            END
        		
		-- Populate the initial list.
		exec @retcode = dbo.sp_grant_publication_access 
			@publication = @publication,
			@login = null,
			@reserved = 'init'
		IF @@error <> 0 OR @retcode <> 0
			GOTO FAILURE



    commit tran

    --Put this part outside of a TRANSACTION. It can fail without having to affect publication creation.
	declare @returnstring nvarchar(512) 
	set @returnstring = N''
	if LOWER(@add_to_active_directory)='true'
    begin
    	--no error checking needed here.    

		create table #guid_name_for_active_directory(ad_guidname sysname collate database_default null)
		if @@ERROR<>0
			goto SKIP_AD
		insert into #guid_name_for_active_directory exec @retcode=master.dbo.sp_ActiveDirectory_Obj 'CREATE', 'PUBLICATION', @publication, @db_name
		if @retcode <> 0 or @@ERROR<>0
    	begin
			set @returnstring = (select TOP 1 ad_guidname from #guid_name_for_active_directory)
			goto SKIP_AD	   	
		end
    	select TOP 1 @ad_guidname = ad_guidname from #guid_name_for_active_directory
    	if @ad_guidname is not NULL
    	begin
    		update sysmergepublications set ad_guidname=@ad_guidname where pubid=@pubid
    		if @@ERROR<>0
    			goto SKIP_AD
    	end
	    drop table #guid_name_for_active_directory
    end
    return (0)
SKIP_AD:
	drop table #guid_name_for_active_directory
	if @returnstring is NULL
		select @returnstring = N''
	raiserror(21363, 16, -1, @publication, @returnstring)
	return (1) 
FAILURE:
    RAISERROR (14018, 16, -1)
    /* UNDONE : This code is specific to 6.X nested transaction semantics */
    if @@TRANCOUNT > 0
    begin
        ROLLBACK TRANSACTION sp_addmergepublication
        COMMIT TRANSACTION
    end
    return (1)
go
exec dbo.sp_MS_marksystemobject sp_addmergepublication 
go

grant execute on dbo.sp_addmergepublication to public
go

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

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

    SET NOCOUNT ON

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

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

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

    /* 
    ** Check if current DB is enabled for publication/subscription
    */
    
	/* make sure current database is enabled for merge replication */
    exec @retcode=dbo.sp_MSCheckmergereplication
    if @@ERROR<>0 or @retcode<>0
    	return (1)
            
    /*
    ** Parameter Check:  @property.
    ** If the @property parameter is NULL, print the options.
    */
    if @property IS NULL
        BEGIN
            CREATE TABLE #tab1 (properties sysname collate database_default)
            INSERT INTO #tab1 VALUES ('description')
            INSERT INTO #tab1 VALUES ('status')
            INSERT INTO #tab1 VALUES ('retention')
            INSERT INTO #tab1 VALUES ('sync_mode')
            INSERT INTO #tab1 VALUES ('allow_push')
            INSERT INTO #tab1 VALUES ('allow_pull')
            INSERT INTO #tab1 VALUES ('allow_anonymous')
            INSERT INTO #tab1 VALUES ('enabled_for_internet')
            INSERT INTO #tab1 VALUES ('centralized_conflicts')
            INSERT INTO #tab1 VALUES ('snapshot_ready')
            INSERT INTO #tab1 VALUES ('snapshot_in_defaultfolder')
            INSERT INTO #tab1 VALUES ('alt_snapshot_folder')
            INSERT INTO #tab1 VALUES ('pre_snapshot_script')
            INSERT INTO #tab1 VALUES ('post_snapshot_script')
            INSERT INTO #tab1 VALUES ('compress_snapshot')
            INSERT INTO #tab1 VALUES ('ftp_address')
            INSERT INTO #tab1 VALUES ('ftp_port')
            INSERT INTO #tab1 VALUES ('ftp_subdirectory')
            INSERT INTO #tab1 VALUES ('ftp_login')
            INSERT INTO #tab1 VALUES ('ftp_password')
            INSERT INTO #tab1 VALUES ('conflict_retention')
            INSERT INTO #tab1 VALUES ('allow_subscription_copy')
            INSERT INTO #tab1 VALUES ('allow_synctoalternate')
            INSERT INTO #tab1 VALUES ('validate_subscriber_info')
            INSERT INTO #tab1 VALUES ('publish_to_ActiveDirectory')
            INSERT INTO #tab1 VALUES ('dynamic_filters')
            INSERT INTO #tab1 VALUES ('max_concurrent_merge')
            INSERT INTO #tab1 VALUES ('max_concurrent_dynamic_snapshots')
            select * FROM #tab1
            RETURN (0)
        END

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

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

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

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

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

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


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

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

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

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

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

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

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

    BEGIN TRAN changemergepublication    
    save tran changemergepublication

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

	if LOWER(@property)='dynamic_filters' and @snapshot_ready>0
	begin
		if @force_invalidate_snapshot = 0
		begin
			raiserror(20607, 16, -1)
			goto UNDO
		end
		if @force_reinit_subscription = 0
		begin
			raiserror(20608, 16, -1)
			goto UNDO
		end
		if LOWER(@value) = 'true' 
        	select @dynamic_filters_id = 1
	    else if LOWER(@value) = 'false' 
       		select @dynamic_filters_id = 0
       	else
       		begin
       			raiserror(14137, 16, -1)
       			goto UNDO
       		end
		update sysmergepublications set dynamic_filters=@dynamic_filters_id, snapshot_ready=2 where pubid=@pubid
		if @@ERROR<>0
			goto UNDO
		exec @retcode = sp_MSreinitmergepublication @publication
		if @retcode<>0 or @@ERROR<>0
			goto UNDO
	end
	
	if LOWER(@property)='validate_subscriber_info'
	begin
		if @value is not NULL
		begin
			if @dynamic_filters_id = 0
				begin
					raiserror(21313, 16, -1)
					GOTO UNDO
				end
			exec ('select ' + @value)
			if @@ERROR<>0
				begin
					raiserror(21299, 16, -1, @value)
					GOTO UNDO
				end
		end

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

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

  
    if LOWER(@property) = 'status'
        BEGIN

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

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

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

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

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

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

            if @@ERROR <> 0 GOTO UNDO
        END


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

        select @retention = CONVERT(int, @value)

        if @retention is NULL 
        	select @retention = 0

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

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

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


        END

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

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

        if @value is NULL 
        	select @retention = 0

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

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

        if @@ERROR <> 0 
            GOTO UNDO

        END

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

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

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

        if @@ERROR <> 0 
            GOTO UNDO

        END


    if LOWER(@property) = 'sync_mode'
        BEGIN

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

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

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

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

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

            if @@ERROR <> 0 GOTO UNDO
        END

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

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

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

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

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

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

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

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

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

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

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

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

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

    if LOWER(@property) IN ('allow_push', 'allow_pull', 'allow_anonymous', 'enabled_for_internet',
        'centralized_conflicts', 'snapshot_ready', 'snapshot_in_defaultfolder', 'compress_snapshot', 
        'allow_subscription_copy', 'allow_synctoalternate')
    BEGIN

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

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

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


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

        END

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

        if LOWER(@property) = 'allow_pull'
        BEGIN

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

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

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

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

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

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

        END

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

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

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

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

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

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

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

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

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

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

    COMMIT TRAN

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

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

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

grant execute on dbo.sp_changemergepublication to public
go

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

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

    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    declare @retcode            int
    declare @no_row             bit
    declare @our_srvid          int
    declare @has_subscription   bit
    /*
    ** Initializations.
    */
    select @has_subscription = 0

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

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


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

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

    IF LOWER(@reserved) = 'internal'
        GOTO SelectPubs
    
    if  NOT EXISTS (select * FROM sysmergepublications pub, sysmergesubscriptions sub
        WHERE pub.name like @publication  
            and UPPER(pub.publisher)=UPPER(@@servername) 
            and pub.publisher_db=db_name() 
            and sub.pubid = pub.pubid  
            and sub.srvid = @our_srvid 
            and sub.db_name = db_name())
        BEGIN
            select @found = 0
            RETURN (0) 
        END
    else
        BEGIN
            select @found = 1
            select @publication_id = pubid FROM sysmergepublications 
                WHERE name = @publication and UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name()
            if exists (select * from sysmergesubscriptions where pubid<>subid and pubid in 
                (select pubid from sysmergepublications where name like @publication and UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name()))
                select @has_subscription = 1
            if @no_row <> 0
                RETURN(0)
        END

SelectPubs:

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

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

	update #tab1 set snapshot_ready=0 where snapshot_ready>1
	if @@ERROR<>0
		RETURN (1)
    select * FROM #tab1
    RETURN (0)
go
exec dbo.sp_MS_marksystemobject sp_helpmergepublication 
go

grant execute on dbo.sp_helpmergepublication to public
go


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

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

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

	select @close_cursor_at_commit = 0
	select @implicit_transaction = 0
	/*
	** Save setting values first before changing them
	*/
	IF (@reserved = 0)
	BEGIN
		SELECT @implicit_transaction = @@options & 2
		SELECT @close_cursor_at_commit = @@options & 4
		SET IMPLICIT_TRANSACTIONS OFF
		SET CURSOR_CLOSE_ON_COMMIT OFF
	END

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

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

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

    if LOWER(@publication) = 'all'
        BEGIN

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

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

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

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

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


    begin tran
    save TRANSACTION dropmergepublication

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    COMMIT TRANSACTION
    /*
	** Set back original settings
	*/	
	IF @reserved = 0
	BEGIN
		IF @implicit_transaction <>0 
			SET IMPLICIT_TRANSACTIONS ON
		IF @close_cursor_at_commit <>0 
			SET CURSOR_CLOSE_ON_COMMIT ON
	END
    return (0)

FAILURE:            
    RAISERROR (14006, 16, -1)
    /* UNDONE : This code is specific to 6.X nested transaction semantics */
    if @@TRANCOUNT > 0
    begin
        ROLLBACK TRANSACTION dropmergepublication
        COMMIT TRANSACTION
    end
    /*
	** Set back original settings
	*/	
	IF @reserved = 0
	BEGIN
		IF @implicit_transaction <>0 
			SET IMPLICIT_TRANSACTIONS ON
		IF @close_cursor_at_commit <>0 
			SET CURSOR_CLOSE_ON_COMMIT ON
	END        
    RETURN (1)
go
exec dbo.sp_MS_marksystemobject sp_dropmergepublication
go

grant execute on dbo.sp_dropmergepublication to public
go

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    begin tran
    save TRANSACTION articlecolumn

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    return (1)
go
 
EXEC dbo.sp_MS_marksystemobject sp_mergearticlecolumn
GO

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

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

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

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

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

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

    /*
    ** Parameter Check: @publication.
    ** The @publication name must conform to the rules for identifiers.
    */
    IF @publication IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@publication')
            RETURN (1)
        END
    EXECUTE @retcode = dbo.sp_validname @publication
    IF @retcode <> 0 or @@ERROR<>0
    RETURN (1)
    
    SELECT @pubid = pubid FROM sysmergepublications WHERE name = @publication 
    													  and LOWER(publisher)=LOWER(@publisher)
    													  and publisher_db = @publisher_db
    IF @pubid IS NULL
        BEGIN
            RAISERROR (20026, 11, -1, @publication)
            RETURN (1)
        END

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

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

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

