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


    -- Retrieve underlying table name and replicated columns
    select @objid = objid, @columns = columns from sysarticles a, syspublications p
        where a.name = @article and
              p.name = @publication and
              a.pubid = p.pubid

    exec sp_MSget_qualified_name @objid, @qualname OUTPUT

	-- Trigger should be invoked for repl processes as well.
	select @cmd = N'create trigger ' + QUOTENAME(@procname) + N' on ' + @qualname + N' '
    select @cmd = @cmd + N'for update as ' 

    exec dbo.sp_MSflush_command @cmd output, 1

    insert into #proctext(procedure_text) values(N'
')
    -- declare common local variables
	insert into #proctext(procedure_text) values 
		(N'declare @rc int
')
    insert into #proctext(procedure_text) values(N'select @rc = @@ROWCOUNT 

')
		
	-- Optimization. Return immediately if no row changed
	-- This must be at the beginning of the trigger to @@rowcount be overwritten.
    insert into #proctext(procedure_text) values(N'if @rc = 0 return 
')
    insert into #proctext(procedure_text) values(N'if update (msrepl_tran_version) return 
')

    -- update the version column of all the updated rows all at once.
	select @cmd = N'update ' + @qualname + N' set msrepl_tran_version = newid() from ' +
        @qualname + ', inserted '
    exec dbo.sp_MSflush_command @cmd output, 1
    insert into #proctext(procedure_text) values(N'
')
    exec dbo.sp_MSscript_where_clause @objid, @columns, 'version pk', null, 4

    insert into #proctext(procedure_text) values(N'
')
    -- send fragments to client
    select procedure_text from #proctext order by c1 asc
end
go
EXEC dbo.sp_MS_marksystemobject sp_MSscript_pub_upd_trig
go
grant exec on dbo.sp_MSscript_pub_upd_trig to public
go

--------------------------------------------------------------------------------
--. sp_MSscript_sync_del_proc
--------------------------------------------------------------------------------

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_sync_del_proc')
drop procedure sp_MSscript_sync_del_proc
go
raiserror('Creating procedure sp_MSscript_sync_del_proc', 0,1)
go
create procedure sp_MSscript_sync_del_proc (
    @publication sysname, 
    @article     sysname,
    @procname    sysname)
as
BEGIN
	declare @source_objid int
	declare @colname sysname
	declare @indid int
	declare @cmd          nvarchar(4000)
	declare @columns      binary(32)
	declare @outvars      nvarchar(4000)
	declare @rc           int
	declare @error_cmd	tinyint
	declare @queued_pub bit

	set nocount on
	--
	-- security check
	--
	exec @rc = dbo.sp_MSreplcheck_publish
	if @@error <> 0 or @rc <> 0
	begin
		return (1)
	end
	--
	-- Create temp table
	--
	create table #proctext ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
	select @queued_pub = allow_queued_tran from syspublications where name = @publication

	--
	-- proc definition
	--
	exec @rc = dbo.sp_MSscript_beginproc @publication, @article, @procname, @source_objid output, @columns output
	if @rc = 0
	    return

	--
	-- construct parameter list
	--
	exec dbo.sp_MSscript_params @source_objid, @columns, N'_old', 0, null

	--
	-- add other parameters and start body of proc
	--
	exec dbo.sp_MSscript_procbodystart @queued_pub 

	--
	-- script out security check
	--
	exec dbo.sp_MSscript_security @publication

	--
	-- script the execution mode checks
	--
	exec dbo.sp_MSscript_ExecutionMode_stmt @publication, @article, 1

	--
	-- script out subscription validation
	--
	exec dbo.sp_MSscript_validate_subscription @publication, @article

	--
	-- Work around for case where article has 1 col that is not user-modfied (identity, timestamp)
	-- *** Do we need to check this here - 
	-- *** we should be checking this when creating subscription
	-- 
	exec @rc = dbo.sp_MStable_not_modifiable @source_objid, @columns
	if @rc = 1
		select @error_cmd = 1
	else
	begin
		exec @indid = dbo.sp_MStable_has_unique_index @source_objid 
		if (@outvars != null and @indid = 0)
			-- no insert/update allowed if timestamp/identity col and no unique index
			select @error_cmd = 1
		else
			select @error_cmd = 0		
	end

	if (@error_cmd = 0)
	begin
		-- Continue generation
		
		--
		-- script delete statemnt
		--
		exec dbo.sp_MSscript_delete_statement @publication, @article, @source_objid, @columns, @queued_pub

		--
		-- script queued specific stuff
		--
		if (@queued_pub = 1)
		begin
			--
			-- script Conflict resolution block for Subscriber Wins case
			--
			exec dbo.sp_MSscript_delete_subwins @publication, @article, @source_objid, @columns

			--
			-- script Conflict resolution block for Publisher Wins case
			--
			exec dbo.sp_MSscript_delete_pubwins @publication, @article, @source_objid, @columns
		end
		
		--
		-- script closing 
		--
		exec dbo.sp_MSscript_endproc @source_objid, 'del', @columns, @outvars, @queued_pub
	end
	else
	begin
		--
		-- Generate error command and finish
		--
		insert into #proctext(procedure_text) values( N'
	exec sp_MSreplraiserror 20516
END
')
	end
	              
	--
	-- send fragments to client
	--
	select procedure_text from #proctext order by c1 asc

END
go
EXEC dbo.sp_MS_marksystemobject sp_MSscript_sync_del_proc
GO
grant exec on dbo.sp_MSscript_sync_del_proc to public
go

--------------------------------------------------------------------------------
--. sp_MSscript_sync_ins_proc
--------------------------------------------------------------------------------

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_sync_ins_proc')
drop procedure sp_MSscript_sync_ins_proc
go
raiserror('Creating procedure sp_MSscript_sync_ins_proc', 0,1)
go
create procedure sp_MSscript_sync_ins_proc (
    @publication sysname, 
    @article     sysname,
    @procname    sysname)
as
BEGIN
	declare @source_objid int
			,@colname sysname
			,@indid int
			,@cmd          nvarchar(4000)
			,@columns      binary(32)
			,@outvars      nvarchar(4000)
			,@rc           int
			,@error_cmd	tinyint
			,@identity_insert bit
			,@queued_pub bit

	set nocount on
	--
	-- security check
	--
	exec @rc = dbo.sp_MSreplcheck_publish
	if @@error <> 0 or @rc <> 0
	begin
		return (1)
	end
	--
	-- Create temp table
	--
	create table #proctext ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
	select @queued_pub = allow_queued_tran from syspublications where name = @publication

	--
	-- proc definition
	--
	exec @rc = dbo.sp_MSscript_beginproc @publication, @article, @procname, @source_objid output, @columns output
	if @rc = 0
	    return

	--
	-- construct parameter list
	--
	exec dbo.sp_MSscript_params @source_objid, @columns, null, 1,  @outvars output

	--
	-- add other parameters and start body of proc
	--
	exec dbo.sp_MSscript_procbodystart @queued_pub

	--
	-- script out security check
	--
	exec dbo.sp_MSscript_security @publication

	--
	-- script the execution mode checks
	--
	exec dbo.sp_MSscript_ExecutionMode_stmt @publication, @article, 0

	--
	-- script out subscription validation
	--
	exec dbo.sp_MSscript_validate_subscription @publication, @article

	--
	-- Work around for case where article has 1 col that is not user-modfied (identity, timestamp)
	-- *** Do we need to check this here - 
	-- *** we should be checking this when creating subscription
	-- 
	exec @rc = dbo.sp_MStable_not_modifiable @source_objid, @columns
	if @rc = 1
		select @error_cmd = 1
	else
	begin
		exec @indid = dbo.sp_MStable_has_unique_index @source_objid 
		if (@outvars != null and @indid = 0)
			-- no insert/update allowed if timestamp/identity col and no unique index
			select @error_cmd = 1
		else
			select @error_cmd = 0		
	end

	if (@error_cmd = 0)
	begin
		-- Continue generation
	
		-- 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 @publication, @article, @identity_insert output
		--
		-- script insert statemnt
		--
		exec dbo.sp_MSscript_insert_statement @source_objid, @columns, @identity_insert, @queued_pub

		--
		-- script queued specific stuff
		--
		if (@queued_pub = 1)
		begin
			--
			-- script Conflict resolution block for Subscriber Wins case
			--
			exec dbo.sp_MSscript_insert_subwins @publication, @article, @source_objid, @columns, @identity_insert

			--
			-- script Conflict resolution block for Publisher Wins case
			--
			exec dbo.sp_MSscript_insert_pubwins @publication, @article, @source_objid, @columns
		end
		
		--
		-- script closing 
		--
		exec dbo.sp_MSscript_endproc @source_objid, 'ins', @columns, @outvars, @queued_pub
	end
	else
	begin
		--
		-- Generate error command and finish
		--
		insert into #proctext(procedure_text) values( N'
	exec sp_MSreplraiserror 20516
END
')
	end
	              
	--
	-- send fragments to client
	--
	select procedure_text from #proctext order by c1 asc
END
go
EXEC dbo.sp_MS_marksystemobject sp_MSscript_sync_ins_proc
GO
grant exec on dbo.sp_MSscript_sync_ins_proc to public
go

--------------------------------------------------------------------------------
--. sp_MSscript_sync_upd_proc
--------------------------------------------------------------------------------

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_sync_upd_proc')
drop procedure sp_MSscript_sync_upd_proc
go
raiserror('Creating procedure sp_MSscript_sync_upd_proc', 0,1)
go
create procedure sp_MSscript_sync_upd_proc (
    @publication sysname, 
    @article     sysname,
    @procname    sysname)
as
BEGIN
	declare @source_objid int
			,@colname sysname
			,@indid int
			,@cmd          nvarchar(4000)
			,@columns      binary(32)
			,@outvars      nvarchar(4000)
			,@rc           int
			,@error_cmd	tinyint
			,@identity_insert bit
			,@queued_pub bit

	set nocount on
	--
	-- security check
	--
	exec @rc = dbo.sp_MSreplcheck_publish
	if @@error <> 0 or @rc <> 0
	begin
		return (1)
	end
	--
	-- Create temp table
	--
	create table #proctext ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
	select @queued_pub = allow_queued_tran from syspublications where name = @publication

	--
	-- proc definition
	--
	exec @rc = dbo.sp_MSscript_beginproc @publication, @article, @procname, @source_objid output, @columns output
	if @rc = 0
	    return

	--
	-- construct parameter list
	-- Script bitmap parameter
	--
	exec dbo.sp_MSscript_params @source_objid, @columns, null, 1,  @outvars output
	insert into #proctext(procedure_text) values( N',')
	exec dbo.sp_MSscript_params @source_objid, @columns, N'_old', 0, null
	insert into #proctext(procedure_text) values( N'
	,@bitmap varbinary(4000)')

	--
	-- add other parameters and start body of proc
	--
	exec dbo.sp_MSscript_procbodystart @queued_pub 

	--
	-- script out security check
	--
	exec dbo.sp_MSscript_security @publication

	--
	-- script the execution mode checks
	--
	exec dbo.sp_MSscript_ExecutionMode_stmt @publication, @article, 2

	--
	-- script out subscription validation
	--
	exec dbo.sp_MSscript_validate_subscription @publication, @article

	--
	-- Work around for case where article has 1 col that is not user-modfied (identity, timestamp)
	-- *** Do we need to check this here - 
	-- *** we should be checking this when creating subscription
	-- 
	exec @rc = dbo.sp_MStable_not_modifiable @source_objid, @columns
	if @rc = 1
		select @error_cmd = 1
	else
	begin
		exec @indid = dbo.sp_MStable_has_unique_index @source_objid 
		if (@outvars != null and @indid = 0)
			-- no insert/update allowed if timestamp/identity col and no unique index
			select @error_cmd = 1
		else
			select @error_cmd = 0		
	end

	if (@error_cmd = 0)
	begin
		-- Continue generation
		
		--
		-- script update statemnt
		--
		exec dbo.sp_MSscript_update_statement @publication, @article, @source_objid, @columns, @queued_pub

		--
		-- script queued specific stuff
		--
		if (@queued_pub = 1)
		begin
			-- 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 @publication, @article, @identity_insert output
			--
			--
			-- script Conflict resolution block for Subscriber Wins case
			--
			exec dbo.sp_MSscript_update_subwins @publication, @article, @source_objid, @columns, @identity_insert

			--
			-- script Conflict resolution block for Publisher Wins case
			--
			exec dbo.sp_MSscript_update_pubwins @publication, @article, @source_objid, @columns
		end

		--
		-- script closing 
		--
		exec dbo.sp_MSscript_endproc @source_objid, 'upd', @columns, @outvars, @queued_pub
	end
	else
	begin
		--
		-- Generate error command and finish
		--
		insert into #proctext(procedure_text) values( N'
	exec sp_MSreplraiserror 20516
END
')
	end
	              
	--
	-- send fragments to client
	--
	select procedure_text from #proctext order by c1 asc
END
go
EXEC dbo.sp_MS_marksystemobject sp_MSscript_sync_upd_proc
GO
grant exec on dbo.sp_MSscript_sync_upd_proc 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('Creating procedure sp_script_reconciliation_insproc', 0,1)
go
create procedure sp_script_reconciliation_insproc (
	@artid int)
as
BEGIN
declare @retcode int
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
	
--
-- security check
--
exec @retcode = dbo.sp_MSreplcheck_publish
if @@error <> 0 or @retcode <> 0
begin
    return (1)
end

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('Creating procedure sp_script_reconciliation_delproc', 0,1)
go
create procedure sp_script_reconciliation_delproc 
( 
	@artid int
)
as
begin
    declare @retcode int
    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)

    --
    -- security check
    --
    exec @retcode = dbo.sp_MSreplcheck_publish
    if @@error <> 0 or @retcode <> 0
    begin
        return (1)
    end

    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
end
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('Creating procedure sp_script_reconciliation_xdelproc', 0,1)
go
create procedure sp_script_reconciliation_xdelproc 
(
    @artid int
)
as
begin
    declare @retcode int
    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)

    --
    -- security check
    --
    exec @retcode = dbo.sp_MSreplcheck_publish
    if @@error <> 0 or @retcode <> 0
    begin
        return (1)
    end

    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
end
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('Creating procedure sp_scriptinsproc', 0,1)
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)

set nocount on
--
-- security check
--
exec @rc = dbo.sp_MSreplcheck_publish
if @@error <> 0 or @rc <> 0
begin
    return (1)
end

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

-- 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
	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 we are a part of queued publication then 
-- insert only if PK or other unique key(s) do not exist
--
if (@queued_check = 1)
begin
    select @cmd = N'
if not exists (select * from ' + @dest_owner + QUOTENAME(@dest_tabname) + N' '
    insert into #proctext(procedure_text) values( @cmd )
    exec @rc = sp_replscriptuniquekeywhereclause @tabid = @src_objid
                                                        ,@columns = @columns
                                                        ,@prefix = '@c' 
                                                        ,@mode = 1
    if (@@error != 0 or @rc != 0)
        return 1
    select @cmd = N')
BEGIN'
    insert into #proctext(procedure_text) values( @cmd )
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('Creating procedure sp_scriptdelproc', 0,1)
go
create procedure sp_scriptdelproc 
(
    @artid int
)
as
begin
    declare @retcode int
    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)

    --
    -- security check
    --
    exec @retcode = dbo.sp_MSreplcheck_publish
    if @@error <> 0 or @retcode <> 0
    begin
        return (1)
    end
    
    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
end
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('Creating procedure sp_scriptxdelproc', 0,1)
go
create procedure sp_scriptxdelproc 
(
    @artid int
)
as
begin
    declare @retcode int
    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)

    --
    -- security check
    --
    exec @retcode = dbo.sp_MSreplcheck_publish
    if @@error <> 0 or @retcode <> 0
    begin
        return (1)
    end
    
    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
end
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('Creating procedure sp_scriptupdproc', 0,1)
go
create procedure sp_scriptupdproc 
(
    @artid int
)
as
begin
    declare @retcode int
    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

    --
    -- security check
    --
    exec @retcode = dbo.sp_MSreplcheck_publish
    if @@error <> 0 or @retcode <> 0
    begin
        return (1)
    end
    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
end
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('Creating procedure sp_scriptmappedupdproc', 0,1)
go
create procedure sp_scriptmappedupdproc 
(
    @artid int
)
as
begin
    declare @retcode int
    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

    --
    -- security check
    --
    exec @retcode = dbo.sp_MSreplcheck_publish
    if @@error <> 0 or @retcode <> 0
    begin
        return (1)
    end
    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
end
go
EXEC dbo.sp_MS_marksystemobject sp_scriptmappedupdproc
GO
grant exec on dbo.sp_scriptmappedupdproc to public
go

--------------------------------------------------------------------------------
--. sp_scriptdynamicupdproc
--------------------------------------------------------------------------------

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_scriptdynamicupdproc')
drop procedure sp_scriptdynamicupdproc
go
raiserror('Creating procedure sp_scriptdynamicupdproc', 0,1)
go
create procedure sp_scriptdynamicupdproc 
(
    @artid int
)
as
    begin
    declare @retcode int
    declare @cmd          nvarchar(4000)
    declare @dest_owner   nvarchar(255)
    declare @dest_tabname sysname
    declare @src_objid    int
    declare @artcolumns   binary(32)
    declare @pkcolumns    binary(32)
    declare @upd_cmd      nvarchar(255)
    declare @dest_proc    sysname
    declare @art_cols     int
    declare @this_col     int
    declare @art_col      int
    declare @pk_col       int
    declare @pkart_col    int
    declare @isset        int
    declare @ispk         int
    declare @isidentity   bit
    declare @iscomputed   bit
    declare @bytestr      nvarchar(10)
    declare @bitstr       nvarchar(10)
    declare @typestring   nvarchar(255)
    declare @spacer_param nvarchar(10)
    declare @update_created  bit
    declare @pkcomputed   int
    declare @pubid        int, @allow_queued_tran bit

    set nocount on 

    --
    -- security check
    --
    exec @retcode = dbo.sp_MSreplcheck_publish
    if @@error <> 0 or @retcode <> 0
    begin
        return (1)
    end
    select @update_created	 = 0

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

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

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



    -------- get sysarticles information

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

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

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

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

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

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

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

    -------- construct parameter list

    exec dbo.sp_getarticlepkcolbitmap @src_objid, @pkcolumns output

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

    ----- add changed data bitmap

    select @art_col = 1
    select @pk_col = 1

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

    OPEN hCColid

    FETCH hCColid INTO @this_col

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

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

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

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



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

    exec @pkcomputed = sp_MSareallcolumnscomputed @src_objid, @pkcolumns

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

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

    	OPEN hCColid

    	FETCH hCColid INTO @this_col

    	select @pk_is_identity = 1

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

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

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

        -- create SET clause consisting of CASE statements

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

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

    	OPEN hCColid

    	FETCH hCColid INTO @this_col

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

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

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

    			    	select @update_created = 1
    				end

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

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

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

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

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

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

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

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

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

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

    				select @spacer_param = ',' 
    			end

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

        if @update_created = 1
    	begin

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

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

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

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

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

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

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

    		if @allow_queued_tran <> 1
    			exec dbo.sp_MSscript_missing_row_check
    	end

    end

    select procedure_text from #proctext order by c1 asc
end
go
EXEC dbo.sp_MS_marksystemobject sp_scriptdynamicupdproc
GO
grant exec on dbo.sp_scriptdynamicupdproc to public
go

--------------------------------------------------------------------------------
--. sp_scriptxupdproc
--------------------------------------------------------------------------------

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_scriptxupdproc')
drop procedure sp_scriptxupdproc
go
raiserror('Creating procedure sp_scriptxupdproc', 0,1)
go
create procedure sp_scriptxupdproc @artid int
as
BEGIN
    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,
                @rc int,
                @fhasnonpkuniquekeys 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
    --
    -- security check
    --
    exec @rc = dbo.sp_MSreplcheck_publish
    if @@error <> 0 or @rc <> 0
    begin
        return (1)
    end

    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
             ,@fhasnonpkuniquekeys = 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' )

    --
    -- If we are a part of queued publication 
    --
    if (@queued_check = 1)
    begin
        --
        -- Check if we have non PK unique keys
        --
        exec @fhasnonpkuniquekeys = dbo.sp_repltablehasnonpkuniquekey @tabid = @src_objid
        if (@fhasnonpkuniquekeys = 1)
        begin
            --
            -- There are non PK unique keys
            -- update only if updated values of non PK unique key(s) do not exist
            --
            select @cmd = N'
if not exists (select * from ' + @dest_owner + QUOTENAME(@dest_tabname) + N' '
            insert into #proctext(procedure_text) values( @cmd )
            exec @rc = sp_replscriptuniquekeywhereclause @tabid = @src_objid
                                                            ,@columns = @artcolumns
                                                            ,@prefix = '@c' 
                                                            ,@mode = 2
                                                            ,@paramcount = @param_count
            if (@@error != 0 or @rc != 0)
                return 1
            select @cmd = N')
begin'
            insert into #proctext(procedure_text) values( @cmd )
        end
    end

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

    --
    -- If we are a part of queued publication then 
    -- update only if updated value of PK does not exist
    --
    if (@queued_check = 1 and @pk_is_identity = 0)
    begin
        select @cmd = N'
if not exists (select * from ' + @dest_owner + QUOTENAME(@dest_tabname) + N' '
        insert into #proctext(procedure_text) values( @cmd )
        exec @rc = sp_replscriptuniquekeywhereclause @tabid = @src_objid
                                                            ,@columns = @artcolumns
                                                            ,@prefix = '@c' 
                                                            ,@mode = 3
                                                            ,@paramcount = @param_count
        if (@@error != 0 or @rc != 0)
            return 1
        select @cmd = N')
begin'
        insert into #proctext(procedure_text) values( @cmd )
    end

    -- 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)
    begin
        insert into #proctext(procedure_text) values( @qwhere_string ) 
        if (@pk_is_identity = 0)
        begin
            select @cmd = N'
end'
            insert into #proctext(procedure_text) values( @cmd )
        end
    end
    else
    	exec dbo.sp_MSscript_missing_row_check

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

    --
    -- End the if exists block for Queued publications
    --
    if (@queued_check = 1) and (@fhasnonpkuniquekeys = 1)
    begin
        select @cmd = N'
end'
        insert into #proctext(procedure_text) values( @cmd )
    end

    -- flush to client

    select procedure_text from #proctext order by c1 asc
END
go
EXEC dbo.sp_MS_marksystemobject sp_scriptxupdproc
GO
grant exec on dbo.sp_scriptxupdproc to public
go

--------------------------------------------------------------------------------
--. sp_MSgetarticlereinitvalue
--------------------------------------------------------------------------------

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSgetarticlereinitvalue')
drop procedure sp_MSgetarticlereinitvalue
go
raiserror('Creating procedure sp_MSgetarticlereinitvalue', 0,1)
go
create procedure sp_MSgetarticlereinitvalue (
	@subscriber 			sysname,
	@subscriberdb 		sysname,
	@artid	 			int, 
	@reinit		 		int output
)
as
begin
	set NOCOUNT ON
	declare @orig_srvid int
		,@retcode int
		,@publication sysname

	--
	-- PAL security check
	-- Get publication name using artid
	--
	select @publication = p.name
	from syspublications p join sysarticles a on p.pubid = a.pubid
	where a.artid = @artid
	if (@publication is null)
	begin
		return 1
	end
	exec @retcode = dbo.sp_MSreplcheck_pull @publication = @publication
	if @@error <> 0 or @retcode <> 0
	begin
		return (1)
	end
	--
	-- get the value of reinit flag
	--
	select @orig_srvid = srvid from master.dbo.sysservers where UPPER(srvname) = UPPER(@subscriber) collate database_default
	select @reinit = queued_reinit 
	from syssubscriptions 
	where 
		artid = @artid 
		and srvid = @orig_srvid
		and dest_db = @subscriberdb

	-- All done
	return 0
end
go
exec dbo.sp_MS_marksystemobject sp_MSgetarticlereinitvalue
go
grant execute on dbo.sp_MSgetarticlereinitvalue to public
go

--------------------------------------------------------------------------------
--.	sp_MScomputearticlescreationorder
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MScomputearticlescreationorder')
drop procedure sp_MScomputearticlescreationorder
go
print ''
print 'Creating procedure 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
    DECLARE @retcode int

    SELECT @retcode = 0

    EXEC @retcode = sp_MSreplcheck_publish
    IF @@ERROR <> 0 OR @retcode <> 0
        return (1)

    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
                          and id <> depid)
    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
               AND d.id <> d.depid)
    
        -- 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
                AND d.id <> d.depid)
        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 execute on sp_MScomputearticlescreationorder to public
go

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

create procedure sp_MSscriptmvastablenci @artid int
as
declare @cmd          nvarchar(4000)
declare @dest_owner   nvarchar(255)
declare @dest_tabname sysname
declare @src_objid    int
declare @constraint_name  sysname
declare @spacer		  nvarchar(1)
declare @srcobj		  nvarchar(1000)
declare @colname	  sysname
declare @indkey		  int
declare @indid		  int
declare @status		  int
declare @unique	      nvarchar(10)
declare @cmd_sep	  nvarchar(10)


-------- security check, db_owner
declare @retcode int
exec @retcode = dbo.sp_MSreplcheck_publish
if @@ERROR <> 0 or @retcode <> 0
    return(1)

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

select @srcobj = QUOTENAME( USER_NAME(OBJECTPROPERTY(@src_objid,'OwnerId'))) collate database_default + N'.' + QUOTENAME(object_name(@src_objid)) collate database_default
select @cmd_sep = N''

DECLARE hCIdx CURSOR LOCAL FAST_FORWARD FOR
select name, indid, status from sysindexes where id = @src_objid and indid > 1 order by indid asc

OPEN hCIdx
FETCH hCIdx INTO @constraint_name, @indid, @status

WHILE (@@fetch_status <> -1)
begin
	if @status & 2 = 2
	begin
		select @unique = N' unique '
	end
	else
	begin
		select @unique = N' '
	end

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

	select @cmd = N'create' + @unique + N'nonclustered index ' + QUOTENAME(@constraint_name) + N' on ' +@dest_owner + QUOTENAME(@dest_tabname) + N'('
	insert into #proctext(procedure_text) values( @cmd )

	select @spacer = N' '
	select @cmd = N''

	select @indkey = 1
	while (@indkey <= 16)
	begin	
		select @colname = index_col(@srcobj, @indid, @indkey)
		if (@colname is null)
		begin
			select @indkey = 16
		end
		else
		begin
			select @cmd = @cmd + @spacer + QUOTENAME(@colname)
			select @spacer = N','

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

	insert into #proctext(procedure_text) values( @cmd )
	insert into #proctext(procedure_text) values( N')' )
	
	FETCH hCIdx INTO @constraint_name, @indid, @status
end

CLOSE hCIdx
DEALLOCATE hCIdx
select procedure_text from #proctext order by c1 asc

go
EXEC dbo.sp_MS_marksystemobject sp_MSscriptmvastablenci
GO
grant exec on dbo.sp_MSscriptmvastablenci to public
go
--------------------------------------------------------------------------------
--. sp_MSscriptmvastablepkc
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscriptmvastablepkc')
drop procedure sp_MSscriptmvastablepkc
go
print ''
print 'Creating procedure sp_MSscriptmvastablepkc'
go

create procedure sp_MSscriptmvastablepkc @artid int
as
declare @cmd          nvarchar(4000)
declare @dest_owner   nvarchar(255)
declare @dest_tabname sysname
declare @src_objid    int
declare @constraint_name  sysname
declare @spacer		  nvarchar(1)
declare @srcobj		  nvarchar(1000)
declare @colname	  sysname
declare @indkey		  int
declare @indid		  int

-------- security check, db_owner
declare @retcode int
exec @retcode = dbo.sp_MSreplcheck_publish
if @@ERROR <> 0 or @retcode <> 0
    return(1)

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

select @srcobj = QUOTENAME( USER_NAME(OBJECTPROPERTY(@src_objid,'OwnerId'))) collate database_default + N'.' + QUOTENAME(object_name(@src_objid)) collate database_default

select @constraint_name = name, @indid = indid from sysindexes where id = @src_objid and status & 16 <> 0

select @cmd = N'alter table ' + @dest_owner + QUOTENAME(@dest_tabname) collate database_default + N' add constraint ' + QUOTENAME(@constraint_name) collate database_default + N' primary key clustered ('
insert into #proctext(procedure_text) values( @cmd )

select @spacer = N' '
select @cmd = N''

select @indkey = 1
while (@indkey <= 16)
begin	
	select @colname = index_col(@srcobj, @indid, @indkey)
	if (@colname is null)
	begin
		select @indkey = 16
	end
	else
	begin
		select @cmd = @cmd + @spacer + QUOTENAME(@colname) collate database_default
		select @spacer = N','

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

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

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

select procedure_text from #proctext order by c1 asc

go
EXEC dbo.sp_MS_marksystemobject sp_MSscriptmvastablepkc
GO
grant exec on dbo.sp_MSscriptmvastablepkc to public
go
--------------------------------------------------------------------------------
--. sp_MSscriptmvastableidx
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscriptmvastableidx')
drop procedure sp_MSscriptmvastableidx
go
print ''
print 'Creating procedure sp_MSscriptmvastableidx'
go

create procedure sp_MSscriptmvastableidx @artid int
as
declare @cmd          nvarchar(4000)
declare @dest_owner   nvarchar(255)
declare @dest_tabname sysname
declare @src_objid    int
declare @constraint_name  sysname
declare @spacer		  nvarchar(1)
declare @srcobj		  nvarchar(1000)
declare @colname	  sysname
declare @indkey		  int
declare @indid		  int

-------- security check, db_owner
declare @retcode int
exec @retcode = dbo.sp_MSreplcheck_publish
if @@ERROR <> 0 or @retcode <> 0
    return(1)

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

select @srcobj = QUOTENAME( USER_NAME(OBJECTPROPERTY(@src_objid,'OwnerId'))) collate database_default + N'.' + QUOTENAME(object_name(@src_objid)) collate database_default

select @constraint_name = name, @indid = indid from sysindexes where id = @src_objid and status & 16 <> 0

select @cmd = N'create unique clustered index ' + QUOTENAME(@constraint_name) + N' on ' +@dest_owner + QUOTENAME(@dest_tabname) + N'('
insert into #proctext(procedure_text) values( @cmd )

select @spacer = N' '
select @cmd = N''

select @indkey = 1
while (@indkey <= 16)
begin	
	select @colname = index_col(@srcobj, @indid, @indkey)
	if (@colname is null)
	begin
		select @indkey = 16
	end
	else
	begin
		select @cmd = @cmd + @spacer + QUOTENAME(@colname)
		select @spacer = N','

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

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

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

select procedure_text from #proctext order by c1 asc

go
EXEC dbo.sp_MS_marksystemobject sp_MSscriptmvastableidx
GO
grant exec on dbo.sp_MSscriptmvastableidx to public
go
--------------------------------------------------------------------------------
--. sp_MSscriptmvastable
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscriptmvastable')
drop procedure sp_MSscriptmvastable
go
print ''
print 'Creating procedure sp_MSscriptmvastable'
go

create procedure sp_MSscriptmvastable @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 @schema_option binary(8)
declare @art_col	  int
declare @this_col	  int
declare @isset	      int
declare @use_base_type tinyint

declare @spacer		  nvarchar(1)
declare @nullability  nvarchar(10)
declare @typestring   nvarchar(255)
declare @col_name	  sysname

-------- security check, db_owner
declare @retcode int
exec @retcode = dbo.sp_MSreplcheck_publish
if @@ERROR <> 0 or @retcode <> 0
    return(1)

-------- 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, 
	   @use_base_type = substring(schema_option,8,1) & 32
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

-- script out CREATE TABLE statement

-- begin create table
select @cmd = N'create table ' + @dest_owner + QUOTENAME(@dest_tabname) + N'('
insert into #proctext(procedure_text) values( @cmd )

-- columns
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
   select @col_name = name, 
          @nullability = case isnullable 
		  when 0 then N'NOT NULL' 
		  else N'NULL' end
		  from syscolumns where id=@src_objid and @this_col=colid 
   if @isset != 0 and @col_name is not null
   begin
		if @use_base_type <> 0
		begin
			exec dbo.sp_gettypestring @src_objid, @this_col, @typestring OUTPUT
		end
		else
		begin
			exec dbo.sp_MSgettypestringudt @src_objid, @this_col, @typestring OUTPUT
		end
        select @cmd = @spacer + QUOTENAME(@col_name) + N' ' + @typestring + N' ' + @nullability 
        insert into #proctext(procedure_text) values( @cmd )
		select @art_col = @art_col + 1
        select @spacer = N','
   end
   FETCH hCColid INTO @this_col
end

CLOSE hCColid
DEALLOCATE hCColid

-- end create table
insert into #proctext(procedure_text) values( N')' )

select procedure_text from #proctext order by c1 asc
go
EXEC dbo.sp_MS_marksystemobject sp_MSscriptmvastable
GO
grant exec on dbo.sp_MSscriptmvastable to public
go
--------------------------------------------------------------------------------
--. sp_verify_publication
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_verify_publication')
revoke execute on sp_verify_publication to public
go
--------------------------------------------------------------------------------
--. sp_MSscript_insert_subwins
--------------------------------------------------------------------------------

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_insert_subwins')
drop procedure sp_MSscript_insert_subwins
go
raiserror('Creating procedure sp_MSscript_insert_subwins', 0,1)
go
create procedure sp_MSscript_insert_subwins (
	@publication sysname,
	@article     sysname, 
	@objid int,
	@columns binary(32),
	@identity_insert bit)
AS
BEGIN
	declare @cmd nvarchar(4000)
			,@qualname nvarchar(512)
			,@column_string nvarchar(4000)
			,@var_string nvarchar(4000)
			,@colname      sysname
			,@ccoltype     sysname
			,@this_col     int
			,@rc           int
			,@num_col	  int

	exec sp_MSget_qualified_name @objid, @qualname OUTPUT

	select @cmd = N'
	if (@error in (0, 547, 2601, 2627) and @execution_mode = @QSubWins)
	begin
		' + N'--
		' + N'-- Subscriber Wins resolution
		' + 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'-- do nothing
			' + N'-- will remove this code block later 
			' + N'-- for optimization
			' + N'--
			select @retcode = 0
		end'

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

	select @cmd = N'		
		else if (@rowcount = 0 and @error in (547, 2601, 2627))
		begin
			' + N'--
			' + N'-- Conflict for this command
			' + N'-- Row already exists
			' + N'-- delete and insert row with Queue values
			' + N'-- '
	insert into #proctext(procedure_text) values( @cmd )

	--
	-- script delete
	--
	select @cmd = N'
			delete ' + @qualname 
	insert into #proctext(procedure_text) values( @cmd )
	exec dbo.sp_MSscript_where_clause @objid, @columns, 'new_pk_q', NULL, 0

	--
	-- script the insert assignment
	--
	select @num_col = 0
	create table #worktab ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
	create table #worktab2 ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)

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

	OPEN hCColid
	FETCH hCColid INTO @this_col
	WHILE (@@fetch_status != -1)
	begin
		exec @rc = dbo.sp_MSget_colinfo @objid, @this_col, @columns, 0, @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
				select @column_string = QUOTENAME(@colname)
				select @var_string = N'@c' + cast(@this_col as nvarchar(4))
				
				if (@num_col > 1)
				begin
					select @column_string = N', ' + @column_string
					select @var_string = N', ' +@var_string 
				end

				insert into #worktab(procedure_text) values( @column_string )				
				insert into #worktab2(procedure_text) values( @var_string )				
				
            end
		end
		FETCH hCColid INTO @this_col
	end
	CLOSE hCColid
	DEALLOCATE hCColid

	if (@num_col > 0)
	begin
		if (@identity_insert = 1)
		begin
			-- Only to call set if identity is not marked for 'not for repl'
			-- This is to avoid security failure of 'SET' for PAL users
			if not exists (select * from syscolumns where id = @objid and
				ColumnProperty(id, name, 'IsIdNotForRepl') = 1)
			begin
				select @cmd = N'
			set identity_insert ' + @qualname + N' on '
				insert into #proctext(procedure_text) values( @cmd )
			end
		end

		select @cmd = N'
			insert into ' + @qualname + N'( '
		insert into #proctext(procedure_text) values( @cmd )
		insert into #proctext(procedure_text) 
			select procedure_text from #worktab order by c1 asc
		select @cmd = N' )
			values ( '
		insert into #proctext(procedure_text) values( @cmd )
		insert into #proctext(procedure_text) 
			select procedure_text from #worktab2 order by c1 asc
		select @cmd = N' )'
		insert into #proctext(procedure_text) values( @cmd )

		if (@identity_insert = 1)
		begin
			-- Only to call set if identity is not marked for 'not for repl'
			-- This is to avoid security failure of 'SET' for PAL users
			if not exists (select * from syscolumns where id = @objid and
				ColumnProperty(id, name, 'IsIdNotForRepl') = 1)
			begin
				select @cmd = N'
			set identity_insert ' + @qualname + N' off '
				insert into #proctext(procedure_text) values( @cmd )
			end
		end
	end
	
	select @cmd = N'
			if (@@error != 0 or @retcode != 0)
				return -1
		end
		else
			return -1
	end'
	insert into #proctext(procedure_text) values( @cmd )

	--
	-- all done
	--
	drop table #worktab
	drop table #worktab2
	return 0
END
go
exec dbo.sp_MS_marksystemobject sp_MSscript_insert_subwins
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 nvarchar(260)
			,@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, 2601, 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, 2601, 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 ' + master.dbo.fn_MSgensqescstr(@dest_owner) collate database_default 
		+ quotename(master.dbo.fn_MSgensqescstr(@dest_table) collate database_default) + 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

--------------------------------------------------------------------------------
--. sp_MSscript_update_subwins
--------------------------------------------------------------------------------

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_update_subwins')
drop procedure sp_MSscript_update_subwins
go
raiserror('Creating procedure sp_MSscript_update_subwins', 0,1)
go
create procedure sp_MSscript_update_subwins (
	@publication sysname,
	@article     sysname, 
	@objid int,
	@columns binary(32),
	@identity_insert bit)
AS
BEGIN
	declare @cmd nvarchar(4000)
			,@artid int
			,@pubid int
			,@qualname nvarchar(512)
			,@column_string nvarchar(4000)
			,@colname      sysname
			,@ccoltype     sysname
			,@this_col     int
			,@rc           int
			,@num_col	  int
			,@art_col	  int
			,@bitstr nvarchar(20)
			,@bytestr nvarchar(20)
			,@isset int
			,@timestamp_subscribed bit

	--
	-- initialize the vars we will use
	--
	select @pubid = pubid from syspublications where name = @publication
	select @artid = artid from sysarticles 
		where name = @article and pubid = @pubid
	exec sp_MSget_qualified_name @objid, @qualname OUTPUT

	--
	-- special flag for timestamp column subscription
	--
	if exists (select * from sysarticles where pubid = @pubid and
		name = @article and
		status & 32 <> 0)
		select @timestamp_subscribed = 1
	else
		select @timestamp_subscribed = 0

	--
	-- start scripting
	--
	select @cmd = N'
	if (@error in (0, 547, 2601, 2627) and @execution_mode = @QSubWins)
	begin
		' + N'--
		' + N'-- Subscriber Wins resolution
		' + N'--
		select @retcode = 0
		if (@rowcount = 0)
		begin
			--
			-- Conflict for this command
			-- Check if PK update was being done
			--'
	insert into #proctext(procedure_text) values( @cmd )
	
	--
	-- script the PK update check
	--
	select @cmd = N'
			exec @retcode = dbo.sp_MSispkupdateinconflict ' + 
		cast(@pubid as nvarchar(10)) + N', ' + cast(@artid as nvarchar(10)) + N', @bitmap'
	insert into #proctext(procedure_text) values( @cmd )

	select @cmd = N'
			if (@retcode = -1)
				return -1
			if (@retcode = 0)
			begin
				' + N'--
				' + N'-- PK update is not being done
				' + N'-- '
	insert into #proctext(procedure_text) values( @cmd )

	--
	-- script the if exists code using OLD_PK
	--
	select @cmd = N'
				if exists (select * from ' + @qualname 
	insert into #proctext(procedure_text) values( @cmd )
	exec dbo.sp_MSscript_where_clause @objid, @columns, 'upd version', NULL, 0
	insert into #proctext(procedure_text) values( N' )')
	
	--
	-- continue scripting
	--
	select @cmd = N'
				begin
					' + N'--
					' + N'-- row with OLD_PK exists
					' + N'-- update row with OLD_PK with queue values
					' + N'-- 
					select @cftcase = 11
				end'
	insert into #proctext(procedure_text) values( @cmd )

	select @cmd = N'
				else
				begin
					' + N'--
					' + N'-- row with OLD_PK does not exists
					' + N'-- insert row with queue values using OLD_PK
					' + N'-- 
					select @cftcase = 13
				end
			end'
	insert into #proctext(procedure_text) values( @cmd )

	--
	-- continue scripting
	--
	select @cmd = N'
			else
			begin
				' + N'--
				' + N'-- PK update is being done
				' + N'-- drop rows with OLD_PK, NEW_PK and insert row with queue values
				' + N'--
				select @cftcase = 0'
	insert into #proctext(procedure_text) values( @cmd )
				
	--
	-- script the if exists code using OLD_PK
	--
	select @cmd = N'
				if exists (select * from ' + @qualname 
	insert into #proctext(procedure_text) values( @cmd )
	exec dbo.sp_MSscript_where_clause @objid, @columns, 'upd version', NULL, 0
	insert into #proctext(procedure_text) values( N' )')
	
	--
	-- continue scripting
	--
	select @cmd = N'
				begin
					' + N'--
					' + N'-- row with OLD_PK exists
					' + N'-- insert row with queue values using NEW_PK +
					' + N'-- delete row with OLD_PK
					' + N'-- 
					select @cftcase = 14
				end' 
	insert into #proctext(procedure_text) values( @cmd )
	
	--
	-- script the if exists code using NEW_PK
	--
	select @cmd = N'
				if exists (select * from ' + @qualname 
	insert into #proctext(procedure_text) values( @cmd )
	exec dbo.sp_MSscript_where_clause @objid, @columns, 'new_pk_q', NULL, 0
	insert into #proctext(procedure_text) values( N' )')

	--
	-- continue scripting
	--
	select @cmd = N'
				begin
					' + N'--
					' + N'-- row with NEW_PK exists
					' + N'-- case 15: rows with NEW_PK and OLD_PK exist
					' + N'-- update row with NEW_PK with queue values + 
					' + N'-- delete row with OLD_PK'
	insert into #proctext(procedure_text) values( @cmd )

	select @cmd = N'
					' + N'-- case 16: row with NEW_PK exists
					' + N'-- update row with NEW_PK with queue values
					' + N'--
					select @cftcase = case when (@cftcase = 14) then 15 else 16 end
				end'
	insert into #proctext(procedure_text) values( @cmd )

	select @cmd = N'
				if (@cftcase = 0)
				begin
					' + N'--
					' + N'-- no existing rows with OLD_PK or NEW_PK
					' + N'-- insert with queue values
					' + N'--
					select @cftcase = 12
				end
			end'
	insert into #proctext(procedure_text) values( @cmd )

	--
	-- continue scripting
	--
	select @cmd = N'
			' + N'--
			' + N'-- based on retcode do the resolution
			' + N'-- 
			if (@cftcase in (12, 13, 14))
			begin
				' + N'--
				' + N'-- insert
				' + N'--'
	insert into #proctext(procedure_text) values( @cmd )

	--
	-- prepare the insert statement
	--
	if (@identity_insert = 1)
	begin
		-- Only to call set if identity is not marked for 'not for repl'
		-- This is to avoid security failure of 'SET' for PAL users
		if not exists (select * from syscolumns where id = @objid and
			ColumnProperty(id, name, 'IsIdNotForRepl') = 1)
		begin
			select @cmd = N'
				set identity_insert ' + @qualname + N' on '
			insert into #proctext(procedure_text) values( @cmd )
		end
	end

	select @cmd = N'
				insert into ' + @qualname + N'('
	insert into #proctext(procedure_text) values( @cmd )

	select @num_col = 0
			,@art_col = 0
			,@cmd = NULL
	create table #collisttab ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
	create table #worktab ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
	

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

	OPEN hCColid
	FETCH hCColid INTO @this_col
	WHILE (@@fetch_status != -1)
	begin
		-- Get the ordinal of the article partition or not.
		exec @isset = dbo.sp_isarticlecolbitset @this_col, @columns
		if @isset = 0
		begin
			if ((@timestamp_subscribed = 1) and 
					exists ( select * from dbo.syscolumns where id = @objid and colid = @this_col and xtype = 189))
				select @art_col = @art_col + 1
		end
		else
			select @art_col = @art_col + 1

		exec @rc = dbo.sp_MSget_colinfo @objid, @this_col, @columns, 0, @colname output, @ccoltype output
		if @rc = 0 and EXISTS (select name from dbo.syscolumns where id=@objid and colid=@this_col and iscomputed<>1)
		begin
			if rtrim(@ccoltype) != N'timestamp' 
			begin
				select @num_col = @num_col + 1
						,@bytestr = cast((1 + (@art_col-1) / 8 ) as nvarchar)
						,@bitstr =  cast( power(2, (@art_col-1) % 8 ) as nvarchar)

				select @column_string = N'case substring(@bitmap,' + 
					@bytestr + N',1) & ' + @bitstr + N' when ' + @bitstr + 
					N' then @c' + cast(@this_col as nvarchar(4)) + 
					N' else @c' + cast(@this_col as nvarchar(4)) + N'_old end '

				if (@num_col = 1)
				begin
					select @cmd = quotename(@colname)
					select @column_string = N'
					' + @column_string
				end
				else
				begin
					select @cmd = @cmd + N',' + quotename(@colname)
					select @column_string = N'
					, ' + @column_string
				end

				-- transfer the column list string to table if large
				if (len(@cmd) > 3000)
				begin
					insert into #collisttab(procedure_text) values( @cmd )
					select @cmd = N' '
				end
				insert into #worktab(procedure_text) values( @column_string )
				
            end
		end
		FETCH hCColid INTO @this_col
	end
	CLOSE hCColid
	DEALLOCATE hCColid

	-- transfer the remaining column list string to table
	insert into #collisttab(procedure_text) values( @cmd )
	
	insert into #proctext(procedure_text) 
		select procedure_text from #collisttab order by c1 asc
	select @cmd = N')
				select '
	insert into #proctext(procedure_text) values( @cmd )
	insert into #proctext(procedure_text) 
		select procedure_text from #worktab order by c1 asc

	if (@identity_insert = 1)
	begin
		-- Only to call set if identity is not marked for 'not for repl'
		-- This is to avoid security failure of 'SET' for PAL users
		if not exists (select * from syscolumns where id = @objid and
			ColumnProperty(id, name, 'IsIdNotForRepl') = 1)
		begin
			select @cmd = N'
				set identity_insert ' + @qualname + N' off '
			insert into #proctext(procedure_text) values( @cmd )
		end
	end

	--
	-- continue with scripting
	--
	select @cmd = N'
			end

			if (@cftcase = 11)
			begin
				' + N'--
				' + N'-- update using OLD_PK
				' + N'--'
	insert into #proctext(procedure_text) values( @cmd )

	--
	-- prepare the column list for update statement
	--
	select @num_col = 0
			,@art_col = 0
	create table #worktab2 ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)

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

	OPEN hCColid
	FETCH hCColid INTO @this_col
	WHILE (@@fetch_status <> -1)
	begin
		-- Get the ordinal of the article partition or not.
		exec @isset = dbo.sp_isarticlecolbitset @this_col, @columns
		if @isset = 0
		begin
			if ((@timestamp_subscribed = 1) and 
					exists ( select * from dbo.syscolumns where id = @objid and colid = @this_col and xtype = 189))
				select @art_col = @art_col + 1
		end
		else
			select @art_col = @art_col + 1

		exec @rc = dbo.sp_MSget_colinfo @objid, @this_col, @columns, 0, @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) != N'timestamp') and ColumnProperty(@objid, @colname, 'IsIdentity') != 1
			begin
				select @num_col = @num_col + 1
						,@bytestr = cast((1 + (@art_col-1) / 8 ) as nvarchar)
						,@bitstr =  cast( power(2, (@art_col-1) % 8 ) as nvarchar)

				select @column_string = quotename(@colname) + N' = case substring(@bitmap,' + 
					@bytestr + N',1) & ' + @bitstr + N' when ' + @bitstr + 
					N' then @c' + cast(@this_col as nvarchar(4)) + 
					N' else @c' + cast(@this_col as nvarchar(4)) + N'_old  end '

				if (@num_col = 1)
				begin
					select @column_string = N'
					' + @column_string
				end
				else
				begin
					select @column_string = N'
					, ' + @column_string
				end

				insert into #worktab2(procedure_text) values( @column_string )
				
			end
		end
		FETCH hCColid INTO @this_col
	end
	CLOSE hCColid
	DEALLOCATE hCColid

	--
	-- script the update with OLD_PK statement
	--
	select @cmd = N'
				update ' + @qualname + N' set '
	insert into #proctext(procedure_text) values( @cmd )
	insert into #proctext(procedure_text) 
		select procedure_text from #worktab2 order by c1 asc
	exec dbo.sp_MSscript_where_clause @objid, @columns, 'upd version', NULL, 0
	
	--
	-- continue with scripting
	--
	select @cmd = N'
			end

			if (@cftcase in (15, 16))
			begin
				' + N'--
				' + N'-- update using NEW_PK
				' + N'--'
	insert into #proctext(procedure_text) values( @cmd )
				
	--
	-- script the update with NEW_PK statement
	--
	select @cmd = N'
				update ' + @qualname + N' set '
	insert into #proctext(procedure_text) values( @cmd )
	insert into #proctext(procedure_text) 
		select procedure_text from #worktab2 order by c1 asc
	exec dbo.sp_MSscript_where_clause @objid, @columns, 'new_pk_q', NULL, 0
	
	--
	-- continue with scripting
	--
	select @cmd = N'
			end

			if (@cftcase in (14, 15))
			begin
				' + N'--
				' + N'-- Do not delete if subscriber has already
				' + N'-- updated this row as part of this transaction
				' + N'--'
	insert into #proctext(procedure_text) values( @cmd )

	--
	-- script the if exists code using OLD_PK and msprepl_tran_version
	--
	select @cmd = N'
				if not exists (select * from ' + @qualname 
	insert into #proctext(procedure_text) values( @cmd )
	exec dbo.sp_MSscript_where_clause @objid, @columns, 'subwins_check', 'msrepl_tran_version', 0
	insert into #proctext(procedure_text) values( N' )')

	select @cmd = N'
				begin
					' + N'-- 
					' + N'-- delete row with OLD_PK
					' + N'--'
	insert into #proctext(procedure_text) values( @cmd )

	--
	-- script delete with OLD_PK
	--
	select @cmd = N'
					delete ' + @qualname 
	insert into #proctext(procedure_text) values( @cmd )
	exec dbo.sp_MSscript_where_clause @objid, @columns, 'upd version', NULL, 0

	--
	-- continue with rest of scripting
	--
	select @cmd = N'
				end
			end
	
			if (@@error != 0)
				return -1

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

	--
	-- all done
	--
	drop table #collisttab
	drop table #worktab
	drop table #worktab2
	return 0
END
go
exec dbo.sp_MS_marksystemobject sp_MSscript_update_subwins
go

--------------------------------------------------------------------------------
--. sp_MSscript_update_pubwins
--------------------------------------------------------------------------------

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_update_pubwins')
drop procedure sp_MSscript_update_pubwins
go
raiserror('Creating procedure sp_MSscript_update_pubwins', 0,1)
go
create procedure sp_MSscript_update_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 nvarchar(260)
			,@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)
			,@fhasnonpkuniquekeys 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
	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 scripting
	--
	select @cmd = N'
	else if (@error in (0, 547, 2601, 2627) and @execution_mode = @QPubWins)
	begin '
	insert into #proctext(procedure_text) values( @cmd )
	--
	--  special declare for case when we have non PK unique keys
	--
	exec @fhasnonpkuniquekeys = dbo.sp_repltablehasnonpkuniquekey @tabid = @objid
	if (@fhasnonpkuniquekeys = 1)
	begin
 		declare @spacer nvarchar(5)
			,@art_col int
			,@isset int
			,@pkcolumns varbinary(32)

		select @cmd = N'
		declare '
			,@spacer = N''
			,@art_col = 1
		exec dbo.sp_getarticlepkcolbitmap @objid, @pkcolumns output
		declare #hccolid cursor local fast_forward for 
			select colid from syscolumns where id = @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=@objid and colid=@this_col and iscomputed !=1 ) 
			begin
				exec dbo.sp_gettypestring @objid, @this_col, @typestring output
				select @cmd = @cmd + @spacer + N'@pkc' + convert( nvarchar, @art_col ) + N' ' + @typestring 
				select @art_col = @art_col + 1
						,@spacer = N','

				if len( @cmd ) > 3000
				begin
					insert into #proctext(procedure_text) values( @cmd )
					select @cmd = N''
				end
			end
			fetch #hccolid into @this_col
		end
		close #hccolid
		deallocate #hccolid
		if len(@cmd) > 0
			insert into #proctext(procedure_text) values( @cmd )
	end
	--
	-- continue scripting
	--
	select @cmd = N'
		if (@rowcount = 1)
		begin
			' + N'--
			' + N'-- no conflict for this command
			' + N'-- Check if PK update was being done
			' + N'-- '
	insert into #proctext(procedure_text) values( @cmd )

	--
	-- script the PK update check
	--
	select @cmd = N'
			exec @retcode = dbo.sp_MSispkupdateinconflict ' + 
		cast(@pubid as nvarchar(10)) + N', ' + cast(@artid as nvarchar(10)) + N', @bitmap'
	insert into #proctext(procedure_text) values( @cmd )

	--
	-- continue scripting
	--
	select @cmd = N'
			if (@retcode = -1)
				return -1
			if (@retcode = 0)
			begin
				' + N'--
				' + N'-- PK update is not being done
				' + N'-- generate delete + insert compensating action with OLD_PK
				' + N'-- 
				select @cftcase = 0
			end'
	insert into #proctext(procedure_text) values( @cmd )

	--
	-- continue scripting
	--
	select @cmd = N'
			else
			begin
				' + N'--
				' + N'-- PK update is being done
				' + N'-- generate delete + insert compensating action with OLD_PK
				' + N'-- generate delete compensating action with NEW_PK
				' + N'-- 
				select @cftcase = 1
			end
		end'
	insert into #proctext(procedure_text) values( @cmd )

	--
	-- continue scripting
	--
	select @cmd = N'
		else
		begin
			' + N'--
			' + N'-- Conflict for this command
			' + N'-- Check if PK update was being done
			' + N'--'
	insert into #proctext(procedure_text) values( @cmd )

	--
	-- script the PK update check
	--
	select @cmd = N'
			exec @retcode = dbo.sp_MSispkupdateinconflict ' + 
		cast(@pubid as nvarchar(10)) + N', ' + cast(@artid as nvarchar(10)) + N', @bitmap'
	insert into #proctext(procedure_text) values( @cmd )

	select @cmd = N'
			if (@retcode = -1)
				return -1
			if (@retcode = 0)
			begin
				' + N'--
				' + N'-- PK update is not being done
				' + N'-- Now find the type of conflict
				' + N'-- '
	insert into #proctext(procedure_text) values( @cmd )
				
	--
	-- script the if exists code using OLD_PK
	--
	select @cmd = N'
				if exists (select * from ' + @qualname 
	insert into #proctext(procedure_text) values( @cmd )
	exec dbo.sp_MSscript_where_clause @objid, @columns, 'upd version', NULL, 0
	insert into #proctext(procedure_text) values( N' )')

	--
	-- continue scripting
	--
	select @cmd = N'
				begin
					' + N'--
					' + N'-- row exists
					' + N'-- generate delete + insert compensating action with OLD_PK
					' + N'--
					select @cftcase = 11'
	insert into #proctext(procedure_text) values( @cmd )
	--
	-- special scripting for table with unique keys
	--
	if (@fhasnonpkuniquekeys = 1)
	begin
		select @cmd = N'
					if (@error in (2601, 2627))
					begin
						' + N'--
						' + N'-- Unique key violation
						' + N'-- case 15 : row with OLD_PK exist and row with NEW_UNK exists 
						' + N'-- generate delete compensating action with OLD_PK'
		insert into #proctext(procedure_text) values( @cmd )
		select @cmd = N'
						' + N'-- insert compensating action with OLD_PK 
						' + N'-- insert compensating action with rows that qualify the NEW_UNKEYS
						' + N'--
						select @cftcase = 15
					end'
		insert into #proctext(procedure_text) values( @cmd )
	end
	select @cmd = N'
				end'
	insert into #proctext(procedure_text) values( @cmd )
	select @cmd = N'
				else
				begin
					' + N'--
					' + N'-- row does not exist
					' + N'-- generate delete compensating action with OLD_PK
					' + N'--
					select @cftcase = 13'
	insert into #proctext(procedure_text) values( @cmd )
	--
	-- special scripting for table with unique keys
	--
	if (@fhasnonpkuniquekeys = 1)
	begin
		select @cmd = N'
					if (@error in (2601, 2627))
					begin
						' + N'--
						' + N'-- Unique key violation
						' + N'-- case 16 : rows with OLD_PK does not exist and row with NEW_UNK exists 
						' + N'-- generate delete compensating action with OLD_PK'
		insert into #proctext(procedure_text) values( @cmd )
		select @cmd = N'
						' + N'-- insert compensating action with rows that qualify the NEW_UNKEYS
						' + N'--
						select @cftcase = 16
					end'
		insert into #proctext(procedure_text) values( @cmd )
	end
	select @cmd = N'
				end
			end'
	insert into #proctext(procedure_text) values( @cmd )
			
	--
	-- continue scripting
	--
	select @cmd = N'
			else
			begin
				' + N'--
				' + N'-- PK update is being done
				' + N'-- Now find the type of conflict
				' + N'-- 
				select @cftcase = 0'
	insert into #proctext(procedure_text) values( @cmd )
				
	--
	-- script the if exists code using OLD_PK
	--
	select @cmd = N'
				if exists (select * from ' + @qualname 
	insert into #proctext(procedure_text) values( @cmd )
	exec dbo.sp_MSscript_where_clause @objid, @columns, 'upd version', NULL, 0
	insert into #proctext(procedure_text) values( N' )')

	--
	-- continue scripting
	--
	select @cmd = N'
				begin
					' + N'--
					' + N'-- row with OLD_PK exists
					' + N'-- generate delete compensating action with OLD_PK +
					' + N'-- insert compensating action with OLD_PK 
					' + N'--
					select @cftcase = 14
				end'
	insert into #proctext(procedure_text) values( @cmd )

	--
	-- script the if exists code using NEW_PK
	--
	select @cmd = N'
				if exists (select * from ' + @qualname 
	insert into #proctext(procedure_text) values( @cmd )
	exec dbo.sp_MSscript_where_clause @objid, @columns, 'new_pk_q', NULL, 0
	insert into #proctext(procedure_text) values( N' )')

	--
	-- continue scripting
	--
	select @cmd = N'
				begin
					' + N'--
					' + N'-- row with NEW_PK exists
					' + N'-- case 15: rows with NEW_PK and OLD_PK exist
					' + N'-- generate delete compensating actions with OLD_PK, NEW_PK +
					' + N'-- insert compensating actions with OLD_PK, NEW_PK' 
	insert into #proctext(procedure_text) values( @cmd )
	select @cmd = N'
					' + N'-- case 16: row with NEW_PK exists and OLD_PK does not exist
					' + N'-- generate delete compensating action with OLD_PK, NEW_PK +
					' + N'-- insert compensating action with NEW_PK 
					' + N'--
					select @cftcase = case when (@cftcase = 14) then 15 else 16 end
				end'
	insert into #proctext(procedure_text) values( @cmd )

	--
	-- continue scripting
	--	
	select @cmd = N'
				else
				begin
					' + N'--
					' + N'-- row with NEW_PK does not exists
					' + N'-- case 12 : no existing rows with OLD_PK or NEW_PK
					' + N'-- generate delete compensating action with OLD_PK, NEW_PK '
	insert into #proctext(procedure_text) values( @cmd )
	select @cmd = N'
					' + N'-- case 17 : row with OLD_PK exist and NEW_PK does not exist 
					' + N'-- generate delete compensating action with OLD_PK, NEW_PK +
					' + N'-- insert compensating action with OLD_PK 
					' + N'-- 
					select @cftcase = case when (@cftcase = 0) then 12 else 17 end 
				end '
	insert into #proctext(procedure_text) values( @cmd )
	select @cmd = N'
			end
		end' 		
	insert into #proctext(procedure_text) values( @cmd )

	--
	-- continue scripting
	--
	select @cmd = N'
		if (@cftcase in (0, 1, 11, 12, 13, 14, 15, 16, 17))
		begin
			' + N'--
			' + N'-- generation of delete compensating command with OLD_PK
			' + N'--'
	insert into #proctext(procedure_text) values( @cmd )

	--
	-- generate delete compensating cmd with OLD_PK
	--
	select @cmd = N'
			select @cmd = ''DELETE ' + master.dbo.fn_MSgensqescstr(@dest_owner) collate database_default 
		+ quotename(master.dbo.fn_MSgensqescstr(@dest_table) collate database_default) + N' '' + '
	insert into #proctext(procedure_text) values( @cmd )
	exec dbo.sp_MSscript_where_clause @objid, @columns, 'qcft_comp', NULL, 0, 'del'
	
	--
	-- script the sending command
	--
	exec sp_MSscript_compensating_send @pubid, @artid, 0, 1
	
	--
	-- continue scripting
	--
	select @cmd = N'
		end
		
		if (@cftcase in (1, 12, 15, 16, 17))
		begin
			' + N'--
			' + N'-- generation of delete compensating command with NEW_PK
			' + N'--'
	insert into #proctext(procedure_text) values( @cmd )

	--
	-- generate delete compensating cmd with NEW_PK
	--
	select @cmd = N'
			select @cmd = ''DELETE ' + master.dbo.fn_MSgensqescstr(@dest_owner) collate database_default 
		+ quotename(master.dbo.fn_MSgensqescstr(@dest_table) collate database_default) + N' '' + '
	insert into #proctext(procedure_text) values( @cmd )
	exec dbo.sp_MSscript_where_clause @objid, @columns, 'qcft_comp', NULL, 0, 'ins'
	
	--
	-- script the sending command
	--
	exec sp_MSscript_compensating_send @pubid, @artid, 0, 1
	
	--
	-- continue scripting
	--
	select @cmd = N'
		end

		if (@cftcase in (0, 1, 11, 14, 15, 17))
		begin
			' + N'--
			' + N'-- generate and send insert compensating command with OLD_PK
			' + N'--'
	insert into #proctext(procedure_text) values( @cmd )

	--
	-- script compensating insert with OLD_PK
	--
	exec dbo.sp_MSscript_compensating_insert @publication, @article, @objid, @columns, 1, 0
	
	--
	-- continue scripting
	--
	select @cmd = N'
		end

		if (@cftcase in (15, 16))
		begin
			' + N'--
			' + N'-- generate and send insert compensating command with NEW_PK
			' + N'--'
	insert into #proctext(procedure_text) values( @cmd )

	--
	-- script compensating insert with NEW_PK
	--
	exec dbo.sp_MSscript_compensating_insert @publication, @article, @objid, @columns, 0, 0
	
	--
	-- continue with scripting
	--
	select @cmd = N'
		end
		select @retcode = 0
	end'
	insert into #proctext(procedure_text) values( @cmd )
	
	--
	-- all done
	--
	return 0	
END
go
exec dbo.sp_MS_marksystemobject sp_MSscript_update_pubwins
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('Creating procedure sp_MSscript_compensating_insert', 0,1)
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
	@fdodeclare bit = 1		-- 0 = do not script declares for non PK unique key processing
)
AS
BEGIN
	declare @cmd nvarchar(4000)
			,@artid int
			,@pubid int
			,@dest_table sysname
			,@dest_owner nvarchar(260)
			,@colname      sysname
			,@ccoltype     sysname
			,@this_col     int
			,@rc           int
			,@num_col	  int
			,@qualname nvarchar(540)
			,@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
			,@pkcolumns varbinary(32)
			,@fhasnonpkuniquekeys int
	declare @pkfetch table ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)

	--
	-- 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
			,@fhasnonpkuniquekeys = 0
	exec sp_MSget_qualified_name @objid, @qualname OUTPUT

	--
	-- Do we have non PK unique keys
	--
	exec @fhasnonpkuniquekeys = dbo.sp_repltablehasnonpkuniquekey @tabid = @objid
	if (@fhasnonpkuniquekeys = 1)
	begin
		--
		-- We have to generate commands based on where it was confict with PK or with other
		-- unique keys - so we will declare a cursor and walk through all the rows on publisher
		-- that could have conflicts with the subscriber row. For each row (PK) we will now perform
		-- compensating delete of non PK unique keys and then an compensating insert
		--
 		declare @cmd2 nvarchar(4000)
			,@cmd3 nvarchar(4000)
			,@spacer nvarchar(5)
			,@typestring sysname
			,@art_col int
			,@isset int
		declare @pkvars table ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
		declare @pkcols table ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)

		--
		-- build some strings for PK columns and PK variables (@pkc1..)
		--
		exec dbo.sp_getarticlepkcolbitmap @objid, @pkcolumns output
		select @cmd = N''
			,@cmd2 = N''
			,@cmd3 = N''
			,@spacer = N''
			,@art_col = 1
		declare #hccolid cursor local fast_forward for 
			select colid, name from syscolumns where id = @objid order by colid asc
		open #hccolid
		fetch #hccolid into @this_col, @colname
		while (@@fetch_status != -1)
		begin
			exec @isset = dbo.sp_isarticlecolbitset @this_col, @pkcolumns
			if @isset != 0 and (@colname is not null)
			begin
				exec dbo.sp_gettypestring @objid, @this_col, @typestring output
				select @cmd = @cmd + @spacer + N'@pkc' + convert( nvarchar, @art_col ) + N' ' + @typestring 
						,@cmd2 = @cmd2 + @spacer + quotename(@colname)
						,@cmd3 = @cmd3 + @spacer + N'@pkc' + convert( nvarchar, @art_col )

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

				if len( @cmd ) > 3000
				begin
					insert into @pkvars(procedure_text) values( @cmd )
					select @cmd = N''
				end
				if len( @cmd2 ) > 3000
				begin
					insert into @pkcols(procedure_text) values( @cmd2 )
					select @cmd2 = N''
				end
				if len( @cmd3 ) > 3000
				begin
					insert into @pkfetch(procedure_text) values( @cmd3 )
					select @cmd3 = N''
				end
			end
			fetch #hccolid into @this_col, @colname
		end
		close #hccolid
		deallocate #hccolid
		if len(@cmd) > 0
			insert into @pkvars(procedure_text) values( @cmd )
		if len(@cmd2) > 0
			insert into @pkcols(procedure_text) values( @cmd2 )
		if len(@cmd3) > 0
			insert into @pkfetch(procedure_text) values( @cmd3 )
		--
		-- script the PK variable declare now
		--
		if (@fdodeclare = 1)
		begin
			select @cmd = N'
			declare '
			insert into #proctext(procedure_text) values( @cmd )
			insert into #proctext(procedure_text) 
				select procedure_text from @pkvars order by c1 asc
		end
		--
		-- script the cursor declare now
		--
		select @cmd = N'
			declare #hccompins cursor local fast_forward for 
				select distinct '
		insert into #proctext(procedure_text) values( @cmd )
		insert into #proctext(procedure_text) 
			select procedure_text from @pkcols order by c1 asc
		select @cmd = N'
			from ' + @qualname 
		insert into #proctext(procedure_text) values( @cmd )
		if (@proctype = 1)
		begin
			exec @rc = sp_replscriptuniquekeywhereclause @tabid = @objid
						,@columns = @columns
						,@prefix = N'@c' 
						,@suffix = N'_old'
						,@mode = 5
		end
		else
		begin
			exec @rc = sp_replscriptuniquekeywhereclause @tabid = @objid
						,@columns = @columns
						,@prefix = N'@c' 
						,@mode = 5
		end
		--
		-- script the cursor open and fetch
		--
		select @cmd = N'
			open #hccompins
			fetch #hccompins into '
		insert into #proctext(procedure_text) values( @cmd )
		insert into #proctext(procedure_text) 
			select procedure_text from @pkfetch order by c1 asc
		select @cmd = N'
			while (@@fetch_status != -1)
			begin '
		insert into #proctext(procedure_text) values( @cmd )
		--
		-- The table has unique keys other than PK We will issue a compensating
		-- DELETE command to clear any rows that already have values for the 
		-- unique key columns - the subsequent compensating INSERT has to succeed.
		-- We will restore the current view of the publisher on to the subscriber
		-- if Subscriber had a rows that were different from publisher - then they will
		-- be resolved during the Queue reader run. So we are covered
		--
		select @cmd = N'
			' + '--
			' + '-- Issue a delete command for unique keys
			' + '-- '
		insert into #proctext(procedure_text) values( @cmd )
		select @cmd = N'
			select @cmd = ''DELETE ' + master.dbo.fn_MSgensqescstr(@dest_owner) collate database_default 
			+ quotename(master.dbo.fn_MSgensqescstr(@dest_table) collate database_default) + N' '' + '
		insert into #proctext(procedure_text) values( @cmd )
		exec dbo.sp_replscriptuniquekeywhereclause @tabid = @objid
					,@columns = @columns
					,@mode = 4
		select @cmd = N'
			from ' + @qualname 
		insert into #proctext(procedure_text) values( @cmd )
		exec @rc = sp_scriptpkwhereclause @src_objid = @objid
					,@pkcolumns = @pkcolumns
					,@prefix = N'@pkc'
					,@artcolumns = @columns
		exec sp_MSscript_compensating_send @pubid, @artid, 0, 1
		select @cmd = N'
			' + '--
			' + '-- Issue the compensating insert for this PK row
			' + '-- '
		insert into #proctext(procedure_text) values( @cmd )
	end
	
	--
	-- 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 ' + master.dbo.fn_MSgensqescstr(@dest_owner) collate database_default 
						+ quotename(master.dbo.fn_MSgensqescstr(@dest_table) collate database_default) + 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', 112) + N'' '' +  CONVERT(nvarchar(40), ' + quotename(@colname) + N', 114) + '''''''', ''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 )
							--
							-- script the where clause
							--
							if (@fhasnonpkuniquekeys = 1)
							begin
								--
								-- Non PK unique keys
								-- we are in the cursor for qualifying row
								-- 
								exec dbo.sp_scriptpkwhereclause @src_objid = @objid
											,@pkcolumns = @pkcolumns
											,@prefix = N'@pkc'
											,@artcolumns = @columns
							end
							else
							begin
								--
								-- only PK unique key
								--
								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'
							end
							--
							-- Script the compensating send
							--
							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 )
						--
						-- script the where clause
						--
						if (@fhasnonpkuniquekeys = 1)
						begin
							--
							-- Non PK unique keys
							-- we are in the cursor for qualifying row
							-- 
							exec dbo.sp_scriptpkwhereclause @src_objid = @objid
										,@pkcolumns = @pkcolumns
										,@prefix = N'@pkc'
										,@artcolumns = @columns
						end
						else
						begin
							--
							-- only PK unique key
							--
							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'
						end
						--
						-- Script the compensating send
						--
						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 )
						--
						-- script the where clause
						--
						if (@fhasnonpkuniquekeys = 1)
						begin
							--
							-- Non PK unique keys
							-- we are in the cursor for qualifying row
							-- 
							exec dbo.sp_scriptpkwhereclause @src_objid = @objid
										,@pkcolumns = @pkcolumns
										,@prefix = N'@pkc'
										,@artcolumns = @columns
						end
						else
						begin
							--
							-- only PK unique key
							--
							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'
						end
						--
						-- Script the compensating send
						--
						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 )
		--
		-- script the where clause
		--
		if (@fhasnonpkuniquekeys = 1)
		begin
			--
			-- Non PK unique keys
			-- we are in the cursor for qualifying row
			-- 
			exec dbo.sp_scriptpkwhereclause @src_objid = @objid
						,@pkcolumns = @pkcolumns
						,@prefix = N'@pkc'
						,@artcolumns = @columns
		end
		else
		begin
			--
			-- only PK unique key
			--
			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'
		end
		--
		-- Script the compensating send
		--
		exec sp_MSscript_compensating_send @pubid, @artid, 0, @setprefix
	end

	--
	-- More scripting for non PK unique key case
	--
	if (@fhasnonpkuniquekeys = 1)
	begin
		--
		-- script the cursor fetch
		--
		select @cmd = N'
			fetch #hccompins into '
		insert into #proctext(procedure_text) values( @cmd )
		insert into #proctext(procedure_text) 
			select procedure_text from @pkfetch order by c1 asc
		--
		-- script the cursor close and deallocate
		--
		select @cmd = N'
			end
			close #hccompins
			deallocate #hccompins '
		insert into #proctext(procedure_text) values( @cmd )
	end
	--
	-- all done
	--
	return 0
END
go
exec dbo.sp_MS_marksystemobject sp_MSscript_compensating_insert
go

--------------------------------------------------------------------------------
--. sp_MSscript_endproc
--------------------------------------------------------------------------------

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSscript_endproc')
drop procedure sp_MSscript_endproc
go
raiserror('Creating procedure sp_MSscript_endproc', 0,1)
go
create procedure sp_MSscript_endproc (
	@objid int, 
	@op_type varchar(3) = 'ins', -- 'ins', 'upd', 'del'
	@columns binary(32),
	@outvars nvarchar(4000),
	@queued_pub bit = 0
)
as
BEGIN
	declare @cmd nvarchar(4000)
	declare @qualname nvarchar(512)

	exec sp_MSget_qualified_name @objid, @qualname OUTPUT

	--
	-- start scripting
	--
	select @cmd = N'
	' + N'--
	' + N'-- decide the return code
	' + N'--
	if (@execution_mode = @immediate)
	begin
		if @error != 0
      		return -1
		-- Return special code to indicate the subscriber row needs to be
		-- refreshed.
		if @rowcount = 0
      		return 5
	end'
	insert into #proctext(procedure_text) values(@cmd)

	--
	-- operation specific stuff
	--
	if (@queued_pub = 1)
	begin
		if (@op_type = 'ins')
		begin
			select @cmd = N'
	if (@execution_mode = @QFirstPass)
	begin
		if (@rowcount = 0)
		begin
			if (@error in (547, 2601, 2627))
				return 2 -- insert conflict
			else
				return -1 -- error
		end
	end'
		end
		else if (@op_type = 'upd')
		begin
			select @cmd = N'
	if (@execution_mode = @QFirstPass)
	begin
		if (@rowcount = 0)
		begin
			if (@error in (0, 547, 2601, 2627))
				return 1 -- update conflict
			else
				return -1 -- error
		end
	end'
		end
		else if (@op_type = 'del')
		begin
			select @cmd = N'
	if (@execution_mode = @QFirstPass)
	begin
		if (@rowcount = 0)
		begin
			if (@error in (0, 547))
				return 3 -- delete conflict
			else
				return -1 -- error
		end
	end'
		end
		insert into #proctext(procedure_text) values(@cmd)
		
		--
		-- continue with scripting
		--
		select @cmd = N'
	
	if (@execution_mode in (@QPubWins, @QSubWins))
	begin		
		if (@@error != 0 or @retcode != 0)
			return -1 -- error
	end
    '
		insert into #proctext(procedure_text) values(@cmd)
	end

	--
	-- if we have output vars to assign do it now
	--
	if (@outvars is not null)
	begin   
		if @op_type = 'upd'
		begin
			--
			-- Script out pk var assigment that used in sp_MSscript_where_clause
			--
			exec dbo.sp_MSscript_pkvar_assignment @objid, @columns, 1
			insert into #proctext(procedure_text) values(N'
	')
		end

		select @cmd = N'
	select ' + @outvars + N'
	from ' + @qualname 
		insert into #proctext(procedure_text) values( @cmd)
		insert into #proctext(procedure_text) values( N'
	')
	
		if (@op_type = 'ins')
			exec dbo.sp_MSscript_where_clause @objid, @columns, 'new pk', null, 4
		else if (@op_type = 'upd')
			exec dbo.sp_MSscript_where_clause @objid, @columns, 'old pk', null, 4
	end

	--
	-- Final part of the proc
	--
    select @cmd = N'
    
	' + N'--
	' + N'-- past all checks
	' + N'--
	return 0
END
'
	insert into #proctext(procedure_text) values(@cmd)
	
	--
	-- all done
	--
	return 0
END
go
exec dbo.sp_MS_marksystemobject sp_MSscript_endproc
go
--------------------------------------------------------------------------------
--. sp_repltablehasnonpkuniquekey
--------------------------------------------------------------------------------

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_repltablehasnonpkuniquekey')
drop procedure sp_repltablehasnonpkuniquekey
go
raiserror('Creating procedure sp_repltablehasnonpkuniquekey',0,-1)
go
create procedure sp_repltablehasnonpkuniquekey 
(
    @tabid int              -- id of the table
)
as
begin
    set nocount on
    declare @retcode int
    --
    -- security check - should be dbo or sysadmin
    --
    exec @retcode = dbo.sp_MSreplcheck_publish
    if @@ERROR != 0 or @retcode != 0
        return (1)
    --
    -- process if the object is a table and has index
    --
    if (ObjectProperty(@tabid, 'IsTable') = 1) and (ObjectProperty(@tabid, 'TableHasIndex') = 1)
    begin
        --
        -- Set return flag if it is Unique key but not PK
        --
        if exists (select indid from sysindexes 
                where id = @tabid 
                    and indid > 0 and indid < 255
                    and (status & 2) != 0 and (status & 2048) = 0 )
        begin
            select @retcode = 1
        end
    end
    --
    -- all done
    --
    return @retcode
end
go
exec sp_MS_marksystemobject sp_repltablehasnonpkuniquekey
go

--------------------------------------------------------------------------------
--. sp_replscriptuniquekeywhereclause
--------------------------------------------------------------------------------

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_replscriptuniquekeywhereclause')
drop procedure sp_replscriptuniquekeywhereclause
go
raiserror('Creating procedure sp_replscriptuniquekeywhereclause', 0,1)
go
create procedure sp_replscriptuniquekeywhereclause 
(
    @tabid int                                  -- id of the table
    ,@columns binary(32)                 -- column map for the article
    ,@prefix nvarchar(10) = '@c'       -- prefix for the scripted column variables
    ,@suffix nvarchar(10) = null	   -- suffix for the scripted column variables
    ,@mode tinyint                           -- 1 = insert custom proc, 2 = upd custom proc non PK, 3 = upd custom proc PK only 4 = compensating delete, 5 = compensating insert cursor declare
    ,@paramcount int = null              -- Total number of parameters - needed for mode = 2 and 3
 )
as
begin
    set nocount on
    declare @retcode int
                ,@indid int
                ,@indstatus int
                ,@indkey int
                ,@qualname nvarchar(512)
                ,@colname sysname
                ,@var sysname
                ,@artcol int
                ,@thiscol int
                ,@cmd nvarchar(4000)
                ,@findexstarted bit
                ,@fisfirstindex bit
    --
    -- constants
    --
                ,@modeinscustproc tinyint
                ,@modeupdcustprocnonpk tinyint
                ,@modeupdcustprocpkonly tinyint
                ,@modedelcompensating tinyint
                ,@modeinscompensatingcursordeclare tinyint

    --
    -- initialize
    --
    select @modeinscustproc = 1
            ,@modeupdcustprocnonpk = 2
            ,@modeupdcustprocpkonly = 3
            ,@modedelcompensating = 4
            ,@modeinscompensatingcursordeclare = 5
    --
    -- security check - should be dbo or sysadmin
    --
    exec @retcode = dbo.sp_MSreplcheck_publish
    if @@error != 0 or @retcode != 0
        return (1)
    --
    -- process if the object is a table and has index
    --
    if (ObjectProperty(@tabid, 'IsTable') != 1) or (ObjectProperty(@tabid, 'TableHasIndex') != 1)
        return (1)
    --
    -- Get the qualified name of the table
    --
    exec @retcode = sp_MSget_qualified_name @tabid, @qualname OUTPUT
    if @@error != 0 or @retcode != 0 or @qualname is null
        return (1)
    --
    -- @columns cannot be null
    --
    if (@columns is null)
        return (1)
    --
    -- Check @mode
    --
    if (@mode not in (@modeinscustproc, @modeupdcustprocnonpk, @modeupdcustprocpkonly, @modedelcompensating, @modeinscompensatingcursordeclare))
    begin
        return (1)
    end
    --
    -- validate @paramcount
    --
    if ((@mode in (@modeupdcustprocnonpk,@modeupdcustprocpkonly)) and (@paramcount is null))
    begin
        return (1)
    end
    --
    -- enumerate all the unique indices
    -- The scripting will be done as follows :
    --      where (ui1k1 = @cx and ui1k2 = @cy ...) or (u2k1 = @cz and ...) ...
    -- If we are scripting for the cursor declare in insert compensating then it will be as follows:
    --      where (ui1k1 = @cx or ui1k2 = @cy ...) or (u2k1 = @cz or ...) ...
    --
    select @cmd = case when (@mode = @modedelcompensating) then N' '' where '
                            else N' where ' end
            ,@findexstarted = 0
            ,@fisfirstindex = 1
    declare #hcindid cursor local fast_forward for
        select indid, status from sysindexes 
                where id = @tabid and (status & 2) != 0
                    and indid > 0 and indid < 255
                    order by indid asc
    open #hcindid
    fetch #hcindid into @indid, @indstatus
    while (@@fetch_status != -1)
    begin
        --
        -- If we are in @modedelcompensating or @modeupdcustprocnonpk mode skip processing the PK index
        -- If we are in @modeupdcustprocpkonly mode skip processing the non PK index
        --
        if ((@mode in (@modeupdcustprocnonpk,@modedelcompensating)) and (@indstatus & 2048) != 0) 
            or ((@mode = @modeupdcustprocpkonly) and (@indstatus & 2048) = 0)
        begin
            --
            -- fetch next unique index
            --
            fetch #hcindid into @indid, @indstatus
            continue
        end
        --
        -- Enumerate the keys in this index
        --
        select @indkey = 1
        while (@indkey <= 16)
        begin
            --
            -- get the column name for the key
            --
            select @colname = index_col(@qualname, @indid, @indkey)
            if (@colname is null) 
                break
            --
            -- check if this column is enabled for replication
            --
            select @artcol = 0
            exec dbo.sp_MSget_col_position @tabid, @columns, @colname, NULL, @artcol output, 0, NULL, @thiscol output
            if (@artcol > 0)
            begin
                --
                -- check if we are scripting the first key for this index
                --
                if (@findexstarted = 1)
                begin
                    select @cmd = case when (@mode = @modedelcompensating) then @cmd + N' + '' and '
                                            when (@mode = @modeinscompensatingcursordeclare) then @cmd + N' or ' 
                                            else @cmd + N' and ' end
                end
                else
                begin
                    --
                    -- check if we are scripting the first index. 
                    --
                    if (@fisfirstindex = 0)
                    begin
                        select @cmd =  @cmd + N' or ' 
                    end
                    else
                    begin
                        select @fisfirstindex = 0
                    end
                    --
                    -- set @findexstarted while processing the first key
                    --
                    select @findexstarted = 1
                    select @cmd = @cmd + N' ( ' 
                end
                --
                -- script this column
                --
                if (@mode = @modedelcompensating)
                begin
                    --
                    -- scripting a compensating command - we are building a dynamic string
                    --
                    declare @ccoltype sysname

                    select @cmd = @cmd + quotename(@colname)				
                            ,@var = quotename(@colname)
                    exec dbo.sp_MSget_colinfo @tabid, @thiscol, @columns, 0, NULL, @ccoltype output
                    if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'varchar')
                        select @cmd = @cmd + N' = '' + ISNULL('''''''' + master.dbo.fn_MSgensqescstr(' + @var + N') collate database_default + '''''''', ''null'') '
                    else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'nvarchar')
                        select @cmd = @cmd + N' = '' + ISNULL(''N'''''' + master.dbo.fn_MSgensqescstr(' + @var + N') collate database_default + '''''''', ''null'') '
                    else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'char')
                        select @cmd = @cmd + N' = '' + ISNULL('''''''' + master.dbo.fn_MSgensqescstr(CAST(RTRIM(' + @var + N') as nvarchar)) collate database_default + '''''''', ''null'') '
                    else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'nchar')
                        select @cmd = @cmd + N' = '' + ISNULL(''N'''''' + master.dbo.fn_MSgensqescstr(CAST(RTRIM(' + @var + N') as nvarchar)) collate database_default + '''''''', ''null'') '
                    else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('binary','varbinary'))
                        select @cmd = @cmd + N' = '' + ISNULL(master.dbo.fn_varbintohexstr(' + @var + N') collate database_default, ''null'') ' 
                    else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('bit','bigint','int','smallint','tinyint','float','real','decimal','numeric'))
                        select @cmd = @cmd + N' = '' + ISNULL(CAST(' + @var + N' as nvarchar), ''null'') '
                    else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('money','smallmoney'))
                        select @cmd = @cmd + N' = '' + ISNULL(CONVERT(nvarchar(40),' + @var + N', 2), ''null'') '
                    else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'uniqueidentifier')
                        select @cmd = @cmd + N' = '' + ISNULL('''''''' + CAST(' + @var + N' as nvarchar(40)) + '''''''', ''null'') '
                    else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('datetime','smalldatetime'))
                        select @cmd = @cmd + N' = '' + ISNULL('''''''' + CONVERT(nvarchar(40), ' + @var + N', 112) + N'' '' + CONVERT(nvarchar(40), ' + @var + N', 114) + '''''''', ''null'') '	
                    else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'sql_variant')
                        select @cmd = @cmd + N' = '' + ISNULL(master.dbo.fn_sqlvarbasetostr(' + @var + N' ) collate database_default, ''null'') '
                    else
                        select @cmd = @cmd + N' = '' + ISNULL(CAST(' + @var + N' as nvarchar), ''null'') '
                end
                else
                begin
                    --
                    -- scripting for custom procs - static script
                    --
                    select @var = case when (@mode in (@modeupdcustprocnonpk,@modeupdcustprocpkonly)) 
                                                    then @prefix + cast((@artcol + @paramcount/2) as nvarchar(10))
                                                    else @prefix + cast(@artcol as nvarchar(10)) end
                    if (@suffix is not null)
                        select @var = @var + @suffix
                    --
                    -- Does the column allow NULLs
                    --
                    if (columnproperty( @tabid , @colname , 'AllowsNull' ) = 1)
                    begin
                        --
                        -- static scripting should handle NULL valued column
                        -- ((<@var> is null and <colname> is null) or (<@var> is not null and <colname> = <@var>)) 
                        --
                        select @cmd = @cmd + N'((' + @var + N' is null and ' + quotename(@colname) + N' is null) or (' 
                                                + @var + N' is not null and ' + quotename(@colname) + N' = ' + @var + N'))' 
                    end
                    else
                    begin
                        --
                        -- static scripting does not need to check for NULL values
                        -- <colname> = <@var>
                        --
                        select @cmd = @cmd + quotename(@colname) + N' = ' + @var
                    end
                    --
                    -- special processing for @modeupdcustprocnonpk
                    --
                    if (@mode = @modeupdcustprocnonpk)
                    begin
                        select @cmd = @cmd + N' and ' + @prefix + cast((@artcol + @paramcount/2) as nvarchar(10))
                        if (@suffix is not null)
                            select @cmd = @cmd + @suffix
                        select @cmd = @cmd + N' != ' + @prefix + cast(@artcol as nvarchar(10))
                        if (@suffix is not null)
                            select @cmd = @cmd + @suffix
                    end
                end
                --
                -- transfer command string to table if too large
                --
                if (len(@cmd) > 3000)
                    exec dbo.sp_MSflush_command @cmd output, 1, 0                
            end            
            --
            -- get the next key for the index
            --
            select @indkey = @indkey + 1
        end
        --
        -- done with current index
        --
        select @findexstarted = 0
                ,@cmd = case when (@mode = @modedelcompensating) then @cmd + N' + '' ) '
                                            else @cmd + N' ) ' end
        --
        -- fetch next unique index
        --
        fetch #hcindid into @indid, @indstatus
    end
    close #hcindid
    deallocate #hcindid
    --
    -- Final flush
    --
    if (@mode = @modedelcompensating)
        select @cmd = @cmd + N''''
    if (len(@cmd) > 0)
        exec dbo.sp_MSflush_command @cmd output, 1, 0                
    --
    -- all done
    --
    return 0
end
go
exec sp_MS_marksystemobject sp_replscriptuniquekeywhereclause
go

--------------------------------------------------------------------------------
--. sp_MSgettrancftsrcrow
--------------------------------------------------------------------------------

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSgettrancftsrcrow')
drop procedure sp_MSgettrancftsrcrow
go
raiserror('Creating procedure sp_MSgettrancftsrcrow',0,-1)
go
create procedure sp_MSgettrancftsrcrow ( 
	@tran_id sysname,
	@row_id sysname,
	@conflict_table nvarchar(256),
	@is_subscriber bit,
	@is_debug bit=0 )
as 
begin
	set nocount on
	declare @decllist nvarchar(4000)
			,@sellist nvarchar(4000)
			,@wherelist nvarchar(4000)
			,@cmd nvarchar(4000)
			,@cmdrow nvarchar(4000)
			,@srctable sysname
			,@srctabid int
			,@srcowner sysname
			,@columns  binary(32)
			,@indid int
			,@indkey int
			,@key sysname
			,@this_col int
			,@col sysname
			,@typestring nvarchar(60)
			,@dbname sysname
			,@retcode smallint
			,@unqualified_cft_tab sysname
			,@startoffset int
			
	--
	-- validate
	--
	if ((@tran_id is null) or (@row_id is null) or 
		(@conflict_table is null) or (@is_subscriber is null))
	begin
		raiserror('sp_MSgettrancftsrcrow(debug): @tran_id,@row_id,@conflict_table,@is_subscriber cannot be null', 16, 1)
		return (1)
	end

	--
	-- check if the conflict table is owner qualified
	--
	select @startoffset = charindex(N'].[', @conflict_table, 0)
	select @unqualified_cft_tab = case when (@startoffset > 0) 
		then substring(@conflict_table, @startoffset + 2, len(@conflict_table) - @startoffset - 1)
		else quotename(@conflict_table) end
		
	--
	-- get the source table info
	--
	if (@is_subscriber = 1)
	begin
		select @srcowner = owner, @srctable = dest_table, 
			@srctabid = OBJECT_ID(dest_table), @columns = columns
		from MSsubscription_articles
		where quotename(cft_table) = @unqualified_cft_tab
	end
	else
	begin
		select @srcowner = user_name(OBJECTPROPERTY(objid,'OwnerId')), @srctable = OBJECT_NAME(objid), 
			@srctabid = objid, @columns = columns
		from sysarticles a join sysarticleupdates b on
			a.pubid = b.pubid and 
			a.artid = b.artid
		where b.conflict_tableid = OBJECT_ID(@conflict_table)
	end

    --
    -- create code for the following :
    -- select the row of conflict with given tranid and insertdate
    -- retrieve the values of the PK/UI columns for the source table from this row in cft_table
    -- select all columns from source table using the values in a where clause for PK/UI
    --

    --
    -- PK/UI check for source table
    --
	exec @indid = dbo.sp_MStable_has_unique_index @srctabid
	if (@indid = 0)
	begin
		raiserror('sp_MSgettrancftsrcrow(debug):source table %s does not have unique index', 16, 1, @srctable)
		return (1)
	end

	--
	-- create temp tables
	--
    create table #decltext ( c1 int identity NOT NULL, cmdtext nvarchar(4000) collate database_default null)
    create table #seltext ( c1 int identity NOT NULL, cmdtext nvarchar(4000) collate database_default null)
    create table #wheretext ( c1 int identity NOT NULL, cmdtext nvarchar(4000) collate database_default null)

	--
	-- walk through each column in PK/UI and build parts of code
	--
	select @indkey = 1
	while (@indkey <= 16)
	begin	
		select @key = index_col(@srctable, @indid, @indkey)
		if (@key is null)
		begin
			select @indkey = 16
		end
		else
		begin
			--
			-- get the column index in the source table for this index key
			--
			exec dbo.sp_MSget_col_position @srctabid, @columns, @key, @col output, @this_col output

			--
			-- get the typestring for this column in source table
			--
			exec dbo.sp_gettypestring @srctabid, @this_col, @typestring OUTPUT

			--
			-- build command strings
			--
			if (@decllist is NULL)
				select @decllist = N'declare @' + @col + N' ' + @typestring
			else
				select @decllist = N' ,@' + @col + N' ' + @typestring
			
			if (@sellist is NULL)
				select @sellist = N'select @' + @col + N' = ' + quotename(@key)
			else
				select @sellist = N' ,@' + @col + N' = ' + quotename(@key)
				
			if (@wherelist is NULL)
				select @wherelist = N'where ' + quotename(@key) + N' = @' + @col
			else
				select @wherelist = N' and ' + quotename(@key) + N' = @' + @col

			--
			-- store them in the temp tables
			--
			insert into #decltext(cmdtext) values(@decllist)
			insert into #seltext(cmdtext) values(@sellist)
			insert into #wheretext(cmdtext) values(@wherelist)
			
		end
		select @indkey = @indkey + 1
	end

    --
    -- Now put all the code in order in the codetext
    --
    if exists (select * from sysobjects where name = 'MSsrcrow_codetext')
    	drop table MSsrcrow_codetext
    	
    create table MSsrcrow_codetext ( step int identity NOT NULL, cmdtext nvarchar(4000) NULL)

    insert into MSsrcrow_codetext(cmdtext)
    	select cmdtext from #decltext order by c1
    insert into MSsrcrow_codetext(cmdtext) values (N' ')
    insert into MSsrcrow_codetext(cmdtext)
    	select cmdtext from #seltext order by c1
    select @cmd = N' 
	from ' 

	if (@startoffset > 0)
		select @cmd = @cmd + @conflict_table
	else
		select @cmd = @cmd + quotename(@srcowner) + N'.' + @unqualified_cft_tab
		
	select @cmd = @cmd + N'
	where tranid = ''' + @tran_id + ''' and qcfttabrowid = ''' + @row_id + ''' '
    insert into MSsrcrow_codetext(cmdtext) values (@cmd)
    select @cmd = N'select * from ' + quotename(@srcowner) + N'.' + quotename(@srctable) + N' '
    insert into MSsrcrow_codetext(cmdtext) values (@cmd)
    insert into MSsrcrow_codetext(cmdtext)
    	select cmdtext from #wheretext order by c1

	--
	-- now execute the code we just built
	--
	if (@is_debug = 0)
	begin
		--
		-- Build 139:
		-- NOTE xp_execresultset should work here but does not
		-- return for a long time. Using exec() - should revisit
		-- this for more stable builds
		--
		declare #srccursor cursor local FAST_FORWARD FOR 
		select cmdtext from MSsrcrow_codetext order by step 
		FOR READ ONLY

		select @cmd = N' '
		select @dbname = db_name()
		open #srccursor
		fetch #srccursor into @cmdrow
		while (@@FETCH_STATUS = 0)
		begin
			select @cmd = @cmd + @cmdrow
			fetch #srccursor into @cmdrow	
		end		
		close #srccursor
		deallocate #srccursor
		
		execute(@cmd)
		if (@@error != 0)
		begin
			raiserror('sp_MSgettrancftsrcrow(debug): execute() failed for [%s] in db [%s] ', 16, 1, @cmd, @dbname)
			return (1)
		end

	end
	else
		select cmdtext from MSsrcrow_codetext order by step 

	--
	-- all done
	--
	drop table MSsrcrow_codetext
	return 0
end
go
exec dbo.sp_MS_marksystemobject sp_MSgettrancftsrcrow
go
grant execute on dbo.sp_MSgettrancftsrcrow to public
go
--------------------------------------------------------------------------------
--. sp_check_for_sync_trigger
--------------------------------------------------------------------------------

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_script_synctran_commands')
drop procedure sp_script_synctran_commands
go
raiserror('Creating procedure sp_script_synctran_commands', 0,1)
GO
CREATE PROCEDURE sp_script_synctran_commands(
    @publication sysname,    /* publication name */
    @article sysname = 'all'    /* article name, all means all article */
) 
AS
begin
    declare @retcode int

    --
    -- security check
    --
    exec @retcode = dbo.sp_MSreplcheck_publish
    if @@error <> 0 or @retcode <> 0
    begin
        return (1)
    end
    --
    -- Generate the scripts by calling internal SP
    --
    exec @retcode = dbo.sp_MSget_synctran_commands 
        @publication = @publication,
        @article = @article,
        @command_only = 1
    if @retcode <> 0 or @@error <> 0
        return (1)
end
go
EXEC dbo.sp_MS_marksystemobject sp_script_synctran_commands
GO
grant execute on dbo.sp_script_synctran_commands to public
go

--------------------------------------------------------------------------------
--. sp_replsqlqgetrows
--------------------------------------------------------------------------------

if exists (select * from sysobjects
    where type = 'P' and name = 'sp_replsqlqgetrows')
drop procedure sp_replsqlqgetrows
go
raiserror('Creating procedure sp_replsqlqgetrows', 0,1)
go
create proc sp_replsqlqgetrows (
	@publisher 		sysname
	,@publisherdb 	sysname
	,@publication 	sysname
)
as
begin
	set nocount on
	declare @retcode int

	--
	-- security check
	--
	exec @retcode = sp_MSreplcheck_subscribe
	if @@error <> 0 or @retcode <> 0
	begin
		return (1)
	end
	--
	-- return rows
	--
	if exists (select * from dbo.sysobjects where name = 'MSreplication_queue')
	begin
		select tranid, datalen, data, commandtype, insertdate, orderkey, cmdstate 
		from dbo.MSreplication_queue with (READPAST) 
		where UPPER(publisher) = UPPER(@publisher) 
			and publisher_db = @publisherdb 
			and publication = @publication
		order by orderkey, tranid 

		if (@@error != 0)
			return 1
	end
	else
	begin
		--
		-- create empty rowset
		--
		declare @nomesgs TABLE (tranid     sysname, datalen int, data varbinary(8000),
			commandtype int, insertdate datetime, orderkey bigint, cmdstate bit)
		select * from @nomesgs
	end
	
	--
	-- All done
	--
	return 0
end
go
exec dbo.sp_MS_marksystemobject sp_replsqlqgetrows
go
grant execute on dbo.sp_replsqlqgetrows to public
go
--------------------------------------------------------------------------------
--. sp_changesubstatus
--------------------------------------------------------------------------------
if exists (select * from sysobjects
     where type = 'P '
           and name = 'sp_changesubstatus')
    drop procedure sp_changesubstatus
go
print ''
print 'Creating procedure 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
						--
					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_internal @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
--------------------------------------------------------------------------------
--.	System objects (rladmin.sql)
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--.	sp_MSremove_userscript
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MSremove_userscript')
drop procedure sp_MSremove_userscript
go
create procedure sp_MSremove_userscript(
@pubid				uniqueidentifier,
@drop_publication	bit = 0
)as
	declare @retention			int
	declare @last_snapshot		datetime
	declare @post_snapshot_ver	int
	declare @post_snapshot_type	int
	declare @user_script_type	int
	declare @retcode			int
	declare @len				int

	declare @file_path			nvarchar(4000)
	declare @delfile_cmd		nvarchar(4000)
	declare @rmdir_cmd			nvarchar(4000)
	
	select @post_snapshot_type=52
	select @user_script_type=46
	
	if not exists (select * from sysmergeschemachange where pubid=@pubid and schematype=@user_script_type)
		return (0)
	select @retention=retention from sysmergepublications where pubid=@pubid
	select @last_snapshot=last_validated from sysmergesubscriptions where pubid=@pubid and subid=@pubid

	--I do not want to remove  script files by setting retention to 0
	if (@retention=0 or dateadd(day, -@retention, getdate()) < @last_snapshot) and @drop_publication = 0
		return (0)

	select @post_snapshot_ver=schemaversion from sysmergeschemachange 
		where schematype=@post_snapshot_type and pubid=@pubid
		
	--only get those script that can be safely removed
	
	declare #per_script cursor local fast_forward for
		select schematext from sysmergeschemachange 
			where pubid=@pubid and schematype=@user_script_type 
			and (schemaversion<@post_snapshot_ver or @drop_publication = 1)
	open #per_script
	fetch #per_script into @file_path
	while (@@fetch_status<>-1)
	begin
	if(left(@file_path, 1) = N'0' or left(@file_path, 1) = N'1')
		select @file_path = right(@file_path, len(@file_path) - 1)
        select @delfile_cmd = N'del "' + fn_escapecmdshellsymbolsremovequotes(@file_path) collate database_default + N'"'
        EXEC @retcode = master..xp_cmdshell @delfile_cmd, NO_OUTPUT
		if @@ERROR<>0
			goto FAILURE
		select @len=CHARINDEX ( '\' , reverse(@file_path) ) 
		select @file_path=SUBSTRING(@file_path , 1 , len(@file_path)-@len + 1)
		
        select @delfile_cmd = N'rmdir "' + fn_escapecmdshellsymbolsremovequotes(@file_path) collate database_default + N'"'
        EXEC @retcode = master..xp_cmdshell @delfile_cmd, NO_OUTPUT
		if @@ERROR<>0
			goto FAILURE
		fetch next from #per_script into @file_path
	end
	
	close #per_script
	deallocate #per_script
	return (0)
FAILURE:
	close #per_script
	deallocate #per_script
	return (1)
go
EXEC dbo.sp_MS_marksystemobject 'sp_MSremove_userscript'
go

--------------------------------------------------------------------------------
--.	sp_MScomputemergearticlescreationorder
--------------------------------------------------------------------------------
if exists (select * from sysobjects
    where type = 'P' and name = 'sp_MScomputemergearticlescreationorder')
drop procedure sp_MScomputemergearticlescreationorder
go
raiserror(15339,-1,-1,'sp_MScomputemergearticlescreationorder')
GO
CREATE PROCEDURE sp_MScomputemergearticlescreationorder
    @publication sysname
AS
    SET NOCOUNT ON
    DECLARE @pubid uniqueidentifier 
    DECLARE @max_level int
    DECLARE @current_level int
    DECLARE @update_level int
    DECLARE @limit int
    DECLARE @retcode int

    SELECT @retcode = 0

    EXEC @retcode = sp_MSreplcheck_publish
    IF @@ERROR <> 0 OR @retcode <> 0
        return (1)

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

    IF @@ERROR <> 0
        RETURN (1)

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

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

    IF @@ERROR <> 0
    BEGIN
        GOTO Failure
    END

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

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

    UPDATE #article_level_info 
       SET tree_level = @max_level
     WHERE NOT EXISTS (SELECT * 
                         FROM sysdepends 
                        WHERE source_objid = id
                          AND id <> depid)
    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
               AND d.id <> d.depid)
    
        -- 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
                AND d.id <> d.depid)
        IF @@ROWCOUNT = 0
            GOTO PHASE2
        
        IF @@ERROR <> 0
            GOTO Failure

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

PHASE2:

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

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

    DROP TABLE #article_level_info
    RETURN (0)

Failure:

    DROP TABLE #article_level_info
    RETURN (1)
GO

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

--------------------------------------------------------------------------------
--.	System objects (rlrecon.sql)
--------------------------------------------------------------------------------
if exists (select * from sysobjects
            where type = 'P' and 
            name = 'sp_MSenumschemachange_80')
    drop procedure sp_MSenumschemachange_80

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

CREATE PROCEDURE sp_MSenumschemachange_80(
	@pubid 			       uniqueidentifier,
	@AlterTableOnly					int,
	@schemaversion 	       int
	)
as
    set nocount on
	/*
	** To public
	*/
	declare	@alter_table_type		int
	declare @reinit_all_type 		int
	declare @reinit_all_upload_type int
	declare @schemaversion_of_snapshottrailer int
	
	select @reinit_all_type = 12
	select @alter_table_type = 11
	select @reinit_all_upload_type = 14
	
	if (@schemaversion is null)
		begin
		RAISERROR(14043, 16, -1, '@schemaversion')
		return (1)
		end

		--	@schema_needed = 0 - only send back reinitall command, if any 
		--  @schema_needed = 1 - normal enumeration
		--  @schema_needed = 2 - only send back alter-table command, if any
		--  @schema_needed = 3 - only send back reinitall-with-upload command, if any.

	if (@AlterTableOnly = 1)
		begin
			select pubid, artid, schemaversion, schemaguid, schematype, schematext
				from sysmergeschemachange where pubid=@pubid and schemaversion > @schemaversion and schematype = @alter_table_type
			return (0)			
		end
		
	if exists (select * from sysmergeschemachange where 
		pubid=@pubid and ((schemaversion > @schemaversion and schematype = @reinit_all_type)
		or (schemaversion > @schemaversion and schematype = @reinit_all_upload_type)))
		begin
			select @schemaversion=0
		end

	-- If subscriber missed a preparecleanup and a completecleanup they must be reinitialized
	
	if exists (select * from sysmergeschemachange where 
		pubid=@pubid and schemaversion > @schemaversion and schematype = 17) and
	    exists (select * from sysmergeschemachange where 
		pubid=@pubid and schemaversion > @schemaversion and schematype = 19) 		
		begin
			set @schemaversion=0
		end
		
    if (@schemaversion > 0) 
    begin
        -- Subscriber has already received the snapshot so filter out 
        -- the pre/post-snapshot commands. 
        -- Also filter out the schemtypes for the setlastsentgen (5) and setlastrecgen (6)
        -- This ensures that the subscriber does not apply these schema changes when 
        -- it applies incremental schema - ie the perf optimization that is implemented 
        -- by setting last sent/rec generation should be done only for brand new subscriptions.
        select pubid, artid, schemaversion, schemaguid, schematype, schematext
          from sysmergeschemachange where schemaversion > @schemaversion  
		   and pubid = @pubid
           and schematype not in (40, 45, 50, 51, 52, 53, 54, 55, 56, 57, 58, 25, 5, 6)
      order by schemaversion 
    end
    Else 
    begin
        -- Subscriber requires a snapshot, so carefully sequence the 
        -- pre/post-snapshot commands around the snapshot boundary

        create table #schemachanges 
        (   
            pubid           uniqueidentifier  NOT NULL,
            artid           uniqueidentifier  NULL,
            schemaversion   int               NOT NULL,
            schemaguid      uniqueidentifier  NOT NULL,
            schematype      int               NOT NULL,
            schematext      nvarchar(2000)    collate database_default not null,
            seqno           int identity      NOT NULL
        )
        
        truncate table #schemachanges
        -- Insert snapshot header 

        -- Header begins
        insert into #schemachanges
	    select pubid, artid, schemaversion, schemaguid, schematype, schematext
		  from sysmergeschemachange where schemaversion > @schemaversion 
		   and pubid = @pubid
           and schematype = 50

        -- Header content
        insert into #schemachanges
	    select pubid, artid, schemaversion, schemaguid, schematype, schematext
		  from sysmergeschemachange where schemaversion > @schemaversion 
		   and pubid = @pubid
           and schematype in (25, 53, 54, 55, 56, 57, 58) 
        order by schemaversion 

        -- Header ends
        insert into #schemachanges
	    select pubid, artid, schemaversion, schemaguid, schematype, schematext
		  from sysmergeschemachange where schemaversion > @schemaversion 
		   and pubid = @pubid
           and schematype = 51 

        -- End of snapshot header 
        -- Insert pre command
        insert into #schemachanges
	    select pubid, artid, schemaversion, schemaguid, schematype, schematext
		  from sysmergeschemachange where schemaversion > @schemaversion 
		   and pubid = @pubid
           and schematype = 40 

        -- Exclude pre-post, but include snapshot only commands
        insert into #schemachanges
	    select pubid, artid, schemaversion, schemaguid, schematype, schematext
		  from sysmergeschemachange where schemaversion > @schemaversion 
		   and pubid = @pubid
           and schematype in (2, 3, 4, 20, 7, 60, 61, 62, 63, 64)
		 order by schemaversion 

        -- Dynamic BCP commands
        insert into #schemachanges
	    select pubid, artid, schemaversion, schemaguid, schematype, schematext
		  from sysmergeschemachange where schemaversion > @schemaversion 
		   and pubid = @pubid
           and schematype in (131, 132)
		 order by schemaversion		 
        
        -- DRI/Trg/XPROP
        insert into #schemachanges
	    select pubid, artid, schemaversion, schemaguid, schematype, schematext
		  from sysmergeschemachange where schemaversion > @schemaversion 
		   and pubid = @pubid
           and schematype in (10, 15, 65)
		 order by schemaversion		 

        -- Insert post command
        insert into #schemachanges
	    select pubid, artid, schemaversion, schemaguid, schematype, schematext
		  from sysmergeschemachange where schemaversion > @schemaversion 
		   and pubid = @pubid
           and schematype = 45 

        -- Insert snapshot trailer
        insert into #schemachanges
	    select pubid, artid, schemaversion, schemaguid, schematype, schematext
		  from sysmergeschemachange where schemaversion > @schemaversion 
		   and pubid = @pubid
           and schematype = 52 
        
        -- Insert other schema changes
        insert into #schemachanges 
	    select pubid, artid, schemaversion, schemaguid, schematype, schematext
		  from sysmergeschemachange where schemaversion > @schemaversion 
           and schematype not in (2, 3, 4, 10, 15, 20, 7, 40, 45, 60)
           and schematype not in (61, 62, 63, 64, 65)
           and schematype not in (25, 50, 51, 52, 53, 54, 55, 56, 57, 58)
           and schematype not in (131, 132) -- dynamic bcp commands
		   and schematype not in (46, 11, 13) -- The on-demand script and schema replication commands should not be enumerated unless 
									  -- we have made sure that it was posted after the current snapshot.
		   and pubid = @pubid
		 order by schemaversion		           

		-- get the schemaversion of the snapshot trailer row
		select @schemaversion_of_snapshottrailer = schemaversion from sysmergeschemachange 
			where schemaversion > @schemaversion 
			and pubid = @pubid
			and schematype = 52 

		if (@schemaversion_of_snapshottrailer is not null)
		begin
			-- insert schema changes for on-demand script and schema replication commands which have schemaversion greater than
			-- schemaversion of the snapshot trailer row
			insert into #schemachanges 
			select pubid, artid, schemaversion, schemaguid, schematype, schematext
			from sysmergeschemachange where schemaversion > @schemaversion_of_snapshottrailer 
			and schematype in (46, 11, 13) -- The on-demand script and schema replication commands only.
			and pubid = @pubid
			order by schemaversion
        end
		    
		declare @endofsnapshot int
        select @endofsnapshot = min(seqno) from #schemachanges 
        where schematype = 52
        delete #schemachanges where seqno > @endofsnapshot
            and (schematype in (2, 3, 4, 10, 15, 20, 7, 40, 45, 65) or 
            schematype in (25, 50, 51, 52, 53, 54, 55, 56, 57, 58))

		--only list the last reinitall command 
		delete schemachanges1 from #schemachanges schemachanges1,#schemachanges schemachanges2 
			where schemachanges1.pubid=@pubid and schemachanges1.schematype=12 and 
				  schemachanges2.pubid=@pubid and schemachanges2.schematype=12 and
				  schemachanges1.schemaversion<schemachanges2.schemaversion
		
		--only list the last reinitall-with-upload command 
		delete schemachanges1 from #schemachanges schemachanges1, #schemachanges schemachanges2 
			where schemachanges1.pubid=@pubid and schemachanges1.schematype=14 and 
				  schemachanges2.pubid=@pubid and schemachanges2.schematype=14 and
				  schemachanges1.schemaversion<schemachanges2.schemaversion

        select pubid, artid, schemaversion, schemaguid, schematype, schematext
          from #schemachanges order by seqno

        drop table #schemachanges
    end

	return (0)
go
exec dbo.sp_MS_marksystemobject sp_MSenumschemachange_80
go
grant exec on dbo.sp_MSenumschemachange_80 to public
go

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

CREATE PROCEDURE sp_MSregistersubscription (
	@replication_type			int, /* Transactional = 1, Merge = 2 */
    @publisher 					sysname,
    @publisher_db 				sysname,
	@publisher_security_mode	int = NULL,						/* 0 standard; 1 integrated */
	@publisher_login			sysname = NULL,
	@publisher_password	nvarchar(524) = NULL,
    @publication 				sysname,         
	@subscriber 				sysname,
    @subscriber_db 				sysname,
	@subscriber_security_mode	int = NULL,						/* 0 standard; 1 integrated */
	@subscriber_login			sysname = NULL,
	@subscriber_password	    nvarchar(524) = NULL,
	@distributor 				sysname,
	@distributor_security_mode 	int = NULL,
	@distributor_login 			sysname = NULL,
	@distributor_password	    nvarchar(524) = NULL,
    @subscription_id			uniqueidentifier ,
	@independent_agent			int = NULL,
	@subscription_type			int,
	@use_interactive_resolver	int = NULL,
	@failover_mode				int = NULL
    ) AS

    SET NOCOUNT ON

	/* MobileSync Support */
	declare @subscription_name				nvarchar(1000)
	declare @regkey							nvarchar(1000)
    declare @subidstr						nvarchar(38)
    declare @profile_name 					nvarchar(100)
	declare @retcode						int
	
	set @subscription_name = @publisher + ':' + @publisher_db + ':' + @publication + ':' + @subscriber + ':' + @subscriber_db
	/* Replace back slash with forward slash so that the key name is a valid REGISTRY key name */
	set @subscription_name = REPLACE(@subscription_name,'\','/')
	set @regkey = 'SOFTWARE\Microsoft\Microsoft SQL Server\80\Replication\Subscriptions\' + @subscription_name
	set @subidstr = '{' + convert ( nchar(36), @subscription_id) + '}'
    set @profile_name = formatmessage(20550) -- SyncMgr Profile

	EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
                               @regkey,
                               'ProfileName',
                               'REG_SZ',
                                @profile_name	
	if @retcode <> 0 OR @@ERROR <> 0
		return 1

	EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
                               @regkey,
                               'ReplicationType',
                               'REG_DWORD',
                                @replication_type	
	if @retcode <> 0 OR @@ERROR <> 0
		return 1

	EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
                               @regkey,
                               'SubscriptionType',
                               'REG_DWORD',
                                @subscription_type	
	if @retcode <> 0 OR @@ERROR <> 0
		return 1

    EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
                               @regkey,
                               'Subid',
                               'REG_SZ',
                                @subidstr	
	if @retcode <> 0 OR @@ERROR <> 0
		return 1

    EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
                               @regkey,
                               'Publisher',
                               'REG_SZ',
                                @publisher	                                    
	if @retcode <> 0 OR @@ERROR <> 0
		return 1

    EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
                               @regkey,
                               'PublisherDb',
                               'REG_SZ',
                                @publisher_db
	if @retcode <> 0 OR @@ERROR <> 0
		return 1

	
	IF @use_interactive_resolver IS NOT NULL
	BEGIN
		EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
                               @regkey,
                               'UseInteractiveResolver',
                               'REG_DWORD',
                                @use_interactive_resolver
		if @retcode <> 0 OR @@ERROR <> 0
			return 1
	END

	/* If Publisher security mode is NOT NULL, write out the entries */
	if @publisher_security_mode IS NOT NULL
		begin
		   	EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
		                               @regkey,
		                               'PublisherSecurityMode',
		                               'REG_DWORD',
		                                @publisher_security_mode
			if @retcode <> 0 OR @@ERROR <> 0
				return 1

		    EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
		                               @regkey,
		                               'PublisherLogin',
		                               'REG_SZ',
		                                @publisher_login                                    
			if @retcode <> 0 OR @@ERROR <> 0
				return 1

		end
    EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
                               @regkey,
                               'Publication',
                               'REG_SZ',
                                @publication
	if @retcode <> 0 OR @@ERROR <> 0
		return 1

    EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
                               @regkey,
                               'Subscriber',
                               'REG_SZ',
                                @subscriber
	if @retcode <> 0 OR @@ERROR <> 0
		return 1

	/* If Subscriber security mode is NOT NULL, write out the entries */
	if @subscriber_security_mode IS NOT NULL
		begin
		    EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
                               @regkey,
                               'SubscriberSecurityMode',
                               'REG_DWORD',
                                @subscriber_security_mode
			if @retcode <> 0 OR @@ERROR <> 0
				return 1

		    EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
                               @regkey,
                               'SubscriberLogin',
                               'REG_SZ',
                                @subscriber_login                                    
			if @retcode <> 0 OR @@ERROR <> 0
				return 1

		end

    EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
                               @regkey,
                               'SubscriberDb',
                               'REG_SZ',
                                @subscriber_db                                    
	if @retcode <> 0 OR @@ERROR <> 0
		return 1
				
    EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
                               @regkey,
                               'Distributor',
                               'REG_SZ',
                                @distributor
	if @retcode <> 0 OR @@ERROR <> 0
		return 1

	/* If Distributor security mode is NOT NULL, write out the entries */
	if @distributor_security_mode IS NOT NULL
		begin
		    EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
                               @regkey,
                               'DistributorSecurityMode',
                               'REG_DWORD',
                                @distributor_security_mode
			if @retcode <> 0 OR @@ERROR <> 0
				return 1

		    EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
                               @regkey,
                               'DistributorLogin',
                               'REG_SZ',
                                @distributor_login                                    
			if @retcode <> 0 OR @@ERROR <> 0
				return 1

		end

    if @independent_agent IS NOT NULL
    	begin
			EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
                               @regkey,
                               'IndependentAgent',
                               'REG_DWORD',
                                @independent_agent	
			if @retcode <> 0 OR @@ERROR <> 0
				return 1
		end				

	IF @failover_mode IS NOT NULL
		begin
			EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
                               @regkey,
                               'FailoverMode',
                               'REG_DWORD',
                                @failover_mode	
			if @retcode <> 0 OR @@ERROR <> 0
				return 1

		end			

	-- Mark enabled_for_syncmgr bit if every thing succeeded.
	-- If the row exists in MSreplication_properties table, 
	-- set enabled_for_syncmgr bit
	-- The logic need to be here because UI call this sp directly.
	if exists (select * from sysobjects where name = 'MSsubscription_properties')
	begin
		update MSsubscription_properties set enabled_for_syncmgr = 1 where
			UPPER(publisher) = UPPER(@publisher)
			and publisher_db =  @publisher_db
			and publication = @publication
		if @retcode <> 0 OR @@ERROR <> 0
			return 1
	end

	return 0
GO

exec sp_MS_upd_sysobj_category 2
go

exec sp_configure 'allow updates',0
go

reconfigure with override
go
