        drop table #proctext_scriptpublicationcustomprocs
    end
    
    if @cursor_opened <> 0
    begin
        close harticle
    end

    if @cursor_allocated <> 0
    begin
        deallocate harticle
    end 
    return @retcode    
end
go

exec sp_MS_marksystemobject sp_scriptpublicationcustomprocs
grant execute on dbo.sp_scriptpublicationcustomprocs to public

--------------------------------------------------------------------------------
--. fn_sqlvarbasetostr 
--------------------------------------------------------------------------------

if exists (select * from sysobjects
		where type = 'FN' and name = 'fn_sqlvarbasetostr')
     drop function fn_sqlvarbasetostr
go

raiserror('Creating function fn_sqlvarbasetostr',0,1)
go
--
-- fn_sqlvarbasetostr
--
-- UDF to generate string from a given sqlvariant using its base type.
-- This function is used by compensating commands and use special rules
-- for generating strings :
-- For datatypes - varchar, nvarchar, char, nchar, uniqueidenfier, 
-- datetime, smalldatetime, invalid date : The value is quoted with single
-- quotes and any embedded quotes are prefixed with single quote
-- For other datatypes - The value is not quoted
-- 
-- Parameters:
--	@ssvar sql_variant 		input sqlvariant parameter (could be NULL)
--
-- Returns:
-- 	nvarchar(4000) NULL		generated string. Is NULL if input is NULL
--
create function dbo.fn_sqlvarbasetostr (
	@ssvar sql_variant
)
returns nvarchar(4000)
as
begin
	declare @pstrout nvarchar(4000)
			,@basetype sysname

	select @basetype = CAST(SQL_VARIANT_PROPERTY ( @ssvar, 'BaseType' ) as nvarchar(255))
	if (@ssvar IS NOT NULL and @basetype IS NOT NULL)
	begin
		if (lower(@basetype collate SQL_Latin1_General_CP1_CS_AS) = 'varchar')
			select @pstrout = N'''' + REPLACE(CAST(@ssvar as nvarchar(4000)), '''', '''''') + N''''
		else if (lower(@basetype collate SQL_Latin1_General_CP1_CS_AS) = 'nvarchar')
			select @pstrout = N'N''' + REPLACE(CAST(@ssvar as nvarchar(4000)), '''', '''''') + N''''
		else if (lower(@basetype collate SQL_Latin1_General_CP1_CS_AS) = 'char')
			select @pstrout = N'''' + REPLACE(RTRIM(CAST(@ssvar as nvarchar(4000))), '''', '''''') + N''''
		else if (lower(@basetype collate SQL_Latin1_General_CP1_CS_AS) = 'nchar')
			select @pstrout = N'N''' + REPLACE(RTRIM(CAST(@ssvar as nvarchar(4000))), '''', '''''') + N''''
		else if (lower(@basetype collate SQL_Latin1_General_CP1_CS_AS) in ('binary','varbinary'))
			select @pstrout = master.dbo.fn_varbintohexsubstring(1, CAST(@ssvar as varbinary(8000)), 1, 0)
		else if (lower(@basetype collate SQL_Latin1_General_CP1_CS_AS) in ('bit','bigint','int','smallint','tinyint','float','real','decimal','numeric'))
			select @pstrout = CAST(@ssvar as nvarchar(40))
		else if (lower(@basetype collate SQL_Latin1_General_CP1_CS_AS) in ('money','smallmoney'))
			select @pstrout = CONVERT(nvarchar(40), @ssvar, 2)
		else if (lower(@basetype collate SQL_Latin1_General_CP1_CS_AS) = 'uniqueidentifier')
			select @pstrout = N'''' + CAST(@ssvar as nvarchar(40)) + N''''
		else if (lower(@basetype collate SQL_Latin1_General_CP1_CS_AS) in ('datetime','smalldatetime'))
			select @pstrout = N'''' + CONVERT(nvarchar(40), @ssvar, 121) + N''''
		else
			select @pstrout = N'''Invalid Datatype' + lower(@basetype collate SQL_Latin1_General_CP1_CS_AS) + N'(' + CAST(@ssvar as nvarchar) + N')'''
	end

	-- All done
	return @pstrout
end
go
exec dbo.sp_MS_marksystemobject fn_sqlvarbasetostr
go

grant execute on dbo.fn_sqlvarbasetostr to public
go


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

raiserror(15339,-1,-1,'sp_replqueuemonitor')
GO

--
-- sp_replqueuemonitor
--
-- Description: this stored procedure displays the messages stored in the 
--  	queue for a given subscription to a queued publication
--
-- Parameters:  
--    1. publisher (sysname, NULL for all publishers)
--    2. publisherdb (sysname, NULL for all publisherdbs)
--    3. publication (sysname , NULL for all publications)
--    4. tranid ( nvarchar(70) NULL for all transactions)
--	  5. queuetype (tinyint 0 for all types of queues, 1 for MSMQ, 2 for SQL)
--
-- Results:
--  Rows of Messages stored in the queue (all types) in the following format:
--	publisher 		sysname,
--	publisher_db 	sysname,
--	publication 	sysname,
--	tranid 			sysname,
--	commandlen 		int,
--	command 		nvarchar(4000)
-- The command is truncated (less than 4000 characters) if
--	1. The total rowsize exceeds the maximum of 8060 bytes
--	2. The SQL command spans multiple queue messages
-- The queue messages that do not contain SQL command or are part of a spanning
-- SQL command are not displayed
--
-- Returns:
-- 		0 if success
--		1 if failure
--
raiserror('Creating procedure sp_replqueuemonitor', 0,1)
go
create proc sp_replqueuemonitor (
	@publisher 		sysname = NULL
	,@publisherdb 	sysname = NULL
	,@publication 	sysname = NULL
	,@tranid		 	sysname = NULL
	,@queuetype		tinyint = 0	-- 0 = All Queues, 1 = MSMQ, 2 = SQL
)
as
begin
	set nocount on
	declare @retcode int
			,@queue_server sysname
			,@queue_id sysname
			,@data varbinary(8000)
			,@datalen int
			,@commandtype int
			,@cmdstate bit
			,@mesglen int
			,@command nvarchar(4000)
			,@partialindex int
			,@rowlen int
			,@comandlen int

	declare	@k_mesg_partial_state bit
			,@k_mesg_complete_state bit
			,@k_mesg_tran_cmd int
			,@k_max_rowlen int
			,@k_queuetype_all tinyint
			,@k_queuetype_msmq tinyint
			,@k_queuetype_sql tinyint

	create table #mesgs (mesgid int identity PRIMARY KEY, queuetype tinyint default 1, publisher sysname collate database_default, publisher_db sysname collate database_default, publication sysname collate database_default, 
							tranid sysname collate database_default, commandlen int, command ntext)

	--
	-- Check if need to look for subscriptions
	--
	if exists (select * from dbo.sysobjects where name = 'MSsubscription_agents')
	begin
		--
		-- Are there any qualifying subscriptions
		--
		if exists (select * from dbo.MSsubscription_agents where
				publisher = case when @publisher is NULL then publisher else UPPER(@publisher) end AND
				publisher_db = case when @publisherdb is NULL then publisher_db else @publisherdb end AND
				publication = case when @publication is NULL then publication else @publication end )
		begin
			--
			-- initialize
			--
			select 	@k_queuetype_all = 0
					,@k_queuetype_msmq = 1
					,@k_queuetype_sql = 2
					
			--
			-- MSMQ based
			--
			if (@queuetype in (@k_queuetype_all, @k_queuetype_msmq) and
				exists (select * from dbo.MSsubscription_agents where
					publisher = case when @publisher is NULL then publisher else UPPER(@publisher) end AND
					publisher_db = case when @publisherdb is NULL then publisher_db else @publisherdb end AND
					publication = case when @publication is NULL then publication else @publication end  AND
					update_mode IN (2,3) AND
					queue_id != N'mssqlqueue'))
			begin
				--
				-- enumerate each queue
				--
				create table #queues (publisher sysname collate database_default, publisher_db sysname collate database_default, publication sysname collate database_default, queue_id sysname collate database_default)
				declare #htempcursor cursor local for
					select publisher, publisher_db, publication, queue_server, queue_id 
					from dbo.MSsubscription_agents 
					where
						publisher = case when @publisher is NULL then publisher else UPPER(@publisher) end AND
						publisher_db = case when @publisherdb is NULL then publisher_db else @publisherdb end AND
						publication = case when @publication is NULL then publication else @publication end  AND
						update_mode IN (2,3) AND
						queue_id != N'mssqlqueue'

				open #htempcursor
				fetch #htempcursor into @publisher, @publisherdb, @publication, @queue_server, @queue_id
				while (@@fetch_status = 0)
				begin
					--
					-- add the queue server prefix
					--
					select @queue_id = N'DIRECT=OS:' + @queue_server + N'\PRIVATE$\' + @queue_id
					
					--
					-- Display all the messages in this queue
					--
					insert into #mesgs (publisher, publisher_db, publication, tranid, commandlen, command)
						exec @retcode = master.dbo.xp_displayqueuemesgs @publisher, @publisherdb, @publication, @queue_id, @tranid
					if (@retcode != 0 or @@error != 0)
						return 1

					--
					-- fetch next row
					--
					fetch #htempcursor into @publisher, @publisherdb, @publication, @queue_server, @queue_id
				end
				close #htempcursor
				deallocate #htempcursor
	
				--
				-- All MSMQ Queues processed
				--
				drop table #queues
			end
			
			--
			-- SQL Queued based
			--
			if (@queuetype in (@k_queuetype_all, @k_queuetype_sql) and
				exists (select * from dbo.MSsubscription_agents where
					publisher = case when @publisher is NULL then publisher else UPPER(@publisher) end AND
					publisher_db = case when @publisherdb is NULL then publisher_db else @publisherdb end AND
					publication = case when @publication is NULL then publication else @publication end  AND
					update_mode IN (4,5) AND
					queue_id = N'mssqlqueue'))
			begin
				--
				-- check if we have a queue
				--
				if exists (select * from dbo.sysobjects where name = 'MSreplication_queue')
				begin
					--
					-- initialize
					--
					select @mesglen = 0
							,@partialindex = 0
							,@k_mesg_partial_state = 1
							,@k_mesg_complete_state = 0
							,@k_mesg_tran_cmd = 1
							,@k_max_rowlen = 8000
					
					--
					-- select the messages that qualify
					--
											
					declare #htempcursor cursor local for
						select publisher, publisher_db, publication, tranid, datalen, data, commandtype, cmdstate
						from dbo.MSreplication_queue 
						where
							publisher = case when @publisher is NULL then publisher else UPPER(@publisher) end AND
							publisher_db = case when @publisherdb is NULL then publisher_db else @publisherdb end AND
							publication = case when @publication is NULL then publication else @publication end  AND
							tranid = case when @tranid IS NULL then tranid else @tranid end
					open #htempcursor
					fetch #htempcursor into @publisher, @publisherdb, @publication, @tranid, @datalen, @data, @commandtype, @cmdstate
					while (@@fetch_status = 0)
					begin
						--
						-- check the message state
						--
						if (@cmdstate = @k_mesg_partial_state)
							select @partialindex = @partialindex + 1
						select @mesglen = @mesglen + @datalen

						--
						-- process the body only for command type messages
						-- and if the command spans multiple rows, then
						-- display only the first row
						--
						if ((@commandtype = @k_mesg_tran_cmd) and
							((@cmdstate = @k_mesg_complete_state and @partialindex = 0) or
							(@cmdstate = @k_mesg_partial_state and @partialindex = 1)))
						begin
							--
							-- decode the command
							--
							exec @retcode = master.dbo.xp_decodequeuecmd @data, @command OUTPUT
							if (@retcode != 0 or @@error != 0)
								return 1
						end
						
						--
						-- Are processing the final row for this command
						--
						if (@cmdstate = @k_mesg_complete_state)
						begin
							--
							-- reset partial index
							--
							if (@partialindex > 0)
								select @partialindex = 0

							if (@command IS NOT NULL)
							begin
								--
								-- check if the command needs to truncated to fit the max rowsize
								--
								select @rowlen = 4 + DATALENGTH(@k_queuetype_sql) + 
												 DATALENGTH(@publisher) + DATALENGTH(@publisherdb) +
												 DATALENGTH(@publication) + DATALENGTH(@tranid) + 
												 DATALENGTH(@mesglen)
										,@comandlen = DATALENGTH(@command)
								if (@rowlen + @comandlen > @k_max_rowlen)
								begin
									select @comandlen = @k_max_rowlen - @rowlen
									select @comandlen = @comandlen / 2
									select @command = SUBSTRING(@command, 1, @comandlen)
								end
								
								insert into #mesgs (queuetype, publisher, publisher_db, publication, tranid, commandlen, command)
								values (@k_queuetype_sql, @publisher, @publisherdb, @publication, @tranid, @mesglen, @command)
								if (@retcode != 0 or @@error != 0)
									return 1

								select @command = NULL
							end

							--
							-- reset command len
							--
							if (@mesglen > 0)
								select @mesglen = 0
						end
						
						--
						-- fetch next row
						--
						fetch #htempcursor into @publisher, @publisherdb, @publication, @tranid, @datalen, @data, @commandtype, @cmdstate
					end
					close #htempcursor
					deallocate #htempcursor
				end 
	
				--
				-- All SQL Queues processed
				--
			end
		end
	end
	
	--
	-- return result
	--
	select 	queue = case when queuetype = @k_queuetype_msmq then N'MSMQ'
						when queuetype = @k_queuetype_sql then N'SQLQ' end 
			,publisher
			,publisher_db
			,publication
			,tranid
			,commandlen
			,command 
	from #mesgs
	order by mesgid
	
	--
	-- All done
	--
	drop table #mesgs
	return 0
end
go
exec dbo.sp_MS_marksystemobject sp_replqueuemonitor
go


--
-- sp_MSrepl_schema 
--
-- Description: Helper store proc, used by schema replication.
--		constructs schema modification code and post to the log.
--
-- Returns:
-- 		0 if success
--		1 if failure
--
raiserror('Creating procedure sp_MSrepl_schema', 0,1)
go

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

raiserror(15339,-1,-1,'sp_MSrepl_schema')
GO

create proc sp_MSrepl_schema @pubname sysname
				,@artid int 
				,@qual_source_object nvarchar(362) -- quoted table name
				,@column sysname -- column name, not quoted, as we need to search in syscolumns by it.
				,@operation int -- 0 is add, 1 is drop
				,@typetext nvarchar(3000) = NULL	
				,@schema_change_script nvarchar(4000) = NULL
as
begin
	declare @retcode int
	declare @pubid int
	declare @objid int
	declare @schema_option binary(8)
	declare @auto_gen int
	declare @cmd_type int
	declare @ins_cmd nvarchar(510)
	declare @del_cmd nvarchar(510)
	declare @upd_cmd nvarchar(510)
	declare @repub_command nvarchar(4000)
	declare @nopub_command nvarchar(4000)
	declare @prefix nvarchar(32)
	declare @post_cmd nvarchar(4000)
	declare @qual_column sysname
	declare @use_script bit
	declare @allow_dts bit
	
	set nocount on
	select @retcode = 0
	select @auto_gen = 2 -- auto generate custom procs
	select @cmd_type = 35 -- SQL statement
	select @qual_column = QUOTENAME(@column)
	select @objid = object_id(@qual_source_object)

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

	if (@schema_change_script is not NULL) and (len(@schema_change_script) > 0)
		select @use_script = 1
	else
		select @use_script = 0
	
	
	select @pubid = a.pubid, @schema_option = schema_option, @ins_cmd = ins_cmd, @del_cmd = del_cmd, @upd_cmd = upd_cmd, @allow_dts = allow_dts 
		from sysarticles a join syspublications p on a.pubid = p.pubid where artid = @artid
	
	if (@allow_dts = 1)
		goto SCRIPTONLY

	if(@operation = 0)
	begin
		select @repub_command = N'exec sp_repladdcolumn @source_object=N''' + @qual_source_object + N''',@column=N''' + replace(@column  , N'''', N'''''')
						+ N''',@typetext=N''' + replace(@typetext, N'''', N'''''') + N''' '
		select @nopub_command = N'else alter table ' + @qual_source_object + N' add ' + @qual_column + N' ' + @typetext + N' ' 
		select @prefix = N'if not exists '
	end
	else
	begin
		select @repub_command = N'exec sp_repldropcolumn @source_object=N''' + @qual_source_object + N''',@column=N''' + @column  + N''' '
		select @nopub_command = N'else alter table ' + @qual_source_object + N' drop column ' + @qual_column + N' '
		select @prefix = N'if exists '
	end
	if (@use_script = 1)--Need to pass the script file along if sub is republished.
		select @repub_command = @repub_command  + N',@schema_change_script=N''' + @schema_change_script + N''' '
	select @post_cmd = @prefix + N'(select * from syscolumns where name=''' + @column + ''' and id = object_id('''+ PARSENAME(@qual_source_object, 1) + ''')) begin '
	exec @retcode = sp_replpostcmd 1, @pubid, @artid, @cmd_type, @post_cmd 
	if(@retcode <> 0) or (@@error <> 0)
		return 1
	select @post_cmd = N'if exists (select * from sysobjects where name=''syspublications'') if exists (select * from sysarticles where objid=object_id('''+ PARSENAME(@qual_source_object, 1) + ''')) and @@microsoftversion >= 0x07320000 '
	exec @retcode = sp_replpostcmd 1, @pubid, @artid, @cmd_type, @post_cmd 
	if(@retcode <> 0) or (@@error <> 0)
		return 1
	exec @retcode = sp_replpostcmd 1, @pubid, @artid, @cmd_type, @repub_command
	if(@retcode <> 0) or (@@error <> 0)
		return 1
	exec @retcode = sp_replpostcmd 1, @pubid, @artid, @cmd_type, @nopub_command
	if(@retcode <> 0) or (@@error <> 0)
		return 1
	exec @retcode = sp_replpostcmd 1, @pubid, @artid, @cmd_type, @nopub_command
	if(@retcode <> 0) or (@@error <> 0)
		return 1
	exec @retcode = sp_replpostcmd 0, @pubid, @artid, @cmd_type, N' end '
	if(@retcode <> 0) or (@@error <> 0)
		return 1
	
	if ((convert(int, @schema_option) & @auto_gen) > 0)-- No script, but custom procs were auto-generated
	begin
		if(UPPER(LEFT(LTRIM(@ins_cmd), 4) collate SQL_Latin1_General_CP1_CS_AS) = UPPER('CALL' collate SQL_Latin1_General_CP1_CS_AS))
		begin
			exec @retcode = sp_MSpost_auto_proc @pubid, @artid, 'sp_scriptinsproc' 
			if(@retcode <> 0) or (@@error <> 0)
				return 1
		end
		if(UPPER(LEFT(LTRIM(@del_cmd), 4) collate SQL_Latin1_General_CP1_CS_AS) = UPPER('CALL' collate SQL_Latin1_General_CP1_CS_AS))
		begin
			exec @retcode = sp_MSpost_auto_proc @pubid, @artid, 'sp_scriptdelproc' 
			if(@retcode <> 0) or (@@error <> 0)
				return 1
		end
		else if(UPPER(LEFT(LTRIM(@del_cmd), 5) collate SQL_Latin1_General_CP1_CS_AS) = UPPER('XCALL' collate SQL_Latin1_General_CP1_CS_AS))
		begin
			exec @retcode = sp_MSpost_auto_proc @pubid, @artid, 'sp_scriptxdelproc' 
			if(@retcode <> 0) or (@@error <> 0)
				return 1
		end
		if(UPPER(LEFT(LTRIM(@upd_cmd), 4) collate SQL_Latin1_General_CP1_CS_AS) = UPPER('CALL' collate SQL_Latin1_General_CP1_CS_AS))
		begin
			exec @retcode = sp_MSpost_auto_proc @pubid, @artid, 'sp_scriptupdproc' 
			if(@retcode <> 0) or (@@error <> 0)
				return 1
		end
		else if(UPPER(LEFT(LTRIM(@upd_cmd), 5) collate SQL_Latin1_General_CP1_CS_AS) = UPPER('MCALL' collate SQL_Latin1_General_CP1_CS_AS))
		begin
			exec @retcode = sp_MSpost_auto_proc @pubid, @artid, 'sp_scriptmappedupdproc' 
			if(@retcode <> 0) or (@@error <> 0)
				return 1
		end
		else if(UPPER(LEFT(LTRIM(@upd_cmd), 5) collate SQL_Latin1_General_CP1_CS_AS) = UPPER('XCALL' collate SQL_Latin1_General_CP1_CS_AS))
		begin
			exec @retcode = sp_MSpost_auto_proc @pubid, @artid, 'sp_scriptxupdproc' 
			if(@retcode <> 0) or (@@error <> 0)
				return 1
		end
	end
SCRIPTONLY:

	if (@use_script = 1)
	begin
		exec @retcode = sp_addscriptexec @publication = @pubname, @scriptfile = @schema_change_script
		if @retcode<>0 or @@ERROR<>0
			return 1
	end 
	return 0
end
go

EXEC dbo.sp_MS_marksystemobject sp_MSrepl_schema 
go

grant execute on dbo.sp_MSrepl_schema to public
go

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

raiserror(15339,-1,-1,'sp_getqueuedrows')
GO

--
-- sp_getqueuedrows
--
-- sp_getqueuedrows is invoked by user to find the rows of given table on
-- a subscriber database that have participated in a queued update and 
-- currently have not been resolved by the queue reader agent - i.e. current
-- have an outstanding queued transaction. The table has to be part of 
-- queued subscription.
-- 
-- Parameters
-- 	@tablename	sysname -- name of table
--	@user		sysname -- optional name of user
--	@tranid		nvarchar(70) -- optional tranid to filter results on
--
-- Returns
--	0 if success 
--	1 if failure
--
-- Resultset
-- 	Rows that currently have at least one queued transaction for this
--	subscribed table
--
raiserror('Creating procedure sp_getqueuedrows', 0,1)
go
create proc sp_getqueuedrows (
	@tablename sysname
	,@owner sysname = NULL
	,@tranid nvarchar(70) = NULL
)
as
begin
	set nocount on
	declare @retcode int
		,@dbname sysname
		,@qualified_tabname nvarchar(1000)
		,@tabid int
		,@agent_id int
		,@publisher sysname
		,@publisher_db sysname
		,@publication sysname
		,@queue_id sysname
		,@update_mode int
		,@failover_id int
		,@cmd nvarchar(4000)
		,@queue_server sysname
		,@indid int
		,@indkey int
		,@key sysname
		,@colid int
		,@typestring nvarchar(4000)
		,@artcol int
		,@xpinputstr nvarchar(4000)
		,@selectcl nvarchar(4000)
		,@joincl nvarchar(4000)

	--
	-- prepare the fully qualified table
	--
	select @owner = case when (@owner IS NULL) then N'dbo' else @owner end
			,@dbname = db_name()	
	select @qualified_tabname = quotename(@dbname) + N'.' 
					+ quotename(@owner) + N'.' + quotename(@tablename)
	select @tabid = object_id(@qualified_tabname)
	if (@tabid IS NULL) or (@tabid = 0)
	begin
		-- error
		raiserror('sp_getqueuedrows(debug): could not locate table %s', 16, 1, @qualified_tabname)
		return 1
	end

	--
	-- current user should have SELECT permission on the table
	--
	if ( permissions(@tabid) & 0x1 = 0 )
	begin
		-- error
		raiserror('sp_getqueuedrows(debug): current user does not have SELECT permission on table %s', 16, 1, @qualified_tabname)
		return 1
	end

	--
	-- make sure the table is participating in a active queued subscription
	--
	select @agent_id = agent_id 
	from dbo.MSsubscription_articles 
	where dest_table = @tablename and owner = @owner

	if (@agent_id IS NULL)
	begin
		-- error
		raiserror('sp_getqueuedrows(debug): table %s is not part of any active initialized queued subscription. Make sure your queued subscriptions are properly initialized', 16, 1, @qualified_tabname)
		return 1
	end

	--
	-- get the details for the subscription
	--
	select @publisher = publisher
			,@publisher_db = publisher_db
			,@publication = publication
			,@update_mode = update_mode
			,@queue_server = queue_server
			,@queue_id = queue_id
			,@failover_id = failover_mode
	from dbo.MSsubscription_agents where id = @agent_id
	if (@update_mode not in (2,3,4,5))
	begin
		-- error
		raiserror('sp_getqueuedrows(debug): table %s is not part of any active initialized queued subscription. Make sure your queued subscriptions are properly initialized', 16, 2, @qualified_tabname)
		return 1
	end
	
	--
	-- If we are in Immediate Failover mode - no queued messages
	--
	if (@update_mode in (3,5) and (@failover_id = 0))
	begin
		--
		-- do an empty select on the source table and return
		--
		select @cmd = N'declare @dummy_action nvarchar(10), @dummy_tranid nvarchar(70)
					select action=@dummy_action, tranid=@dummy_tranid, * from ' + 
					@qualified_tabname + N' where 1 = 2 '
		exec (@cmd)
		return 0
	end

	if (@update_mode in (2,3))
	begin
		--
		-- set queue prefix for MSMQ cases
		--
		select @queue_id = N'DIRECT=OS:' + @queue_server + N'\PRIVATE$\' + @queue_id
	end
	else
	begin
		--
		-- Check the queue table for SQLQ
		--
		if not exists (select * from dbo.MSreplication_queue
		where UPPER(publisher) = UPPER(@publisher) and
				publisher_db = @publisher_db and
				publication = @publication and
				tranid = case when @tranid IS NULL then tranid else @tranid end)
		begin
			--
			-- do an empty select on the source table and return
			--
			select @cmd = N'declare @dummy_action nvarchar(10), @dummy_tranid nvarchar(70)
					select action=@dummy_action, tranid=@dummy_tranid, * from ' + 
					@qualified_tabname + N' where 1 = 2 '
			exec (@cmd)
			return 0
		end
	end

	--
	-- Now find the PK columns for this table
	--
	select @indkey = 1
		,@artcol = 0
		,@xpinputstr = N''
		,@selectcl = N''
		,@joincl = N''
		,@retcode = 0

	select @indid = i.indid 
	from dbo.sysindexes i 
	where ((i.status & 2048) != 0) and (i.id = @tabid)
	if (@indid is null)
	begin
		raiserror('sp_getqueuedrows(debug): Cannot find primary key for %s', 
				16, -1, @qualified_tabname)
		return 1
	end
	
	--
	-- create an enumeration of all the columns that are part of PK
	--
	create table #pkcoltab(pkindex int identity, keyname sysname collate database_default not null)
	while (@indkey <= 16)
	begin
		select @key = index_col( @qualified_tabname, @indid, @indkey )
		if (@key is null)
			break
		else
			insert into #pkcoltab(keyname) values(@key)

		select @indkey = @indkey + 1
	end

	--
	-- initialize the commands that we need to build
	--
	if exists (select * from dbo.sysobjects where name = 'tempcrtcmd')
		drop table tempcrtcmd
	create table tempcrtcmd (c1 int identity NOT NULL, procedure_text nvarchar(4000) NULL)
	
	select @cmd = N'create table tempqjointab (action nvarchar(10), tranid nvarchar(70) '
	insert into tempcrtcmd(procedure_text) values(@cmd)

	--
	-- now walk through each article col and if it is
	-- a part of PK, then check find the column position of the key
	-- corresponding to any article column is set
	--
	DECLARE #hCColid CURSOR LOCAL FAST_FORWARD FOR 
		select colid, [name] from dbo.syscolumns 
		where id = @tabid order by colid asc

	OPEN #hCColid
	FETCH #hCColid INTO @colid, @key
	WHILE (@@fetch_status != -1)
	begin
		exec sp_MSget_type @tabid, @colid, NULL, @typestring output
		if ((@typestring IS NOT NULL) and (@typestring != N'timestamp'))
		begin
			--
			-- this column is part of the article
			--
			select @artcol = @artcol + 1
			if exists (select * from #pkcoltab where keyname = @key)
			begin
				--
				-- this column is part of PK (offset and precision, scale)
				-- prepare the input string for XP
				-- prepare the create join table command
				-- prepare the join and select clause for the result
				--
				select @xpinputstr = @xpinputstr + N';' + cast(@artcol as nvarchar) 
				if (@typestring = N'bigint')
					select @xpinputstr = @xpinputstr + N'(19,0)'
				else if (@typestring like N'decimal%') or (@typestring like N'numeric%')
				begin
					declare @startpos int
							,@endpos  int

					select @startpos = charindex(N'(', @typestring, 1)
					select @endpos = charindex(N')', @typestring, @startpos)
					select @xpinputstr = @xpinputstr + substring(@typestring, @startpos, (@endpos - @startpos + 1))
				end
				select @cmd = N',' + quotename(@key) + N' ' + @typestring
				insert into tempcrtcmd(procedure_text) values(@cmd)
				select @selectcl = @selectcl + N', b.' + quotename(@key)
				
				if (@artcol = 1)
				begin
					select @joincl = @joincl + N'a.' + quotename(@key) + N' = b.' + quotename(@key)
				end
				else
				begin
					select @joincl = @joincl + N'and a.' + quotename(@key) + N' = b.' + quotename(@key)
				end				
			end
			else
			begin
				--
				-- this column is not part of PK
				-- build the select clause for this column
				--
				select @selectcl = @selectcl + N', a.' + quotename(@key)
			end
		end		

		--
		-- get the next column
		--
		FETCH #hCColid INTO @colid, @key
	end
	CLOSE #hCColid
	DEALLOCATE #hCColid
	drop table #pkcoltab

	--
	-- create the join table now
	--
	select @cmd = N') '
	insert into tempcrtcmd(procedure_text) values(@cmd)
	if exists (select * from dbo.sysobjects where name = N'tempqjointab')
		drop table tempqjointab
	select @cmd = 'select procedure_text from dbo.tempcrtcmd order by c1'
	exec master..xp_execresultset @cmd, @dbname

	--
	-- populate the join table now
	--
	if (@update_mode in (2,3))
	begin
		--
		-- MSMQ case : one call to the xp should populate the join table
		--
		insert into tempqjointab
			exec master.dbo.xp_readpkfromqueue @tablename, @queue_id, @xpinputstr, @tranid
	end
	else
	begin
		--
		-- SQLQ case : select the data for this subscription and call the
		-- xp for each row in the cursor to populate the join table
		--
		declare @spancount int
				,@data varbinary(8000)
				,@state bit
		
		declare #hcurQInfo cursor local FAST_FORWARD FOR
		select data, cmdstate, tranid
		from dbo.MSreplication_queue
		where UPPER(publisher) = UPPER(@publisher) and
				publisher_db = @publisher_db and
				publication = @publication and
				tranid = case when @tranid IS NULL then tranid else @tranid end and
				commandtype = 1
		order by orderkey
		FOR READ ONLY

		select @spancount = 0
		open #hcurQInfo
		fetch #hcurQInfo into @data, @state, @tranid
		while (@@FETCH_STATUS = 0)
		begin
			declare @qbdata0 varbinary(8000)
					,@qbdata1 varbinary(8000)

			if (@state = 1)
			begin
				--
				-- command spanning more than a row
				-- we will allow spanning upto 2 rows
				--
				if (@spancount = 0)
					select @qbdata0 = @data
				else
				begin
					raiserror('sp_getqueuedrows(debug): Queued data spans 3 rows, cannot proceed', 16, -1)
					close #hcurQInfo
					deallocate #hcurQInfo
					select @retcode = 1
					goto cleanup
				end
				select @spancount = @spancount + 1
			end
			else
			begin
				--
				-- final row for the command
				--
				if (@spancount = 0)
					select @qbdata0 = @data				
				else if (@spancount = 1)
					select @qbdata1 = @data
				else
				begin
					raiserror('sp_getqueuedrows(debug): Queued data spans 3 rows, cannot proceed', 16, -1)
					close #hcurQInfo
					deallocate #hcurQInfo
					select @retcode = 1
					goto cleanup
				end

				--
				-- call the xp to populate the join table
				--
				insert into tempqjointab
					exec master.dbo.xp_readpkfromvarbin @tablename, @xpinputstr, @tranid, @spancount, @qbdata0, @qbdata1

				--
				-- reset the span count
				--
				select @spancount = 0
			end

			--
			-- fetch the next row
			--
			fetch #hcurQInfo into @data, @state, @tranid
		end
		close #hcurQInfo
		deallocate #hcurQInfo
	end

	--
	-- Now perform the join
	--
	select @cmd = N'select b.action, b.tranid ' + @selectcl 
		+ N'from ' + @qualified_tabname + N' a right join tempqjointab b on (' + @joincl + N') '
	exec (@cmd)
	
	--
	-- all done
	--
cleanup:	
	if exists (select * from dbo.sysobjects where name = N'tempqjointab')
		drop table tempqjointab
	if exists (select * from dbo.sysobjects where name = 'tempcrtcmd')
		drop table tempcrtcmd
	return @retcode
end
go

EXEC dbo.sp_MS_marksystemobject sp_getqueuedrows
GO


grant execute on dbo.sp_getqueuedrows to public
go

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

raiserror(15339,-1,-1,'sp_verify_publication')
GO

--
-- sp_verify_publication
--
-- Description: Invoked by user on a distributor server to perform a simple replication plumbing check.
--		Tests the connectivity of all the push subscriptions for a given publication.
--		(doesn't test pull or anonymous subscriptions)
--
-- Returns:
-- 		0 if success
--		1 if failure
--
create proc sp_verify_publication (
	@publisher sysname,		/*name of the publisher*/
	@publisher_db sysname,		/*the database for the publication*/
	@publication sysname,		/*name of the publication*/
	@reserved int = 0		/*reserved -- should not be used directly by user.
					  For internal use only.*/
)
AS
BEGIN
set nocount on

if( @reserved = 0 )
begin
  declare @proc nvarchar(255),
	  @distribution_db sysname,
	  @recurse_retcode int
  --
  --verify that this server is a distributor.
  --
  if (not exists( select * from msdb..sysobjects where name = 'MSdistpublishers') )
  begin
    RAISERROR(14114, 16, -1, @@SERVERNAME)
    return(1)
  end
  --
  --get the distribution database
  --
  select @distribution_db = distribution_db from msdb.dbo.MSdistpublishers where UPPER(name) = UPPER(@publisher) collate database_default
  if( @distribution_db is NULL )
  begin
    RAISERROR(21169, 16, -1, @publisher, @@SERVERNAME, @publisher)
    return(1)
  end

  select @proc = @distribution_db + '.dbo.sp_verify_publication'
  exec @recurse_retcode = @proc @publisher, @publisher_db, @publication, 1
  return(@recurse_retcode)
end

declare @publisher_id smallint,
	@subscriber sysname,
	@subscriber_db sysname,
	@type tinyint,
	@job_id binary(16),
	@publication_type int,
	@agent_type varchar(15),
	@command nvarchar(3200),
	@success varchar(300),
	@srvid smallint

select @srvid = srvid from master.dbo.sysservers where UPPER(srvname) = UPPER(@publisher) collate database_default
if @srvid is NULL
begin
  RAISERROR(21169, 16, -1, @publisher, @@SERVERNAME, @publisher)
  return(1)
end

select @publication_type = publication_type from MSpublications
  where publisher_id = @srvid and
  publisher_db = @publisher_db and
  publication = @publication

if @publication_type is NULL
begin
  RAISERROR(21332, 16, -1, @publication)
  return(1)
end

create table #T_VERIFICATION_RESULTS (
  subscriber sysname NOT NULL, subscriber_db sysname NOT NULL, type int NOT NULL,
  job_id binary(16) NULL, results varchar(300) NULL
)

-- Parameter Check: @publication_type
-- Make sure that the publication type is one of the following:
-- 0  transactional
-- 1  snapshot
-- 2  merge
if( @publication_type = 0 or @publication_type = 1 )
  begin
    select @agent_type = 'distribution'

    create table #T_SUBSCRIPTIONS (subscriber sysname NOT NULL,  status int NOT NULL, 
	subscriber_db sysname NOT NULL,
	type tinyint NOT NULL, distribution_agent nvarchar(100) NOT NULL, last_action nvarchar(255) NULL, 
	action_time nvarchar(24) NULL, start_time nvarchar(24) NULL, duration int NULL, 
	delivery_rate float NULL,
	delivery_latency int NULL, delivered_transactions int NULL, 
	delivered_commands int NULL,
	delivery_time int NULL, average_commands int NULL, 
	error_id int NULL, 
	job_id binary(16) NULL, local_job bit NULL, profile_id int NOT NULL,
	agent_id int NOT NULL, last_timestamp binary(8) NOT NULL, offload_enabled bit NOT NULL, 
	offload_server sysname NULL, subscriber_type tinyint NULL)

    insert #T_SUBSCRIPTIONS
      exec sp_MSenum_subscriptions @publisher, @publisher_db, @publication

    insert into #T_VERIFICATION_RESULTS (subscriber, subscriber_db, type, job_id)
	(select subscriber, subscriber_db, type, job_id from #T_SUBSCRIPTIONS)

    drop table #T_SUBSCRIPTIONS
  end
else if( @publication_type = 2 )
  begin
    select @agent_type = 'merge'

    create table #T_MERGE_SUBSCRIPTIONS (subscriber sysname NOT NULL,  status int NOT NULL, 
        subscriber_db sysname NOT NULL, type int NOT NULL, agent_name nvarchar(100) NOT NULL, last_action nvarchar(255) NULL, 
        action_time nvarchar(24) NULL, start_time nvarchar(24) NULL, duration int NULL, 
        delivery_rate float NULL,
        publisher_insertcount int NULL, publisher_updatecount int NULL, publisher_deletecount int NULL,
        publisher_conficts int NULL, 
        subscriber_insertcount int NULL, subscriber_updatecount int NULL, subscriber_deletecount int NULL,
        subscriber_conficts int NULL, error_id int NULL, job_id binary(16) NULL,
        local_job bit NULL, profile_id int NOT NULL, 
        agent_id int NOT NULL, last_timestamp binary(8) NOT NULL, offload_enabled bit NOT NULL, 
	offload_server sysname NULL, subscriber_type tinyint NULL)

    insert #T_MERGE_SUBSCRIPTIONS
      exec sp_MSenum_merge_subscriptions @publisher, @publisher_db, @publication

    insert into #T_VERIFICATION_RESULTS (subscriber, subscriber_db, type, job_id)
	(select subscriber, subscriber_db, type, job_id from #T_MERGE_SUBSCRIPTIONS)

    drop table #T_MERGE_SUBSCRIPTIONS
  end
else
  begin
    drop table #T_VERIFICATION_RESULTS
    RAISERROR(20033, 16, -1)
    return(1)
  end

if( not exists( select * from #T_VERIFICATION_RESULTS ) )
begin
  --
  --if #T_VERIFICATION_RESULTS is empty, then there's no subscriptions
  --
  RAISERROR(14135, 16, -1, @publisher, @publisher_db, @publication)
  drop table #T_VERIFICATION_RESULTS
  return(1)
end

declare subscribers_cursor cursor LOCAL FAST_FORWARD for
select subscriber, subscriber_db, type, job_id
  from #T_VERIFICATION_RESULTS
  order by subscriber, subscriber_db

open subscribers_cursor
fetch next from subscribers_cursor
  into @subscriber, @subscriber_db, @type, @job_id
while @@FETCH_STATUS = 0
begin
  if( @type = 0 )
  begin
    --
    --local push subscription
    --
    select @command = ''
    select @success = 'unknown'
    select @command = command from msdb.dbo.sysjobsteps
      where job_id = @job_id and
	    subsystem = case @publication_type  -- 0 = Transactional 1 = Snapshot 2 = Merge
	      when 0 then N'Distribution'
	      when 1 then N'Distribution'
	      when 2 then N'Merge'
	    end

    exec master.dbo.xp_replproberemsrv 'local', @agent_type, @success OUTPUT, @command, 1 --(islocal)
    if( @@ERROR <> 0) return(1)
    if UPPER(@success collate SQL_Latin1_General_CP1_CS_AS) = 'FALSE'
      select 'subscriber' = @subscriber, 'subscriber database' = @subscriber_db,
	     'subscription type' = case @type
		    when 0 then 'push'
		    when 1 then 'pull'
		    when 2 then 'anonymous'
		    else 'unknown'
		  end
	     , 'results' = 'connection failed'
    else if( ( CHARINDEX('TRUE', UPPER(@success collate SQL_Latin1_General_CP1_CS_AS)) = 1 ) and (LEN(@success) = 4) )
      select @success = 'connection succeeded'
  end
  else
  begin
    --
    --non-push subscription, not supported
    --
    select @success = 'only push subscriptions can be verified with this stored procedure'
  end

  update #T_VERIFICATION_RESULTS set results = @success where
    subscriber = @subscriber and
    subscriber_db = @subscriber_db and
    type = @type and
    job_id = @job_id
  --
  --get next subscription
  --
  fetch next from subscribers_cursor
    into @subscriber, @subscriber_db, @type, @job_id
end
close subscribers_cursor
deallocate subscribers_cursor

select subscriber, subscriber_db as 'subscriber database', 'subscription type' =
  case type
    when 0 then 'push'
    when 1 then 'pull'
    when 2 then 'anonymous'
    else 'unknown'
  end
, results from #T_VERIFICATION_RESULTS where UPPER(results collate SQL_Latin1_General_CP1_CS_AS) != 'FALSE'

drop table #T_VERIFICATION_RESULTS
END
go
EXEC dbo.sp_MS_marksystemobject sp_verify_publication


grant execute on dbo.sp_verify_publication to public
go

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

raiserror(15339,-1,-1,'sp_scriptdynamicupdproc')
GO

create procedure sp_scriptdynamicupdproc @artid int
as
declare @cmd          nvarchar(4000)
declare @dest_owner   nvarchar(255)
declare @dest_tabname sysname
declare @src_objid    int
declare @artcolumns   binary(32)
declare @pkcolumns    binary(32)
declare @upd_cmd      nvarchar(255)
declare @dest_proc    sysname
declare @art_cols     int
declare @this_col     int
declare @art_col      int
declare @pk_col       int
declare @pkart_col    int
declare @isset        int
declare @ispk         int
declare @isidentity   bit
declare @iscomputed   bit
declare @bytestr      nvarchar(10)
declare @bitstr       nvarchar(10)
declare @typestring   nvarchar(255)
declare @spacer_param nvarchar(10)
declare @update_created  bit
declare @pkcomputed   int
declare @pubid        int, @allow_queued_tran bit

set nocount on 

select @update_created	 = 0

if not exists( select * from sysarticles where artid = @artid AND (type & 1) = 1 )
begin
    raiserror (14155, 16, 1 )
    return 1
end

-------- create temp table for command fragments

create table #proctext ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default )
create table #proctext_params ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default )
create table #proctext_paramdef ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default )
create table #proctext_pkparams ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default )



-------- get sysarticles information

select @dest_owner = dest_owner, @dest_tabname = dest_table, 
       @src_objid = objid, @artcolumns = columns, @upd_cmd = upd_cmd,
	   @pubid = pubid
from sysarticles
where artid = @artid

if @dest_owner is not null
begin
	select @dest_owner = QUOTENAME( @dest_owner ) + N'.'
end
else
begin
	select @dest_owner = N''
end

-------- get dest proc name

if( 1 != charindex( N'MCALL', upper(@upd_cmd) ) ) or @upd_cmd is null
begin
    raiserror (14156, 16, 1 )
    return 1
end

select @allow_queued_tran = allow_queued_tran from syspublications where pubid = @pubid 

declare @keep_identity bit
if @allow_queued_tran = 1 and OBJECTPROPERTY(@src_objid, 'tablehasidentity') = 1
    select @keep_identity = 1
else
    select @keep_identity = 0

select @dest_proc = substring( @upd_cmd, 7, len( @upd_cmd ) - 5 )
select @cmd = N'if exists (select * from sysobjects where type = ''P''  and name = ''' + replace(@dest_proc, N'''', N'''''') + N''')  drop proc ' + QUOTENAME(@dest_proc)
insert into #proctext(procedure_text) values( @cmd )
insert into #proctext(procedure_text) values( N'go' )
insert into #proctext( procedure_text ) values ( N'create procedure ' + QUOTENAME(@dest_proc) + N' ' )
  

-------- construct parameter list

exec dbo.sp_getarticlepkcolbitmap @src_objid, @pkcolumns output

exec dbo.sp_scriptupdateparams @src_objid, @artcolumns, @pkcolumns

----- add changed data bitmap

select @art_col = 1
select @pk_col = 1

DECLARE hCColid CURSOR LOCAL FAST_FORWARD FOR 
select colid from syscolumns where id = @src_objid order by colid asc

OPEN hCColid

FETCH hCColid INTO @this_col

WHILE (@@fetch_status <> -1)
begin
   exec @isset = dbo.sp_isarticlecolbitset @this_col, @artcolumns
   if @isset != 0 and EXISTS (select name from syscolumns where colid=@this_col and iscomputed<>1 and id = @src_objid)
   begin
        select @art_col = @art_col + 1
   end
   FETCH hCColid INTO @this_col
end
CLOSE hCColid
DEALLOCATE hCColid

-- Note that bitmap size is based on number of article columns
-- (computed by loop above) not source table columns

select @cmd = N',@bitmap binary(' + convert(nvarchar,1+(@art_col-1) / 8) + N')'

insert into #proctext(procedure_text) values( @cmd )
insert into #proctext(procedure_text) values( N'as' )



-- Find out if pk is identity
declare @pk_is_identity bit
select @pk_is_identity = 0

exec @pkcomputed = sp_MSareallcolumnscomputed @src_objid, @pkcolumns

if @artcolumns != @pkcolumns and @pkcomputed = 0
begin
    select @art_col = 1

	DECLARE hCColid CURSOR LOCAL FAST_FORWARD FOR 
	select colid from syscolumns where id = @src_objid order by colid asc

	OPEN hCColid

	FETCH hCColid INTO @this_col

	select @pk_is_identity = 1

	WHILE (@@fetch_status <> -1)
    begin
        exec @isset = dbo.sp_isarticlecolbitset @this_col, @artcolumns
        if @isset != 0 and EXISTS (select name from syscolumns where id=@src_objid and @this_col=colid and iscomputed<>1)
        begin 
            exec @isset = dbo.sp_isarticlecolbitset @this_col, @pkcolumns
            if @isset != 0
            begin
				if not (@keep_identity = 1 and 
					columnproperty(@src_objid, col_name( @src_objid, @this_col), 'IsIdentity') = 1)
				begin
					select @pk_is_identity = 0
				end
            end
            select @art_col = @art_col + 1
        end
		FETCH hCColid INTO @this_col
    end
	CLOSE hCColid
	DEALLOCATE hCColid
end 

-- construct UPDATE that does not set PK cols
-- only do this if the article contains columns that are not included
-- in the pk
-- Note: we assume pk columns never change. pk changes will be replicated
-- as delete insert.

if @artcolumns != @pkcolumns  and @pkcomputed = 0 and @pk_is_identity = 0
begin

    -- create SET clause consisting of CASE statements

    select @art_col = 1
    select @spacer_param = N''

	DECLARE hCColid CURSOR LOCAL FAST_FORWARD FOR 
	select colid from syscolumns where id = @src_objid order by colid asc

	OPEN hCColid

	FETCH hCColid INTO @this_col

	WHILE (@@fetch_status <> -1)
    begin
        exec @isset = dbo.sp_isarticlecolbitset @this_col, @artcolumns
        if @isset != 0  
		begin
            exec @ispk = dbo.sp_isarticlecolbitset @this_col, @pkcolumns
			
			if EXISTS (select name from syscolumns where colid=@this_col and iscomputed<>1 and id = @src_objid)
				select @iscomputed = 0
			else
				select @iscomputed = 1
			
			if (@keep_identity = 1 and 
					columnproperty(@src_objid, col_name( @src_objid, @this_col), 'IsIdentity') = 1)  
				select @isidentity = 1
			else
				select @isidentity = 0
				
			-- If not pk, not computed and not identity	
			if @ispk = 0 and @iscomputed = 0 and @isidentity = 0
            begin
            	if (@update_created = 0)
            	begin
			    	insert into #proctext(procedure_text) values( 
						N'declare @stmt nvarchar(4000), @spacer nvarchar(1)')

			    	insert into #proctext(procedure_text) values( 
						N'select @spacer =N''''')

			    	insert into #proctext(procedure_text) values( 
						N'select @stmt = N''update ' + 
						replace(@dest_owner + QUOTENAME(@dest_tabname), N'''', N'''''' ) +
						N' set ''')

			    	select @update_created = 1
				end

				select @bytestr = convert( nvarchar, 1 + (@art_col-1) / 8 )
				select @bitstr =  convert( nvarchar, power(2, (@art_col-1) % 8 ) )

				insert into #proctext(procedure_text) values (
					 N'if substring(@bitmap,' + @bytestr + N',1) & ' + @bitstr +  
					 N' = ' + @bitstr)

				insert into #proctext(procedure_text) values (N'begin')

				-- Append statement
				insert into #proctext(procedure_text) values (
					 N'select @stmt = @stmt + @spacer + ' + 
					 N'N''' + replace(QUOTENAME(col_name( @src_objid, @this_col)), N'''', N'''''') + N''' + ' +
					 N'N''=@'+ convert( nvarchar, @art_col ) + N'''')

				-- Set @spacer is the proc
				insert into #proctext(procedure_text) values (
					N'select @spacer = N'',''')

				insert into #proctext(procedure_text) values (N'end')
            end
			-- If pk or (not computed and not identity) add the param
			if @ispk <> 0 or (@iscomputed = 0 and @isidentity = 0)
			begin
				-- Add to param list
			    if (@ispk <> 0)
				  begin
				    -- Use primary key value
				    insert into #proctext_params(procedure_text) values( 
						@spacer_param + N'@pkc' + convert( nvarchar, @pk_col ))

				    -- Increment primary key counter
				    select @pk_col = @pk_col + 1;

				  end
				else
				    -- Use column value
					insert into #proctext_params(procedure_text) values( 
						@spacer_param + N'@c' + convert( nvarchar, @art_col ))

				-- Get type str
				exec dbo.sp_gettypestring @src_objid, @this_col, @typestring OUTPUT

				-- Append typedef
				insert into #proctext_paramdef(procedure_text) values (
					 @spacer_param + N'@' + convert( nvarchar, @art_col ) + 
					 N' ' + @typestring )

				select @spacer_param = ',' 
			end

            select @art_col = @art_col + 1
        end
       	FETCH hCColid INTO @this_col
    end
	CLOSE hCColid
	DEALLOCATE hCColid

    if @update_created = 1
	begin

		-- Append statement of the start of where clause
		insert into #proctext(procedure_text) values (
			 N'	select @stmt = @stmt + N'' ')

		-- Escape ' in the where clause
		declare @low_mark int, @high_mark int
		select @low_mark = max(c1) from #proctext
		-- Pass in @artcolumns so that the where clause will use the ordinal in the
		-- article bit map rather then one in pk bitmap. This is consistent with
		-- the type def string.
		-- UNDONE: change comments in sp_scriptpkwhereclause
		-- Use prefix '@' to be consistent with type def string
		exec dbo.sp_scriptpkwhereclause @src_objid, @pkcolumns, '@', @artcolumns
		select @high_mark = max(c1) from #proctext
		update #proctext set procedure_text = replace ( procedure_text, N'''', N'''''') 
			where c1 > @low_mark and c1 <= @high_mark

		-- Close the where clause
		insert into #proctext(procedure_text) values (
			 N'''')

		-- Add call to sql_executesql and the param list
		insert into #proctext(procedure_text) values( 
			N'exec sp_executesql @stmt, N'' ')

		-- Add param def
		insert into #proctext(procedure_text) 
			select procedure_text from #proctext_paramdef order by c1 asc

		-- Close the param def
		insert into #proctext(procedure_text) values (
			 N''',')

		-- Add param list
		insert into #proctext(procedure_text) 
			select procedure_text from #proctext_params order by c1 asc

		if @allow_queued_tran <> 1
			exec dbo.sp_MSscript_missing_row_check
	end

end

select procedure_text from #proctext order by c1 asc

go
  
EXEC dbo.sp_MS_marksystemobject sp_scriptdynamicupdproc
go

grant execute on sp_scriptdynamicupdproc to public
go  

--------------------------------------------------------------------------------
--.	Merge Replication objects (rladmin.sql)
--------------------------------------------------------------------------------

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

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 collate SQL_Latin1_General_CP1_CS_AS)

    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 collate SQL_Latin1_General_CP1_CS_AS) 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 collate SQL_Latin1_General_CP1_CS_AS) = 'none'
       select @precmdid = 0
    ELSE IF LOWER(@pre_creation_cmd collate SQL_Latin1_General_CP1_CS_AS) = 'drop'
       select @precmdid = 1
    ELSE IF LOWER(@pre_creation_cmd collate SQL_Latin1_General_CP1_CS_AS) = 'delete'
       select @precmdid = 2
    ELSE IF LOWER(@pre_creation_cmd collate SQL_Latin1_General_CP1_CS_AS) = '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 collate SQL_Latin1_General_CP1_CS_AS) 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 collate SQL_Latin1_General_CP1_CS_AS) = N'table'
    BEGIN
       SET @typeid = 0x0a
    END
    ELSE IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) = N'proc schema only'
    BEGIN
       SET @typeid = 0x20 
    END
    ELSE IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) = N'view schema only'
    BEGIN
       SET @typeid = 0x40
    END
    ELSE IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) = N'indexed view schema only'
    BEGIN
       SET @typeid = 0x40
    END
    ELSE IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) = 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 collate SQL_Latin1_General_CP1_CS_AS) = '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 collate SQL_Latin1_General_CP1_CS_AS) = '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(20086 , 16, -1, @publication)
                    return (1)
                end
        end

	/*
	** If you want to have identity support, @range and threshold can not be NULL
	*/
	if LOWER(@auto_identity_range collate SQL_Latin1_General_CP1_CS_AS) = '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 collate SQL_Latin1_General_CP1_CS_AS) = '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 collate SQL_Latin1_General_CP1_CS_AS) = '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 collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@column_tracking')
            RETURN (1)
        END
    if LOWER(@column_tracking collate SQL_Latin1_General_CP1_CS_AS) = '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 collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@allow_interactive_resolver')
            RETURN (1)
        END
    if LOWER(@allow_interactive_resolver collate SQL_Latin1_General_CP1_CS_AS) = 'true'
        set @allow_interactive_bit = 1
    else 
        set @allow_interactive_bit = 0

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

				/* To avoid div by zero errors, error out if pub_range is 0 */
				if @pub_identity_range = 0
					begin
						goto FAILURE
					end
				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
					where objid=@objid
				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=NULL
			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=NULL
			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

                execute @retcode = dbo.sp_MSaddguidindex @publication, @source_owner, @source_object
                if @@ERROR <> 0 OR @retcode <> 0
                    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


--------------------------------------------------------------------------------
--. sp_MSchangemergeschemaarticle 
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P '
            and name = 'sp_MSchangemergeschemaarticle')
    drop procedure sp_MSchangemergeschemaarticle
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 collate SQL_Latin1_General_CP1_CS_AS)
    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 collate SQL_Latin1_General_CP1_CS_AS)
        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 collate SQL_Latin1_General_CP1_CS_AS)

        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

--------------------------------------------------------------------------------
--. sp_changemergearticle 
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P '
            and name = 'sp_changemergearticle')
    drop procedure sp_changemergearticle
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 @resolver_clsid_str nvarchar(55)
    declare @article_resolver_str   nvarchar(270)
	declare @value_str			nvarchar(270)
    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 collate SQL_Latin1_General_CP1_CS_AS) 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 collate SQL_Latin1_General_CP1_CS_AS) 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 collate SQL_Latin1_General_CP1_CS_AS) 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 collate SQL_Latin1_General_CP1_CS_AS) 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 collate SQL_Latin1_General_CP1_CS_AS) = 'column_tracking'
        BEGIN
            /*
            ** Check to make sure that we have a valid type.
            */
            if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
                BEGIN
                    RAISERROR (14137, 16, -1)
                    goto UNDO
                END

            /*
            ** Update the syssubsetdefintions table with the new column tracking.
            */
            if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = '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 collate SQL_Latin1_General_CP1_CS_AS) = '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 collate SQL_Latin1_General_CP1_CS_AS) = '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 collate SQL_Latin1_General_CP1_CS_AS) = '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 collate SQL_Latin1_General_CP1_CS_AS) = '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 collate SQL_Latin1_General_CP1_CS_AS)='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 collate SQL_Latin1_General_CP1_CS_AS) ='creation_script'
        BEGIN
            update sysmergearticles set creation_script=@value where artid=@artid and pubid=@pubid
            if @@ERROR <> 0 
                goto UNDO
        END

    if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = '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 collate SQL_Latin1_General_CP1_CS_AS) ='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 ' + quotename(@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 collate SQL_Latin1_General_CP1_CS_AS) ='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

            -- Convert NULL value to 'NULL' string to be used for constructing schema text
            -- we do not want to change NULL value to ''.
            select @article_resolver_str=QUOTENAME(@article_resolver)
            select @resolver_clsid_str = @resolver_clsid
            select @value_str=@value
            if @article_resolver is null set @article_resolver_str = 'NULL'
            if @resolver_clsid is null set @resolver_clsid_str = 'NULL'
            if @value is null set @value_str = 'NULL'

			/* 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_str + ',' + '''' + @resolver_clsid_str + '''' + ',' + '''' + convert(nchar(36), @artid) + '''' + ',' + '''' + @value_str + ''''
		                         
		                        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 collate SQL_Latin1_General_CP1_CS_AS) = 'pre_creation_command'
        BEGIN

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

            if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) 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 collate SQL_Latin1_General_CP1_CS_AS) = 'none'
                select @precmdid = 0
            else if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'drop'
                select @precmdid = 1
            else if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'delete'
                select @precmdid = 2
            else if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = '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 collate SQL_Latin1_General_CP1_CS_AS) = 'status'
        BEGIN

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

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

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

            if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'unsynced'
                select @statusid = 1
            else if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'active'
                select @statusid = 2
            else if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'new_inactive'
            	select @statusid = 5
            else if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = '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 collate SQL_Latin1_General_CP1_CS_AS) = '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 collate SQL_Latin1_General_CP1_CS_AS) = '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 collate SQL_Latin1_General_CP1_CS_AS) = '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 collate SQL_Latin1_General_CP1_CS_AS) = 'allow_interactive_resolver'
        BEGIN

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

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

            /* Determine the bit value. */

	        IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = '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

--------------------------------------------------------------------------------
--. sp_dropmergearticle
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P '
            and name = 'sp_dropmergearticle')
    drop procedure sp_dropmergearticle
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 except
    ** when the article is schema-only
    */
    if @snapshot_ready>0 and 
       (@allow_anonymous=1 or 
       exists (select * from sysmergesubscriptions 
                where pubid=@pubid and subid<>pubid and status=1)) and
       not exists (select * from sysmergeschemaarticles 
                    where pubid = @pubid and name = @article) 
        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

--------------------------------------------------------------------------------
--. sp_MSadjustmergeidentity 
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P '
            and name = 'sp_MSadjustmergeidentity')
    drop procedure sp_MSadjustmergeidentity
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

		/* To avoid div by zero errors, error out if pub_range is 0 */
		if @pub_range = 0
			begin
				return 1
			end
	
		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


--------------------------------------------------------------------------------
--. sp_addmergepublication 
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P '
            and name = 'sp_addmergepublication')
    drop procedure sp_addmergepublication
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 collate SQL_Latin1_General_CP1_CS_AS)='portable' select @sync_mode='character'

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

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

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

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

    if LOWER(@allow_push collate SQL_Latin1_General_CP1_CS_AS) = '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 collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@allow_pull')
            RETURN (1)
        END
    if LOWER(@allow_pull collate SQL_Latin1_General_CP1_CS_AS) = '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 collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@allow_anonymous')
            RETURN (1)
        END
    if LOWER(@allow_anonymous collate SQL_Latin1_General_CP1_CS_AS) = '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 collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@enabled_for_internet')
            RETURN (1)
        END

    IF LOWER(@enabled_for_internet collate SQL_Latin1_General_CP1_CS_AS) = '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 collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@centralized_conflicts')
            RETURN (1)
        END
    if LOWER(@centralized_conflicts collate SQL_Latin1_General_CP1_CS_AS) = '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 collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@dynamic_filters')
            RETURN (1)
        END

    IF LOWER(@dynamic_filters collate SQL_Latin1_General_CP1_CS_AS) = '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 collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
    BEGIN
        RAISERROR (14148, 16, -1, '@snapshot_in_defaultfolder')
        RETURN (1)
    END
    
    IF LOWER(@snapshot_in_defaultfolder collate SQL_Latin1_General_CP1_CS_AS) = '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 collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
    BEGIN
        RAISERROR (14148, 16, -1, '@compress_snapshot')
        RETURN (1)
    END
    
    IF LOWER(@compress_snapshot collate SQL_Latin1_General_CP1_CS_AS) = '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 collate SQL_Latin1_General_CP1_CS_AS) = 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 collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@keep_partition_changes')
            RETURN (1)
        END

    if LOWER(@keep_partition_changes collate SQL_Latin1_General_CP1_CS_AS) = '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 collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@allow_subscription_copy')
            RETURN (1)
        END
        
    IF LOWER(@allow_subscription_copy collate SQL_Latin1_General_CP1_CS_AS) = 'true' 
    	SELECT @allow_subscription_copy_id = 1
    ELSE 
    	SELECT @allow_subscription_copy_id = 0

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

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

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

    if LOWER(@add_to_active_directory collate SQL_Latin1_General_CP1_CS_AS) 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 collate SQL_Latin1_General_CP1_CS_AS)='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 collate SQL_Latin1_General_CP1_CS_AS)='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


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

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

    SET NOCOUNT ON

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

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

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

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

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

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

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

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

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

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


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

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

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

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

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

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

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

    BEGIN TRAN changemergepublication    
    save tran changemergepublication

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

	if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS)='dynamic_filters' 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 collate SQL_Latin1_General_CP1_CS_AS) = 'true' 
        	select @dynamic_filters_id = 1
	    else if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'false' 
       		select @dynamic_filters_id = 0
       	else
       		begin
       			raiserror(14137, 16, -1)
       			goto UNDO
       		end
		update sysmergepublications set dynamic_filters=@dynamic_filters_id, 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 collate SQL_Latin1_General_CP1_CS_AS)='validate_subscriber_info'
	begin
		if @value is not NULL
		begin
			if @dynamic_filters_id = 0
				begin
					raiserror(21313, 16, -1)
					GOTO UNDO
				end
			exec ('select ' + @value)
			if @@ERROR<>0
				begin
					raiserror(21299, 16, -1, @value)
					GOTO UNDO
				end
		end

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

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

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

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

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

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

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

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

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

            if @@ERROR <> 0 GOTO UNDO
        END


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

        select @retention = CONVERT(int, @value)

        if @retention is NULL 
        	select @retention = 0

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

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

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


        END

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

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

        if @value is NULL 
        	select @retention = 0

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

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

        if @@ERROR <> 0 
            GOTO UNDO

        END

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

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

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

        if @@ERROR <> 0 
            GOTO UNDO

        END


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

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

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

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

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

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

            if @@ERROR <> 0 GOTO UNDO
        END

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

        END

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

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

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

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

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

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

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

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

        END

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

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

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

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

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

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

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

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

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

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

    COMMIT TRAN

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

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

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

grant execute on dbo.sp_changemergepublication to public
go


--------------------------------------------------------------------------------
--. sp_helpmergepublication 
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P '
            and name = 'sp_helpmergepublication')
    drop procedure sp_helpmergepublication
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 collate SQL_Latin1_General_CP1_CS_AS) = 'internal'
        GOTO SelectPubs
    
    if  NOT EXISTS (select * FROM sysmergepublications pub, sysmergesubscriptions sub
        WHERE pub.name like @publication  
            and UPPER(pub.publisher)=UPPER(@@servername) 
            and pub.publisher_db=db_name() 
            and sub.pubid = pub.pubid  
            and sub.srvid = @our_srvid 
            and sub.db_name = db_name())
        BEGIN
            select @found = 0
            RETURN (0) 
        END
    else
        BEGIN
            select @found = 1
            select @publication_id = pubid FROM sysmergepublications 
                WHERE name = @publication and UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name()
            if exists (select * from sysmergesubscriptions where pubid<>subid and pubid in 
                (select pubid from sysmergepublications where name like @publication and UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name()))
                select @has_subscription = 1
            if @no_row <> 0
                RETURN(0)
        END

SelectPubs:

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

    /* This is valid at all sites - used for decentralized conflicts */
    IF LOWER(@reserved collate SQL_Latin1_General_CP1_CS_AS) = 'internal'
        begin
            INSERT into #tab1(name, description, status, retention, sync_mode, 
                    allow_push, allow_pull, allow_anonymous, centralized_conflicts, 
                    priority, snapshot_ready, publication_type, pubid, snapshot_jobid, 
                    enabled_for_internet, 
					dynamic_filters, snapshot_in_defaultfolder, alt_snapshot_folder, pre_snapshot_script,
                    post_snapshot_script, compress_snapshot, ftp_address, ftp_port, 
                    ftp_subdirectory, ftp_login, conflict_retention, 
                    keep_partition_changes, allow_subscription_copy, allow_synctoalternate, 
                    validate_subscriber_info,backward_comp_level, publish_to_activedirectory, max_concurrent_merge, max_concurrent_dynamic_snapshots)
            select  pubs.name, pubs.description, pubs.status, pubs.retention, pubs.sync_mode, 
                    pubs.allow_push, pubs.allow_pull, pubs.allow_anonymous, pubs.centralized_conflicts, 
                    subs.priority, pubs.snapshot_ready, pubs.publication_type, pubs.pubid, replinfo.snapshot_jobid,
                    pubs.enabled_for_internet, 
					pubs.dynamic_filters,
                    pubs.snapshot_in_defaultfolder,
                    pubs.alt_snapshot_folder,
                    pubs.pre_snapshot_script,
                    pubs.post_snapshot_script,
                    pubs.compress_snapshot,
                    pubs.ftp_address,
                    pubs.ftp_port,
                    pubs.ftp_subdirectory,
                    pubs.ftp_login,
                    pubs.conflict_retention,
                    pubs.keep_before_values,
                    pubs.allow_subscription_copy,
                    pubs.allow_synctoalternate,
                    pubs.validate_subscriber_info,
                    pubs.backward_comp_level,
                    case when pubs.ad_guidname is NULL then 0 else 1 end,
                    pubs.max_concurrent_merge,
                    pubs.max_concurrent_dynamic_snapshots
            FROM    sysmergesubscriptions   subs,
                    sysmergepublications    pubs,
                    MSmerge_replinfo        replinfo
                    WHERE pubs.name LIKE @publication
                        AND UPPER(pubs.publisher)=UPPER(@@servername) 
                        AND pubs.publisher_db=db_name()
                        AND subs.subid = pubs.pubid
                        AND replinfo.repid = pubs.pubid
                        AND subs.subscriber_type = 1
            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


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


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

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

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

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

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

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

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

    if LOWER(@publication) = 'all'
        BEGIN

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

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

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

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

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


    begin tran
    save TRANSACTION dropmergepublication

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

grant execute on dbo.sp_dropmergepublication to public
go

--------------------------------------------------------------------------------
--. sp_mergearticlecolumn 
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P '
            and name = 'sp_mergearticlecolumn')
    drop procedure sp_mergearticlecolumn
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 @v_foreign_key int
	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_foreign_key = 3			-- status in sysconstraints
	select @v_unique_constraint 	 = 4096 --status in sysindexes

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

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

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

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

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

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

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

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

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

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

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

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

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

    begin tran
    save TRANSACTION articlecolumn

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

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

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

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

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

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

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

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

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

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

	/*
    ** if snapshot is ready, change it to obsolete to force another snapshot run.	
    ** Note this is the third value of snapshot_ready. 0 for not ready, 1 for OK, 2 for obsolete
    */   
    IF EXISTS (SELECT * FROM sysmergepublications WHERE pubid=@pubid and snapshot_ready>0) 
    		and LOWER(@schema_replication collate SQL_Latin1_General_CP1_CS_AS)='false'
        BEGIN
	        update sysmergearticles set status=1, conflict_table=NULL where pubid=@pubid and artid=@artid and status<>5 and status <>6
	        if @@ERROR<>0
	        	goto FAILURE
		/*
		** Force a re-generation of conflict table and its ins_proc
		*/
	        if object_id(@ins_conflict_proc) is not NULL
	        	begin
	        		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


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

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

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

	set nocount on
	/* 
    ** Security Check.
    ** We use login_name stored in syssubscriptions to manage security 
    */
	exec @retcode = dbo.sp_MSreplcheck_publish
    if @@ERROR <> 0 or @retcode <> 0
        return(1)
        
	select @publisher=@@servername
	select @publisher_db = db_name()
	
    select @pubid=pubid from sysmergepublications 
    	where name=@publication and UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name()

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

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

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

GO

exec dbo.sp_MS_marksystemobject sp_MSreinitmergepublication
go  

grant execute on dbo.sp_MSreinitmergepublication to public
go


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

GO

exec dbo.sp_MS_marksystemobject sp_reinitmergesubscription
go  

grant execute on dbo.sp_reinitmergesubscription to public
go


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

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

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

    SET NOCOUNT ON

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

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

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


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

    /*
    ** Security Check.
    */

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

	
    /* 
    ** Assign priority appropriately - choose 0.99 times the minimum priority
    ** of the global replicas.
    */
    if (@subscription_priority >= 100.0 or @subscription_priority < 0.0)
        BEGIN
            RAISERROR (20088, 16, -1)
            RETURN (1)
        END
    if (@subscription_priority IS NULL)
        begin
            select @priority = 0.99 * min(priority) from sysmergesubscriptions where subscriber_type = 1
            if (@priority IS NOT NULL)
                select @subscription_priority = @priority
            if (@subscription_priority IS NULL) 
                select @subscription_priority = 0.0
        end
    /*
    ** For local and anonymous subscriptions the priority is 0.0
    */
    if LOWER(@subscriber_type collate SQL_Latin1_General_CP1_CS_AS) IN ('local', 'anonymous')
        select @subscription_priority = 0.0
    
    /*
    ** Validate that the subscriber is a valid server
    */
    select @subscriber_srvid = srvid from master..sysservers where UPPER(srvname) = UPPER(@subscriber) collate database_default
    IF @subscriber_srvid IS NULL
        BEGIN
            RAISERROR (14010, 16, -1)
            RETURN (1)
        END

    IF exists (select * from sysobjects where name= 'syssubscriptions')
    begin
        if exists (select name from sysmergeextendedarticlesview where pubid=@pubid and objid in
            (select objid from sysextendedarticlesview where artid in
                (select artid from syssubscriptions where dest_db=@subscriber_db and srvid=@subscriber_srvid)))
        begin
            RAISERROR(21280, 16, -1, @publication, @subscriber_db)
            RETURN (1)
        end
    end


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

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


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


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


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

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

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

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

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

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

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

            END                                    
        END     
    COMMIT TRAN
    return (0)

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

go
exec dbo.sp_MS_marksystemobject sp_addmergesubscription 
go

grant execute on dbo.sp_addmergesubscription to public
go

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

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

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

    SET NOCOUNT ON

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

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

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


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

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

    IF @property IS NULL
        BEGIN
