create table #indcoltab ( colname sysname collate database_default )
select @indkey = 1;
while (@indkey <= 16)
begin
	select @ind_col_name = index_col(@source_table, @indid, @indkey)
	if (@ind_col_name is not NULL) 
		insert into #indcoltab(colname) values(@ind_col_name)
	else
		select @indkey = 16

	select @indkey = @indkey + 1
end

-- build the lists
select @argtabempty = 1
	,@valtabempty = 1
	,@paramtabempty = 1
	,@seltabempty = 1
	,@sel2tabempty = 1
	,@decltabempty = 1
	,@assigntabempty = 1
	,@where_clausetabempty = 1
	,@compinsertabempty = 1

create table #argtab ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
create table #valtab ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
create table #paramtab ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
create table #seltab ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
create table #sel2tab ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
create table #decltab ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
create table #assigntab ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
create table #where_clausetab ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
create table #compinstab ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)

-- some predefined declares and assignments
select @cmd = N'
	declare @reinit_code int, @subwins_code int, @pubwins_code int, @qcfttabrowid uniqueidentifier
			,@retcode smallint, @compcmd nvarchar(4000), @centralized_conflicts bit'
insert into #decltab(procedure_text) values(@cmd)
select @decltabempty = 0
	
select @cmd = N'
	select @reinit_code = 3
			,@subwins_code = 2
			,@pubwins_code = 1
			,@qcfttabrowid = NEWID()'
insert into #assigntab(procedure_text) values(@cmd)
select @cmd = N'
	select @centralized_conflicts = centralized_conflicts
	from dbo.syspublications where pubid = ' + cast(@pubid as nvarchar)
insert into #assigntab(procedure_text) values(@cmd)
select @assigntabempty = 0
	
declare #argcursor cursor local FAST_FORWARD FOR 
		select colid
		from syscolumns
		where iscomputed = 0 and id=@conflict_tableid 
		order by colid
FOR READ ONLY

select @retain_varname = 0
open #argcursor
fetch #argcursor into @colid
while (@@FETCH_STATUS = 0)
begin
	--
	-- Get the column name and column type
	--
	exec dbo.sp_MSget_type @conflict_tableid, @colid, @colname output, @coltype OUTPUT
	if (@@ERROR<>0 or @retcode<>0)
		return (1)

	--
	-- skip this specific column or if type is not returned
	--
	if ((@coltype IS NULL) or (LOWER(@colname) = 'qcfttabrowid'))
	begin
		-- do the next fetch and continue
		fetch #argcursor into @colid
		continue	
	end
		
	exec dbo.sp_MSget_colinfo @conflict_tableid, @colid, NULL, 0, NULL, @ccoltype output
	if (@@ERROR<>0 or @retcode<>0)
		return (1)

	--
	-- parameterize the vars that are the column values of the source
	-- table. For the columns that are specific to the conflict table
	-- retain specific names for the vars
	--
	if (LOWER(@colname collate SQL_Latin1_General_CP1_CS_AS) = 'origin_datasource')
		select @retain_varname = @colid

	if (@retain_varname = 0)
		select @argterm = N'@param' + cast(@colid as nvarchar) 
	else
		select @argterm = N'@' + @colname
		
	select @valterm = quotename(@colname)
	select @paramterm = @argterm
	select @updterm = @valterm + N' = ' + @argterm

	if (@retain_varname = 0)
	begin
		select @selterm = @paramterm + N' = ' + @valterm
		select @sel2term = @paramterm + N' = case when ' + @paramterm + 
					N' is NULL then ' + @valterm + N' else ' + @paramterm + N' end'
	end
	else
	begin
		select @selterm = NULL
		select @sel2term = NULL
	end
	 
	select @argterm = @argterm + N' ' + @coltype

	-- Check if this is part of primary key	/ unique index
	if (@colname in ( select colname from #indcoltab ) )
	begin
		-- this key assignment becomes part of where clause
		select @where_term = @updterm
		select @updterm = NULL
		select @selterm = NULL
		select @sel2term = NULL
	end
	else
		select @where_term = NULL

	-- special columns - process them as local var
	if (LOWER(@colname collate SQL_Latin1_General_CP1_CS_AS) = 'insertdate' )
	begin
		select @declterm = N'
	declare ' + @argterm
		select @assignterm = N'
	select ' + @paramterm + N' = GETDATE()'
		select @argterm = NULL
	end
	else if (LOWER(@colname collate SQL_Latin1_General_CP1_CS_AS) = 'pubid' )
	begin
		select @declterm = N'
	declare ' + @argterm
		select @assignterm = N'
	select ' + @paramterm + N' = ' + cast(@pubid as nvarchar)
		select @argterm = NULL
	end
	else
	begin
		select @declterm = NULL
		select @assignterm = NULL
	end

	-- build the term for compensating insert
	if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'varchar')
		select @compinsterm = N' '''''' + master.dbo.fn_MSgensqescstr(' + @valterm + N') collate database_default + '''''''' '
	else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'nvarchar')
		select @compinsterm = N' N'''''' + master.dbo.fn_MSgensqescstr(' + @valterm + N') collate database_default + '''''''' '
	else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'char')
		select @compinsterm = N' '''''' + master.dbo.fn_MSgensqescstr(CAST(RTRIM(' + @valterm + N') as nvarchar(4000))) collate database_default + '''''''' '
	else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'nchar')
		select @compinsterm = N' N'''''' + master.dbo.fn_MSgensqescstr(CAST(RTRIM(' + @valterm + N') as nvarchar(4000))) collate database_default + '''''''' '
	else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('binary','varbinary'))
		select @compinsterm = N' '' + master.dbo.fn_varbintohexstr(' + @valterm + N') collate database_default ' 
	else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('bit','bigint','int','smallint','tinyint','float','real','decimal','numeric'))
		select @compinsterm = N' '' + CAST(' + @valterm + N' as nvarchar) '
	else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('money','smallmoney'))
		select @compinsterm = N' '' + CONVERT(nvarchar(40),' + @valterm + N', 2) '
	else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'uniqueidentifier')
		select @compinsterm = N' '''''' + CAST(' + @valterm + N' as nvarchar(40)) + '''''''' '
	else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('datetime','smalldatetime'))
		select @compinsterm = N' '''''' + CONVERT(nvarchar(40), ' + @valterm + N', 121) + '''''''' '
	else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'sql_variant')
		select @compinsterm = N' '' + master.dbo.fn_sqlvarbasetostr(' + @valterm + N' ) collate database_default '
	else
		select @compinsterm = N' '' + CAST(' + @valterm + N' as nvarchar) '
	
	-- Now append to the various lists
	if (@argterm is NOT NULL)
	begin		
		if (@argtabempty = 1)
		begin
			select @argtabempty = 0
			select @cmd = N'
	' + @argterm
		end
		else
			select @cmd = N',
	' + @argterm
		insert into #argtab(procedure_text) values(@cmd)
	end
	if (@valterm is NOT NULL)
	begin
		if (@valtabempty = 1)
		begin
			select @valtabempty = 0
			select @cmd = @valterm
		end
		else
			select @cmd = N', ' + @valterm
		insert into #valtab(procedure_text) values(@cmd)
	end
	if (@paramterm is NOT NULL)
	begin
		if (@paramtabempty = 1)
		begin
			select @paramtabempty = 0
			select @cmd = @paramterm
		end
		else
			select @cmd = N', ' + @paramterm
		insert into #paramtab(procedure_text) values(@cmd)
	end
	if (@selterm is NOT NULL)
	begin
		if (@seltabempty = 1)
		begin
			select @seltabempty = 0
			select @cmd = N'
		' + @selterm
		end
		else
			select @cmd = N'
		,' + @selterm		
		insert into #seltab(procedure_text) values(@cmd)
	end
	if (@sel2term is NOT NULL)
	begin
		if (@sel2tabempty = 1)
		begin
			select @sel2tabempty = 0
			select @cmd = N'
		' + @sel2term
		end
		else
			select @cmd = N'
		,' + @sel2term		
		insert into #sel2tab(procedure_text) values(@cmd)
	end	
	if (@where_term is NOT NULL)
	begin
		if (@where_clausetabempty = 1)
		begin
			select @where_clausetabempty = 0
			select @cmd = @where_term
		end
		else
			select @cmd = N' AND 
			' + @where_term
		insert into #where_clausetab(procedure_text) values(@cmd)
	end
	if (@declterm is NOT NULL)
	begin
		select @cmd = @declterm + N'
	'
		insert into #decltab(procedure_text) values(@cmd)
	end		

	if (@assignterm is NOT NULL)
	begin
		select @cmd = @assignterm + N'
	'
		insert into #assigntab(procedure_text) values(@cmd)
	end		

	if (@compinsterm is NOT NULL)
	begin
		if (@compinsertabempty = 1)
		begin
			select @compinsertabempty = 0
			select @cmd = N' + ISNULL(''' + @compinsterm + N', ''null'')'
		end
		else
			select @cmd = N' + ISNULL('',' + @compinsterm + N', ''null'')'
		insert into #compinstab(procedure_text) values(@cmd)
	end
	
	-- do the next fetch
	fetch #argcursor into @colid

end
close #argcursor
deallocate #argcursor
drop table #indcoltab

--
-- generation phase
--
BEGIN TRAN sp_MSmaketrancftproc

-- create temp table to select the command text out of
if exists (select * from sysobjects where name = 'tempcmd' and uid = user_id('dbo'))
		drop table dbo.tempcmd
create table dbo.tempcmd ( c1 int identity NOT NULL, cmdtext nvarchar(4000) NULL)

-- create header
insert into  dbo.tempcmd(cmdtext) 
values(N'create procedure '+QUOTENAME(@owner)+ N'.'+ QUOTENAME(@procname) + N'( 
')

-- insert the arglist
insert into dbo.tempcmd(cmdtext) select procedure_text from #argtab order by c1 
insert into dbo.tempcmd(cmdtext) values(N' ,@subcriber sysname = NULL, @subdb sysname = NULL )
as
begin
')

-- insert the declare list
insert into dbo.tempcmd(cmdtext) select procedure_text from #decltab order by c1 
insert into dbo.tempcmd(cmdtext) values(N'
')

-- insert the assignment list (for declared vars)
insert into dbo.tempcmd(cmdtext) select procedure_text from #assigntab order by c1

-- do the select for the case where we need to retain values of publisher
insert into dbo.tempcmd(cmdtext) values(N'
	if (@reason_code = @subwins_code)
	begin
		select ')
insert into dbo.tempcmd(cmdtext) select procedure_text from #seltab order by c1
insert into dbo.tempcmd(cmdtext) values(N'
		from ' + @source_table + N' where ')
insert into dbo.tempcmd(cmdtext) select procedure_text from #where_clausetab order by c1
insert into dbo.tempcmd(cmdtext) values(N'
	end')

insert into dbo.tempcmd(cmdtext) values(N'
	else
	begin
		select ')
	
insert into dbo.tempcmd(cmdtext) select procedure_text from #sel2tab order by c1
insert into dbo.tempcmd(cmdtext) values(N'
		from ' + @source_table + N' where ')
insert into dbo.tempcmd(cmdtext) select procedure_text from #where_clausetab order by c1
insert into dbo.tempcmd(cmdtext) values(N'
	end
')

--
-- insert the conflict row in the publisher cft table
--
insert into dbo.tempcmd(cmdtext) values(N'
	insert into ' + @qualname + N'(')
insert into dbo.tempcmd(cmdtext) select procedure_text from #valtab order by c1
insert into dbo.tempcmd(cmdtext) values(N',[qcfttabrowid]) 
	values (')
insert into dbo.tempcmd(cmdtext) select procedure_text from #paramtab order by c1
insert into dbo.tempcmd(cmdtext) values(N',@qcfttabrowid)
')

--
-- generate compensating command decentralized logging
-- depending on the number of columns, we will split the compensating
-- command into several compensating commands
--
select @rowcnt = 0, @compinsertabempty = 1
select @cmd = N'
	if (@centralized_conflicts = 0)
	begin
		select @compcmd = N''insert into ' + master.dbo.fn_MSgensqescstr(@qualname) collate database_default + N' ( '
insert into dbo.tempcmd(cmdtext) values(@cmd)

declare #htempcur cursor local for
	select master.dbo.fn_MSgensqescstr(procedure_text) from #valtab order by c1
for read only

open #htempcur
fetch #htempcur into @compinsterm
while (@@fetch_status = 0)
begin
	insert into dbo.tempcmd(cmdtext) select @compinsterm
	select @rowcnt = @rowcnt + 1

	--
	-- if we have processed 10 terms then split the command
	--
	if (@rowcnt > 9)
	begin
		select @cmd = N'''
		from ' + @qualname + N' where qcfttabrowid = @qcfttabrowid and tranid = @tranid' 
		insert into dbo.tempcmd(cmdtext) values(@cmd)		
	
		select @cmd = N'
		exec @retcode = dbo.sp_MSadd_compensating_cmd @subcriber, @subdb, @compcmd, ' 
			+ CAST(@artid as nvarchar(10)) + N', ' + CAST(@pubid as nvarchar(10)) + N',1,0,'
			+ CAST(@compinsertabempty as nvarchar(4)) + N'
		if (@@error != 0 or @retcode != 0)
			return 1 
		
		select @compcmd = N''' 
		insert into dbo.tempcmd(cmdtext) values(@cmd)
		select @rowcnt = 0, @compinsertabempty = 0
	end
	fetch #htempcur into @compinsterm
end

close #htempcur
deallocate #htempcur

insert into dbo.tempcmd(cmdtext) values(N', [qcfttabrowid] ) values ('' ')
select @rowcnt = @rowcnt + 1

declare #htempcur cursor local for
	select procedure_text from #compinstab order by c1
for read only

open #htempcur
fetch #htempcur into @compinsterm
while (@@fetch_status = 0)
begin
	insert into dbo.tempcmd(cmdtext) select @compinsterm
	select @rowcnt = @rowcnt + 1

	--
	-- if we have processed 10 terms then split the command
	--
	if (@rowcnt > 9)
	begin
		select @cmd = N'
		from ' + @qualname + N' where qcfttabrowid = @qcfttabrowid and tranid = @tranid' 
		insert into dbo.tempcmd(cmdtext) values(@cmd)		
	
		select @cmd = N'
		exec @retcode = dbo.sp_MSadd_compensating_cmd @subcriber, @subdb, @compcmd, ' 
			+ CAST(@artid as nvarchar(10)) + N', ' + CAST(@pubid as nvarchar(10)) + N',1,0,'
			+ CAST(@compinsertabempty as nvarchar(4)) + N'
		if (@@error != 0 or @retcode != 0)
			return 1 
		
		select @compcmd = N'' ''' 
		insert into dbo.tempcmd(cmdtext) values(@cmd)
		select @rowcnt = 0, @compinsertabempty = 0
	end
	fetch #htempcur into @compinsterm
end

close #htempcur
deallocate #htempcur

--
-- script the remaining compensating command
--
select @cmd = N' + '', '''''' + CAST([qcfttabrowid] as nvarchar(40)) + '''''''' + N'' ) ''
		from ' + @qualname + N' where qcfttabrowid = @qcfttabrowid and tranid = @tranid' 
insert into dbo.tempcmd(cmdtext) values(@cmd)
select @rowcnt = @rowcnt + 1

select @cmd = N'
		exec @retcode = dbo.sp_MSadd_compensating_cmd @subcriber, @subdb, @compcmd, ' 
			+ CAST(@artid as nvarchar(10)) + N', ' + CAST(@pubid as nvarchar(10)) + N',0,0,'
			+ CAST(@compinsertabempty as nvarchar(4)) + N'
		if (@@error != 0 or @retcode != 0)
			return 1 ' 
insert into dbo.tempcmd(cmdtext) values(@cmd)		
insert into dbo.tempcmd(cmdtext) values(N'
	end
end')

if (@is_debug = 0)
begin
	-- Now we select out the command text pieces in proper order so that our caller,
	-- xp_execresultset will execute the command that creates the stored procedure.
	select @dbname = db_name()
	select @cmd = N'select cmdtext from dbo.tempcmd order by c1'
	exec @retcode = master..xp_execresultset @cmd, @dbname
	if (@@error != 0 or @retcode != 0)
	begin
		-- roll back the tran
		rollback tran sp_MSmaketrancftproc
		return (1)
	end
	
	--
	-- Check if we create the proc and update sysarticleupdates
	--
	select @conflict_proc_id = id from sysobjects where name = @procname and type = 'P '
	if (@conflict_proc_id is NULL or @conflict_proc_id = 0)
	begin
		-- roll back the tran
		rollback tran sp_MSmaketrancftproc
		return (1)
	end
	else
	begin
		update dbo.sysarticleupdates set ins_conflict_proc = @conflict_proc_id
			where artid = @artid and pubid = @pubid
		if @@error <> 0
		begin
			-- roll back the tran
			rollback tran sp_MSmaketrancftproc
			return (1)
		end

		-- mark the proc as system object
		if (@owner in ('dbo','INFORMATION_SCHEMA'))
		begin
			exec @retcode = dbo.sp_MS_marksystemobject @procname
			if (@@error != 0 or @retcode != 0)
			begin
				-- roll back the tran
				rollback tran sp_MSmaketrancftproc
				return (1)
			end
		end
	end
end
else
	select cmdtext from dbo.tempcmd order by c1

COMMIT TRAN 

-- drop the temp tables
drop table dbo.tempcmd
drop table #argtab 
drop table #valtab 
drop table #paramtab 
drop table #seltab 
drop table #sel2tab 
drop table #decltab 
drop table #assigntab 
drop table #where_clausetab 
drop table #compinstab
END
go

exec dbo.sp_MS_marksystemobject sp_MSmaketrancftproc 
go

grant execute on dbo.sp_MSmaketrancftproc to public
go

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

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

CREATE PROCEDURE sp_changesubstatus (
    @publication sysname = '%',    /* publication name */
    @article sysname = '%',        /* article name */
    @subscriber sysname = '%',      /* subscriber name */
    @status sysname,                /* subscription status */
    @previous_status sysname=NULL,  /* previous subscription status */
    @destination_db sysname = '%',   /* destination database name */

    @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,
    @distribution_jobid binary(16) = NULL OUTPUT,
    @from_auto_sync bit = 0,
    @ignore_distributor bit = 0,
    -- Agent offload
    @offloadagent bit = 0,
    @offloadserver sysname = NULL,
	@dts_package_name sysname = NULL,
	@dts_package_password nvarchar(524) = NULL,
	@dts_package_location int = 0,
    @schemastabilityonly int = 0,
	@distribution_job_name sysname = NULL

) AS

    SET NOCOUNT ON
    DECLARE @inactive tinyint
    DECLARE @subscribed tinyint
    DECLARE @active tinyint
	DECLARE @initiated tinyint
    DECLARE @public tinyint
    DECLARE @replicate_bit smallint
    DECLARE @msg nvarchar(255)
    DECLARE @prevstatid tinyint
    DECLARE @artid int
    DECLARE @tabid int
    DECLARE @srvid smallint
    DECLARE @statusid tinyint
    DECLARE @distributor sysname
    DECLARE @distribdb sysname
    DECLARE @distproc nvarchar (255)
    DECLARE @pub_db sysname
    DECLARE @dest_db sysname
    DECLARE @sub_name sysname
    DECLARE @sub_status tinyint
    DECLARE @sub_ts varbinary (16)
    DECLARE @non_sql_flag bit
    DECLARE @qcmd nvarchar (4000)
    DECLARE @cmd1 nvarchar (255)
    DECLARE @cmd2 nvarchar (255)
    DECLARE @cmd3 nvarchar (255)
    DECLARE @retcode int
    DECLARE @repl_freq tinyint
    DECLARE @art_type tinyint
    DECLARE @proccmd  nvarchar(255)
    DECLARE @procnum  smallint
    DECLARE @finished_real bit
    DECLARE @finished_virtual bit
    DECLARE @virtual_id smallint
    DECLARE @immediate_sync bit
    DECLARE @enabled_for_internet bit
    DECLARE @allow_anonymous bit
    DECLARE @subscription_type int
    DECLARE @xact_seqno binary(10)
    DECLARE @sync_type tinyint
    DECLARE @automatic tinyint
    DECLARE @bcp_char tinyint	
    DECLARE @concurrent_char tinyint	

    DECLARE @art_change bit
    declare @login_name sysname

	DECLARE @pubid int
	DECLARE @syncinit_lsn binary(10)

	DECLARE @f_syncstat_posted bit

    -- synctran
    DECLARE @update_mode tinyint
    DECLARE @art_name sysname
    declare @synctran tinyint
    declare @no_distproc bit
    declare @loopback_detection bit
	
    /*
    ** Initializations.
    */
    select @synctran = 1
			
    SELECT @automatic = 1
    SELECT @inactive = 0        /* Const: subscription status 'inactive' */
    SELECT @subscribed = 1      /* Const: subscription status 'subscribed' */
    SELECT @active = 2          /* Const: subscription status 'active' */
	SELECT @initiated = 3        /* Const: subscription status 'initiated' */
    SELECT @public = 0          /* Const: publication status 'public' */
    SELECT @pub_db = DB_NAME()
    SELECT @virtual_id = -1
    SELECT @art_change = 0
    select @bcp_char = 1
    select @concurrent_char = 4	

	SELECT @f_syncstat_posted = 0

    SELECT @replicate_bit = 2

    /* 
    ** Security Check.
    ** We use login_name stored in syssubscriptions to manage security 
    */

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

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

    IF @publication <> '%'
        BEGIN
            EXECUTE @retcode = dbo.sp_validname @publication
            IF @@ERROR <> 0 OR @retcode <> 0
            RETURN (1)
        END

    IF NOT EXISTS (SELECT * FROM syspublications WHERE name LIKE @publication)
        BEGIN
        IF @publication = '%'
                RAISERROR (14008, 11, -1)
        ELSE
                RAISERROR (20026, 11, -1, @publication)
        RETURN (1)
        END

    /*
    ** Parameter Check:  @article
    ** Check to make sure that the article exists, that it's not null,
    ** and that it conforms to the rules for identifiers.
    */

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

    /*
    IF @article <> '%'
        BEGIN
            EXECUTE @retcode = dbo.sp_validname @article
            IF @@ERROR <> 0 OR @retcode <> 0
            RETURN (1)
        END
    */

    IF NOT EXISTS (SELECT *
                     FROM sysextendedarticlesview a,
                          syspublications b
                WHERE a.name LIKE @article
                      AND a.pubid = b.pubid
                      AND b.name LIKE @publication)

        BEGIN
        IF @article = '%'
                RAISERROR (14009, 11, -1, @publication)
        ELSE
                RAISERROR (20027, 11, -1, @article)
        RETURN (1)
        END

    /*
    ** Parameter Check:  @subscriber
    ** Check to make sure that the subscriber exists, that it is not NULL,
    ** and that it conforms to the rules for identifiers.
    ** Null subscriber represents virtual subscriptions
    */

    IF @subscriber IS NOT NULL AND @subscriber <> '%'
    BEGIN    
        EXECUTE @retcode = dbo.sp_validname @subscriber
        IF @@ERROR <> 0 OR @retcode <> 0
        RETURN (1)

        IF NOT EXISTS (SELECT *
                         FROM master..sysservers
                        WHERE UPPER(srvname) = UPPER(@subscriber) collate database_default
                          AND (srvstatus & 4) <> 0)

            BEGIN
                RAISERROR (14063, 11, -1)
                RETURN (1)
            END
    END

    /*
    ** Parameter Check: @status.
    ** Set the @statusid according to the @status value.  Values can be
    ** any of the following:
    **
    **      status      statusid
    **      =========   ========
    **      inactive           0
    **      subscribed         1
    **      active             2
	**		initiated          3
    */

    IF LOWER(@status collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('active', 'subscribed', 'inactive', 'initiated')
        BEGIN
            RAISERROR (14065, 16, -1)
        RETURN (1)
        END

	IF LOWER(@status collate SQL_Latin1_General_CP1_CS_AS) IN ('initiated')
		SELECT @statusid = @initiated
    ELSE IF LOWER(@status collate SQL_Latin1_General_CP1_CS_AS) IN ('active')
        SELECT @statusid = @active
    ELSE IF LOWER(@status collate SQL_Latin1_General_CP1_CS_AS) IN ('subscribed')
        SELECT @statusid = @subscribed
    ELSE
        SELECT @statusid = @inactive

    /*
    ** Parameter Check: @previous_status.
    ** Set the @prevstatid according to the @previous_status value.
    ** Values can be any of the following:
    **
    **      previous_status      prevstatid
    **      ===============      ==========
    **      inactive                      0
    **      subscribed                    1
    **      active                        2
	**		initiated					  3
    */

    IF @previous_status IS NOT NULL
    BEGIN
        IF LOWER(@previous_status collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('initiated','active', 'subscribed', 'inactive')
        BEGIN
            RAISERROR (14066, 16, -1)
            RETURN (1)
        END

        IF LOWER(@status collate SQL_Latin1_General_CP1_CS_AS) = LOWER(@previous_status collate SQL_Latin1_General_CP1_CS_AS)
        BEGIN
            RAISERROR (14067, 16, -1)
            RETURN (1)
        END

		IF LOWER(@previous_status collate SQL_Latin1_General_CP1_CS_AS) IN ('initiated')
			SELECT @prevstatid = @initiated
        ELSE IF LOWER(@previous_status collate SQL_Latin1_General_CP1_CS_AS) IN ('active')
            SELECT @prevstatid = @active
        ELSE IF LOWER(@previous_status collate SQL_Latin1_General_CP1_CS_AS) IN ('subscribed')
            SELECT @prevstatid = @subscribed
        ELSE
           SELECT @prevstatid = @inactive
    END

    /*
    ** Parameter Check: @destination_db.
    ** Set @destination_db to current database if not specified.  Make
    ** sure that the @destination_db conforms to the rules for identifiers.
    */

    IF @destination_db <> '%' 
    BEGIN
		EXECUTE @retcode = dbo.sp_validname @destination_db
		IF @retcode <> 0
		RETURN (1)
    END

    /*
    ** Get distribution server information for remote RPC
    ** subscription calls.
    ** if @ignore_distributor = 1, we are in bruteforce cleanup mode, don't do RPC.
    */
    if @ignore_distributor = 1 
        select @no_distproc = 1
    else
        select @no_distproc = 0

    IF @no_distproc = 0 --and @from_auto_sync = 0 
    BEGIN
        EXEC @retcode = dbo.sp_helpdistributor @rpcsrvname = @distributor OUTPUT,
                                           @distribdb = @distribdb OUTPUT

        IF @@ERROR <> 0
        BEGIN
            RAISERROR (14071, 16, -1)
            RETURN (1)
        END

        IF @retcode <> 0 OR @distribdb IS NULL OR @distributor IS NULL
        BEGIN
            RAISERROR (14071, 16, -1)
            RETURN (1)
        END
    END


    create table #sysextendedarticlesview
    (
    artid               int                 NULL,
    columns             varbinary(32)       NULL,
    creation_script     nvarchar(255)       collate database_default null,
    del_cmd             nvarchar(255)       collate database_default null,
    description         nvarchar(255)       collate database_default null,
    dest_table          sysname             collate database_default null,
    filter              int                 NULL,
    filter_clause       ntext               NULL,
    ins_cmd             nvarchar(255)       collate database_default null,
    name                sysname             collate database_default null,
    objid               int                 NULL,
    pubid               int                 NULL,
    pre_creation_cmd    tinyint             NULL,
    status              tinyint             NULL,
    sync_objid          int                 NULL,
    type                tinyint             NULL,
    upd_cmd             nvarchar(255)       collate database_default null,
    schema_option       binary(8)           NULL,
    dest_owner          sysname             collate database_default null
    )

    insert into #sysextendedarticlesview select * from sysextendedarticlesview

    begin tran
    save TRANSACTION changesubstatus

        SELECT @finished_virtual = 0
        SELECT @finished_real = 0

        /* 
        ** If @subscriber is null, don't process real subscriptions
        ** If @subscriber is not null and '%', don't process virtual subscriptions
        */
        IF @subscriber IS NULL SELECT @finished_real = 1
        ELSE IF @subscriber <> '%'  SELECT @finished_virtual = 1

        WHILE (@finished_real = 0 OR @finished_virtual = 0)
        BEGIN
            /*
            ** Declare cursor containing subscriptions to be updated.
            */
            IF @finished_real = 0
            BEGIN
                IF @previous_status IS NOT NULL
                BEGIN
                    DECLARE hCsubstatus CURSOR LOCAL SCROLL_LOCKS FOR
                        SELECT sub.artid,
                               art.objid,
                               sub.srvid,
                               ss.srvname,
                               sub.dest_db,
                               sub.status,
                           case when ss.srvproduct = 'MSREPL-NONSQL' or
								pub.allow_dts = 1
								then 1
                           else 0 end,
                           pub.repl_freq,
                               art.type,
                           pub.immediate_sync,
                           pub.enabled_for_internet,
                           pub.allow_anonymous,
                           sub.subscription_type,
                           sub.sync_type,
                           sub.update_mode,
                           art.name,
                           sub.login_name,
                           sub.loopback_detection,
						   pub.pubid
                          FROM syssubscriptions sub,
                               #sysextendedarticlesview art,
                               syspublications pub,
                               master..sysservers ss
                         WHERE pub.name LIKE @publication collate database_default
                           AND art.name LIKE @article collate database_default
                           AND ((@subscriber = N'%') OR (UPPER(ss.srvname) = UPPER(@subscriber) collate database_default))
                           AND sub.srvid = ss.srvid
                           AND sub.artid = art.artid
                           AND art.pubid = pub.pubid
                           AND sub.status = @prevstatid
                           AND ((@destination_db = N'%') OR (sub.dest_db = @destination_db collate database_default))
                END
                ELSE
                BEGIN
                    DECLARE hCsubstatus CURSOR LOCAL SCROLL_LOCKS FOR
                        SELECT sub.artid,
                               art.objid,
                               sub.srvid,
                               ss.srvname,
                               sub.dest_db,
                               sub.status,
                           case when ss.srvproduct = 'MSREPL-NONSQL' or
								pub.allow_dts = 1
								then 1
                           else 0 end,
                           pub.repl_freq,
                               art.type,
                           pub.immediate_sync,
                           pub.enabled_for_internet,
                           pub.allow_anonymous,
                           sub.subscription_type,
                           sub.sync_type,
                           sub.update_mode,
                           art.name,
                           sub.login_name,
                           sub.loopback_detection,
						   pub.pubid
                          FROM syssubscriptions sub,
                               #sysextendedarticlesview art,
                               syspublications pub,
                               master..sysservers ss
                         WHERE pub.name LIKE @publication collate database_default
                           AND art.name LIKE @article collate database_default
                           AND ((@subscriber = N'%') OR (UPPER(ss.srvname) = UPPER(@subscriber) collate database_default))
                           AND sub.srvid = ss.srvid
                           AND sub.artid = art.artid
                           AND art.pubid = pub.pubid
                           AND ((@destination_db = N'%') OR (sub.dest_db = @destination_db))
                END		   
                SELECT @finished_real = 1
            END

            ELSE IF @finished_virtual = 0  
            BEGIN
                DECLARE @sub_bit smallint
                DECLARE @null_name sysname

                SELECT @sub_bit = 4
                SELECT @null_name = NULL

                /*
                ** Treat anonymous virtual subscription as DSN subscriber.
                ** This will cause sp_MSarticlecol being called in sp_changesubstatus
                */
                DECLARE hCsubstatus CURSOR LOCAL SCROLL_LOCKS FOR
                    SELECT sub.artid,
                           art.objid,
                           sub.srvid,
                           @null_name,              /* subscriber name. NULL for virtual */
                           sub.dest_db,
                           sub.status,
                      case when (pub.allow_anonymous = 1 and (pub.sync_method = @bcp_char or pub.sync_method = @concurrent_char)) or
							pub.allow_dts = 1 then 1
                       else 0 end, /*indicate dsn or not */ 
                       pub.repl_freq,
                       art.type,
                       pub.immediate_sync,
                       pub.enabled_for_internet,
                       pub.allow_anonymous,
                       sub.subscription_type,
                       sub.sync_type,
                       sub.update_mode,
                       art.name,
                       login_name,
                       sub.loopback_detection,
					   pub.pubid

                      FROM syssubscriptions sub,
                           #sysextendedarticlesview art,
                           syspublications pub
                     WHERE pub.name LIKE @publication
                       AND art.name LIKE @article
                       AND sub.srvid = -1
                       AND sub.artid = art.artid
                       AND art.pubid = pub.pubid
                SELECT @finished_virtual = 1
            END

            OPEN hCsubstatus
            FETCH hCsubstatus INTO @artid, @tabid, @srvid, @sub_name, @dest_db,
                @sub_status, @non_sql_flag, @repl_freq, @art_type,
                @immediate_sync, @enabled_for_internet,
                @allow_anonymous, @subscription_type, @sync_type, @update_mode,
                @art_name, @login_name, @loopback_detection,@pubid


            WHILE (@@fetch_status <> -1)
            BEGIN

                IF  suser_sname(suser_sid()) <> @login_name AND is_srvrolemember('sysadmin') <> 1  
                    AND is_member ('db_owner') <> 1
                BEGIN
                        RAISERROR (14126, 11, -1)
                        RETURN (1)
                END
                /*
				** condition 1:
                ** If current status is same as new status, and status is not 'initiated' do nothing.
				** If both old and new status = 'initiated', this indicates that the 
				** snapshot agent previously bombed out between the initiation and activation stages and
				** is now again trying to sync the publication.
				**
				** condition 2:
                ** @auto_sync_only is used by snapshot for immediate_sync
                ** publications.
				**
				** condition 3:
				** Because sp_MSactivate_auto_sub (and thus the snapshot agent)
				** calls this procedure for all subscriptions, we need to ignore
				** the real subscriptions that are already active so that they won't be
				** transitioned to the initiated state.  If we don't do this, those
				** subscriptions will be resynced using the new snapshot.
				** 
				** however, we DO want a new snapshot to be generated for virtual
				** subscriptions to active publications.
				**
                */
                IF  (@sub_status = @statusid AND @sub_status <> @initiated ) OR
                    (@from_auto_sync = 1 AND @sync_type <> @automatic) OR
					(@sub_status = @active AND @statusid = @initiated AND @srvid <> -1 AND @from_auto_sync = 1)
                BEGIN
                    FETCH hCsubstatus INTO @artid, @tabid, @srvid, @sub_name,
                       @dest_db, @sub_status, @non_sql_flag, @repl_freq, @art_type, 
                       @immediate_sync, @enabled_for_internet,
                       @allow_anonymous, @subscription_type, @sync_type, @update_mode,
                       @art_name, @login_name, @loopback_detection, @pubid

                    CONTINUE
                END

				-- If changing a virtual subscription to 'subscribed' status
				-- change the immediate_sync_ready bit
				if @statusid = @subscribed and @sub_name is NULL
				begin
					UPDATE syspublications SET immediate_sync_ready = 0 WHERE 
						pubid = @pubid and
						immediate_sync_ready <> 0
                    IF @@ERROR <> 0
                    BEGIN
                      CLOSE hCsubstatus
                      DEALLOCATE hCsubstatus
                      if @@trancount > 0
                        begin
                            ROLLBACK TRANSACTION changesubstatus
                            commit tran
                        end
                      RETURN (1)
                    END
				end


				-- acquire schema lock, mark rollback point in order to allow
				-- for 'unflush' of proc cache

                declare @qualified_name nvarchar(512)

                exec dbo.sp_MSget_qualified_name @tabid, @qualified_name output
                exec dbo.sp_replupdateschema @qualified_name, @schemastabilityonly

                /*
                ** Update syssubscription status
                */
                UPDATE syssubscriptions
                       SET status = @statusid
                       FROM syssubscriptions sub,
                           sysextendedarticlesview art,
                           syspublications pub
                       WHERE pub.name LIKE @publication
                            AND art.artid = @artid
                            AND sub.srvid = @srvid
                            AND sub.artid = @artid
                            AND art.pubid = pub.pubid
                            AND sub.dest_db = @dest_db
                if @@ERROR <> 0
                               BEGIN
                               CLOSE hCsubstatus
                               DEALLOCATE hCsubstatus
                                if @@trancount > 0
                                begin
                                    ROLLBACK TRANSACTION changesubstatus
                                    commit tran
                                end
                                RAISERROR (14053, 16, -1)
                                   RETURN (1)
                               END

				--
				-- Subscription reinitialization processing for Immediate 
				-- and Queued publications
				--
				if (@update_mode in (1,2,3,4,5))
				begin
					select @retcode = 0
					IF ((@statusid != @active) AND (@sub_status = @active))
					begin
						--
						-- If we are going from active state to subscribed
						-- set the reinit column so that no more updates from
						-- subscriber are applied until (re)activation
						--
						update dbo.syssubscriptions
						set queued_reinit = 1
						where 
							artid = @artid 
							and srvid = @srvid
							and dest_db = @dest_db
					end
					ELSE IF ((@statusid = @active) AND (@sub_status != @active ))
					begin
						--
						-- If we are going from subscribed state to active state
						--
						if (@update_mode = 1)
						begin
							--
							-- Sync tran case : reset the reinit column
							--
							update dbo.syssubscriptions
							set queued_reinit = 0
							where 
								artid = @artid 
								and srvid = @srvid
								and dest_db = @dest_db
						end

						--
						-- For queued case : we do not need to send compensating
						-- command anymore, sp_addqueued_artinfo will do the 
						-- queue reinitialization for all types of queued
						-- subscriptions
						--

						/**********
						else
						begin
							-- 
							-- Queued case : send a compensating action command to 
							-- subscriber to reinitialize its queue
							--
							select @qcmd = N'exec sp_MSreset_queue ''' + @@servername + '''' + 
								', ''' + db_name() + '''' +
								', ''' + name + '''' +
								', ' + cast(@artid as nvarchar(8))
							from syspublications
							where pubid = @pubid
								  					
							exec @retcode = dbo.sp_MSadd_compensating_cmd
								@orig_srv = @sub_name
								,@orig_db = @dest_db
								,@command = @qcmd
								,@article_id = @artid 
								,@publication_id = @pubid
						end
						**********/
					end					

					--
					-- Check for error
					--
					if (@@error != 0 or @retcode != 0)
					begin
						CLOSE hCsubstatus
						DEALLOCATE hCsubstatus
						if @@trancount > 0
						begin
							ROLLBACK TRANSACTION changesubstatus
							commit tran
						end
						RAISERROR (14053, 16, -1)
						RETURN (1)
					end
				end

                /*
                ** Get timestamp of subscription.
                */
                EXEC @retcode = dbo.sp_replincrementlsn @xact_seqno OUTPUT
                IF @@ERROR <> 0 or @retcode <> 0
                            BEGIN
                               CLOSE hCsubstatus
                               DEALLOCATE hCsubstatus
                               if @@trancount > 0
                                begin
                                    ROLLBACK TRANSACTION changesubstatus
                                    commit tran
                                end
                               RETURN (1)
                            END
                select @sub_ts = @xact_seqno


                IF @sub_ts IS NULL
                            BEGIN
                               CLOSE hCsubstatus
                               DEALLOCATE hCsubstatus
                               if @@trancount > 0
                                begin
                                    ROLLBACK TRANSACTION changesubstatus
                                    commit tran
                                end
                                RAISERROR (14053, 16, -1)
                               RETURN (1)
                            END
				
				-------------------------------------------------------------------
				-- If initiating the subscription, toss a SYNCINIT token into the 
				-- log for the article and return LSN as a results set
				--
				-- Note:  This should come after the subscription LSN is obtained.
				-- in order to assure proper application of SYNSTAT tokens in the
				-- distribution database
				-------------------------------------------------------------------

				IF @statusid = @initiated --and @sub_status <> @initiated
				BEGIN
					-- set filtered status.  Must log old text information during initiated state
					-- in order to support update splitting

					exec sp_MSsetfilteredstatus @tabid

					-- set nonsqlsub status.  must prevent UPDATETEXT operations during
					-- initiated state

                    exec sp_MSarticlecol @artid, NULL,N'nonsqlsub', N'add'
					exec sp_replpostsyncstatus @pubid, @artid, 1, @syncinit_lsn output
					if @f_syncstat_posted = 0
					begin
						select @pubid, @artid, @syncinit_lsn
						select @f_syncstat_posted = 1
					end
				END

				-------------------------------------------------------------------
				-- If changing the state FROM initiated, post a SYNCDONE token to the 
				-- log for the article. 
				-------------------------------------------------------------------
				IF @sub_status = @initiated and @statusid <> @initiated
				BEGIN
					-- reset filtered status to normal value

					exec sp_MSsetfilteredstatus @tabid

					-- clear nonsqlsub status for this article.

                    exec sp_MSarticlecol @artid, NULL,N'nonsqlsub', N'drop'

					--if @f_syncstat_posted = 0
					--begin
						exec sp_replpostsyncstatus @pubid, @artid, 0, @syncinit_lsn output
					--	select @f_syncstat_posted = 1
					--end
				END

                /*
                ** If activating subscription, update sysextendedarticlesview, sysobjects and
                ** MSrepl_subscriptions.
                */
                IF @statusid in ( @active, @initiated )
                BEGIN
                    
                    /*
                    ** Update status of article to show it has been activated.
                    */
                    IF @repl_freq = 0 and EXISTS (SELECT * FROM sysextendedarticlesview WHERE artid = @artid
                        AND status & 1 <> 1)
                    BEGIN
                        -- At most one row will be updated in the following two updates as the artid is unique
                        -- among both sysarticles and sysschemaarticles
                        UPDATE sysarticles SET status = status | 1 WHERE artid = @artid
                        IF @@ERROR <> 0
                            BEGIN
                                CLOSE hCsubstatus
                                DEALLOCATE hCsubstatus
                                if @@trancount > 0
                                begin
                                    ROLLBACK TRANSACTION changesubstatus
                                    commit tran
                                end
                                RAISERROR (14069, 16, -1)
                                RETURN (1)
                            END
                        UPDATE sysschemaarticles SET status = status | 1 WHERE artid = @artid
                        IF @@ERROR <> 0
                            BEGIN
                                CLOSE hCsubstatus
                                DEALLOCATE hCsubstatus
                                if @@trancount > 0
                                begin
                                    ROLLBACK TRANSACTION changesubstatus
                                    commit tran
                                end
                                RAISERROR (14069, 16, -1)
                                RETURN (1)
                            END
                        SELECT @art_change = 1
                    END

                        /*
                        ** Turn the replication flag on for this object in the
                        ** sysobjects table (make it logbased).
                        */

                    if @repl_freq = 0
                      BEGIN
                        UPDATE sysobjects SET replinfo = replinfo | @replicate_bit
                        WHERE id = ( SELECT objid FROM sysextendedarticlesview WHERE artid = @artid )
                      END

                      IF @@ERROR <> 0
                      BEGIN
                          CLOSE hCsubstatus
                          DEALLOCATE hCsubstatus
                          if @@trancount > 0
                            begin
                                ROLLBACK TRANSACTION changesubstatus
                                commit tran
                            end
                          RAISERROR (14068, 16, -1)
                          RETURN (1)
                      END

                END

                /*
                ** Update status of all columns if subscriber is non-SQL Server.
                */
                IF @non_sql_flag <> 0 AND ( @art_type & 1 ) = 1
                BEGIN
                    IF @statusid = @subscribed OR @statusid = @active
                    BEGIN

                        EXEC @retcode = dbo.sp_MSarticlecol @artid, NULL,
                                                          'nonsqlsub', 'add'
                        IF @@ERROR <> 0 OR @retcode <> 0
                        BEGIN
                            CLOSE hCsubstatus
                            DEALLOCATE hCsubstatus
                            if @@trancount > 0
                            begin
                                ROLLBACK TRANSACTION changesubstatus
                                commit tran
                            end
                            RAISERROR (14068, 16, -1)
                            RETURN (1)
                        END


                    END
                    ELSE IF @statusid = @inactive
                    BEGIN

                        EXEC @retcode = dbo.sp_MSarticlecol @artid, NULL,
                                                          'nonsqlsub', 'drop'
                        IF @@ERROR <> 0 OR @retcode <> 0
                        BEGIN
                          CLOSE hCsubstatus
                          DEALLOCATE hCsubstatus
                          if @@trancount > 0
                            begin
                                ROLLBACK TRANSACTION changesubstatus
                                commit tran
                            end
                          RAISERROR (14068, 16, -1)
                          RETURN (1)
                        END
                    END
                END

				
                /*
                ** If deactivating subscription, update sysextendedarticlesview, sysobjects and
                ** MSrepl_subscriptions.
                */

                IF @statusid NOT IN( @active, @initiated ) AND @sub_status IN ( @active, @initiated )
                BEGIN
                    /*
                    ** Set the article status to 'inactive' if there are
                    ** no other active subscriptions on it.
                    */
                    IF NOT EXISTS (SELECT * FROM syssubscriptions WHERE
                       artid = @artid AND status = @active)
                    BEGIN
                        IF EXISTS (SELECT * FROM sysextendedarticlesview WHERE artid = @artid
                            AND status & 1 = 1)
                        BEGIN
                            -- At most one row will be updated in the following two updates as the artid is unique
                            -- among both sysarticles and sysschemaarticles
                            UPDATE sysarticles SET status = status & ~1 WHERE
                                artid = @artid
                            IF @@ERROR <> 0
                            BEGIN
                                CLOSE hCsubstatus
                                DEALLOCATE hCsubstatus
                                if @@trancount > 0
                                begin
                                    ROLLBACK TRANSACTION changesubstatus
                                    commit tran
                                end
                                RAISERROR (14069, 16, -1)
                                RETURN (1)
                            END
                            UPDATE sysschemaarticles SET status = status & ~1 WHERE
                                artid = @artid
                            IF @@ERROR <> 0
                            BEGIN
                                CLOSE hCsubstatus
                                DEALLOCATE hCsubstatus
                                if @@trancount > 0
                                begin
                                    ROLLBACK TRANSACTION changesubstatus
                                    commit tran
                                end
                                RAISERROR (14069, 16, -1)
                                RETURN (1)
                            END

                            SELECT @art_change = 1
                        END
                    END

                    /*
                    ** Set the object replication bits  to 'inactive' if
                    ** there are no other active subscriptions on the
                    ** table.
                    */
                    IF NOT EXISTS (SELECT * FROM syssubscriptions WHERE
                        artid IN (SELECT sa.artid FROM sysextendedarticlesview sa, syspublications sp WHERE
                        sa.objid = @tabid and sa.pubid = sp.pubid and sp.repl_freq = 0) AND status = @active)
                    BEGIN
                        UPDATE sysobjects SET replinfo =  replinfo & ~@replicate_bit
                        WHERE id = (SELECT objid FROM sysextendedarticlesview WHERE artid = @artid )

                        IF @@ERROR <> 0
                        BEGIN
                           CLOSE hCsubstatus
                            DEALLOCATE hCsubstatus
                            RAISERROR (14068, 16, -1)
                            if @@trancount > 0
                            begin
                                ROLLBACK TRANSACTION changesubstatus
                                commit tran
                            end
                            RETURN (1)
                        END
                    END
                END

				-- Note:  Not only do we need to have the replupdateschema already executed
				-- so we can handle rollbacks, we also need to
                -- acquire the schema lock before RPC to the distributor to avoid livelock
                -- with snapshot agents. Snapshot agents acquire lock on user table before
                -- updating the distribution db.

                if @no_distproc = 0
                begin
                    /*
                    ** Add the active subscription to the distributor's
                    ** subscriptions table if changing status from @inactive
                    */
                    IF @sub_status = @inactive 
                    -- From inactive to subscribed or active
                    BEGIN

                        DECLARE @null_char sysname
                        SELECT @null_char = NULL

                        DECLARE @zero_bit bit
                        SELECT @zero_bit = 0

                        SELECT @distproc = RTRIM(@distributor) + '.' + RTRIM(@distribdb) + '.dbo.sp_MSadd_subscription'
                        EXEC @retcode = @distproc @@SERVERNAME, @pub_db, @sub_name, 
                            @artid, @dest_db, @statusid, @sub_ts,
                            @publication, 
                            @null_char, /* Pass null to @article, we already gave @artid */
                            @subscription_type,
                            --@immediate_sync, 
                            @sync_type, 
                            @zero_bit,
                            @frequency_type,
                            @frequency_interval,
                            @frequency_relative_interval,
                            @frequency_recurrence_factor,
                            @frequency_subday,
                            @frequency_subday_interval,
                            @active_start_time_of_day,
                            @active_end_time_of_day,
                            @active_start_date,
                            @active_end_date,
                            @optional_command_line = @optional_command_line,
                            -- synctran
                            @update_mode = @update_mode,
                            @loopback_detection = @loopback_detection,
                            @distribution_jobid = @distribution_jobid OUTPUT,
                            @offloadagent = @offloadagent,
                            @offloadserver = @offloadserver,
							@dts_package_name = @dts_package_name,
							@dts_package_password = @dts_package_password,
							@dts_package_location = @dts_package_location,
							@distribution_job_name = @distribution_job_name

                        IF @@ERROR <> 0 OR @retcode <> 0
                        BEGIN
                            CLOSE hCsubstatus
                            DEALLOCATE hCsubstatus
                            RAISERROR (14070, 16, -1)
                            if @@trancount > 0
                            begin
                                ROLLBACK TRANSACTION changesubstatus
                                commit tran
                            end
                            RETURN (1)
                        END
                    END
                    ELSE
                    -- From subscribed or active to others
                    BEGIN
                        /*
                        ** Drop the deactivated subscription from the distributor's
                        ** subscriptions table.
                        */
                        IF @statusid = @inactive
                        -- From subscribed to inactive or from active to inactive
                        BEGIN
                            SELECT @distproc = RTRIM(@distributor) + '.' + RTRIM(@distribdb) + '.dbo.sp_MSdrop_subscription'
                            EXEC @retcode = @distproc @@SERVERNAME, @pub_db, @sub_name,  @artid, @dest_db, @publication
                            IF @@ERROR <> 0 OR @retcode <> 0
                            BEGIN
                                CLOSE hCsubstatus
                                DEALLOCATE hCsubstatus
                                RAISERROR (14070, 16, -1)
                                if @@trancount > 0
                                begin
                                    ROLLBACK TRANSACTION changesubstatus
                                    commit tran
                                end
                                RETURN (1)
                            END
                        END
                        ELSE 
                        -- From subscribed to initiated to active or from active to subscribed.
                        BEGIN
                            -- Don't do it if activating the subscription for snapshot agent.
                            --IF NOT (@from_auto_sync = 1 AND @statusid in(@active, @initiated) )
                            IF NOT (@from_auto_sync = 1 AND @statusid in(@active) )
                            BEGIN
                                SELECT @distproc = RTRIM(@distributor) + '.' + RTRIM(@distribdb) + '.dbo.sp_MSupdate_subscription'
                                EXEC @retcode = @distproc @@SERVERNAME, @pub_db, @sub_name, @artid, @statusid, @sub_ts, @dest_db
                                IF @@ERROR <> 0 OR @retcode <> 0
                                BEGIN
                                    CLOSE hCsubstatus
                                    DEALLOCATE hCsubstatus
                                    RAISERROR (14070, 16, -1)
                                    if @@trancount > 0
                                    begin
                                        ROLLBACK TRANSACTION changesubstatus
                                        commit tran
                                    end
                                    RETURN (1)
                                END
                            END
                        END
                    END
                end

                /*
                ** Set internal object replication bit  to 'inactive' if
                ** there are no other active subscriptions on the
                ** table.
                */

                IF @statusid = @inactive AND @sub_status IN (@active,@initiated) AND
                    NOT EXISTS (SELECT * FROM syssubscriptions WHERE
                    artid IN (SELECT artid FROM sysextendedarticlesview WHERE
                    objid = @tabid) AND status IN (@active,@initiated) )
                BEGIN
                       /*
                       ** If it's a procedure execution article, clear proc status bits
                       */
                       IF (@art_type & 8 ) = 8
                       BEGIN
                           UPDATE sysobjects SET replinfo = replinfo & ~24 WHERE id = @tabid
                       END
                END


                /* Turn on object replication */

                ELSE IF @statusid = @active
                BEGIN
                       IF (@art_type & 24 ) = 24
                       BEGIN

                           UPDATE sysobjects SET replinfo = replinfo | 24 WHERE id = @tabid
                       END
                       ELSE IF( @art_type & 8 ) = 8
                       BEGIN
                           UPDATE sysobjects SET replinfo = replinfo | 8 WHERE id = @tabid
                       END
                END


                exec dbo.sp_MSget_qualified_name @tabid, @qualified_name output
                exec dbo.sp_replupdateschema @qualified_name, @schemastabilityonly

               /*
               ** Get next row.
               */
               FETCH hCsubstatus INTO @artid, @tabid, @srvid, @sub_name, @dest_db,
               @sub_status, @non_sql_flag, @repl_freq, @art_type ,  
               @immediate_sync, @enabled_for_internet,
               @allow_anonymous, @subscription_type, @sync_type, @update_mode,
                @art_name, @login_name, @loopback_detection, @pubid

          
                                    
           END  -- end while for cursor

           CLOSE hCsubstatus
           DEALLOCATE hCsubstatus
        
        END -- end while for virtual and real

        -- force refresh of article cache
        -- Only do it if necessary
        -- No need on brute force cleanup
        IF ( @art_change = 1 ) and ( @ignore_distributor = 0 )
            EXECUTE dbo.sp_replflush

    COMMIT TRANSACTION
    drop table #sysextendedarticlesview
    RETURN(0)
go
 
 
EXEC dbo.sp_MS_marksystemobject sp_changesubstatus
GO

grant execute on dbo.sp_changesubstatus to public
go


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

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

CREATE PROCEDURE sp_addsubscription (
    @publication sysname,                            /* publication name */
    @article sysname = 'all',                        /* article name */
    @subscriber sysname = NULL,                        /* subscriber name */
    @destination_db sysname = NULL,                /* destination database */
    @sync_type nvarchar (15) = 'automatic',                /* subscription sync type */
    @status sysname = NULL,                            /* subscription status */
    @subscription_type nvarchar(4) = 'push',                /* subscription type:
                                                        ** 'push' or 'pull' */
    -- SyncTran
    @update_mode           nvarchar(30)    = 'read only',    -- Can be 'read only', 'sync tran', 'queued tran', 'failover'
    @loopback_detection nvarchar(5) = NULL, -- 'true' or 'false'
    -- end SyncTran

    @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,
    
    @reserved nvarchar(10) = NULL,          /* reserved, used when calling from other system */
                                            /* stored procedures, it will be set to 'internal'.*/
                                            /* It should never be used directly */
    @enabled_for_syncmgr nvarchar(5) = 'false', /* Enabled for SYNCMGR: true or false */
    -- Agent offload
    @offloadagent bit = 0,
    @offloadserver sysname = NULL,
    -- End of agent offload
	-- DTS package name
	@dts_package_name sysname  = NULL,	/* value will be sent and validated at distributor */                                  
 	@dts_package_password  sysname = NULL,
	@dts_package_location nvarchar(12) = N'distributor',
	@distribution_job_name sysname = NULL
   ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    DECLARE @artid int
    DECLARE @pre_creation_cmd tinyint
    DECLARE @none tinyint
    DECLARE @automatic tinyint
    DECLARE @cmd nvarchar(255)
    DECLARE @cmd2 nvarchar(255)
    DECLARE @inactive tinyint
    DECLARE @active tinyint
    DECLARE @subscribed tinyint
    DECLARE @manual tinyint
    DECLARE @pubid int
    DECLARE @retcode int
    DECLARE @srvid smallint
    DECLARE @subscriber_bit smallint
    DECLARE @sync_typeid tinyint
    DECLARE @non_sql_flag bit
    DECLARE @truncate tinyint
    DECLARE @sync_method tinyint
    DECLARE @char_bcp tinyint
    DECLARE @concurrent tinyint
	DECLARE @concurrent_char tinyint
    DECLARE @internal nvarchar(10)
    DECLARE @status_id tinyint
    DECLARE @virtual_id smallint
    DECLARE @subscription_type_id int /* 0 push, 1 pull */
    DECLARE @immediate_sync bit    /* publication type */
    DECLARE @count_subs int
    DECLARE @count_arts int
    DECLARE @distribution_jobid binary(16)
    DECLARE @pubstatus tinyint
    DECLARE @allow_anonymous bit
    DECLARE @immediate_sync_ready bit
    declare @loopback_detection_id bit
    declare @independent_agent_id bit
    DECLARE @platform_nt binary
    		,@artsrctabid int

    DECLARE @dsn_dbname sysname
    DECLARE @dts_package_enc_password nvarchar(524)

    -- SyncTran
    DECLARE @allow_sync_tran_id bit
    DECLARE @allow_queued_tran_id bit
    DECLARE @update_mode_id     tinyint -- 0 = read only, 1 = sync tran, 2 = queued tran, 3 = failover
										-- 4 = sqlqueued tran, 5 = sqlqueued failover
	DECLARE	@publication_queue_type int 
    -- end SyncTran

    /*
    ** Initializations.
    */

    SELECT @none = 2            /* Const: synchronization type 'none' */
    SELECT @automatic = 1       /* Const: synchronization type 'automatic' */
    SELECT @manual = 0          /* Const: synchronization type 'manual' */
    SELECT @inactive = 0        /* Const: subscription status 'inactive' */
    SELECT @subscribed = 1        /* Const: subscription status 'subscribed' */
    SELECT @active = 2        /* Const: subscription status 'arctive' */
    SELECT @subscriber_bit = 4  /* Const: subscription server status */
    SELECT @truncate = 3    /* Const: truncate pre-creation command */
    SELECT @char_bcp = 1    /* Const: character bcp sync method */
    SELECT @concurrent = 3  /* Const: concurrent sync method */
    SELECT @concurrent_char = 4  /* Const: concurrent char mode sync method */
	SELECT @virtual_id = -1 /* Const: virtual subscriber id */
    SELECT @internal = 'internal' /* Const: Flag of calling internally from system */
                                  /* stored procedures     */
	
	-- Change it  in 7.5 to avoid confusion, expecially in ole db case
    -- SELECT @dsn_dbname = 'DSN'
    SELECT @dsn_dbname = formatmessage(20586)
    SELECT @platform_nt = 0x1
 
    /*
    ** Parameter Check: @publication.
    ** Check to make sure that the publication exists and that it conforms
    ** to the rules for identifiers.
    ** set subscription_type for the publication
    */

    IF @publication IS NOT NULL
        BEGIN
            
            EXECUTE @retcode = dbo.sp_validname @publication

            IF @retcode <> 0
        RETURN (1)

            IF NOT EXISTS (SELECT * FROM syspublications WHERE name = @publication)
                BEGIN
                    RAISERROR (20026, 11, -1, @publication)
                    RETURN (1)
                END

        END

    /*
    ** Parameter Check: @subscription_type
    ** Valid values:
    ** push
    ** 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'
        SELECT @subscription_type_id = 0
    ELSE 
        SELECT @subscription_type_id = 1

    /*
    ** Parameter Check: @offloadagent
    ** Valid values: 0 or 1 
    ** If @offloadagent = 1 then @subscription_type must be 'push'
    */
    IF (@offloadagent = 1 AND LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) <> 'push')
    BEGIN
        RAISERROR(21138, 16, -1)
        RETURN (1)
    END 


    /*
    ** Parameter Check: @offloadserver
    ** Make sure that @offlaod server doesn't contain any invalid characters
    */
    EXEC @retcode = sp_MSreplcheckoffloadserver @offloadserver
    IF @retcode<>0 OR @@error<>0
        RETURN (1)
	/*
    ** 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
            return(1)
    END

   declare @allow_dts bit

   SELECT @pubid = pubid, @sync_method = sync_method, 
        @immediate_sync = immediate_sync, @pubstatus = status, 
        @allow_anonymous = allow_anonymous, 
        @immediate_sync_ready = immediate_sync_ready,
        -- SyncTran
        @allow_sync_tran_id = allow_sync_tran,
        @allow_queued_tran_id = allow_queued_tran,
        @independent_agent_id = independent_agent,
		@allow_dts = allow_dts
		,@publication_queue_type = queue_type
    FROM syspublications WHERE name = @publication

    select @srvid = srvid from master..sysservers where UPPER(srvname)=UPPER(@subscriber) collate database_default

    if exists (select name from sysobjects where name='sysmergesubscriptions')
        begin
            IF exists (select name from sysextendedarticlesview where pubid=@pubid and 
                objid in (select objid from sysmergeextendedarticlesview where 
                    pubid in (select pubid from sysmergesubscriptions where db_name=@destination_db and srvid=@srvid)))
            begin
                RAISERROR(21281, 16, -1, @publication, @destination_db)
                return (1)
            end
        end


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

	/* 
	** If publication is of concurrent sync, then all articles must
	** be subscribed to
	*/
	IF @sync_method IN( @concurrent, @concurrent_char) AND
	   LOWER(@article) != 'all' AND
	   @reserved != @internal
	BEGIN
		RAISERROR( 14100, 16, -1 )
		RETURN (1)
	END

    /* 
    ** Check to see if the desired subscription type is allowed
    */
    /* 
    ** push 
    ** Virtual subscriptions are always push type
    */
    IF @subscription_type_id = 0 AND @subscriber IS NOT NULL
    BEGIN
        IF NOT EXISTS (SELECT * from syspublications where
            allow_push = 1 AND
            pubid = @pubid)
        BEGIN
            RAISERROR (20012, 16, -1, @subscription_type, @publication)    
            RETURN (1)
        END
    END
        
    /* pull */
    IF @subscription_type_id = 1 AND @subscriber IS NOT NULL
    BEGIN
        IF NOT EXISTS (SELECT * from syspublications where
            allow_pull = 1 AND
            pubid = @pubid)
        BEGIN
            RAISERROR (20012, 16, -1, @subscription_type, @publication)    
            RETURN (1)
        END
    END

 /*
    ** Parameter Check: @subscriber.
    **
    ** Check if the server exists and that it is a subscription server.
    **
    ** @subscriber is NULL represent virtual subscription, which is not allowed
    ** in following case:
    ** 1. Non-immediate-sync publication
    ** 2. the stored procedure is not in the internal usage mode 
    **        (called by system stored procedures)
    ** 3. non push mode
    ** 
    */

    IF  @subscriber IS NULL AND (
        @immediate_sync = 0 OR
        @subscription_type_id <> 0 OR
        @reserved <> @internal)
        BEGIN
            RAISERROR (14043, 16, -1, '@subscriber')
            RETURN (1)
        END


    IF @subscriber IS NULL
        BEGIN
        /* set virtual subscriber ID */
            SELECT @srvid = @virtual_id 
            select @non_sql_flag = 0
        END
    ELSE
        BEGIN
            /* validate name and get subscriber ID  and server status  */
            EXECUTE @retcode = dbo.sp_validname @subscriber

            IF @retcode <> 0
            RETURN (1)

			select @srvid = null
            SELECT @srvid = srvid, @non_sql_flag = 
                case when srvproduct = N'MSREPL-NONSQL' then 1
                else 0 end
              FROM master..sysservers
             WHERE UPPER(srvname) = UPPER(@subscriber) collate database_default
               AND (srvstatus & @subscriber_bit) <> 0

            IF @srvid IS NULL
                BEGIN
                    RAISERROR (14010, 16, -1)
                   RETURN (1)
                END
        END

    /*
    ** Parameter Check: @destination_db.
    ** @destination_db can not be all. 
    ** Set @destination_db to current database if not specified.  Make
    ** sure that the @destination_db conforms to the rules for identifiers.
    */

    if LOWER(@destination_db) = 'all'
    BEGIN
        RAISERROR (14032, 16, -1, '@destination_db')
        RETURN (1)
    END

    IF @destination_db IS not NULL
	begin
		EXECUTE @retcode = dbo.sp_validname @destination_db
		IF @retcode <> 0
		RETURN (1)
	end

    /*
    ** Parameter Check:  @article
    */

    /* @article can not be null     */
    IF @article IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@article')
            RETURN (1)
        END

	-- Queued Tran
    -- SyncTran
    /*
    ** Parameter check: @update_mode
    */
    IF @update_mode IS NULL OR LOWER(@update_mode collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('read only', 'sync tran', 'queued tran', 'failover')
    BEGIN
        RAISERROR (20502, 16, -1, '@update_mode')
        RETURN (1)
    END

    IF (LOWER(@update_mode collate SQL_Latin1_General_CP1_CS_AS) = 'sync tran') 
    BEGIN
        SELECT @update_mode_id = 1
       
        -- Check if publication allows this option
        IF @allow_sync_tran_id <> 1
        BEGIN
            RAISERROR (20503, 16, -1, '@update_mode', 'sp_addsubscription','sync tran')
            RETURN (1)
        END
    END
    ELSE IF (LOWER(@update_mode collate SQL_Latin1_General_CP1_CS_AS) = 'queued tran')
    BEGIN    	
		SELECT @update_mode_id = case 
			when (@publication_queue_type = 2) then 4
			else 2 end
       
        -- Check if publication allows this option
        -- If the publication allow synctran, it allows queued tran.
        IF @allow_queued_tran_id <> 1
        BEGIN
            RAISERROR (20503, 16, -1, '@update_mode', 'sp_addsubscription', 'queued tran')
            RETURN (1)
        END
    END
    ELSE IF (LOWER(@update_mode collate SQL_Latin1_General_CP1_CS_AS) = 'failover')
    BEGIN
		SELECT @update_mode_id = case
			when (@publication_queue_type = 2) then 5
			else 3 end
       
        -- Check if publication allows this option
        IF @allow_sync_tran_id <> 1
        BEGIN
            RAISERROR (20503, 16, -1, '@update_mode', 'sp_addsubscription', 'sync tran')
            RETURN (1)
        END

        -- Check if publication allows this option
        IF @allow_queued_tran_id <> 1
        BEGIN
            RAISERROR (20503, 16, -1, '@update_mode', 'sp_addsubscription','queued tran')
            RETURN (1)
        END
    END
    ELSE 
    BEGIN
        SELECT @update_mode_id = 0
    END
    -- end SyncTran
    -- end Queued Tran

    /*
    ** Parameter Check: @dts_package_location
    ** Valid values:
    ** distributor
    ** subscriber
    **
    */
    IF LOWER(@dts_package_location collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('distributor', 'subscriber')
    BEGIN
		RAISERROR(21179, 16, -1)    
        RETURN (1)
    END

	declare @dts_package_location_id int

    IF LOWER(@dts_package_location collate SQL_Latin1_General_CP1_CS_AS) = 'distributor'
        SELECT @dts_package_location_id = 0
    ELSE 
        SELECT @dts_package_location_id = 1

	-- Have to be a push, non updatable  subscription to set DTS package name
    if @dts_package_name is not null
	begin
		if	@subscription_type_id != 0
		begin
			RAISERROR(21181, 16, -1)    
			RETURN (1)
		end
		if	@allow_dts = 0
		begin
			RAISERROR(21178, 16, -1)    
			RETURN (1)
		end
	end
	
	/** For immediate_sync publication, @article has to be 'all'     */
    IF NOT @reserved = @internal AND @immediate_sync = 1
        AND NOT LOWER(@article) = 'all'
        BEGIN
            RAISERROR (14122, 16, -1)
            RETURN (1)
        END

    /* 
    ** For full subscription, check to see if  subscriptions
    ** to ALL the articles exist before expanding parameter @article.
    **
    */
    IF LOWER(@article) = 'all' AND @reserved <> @internal AND
        EXISTS (SELECT * FROM syspublications WHERE pubid = @pubid)
    BEGIN
        SELECT @count_arts = count(*) FROM sysextendedarticlesview art
            WHERE art.pubid = @pubid 

		if @count_arts = 0
		BEGIN
            RAISERROR (14124, 16, -1)
            RETURN(1)
        END

        SELECT @count_subs = count(*) FROM syssubscriptions sub, 
                  sysextendedarticlesview art
            WHERE sub.srvid = @srvid
              AND sub.srvid >= 0
              AND sub.dest_db = @destination_db --or @non_sql_flag <> 0)
              AND sub.artid = art.artid
              AND art.pubid = @pubid

        IF @count_arts = @count_subs
        BEGIN
              RAISERROR (14058, 16, -1)
              RETURN (1)
        END
    END

    /* 
    ** Real subscription to inactive publicaton is not allowed
    ** Note, subscriptions to the new article will be added automatically
    ** for immediate_sync publications. At that time, the publication may not
    ** be active.
    */

    IF  @srvid <> @virtual_id AND @pubstatus = 0 AND @reserved <> @internal
        BEGIN
            RAISERROR (21000, 16, -1)
            RETURN (1)
        END


	-- If the publication is 'allow_dts', push subscription has to specify a DTS package.
	-- Error check that disallow ODBC subscriber to subscriber with DTS package
	-- is at the distributor.
	-- Show dts error first, otherwise user will get 21060 below which is confusing
	IF @allow_dts <> 0 and @dts_package_name is null and @subscriber IS not NULL and
		@reserved is null and @subscription_type_id = 0
	begin
		raiserror(21213, 16, -1)
		return(1)
	end

    /* 
    ** Do special things for DSN subscribers.
    */
    IF @subscriber IS NOT NULL AND @non_sql_flag <> 0
    BEGIN
		-- DSN or oledb subscriber not using DTS 
		-- cannot subscribe to native mode or concurrent snapshot publication
        IF @sync_method <> @char_bcp and @dts_package_name is null
        BEGIN
            RAISERROR (14095, 16, -1, @publication, @subscriber)
            RETURN (1)
        END

        -- DSN subscriber cannot subscribe with 'Sync Update'
        IF @update_mode_id <> 0
        BEGIN
            RAISERROR (21032, 16, -1, @subscriber)
            RETURN (1)
        END

        -- DSN subscriber cannot subscribe to article using custom procs
        -- or articles using parameterized statements
        -- ( only run this test during execs when the article name is actually specified )
        IF( LOWER( @article ) <> 'all' )
        BEGIN
            --IF EXISTS ( select * from sysextendedarticlesview sa, syspublications sp
                        --where sa.pubid = sp.pubid 
                        --and sp.name = @publication
                        --and sa.name = @article
                        --and ( ins_cmd like '%call%' or upd_cmd like '%call%' or del_cmd like '%call%' ) )
            --BEGIN
                --RAISERROR(21051, 16, -1, @subscriber)
                --RETURN (1)
            --END
			
			declare @art_status tinyint

			select @art_status = sa.status, @artsrctabid = sa.objid 
			from sysarticles sa, syspublications sp
				where sa.pubid = sp.pubid 
				and sp.name = @publication
				and sa.name = @article

			-- OLEDB or ODBC subscriber can not subscribe to article with parameterized command 
			-- unless using DTS 
            IF @dts_package_name is null and @art_status & 16 = 16 
            BEGIN
                RAISERROR(21060, 16, -1, @subscriber)
                RETURN (1)
            END

			-- OLEDB or ODBC subscribers can not subscriber to article with subscriber managed
			-- timestamp column
			if @art_status & 32 = 32
			begin
				raiserror(21249, 16, -1, @article, @publication)
				return (1)
			end

        END
    END

    -- DNS may define db.  If no db given, specify that DSN default should be used.
    -- use internal values
	if @subscriber IS NOT NULL and @destination_db is NULL
	begin
		IF @non_sql_flag <> 0 
			SELECT @destination_db = @dsn_dbname
		else
			SELECT @destination_db = DB_NAME()
	end

	-- if we're subscribing to a dump type publication, error
	-- out if this subscriber has any other subscriptions to publications
	-- other than this one

	IF @sync_method = 2
	BEGIN
		IF EXISTS( SELECT * FROM syssubscriptions sub, sysextendedarticlesview art
				WHERE sub.srvid = @srvid
				  AND sub.srvid >= 0
				  AND sub.dest_db = @destination_db
				  AND sub.artid = art.artid
				  AND art.pubid != @pubid )
		BEGIN
			RAISERROR(21144, 16, -1)
			RETURN 1
		END
	END

	-- else if we're subscribing to a char or native mode publication, 
	-- error out if the subscriber is subscribed to any dump type publications
	ELSE
	BEGIN
		IF EXISTS( SELECT * FROM syssubscriptions sub, sysextendedarticlesview art, syspublications pub
				WHERE sub.srvid = @srvid
				  AND sub.srvid >= 0
				  AND sub.dest_db = @destination_db
				  AND sub.artid = art.artid
				  AND art.pubid != @pubid
				  AND pub.pubid = art.pubid
				  AND pub.sync_method = 2 )

		BEGIN
			RAISERROR(21145, 16, -1, @publication )
			RETURN 1
		END
	END


    IF LOWER(@article) = 'all' 
    /*
    ** Get all articles in the publication that are not subscribed to
    ** by the @subscriber
    */
    BEGIN
            /*
            ** Make the operation atomic. This is to prevent multiple subscription_type
            ** from one subscriber on an immediate_sync publication
            */
            BEGIN TRAN 

            DECLARE hCx CURSOR LOCAL FAST_FORWARD FOR  SELECT DISTINCT a.name
                FROM sysextendedarticlesview a, syspublications b  
                WHERE a.pubid = b.pubid 
                AND b.name = @publication
                AND NOT EXISTS (SELECT * from syssubscriptions s 
                    WHERE s.artid = a.artid AND s.status <> 0 AND s.srvid = @srvid
                    AND s.dest_db = @destination_db)
            FOR READ ONLY

            EXECUTE (@cmd + @cmd2)
            OPEN hCx
            FETCH hCx INTO @article

            WHILE (@@fetch_status <> -1)
                BEGIN
                    EXECUTE @retcode = dbo.sp_addsubscription 
                                @publication       = @publication,
                                @article        = @article,
                                @subscriber     = @subscriber,
                                @destination_db = @destination_db,
                                @sync_type      = @sync_type,
								@status			= @status,
                                @subscription_type = @subscription_type,
                                @reserved       = @internal,
                                -- SyncTran
                                @update_mode    = @update_mode,      
                                -- end SyncTran
								@loopback_detection = @loopback_detection,
                                @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,
                                @offloadserver = @offloadserver,
                                @offloadagent = @offloadagent,
								@dts_package_name = @dts_package_name,
								@dts_package_password = @dts_package_password,
								@dts_package_location = @dts_package_location,
								@distribution_job_name = @distribution_job_name
    

                    IF @@error <> 0 OR @retcode <> 0
                    BEGIN
                       CLOSE hCx
                       DEALLOCATE hCx
                       if @@trancount > 0
                            ROLLBACK TRAN 
                       RETURN (1)
                    END
                    FETCH hCx INTO @article
                END
            CLOSE hCx
            DEALLOCATE hCx

            COMMIT TRAN

            RETURN (0)
        END

   
    /* After 'all' being expanded, check to make sure that the article exists, 
    ** is not NULL, and conforms to the rules for identifiers.
    */
    /*
    EXECUTE @retcode = dbo.sp_validname @article
    IF @retcode <> 0
    RETURN (1)
    */

	declare @dest_owner sysname

    SELECT @artid = artid, @pre_creation_cmd = pre_creation_cmd,
		@dest_owner = dest_owner
    FROM sysextendedarticlesview
    WHERE name = @article
    AND pubid = @pubid

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


    /*
    ** If the subscriber is an ODBC DSN, do not allow subscriptions to
    ** articles with a "truncate" pre_creation_cmd.
    */
    IF @non_sql_flag <> 0 AND @pre_creation_cmd = @truncate
        BEGIN
            RAISERROR (14094, 16, -1, @article, @subscriber)
            RETURN (1)
        END

    IF @non_sql_flag <> 0 AND @dest_owner is not null and @dest_owner <> N''
        BEGIN
			--RAISERROR (21039, 16, -1, @article, @subscriber)
			-- YWU: UNDONE fix the message after Beta2
			RAISERROR (21039, 16, -1)
            RETURN (1)
        END

   /*
   ** Parameter Check: @sync_type.
   ** Set sync_typeid based on the @sync_type specified.
   **
   **   sync_typeid     sync_type
   **   ===========     =========
   **             0     manual
   **             1     automatic
   **             2     none
   */


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

   IF LOWER(@sync_type collate SQL_Latin1_General_CP1_CS_AS) = 'manual'
       BEGIN
           RAISERROR (14123, 16, -1)
           RETURN (1)
       END


   IF LOWER(@sync_type collate SQL_Latin1_General_CP1_CS_AS) = 'automatic'
   BEGIN
        SELECT @sync_typeid = @automatic
   END
   ELSE
   BEGIN
        SELECT @sync_typeid = @none
   END


    /*
    ** Parameter Check: @status
    ** If the publication is immediate_sync type and sync_type is automatic
    ** the status has to be NULL.
    ** Note for 6x backward compatibility, don't do the check for non immediate_sync
    ** publication
    */
    IF @immediate_sync = 1 and @sync_typeid = @automatic AND 
        @status IS NOT NULL
    BEGIN
          RAISERROR (14129, 16, -1)
          RETURN (1)
    END

    /*
    ** Parameter Check:  @loopback_detection
    */
    IF @loopback_detection is not null and LOWER(@loopback_detection collate SQL_Latin1_General_CP1_CS_AS) 
        NOT IN ('true', 'false')
    BEGIN
        RAISERROR (14148, 16, -1, '@loopback_detection')
        RETURN (1)
    END

    IF  LOWER(@loopback_detection collate SQL_Latin1_General_CP1_CS_AS) = 'true'  
        SELECT @loopback_detection_id = 1
    ELSE IF LOWER(@loopback_detection collate SQL_Latin1_General_CP1_CS_AS) = 'false' 
        SELECT @loopback_detection_id = 0
    ELSE 
    -- if @loopback_detection is null, we will chose the value
    begin
	    -- turn on loopback detection for sync and queued
		if @update_mode_id in (1,2,3,4,5) 
            select @loopback_detection_id = 1
        else
            select @loopback_detection_id = 0
    end

	select @dts_package_enc_password = @dts_package_password
	if @dts_package_password is not null
	begin
		EXEC @retcode = master.dbo.xp_repl_encrypt @dts_package_enc_password OUTPUT
		IF @@error <> 0 OR @retcode <> 0
			return 1
	end

	--
	-- For updating subscriptions (immediate)
	-- Check if there exists an subscription to the same
	-- dest_db which contain at least one article which has 
	-- the same source_table as the current article - 
	-- If yes then raise a warning
	-- NOTE : this restriction is due to the fact that loopback
	-- detection happens at database level and hence for updating
	-- subscriptions, we can lose the updates made in one subscription
	-- w.r.t. the other subscription
	--
	if (@update_mode_id = 1)
	begin
		if exists (select * from dbo.syssubscriptions 
			where srvid = @srvid and dest_db = @destination_db and artid in 
				(select artid from dbo.sysarticles 
					where objid = (select objid from sysarticles where artid = @artid)))
		begin
			raiserror(21293, 10, 1, @article, @destination_db)
		end
	end

    /*
    ** Add subscription to syssubscriptions
    */
    begin tran
    save TRAN addsubscription

    /*
    ** If no subscription exists, add it to syssubscriptions.
    */
    IF NOT EXISTS (SELECT *
                     FROM syssubscriptions
                    WHERE srvid = @srvid
                      AND artid = @artid
                      AND dest_db = @destination_db )--or @non_sql_flag <> 0))
        BEGIN
       INSERT syssubscriptions (artid,
                                    srvid,
                                    dest_db,
                                    login_name,
                                    status,
                                    sync_type,
                                    subscription_type,
                                    distribution_jobid,
                                    -- SyncTran
                                    update_mode,
                                    loopback_detection,
                                    queued_reinit)
                                 /*  timestamp) */
       VALUES (@artid,
                   @srvid,
                   @destination_db,    
                   suser_sname(suser_sid()),
                   @inactive,
                   @sync_typeid,
                   @subscription_type_id,
                   0,
                   -- SyncTran
                   @update_mode_id,
                   @loopback_detection_id,
                   1)
           /*  NULL) */

       IF @@ERROR <> 0
           BEGIN
                if @@trancount > 0
                begin
                    ROLLBACK TRAN  addsubscription
                    commit tran
                end
                RAISERROR (14057, 16, -1)
                RETURN (1)
           END
        END
    ELSE
       BEGIN
          RAISERROR (14058, 16, -1)
          if @@trancount > 0
            begin
                ROLLBACK TRAN  addsubscription
                commit tran
            end
          RETURN (1)
       END

    /*
    ** If the @status was not provided determine the default value.
    ** If the @sync_type = 'none' then the subscription defaults to 'active'.
    ** Else the subscription defaults to 'subscribed'.
    */
    IF @status IS NULL
    BEGIN
        IF @sync_typeid = @none    
            SELECT @status = 'active'
        ELSE
            SELECT @status = 'subscribed'
    END


    /*
    ** Set publication subscription status.
    */
    EXEC @retcode = dbo.sp_changesubstatus
    @publication = @publication,
    @article     = @article,
    @subscriber  = @subscriber,
    @status      = @status,
    @destination_db = @destination_db,
    
    @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,
    @distribution_jobid = @distribution_jobid OUTPUT,
    @offloadagent = @offloadagent,
    @offloadserver = @offloadserver,
	@dts_package_name = @dts_package_name,
	@dts_package_password = @dts_package_enc_password,
	@dts_package_location = @dts_package_location_id,
	@distribution_job_name = @distribution_job_name  
    
	IF @@error <> 0 OR @retcode <> 0
    BEGIN
        if @@trancount > 0
        begin
            ROLLBACK TRAN  addsubscription
            commit tran
        end
       RAISERROR (14057, 16, -1)
       RETURN (1)
    END

    UPDATE syssubscriptions SET 
        distribution_jobid = @distribution_jobid where
        artid = @artid AND
        srvid = @srvid AND
        dest_db = @destination_db            

    IF @@error <> 0
    BEGIN
        if @@trancount > 0
        begin
            ROLLBACK TRAN  addsubscription
            commit tran
        end
       RETURN (1)
    END


    /*
    ** If possible, activate the real subscriptions on immediate_sync publication
    ** immediately. Also, activate the virtual subscriptions on 
    ** anonymous publications immediately.
    ** We change the subscription status from 'subscribed' to 'active' so that 
    ** sp_MSupdate_subscription will be called, which will set the subscription's
    ** xactid_ts to the snapshot xactid_ts of virtual subscriptions. This means that
    ** we have to call sp_changesubstatus again here. We can not combine two calls 
    ** into ONE !!!
    **
    ** Activate the subscription immediately if 
    ** 1. The publication is immediate_sync type
    ** 2. sync_type is 'automatic'
    ** AND
    ** 1. The subscription is real
    ** 2. The snapshot has completed once
    ** 3. The subscription is the last subscription added to the publication (subscription for
    ** the last article). This is to guarantee the subscription status of all the articles
    ** in the publication be activate in one transaction at the distributor. This is
    ** to prevent the distribution agent from picking up partial subscriptions. 
    ** Note that this SP will be called with @article = 'all'
    ** OR
    ** 1. The publication is active
    ** 2. The publication is allow_anonymous
    ** 3. The subscription is virtual
    ** 
    */
    
    IF  @sync_typeid = @automatic AND @immediate_sync = 1 AND
        
        ((@srvid <> @virtual_id AND 
        @immediate_sync_ready = 1 AND
        NOT EXISTS (select * from sysextendedarticlesview art where
                    art.pubid = @pubid and
                    not exists (select * from syssubscriptions sub
                        where sub.artid = art.artid and
                              sub.srvid = @srvid and
                              sub.dest_db = @destination_db))) OR

        (@pubstatus = 1 and @srvid = @virtual_id and @allow_anonymous = 1))
    BEGIN
        DECLARE @article_ex sysname
        IF @srvid <> @virtual_id
            SELECT @article_ex = '%'
        ELSE
            SELECT @article_ex = @article

        /*
        ** Set publication subscription status.
        */
        EXEC @retcode = dbo.sp_changesubstatus
        @publication = @publication,
        @article     = @article_ex,
        @subscriber  = @subscriber,
        @status      = 'active',
        @destination_db = @destination_db,
        @offloadagent = @offloadagent,
        @offloadserver = @offloadserver

        IF @@error <> 0 OR @retcode <> 0
        BEGIN
            if @@trancount > 0
            begin
                ROLLBACK TRAN  addsubscription
                commit tran
            end
           RAISERROR (14057, 16, -1)
           RETURN (1)
        END
    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               sysname
        declare @publisher_db sysname
        
        set @publisher_db = DB_NAME()
        /* 
        ** 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 LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) = 'push'
        BEGIN
            EXECUTE @retcode = dbo.sp_helpdistributor
                @distributor = @distributor_server OUTPUT               /* Distributor RPC server name */

            IF @@ERROR <> 0 or @retcode <> 0
                BEGIN
                    if @@trancount > 0
                        ROLLBACK TRAN  addsubscription
                    RAISERROR (14057, 16, -1)
                    RETURN (1)
                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. */
            declare @subscription_id uniqueidentifier
            set @subscription_id = convert(uniqueidentifier, @distribution_jobid)
            exec @retcode = dbo.sp_MSregistersubscription @replication_type = 1,
                                    @publisher = @@SERVERNAME,
                                    @publisher_db = @publisher_db,
                                    @publication = @publication,
                                    @subscriber = @subscriber,
                                    @subscriber_db = @destination_db,
                                    @distributor = @distributor_server,
                                    @distributor_security_mode = @distributor_security_mode,
                                    @distributor_login = @distributor_login,
                                    @distributor_password = @distributor_password,
                                    @subscription_id = @subscription_id,
                                    @independent_agent = @independent_agent_id,
                                    @subscription_type = @subscription_type_id


            IF @@ERROR <> 0 or @retcode <> 0
                BEGIN
                    if @@trancount > 0
                        ROLLBACK TRAN  addsubscription
                    RAISERROR (14057, 16, -1)
                    RETURN (1)
                END
        END
    END
    COMMIT TRAN
go
 
EXEC dbo.sp_MS_marksystemobject sp_addsubscription
GO

grant execute on dbo.sp_addsubscription to public
go

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

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

CREATE PROCEDURE sp_MSchangeschemaarticle (
    @pubid int,
    @artid int,
    @property sysname,
    @value nvarchar(255)
    ) AS
    SET NOCOUNT ON
    DECLARE @retcode int
    DECLARE @pre_creation_cmdid tinyint
    DECLARE @statusid int
    DECLARE @schema_option_table_created bit
    DECLARE @creation_script nvarchar(255)
    DECLARE @type tinyint
    DECLARE @schema_option binary(8)
    DECLARE @valid_schema_options int

    SELECT @type = type 
      FROM sysextendedarticlesview
     WHERE artid = @artid
       AND pubid = @pubid 

    SELECT @schema_option_table_created = 0

    /* 
    ** The pubid and artid passed into this procedure from sp_changearticle
    ** have to be valid by now.
    */
    
    /*
    ** Parameter check: @property
    */
    SELECT @property = LOWER(@property collate SQL_Latin1_General_CP1_CS_AS)
    IF @property NOT IN ('description',
                         'dest_object',
                         'creation_script',
                         'pre_creation_cmd',
                         'schema_option',
                         'destination_owner')
    BEGIN
        RAISERROR(21224, 16, -1, @property)
        RETURN (1)
    END
                         
    -- Since all property changes will take the form of 
    -- simple update stataments, no transaction will be 
    -- started. 

    IF @property = N'description'
    BEGIN
        UPDATE sysschemaarticles 
           SET description = @value
         WHERE artid = @artid
           AND pubid = @pubid

        IF @@ERROR <> 0
            RETURN (1)
    END
    ELSE IF @property = N'dest_object'
    BEGIN
        UPDATE sysschemaarticles
           SET dest_object = @value
         WHERE artid = @artid
           AND pubid = @pubid 

        IF @@ERROR <> 0
            RETURN (1)
    END
    ELSE IF @property = N'creation_script'
    BEGIN
        UPDATE sysschemaarticles 
           SET creation_script = @value
         WHERE artid = @artid
           AND pubid = @pubid

        IF @@ERROR <> 0
            RETURN (1)
    END
    ELSE IF @property = N'pre_creation_cmd'
    BEGIN
        /* 
        ** Validate the given value for
        ** the property. It has to be either 
        ** 'none' or 'drop' case-insensitive.
        */
        SELECT @value = LOWER(@value collate SQL_Latin1_General_CP1_CS_AS)
        IF @value NOT IN ('none', 'drop')
        BEGIN
            RAISERROR(21223, 16, -1)
            RETURN (1)
        END

        IF @value = N'none'
            SELECT @pre_creation_cmdid = 0
        ELSE IF @value = N'drop'
            SELECT @pre_creation_cmdid = 1
        
        UPDATE sysschemaarticles
           SET pre_creation_cmd = @pre_creation_cmdid
         WHERE artid = @artid
           AND pubid = @pubid
        
        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 used, the following check is sufficient.
            -- Note that @schema_option should have been padded by now.
            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 sysschemaarticles
             WHERE artid = @artid
               AND pubid = @pubid
/*            
            IF @creation_script IS NULL OR
               @creation_script = N''
            BEGIN
                RAISERROR(21218, 16, -1)
                DROP TABLE #tab_changeschemaarticle
                RETURN (1)
            END
*/
        END
            
        UPDATE sysschemaarticles
           SET schema_option = tab.value
          FROM #tab_changeschemaarticle tab
         WHERE artid = @artid 
           AND pubid = @pubid         
            
        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
        
        UPDATE sysschemaarticles
           SET dest_owner = @value
         WHERE artid = @artid
           AND pubid = @pubid

        IF @@ERROR <> 0
            RETURN (1)
    END    

    RAISERROR (14025, 10, -1)
    RETURN (0)
GO

EXEC dbo.sp_MS_marksystemobject sp_MSchangeschemaarticle
GO

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

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

CREATE PROCEDURE sp_changearticle (
    @publication sysname = NULL,        /* Publication name */
    @article sysname = NULL,            /* Article name */
    @property nvarchar(20) = 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
BEGIN

    SET NOCOUNT ON

    /*
    ** Declarations.
    */

	DECLARE @artid int
			,@cmd1 nvarchar(512)
			,@cmd2 nvarchar(512)
			,@db sysname
			,@filter int
			,@object sysname
			,@owner sysname
			,@pubid int
			,@retcode int
			,@site sysname
			,@sync_objid int
			,@typeid tinyint
			,@old_typeid tinyint
			,@precmdid tinyint
			,@active tinyint
			,@virtual_id smallint
			,@article_type tinyint

			,@objid    int
			,@objtype  nchar(2)
			,@old_filter_name sysname

			,@distributor sysname
			,@distribdb sysname
			,@dbname sysname
			,@distproc nvarchar (255)
			,@dts_part nvarchar(50)
			,@no_dts_part nvarchar(50)
			,@backward_comp_level int
			,@allow_dts bit
			,@allow_queued_tran bit

	select @backward_comp_level = 10 -- default to sphinx
			,@dts_part = N'dts horizontal partitions'
			,@no_dts_part = N'no dts horizontal partitions'
			,@active = 2
			,@virtual_id = -1

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

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

    IF (SELECT category & 1
          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

    EXECUTE @retcode = dbo.sp_validname @publication

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

	SELECT @pubid = pubid
		,@allow_dts = allow_dts
		,@allow_queued_tran = allow_queued_tran
	FROM syspublications 
	WHERE name = @publication

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

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

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

    /*
    EXECUTE @retcode = dbo.sp_validname @article

    IF @retcode <> 0
    RETURN (1)
    */
    SELECT @artid = artid, @article_type = type, @objid = objid 
      FROM sysextendedarticlesview
     WHERE name = @article
       AND pubid = @pubid
    IF @artid IS NULL
        BEGIN
            RAISERROR (20027, 11, -1, @article)
            RETURN (1)
        END


    /*
    ** Get the object id and type from sysobjects
    */

    SELECT @objtype = type
       FROM sysobjects
       WHERE id = @objid

    IF @objtype IS NULL
    BEGIN
        RAISERROR( 20027, 11, -1, @article )
        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 not null)
            INSERT INTO #tab1 VALUES ('description')
            INSERT INTO #tab1 VALUES ('sync_object (log based article only)')
            INSERT INTO #tab1 VALUES ('type')
            INSERT INTO #tab1 VALUES ('ins_cmd (log based article only)')
            INSERT INTO #tab1 VALUES ('del_cmd (log based article only)')
            INSERT INTO #tab1 VALUES ('upd_cmd (log based article only)')
            INSERT INTO #tab1 VALUES ('filter (log based article only)')
            INSERT INTO #tab1 VALUES ('dest_table (log based article only)')
            INSERT INTO #tab1 VALUES ('dest_object')
            INSERT INTO #tab1 VALUES ('creation_script')
            INSERT INTO #tab1 VALUES ('pre_creation_cmd')
            INSERT INTO #tab1 VALUES ('status')
            INSERT INTO #tab1 VALUES ('schema_option')
            INSERT INTO #tab1 VALUES ('destination_owner')
   			INSERT INTO #tab1 VALUES ('pub_identity_range (log based article only)')
			INSERT INTO #tab1 VALUES ('identity_range (log based article only)')
			INSERT INTO #tab1 VALUES ('threshold (log based article only)')
            PRINT ''
            SELECT * FROM #tab1
            RETURN (0)
        END

    /*
    ** At this point, we have completed all the validations and 
    ** preprocessings common to both regular and schema only articles 
    ** so we call a different proceudre here to handle the schema only
    ** articles differently.
    */
    
    IF @article_type in (0x20, 0x40, 0x80)
    BEGIN
        EXEC @retcode = sp_MSchangeschemaarticle 
                @pubid = @pubid,
                @artid = @artid,
                @property = @property,
                @value = @value    
        RETURN @retcode
    END  

    IF @objtype = 'U' AND LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) NOT IN 
                                                  ('description',
                                                   'sync_object',
                                                   'type',
                                                   'ins_cmd',
                                                   'del_cmd',
                                                   'upd_cmd',
                                                   'filter',
                                                   'dest_table',
                                                   'dest_object',
                                                   'creation_script',
                                                   'pre_creation_cmd',
                                                   'status',
                                                   'schema_option',
                                                   'destination_owner',
                                                    'pub_identity_range',
                                                    'identity_range',
                                                    'threshold')
        BEGIN
            RAISERROR (21183, 16, -1, @property)
            RETURN (1)
        END

    IF @objtype = 'P' AND LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) NOT IN 
                                                  ('description',
												   'dest_object',
                                                   'dest_table',
                                                   'creation_script',
                                                   'pre_creation_cmd',
                                                   'schema_option',
                                                   'destination_owner')
        BEGIN
            RAISERROR (14110, 16, -1)
            RETURN (1)
        END


    /* dest_object and 'dest_table' are same */
    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'dest_object'
        SELECT @property = 'dest_table' 

	
	IF (@allow_dts = 1 or @allow_queued_tran = 1) and LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) IN ('ins_cmd', 'del_cmd', 'upd_cmd' )
	begin
		raiserror(21175, 16, -1)
		return (1)
	end
	
	/*
	** Check to make sure that we have a valid type for status
	*/
    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'status'
    BEGIN
		IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) IN ('not owner qualified', 'owner qualified')
		BEGIN
			RAISERROR (21023, 16, -1,@value)
			RETURN (1)
		END

		IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('no column names', 'include column names', 'string literals', 'parameters',
			@dts_part, @no_dts_part )
		BEGIN
			RAISERROR (14097, 16, -1)
			RETURN (1)
		END

		IF	LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) in (@dts_part,@no_dts_part)
		begin
			if @allow_dts = 0
			begin
				-- Invalid status for non dts pub
				raiserror(20592, 16, -1)
				RETURN (1)
			end
		end
		else
		begin
			if @allow_dts = 1
			begin
				-- Invalid status for dts pub
				raiserror(20591, 16, -1)
				RETURN (1)
			end
		end
	end
	
	IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) IN ( 'ins_cmd', 'del_cmd', 'upd_cmd' )
	BEGIN
		if exists (select * from syspublications 
		where
		pubid = @pubid and sync_method = 3) and lower(@value) not like '%call%'
		BEGIN
			RAISERROR( 21154, 16, -1, @article )
			return 1
		END
	END

	declare @need_new_snapshot bit
		,@need_reinit_subscription bit

	select @need_new_snapshot = 0
	select @need_reinit_subscription = 0

	if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) in ( N'ins_cmd', N'del_cmd', N'upd_cmd', 
        N'dest_table', N'destination_owner' ,N'type',N'filter',  
		N'pre_creation_cmd', N'schema_option') or 
		(LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'status' and LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) in (@dts_part,
		@no_dts_part))
	begin
		select @need_new_snapshot = 1
		select @need_reinit_subscription = 1
	end
	else if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) in ('sync_object',
		'creation_script')
	begin
		select @need_new_snapshot = 1
	end

	-- Have to call this stored procedure to invalidate existing snapshot or reint
	-- subscriptions if needed
	EXECUTE @retcode  = dbo.sp_MSreinit_article
		@publication = @publication, 
		@article = @article,
		@need_new_snapshot = @need_new_snapshot,
		@need_reinit_subscription = @need_reinit_subscription
		,@force_invalidate_snapshot = @force_invalidate_snapshot	/* Force invalidate existing snapshot */
		,@force_reinit_subscription = @force_reinit_subscription	/* Force reinit subscription */
		,@check_only = 1
	IF @@ERROR <> 0 OR @retcode <> 0
		return(1)

    /*
    ** Change the property.
    */

    begin tran
    save TRAN sp_changearticle

        IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) IN ( 'description', 'ins_cmd', 'del_cmd', 'upd_cmd', 'dest_table', 'creation_script', 'dest_object')
            BEGIN


            /*
            ** Check the validity of the destination object.  NULL should
            ** get converted to the source object name.  Destination object
            ** names can be owner qualified, but not database qualified.
            */

            IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'dest_table' OR LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'dest_object'
                BEGIN
                    IF @value IS NULL
                        SELECT @value = object_name(objid)
                          FROM sysarticles
                         WHERE artid = @artid
                           AND pubid = @pubid
                END

            SELECT @cmd1 = 'UPDATE sysarticles '

            IF @value IS NULL
            BEGIN
                    SELECT @cmd1 = @cmd1 + '   SET ' + LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) + ' = NULL'
                SELECT @cmd2 = ' WHERE artid = ' + STR(@artid)
                SELECT @cmd2 = @cmd2 + '   AND pubid = ' + STR(@pubid)
                EXECUTE (@cmd1 + @cmd2)
            END
            ELSE
            BEGIN
                
                SELECT @cmd1 = @cmd1 + '   SET ' + LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) + ' = ''' + @value + ''''
                SELECT @cmd2 = ' WHERE artid = ' + STR(@artid)
                SELECT @cmd2 = @cmd2 + '   AND pubid = ' + STR(@pubid)
                EXECUTE (@cmd1 + @cmd2)
            END

            IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'upd_cmd'
            BEGIN
                IF ( 0 <> ( SELECT PATINDEX( '%[789].[0-9]%', @@version ) ) ) OR
                   ( 0 <> ( SELECT PATINDEX( '%[1-9][0-9].[0-9]%', @@version ) ) )
                BEGIN
                    exec dbo.sp_MSsetfilteredstatus @objid
                END

            END

            IF @@ERROR <> 0 
                BEGIN
                    if @@trancount > 0
                    begin
                        ROLLBACK TRAN sp_changearticle
                        commit tran
                    end
                    RETURN (1)
                END
            END

        IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'sync_object'
            BEGIN

                /*
                ** Check for a valid synchronization object.
                */

                IF @value IS NULL
                    BEGIN
                        RAISERROR (14043, 16, -1, '@value')
                        if @@trancount > 0
                        begin
                            ROLLBACK TRAN sp_changearticle
                            commit tran
                        end
                        RETURN (1)
                    END

                IF @value LIKE '%.%.%' OR @value LIKE '%.%'
                BEGIN
                  select @object = PARSENAME( @value, 1 )
                  select @owner = PARSENAME(  @value, 2 )
                  select @db = PARSENAME(  @value, 3 )
                  select @site = PARSENAME(  @value, 4 )

                  if @object IS NULL
                        return 1
                END


                SELECT @sync_objid = OBJECT_ID(@value)
                IF @sync_objid IS NULL
                    BEGIN
                        RAISERROR (15001, 11, -1, @value)
                        if @@trancount > 0
                        begin
                            ROLLBACK TRAN sp_changearticle
                            commit tran
                        end
                        RETURN (1)
                    END

                IF NOT EXISTS (SELECT *
                                 FROM sysobjects
                                WHERE type IN ('U', 'V')
                                  AND id = @sync_objid)

                    BEGIN
                        RAISERROR (14031, 16, -1)
                        if @@trancount > 0
                        begin
                            ROLLBACK TRAN sp_changearticle
                            commit tran
                        end
                        RETURN (1)
                    END

                /*
                ** Update the article with the new synchronization object.
                */

                UPDATE sysarticles
                   SET sync_objid = @sync_objid
                 WHERE artid = @artid
                   AND pubid = @pubid

                IF @@ERROR <> 0 
                BEGIN
                    if @@trancount > 0
                    begin
                        ROLLBACK TRAN sp_changearticle
                        commit tran
                    end
                    RETURN (1)
                END

            END

        IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'type'
            BEGIN

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

            IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('logbased', 'logbased manualfilter', 'logbased manualview', 'logbased manualboth')
                    BEGIN
                        RAISERROR (14023, 16, -1)
                        if @@trancount > 0
                        begin
                            ROLLBACK TRAN sp_changearticle
                            commit tran
                        end
                        RETURN (1)
                    END

                /*
                ** Determine the integer value for the type.
                */
            IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) IN ('logbased', 'indexed view logbased')
            SELECT @typeid = 1
            ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) IN ('logbased manualfilter', 'indexed view logbased manualfilter')
            SELECT @typeid = 3
            ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) IN ('logbased manualview', 'indexed view logbased manualview')
            SELECT @typeid = 5
            ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) IN ('logbased manualboth', 'indexed view logbased manualboth')
            SELECT @typeid = 7

                /*
                ** Update the article with the new type.
                */

                UPDATE sysarticles
                   SET type = @typeid
                 WHERE artid = @artid
                   AND pubid = @pubid

                IF @@ERROR <> 0 
                BEGIN
                    if @@trancount > 0
                    begin   
                        ROLLBACK TRAN sp_changearticle
                        commit tran
                    end
                    RETURN (1)
                END


            END

        IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'filter'
            BEGIN

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

                IF @value IS NOT NULL
                    BEGIN

                        IF @value LIKE '%.%.%' OR @value LIKE '%.%'
                        BEGIN
                           select @object = PARSENAME( @value, 1 )
                           select @owner = PARSENAME(  @value, 2 )
                           select @db = PARSENAME(  @value, 3 )
                           select @site = PARSENAME(  @value, 4 )

                           if @object IS NULL
                                 return 1
                        END

                    END

                SELECT @filter = OBJECT_ID(@value)

                IF @value IS NOT NULL
                    BEGIN

                        IF @filter IS NULL
                            BEGIN
                                RAISERROR (15001, 11, -1, @value)
                                if @@trancount > 0
                                begin
                                    ROLLBACK TRAN sp_changearticle
                                    commit tran
                                end
                                RETURN (1)
                            END

                        IF NOT EXISTS (SELECT *
                                         FROM sysobjects
                                        WHERE type = 'RF'
                                          AND id = @filter)

                            BEGIN
                                RAISERROR (14049, 16, -1)
                                if @@trancount > 0
                                begin
                                    ROLLBACK TRAN sp_changearticle
                                    commit tran
                                end
                                RETURN (1)
                            END

                    END

                IF @value IS NULL SELECT @filter = 0

                -----------------------------
                -- save off the old filter
                -----------------------------

                SELECT @old_filter_name = object_name( filter )
                FROM sysarticles WHERE artid = @artid
                AND pubid = @pubid

                IF @@ERROR <> 0 
                BEGIN
                    if @@trancount > 0
                    begin
                        ROLLBACK TRAN sp_changearticle
                        commit tran
                    end
                    RETURN (1)
                END

                --------------------------------------------
                -- Update the article with the new filter.
                --------------------------------------------

                UPDATE sysarticles
                   SET filter = @filter
                 WHERE artid = @artid
                   AND pubid = @pubid

                IF @@ERROR <> 0 
                BEGIN
                    if @@trancount > 0
                    begin
                        ROLLBACK TRAN sp_changearticle
                        commit tran
                    end
                    RETURN (1)
                END

                -- SQL SERVER > 7.x ONLY  disassociate old filter with table
                -- and associate new one

                IF ( 0 <> ( SELECT PATINDEX( '%[789].[0-9]%', @@version ) ) ) OR
                   ( 0 <> ( SELECT PATINDEX( '%[1-9][0-9].[0-9]%', @@version ) ) )   
                BEGIN

                    ------------------------------------------
                    -- disassociate table from old filter proc
                    ------------------------------------------

                    EXEC dbo.sp_MSsetfilterparent @old_filter_name, 0

                    IF @@ERROR <> 0
                    BEGIN
                        if @@trancount > 0
                        begin
                            ROLLBACK TRAN sp_changearticle
                            commit tran
                        end
                        RETURN (1)
                    END

                    ------------------------------------------------------
                    -- set the parent of the filter proc to this object_id
                    ------------------------------------------------------

                    EXEC dbo.sp_MSsetfilterparent @value, @objid

                    IF @@ERROR <> 0
                    BEGIN
                        if @@trancount > 0
                        begin
                            ROLLBACK TRAN sp_changearticle
                            commit tran
                        end
                        RETURN (1)
                    END
                END

            END

        IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'pre_creation_cmd'
            BEGIN

                /*
                ** Check to make sure that we have a valid pre_creation_cmd.
                */
            IF @objtype = 'P' and LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('none', 'drop')
                BEGIN
                    RAISERROR ( 14111, 16, -1 )
                    if @@trancount > 0
                    begin   
                        ROLLBACK TRAN sp_changearticle
                        commit tran
                    end
                    RETURN (1)
                END

                IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('none', 'drop', 'delete', 'truncate')
                    BEGIN
                        RAISERROR (14061, 16, -1)
                        if @@trancount > 0
                        begin
                            ROLLBACK TRAN sp_changearticle
                            commit tran
                        end
                        RETURN (1)
                    END

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

                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 sysarticles
                   SET pre_creation_cmd = @precmdid
                 WHERE artid = @artid
                   AND pubid = @pubid

                IF @@ERROR <> 0 
                    BEGIN
                        if @@trancount > 0
                        begin
                            ROLLBACK TRAN sp_changearticle
                            commit tran
                        end
                        RETURN (1)
                    END

            END

        IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'status'
            BEGIN
                /*
                ** Determine the integer value for the type.
                */
                IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'not owner qualified'
                    UPDATE sysarticles 
                    SET status = status & ~4
                    WHERE artid = @artid
                                  AND pubid = @pubid

                ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'owner qualified'
                    UPDATE sysarticles 
                    SET status = status | 4
                    WHERE artid = @artid
                                  AND pubid = @pubid
                     
                ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'no column names'
                    UPDATE sysarticles 
                    SET status = status & ~8
                    WHERE artid = @artid
                                  AND pubid = @pubid
                     
                ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'include column names'
                    UPDATE sysarticles 
                    SET status = status | 8
                    WHERE artid = @artid
                                  AND pubid = @pubid

                ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'string literals'
                    UPDATE sysarticles 
                    SET status = status & ~16
                    WHERE artid = @artid
                                  AND pubid = @pubid
                     
                ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'parameters'
                    UPDATE sysarticles 
                    SET status = status | 16
                    WHERE artid = @artid
                                  AND pubid = @pubid
                ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = @dts_part
				begin
					if exists (select * from sysarticles where
						artid = @artid and
						status & 64 = 0)
					begin
						UPDATE sysarticles 
							SET status = status | 64,
								upd_cmd = N'XCALL sp_MSXpd_' + @article
							WHERE artid = @artid
					end
				end
                ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = @no_dts_part
				begin
					if exists (select * from sysarticles where
						artid = @artid and
						status & 64 <> 0)
					begin
						UPDATE sysarticles 
							SET status = status & ~64,
								upd_cmd = N'CALL sp_MSupd_' + @article
							WHERE artid = @artid
					end
                end
                IF @@ERROR <> 0 
                    BEGIN
                        if @@trancount > 0
                        begin
                            ROLLBACK TRAN sp_changearticle
                            commit tran
                        end
                        RETURN (1)
                    END
            END

        IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'schema_option'
            BEGIN
                IF @value IS NULL
                    BEGIN
                        RAISERROR(14146, 16,1)
                        if @@trancount > 0
                        begin
                            ROLLBACK TRAN sp_changearticle
                            commit tran
                        end
                        RETURN (1)
                    END

                CREATE TABLE #tab_changearticle (value varbinary(8) NULL)
                                     
                IF @@ERROR <> 0 
                    BEGIN
                        if @@trancount > 0
                        begin
                            ROLLBACK TRAN sp_changearticle
                            commit tran
                        end
                        RETURN (1)
                    END

                EXEC ('insert #tab_changearticle values (' + 
                        @value +')' )
                                     
                IF @@ERROR <> 0 
                    BEGIN
                        if @@trancount > 0
                        begin
                            ROLLBACK TRAN sp_changearticle
                            commit tran
                        end
                        RETURN (1)
                    END
                
                IF @objtype = 'P' AND EXISTS (SELECT * from #tab_changearticle 
                    WHERE value <> 0x0000000000000000 AND
                          value <> 0x0000000000000001 )
                    BEGIN
                        RAISERROR ( 20014, 16, -1 )
                        if @@trancount > 0
                        begin
                            ROLLBACK TRAN sp_changearticle
                            commit tran
                        end
                        RETURN (1)
                    END

				--
				-- For queued updating publications
				-- DRI option has to be included
				--
				if ((@allow_queued_tran = 1) and 
					exists (select * from #tab_changearticle where 
						fn_replgetbinary8lodword(fn_replprepadbinary8(value)) & 0x8000 = 0))
				BEGIN
					RAISERROR (21394, 16, 2)
					if @@trancount > 0
					begin
						ROLLBACK TRAN sp_changearticle
						commit tran
					end
					RETURN (1)
				END

                -- Seems to be a good place to check and see if using 
                -- collation 0x00001000 or extended property 0x00002000
	            declare @schema_option_int int
	            select @schema_option_int  = 
                    fn_replgetbinary8lodword(fn_replprepadbinary8(value)) 
                from #tab_changearticle
	            if ((@schema_option_int & 0x000001000 <> 0) or 
                    (@schema_option_int & 0x000002000 <> 0))
		        select @backward_comp_level = 40
                -- End 
               
                UPDATE sysarticles 
                   SET schema_option = fn_replprepadbinary8(tab.value) 
                  FROM #tab_changearticle tab 
                 WHERE artid = @artid
                   AND pubid = @pubid
                DROP TABLE #tab_changearticle 
                                     
                IF @@ERROR <> 0 
                    BEGIN
                        if @@trancount > 0
                        begin
                            ROLLBACK TRAN sp_changearticle
                            commit tran
                        end
                        RETURN (1)
                    END

            END

        IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'destination_owner'
            BEGIN
                IF @value IS NOT NULL
                BEGIN
                    EXECUTE @retcode = dbo.sp_validname @value

                    IF @retcode <> 0
                    BEGIN
                        if @@trancount > 0
                        begin
                            ROLLBACK TRAN sp_changearticle
                            commit tran
                        end
                        RETURN (1)
                    END
                END

                UPDATE sysarticles SET dest_owner = @value from 
                    sysarticles WHERE artid = @artid
                                  AND pubid = @pubid
                                     
                IF @@ERROR <> 0 
                    BEGIN
                        if @@trancount > 0
                        begin
                            ROLLBACK TRAN sp_changearticle
                            commit tran
                        end
                        RETURN (1)
                    END

            END

	       
	    if  LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'pub_identity_range'
        begin
            if not exists (select * from sysarticleupdates where artid = @artid and
                identity_support = 1)
            begin
                raiserror(21235, 16, -1, @property)
                goto UNDO
            end

            declare @pub_range bigint
            select @pub_range = convert(bigint, @value) 
            if @pub_range < 0
            begin
                raiserror(21232, 16, -1)
                goto UNDO
            end

            if exists (select * from MSpub_identity_range where objid = @objid and
                pub_range < 0)
                select @pub_range = @pub_range * -1
			
            update MSpub_identity_range set
                pub_range = @pub_range 
                where objid=@objid
            if @@error < 0
                goto UNDO
        end
                
	    if  LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'identity_range'
        begin
            if not exists (select * from sysarticleupdates where artid = @artid and
                identity_support = 1)
            begin
                raiserror(21235, 16, -1, @property)
                goto UNDO
            end

            declare @range bigint
            select @range = convert(bigint, @value) 
            if @range < 0
            begin
                raiserror(21232, 16, -1)
                goto UNDO
            end

            if exists (select * from MSpub_identity_range where objid = @objid and
                range < 0)
                select @range = @range * -1
			
            update MSpub_identity_range set
                range = @range 
                where objid=@objid
            if @@error < 0
                goto UNDO

            -- Distributor side data will be changed later by sp_MSchange_article.
        end

		-- Check to see if the range is too big.
		-- Must be down after the change. If the check fails, the transaction
		-- will be rolled back.
		if  LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) in ('pub_identity_range', 'identity_range')
		begin
			declare @pub_identity_range bigint, @identity_range int
			select @pub_identity_range = pub_range, 
				@identity_range = range from MSpub_identity_range where
				objid = @objid 

			declare @xtype int, @xprec int, @max_range bigint
			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
       					when 108 then power((convert(bigint,10)), @xprec) 	 --numeric
       					when 106 then power((convert(bigint,10)), @xprec) 	 --decimal
 					else
						power((convert(bigint,2)), 62) + power((convert(bigint,2)), 62) - 1  -- defaulted to bigint
					end
		
			declare @source_table nvarchar (386)
			exec @retcode = dbo.sp_MSget_qualified_name @objid, @source_table output
			if @retcode <> 0 or @@error <> 0
				goto UNDO
			if @pub_identity_range * 2 + @identity_range > (@max_range - IDENT_CURRENT(@source_table))
				begin
					raiserror(21290, 16, -1)
					goto UNDO
				end
		end

        if  LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'threshold'
        begin
            if not exists (select * from sysarticleupdates where artid = @artid and
                identity_support = 1)
            begin
                raiserror(21235, 16, -1, @property)
                goto UNDO
            end

            declare @threshold bigint
            select @threshold = convert(int, @value) 
            if @threshold < 1 or @threshold > 100
            begin
                raiserror(21233, 16, -1)
                goto UNDO
            end

            update MSpub_identity_range set
                threshold = @threshold
                where objid=@objid
            if @@error < 0
                goto UNDO
            -- Distributor side data will be changed later by sp_MSchange_article.
        end

		-------------------------------------------------------------------------
		-- some info on articles is also stored at the distributor.
		-- update info at distributor if these properties change
		-------------------------------------------------------------------------

		if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) in ( N'description', N'dest_table', N'dest_object', 
            'identity_range', 'threshold' )
		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
				RAISERROR (14071, 16, -1)
				if @@trancount > 0
				begin
					ROLLBACK TRAN sp_changearticle
					commit tran
				end
				RETURN (1)
			END

			SELECT @dbname =  DB_NAME()

			SELECT @distproc = RTRIM(@distributor) + '.' + @distribdb + 
				'.dbo.sp_MSchange_article'
			EXECUTE @retcode = @distproc
				@publisher = @@SERVERNAME,
				@publisher_db = @dbname,
				@publication = @publication,
				@article = @article,
				@article_id = @artid,
				@property = @property,
				@value = @value


			IF @@ERROR <> 0 OR @retcode <> 0
			BEGIN
				if @@trancount > 0
				begin
					ROLLBACK TRAN sp_changearticle
					commit tran
				end
				RETURN (1)
			END
		END

		-- Have to call this stored procedure to invalidate existing snapshot or reint
		-- subscriptions if needed
		EXECUTE @retcode  = dbo.sp_MSreinit_article
			@publication = @publication, 
			@article = @article,
			@need_new_snapshot = @need_new_snapshot,
			@need_reinit_subscription = @need_reinit_subscription
			,@force_invalidate_snapshot = @force_invalidate_snapshot	/* Force invalidate existing snapshot */
			,@force_reinit_subscription = @force_reinit_subscription	/* Force reinit subscription */
		IF @@ERROR <> 0 OR @retcode <> 0
			GOTO UNDO


if @backward_comp_level > 10
	update syspublications set backward_comp_level = @backward_comp_level where pubid = @pubid
    COMMIT TRAN

    /*
    ** Force the article cache to be refreshed with the new definition.
    */
    EXECUTE dbo.sp_replflush

    /*
    ** Return succeed.
    */

    RAISERROR (14025, 10, -1)
    RETURN (0)

UNDO:
    if @@TRANCOUNT > 0
    begin
        ROLLBACK TRAN sp_changearticle
        COMMIT TRAN
    end
    return(1)
END
go
 
dump tran master with no_log
go

EXEC dbo.sp_MS_marksystemobject sp_changearticle
GO

grant execute on dbo.sp_changearticle to public
go


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

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

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

    /*
    ** Declarations.
    */

    DECLARE @article sysname
    DECLARE @cmd nvarchar(255)
    DECLARE @retcode int
    DECLARE @distributor sysname
    DECLARE @distribdb sysname
    DECLARE @distproc nvarchar (255)
    DECLARE @agentname nvarchar (40)
    DECLARE @dbname sysname
    DECLARE @virtual_id smallint
	DECLARE @ad_guidname sysname
    DECLARE @alt_snapshot_folder nvarchar(255)
    DECLARE @pub_alt_snapshot_folder nvarchar(255)
	
    SELECT @virtual_id = -1
    select @dbname = 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.
    ** If the @publication is 'all', drop all publications.  Otherwise,
    ** make sure the @publication is a valid non-null identifier.
    ** Delete the logreader agent after all the publications have been 
    ** removed.
    */

    IF LOWER(@publication) = 'all'
        BEGIN
            DECLARE hC1  CURSOR LOCAL FAST_FORWARD FOR 
                SELECT DISTINCT name FROM syspublications 
                    WHERE pubid NOT IN 
                        (SELECT pubid FROM sysextendedarticlesview WHERE artid IN 
                            (SELECT artid FROM syssubscriptions WHERE srvid <> @virtual_id))
            OPEN hC1
            FETCH hC1 INTO @publication
            WHILE (@@fetch_status <> -1)
                BEGIN
                    EXECUTE dbo.sp_droppublication @publication,
                        @ignore_distributor = @ignore_distributor
                    FETCH hC1 INTO @publication
                END
            CLOSE hC1
            DEALLOCATE hC1
            RETURN (0)
        END

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

    EXECUTE @retcode = dbo.sp_validname @publication

    IF @retcode <> 0
    RETURN (1)

    /*
    ** Ascertain the existence of the publication and get the taskid.
    */
    IF NOT EXISTS (SELECT *
                     FROM syspublications
                    WHERE name = @publication)
    BEGIN
        RAISERROR (20026, 11, -1, @publication)
        RETURN (1)
    END

    /*
    ** Check to make sure that there are no subscriptions on the publication.
    */

    IF EXISTS (SELECT *
                 FROM syssubscriptions a, sysextendedarticlesview b, syspublications c
                WHERE c.name = @publication
                  AND c.pubid = b.pubid
                  AND b.artid = a.artid
                  AND a.srvid <>@virtual_id)
        BEGIN
            RAISERROR (14005, 16, -1)
            RETURN (1)
        END

    /*
    ** Delete all articles from the publication.
    */

    EXECUTE dbo.sp_droparticle @publication = @publication, 
        @article = N'all',
        @ignore_distributor = @ignore_distributor
		, @force_invalidate_snapshot = 1
    IF @@ERROR <> 0 OR  @retcode <> 0
		RETURN (1)

	select @ad_guidname = ad_guidname,
           @alt_snapshot_folder = alt_snapshot_folder 
      from syspublications 
     where name=@publication

    BEGIN TRAN

    /*
    ** Delete publication from syspublications.
    */

    DELETE FROM syspublications WHERE name = @publication

    IF @@ERROR <> 0
        GOTO UNDO

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

        IF @@ERROR <> 0 OR  @retcode <> 0
            BEGIN
                RAISERROR (14071, 16, -1)
                RETURN (1)
            END

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

        IF @@ERROR <> 0 or @retcode <> 0
            GOTO UNDO

        IF NOT EXISTS (SELECT * FROM syspublications  where repl_freq = 0)
            BEGIN
                /*
                ** Delete logreader agent, continue if drop is not successful
                */
                SELECT @distproc = RTRIM(@distributor) + '.' + @distribdb + '.dbo.sp_MSdrop_logreader_agent'
                EXECUTE @retcode = @distproc @publisher = @@SERVERNAME,
                    @publisher_db = @dbname,
                    -- 'ALL' is used in sp_addpublication.
                    @publication = 'ALL'
                IF @@ERROR <> 0 or @retcode <> 0
                    GOTO UNDO
            END

        /*
        ** Delete the publication at the distribution server
        */
        SELECT @distproc = RTRIM(@distributor) + '.' + @distribdb + 
            '.dbo.sp_MSdrop_publication'
        EXECUTE @retcode = @distproc
            @publisher = @@SERVERNAME,
            @publisher_db = @dbname,
            @publication = @publication
        IF @@ERROR <> 0 or @retcode <> 0
            GOTO UNDO
        
        /*
        ** 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 + N'.'  + fn_replquotename(@distribdb) collate database_default + 
                N'.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 + N'.'  + fn_replquotename(@distribdb) collate database_default + 
                N'.dbo.sp_MSreplremoveuncdir'
            -- Ignore errors as the snapshot folder may not exist at all
            EXECUTE @distproc
                @dir = @pub_alt_snapshot_folder
        end
    end

	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, @dbname, @ad_guidname
			if @@ERROR<>0 or @retcode<>0
			begin
				raiserror(21369, 16, -1, @publication)
				goto UNDO
			end
    	end
    	else
    	begin
			RAISERROR(21254, 16, -1, @publication)
    		GOTO UNDO
    	end
    end

    COMMIT TRAN

	-- Since we drop publisher_database_id in sp_MSdrop_publication at the distribution db when
	-- dropping the last tran (snapshot) publication, we should call repldone here to clear
	-- repl counters and lsns. This will ensure the correctness of repl counters and avoid
	-- unnecessary log scan in the logreader if it is created again after this.
	-- Ignore all errors.
	if not exists (select * from syspublications)
	begin
		DECLARE @replicate_bit	smallint
		SELECT @replicate_bit = 2

		-- Used for attach and restored db.
		-- sysservers table in master db might be changed so that
		-- sp_dropsubscription won't work, which left repl bits marked in
		-- sysobjects.
		-- We have to unmark them before calling sp_repldone, otherwise
		-- A new transaction updating those objects will be considered
		-- as repl tran. It will set the truncation point to not null, which will
		-- prevent log truncation.
		UPDATE sysobjects SET replinfo =  replinfo & ~@replicate_bit

		/* ensure we can get in as logreader */
	    exec dbo.sp_replflush

		/* clear repl dbtable fields unmark all xacts marked for replication */
		exec dbo.sp_repldone NULL, NULL, 0, 0, 1
    
	    /* release our hold on the db as logreader */
	    EXEC dbo.sp_replflush

		-- Run checkpoint to make sp_repldone result durable (write repl dbtable fields
		-- into the checkpoint record).
		checkpoint
	end	    

    return (0)  
    
UNDO:
    if @@TRANCOUNT = 1
        ROLLBACK TRAN
    else
        COMMIT TRAN
    return(1)
GO
 
EXEC dbo.sp_MS_marksystemobject sp_droppublication
GO

grant execute on dbo.sp_droppublication to public
go


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

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

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

    SET NOCOUNT ON

    /*
    ** Declarations.
    */
    declare @quoted_db      sysname
    declare @db_name        sysname
    declare @command        nvarchar(255)
    declare @description    nvarchar(500)
    declare @category_name  nvarchar(100)
    DECLARE @agentname      nvarchar(300)
    DECLARE @dbname         sysname 
    DECLARE @retcode        int
    DECLARE @distributor    sysname
    DECLARE @distribdb      sysname
    DECLARE @distproc       nvarchar (255)
    /*
    ** Initialization
    */

    SELECT @dbname = DB_NAME()

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

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

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

    /*
    ** Enable the database for publishing.
    */
    IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'
    BEGIN

        /*
        ** Drop and then create central publish tables
        */

        /* 
        ** Drop first if exists
        */

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

        /*
        ** Create central publish tables
        */

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

    ELSE    /* Disable the database for publishing. */
    BEGIN
        /*
        ** Remove all subscriptions in the database.
		** WARNING : must owner qualify proc calls for these to run inside server on restore/attach
        */
        EXEC @retcode = dbo.sp_dropsubscription @publication = 'all',
            @article = 'all', @subscriber = 'all', 
            @ignore_distributor = @ignore_distributor
        IF @@ERROR <> 0 or @retcode <> 0
        BEGIN
            return (1)
        END

		-- Used for attach and restored db.
		-- sysservers table in master db might be changed so that
		-- sp_dropsubscription won't work. Delete the table directly.

		delete syssubscriptions where srvid >= 0
        IF @@ERROR <> 0 
        BEGIN
            return (1)
        END

        /*
        ** Remove all publications and articles in the database.
		** sp_droppublication will also forcefully unmark repl bits in sysobjects
		** and call sp_repldone when dropping the last
		** publication.
        */
        EXEC @retcode = dbo.sp_droppublication @publication = 'all', 
            @ignore_distributor = @ignore_distributor
        IF @@ERROR <> 0 or @retcode <> 0
        BEGIN
            return (1)
		END

	    /* 
		** Drop central publish tables
	    */ 
		EXEC @retcode = dbo.sp_MSdrop_pub_tables
		IF @@ERROR <> 0 or @retcode <> 0
	    BEGIN
		    return (1)
	    END

      END
    return (0)
GO
 
EXEC dbo.sp_MS_marksystemobject sp_MSpublishdb
GO



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

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

CREATE PROCEDURE sp_MSactivate_auto_sub (
    @publication sysname,        /* Publication name */
    @article sysname,
	@status sysname = 'active',
    @schemastabilityonly int = 0
    ) AS

    SET NOCOUNT ON

    DECLARE @retcode int

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

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

    IF (SELECT category & 1
          FROM master..sysdatabases
         WHERE name = DB_NAME() collate database_default) = 0

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

	-- parameter check: @status:  

	IF LOWER(@status collate SQL_Latin1_General_CP1_CS_AS) not in (N'active', N'initiated')
	BEGIN
		RAISERROR(21156, 16, -1)
		RETURN 1
	END

    /*
    ** Parameter Check:  @publication.
    ** Make sure that the publication exists and the publication is not push type
    */

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

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

	IF @status = N'active'
	BEGIN
		UPDATE syspublications SET immediate_sync_ready = 1 
			WHERE
				name = @publication AND
				immediate_sync = 1 AND
				immediate_sync_ready <> 1
		IF @@ERROR <> 0
		BEGIN
			GOTO UNDO
			RETURN (1)
		END
	END

    EXECUTE @retcode = dbo.sp_changesubstatus 
        @publication = @publication,
        @article = @article,
        @status = @status,
        @from_auto_sync = 1,
        @schemastabilityonly = @schemastabilityonly

    IF @@ERROR <> 0 OR @retcode <> 0
    BEGIN
        GOTO UNDO
        RETURN (1)
    END
    
    COMMIT TRAN
    RETURN(0)

UNDO:
    IF @@TRANCOUNT = 1
        ROLLBACK TRAN
    ELSE
        COMMIT TRAN

GO

EXEC dbo.sp_MS_marksystemobject sp_MSactivate_auto_sub
GO

grant exec on dbo.sp_MSactivate_auto_sub to public
go

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

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

create procedure sp_MSis_identity_insert
@publication sysname,
@article sysname,
@identity_insert bit output,
@artid int = NULL	-- If pass in @artid, you don't need to pass in 
					-- @publication and @article
as
	declare @pubid int, @source_objid int
		,@columns      binary(32)
	
	select @identity_insert = 0

	-- Get @artid if not there
	-- If the publication is not queued
	if @artid is null
	begin
		select @pubid = pubid from syspublications where name = @publication
		select @artid = artid from sysarticles where
			pubid = @pubid and
			name = @article
	end		

	-- Get @pubid from @artid
	if @pubid is null
		select @pubid = pubid	from sysarticles where 
			artid = @artid

	if exists (select * from syspublications where
		pubid = @pubid and
		allow_queued_tran = 1)
	begin
		select @source_objid = objid, @columns = columns from sysarticles where
			artid = @artid and
			OBJECTPROPERTY(objid, 'tablehasidentity') = 1
		if @source_objid is not null
		begin
			declare @colid smallint, @in_partition int
			select @colid = colid from syscolumns where id = @source_objid and
				COLUMNPROPERTY(@source_objid, name, 'IsIdentity') = 1
			exec @in_partition = sp_isarticlecolbitset @colid, @columns
			if @in_partition = 1
				select @identity_insert = 1
		end
	end
	return @identity_insert
go

EXEC dbo.sp_MS_marksystemobject sp_MSis_identity_insert
GO



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

raiserror('Creating procedure sp_MSscript_insert_pubwins', 0,1)
go
create procedure sp_MSscript_insert_pubwins (
	@publication sysname,
	@article     sysname, 
	@objid int,
	@columns binary(32) )
AS
BEGIN
	declare @cmd nvarchar(4000)
			,@artid int
			,@pubid int
			,@dest_table sysname
			,@dest_owner sysname
			,@colname      sysname
			,@ccoltype     sysname
			,@this_col     int
			,@rc           int
			,@num_col	  int
			,@qualname nvarchar(512)
			,@cast_str nvarchar(1000)
			,@decl_str nvarchar(2000)
			,@assign_str nvarchar(4000)
			,@typestring nvarchar(100)
			,@exec_str nvarchar(1000)

	--
	-- initialize the vars we will use
	--
	select @pubid = pubid from syspublications where name = @publication
	select @artid = artid, @dest_table = dest_table, @dest_owner = dest_owner
	from sysarticles where name = @article and pubid = @pubid
	select @dest_owner = case when (@dest_owner IS NULL) then N''
				else quotename(@dest_owner) + N'.' end
	exec sp_MSget_qualified_name @objid, @qualname OUTPUT
				
	--
	-- start script generation
	--
	select @cmd = N'
	else if (@error in (0, 547, 2627) and @execution_mode = @QPubWins)
	begin
		' + N'--
		' + N'-- Publisher Wins resolution
		' + N'-- Find where we have to generate compensating action
		' + N'--
		if (@rowcount = 1 and @error = 0)
		begin'
	insert into #proctext(procedure_text) values( @cmd )

	select @cmd = N'
			' + N'--
			' + N'-- No conflict for this command
			' + N'-- Row does not exist
			' + N'-- Generate delete compensating action
			' + N'--
			select @cftcase = 23'
	insert into #proctext(procedure_text) values( @cmd )

	--
	-- Continue with scripting
	--
	select @cmd = N'
		end
		else if (@rowcount = 0 and @error in (547, 2627))
		begin
			' + N'--
			' + N'-- conflict for this command
			' + N'-- Row already exists
			' + N'-- generate update compensating action
			' + N'-- DELETE compensating command + INSERT compensating command
			' + N'--			
			select @cftcase = 21'
	insert into #proctext(procedure_text) values( @cmd )

	--
	-- continue with scripting
	--			
	select @cmd = N'		
		end
		else
			return -1

		' + N'--
		' + N'-- generate compensating command according to the cases
		' + N'--'
	insert into #proctext(procedure_text) values( @cmd )

	select @cmd = N'
		if (@cftcase in (21,23))
		begin
			' + N'--
			' + N'-- delete compensating command
			' + N'--'
	insert into #proctext(procedure_text) values( @cmd )
	
	--
	-- Generate the delete compensating code
	--
	select @cmd = N'
			select @cmd = ''DELETE ' + @dest_owner + quotename(@dest_table) + N' '' + '
	insert into #proctext(procedure_text) values( @cmd )
	exec dbo.sp_MSscript_where_clause @objid, @columns, 'qcft_comp', NULL, 0, 'ins'
	exec sp_MSscript_compensating_send @pubid, @artid, 0, 1

	select @cmd = N'
		end

		if (@cftcase = 21)
		begin
			' + N'--
			' + N'-- insert compensating command
			' + N'--'
	insert into #proctext(procedure_text) values( @cmd )

	--
	-- generate the compensating insert command
	--
	exec dbo.sp_MSscript_compensating_insert @publication, @article, @objid, @columns, 0

	--
	-- continue with scripting
	--			
	select @cmd = N'		
		end
	end'
	insert into #proctext(procedure_text) values( @cmd )

	--
	-- all done
	--
	return 0	
END
go

EXEC dbo.sp_MS_marksystemobject sp_MSscript_insert_pubwins
GO

grant execute on dbo.sp_MSscript_insert_pubwins to public
go

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

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

create procedure sp_MSscript_compensating_insert (
	@publication sysname,
	@article     sysname, 
	@objid int,
	@columns binary(32),
	@proctype	int = 1)		-- 0 = use new_pk, 1 = use old_pk
AS
BEGIN
	declare @cmd nvarchar(4000)
			,@artid int
			,@pubid int
			,@dest_table sysname
			,@dest_owner sysname
			,@colname      sysname
			,@ccoltype     sysname
			,@this_col     int
			,@rc           int
			,@num_col	  int
			,@qualname nvarchar(512)
			,@cast_str nvarchar(4000)
			,@column_string nvarchar(4000)
			,@ins_cmd nvarchar(255)
			,@startoffset int
			,@setprefix bit
			,@commandlen int
			,@fragmentlen int
			,@collen int
			,@first_time bit
			,@fullcastlen int
			,@splitlen int

	--
	-- initialize the vars we will use
	--
	select @pubid = pubid from syspublications where name = @publication
	select @artid = artid, @dest_table = dest_table, 
		@dest_owner = dest_owner, @ins_cmd = ins_cmd from sysarticles 
	where name = @article and pubid = @pubid
	select @dest_owner = case when (@dest_owner IS NULL) then N''
				else quotename(@dest_owner) + N'.' end
	exec sp_MSget_qualified_name @objid, @qualname OUTPUT

	--
	-- The compensating command will be split into one or more
	-- fragment commands if the length exceeds 3450 characters in length 
	-- (to accomodate compensating server/db names)
	-- For correctly estimating the length of the compensating command
	-- we have to take the max column length of the data into consideration along
	-- with the scripting command length
	--

	--
	-- use the insert command if available
	--
	select @commandlen = 0
			,@setprefix = 1

	if (@ins_cmd = N'SQL')
	begin
		select @cmd = N'
			select @cmd = ''INSERT INTO ' + @dest_owner + quotename(@dest_table) + N''' + 
		 				'' SELECT '' + '
	end
	else
	begin
		select @cmd = N'
			select @cmd = ''EXEC ' + substring(@ins_cmd, 5, len(@ins_cmd) - 4) + N' '' + '
	end
	insert into #proctext(procedure_text) values( @cmd )
 	select @commandlen = @commandlen + len(@cmd)
	
	select @num_col = 0
	DECLARE hCColid CURSOR LOCAL FAST_FORWARD FOR 
	select colid, length from syscolumns where id = @objid order by colid asc

	OPEN hCColid
	FETCH hCColid INTO @this_col, @collen
	WHILE (@@fetch_status != -1)
	begin
		exec @rc = dbo.sp_MSget_colinfo @objid, @this_col, @columns, 1, @colname output, @ccoltype output
		if @rc = 0  and EXISTS (select name from syscolumns where id=@objid and colid=@this_col and iscomputed<>1)
		begin
			if rtrim(@ccoltype) not like N'timestamp' 
			begin
				select @num_col = @num_col + 1

				--
				-- Compute the command fragment length needed for this column
				-- based on the coltype
				--				
				if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('ntext','text','image'))
				begin
					--
					-- For compensating commands we have to include the text and image data
					-- as the custom procs used by Distribution process expects them - as it
					-- done for regular transactional replication - but we will only send NULLs
					-- as it is not possible to ascertain the size of the data during the generation
					--		
					select @cast_str = N' ''null'' '
					select @fullcastlen = len(@cast_str)
					select @fragmentlen = @fullcastlen + 4 + @collen
				end
				else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('varchar','nvarchar','char','nchar'))
				begin
					if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('nvarchar', 'nchar'))
						select @collen = (@collen / 2)
	
					select @cast_str = case 
							when (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('nvarchar', 'nchar'))
								then N' ISNULL(''N'''''' + master.dbo.fn_MSgensqescstr(' + quotename(@colname) + N') collate database_default + '''''''', ''null'') '
								else N' ISNULL('''''''' + master.dbo.fn_MSgensqescstr(' + quotename(@colname) + N') collate database_default + '''''''', ''null'') '
							end
	   
					select @fullcastlen = len(@cast_str)
					select @fragmentlen = @fullcastlen + 4 + @collen
				end
				else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('binary','varbinary'))
				begin
					--
					-- each byte has 2 nibbles - we need a char to represent each nibble
					--
					select @collen = @collen * 2
					select @cast_str = N' ISNULL(master.dbo.fn_varbintohexsubstring(1,' + quotename(@colname) + N',1,0) collate database_default, ''null'') '
					select @fullcastlen = len(@cast_str)
					select @fragmentlen = @fullcastlen + 4 + @collen + 2
				end
				else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('bit','bigint','int','smallint','tinyint','float','real','decimal','numeric'))
				begin
					select @collen = 40
					select @cast_str = N' ISNULL(CAST(' + quotename(@colname) + N' as nvarchar), ''null'') '
					select @fragmentlen = len(@cast_str) + @collen
				end
				else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('money','smallmoney'))
				begin
					select @collen = 40
					select @cast_str = N' ISNULL(CONVERT(nvarchar(40),' + quotename(@colname) + N',2), ''null'') '
					select @fragmentlen = len(@cast_str) + @collen
				end
				else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'uniqueidentifier')
				begin
					select @collen = 40
					select @cast_str = N' ISNULL('''''''' + CAST(' + quotename(@colname) + N' as nvarchar(40)) + '''''''', ''null'') '
					select @fragmentlen = len(@cast_str) + @collen
				end
				else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('datetime','smalldatetime'))
				begin
					select @collen = 40
					select @cast_str = N' ISNULL('''''''' + CONVERT(nvarchar(40), ' + quotename(@colname) + N', 121) + '''''''', ''null'') '
					select @fragmentlen = len(@cast_str) + @collen
				end
				else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'sql_variant')
				begin
					--
					-- need to revisit this later
					--
					select @cast_str = N' ISNULL(master.dbo.fn_sqlvarbasetostr(' + quotename(@colname) + N' ) collate database_default, ''null'') '
					select @fragmentlen = len(@cast_str) + @collen
				end					
				else
				begin
					select @collen = 40
					select @cast_str = N' ISNULL(CAST(' + quotename(@colname) + N' as nvarchar), ''null'') '
					select @fragmentlen = len(@cast_str) + @collen
				end
			
				--
				-- for fixed datatypes - we will not split the data at all we will
				-- flush the command script and continue
				-- for varying/large datatypes, we will have to split data if necessary
				--
				if ((lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('varchar','nvarchar','char','nchar','binary','varbinary')) 
						and (@fragmentlen + @commandlen > 3450))
				begin
			 		--
			 		-- the column length is too big, we have to break the data string
			 		-- initialize
			 		--
					if (@num_col = 1)
					begin
						select @column_string = N'
				' 
					end
					else
					begin
						select @column_string = N'
				+ '','' + ' 
					end

					--
					-- use substring to break the string value in the
					-- compensating command
					--
					select @first_time = 1
							,@startoffset = 1
					while (@collen > 0)
					begin
				 		select @splitlen = case when ((@first_time = 1) or (@collen > 3450))
				 								then (3450 - @commandlen - 30 - @fullcastlen)
				 								else @collen end
				 		if (@splitlen < 1)
				 		begin
				 			--
				 			-- we have overcompensated the splitlen
				 			-- set to half of the column length
				 			--
				 			select @splitlen = @collen / 2
				 		end

						--
						-- Do we need to put quotes (many datatypes need it)
						--
					 	if (@first_time = 1)
					 	begin
							if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('varchar','nvarchar','char','nchar'))
								select @column_string = case 
									when (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('nvarchar', 'nchar'))
										then @column_string + N' ISNULL(''N'''''' + master.dbo.fn_MSgensqescstr( '
										else @column_string + N' ISNULL('''''''' + master.dbo.fn_MSgensqescstr( '
									end
							else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('binary','varbinary'))
								select @column_string = @column_string + N' ISNULL(master.dbo.fn_varbintohexsubstring(1,' 
						end
					 	else
					 	begin
							if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('varchar','nvarchar','char','nchar'))
						 		select @column_string = N' + ISNULL(master.dbo.fn_MSgensqescstr( '
							else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('binary','varbinary'))
								select @column_string = @column_string + N' + ISNULL(master.dbo.fn_varbintohexsubstring(0,' 
					 	end

						--
						-- prepare the substring script
						--
						if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('varchar','nvarchar','char','nchar'))
							select @cast_str = N'SUBSTRING(' + quotename(@colname) + N', ' + cast(@startoffset as nvarchar) + N', ' +  cast(@splitlen as nvarchar) + N')'
						else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('binary','varbinary'))
							select @cast_str = quotename(@colname) + N', ' + cast(@startoffset as nvarchar) + N', ' +  cast((@splitlen/2) as nvarchar)

						if (@first_time = 1)
					 	begin
							select @cast_str = @cast_str + N') collate database_default, ''null'') '
									,@first_time = 0
					 	end
					 	else
					 	begin
							if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('varchar','nvarchar','char','nchar'))
							begin
								--
								-- for strings the last fragment needs the single
								-- quote to be added for the string
								--
								select @cast_str = @cast_str + N') collate database_default '
								select @cast_str = case 
									when (@collen - @splitlen < 1)
										then @cast_str + N'+ '''''''', '''') '											
										else @cast_str + N', '''') ' 
									end
							end
							else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('binary','varbinary'))
								select @cast_str = @cast_str + N') collate database_default, '''') '
						end
						 		
						select @column_string = @column_string + @cast_str
						insert into #proctext(procedure_text) values( @column_string )

						if (@fragmentlen + @commandlen > 3450)
						begin
							select @cmd = N'
			from ' + @qualname 
							insert into #proctext(procedure_text) values( @cmd )
							if (@proctype = 1)
								exec dbo.sp_MSscript_where_clause @objid, @columns, 'upd version', NULL, 0, 'del'
							else
								exec dbo.sp_MSscript_where_clause @objid, @columns, 'new_pk_q', NULL, 0, 'ins'
							exec sp_MSscript_compensating_send @pubid, @artid, 1, @setprefix
							if (@setprefix = 1)
								select @setprefix = 0

							select @cmd = N'
			select @cmd = N''''' 
							insert into #proctext(procedure_text) values( @cmd )
							select @commandlen = 0
						end
						else
							select @commandlen = @commandlen + len(@column_string)

						--
						-- update vars for next round
						--
						select @collen = @collen - @splitlen
								,@column_string = N''
								,@startoffset = case 
									when (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('binary','varbinary')) 
										then (@splitlen/2) + @startoffset 
										else @splitlen + @startoffset 
									end
						select @fragmentlen = @fullcastlen + 4 + @collen
					end							

					--
					-- we done with this column now
					-- skip processing further and continue
					--						
					select @commandlen = @commandlen + len(@column_string)
			 	end
			 	else
			 	begin
					--
					-- Handling general fixed type column cases
					--
					if (@num_col = 1)
					begin
						select @column_string = N'
				' + @cast_str
					end
					else
					begin
						select @column_string = N'
				+ '','' + ' + @cast_str
					end

					--
					-- check if we need to flush the command first
					--
					if (@fragmentlen + len(@column_string) + @commandlen > 3450)
					begin
						--
						-- send this compensating command first
						--
						select @cmd = N'
			from ' + @qualname 
						insert into #proctext(procedure_text) values( @cmd )

						if (@proctype = 1)
							exec dbo.sp_MSscript_where_clause @objid, @columns, 'upd version', NULL, 0, 'del'
						else
							exec dbo.sp_MSscript_where_clause @objid, @columns, 'new_pk_q', NULL, 0, 'ins'
		
						exec sp_MSscript_compensating_send @pubid, @artid, 1, @setprefix
						if (@setprefix = 1)
							select @setprefix = 0

						select @cmd = N'
			select @cmd = N'' ''' 
						insert into #proctext(procedure_text) values( @cmd )
						select @commandlen = 0					
					end

					--
					-- script out the column string
					--
					insert into #proctext(procedure_text) values( @column_string )

					--
					-- if we are processing sql_variants, flush the command again
					--
					if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'sql_variant')
					begin
						--
						-- send this compensating command first
						--
						select @cmd = N'
			from ' + @qualname 
						insert into #proctext(procedure_text) values( @cmd )

						if (@proctype = 1)
							exec dbo.sp_MSscript_where_clause @objid, @columns, 'upd version', NULL, 0, 'del'
						else
							exec dbo.sp_MSscript_where_clause @objid, @columns, 'new_pk_q', NULL, 0, 'ins'
		
						exec sp_MSscript_compensating_send @pubid, @artid, 1, @setprefix
						if (@setprefix = 1)
							select @setprefix = 0

						select @cmd = N'
			select @cmd = N'' ''' 
						insert into #proctext(procedure_text) values( @cmd )
						select @commandlen = 0					
					end
					else
						select @commandlen = @commandlen + @fragmentlen + len(@column_string)
				end
			end
		end

		--
		-- process the next column
		--
		FETCH hCColid INTO @this_col, @collen
	end
	CLOSE hCColid
	DEALLOCATE hCColid

	--
	-- Check if we need to flush the command one more time (final)
	--
	if (@commandlen > 0)
	begin
		--
		-- send the last fragment of the command
		--
		select @cmd = N'
			from ' + @qualname 
		insert into #proctext(procedure_text) values( @cmd )

		if (@proctype = 1)
			exec dbo.sp_MSscript_where_clause @objid, @columns, 'upd version', NULL, 0, 'del'
		else
			exec dbo.sp_MSscript_where_clause @objid, @columns, 'new_pk_q', NULL, 0, 'ins'
		exec sp_MSscript_compensating_send @pubid, @artid, 0, @setprefix
	end

	--
	-- all done
	--
	return 0
END
go

EXEC dbo.sp_MS_marksystemobject sp_MSscript_compensating_insert
GO

grant execute on dbo.sp_MSscript_compensating_insert to public
go

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

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

CREATE PROCEDURE sp_articlesynctranprocs
    @publication sysname,         -- publication name 
    @article sysname,              -- article name 
    @ins_proc sysname,       -- name of sproc supporting Sync Tran inserts associated with this article
    @upd_proc sysname,       -- name of sproc supporting Sync Tran updates associated with this article
    @del_proc sysname,       -- name of sproc supporting Sync Tran deletes associated with this article
    @autogen       nvarchar(5) = 'true', -- indicates wether or not to auto generate sprocs
    @upd_trig sysname
AS
    SET NOCOUNT ON

    -- Declarations.
    DECLARE @pubid int
    DECLARE @artid int
    DECLARE @retcode int
    DECLARE @autogen_id bit
    DECLARE @ins_proc_id int
    DECLARE @upd_proc_id int
    DECLARE @del_proc_id int
    DECLARE @upd_trig_id int
    
    /* 
    ** Security Check.
    */
    exec @retcode = dbo.sp_MSreplcheck_publish
    if @@ERROR <> 0 or @retcode <> 0
        return(1)
    
    -- Parameter Check: @article. The @article name cannot be NULL and must conform 
    -- to the rules for identifiers.
    IF @article IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@article')
            RETURN (1)
        END

    EXECUTE @retcode = dbo.sp_validname @article
    IF @retcode <> 0
        RETURN(1)
    
    -- Parameter Check: @publication.
    -- The @publication name cannot be NULL and must conform to the rules
    -- for identifiers.
    IF @publication IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@publication')
            RETURN (1)
        END

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

    -- Parameter Check: @ins_proc, @upd_proc, @del_proc, @upd_trig
    -- The sproc names cannot be NULL and must conform to the rules
    -- for identifiers

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

    EXECUTE @retcode = dbo.sp_validname @ins_proc
    IF @retcode <> 0
        RETURN (1)

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

    EXECUTE @retcode = dbo.sp_validname @upd_proc
    IF @retcode <> 0
        RETURN (1)

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

    EXECUTE @retcode = dbo.sp_validname @del_proc
    IF @retcode <> 0
        RETURN (1)

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

    EXECUTE @retcode = dbo.sp_validname @upd_trig
    IF @retcode <> 0
        RETURN (1)

    -- Parameter Check:  @autogen
    IF @autogen IS NULL OR LOWER(@autogen collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
    BEGIN
        RAISERROR (14148, 16, -1, '@autogen')
        RETURN (1)
    END

    IF LOWER(@autogen collate SQL_Latin1_General_CP1_CS_AS) = 'true' 
        SELECT @autogen_id = 1
    ELSE 
        SELECT @autogen_id = 0

    -- Retrieve pubid & artid
    SELECT @pubid = a.pubid, @artid = a.artid 
    FROM sysarticles a, syspublications p
    WHERE p.name = @publication AND a.name = @article AND
        a.pubid = p.pubid
    IF @pubid IS NULL OR @artid IS NULL
    BEGIN
        if @pubid IS NULL RAISERROR (20026, 16, 1, @publication)
        if @artid IS NULL RAISERROR (20026, 16, 1, @publication)
        RETURN (1)
    END

       BEGIN TRAN sp_articlesynctranprocs

        -- if @autogen is true, generate the sprocs
        IF @autogen_id = 1
        BEGIN
            EXECUTE @retcode =  dbo.sp_MSgen_sync_tran_procs @publication, @article, 
                @ins_proc, @upd_proc, @del_proc, @upd_trig
            IF @retcode <> 0
            BEGIN
                IF @@TRANCOUNT <> 0
                    ROLLBACK tran sp_articlesynctranprocs
                RETURN (1)
            END
        END

        --retrieve sproc id's, fail if they don't exist
        SELECT @ins_proc_id = id FROM sysobjects WHERE name = @ins_proc
        SELECT @upd_proc_id = id FROM sysobjects WHERE name = @upd_proc
        SELECT @del_proc_id = id FROM sysobjects WHERE name = @del_proc
        select @upd_trig_id = id FROM sysobjects WHERE name = @upd_trig

        IF (@ins_proc_id IS NULL) OR (@upd_proc_id IS NULL) OR (@del_proc_id IS NULL) OR
            (@upd_trig_id IS NULL)             
        BEGIN
            if @ins_proc_id IS NULL RAISERROR (20500, 16, 1, @ins_proc)
            if @upd_proc_id IS NULL RAISERROR (20500, 16, 1, @upd_proc)
            if @del_proc_id IS NULL RAISERROR (20500, 16, 1, @del_proc)
            if @upd_trig_id IS NULL RAISERROR (20500, 16, 1, @upd_trig)
            IF @@TRANCOUNT <> 0
                ROLLBACK tran sp_articlesynctranprocs
            RETURN (1)
        END

        -- perform insert into sysarticleupdates
        -- need to mark this as a system table, so this sproc can live in master db
        INSERT sysarticleupdates(pubid, artid, sync_ins_proc, sync_upd_proc, sync_del_proc, autogen, sync_upd_trig)
            VALUES (@pubid, @artid, @ins_proc_id, @upd_proc_id, @del_proc_id, @autogen_id, @upd_trig_id)

        IF @@ERROR <> 0
            BEGIN
                IF @@TRANCOUNT <> 0
                    ROLLBACK tran sp_articlesynctranprocs
                RETURN (1)
            END

    COMMIT TRAN
go

EXEC dbo.sp_MS_marksystemobject sp_articlesynctranprocs
GO

grant exec on dbo.sp_articlesynctranprocs to public
go

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

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

create procedure sp_script_reconciliation_insproc (
	@artid int)
as
BEGIN
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 @ins_cmd      nvarchar(255)
declare @dest_proc    sysname
declare @this_col     int
declare @art_col      int
declare @isset        int

declare @typestring   nvarchar(255)
declare @spacer       nvarchar(1)
	, @identity_insert bit
	

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

-------- get sysarticles information

select @dest_owner = dest_owner, @dest_tabname = dest_table, 
       @src_objid = objid, @artcolumns = columns, @ins_cmd = ins_cmd
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

-- Check to see if identity insert must be turned on
-- i.e. Does the table has identity that are included in the partition?
exec sp_MSis_identity_insert null, null, @identity_insert output, @artid
--

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

if( 1 != charindex( N'CALL', upper(@ins_cmd collate SQL_Latin1_General_CP1_CS_AS) ) ) or @ins_cmd is null
begin
    raiserror (14156, 16, 1 )
    return 1
end

select @dest_proc = substring( @ins_cmd, 6, len( @ins_cmd ) - 4 )
select @cmd = N'create procedure ' + QUOTENAME(@dest_proc) + N';2'

-------- construct parameter list


select @art_col = 1
select @spacer = 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 and EXISTS (select name from syscolumns where id=@src_objid and @this_col=colid and iscomputed<>1)
   begin
        if len( @cmd ) > 3000
        begin
        insert into #proctext(procedure_text) values( @cmd )
            select @cmd = N''
        end

        exec dbo.sp_gettypestring @src_objid, @this_col, @typestring OUTPUT
        select @cmd = @cmd + @spacer + N'@c' + convert( nvarchar, @art_col ) + N' ' + @typestring 
        select @art_col = @art_col + 1
        select @spacer = N','
   end
   FETCH hCColid INTO @this_col
end
CLOSE hCColid
DEALLOCATE hCColid


-- save off cmd fragment

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

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

------- construct proc body

---- if already exists, apply as update

insert into #proctext(procedure_text) 
    values( N'if exists ( select * from ' + @dest_owner + QUOTENAME(@dest_tabname) )
exec dbo.sp_getarticlepkcolbitmap @src_objid, @pkcolumns output
exec dbo.sp_scriptreconwhereclause @src_objid, @pkcolumns, @artcolumns
insert into #proctext(procedure_text) values( N')' )
insert into #proctext(procedure_text) values (N'begin')


if( @artcolumns != @pkcolumns )
begin
	-- construct update 

	select @cmd = N'update ' + @dest_owner + QUOTENAME(@dest_tabname) + N' set'

	-- create SET clause

	select @art_col = 1
	select @spacer = 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  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 (@identity_insert = 1 and 
					columnproperty(@src_objid, col_name( @src_objid, @this_col), 'IsIdentity') = 1)
				begin
					select @cmd = @cmd + @spacer + QUOTENAME(col_name( @src_objid, @this_col)) + N' = @c' + convert( nvarchar, @art_col ) 
					select @spacer = N','

					if len( @cmd ) > 3000
					begin
						insert into #proctext(procedure_text) values( @cmd )
						select @cmd = N''
					end
				end
			end
			select @art_col = @art_col + 1
		end
		FETCH hCColid INTO @this_col
   	end
	CLOSE hCColid
	DEALLOCATE hCColid

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

	exec dbo.sp_scriptreconwhereclause @src_objid, @pkcolumns, @artcolumns
end

-- all article columns are included in the PK, & PK already exists, do nothing
else 
begin
	insert into #proctext(procedure_text ) values( N'return' )
end


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

---- normal insert

-- set identity_insert on
if @identity_insert = 1
begin
	select @cmd = N'
set identity_insert ' + @dest_owner + QUOTENAME(@dest_tabname) + ' on'
	insert into #proctext(procedure_text) values( @cmd )
end

-- prepare the column list

select @cmd = N'insert into ' + @dest_owner + QUOTENAME(@dest_tabname) + N' ('
select @art_col = 1
select @spacer = 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 and EXISTS (select name from syscolumns where colid=@this_col and iscomputed<>1 and id = @src_objid)
    begin
        if len( @cmd ) > 3000
        begin
        insert into #proctext(procedure_text) values( @cmd )
            select @cmd = N''
        end

        select @cmd = @cmd + @spacer + QUOTENAME(col_name( @src_objid, @this_col))  
        select @art_col = @art_col + 1
        select @spacer = N','
    end
	FETCH hCColid INTO @this_col
end
CLOSE hCColid
DEALLOCATE hCColid

-- now the data parameter list

select @cmd = @cmd + N' ) values ('
select @art_col = 1
select @spacer = 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 and EXISTS (select name from syscolumns where colid=@this_col and iscomputed<>1 and id = @src_objid)
    begin
        if len( @cmd ) > 3000
        begin
        insert into #proctext(procedure_text) values( @cmd )
            select @cmd = N''
        end

        select @cmd = @cmd + @spacer + N'@c' + convert( nvarchar, @art_col ) 
        select @art_col = @art_col + 1
        select @spacer = N','
    end
	FETCH hCColid INTO @this_col
end
CLOSE hCColid
DEALLOCATE hCColid

-- finish up proc body

select @cmd = @cmd + N' )'

-- save off cmd fragement

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

-- set identity_insert off
if @identity_insert = 1
begin
	select @cmd = N'
set identity_insert ' + @dest_owner + QUOTENAME(@dest_tabname) + ' off'
	insert into #proctext(procedure_text) values( @cmd )
end

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

-- send fragments to client

select procedure_text from #proctext order by c1 asc
END
go

EXEC dbo.sp_MS_marksystemobject sp_script_reconciliation_insproc
GO

grant exec on dbo.sp_script_reconciliation_insproc to public
go

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

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


create procedure sp_script_reconciliation_delproc @artid int
as
declare @cmd          nvarchar(4000)
declare @dest_owner   nvarchar(255)
declare @dest_tabname sysname
declare @src_objid    int
declare @pkcolumns    binary(32)
declare @del_cmd      nvarchar(255)
declare @dest_proc    sysname
declare @this_col     int
declare @art_col      int
declare @isset        int

declare @typestring   nvarchar(255)
declare @spacer       nvarchar(10)

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

-- get sysarticles information

select @dest_owner = dest_owner, @dest_tabname = dest_table, 
       @src_objid = objid, @del_cmd = del_cmd
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'CALL', upper(@del_cmd collate SQL_Latin1_General_CP1_CS_AS) ) ) or @del_cmd is null
begin
    raiserror (14156, 16, 1 )
    return 1
end

select @dest_proc = substring( @del_cmd, 6, len( @del_cmd ) - 4 )
select @cmd = N'create procedure ' + QUOTENAME(@dest_proc) + N';2'


-------- construct parameter list


select @art_col = 1
select @spacer = N' '

exec dbo.sp_getarticlepkcolbitmap @src_objid, @pkcolumns output

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, @pkcolumns
   if @isset != 0 and EXISTS (select name from syscolumns where id=@src_objid and @this_col=colid )
   begin
        if len( @cmd ) > 3000
        begin
        insert into #proctext(procedure_text) values( @cmd )
            select @cmd = N''
        end

        exec dbo.sp_gettypestring @src_objid, @this_col, @typestring OUTPUT
        select @cmd = @cmd + @spacer + N'@pkc' + convert( nvarchar, @art_col ) + N' ' + @typestring 
        select @art_col = @art_col + 1
        select @spacer = N','
   end
	FETCH hCColid INTO @this_col
end

CLOSE hCColid
DEALLOCATE hCColid

-- save off 

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

------- construct proc body

insert into #proctext(procedure_text) values( N'delete ' + @dest_owner + QUOTENAME(@dest_tabname) ) 

exec dbo.sp_scriptpkwhereclause @src_objid, @pkcolumns

-- flush to client

select procedure_text from #proctext order by c1 asc


go

EXEC dbo.sp_MS_marksystemobject sp_script_reconciliation_delproc
GO

grant exec on dbo.sp_script_reconciliation_delproc to public
go

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

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

create procedure sp_script_reconciliation_xdelproc @artid int
as
declare @cmd          nvarchar(4000)
declare @dest_owner   nvarchar(255)
declare @dest_tabname sysname
declare @src_objid    int
declare @columns    binary(32)
declare @pkcolumns    binary(32)
declare @del_cmd      nvarchar(255)
declare @dest_proc    sysname
declare @this_col     int
declare @art_col      int
declare @isset        int

declare @typestring   nvarchar(255)
declare @spacer       nvarchar(10)

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

-- get sysarticles information

select @dest_owner = dest_owner, @dest_tabname = dest_table, 
       @src_objid = objid, @del_cmd = del_cmd, @columns = columns
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'XCALL', upper(@del_cmd collate SQL_Latin1_General_CP1_CS_AS) ) ) or @del_cmd is null
begin
    raiserror (14156, 16, 1 )
    return 1
end

select @dest_proc = substring( @del_cmd, 7, len( @del_cmd ) - 5 )
select @cmd = N'create procedure ' + QUOTENAME(@dest_proc) + N';2'

-------- construct parameter list


select @art_col = 1
select @spacer = N' '

exec dbo.sp_getarticlepkcolbitmap @src_objid, @pkcolumns output

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, @columns
   if @isset != 0 and EXISTS (select name from syscolumns where id=@src_objid and @this_col=colid and iscomputed<>1)
   begin
        if len( @cmd ) > 3000
        begin
        insert into #proctext(procedure_text) values( @cmd )
            select @cmd = N''
        end

        exec dbo.sp_gettypestring @src_objid, @this_col, @typestring OUTPUT
        select @cmd = @cmd + @spacer + N'@c' + convert( nvarchar, @art_col ) + N' ' + @typestring 
        select @art_col = @art_col + 1
        select @spacer = N','
   end
   FETCH hCColid INTO @this_col
end

CLOSE hCColid
DEALLOCATE hCColid

-- save off 

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

------- construct proc body

insert into #proctext(procedure_text) values( N'delete ' + @dest_owner + QUOTENAME(@dest_tabname) ) 

exec dbo.sp_scriptpkwhereclause @src_objid, @pkcolumns, N'@c', @columns

-- flush to client

select procedure_text from #proctext order by c1 asc

go

EXEC dbo.sp_MS_marksystemobject sp_script_reconciliation_xdelproc
GO

grant exec on dbo.sp_script_reconciliation_xdelproc to public
go

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

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


create procedure sp_scriptinsproc (
	@artid int)
as
BEGIN
declare @cmd          nvarchar(4000)
		,@dest_owner   nvarchar(255)
		,@dest_tabname sysname
		,@src_objid    int
		,@columns      binary(32)
		,@ins_cmd      nvarchar(255)
		,@dest_proc    sysname
		,@this_col     int
		,@art_col      int
		,@isset        int
		,@pubid		  int

		,@identity_insert bit
		,@rc int
		,@colname sysname
		,@ccoltype     sysname
		,@typestring   nvarchar(255)
		,@spacer       nvarchar(1)
		,@queued_check bit
		,@column_string nvarchar(4000)
		,@var_string nvarchar(4000)
		,@qwhere_string nvarchar(4000)
		,@indkey      int
		,@indid       int

set nocount on

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 and insert column list

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

-------- get sysarticles information

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

-- Check to see if identity insert must be turned on
-- i.e. Does the table has identity that are included in the partition?
exec sp_MSis_identity_insert null, null, @identity_insert output, @artid
--

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

-- Check if this is a queued publication
select @queued_check = ISNULL(allow_queued_tran, 0) 
from syspublications
where pubid = @pubid

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

if( 1 != charindex( N'CALL', upper(@ins_cmd collate SQL_Latin1_General_CP1_CS_AS) ) ) or @ins_cmd is null
begin
    raiserror (14156, 16, 1 )
    return 1
end

select @dest_proc = substring( @ins_cmd, 6, len( @ins_cmd ) - 4 )
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' )
select @cmd = N'create procedure ' + QUOTENAME(@dest_proc)

-------- construct parameter list


select @art_col = 1
select @spacer = 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, @columns
   if @isset != 0 and EXISTS (select name from syscolumns where id=@src_objid and @this_col=colid and iscomputed<>1)
   begin
        if len( @cmd ) > 3000
        begin
        insert into #proctext(procedure_text) values( @cmd )
            select @cmd = N''
        end

        exec dbo.sp_gettypestring @src_objid, @this_col, @typestring OUTPUT
        select @cmd = @cmd + @spacer + N'@c' + convert( nvarchar, @art_col ) + N' ' + @typestring 
        select @art_col = @art_col + 1
        select @spacer = N','
   end
   FETCH hCColid INTO @this_col
end

CLOSE hCColid
DEALLOCATE hCColid

-- save off cmd fragment

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

select @cmd = N'
AS
BEGIN
'
insert into #proctext(procedure_text) values( @cmd )

------- construct proc body

-- For Queued case, build a temp table of index columns
-- we also need temp tables for where clause list
if (@queued_check = 1)
begin
	declare @source_object nvarchar(540)

	--
	-- Get the object owner name and build a owner qualified 
	-- source object name
	--
	select @source_object = QUOTENAME(u.name) 
	from sysusers u, sysobjects o 
	where o.id = @src_objid and o.uid = u.uid

	select @source_object = @source_object + N'.' + QUOTENAME(OBJECT_NAME(@src_objid))

	create table #wherecltab ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)	
	create table #indcoltab ( colname sysname collate database_default )
	
	exec @indid = dbo.sp_MStable_has_unique_index @src_objid
	select @indkey = 1;	
	while (@indkey <= 16)
	begin
		select @colname = index_col(@source_object, @indid, @indkey)
		if (@colname is NULL) 
			select @indkey = 16
		else
			insert into #indcoltab(colname) values(@colname)

		select @indkey = @indkey + 1
	end
end

-- Generate strings for col names and variables
select @art_col = 0
select @spacer = 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 @rc = dbo.sp_MSget_colinfo @src_objid, @this_col, @columns, 1, @colname output, @ccoltype output
	if @rc = 0  and EXISTS (select name from syscolumns where id=@src_objid and colid=@this_col and iscomputed<>1)
	begin
		select @art_col = @art_col + 1
		if (@art_col = 1)
		begin
			select @column_string = QUOTENAME(@colname)
			select @var_string = N'@c' + cast(@art_col as nvarchar(4))
		end
		else
		begin
			select @column_string = @column_string + N', ' + QUOTENAME(@colname)
			select @var_string = @var_string + N', @c' + cast(@art_col as nvarchar(4))
		end

		-- transfer column list string to table if too large
		if (len(@column_string) > 3000)
		begin
			insert into #collisttab(procedure_text) values( @column_string )
			select @column_string = ' '
		end

		-- queued processing : add to where clause if column part of PK
		if (@queued_check = 1)
		begin
			if (@colname in (select colname from #indcoltab))
			begin
				select @qwhere_string = case 
					when (@qwhere_string IS NULL) then 
						QUOTENAME(@colname) + N'=@c' + cast(@art_col as nvarchar(4))
					else
						@qwhere_string + N' and ' + QUOTENAME(@colname) + N'=@c' + cast(@art_col as nvarchar(4))
					end

				-- transfer where clause string to table if too large
				if (len(@qwhere_string) > 3000)
				begin
					insert into #wherecltab(procedure_text) values( @qwhere_string )
					select @qwhere_string = ' '
				end
			end
		end
	end
	FETCH hCColid INTO @this_col
end

CLOSE hCColid
DEALLOCATE hCColid

-- insert the remaining strings for column list and where clause
insert into #collisttab(procedure_text) values( @column_string )
if (@queued_check = 1)
	insert into #wherecltab(procedure_text) values( @qwhere_string )

--
-- If we are a part of queued publication then 
-- insert only if PK does not exist
--
if (@queued_check = 1)
begin
	select @cmd = N'
	if not exists (select * from ' + @dest_owner + QUOTENAME(@dest_tabname) + N' 
		where '
	insert into #proctext(procedure_text) values( @cmd )
	insert into #proctext(procedure_text) 
		select procedure_text from #wherecltab order by c1 asc
	select @cmd = N')
	BEGIN'
	insert into #proctext(procedure_text) values( @cmd )
	drop table #wherecltab
	drop table #indcoltab
end


-- set identity_insert on
if @identity_insert = 1
begin
	select @cmd = N'
	set identity_insert ' + @dest_owner + QUOTENAME(@dest_tabname) + ' on'
	insert into #proctext(procedure_text) values( @cmd )
end


--
-- prepare the insert statement now
--
select @cmd = N'
insert into ' +  @dest_owner + QUOTENAME(@dest_tabname)  + N'( '
insert into #proctext(procedure_text) values( @cmd )
insert into #proctext(procedure_text) 
	select procedure_text from #collisttab order by c1 asc
select @cmd = N' )'
insert into #proctext(procedure_text) values( @cmd )
if @art_col > 0
begin
	select @cmd = N'
values ( '
	insert into #proctext(procedure_text) values( @cmd )
	insert into #proctext(procedure_text) values( @var_string )
	select @cmd = N' )
'
	insert into #proctext(procedure_text) values( @cmd )
end

-- set identity_insert off
if @identity_insert = 1
begin
	select @cmd = N'
	set identity_insert ' + @dest_owner + QUOTENAME(@dest_tabname) + ' off'
	insert into #proctext(procedure_text) values( @cmd )
end

--
-- If we are a part of queued publication then 
-- add the block delimiter
--
drop table #collisttab
if (@queued_check = 1)
begin
	select @cmd = N'
	END
END'
end
else
	select @cmd = N'
END'
insert into #proctext(procedure_text) values( @cmd )

-- send fragements to client

select procedure_text from #proctext order by c1 asc
END
go

EXEC dbo.sp_MS_marksystemobject sp_scriptinsproc
GO

grant exec on dbo.sp_scriptinsproc to public
go


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

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

create procedure sp_scriptdelproc @artid int
as
declare @cmd          nvarchar(4000)
declare @dest_owner   nvarchar(255)
declare @dest_tabname sysname
declare @src_objid    int
declare @pkcolumns    binary(32)
declare @del_cmd      nvarchar(255)
declare @dest_proc    sysname
declare @this_col     int
declare @art_col      int
declare @isset        int, @pubid int

declare @typestring   nvarchar(255)
declare @spacer       nvarchar(10)

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

-- get sysarticles information

select @dest_owner = dest_owner, @dest_tabname = dest_table, 
       @src_objid = objid, @del_cmd = del_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'CALL', upper(@del_cmd collate SQL_Latin1_General_CP1_CS_AS) ) ) or @del_cmd is null
begin
    raiserror (14156, 16, 1 )
    return 1
end

select @dest_proc = substring( @del_cmd, 6, len( @del_cmd ) - 4 )
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' )
select @cmd = N'create procedure ' + QUOTENAME(@dest_proc)

-------- construct parameter list


select @art_col = 1
select @spacer = N' '

exec dbo.sp_getarticlepkcolbitmap @src_objid, @pkcolumns output

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, @pkcolumns
   if @isset != 0 and EXISTS (select name from syscolumns where id=@src_objid and @this_col=colid )
   begin
        if len( @cmd ) > 3000
        begin
        insert into #proctext(procedure_text) values( @cmd )
            select @cmd = N''
        end

        exec dbo.sp_gettypestring @src_objid, @this_col, @typestring OUTPUT
        select @cmd = @cmd + @spacer + N'@pkc' + convert( nvarchar, @art_col ) + N' ' + @typestring 
        select @art_col = @art_col + 1
        select @spacer = N','
   end
	FETCH hCColid INTO @this_col
end

CLOSE hCColid
DEALLOCATE hCColid

-- save off 

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

------- construct proc body

insert into #proctext(procedure_text) values( N'delete ' + @dest_owner + QUOTENAME(@dest_tabname) ) 

exec dbo.sp_scriptpkwhereclause @src_objid, @pkcolumns

if exists (select * from syspublications where pubid = @pubid and allow_queued_tran = 0)
	exec dbo.sp_MSscript_missing_row_check
-- flush to client

select procedure_text from #proctext order by c1 asc


go

EXEC dbo.sp_MS_marksystemobject sp_scriptdelproc
GO

grant exec on dbo.sp_scriptdelproc to public
go

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

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

create procedure sp_scriptxdelproc @artid int
as
declare @cmd          nvarchar(4000)
declare @dest_owner   nvarchar(255)
declare @dest_tabname sysname
declare @src_objid    int
declare @columns    binary(32)
declare @pkcolumns    binary(32)
declare @del_cmd      nvarchar(255)
declare @dest_proc    sysname
declare @this_col     int
declare @art_col      int
declare @isset        int
declare @pubid		  int
declare @typestring   nvarchar(255)
declare @spacer       nvarchar(10)
declare @queued_check bit
		,@qwhere_string nvarchar(4000)

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

-- get sysarticles information

select @pubid = pubid, @dest_owner = dest_owner, @dest_tabname = dest_table, 
       @src_objid = objid, @del_cmd = del_cmd, @columns = columns
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

-- Check if this is a queued publication
select @queued_check = ISNULL(allow_queued_tran, 0) 
from syspublications
where pubid = @pubid

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

if( 1 != charindex( N'XCALL', upper(@del_cmd collate SQL_Latin1_General_CP1_CS_AS) ) ) or @del_cmd is null
begin
    raiserror (14156, 16, 1 )
    return 1
end

select @dest_proc = substring( @del_cmd, 7, len( @del_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' )
select @cmd = N'create procedure ' + QUOTENAME(@dest_proc)

-------- construct parameter list


select @art_col = 1
select @spacer = N' '

exec dbo.sp_getarticlepkcolbitmap @src_objid, @pkcolumns output

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, @columns
   if @isset != 0 and EXISTS (select name from syscolumns where id=@src_objid and @this_col=colid and iscomputed<>1)
   begin
        if len( @cmd ) > 3000
        begin
        insert into #proctext(procedure_text) values( @cmd )
            select @cmd = N''
        end

        exec dbo.sp_gettypestring @src_objid, @this_col, @typestring OUTPUT
        select @cmd = @cmd + @spacer + N'@c' + convert( nvarchar, @art_col ) + N' ' + @typestring 

		--
		-- Queued processing:if this is the row version column : need to add to where clause
		--
		if ((@queued_check = 1) and (col_name( @src_objid, @this_col) = N'msrepl_tran_version'))
			select @qwhere_string = N' and msrepl_tran_version = @c' + convert( nvarchar, @art_col )

        select @art_col = @art_col + 1
        select @spacer = N','
   end
   FETCH hCColid INTO @this_col
end

CLOSE hCColid
DEALLOCATE hCColid

-- save off 

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

------- construct proc body

insert into #proctext(procedure_text) values( N'delete ' + @dest_owner + QUOTENAME(@dest_tabname) ) 

exec dbo.sp_scriptpkwhereclause @src_objid, @pkcolumns, N'@c', @columns
if (@queued_check = 1)
	insert into #proctext(procedure_text) values( @qwhere_string )
else
	exec dbo.sp_MSscript_missing_row_check

-- flush to client

select procedure_text from #proctext order by c1 asc


go

EXEC dbo.sp_MS_marksystemobject sp_scriptxdelproc
GO

grant exec on dbo.sp_scriptxdelproc to public
go

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


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

create procedure sp_scriptupdproc @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 @this_col     int
declare @art_col      int
declare @pkart_col    int
declare @isset        int
declare @pkcomputed   int
declare @typestring   nvarchar(255)
declare @spacer       nvarchar(10)
declare @pubid        int, @allow_queued_tran bit, @need_end bit

select @need_end = 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 null)

-------- get sysarticles information

select @pubid = pubid, @dest_owner = dest_owner, @dest_tabname = dest_table, 
       @src_objid = objid, @artcolumns = columns, @upd_cmd = upd_cmd
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'CALL', upper(@upd_cmd collate SQL_Latin1_General_CP1_CS_AS) ) ) 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, 6, len( @upd_cmd ) - 4 )
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

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

-------- now create the update statement

-- construct test to see if pk has changed 
-- only do this if the article has columns not included in the pk

exec @pkcomputed = sp_MSareallcolumnscomputed @src_objid, @pkcolumns

declare @pk_is_identity bit
select @pk_is_identity = 0

if @artcolumns != @pkcolumns and @pkcomputed = 0
begin
    select @cmd = N'if'

    select @art_col = 1
    select @pkart_col = 1
    select @spacer = ' '

	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
					select @cmd = @cmd + @spacer + N'@c'+convert( nvarchar, @art_col ) + N' = @pkc' + convert( nvarchar, @pkart_col ) 
					select @spacer = N' and '
					select @pkart_col = @pkart_col + 1
					if len( @cmd ) > 3000
					begin
					insert into #proctext(procedure_text) values( @cmd )
						select @cmd = N''
					end
				end
            end
            select @art_col = @art_col + 1
        end
		FETCH hCColid INTO @this_col
    end

	CLOSE hCColid
	DEALLOCATE hCColid

	if @pk_is_identity = 0
	begin
		insert into #proctext(procedure_text) values( @cmd )

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

		-- construct update if pk hasn't changed

		select @cmd = N'update ' + @dest_owner + QUOTENAME(@dest_tabname) + N' set'

		-- create SET clause

		select @art_col = 1
		select @spacer = 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  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 @cmd = @cmd + @spacer + QUOTENAME(col_name( @src_objid, @this_col)) + N' = @c' + convert( nvarchar, @art_col ) 
						select @spacer = N','

						if len( @cmd ) > 3000
						begin
							insert into #proctext(procedure_text) values( @cmd )
							select @cmd = N''
						end
					end
				end
				select @art_col = @art_col + 1
			end
			FETCH hCColid INTO @this_col
		end
		CLOSE hCColid
		DEALLOCATE hCColid

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

		exec dbo.sp_scriptpkwhereclause @src_objid, @pkcolumns

		if @allow_queued_tran <> 1
			exec dbo.sp_MSscript_missing_row_check

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

		select @need_end = 1

	end
end -- end if artcols != pkcols


-- construct update if pk has changed

select @cmd = N'update ' + @dest_owner + QUOTENAME(@dest_tabname) + N' set'

-- create SET clause

select @art_col = 1
select @spacer = 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 and EXISTS (select name from syscolumns where id=@src_objid and @this_col=colid and iscomputed<>1)
    begin
		if not (@keep_identity = 1 and 
			columnproperty(@src_objid, col_name( @src_objid, @this_col), 'IsIdentity') = 1)
		begin
			select @cmd = @cmd + @spacer + QUOTENAME(col_name( @src_objid, @this_col)) + N' = @c' + convert( nvarchar, @art_col ) 
			select @spacer = N','

			if len( @cmd ) > 3000
			begin
			insert into #proctext(procedure_text) values( @cmd )
				select @cmd = N''
			end
		end
		select @art_col = @art_col + 1
    end
	FETCH hCColid INTO @this_col
end

CLOSE hCColid
DEALLOCATE hCColid

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

exec dbo.sp_scriptpkwhereclause @src_objid, @pkcolumns

if @allow_queued_tran <> 1
	exec dbo.sp_MSscript_missing_row_check

if @need_end = 1
	insert into #proctext(procedure_text) values( N'end' )
-- flush to client

select procedure_text from #proctext order by c1 asc

go

EXEC dbo.sp_MS_marksystemobject sp_scriptupdproc
GO

grant exec on dbo.sp_scriptupdproc to public
go

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

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

create procedure sp_scriptmappedupdproc @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 @pkart_col    int
declare @isset        int
declare @bytestr      nvarchar(10)
declare @bitstr       nvarchar(10)
declare @typestring   nvarchar(255)
declare @spacer       nvarchar(10)
declare @update_created  bit
declare @pkcomputed   int
declare @pubid        int, @allow_queued_tran bit

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 )

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


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


-- construct IF statement to examine colbitmap and determine if a 
-- primary key column has been updated.  

-- do this only if the article contains columns not included in the pk
-- and at least one of the columns in the PK is real ( i.e. not computed )

-- note that if all the article columns are PK columns, we will
-- construct the 'update all columns including PK columns' statement
-- w/o a preceeding IF, and we will NOT construct the 'only update non-pk columns' 
-- part of the procedure

-- also note that this is pretty much worthless since 7.0 and above are
-- guaranteed to NEVER generate an UPDATE if a PK columns is updated... oh well.
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
    select @spacer = N' '

    select @cmd = N'if'

	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
					select @bytestr = convert( nvarchar, 1 + (@art_col-1) / 8 )
					select @bitstr =  convert( nvarchar, power(2, (@art_col-1) % 8 ) )

					select @cmd = @cmd + @spacer + N'substring(@bitmap,' + @bytestr + N',1) & ' + @bitstr +  
							 N' = ' + @bitstr 
                
					select @spacer = N' or '

					if len( @cmd ) > 3000
					begin
						insert into #proctext(procedure_text) values( @cmd )
						select @cmd = N''
					end
				end
            end
            select @art_col = @art_col + 1
        end
		FETCH hCColid INTO @this_col
    end
	CLOSE hCColid
	DEALLOCATE hCColid

	if @pk_is_identity = 0
		insert into #proctext(procedure_text) values( @cmd )

end  -- if artcolumns != pkcolumns

-- construct update statement including PK columns

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

insert into #proctext(procedure_text) values( N'update ' + @dest_owner + QUOTENAME(@dest_tabname) + N' set' )

-- create SET clause consisting of CASE statements

select @art_col = 1
select @spacer = 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  and EXISTS (select name from syscolumns where colid=@this_col and iscomputed<>1 and id = @src_objid)
    begin
		if not (@keep_identity = 1 and 
			columnproperty(@src_objid, col_name( @src_objid, @this_col), 'IsIdentity') = 1)
		begin
			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 (
				 @spacer + QUOTENAME(col_name( @src_objid, @this_col)) + N' = case substring(@bitmap,' + @bytestr + N',1) & ' + @bitstr +  
				 N' when ' + @bitstr + N' then ' + N'@c'+ convert( nvarchar, @art_col ) + 
				 N' else ' + QUOTENAME(col_name( @src_objid, @this_col)) + N' end' )

			select @spacer = ',' 
		end

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

-- create where clause

exec dbo.sp_scriptpkwhereclause @src_objid, @pkcolumns

if @allow_queued_tran <> 1
	exec dbo.sp_MSscript_missing_row_check

-- construct UPDATE that does not set PK cols
-- only do this if the article contains columns that are not included
-- in the pk

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 = 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  and EXISTS (select name from syscolumns where colid=@this_col and iscomputed<>1 and id = @src_objid)
        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
            		if (@update_created = 0)
            		begin
            			insert into #proctext(procedure_text) values( N'end' )
            			insert into #proctext(procedure_text) values( N'else' )
            			insert into #proctext(procedure_text) values( N'begin' )
			    		insert into #proctext(procedure_text) values( N'update ' + @dest_owner + QUOTENAME(@dest_tabname) + 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 (
						 @spacer + QUOTENAME(col_name( @src_objid, @this_col)) + N' = case substring(@bitmap,' + @bytestr + N',1) & ' + @bitstr +  
						 N' when ' + @bitstr + N' then ' + N'@c'+ convert( nvarchar, @art_col ) + 
						 N' else ' + QUOTENAME(col_name( @src_objid, @this_col)) + N' end' )

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

    if @update_created = 1
	begin
		exec dbo.sp_scriptpkwhereclause @src_objid, @pkcolumns
		if @allow_queued_tran <> 1
			exec dbo.sp_MSscript_missing_row_check
	end

end

insert into #proctext(procedure_text) values( N'end' )
select procedure_text from #proctext order by c1 asc

go

EXEC dbo.sp_MS_marksystemobject sp_scriptmappedupdproc
GO

grant exec on dbo.sp_scriptmappedupdproc to public
go

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

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


create procedure sp_scriptxupdproc @artid int
as
declare @cmd          nvarchar(4000),
		@dest_owner   nvarchar(255),
		@dest_tabname sysname,
		@src_objid    int,
		@artcolumns   binary(32),
		@pkcolumns    binary(32),
		@upd_cmd      nvarchar(255),
		@dest_proc    sysname,
		@this_col     int,
		@art_col      int,
		--@pkart_col    int,
		@isset        int,
		@pkcomputed   int,
		@typestring   nvarchar(255),
		@spacer       nvarchar(10),
		@pubid		  int, 
		@param_count  int
		,@queued_check bit, @exists_else bit
		,@qwhere_string nvarchar(4000)

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

select @exists_else = 0

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

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

-------- get sysarticles information

select @pubid = pubid, @dest_owner = dest_owner, @dest_tabname = dest_table, 
       @src_objid = objid, @artcolumns = columns, @upd_cmd = upd_cmd
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

-- Check if this is a queued publication
select @queued_check = ISNULL(allow_queued_tran, 0) 
from syspublications
where pubid = @pubid

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

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

declare @keep_identity bit
if exists (select * from syspublications where pubid = @pubid and
	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

-- Send null as @pkcolumns. We don't need pk parameters.
exec dbo.sp_scriptupdateparams @src_objid, @artcolumns, NULL, @param_count output

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

-------- now create the update statement

-- construct test to see if pk has changed 
-- only do this if the article has columns not included in the pk

exec @pkcomputed = sp_MSareallcolumnscomputed @src_objid, @pkcolumns

declare @pk_is_identity bit
select @pk_is_identity = 0

if @artcolumns != @pkcolumns and @pkcomputed = 0
begin
    select @cmd = N'if'

    select @art_col = 1
    --select @pkart_col = 1
    select @spacer = ' '

	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
					select @cmd = @cmd + @spacer + N'@c'+convert( nvarchar, @art_col + @param_count/2) + 
						N' = @c' + convert( nvarchar, @art_col ) 
					select @spacer = N' and '
					--select @pkart_col = @pkart_col + 1
					if len( @cmd ) > 3000
					begin
					insert into #proctext(procedure_text) values( @cmd )
						select @cmd = N''
					end
				end
            end
            select @art_col = @art_col + 1
        end
		FETCH hCColid INTO @this_col
    end

	CLOSE hCColid
	DEALLOCATE hCColid

	if @pk_is_identity = 0
	begin
		insert into #proctext(procedure_text) values( @cmd )

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

		-- construct update if pk hasn't changed
		-- We know that there would be a least one column for the update below, even if
		-- the columns outside the pk are identity or timestamp. Since identity and timestamp
		-- will be mapped off unless it is queued tran. In that case, we have 'msrepl_tran_version'
		select @cmd = N'update ' + @dest_owner + QUOTENAME(@dest_tabname) + N' set'

		-- create SET clause

		select @art_col = 1
		select @spacer = 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  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 @cmd = @cmd + @spacer + QUOTENAME(col_name( @src_objid, @this_col)) + 
							N' = @c' + convert( nvarchar, @art_col + @param_count/2) 
						select @spacer = N','

						if len( @cmd ) > 3000
						begin
							insert into #proctext(procedure_text) values( @cmd )
							select @cmd = N''
						end

						--
						-- Queued processing:if this is the row version column : need to add to where clause
						--
						if ((@queued_check = 1) and (col_name( @src_objid, @this_col) = N'msrepl_tran_version'))
							select @qwhere_string = N' and msrepl_tran_version = @c' + convert( nvarchar, @art_col )						
					end
				end
				select @art_col = @art_col + 1
			end
			FETCH hCColid INTO @this_col
		end
		CLOSE hCColid
		DEALLOCATE hCColid

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

		exec dbo.sp_scriptpkwhereclause @src_objid, @pkcolumns, N'@c', @artcolumns
		if (@queued_check = 1)
			insert into #proctext(procedure_text) values( @qwhere_string )
		else
			exec dbo.sp_MSscript_missing_row_check

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

		select @exists_else = 1

	end
end -- end if artcols != pkcols


-- construct update if pk has changed
select @cmd = N'update ' + @dest_owner + QUOTENAME(@dest_tabname) + N' set'

-- create SET clause

select @art_col = 1
select @spacer = 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 and EXISTS (select name from syscolumns where id=@src_objid and @this_col=colid and iscomputed<>1)
    begin
		if not (@keep_identity = 1 and 
			columnproperty(@src_objid, col_name( @src_objid, @this_col), 'IsIdentity') = 1)
		begin
			select @cmd = @cmd + @spacer + QUOTENAME(col_name( @src_objid, @this_col)) + 
				N' = @c' + convert( nvarchar, @art_col + @param_count/2 ) 
			select @spacer = N','

			if len( @cmd ) > 3000
			begin
			insert into #proctext(procedure_text) values( @cmd )
				select @cmd = N''
			end

			--
			-- Queued processing:if this is the row version column : need to add to where clause
			--
			if ((@queued_check = 1) and (col_name( @src_objid, @this_col) = N'msrepl_tran_version'))
				select @qwhere_string = N' and msrepl_tran_version = @c' + convert( nvarchar, @art_col )						
		end
		select @art_col = @art_col + 1
    end
	FETCH hCColid INTO @this_col
end

CLOSE hCColid
DEALLOCATE hCColid

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

exec dbo.sp_scriptpkwhereclause @src_objid, @pkcolumns, N'@c', @artcolumns
if (@queued_check = 1)
	insert into #proctext(procedure_text) values( @qwhere_string ) 
else
	exec dbo.sp_MSscript_missing_row_check

if @exists_else = 1
	insert into #proctext(procedure_text) values( N'end' )

-- flush to client

select procedure_text from #proctext order by c1 asc

go

EXEC dbo.sp_MS_marksystemobject sp_scriptxupdproc
GO

grant exec on dbo.sp_scriptxupdproc to public
go


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

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

CREATE PROCEDURE sp_changesubscriptiondtsinfo (
    @job_id varbinary(16),
    @dts_package_name sysname = NULL,
	@dts_package_password sysname = NULL,
	@dts_package_location nvarchar(12) = NULL

    ) AS

    /*
    ** Declarations.
    */

    DECLARE @srvid smallint
    DECLARE @artid int
    DECLARE @retcode int
	declare @login_name sysname
	declare @update_mode int
	declare @subscription_type int
	declare @dts_package_location_id int

    /*
    ** Initializations.
    */
    SET NOCOUNT ON
    
	
    /* 
    ** Get subscription properties and do Security Check.
    ** We use login_name stored in syssubscriptions to manage security 
    */

	select @update_mode = update_mode, @login_name = login_name,
		@subscription_type = subscription_type,
		@artid = artid              
		FROM syssubscriptions s WHERE 
			s.distribution_jobid = @job_id

    /*
    ** Check if the subscription exists.
    */
    IF @update_mode is null
	BEGIN
        RAISERROR (14055, 11, -1)
        RETURN (1)
    END

	if	@update_mode != 0
	begin
		RAISERROR(21180, 16, -1)    
		RETURN (1)
	end

	-- Only push store DTS info at distributor.
	if @subscription_type <> 0
	begin
		RAISERROR(21181, 16, -1)    
		RETURN (1)
	end

	--Security check

    IF  suser_sname(suser_sid()) <> @login_name AND is_srvrolemember('sysadmin') <> 1  
        AND is_member ('db_owner') <> 1
    BEGIN
		-- Only members of the sysadmin fixed server role, db_owner fixed database role or the creator of the subscription can change this subscription property.'
        RAISERROR(21175, 11, -1)
        RETURN (1)
    END

    -- Get pubid
	declare @pubid int
	select @pubid = pubid from sysarticles where artid = @artid

    /* Get subscription type of the publication */
	if not exists (select * from syspublications where
		pubid = @pubid and
		allow_dts = 1)
	begin
		RAISERROR(21178, 16, -1)    
		RETURN (1)
	end

	if @dts_package_location is null
		select @dts_package_location_id = null
	else IF LOWER(@dts_package_location collate SQL_Latin1_General_CP1_CS_AS) = N'distributor' 
		select @dts_package_location_id = 0
	ELSE IF LOWER(@dts_package_location collate SQL_Latin1_General_CP1_CS_AS) = N'subscriber' 
		select @dts_package_location_id = 1
	ELSE 
	begin
		raiserror(20587, 16, -1, '@dts_package_location', 'sp_changesubscriptiondtsinfo')
		return(1)
	end

	-- Encrypt DTS package password
	declare @change_password bit

	if @dts_package_password is null
		select @change_password = 0
	else
		select @change_password = 1

	-- When user sends in empty string, reset it to null.
	-- Have to do this before scramble because the result may contains invalid
	-- unicode code points which are equael to some collation.
	if @dts_package_password = N''
		select @dts_package_password = NULL

	declare @enc_dts_package_password nvarchar(524)
        select @enc_dts_package_password = @dts_package_password

	if @enc_dts_package_password is not null
	begin
		EXEC @retcode = master.dbo.xp_repl_encrypt @enc_dts_package_password OUTPUT
		IF @@error <> 0 OR @retcode <> 0
			return 1
	end

    /*
    ** Get distribution server information for remote RPC
    ** agent verification.
    */
    declare @distributor sysname
	declare @distribdb sysname
	declare @distproc nvarchar(1000)

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

    IF @@error <> 0 OR @retcode <> 0
    BEGIN
        RAISERROR (14071, 16, -1)
        RETURN (1)
    END

    /*
    ** Call proc to change the distributor
    */
    SELECT @distproc = RTRIM(@distributor) + '.' + @distribdb + 
        '.dbo.sp_MSchange_subscription_dts_info'

	exec @retcode = @distproc 
		@job_id = @job_id,
		@dts_package_name = @dts_package_name,
		@dts_package_password = @enc_dts_package_password,
		@dts_package_location = @dts_package_location_id,
		@change_password = @change_password

    IF @@error <> 0 OR @retcode <> 0
		RETURN (1)

    RETURN (0)
go

EXEC dbo.sp_MS_marksystemobject sp_changesubscriptiondtsinfo
GO

grant execute on dbo.sp_changesubscriptiondtsinfo to public
go


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

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

    SELECT @pubid = NULL
    -- Get the pubid from syspublications 
    SELECT @pubid = pubid 
      FROM syspublications
     WHERE name = @publication

    IF @@ERROR <> 0
        RETURN (1)

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

    EXEC @result = sp_getapplock @Resource = @publication, 
				@LockMode = N'Shared', 
				@LockOwner = N'Session', 
				@LockTimeout = 0

    IF @result < 0
    BEGIN
        RAISERROR(21385, 16, -1, @publication)
        RETURN (1)
    END

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

    IF @@ERROR <> 0
    BEGIN
        GOTO Failure
    END

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

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

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

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

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

        IF @@ERROR <> 0
            GOTO Failure

        SELECT @current_level = @current_level + 1

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

        IF @current_level > @limit
            GOTO PHASE1
    END

PHASE1:

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

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

PHASE2:

    -- Since transactional doesn't keep the nickname around in 
    -- sysmergearticles as merge does, we need to compute FK/PK ordering on 
    -- the fly. 
    SELECT @current_level = 0
    SELECT @limit = @max_level - 9
    WHILE 1 = 1
    BEGIN
        SELECT @update_level = @current_level + 1
        
        UPDATE #article_level_info
           SET ref_level = @update_level
          FROM #article_level_info
        INNER JOIN sysreferences r
            ON (#article_level_info.source_objid = r.fkeyid
                and r.rkeyid <> r.fkeyid)
        INNER JOIN #article_level_info ali1
            ON (r.rkeyid = ali1.source_objid 
                AND ali1.ref_level = @current_level)
        IF @@ROWCOUNT = 0
            GOTO PHASE3

        IF @@ERROR <> 0
            GOTO Failure

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

PHASE3:

    -- Select the articles out of #article_level_info 
    -- in ascending order of tree_level. This will give
    -- the proper order in which articles can be created
    -- without violating the internal dependencies among
    -- themselves. Note that this algorithm still allows 
    -- unresolved external references outside the publication.
    -- All this algorithm can guarantee is that all articles will
    -- be created successfully using the resulting order if 
    -- there is no dependent object outside the publication. 
    -- We need to order the articles in reverse ref_level
    -- to account for FK/PK constraints when dropping/deleting rows/truncating
    -- tables on the Subscriber.

    SELECT article
      FROM #article_level_info
    ORDER BY major_type ASC, tree_level ASC, ref_level DESC

    DROP TABLE #article_level_info
    RETURN (0)

Failure:

    DROP TABLE #article_level_info
    RETURN (1)
GO

exec dbo.sp_MS_marksystemobject sp_MScomputearticlescreationorder
go

grant exec on dbo.sp_MScomputearticlescreationorder to public
go

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

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

raiserror('Creating procedure sp_scriptpublicationcustomprocs',0,-1)
go
--
-- Name: sp_scriptpublicationcustomprocs
--
-- Description: This is a utility procedure for scripting out the 
--              article "custom" ins/upd/del procedures for all 
--              table articles in a publication with the auto-generate custom
--              procedure schema option enabled. This is particularly useful 
--              and in fact specifically designed for setting up no-sync 
--              subscriptions. 
-- 
-- Notes: 1) Reconciliation procedures for concurrent snapshot will
--           not be scripted by this procedure. It does not really make 
--           sense to have concurrent snapshots for no-sync subscriptions.
--        2) Custom procedures will not be scripted out for articles 
--           without the auto-generate custom procedure (0x2) schema_option.
--
-- Parameter: @publication sysname
--
-- Security: Execute permission is granted to public; procedural security 
--           check is performed inside the procedure to restrict access
--           to sysadmins and db_owners of current database. 
--
-- Example: exec Northwind.dbo.sp_scriptpublicationcustomprocs @publication = N'Northwind'
--
create procedure dbo.sp_scriptpublicationcustomprocs
    @publication sysname
as
begin
    set nocount on

    declare @retcode          int,
            @artid            int,
            @pubid            int,
            @cursor_allocated bit,
            @cursor_opened    bit,
            @table_created    bit,
            @ins_cmd          nvarchar(255),
            @upd_cmd          nvarchar(255),
            @del_cmd          nvarchar(255),
            @article          sysname,
            @schema_option    int,
            @repl_freq        int,
            @formattedmessage nvarchar(4000)
    
    -- Initializations 
    select @retcode = 0,
           @pubid = null,
           @cursor_allocated = 0,
           @cursor_opened = 0,
           @table_created = 0

    -- Security check: Sysadmins and db_owners only
    exec @retcode = sp_MSreplcheck_publish
    if @retcode <> 0 or @@error <> 0
    begin
        return 1
    end

    -- Make sure the current database is enabled for transaction replication
    if not exists (select * 
                     from master..sysdatabases 
                    where name = db_name() collate database_default
                      and (category & 0x1) <> 0)
    begin
        raiserror(14013, 16, -1)
        return 1
    end
 
    -- Parameter check: The specified @publication is a valid Transactional publication

    select @pubid = pubid, 
           @repl_freq = repl_freq
      from dbo.syspublications
     where name = @publication
    if @pubid is null
    begin
        raiserror(20026, 16, -1, @publication)
        return 1            
    end
    
    -- Don't script out custom procs for a snapshot publication
    if @repl_freq = 1
    begin
        raiserror(21515, 16, -1, @publication) 
        return 1
    end
    -- Create temp table for procedure text
    create table #proctext_scriptpublicationcustomprocs
    (
        line_no int identity(1,1) primary key,
        line nvarchar(4000)
    ) 
    if @@error<>0
    begin
        return 1
    end    
    
    -- Script header
    select @formattedmessage = formatmessage(21516, @publication, db_name())
    if @@error <> 0 begin select @retcode = 1 goto Failure end
    insert into #proctext_scriptpublicationcustomprocs values(N'--')
    insert into #proctext_scriptpublicationcustomprocs values(N'-- ' + @formattedmessage)
    insert into #proctext_scriptpublicationcustomprocs values(N'--')
    insert into #proctext_scriptpublicationcustomprocs values(N'')
    insert into #proctext_scriptpublicationcustomprocs values(N'')

    -- Open cursor through all table articles in the specified publication and script out
    -- custom procs as necessary
    
    declare harticle cursor local fast_forward for
        select artid, ins_cmd, upd_cmd, del_cmd, name, fn_replgetbinary8lodword(schema_option)
          from sysarticles
         where pubid = @pubid
           and (type & 1) <> 0
    if @@error <> 0
    begin
        select @retcode = 1
        goto Failure
    end
    select @cursor_allocated = 1

    open harticle
    if @@error <> 0
    begin
        select @retcode = 1
        goto Failure
    end
    
    fetch harticle into @artid, @ins_cmd, @upd_cmd, @del_cmd, @article, @schema_option

    while (@@fetch_status<>-1)
    begin        
        
        if (@schema_option & 2) = 0
        begin
            
            select @formattedmessage = formatmessage(21517,@article)
            if @@error <> 0 begin select @retcode = 1 goto Failure end
            insert into #proctext_scriptpublicationcustomprocs values(N'----')
            insert into #proctext_scriptpublicationcustomprocs values(N'---- ' + @formattedmessage)
            insert into #proctext_scriptpublicationcustomprocs values(N'----')
            insert into #proctext_scriptpublicationcustomprocs values(N'')
            goto SkipArticle
        end 

        select @formattedmessage = formatmessage(21518,@article)
        if @@error <> 0 begin select @retcode = 1 goto Failure end
        insert into #proctext_scriptpublicationcustomprocs values(N'----')
        insert into #proctext_scriptpublicationcustomprocs values(N'---- ' + @formattedmessage)
        insert into #proctext_scriptpublicationcustomprocs values(N'----')
        insert into #proctext_scriptpublicationcustomprocs values(N'')

        if lower(substring(@ins_cmd,1,len(N'call'))) = N'call'
        begin
            
            insert into #proctext_scriptpublicationcustomprocs exec @retcode = sp_scriptinsproc @artid
            if @@error <> 0 or @retcode <> 0
            begin
                select @retcode = 1
                goto Failure
            end
            insert into #proctext_scriptpublicationcustomprocs values('go')
            insert into #proctext_scriptpublicationcustomprocs values('')
        end
        else if lower(substring(@ins_cmd,1,len(N'sql'))) = N'sql'
        begin
            select @formattedmessage = formatmessage(21519)
            if @@error <> 0 begin select @retcode = 1 goto Failure end                
            insert #proctext_scriptpublicationcustomprocs values('-- ' + @formattedmessage)
            insert #proctext_scriptpublicationcustomprocs values('')
        end
        else
        begin
            select @formattedmessage = formatmessage(21520,@ins_cmd)
            if @@error <> 0 begin select @retcode = 1 goto Failure end                
            insert #proctext_scriptpublicationcustomprocs values('-- ' + @formattedmessage)
            insert #proctext_scriptpublicationcustomprocs values('')
        end

        if lower(substring(@upd_cmd,1,len(N'call'))) = N'call'
        begin
            insert into #proctext_scriptpublicationcustomprocs exec @retcode = sp_scriptupdproc @artid
            if @@error <> 0 or @retcode <> 0
            begin
                select @retcode = 1
                goto Failure
            end
            insert into #proctext_scriptpublicationcustomprocs values('go')
            insert into #proctext_scriptpublicationcustomprocs values('')

        end
        else if lower(substring(@upd_cmd,1,len(N'mcall'))) = N'mcall'
        begin
            insert into #proctext_scriptpublicationcustomprocs exec @retcode = sp_scriptmappedupdproc @artid
            if @@error <> 0 or @retcode <> 0
            begin
                select @retcode = 1
                goto Failure
            end
            insert into #proctext_scriptpublicationcustomprocs values('go')
            insert into #proctext_scriptpublicationcustomprocs values('')
        end
        else if lower(substring(@upd_cmd,1,len(N'xcall'))) = N'xcall'
        begin
            insert into #proctext_scriptpublicationcustomprocs exec @retcode = sp_scriptxupdproc @artid
            if @@error <> 0 or @retcode <> 0
            begin
                select @retcode = 1
                goto Failure
            end
            insert into #proctext_scriptpublicationcustomprocs values('go')
            insert into #proctext_scriptpublicationcustomprocs values('')
        end
        else if lower(substring(@upd_cmd,1,len(N'sql'))) = N'sql'
        begin
            select @formattedmessage = formatmessage(21519)
            if @@error <> 0 begin select @retcode = 1 goto Failure end                
            insert #proctext_scriptpublicationcustomprocs values('-- ' + @formattedmessage)
            insert #proctext_scriptpublicationcustomprocs values('')
        end
        else
        begin
            select @formattedmessage = formatmessage(21520,@upd_cmd)
            if @@error <> 0 begin select @retcode = 1 goto Failure end                
            insert #proctext_scriptpublicationcustomprocs values('-- ' + @formattedmessage)
            insert #proctext_scriptpublicationcustomprocs values('')
        end

        if lower(substring(@del_cmd,1,len(N'call'))) = N'call'
        begin
            insert into #proctext_scriptpublicationcustomprocs exec @retcode = sp_scriptdelproc @artid
            if @@error <> 0 or @retcode <> 0
            begin
                select @retcode = 1
                goto Failure
            end
            insert into #proctext_scriptpublicationcustomprocs values('go')
            insert into #proctext_scriptpublicationcustomprocs values('')
        end
        else if lower(substring(@del_cmd,1,len(N'xcall'))) = N'xcall'
        begin
            insert into #proctext_scriptpublicationcustomprocs exec @retcode = sp_scriptxdelproc @artid
            if @@error <> 0 or @retcode <> 0
            begin
                select @retcode = 1
                goto Failure
            end
            insert into #proctext_scriptpublicationcustomprocs values('go')
            insert into #proctext_scriptpublicationcustomprocs values('')
        end
        else if lower(substring(@del_cmd,1,len(N'sql'))) = N'sql'
        begin
            select @formattedmessage = formatmessage(21519)
            if @@error <> 0 begin select @retcode = 1 goto Failure end                
            insert #proctext_scriptpublicationcustomprocs values('-- ' + @formattedmessage)
            insert #proctext_scriptpublicationcustomprocs values('')
        end
        else
        begin
            select @formattedmessage = formatmessage(21520,@del_cmd)
            if @@error <> 0 begin select @retcode = 1 goto Failure end                
            insert #proctext_scriptpublicationcustomprocs values('-- ' + @formattedmessage)
            insert #proctext_scriptpublicationcustomprocs values('')
        end
SkipArticle:
        fetch harticle into @artid, @ins_cmd, @upd_cmd, @del_cmd, @article, @schema_option
    end
    
    select '--' = line from #proctext_scriptpublicationcustomprocs order by line_no asc
   
Failure:
    if @table_created <> 0
    begin
