/*------------------------------------------------------------------------------

sp2_repl.sql
THIS SCRIPT UPDATES REPLICATION SYSTEM STORED PROCEDURES FROM SQL 2000 to SQL 2000  SP1

Changes in this file are organized as follows (please maintain):
	Common system objects (replsys.sql)
	Common repl objects (replcom.sql)
	Tran repl objects (repltran.sql) 
	Merge repl objects (rladmin.sql, rlrecon.sql, rlcore.sql)

Notes:
+ Catalog-updates and sp_MS_upd_sysobj_category are enabled for the entire
	file.  Do not disable or re-enable them.  Please do not change set options.
+ grep for "--.
------------------------------------------------------------------------------*/

--------------------------------------------------------------------------------
-- VERIFY Server is started in single-user-mode (catalog-updates enabled), and
--	start marking of system-objects.
--------------------------------------------------------------------------------
use master
go

dump tran master with no_log
go

exec dbo.sp_configure 'allow updates',1
go
reconfigure with override
go

set ANSI_NULLS off

exec sp_MS_upd_sysobj_category 1
go

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

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

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

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

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

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

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

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

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

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

if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSenumsubscriptions')
	drop proc sp_MSenumsubscriptions
	
if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_vupgrade_subpass')
	drop proc sp_vupgrade_subpass

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

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

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

if exists (select * from sysobjects 
		where name = 'sp_vupgrade_replication' 
				and type = 'P')
	  drop procedure sp_vupgrade_replication
go
if exists (select * from sysobjects
	where type = 'P '
			and name = 'sp_MSdrop_rlcore')
begin
	drop procedure sp_MSdrop_rlcore
end

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

	if exists( select * from sysobjects where type = 'P ' and name = 'sp_MSpurgecontentsorphans')
	begin
		drop procedure sp_MSpurgecontentsorphans
	end
	
	if exists( select * from sysobjects where type = 'P ' and name = 'sp_MScleanup_zeroartnick_genhistory')
	begin
		drop procedure sp_MScleanup_zeroartnick_genhistory
	end
	
	if exists( select * from sysobjects where type = 'P ' and name = 'sp_MSdelete_specifiedcontents')
	begin
		drop procedure sp_MSdelete_specifiedcontents
	end

go
exec dbo.sp_MS_marksystemobject sp_MSdrop_rlcore
go
/* Create  sp_replicationdboption */
raiserror('Creating procedure sp_replicationdboption', 0,1)
GO

CREATE PROCEDURE sp_replicationdboption (
      @dbname    sysname,
      @optname   sysname,
      @value     sysname,
      @ignore_distributor bit = 0,
	  @from_scripting bit = 0
    ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */
    
    declare @alert_name     sysname
    declare @alert_id       int
    declare @command        nvarchar(255)
    declare @description    nvarchar(500)
    declare @category_name  sysname
    declare @agentname      sysname
    DECLARE @retcode        int
    DECLARE @optbit         int
    DECLARE @optbit_value   int /* Desired value with the optbit mask */
    DECLARE @proc           nvarchar(255)
		, @category int

    /*
    ** Initialization
    */

    /*
    ** Parameter check
    ** @dbname
    */
    SELECT @category = category FROM master.dbo.sysdatabases WHERE
        name = @dbname collate database_default
	if @category is null
    BEGIN
        RAISERROR(15010, 16, -1, @dbname)
        RETURN(1)
    END

    /*
    ** Parameter check
    ** @type
    */
    IF @optname is null or LOWER(@optname collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('publish',
        'merge publish',
		'subscribe', -- Used by sp_dboption for backward compatibility only.
		'sync with backup',
		'max cmds in tran'
		)
    BEGIN
        RAISERROR(14138,16,-1,@optname)
        RETURN(1)
    END

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

    /*
    ** Security Check
    */
	-- This proc is not granted to public. Only other system proc or sysadmin can use this
	-- sp_dboption will call this and it has its own security check,

    /*
    **  If we're in a transaction, disallow this since it might make recovery
    **  impossible.
    **
    */
    IF @@trancount > 0 
    BEGIN
        RAISERROR(15002,16,-1,'sp_replicationdboption')
        RETURN(1)
    END
    
    IF LOWER(@optname collate SQL_Latin1_General_CP1_CS_AS) = 'publish'
        BEGIN
            SELECT @optbit = 1
            SELECT @proc = QUOTENAME(@dbname) + '.dbo.sp_MSpublishdb'
        END
    ELSE IF LOWER(@optname collate SQL_Latin1_General_CP1_CS_AS) = 'merge publish'
        BEGIN
            SELECT @optbit = 4
            SELECT @proc = QUOTENAME(@dbname) + '.dbo.sp_MSmergepublishdb'
        END
    ELSE IF LOWER(@optname collate SQL_Latin1_General_CP1_CS_AS) = 'subscribe'
        BEGIN
            SELECT @optbit = 2
        END
    ELSE IF LOWER(@optname collate SQL_Latin1_General_CP1_CS_AS) = 'sync with backup'
        BEGIN
            SELECT @optbit = 32
        END
    ELSE IF LOWER(@optname collate SQL_Latin1_General_CP1_CS_AS) = 'max cmds in tran'
        BEGIN
            SELECT @optbit = 64
        END

    IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'
        SELECT @optbit_value = @optbit
    ELSE
        SELECT @optbit_value = 0

          
    /*
    ** Check if the option is set as required already
    */
	if (@category & @optbit) = @optbit_value 
	BEGIN
		if @optbit_value = 64 -- setting 'max cmds in tran' to the same, do nothing
			RETURN (0)
        if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'
            RAISERROR (14035, 10, -1, @optname, @dbname)        
        else
            RAISERROR (14037, 10, -1, @optname, @dbname)
        RETURN (1)
    END

	-- If turning on 'sync with backup', make sure 'publish' or 'dist' is turned on already.
	if  @optbit_value = 32 and (@category & 1 = 0 and @category & 16 = 0)
	begin
		raiserror(20019, 16, -1, 'sync with backup')
		return (1)
	end
	if  @optbit_value = 64 and (@category & 16 = 0)
	begin
		raiserror(20019, 16, -1, 'max cmds in tran')
		return (1)
	end

	-- We do not allow turning on sync with backup mode at publishing db if the db is
	-- in simple recovery mode
	if  @optbit_value = 32 and @category & 1 <> 0 and 
		databasepropertyex(@dbname, 'recovery') = 'SIMPLE'
	begin
		raiserror(20622, 16, -1, 'sync with backup')
		return (1)
	end
	
	-- If turning off 'publish', turn off 'sync with backup' as well if the database
	-- is not a distribution database.
	if @optbit = 1 and @optbit_value = 0 and @category & 32 <> 0 and @category & 16 = 0
	begin
		EXEC @retcode =  dbo.sp_replicationdboption   
			@dbname = @dbname,
			@optname = 'sync with backup',
			@value = 'false',
			@ignore_distributor = @ignore_distributor,
			@from_scripting = @from_scripting
		IF @@ERROR <> 0 or @retcode <> 0
		BEGIN
			GOTO UNDO
		END
	end

	-- if turning on 'sync with backup' a distribution database, initialize the backup lsns 
	-- to nulls, this should be done before the category bit is set.
	declare @backup_proc nvarchar(1000)
	if @optbit_value = 32 and @category & 16 <> 0
	begin
        SELECT @backup_proc = QUOTENAME(@dbname) + 
			'.dbo.sp_MSrepl_init_backup_lsns'
		exec @retcode = @backup_proc
        if @@error <> 0 or @retcode <> 0
            goto UNDO
	end

    /*
    ** Prepare the required option
    */
	if @proc is not null
	begin
		EXEC @retcode = @proc @value = @value,
			@ignore_distributor = @ignore_distributor
		IF @@ERROR <> 0 or @retcode <> 0
		BEGIN
			GOTO UNDO
		END
	end


    /*
    ** Preparation succeeded. 
    ** Toggle the category bit in master..sysdatabases
    */
    UPDATE master..sysdatabases SET category = category ^ @optbit
    WHERE name = @dbname
    IF @@ERROR <> 0 
    BEGIN
        GOTO UNDO
    END


    declare @num_mergedb int
    select @num_mergedb = null
    if lower(@optname collate SQL_Latin1_General_CP1_CS_AS) = 'merge publish'
    begin
        if lower(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'
        begin            
            -- Set the 'startup' option for sp_MScleanupmergepublisher if the
            -- database is enabled for merge replication.  
            exec ('use master 
                   exec dbo.sp_procoption ''sp_MScleanupmergepublisher'', ''startup'', ''true''')
        end
        else
        begin
            -- Reset the 'startup' option for sp_MScleanupmergepublisher if
            -- this is the last database that has its 'merge publish' option 
            -- disabled
            select @num_mergedb = count(*) from master..sysdatabases 
             where (category & 4) <> 0
            if @num_mergedb = 0
            begin
                exec ('use master 
                       exec dbo.sp_procoption ''sp_MScleanupmergepublisher'', ''startup'', ''false''')
            end
        end
    end
    

	-- Get expired subscription cleanup agent name
    set @agentname = formatmessage(20569)  

    IF ((LOWER(@optname collate SQL_Latin1_General_CP1_CS_AS) = 'merge publish') or (LOWER(@optname) = 'publish')) and (LOWER(@value) = 'true')
    BEGIN
        IF NOT EXISTS (SELECT * FROM msdb..sysjobs_view WHERE name = @agentname collate database_default and
						UPPER(originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName'))))
            BEGIN
                SELECT @command =  'EXEC dbo.sp_expired_subscription_cleanup'
            
                set @description = formatmessage(20542)
    
                select @category_name = name FROM msdb.dbo.syscategories where category_id = 17   
                
                EXECUTE @retcode = msdb.dbo.sp_MSadd_repl_job @agentname,
                @subsystem = 'TSQL',
                @server = @@SERVERNAME,
                @databasename = @dbname,
                @description = @description,
                @freqtype = 4,        -- daily
                @activestarttimeofday=010000,   -- from 01:00:00 am
                @command = @command,
                @enabled = 1,
                @retryattempts = 0,
                @loghistcompletionlevel = 0,
                @category_name = @category_name
            
                IF @@ERROR <> 0 or @retcode <> 0
                    BEGIN
                         return (1)
                    END
            END

		-- Expired subscription cleanup alert            
        select @category_name = name FROM msdb.dbo.syscategories where category_id = 20
        set @alert_name = formatmessage(20538)  
        set @alert_id = 14157 -- corresponding to formatmessage(20538)
        if not exists (select * from msdb.dbo.sysalerts where message_id = @alert_id)
        begin
            exec @retcode = msdb.dbo.sp_add_alert @enabled = 0, @name = @alert_name, @category_name = @category_name, @message_id = 14157
            if @@error <> 0 or @retcode <> 0
                goto UNDO
        end
    END

    IF ((LOWER(@optname collate SQL_Latin1_General_CP1_CS_AS) = 'merge publish') or (LOWER(@optname collate SQL_Latin1_General_CP1_CS_AS) = 'publish')) and (LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'false')
    BEGIN
            IF (EXISTS (SELECT * FROM msdb..sysjobs_view WHERE name = @agentname collate database_default and 
							UPPER(originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName'))))) 
                and (NOT exists (select name from master..sysdatabases where category & 4 =4 ))
                and (NOT exists (select name from master..sysdatabases where category & 1 =1))
        BEGIN
            EXEC @retcode = msdb.dbo.sp_delete_job  @job_name = @agentname
            IF @@ERROR <> 0 or @retcode <> 0
                return (1)            
        END
        
        set @alert_id = 14157 -- cleanup  alert
        set @alert_name = formatmessage(20569)
        if exists (select * from msdb.dbo.sysalerts where message_id=@alert_id)
            and (NOT exists (select name from master..sysdatabases where category & 4 =4 ))
                and (NOT exists (select name from master..sysdatabases where category & 1 =1))
        begin
            select @alert_name=name from msdb.dbo.sysalerts where message_id=@alert_id
            exec @retcode = msdb.dbo.sp_delete_alert @alert_name
            if @@error <> 0 or @retcode <> 0
                return (1)            
        end
    END
    
    /*
    **  ??? 
    ** CHECKPOINT the database that was changed. Make the change
    ** effective immediatly
    */
    CHECKPOINT
    IF @@ERROR <> 0 
    BEGIN
        RETURN(1)
    END

    RETURN(0)

UNDO:
    -- Create system table is not allowed in a multi-statement transactions.
    -- Drop the tables here
    IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'
        EXEC dbo.sp_replicationdboption 
          @dbname    = @dbname,
          @optname   = @optname,
          @value     = 'false',
          @ignore_distributor = @ignore_distributor

    return(1)   
GO

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

create procedure sp_MSmakeconflictinsertproc 
	(@tablename sysname, @ownername sysname, @procname sysname, @basetableid int, @pubid uniqueidentifier=NULL)
as
declare @arglist	nvarchar(4000)
declare @header		nvarchar(4000)
declare @qualname   nvarchar(270)
declare @argname	nvarchar(270)
declare @noset		bit
declare @wherepc	nvarchar(255)
declare @id 		int
declare @sync_objid	int
declare @colname nvarchar(140)
declare @typename sysname
declare @colid smallint
declare @status tinyint
declare @len smallint
declare @prec smallint
declare @scale int
declare @retcode smallint
declare @sys_loop bit
declare @old_colname nvarchar(140)
declare @create_time_col nvarchar(8)
declare @p_number_for_conflict_type nvarchar(270)

set nocount on

select @sys_loop = 0
set @create_time_col = NULL
set @id = NULL

/*
** To check if specified object exists in current database
*/
select @id = id, @ownername=user_name(uid) from sysobjects where name = @tablename
	if @id is NULL return (1)

set @qualname = QUOTENAME(@ownername) + '.' + QUOTENAME(@tablename)

-- create temp table to select the command text out of
create table #tempcmd (phase int NOT NULL, step int identity NOT NULL, 
cmdtext nvarchar(4000) collate database_default null)

select @header = 'Create procedure dbo.' + @procname + ' ( ' 
insert into #tempcmd (phase, cmdtext) values (0,  @header)

select @sync_objid = sync_objid from sysmergearticles where objid = @basetableid and (pubid = @pubid or @pubid is NULL)

select @colid = min(colid) from syscolumns where id = @id and iscomputed <>1 and 
	type_name(xtype) <> 'timestamp' and ((name not in (select name from syscolumns where id=@basetableid) 
		and @sys_loop =1) OR
	 (name in (select name from syscolumns where id=@basetableid) and @sys_loop =0))
select @colname = c.name, @status = c.status, @typename = t.name, @len = c.length,
	@prec = c.prec, @scale = c.scale
	from syscolumns c, systypes t
	where c.id = @id and c.colid = @colid and c.xusertype = t.xusertype 

/*
** Get the column list from the conflict_table schema and filter it with 
table view for vertical partitioning
*/
Reverse_Order:
while (@colname is not null) 
begin
	set @noset = 0
	if exists (select * from syscolumns where name=@colname and id=@basetableid)
		and not exists (select * from syscolumns where name=@colname and id=@sync_objid)
		goto NEXT_COL 		
	if @typename='nvarchar' or @typename='nchar' -- a unit of nchar takes 2 bytes
		select @len = @len/2
	exec @retcode = dbo.sp_MSmaptype @typename out, @len, @prec, @scale
	if @@ERROR<>0 or @retcode<>0 return (1)
	select @argname = '@p' + rtrim(convert(nchar, @colid))
	
	if LOWER(@colname collate SQL_Latin1_General_CP1_CS_AS)='conflict_type'
		select @p_number_for_conflict_type=@argname
		
	-- based on colid, add text to appropriate pieces
	if (COLUMNPROPERTY( @basetableid, @colname, 'IsRowGuidCol') = 1)  
		begin
			select @noset =1
			set @wherepc = ' where rowguidcol = ' + @argname
		end
	else if (@colname = 'origin_datasource')
		begin
			select @wherepc =@wherepc +  ' and origin_datasource = ' + @argname
			set @noset =1
		end
	set @old_colname = @colname
	set @colname = QUOTENAME(@colname)
	if @arglist is null
		begin
		set @arglist = @argname + ' ' + @typename
		--give default value of NULL to new merge columns for backward compatibility concern
		insert into #tempcmd (phase, cmdtext) values (3, @colname)
		select @header = ') values ('
		insert into #tempcmd (phase, cmdtext) values (4, @header)

		insert into #tempcmd (phase, cmdtext) values (4, @argname)
		if @noset=0
			insert into #tempcmd (phase, cmdtext) values (1, @colname + ' = ' + @argname)
		end
	else 
		begin
		if len(@arglist)>3700
			begin
				insert into #tempcmd (phase, cmdtext) values (0,  @arglist)			
				select @arglist = ' '
			end
		set @arglist = @arglist + ', ' + @argname + ' ' + @typename
		if @sys_loop = 1 and @old_colname not in ('origin_datasource','conflict_type','reason_code','reason_text', 'pubid') 
			begin
				select @arglist=@arglist + ' = NULL'		
				if @old_colname='MSrepl_create_time'
					select @create_time_col=@argname
			end

		insert into #tempcmd (phase, cmdtext) values (3, ',' + @colname)

		insert into #tempcmd (phase, cmdtext) values (4, ',' + @argname)

		if @noset =0
			begin
				if exists (select * from #tempcmd where phase=1)
					insert into #tempcmd (phase, cmdtext) values (1, ',' + @colname + ' = ' + @argname)
				else
					insert into #tempcmd (phase, cmdtext) values (1, @colname + ' = ' + @argname)
			end
		end
NEXT_COL:
	select @colid = min(colid) from syscolumns where id = @id and colid>@colid and iscomputed <>1 and 
		type_name(xtype) <> 'timestamp' and 
		((name not in (select name from syscolumns where id=@basetableid) and @sys_loop =1) OR
	 		(name in (select name from syscolumns where id=@basetableid) and @sys_loop =0))
	set @colname = NULL
	select @colname = c.name, @status = c.status, @typename = t.name, @len = c.length,
		@prec = c.prec, @scale = c.scale
		from syscolumns c, systypes t
		where c.id = @id and c.colid = @colid and c.xusertype = t.xusertype
end

if @sys_loop = 0
begin
	select @sys_loop = 1
	select @colid = min(colid) from syscolumns where id = @id  and iscomputed <>1 
		and type_name(xtype) <> 'timestamp' and 
		((name not in (select name from syscolumns where id=@basetableid) and @sys_loop =1) OR
	 	(name in (select name from syscolumns where id=@basetableid) and @sys_loop =0))
	select @colname = c.name, @status = c.status, @typename = t.name, @len = c.length,
		@prec = c.prec, @scale = c.scale
		from syscolumns c, systypes t
			where c.id = @id and c.colid = @colid and c.xusertype = t.xusertype 
	goto Reverse_Order
end

-- now create the procedure
select @procname = QUOTENAME(@procname)

insert into #tempcmd (phase, cmdtext) values (0,  @arglist)			

select @header =  ') as'
insert into #tempcmd (phase, cmdtext) values (0,  @header)

select @header = ' '
-- for ease of expansion here in case we add new merge columns in conflict tables.
if @create_time_col is not NULL
	select @header = @header + ' 
		select ' + @create_time_col + ' = getdate() '

select @header = @header + ' if exists (select * from ' + @qualname + ' ' + @wherepc + ')
	begin
	update ' + @qualname + ' set ' 
insert into #tempcmd (phase, cmdtext) values (0,  @header)

--see comment in sp_MSinsertdeleteconflict for this <5 or >4 checking.

select @header = @wherepc + ' and (conflict_type<5 or ' + @p_number_for_conflict_type + ' >4) 
	end
	else
	insert into ' + @qualname + ' ('
insert into #tempcmd (phase, cmdtext) values (2,  @header)

insert into #tempcmd (phase, cmdtext) values (4, ')')

-- Now we select out the command text pieces in proper order so that our caller,
-- xp_execresultset will execute the command that creates the stored procedure.

select cmdtext from #tempcmd order by phase, step
drop table #tempcmd
go
exec dbo.sp_MS_marksystemobject sp_MSmakeconflictinsertproc 
go
grant exec on dbo.sp_MSmakeconflictinsertproc to public
go


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

SET ANSI_NULLS ON 
SET QUOTED_IDENTIFIER ON
GO
-- Call by snapshot
create procedure sp_MSsetartprocs
	(@publication		sysname,
	@article			sysname,
	@force_flag 		int = 0)
as
	declare @ownername sysname
	declare @objectname sysname
	declare @guidstr nvarchar(40)
	declare @pubidstr nvarchar(40)
	declare @conflict_proc sysname
	declare @conflict_table sysname
	declare @snapshot_ready int
	declare @ins_procname sysname
	declare @sel_procname sysname
	declare @upd_procname sysname
	declare @view_selprocname nvarchar(290)
	declare @viewname sysname
	declare @artid uniqueidentifier
	declare @pubid uniqueidentifier
	declare @objid int
    declare @rgcol nvarchar(140)
	declare @sync_objid int
	declare @retcode smallint
	declare @dbname sysname
	declare @command  nvarchar(1000)
	
	set nocount on
	/*
	** Check to see if current publication has permission
	*/
	exec @retcode=sp_MSreplcheck_publish
	if @retcode<>0 or @@ERROR<>0 return (1)
	
	-- figure out pubid and artid
	if @force_flag = 1
		begin
		-- don't qualify that must be publisher when we are forcing remake at subscribers
		select @pubid = pubid, @snapshot_ready=snapshot_ready from sysmergepublications where name = @publication and 
			pubid in (select pubid from sysmergearticles where name=@article)
		end
	else
		select @pubid = pubid, @snapshot_ready=snapshot_ready 
			from sysmergepublications where name = @publication and UPPER(publisher)=UPPER(@@SERVERNAME) and publisher_db=db_name()
    if @pubid IS NULL
        BEGIN
			RAISERROR (20026, 16, -1, @publication)
    	    RETURN (1)
        END

	select @conflict_table=NULL
	select @artid = artid, @objid = objid, @sync_objid = sync_objid, @conflict_table=conflict_table FROM sysmergearticles WHERE name = @article	AND pubid = @pubid
    if @artid IS NULL
        BEGIN
			RAISERROR (20027, 16, -1, @article)
            RETURN (1)
        END

	/* Drop the article procs if they preexist */
	exec @retcode = dbo.sp_MSdroparticleprocs @pubid, @artid
	if @@ERROR<>0 OR @retcode<>0 
		begin
			return (1)
		end
	
	-- get owner name, and table name
	select @objectname = name, @ownername = user_name(uid)	from sysobjects
		where id = @objid 

	-- make the insert and update proc names
	exec @retcode = dbo.sp_MSguidtostr @artid, @guidstr out
	if @@ERROR <>0 OR @retcode <>0 return (1)

	exec @retcode = dbo.sp_MSguidtostr @pubid, @pubidstr out
	if @@ERROR <>0 OR @retcode <>0 return (1)

	select @ins_procname = 'sp_ins_' + substring(@guidstr, 1, 16) + substring(@pubidstr, 1, 16)
	exec dbo.sp_MSuniqueobjectname @ins_procname, @ins_procname output
	if @@ERROR <>0 OR @retcode <>0 return (1)
	
	select @upd_procname = 'sp_upd_' + substring(@guidstr, 1, 16) + substring(@pubidstr, 1, 16)
	exec dbo.sp_MSuniqueobjectname @upd_procname, @upd_procname output
	if @@ERROR <>0 OR @retcode <>0 return (1)

	select @sel_procname = 'sp_sel_' + substring(@guidstr, 1, 16) + substring(@pubidstr, 1, 16)
	exec dbo.sp_MSuniqueobjectname @sel_procname, @sel_procname output
	if @@ERROR <>0 OR @retcode <>0 return (1)

	set @view_selprocname = 'sel_' + substring(@guidstr, 1, 16) + substring(@pubidstr, 1, 16)
    exec @retcode = dbo.sp_MSuniqueobjectname @view_selprocname , @view_selprocname output
    if @retcode <> 0 or @@ERROR <> 0 return (1) 

	-- create the procs
	set @dbname = db_name()

	set @command = 'sp_MSmakeinsertproc ' + QUOTENAME(@objectname) + ' , ' + QUOTENAME(@ownername) + ' , ' + @ins_procname  + ', [' + convert(nchar(36), @pubid) + ']'

	exec @retcode = master..xp_execresultset @command, @dbname

	if @@ERROR<>0 OR @retcode<>0 
		begin
			return (1)
		end


	exec @retcode = dbo.sp_MS_marksystemobject  @ins_procname 
	if @@ERROR<>0 or @retcode<>0  return (1)
	
	exec ('grant exec on ' + @ins_procname + ' to public')
	set @command = 'sp_MSmakeupdateproc ' + QUOTENAME(@objectname) + ' , ' + QUOTENAME(@ownername) + ' , ' + @upd_procname + ', [' + convert(nchar(36), @pubid) + ']'
	exec @retcode = master..xp_execresultset @command, @dbname
	if @@ERROR<>0 OR @retcode<>0 
		begin
			return (1)
		end
	exec @retcode = dbo.sp_MS_marksystemobject  @upd_procname 
	if @@ERROR<>0 or @retcode<>0 return (1)
	exec ('grant exec on ' + @upd_procname + ' to public')
	if @@ERROR<>0 return (1)
	set @command= 'SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON'
	exec (@command)
	if @@ERROR<>0 return (1)
	set @command = 'sp_MSmakeselectproc ' + QUOTENAME(@objectname) + ' , ' + QUOTENAME(@ownername)+ ' , ' + @sel_procname + ', [' + convert(nchar(36), @pubid) + ']'
	exec @retcode = master..xp_execresultset @command, @dbname
	if @@ERROR<>0 or @retcode<>0
		begin
			return (1)
		end
	exec @retcode = dbo.sp_MS_marksystemobject  @sel_procname 
	if @@ERROR<>0 or @retcode<>0 return (1)
	exec ('grant exec on ' + @sel_procname + ' to public')
	if @@ERROR<>0 return (1)

	if @sync_objid <> 0 
		begin

   	 	select @ownername = user_name(uid), @viewname = name from sysobjects 
                where id = @sync_objid 
    	select @rgcol = QUOTENAME(name) from syscolumns where id = @objid and
                ColumnProperty(id, name, 'isrowguidcol') = 1
		
    	exec @retcode=dbo.sp_MSmakeviewproc @viewname, @ownername, @view_selprocname, @rgcol, @objid
		if @@ERROR<>0 or @retcode<>0
			return (1)
		end
	else
		set @view_selprocname = ''

	--to be consistent with upgrade code by checking snapshot_ready>0
	if @snapshot_ready>0 and @conflict_table is not NULl
		begin
			exec @retcode = dbo.sp_MSguidtostr @artid, @guidstr out
			if @@ERROR <>0 OR @retcode <>0 return (1)

			exec @retcode = dbo.sp_MSguidtostr @pubid, @pubidstr out
			if @@ERROR <>0 OR @retcode <>0 return (1)

			select @conflict_proc = 'sp_cft_' + substring(@guidstr, 1, 16) + substring(@pubidstr, 1, 16)

			exec @retcode=sp_MSuniqueobjectname @conflict_proc , @conflict_proc output
			if @@ERROR <> 0 OR @retcode <> 0 
				return(1)
			set @dbname = db_name()
			set @command = 'sp_MSmakeconflictinsertproc ' + QUOTENAME(@conflict_table) + ' , ' + QUOTENAME(@ownername) + ' , ' + @conflict_proc  + ' , ' + convert(nvarchar,@objid) 

			set @command = @command	+ ', [' + convert(nchar(36), @pubid) + ']'
			exec @retcode = master..xp_execresultset @command, @dbname
			if @@ERROR<>0 OR @retcode<>0 
			begin
				return (1)
			end
			exec @retcode = dbo.sp_MS_marksystemobject  @conflict_proc 
			if @@ERROR<>0 or @retcode<>0  return (1)

			exec ('grant exec on ' + @conflict_proc + ' to public')
			if @@ERROR<>0 return (1)
			update sysmergearticles set ins_conflict_proc = @conflict_proc where artid = @artid and pubid=@pubid
		end
	-- update articles to set the names
	update sysmergearticles set insert_proc = @ins_procname, update_proc = @upd_procname ,
		select_proc = @sel_procname, view_sel_proc = @view_selprocname
		where artid = @artid and pubid = @pubid
	IF @@ERROR<>0 return (1)
	return (0)
go
exec dbo.sp_MS_marksystemobject sp_MSsetartprocs
go
grant exec on dbo.sp_MSsetartprocs to public
go
SET ANSI_NULLS OFF 
GO


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

/* Add the conflict table pointer to sysmergearticles - Used by reconciler */
CREATE PROCEDURE sp_MSsetconflicttable (
	@article			sysname,
	@conflict_table		nvarchar(255),
	@publisher			sysname = NULL,
	@publisher_db		sysname = NULL, 
	@publication		sysname = NULL
	) AS

	declare @artid uniqueidentifier
	declare @pubid uniqueidentifier
	declare @quoted_conflict_table nvarchar(270)
	declare @basetableid	int
	
	--special case'd this out for backward compatibility with 7.0 subscribers.
	if @publisher is NULL and @publisher_db is NULL and @publication is NULL
		return (0)

	select @pubid=pubid	from sysmergepublications 
		where name=@publication and LOWER(publisher)=LOWER(@publisher) and publisher_db=@publisher_db
		
	select @artid = artid, @basetableid=objid FROM sysmergearticles WHERE name = @article and pubid=@pubid	
    if @artid IS NULL
        BEGIN
			RAISERROR (20027, 16, -1, @article)
            RETURN (1)
        END

	/*
	** Check to see if current publication has permission
	*/
	declare @retcode int

	if sessionproperty('replication_agent') = 0
	begin
		exec @retcode=sp_MSreplcheck_connection
			@artid = @artid
		if @retcode<>0 or @@ERROR<>0 return (1)
	end

	select @quoted_conflict_table = quotename(@conflict_table)
	exec @retcode = dbo.sp_MS_marksystemobject @quoted_conflict_table
	
	update sysmergearticles set conflict_table = @conflict_table where artid = @artid and pubid=@pubid
	if @@ERROR <> 0
		return (1)

	declare @rgcol nvarchar(135)
	declare @indname nvarchar(131)
	declare @owner sysname
	declare @quotedname nvarchar(270)
		
	select @rgcol = QUOTENAME(name) from syscolumns where id = @basetableid and
                ColumnProperty(id, name, 'isrowguidcol') = 1
	select @owner=user_name(uid) from sysobjects where name=@conflict_table
	select @indname = 'uc_' + @conflict_table
	if len(@indname) > 128
        begin
            select @indname = substring(@indname,1,92) + convert(nvarchar(36), newid())
        end
	set @indname = QUOTENAME(@indname)
	set @quotedname = QUOTENAME(@owner) + '.' + QUOTENAME(@conflict_table)

	--only create the conflict table index when needed.
	if not exists (select * from sysindexes where id = object_id(@quotedname) and keys is not null)
		begin
			exec ('Create unique clustered index ' + @indname + ' on ' + @quotedname +
        			' (' + @rgcol + ', origin_datasource)' )
			if @@error <> 0
				return (1)
		end

	/* Create the conflict insert proc only when necessary for performance reason */
	if exists (select * from sysmergearticles where artid = @artid and pubid=@pubid and OBJECT_ID(ins_conflict_proc) is null)
		BEGIN
		exec dbo.sp_MSgetconflictinsertproc @pubid=@pubid, @artid = @artid, @output = 0
		IF @@ERROR<> 0 OR @retcode <> 0
			return (1)
		END

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

raiserror('Creating procedure sp_addpullsubscription_agent', 0,1)
go
CREATE PROCEDURE sp_addpullsubscription_agent (
    @publisher sysname,
    @publisher_db sysname,
    @publication sysname,         /* publication name */
    @subscriber sysname = NULL,
    @subscriber_db sysname = NULL,
    @subscriber_security_mode       int = NULL,                     /* 0 standard; 1 integrated */
    @subscriber_login               sysname = NULL,
    @subscriber_password            sysname = NULL,
    @distributor sysname = @publisher,
    @distribution_db sysname = NULL,
    @distributor_security_mode int = 0,
    @distributor_login sysname = 'sa',
    @distributor_password sysname = NULL,
    @optional_command_line nvarchar(4000) = '',
    @frequency_type  int = 2 ,  /* 2== OnDemand */
    @frequency_interval int = 1, 
    @frequency_relative_interval int = 1, 
    @frequency_recurrence_factor int = 1, 
    @frequency_subday int = 1, 
    @frequency_subday_interval int = 1 ,
    @active_start_time_of_day int = 0, 
    @active_end_time_of_day int = 0,         
    @active_start_date int = 0, 
    @active_end_date int =0,
    @distribution_jobid binary(16) = NULL OUTPUT,
    @encrypted_distributor_password bit = 0,
    @enabled_for_syncmgr nvarchar(5) = 'false', /* Enabled for SYNCMGR: true or false */
    @ftp_address sysname = NULL,
    @ftp_port int = NULL,
    @ftp_login sysname = NULL,
    @ftp_password sysname = NULL,
    @alt_snapshot_folder  nvarchar(255) = NULL,
    @working_directory    nvarchar(255) = NULL,
    @use_ftp              nvarchar(5) = 'false',
    @publication_type     tinyint = 0,-- 0 - Transactional, 1 - Snapshot, 2 - Merge
    @dts_package_name sysname  = NULL,  /* value will be sent and validated at distributor */                                  
    @dts_package_password  sysname = NULL,
    @dts_package_location nvarchar(12) = N'subscriber',
    @reserved nvarchar(100) = N'', -- Not default to null because null problems in conditional expressions.
    @offloadagent          nvarchar(5) = 'false',
    @offloadserver         sysname = NULL,
    @job_name              sysname = NULL
    ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    DECLARE @command nvarchar(4000)
    DECLARE @retcode int
    DECLARE @subscription_type_id int   /* 1 = pull, 2 = anonymous */
    DECLARE @independent_agent_id bit
    DECLARE @distribution_agent nvarchar(100) 
    DECLARE @category_name sysname
    DECLARE @platform_nt binary
    DECLARE @subscriber_enc_password nvarchar(524)
    DECLARE @distributor_enc_password   nvarchar(524)
    DECLARE @use_ftp_bit bit
    DECLARE @offload_agent_bit bit
    
    select @platform_nt = 0x1

    /*
    ** Security Check
    */

    EXEC @retcode = dbo.sp_MSreplcheck_subscribe
    IF @@ERROR <> 0 or @retcode <> 0
        RETURN(1)

    /*
    ** Initializations.
    */

    -- Set null @optional_command_line to empty string to avoid string concat problem
    if @optional_command_line is null
        set @optional_command_line = ''
    else
        set @optional_command_line = N' ' + LTRIM( RTRIM(@optional_command_line) ) + N' '

    IF @distributor_password = N''
        select @distributor_password = NULL

    IF @ftp_password = N''
        select @ftp_password = NULL

    IF @dts_package_password = N''
        select @dts_package_password = NULL

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

    EXECUTE @retcode = dbo.sp_validname @publisher

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

    /*
    ** Parameter Check: @publisher_db
    */

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

    EXECUTE @retcode = dbo.sp_validname @publisher_db

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

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

    EXECUTE @retcode = dbo.sp_validname @publication

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

    /*
    ** Parameter Check: @subscriber and @subscriber_db
    */

    if @subscriber IS NULL or rtrim(@subscriber) = ''
        SELECT @subscriber = @@SERVERNAME

    if @subscriber_db IS NULL or rtrim(@subscriber_db) = ''
        SELECT @subscriber_db = DB_NAME()
    
    EXECUTE @retcode = dbo.sp_validname @subscriber
    IF @@ERROR <> 0 OR @retcode <> 0
       RETURN (1)
    
    EXECUTE @retcode = dbo.sp_validname @subscriber_db
    IF @@ERROR <> 0 OR @retcode <> 0
       RETURN (1)

    /* 
    ** Check to see if MSreplictaion_subscriptions table exists.
    ** If so, copy it into the temp table
    */
    IF  NOT EXISTS (SELECT * FROM sysobjects WHERE 
        type = 'U' AND
        name = 'MSreplication_subscriptions')
    BEGIN
        RAISERROR (20017, 16, -1)
        RETURN (1)
    END 
    
    /*
    ** Check to make sure that the subscription does exist
    */
    IF NOT EXISTS (SELECT * FROM  MSreplication_subscriptions
                WHERE UPPER(publisher) = UPPER(@publisher) AND
                      publisher_db  = @publisher_db AND
                      publication = @publication)
    BEGIN
        RAISERROR (20017, 16, -1)
        RETURN (1)
    END
    
    declare @update_mode_id int
    SELECT  @distribution_agent = NULL
    SELECT  @independent_agent_id = independent_agent, 
            @subscription_type_id = subscription_type,
            @distribution_agent = distribution_agent,
            @update_mode_id = update_mode
        FROM  MSreplication_subscriptions
        WHERE UPPER(publisher) = UPPER(@publisher) AND
              publisher_db  = @publisher_db AND
              publication = @publication
    /* Distribution agent for push subscriptions is at distributor side */
    IF @subscription_type_id = 0
    BEGIN
        RAISERROR (21001, 16, -1)
        RETURN (1)
    END

    IF @distribution_agent IS NOT NULL
    BEGIN
        RAISERROR (21002, 11, -1, @distribution_agent)
        RETURN (1)
    END

    -- Parameter check: @subscriber_security_mode
    if @subscriber_security_mode is null
    begin
        if ( platform() & @platform_nt ) = @platform_nt
            select @subscriber_security_mode = 1
        else
            select @subscriber_security_mode = 0
    end 

    if ( ( platform() & @platform_nt ) <> @platform_nt and @subscriber_security_mode = 1 )
    begin
        RAISERROR(21038, 16, -1)
        RETURN (1)
    end

    if (@subscription_type_id <> 0)
    begin
        if (@subscriber_security_mode = 0) and (@subscriber_login IS NULL or rtrim(@subscriber_login) = '')
        begin
            raiserror(21344, 16, -1, '@subscriber_login')
            return (1)
        end
    end
    
    if (@distributor_security_mode = 0) and (@distributor_login IS NULL or rtrim(@distributor_login) = '')
    begin
        raiserror(3217, 16, -1, '@distributor_login')
        return (1)
    end

    IF NOT EXISTS (select * from sysobjects where name = 'MSsubscription_properties' and type = 'U')
    begin
        exec @retcode = sp_MScreate_sub_tables @property_table = 1
        if @retcode <> 0 or @@error <> 0
        return (1)
    end

    /* 
    ** Parameter check: @alt_snapshot_folder 
    ** @alt_snapshot_folder and @use_ftp are mutually exclusive    
    */

    IF @alt_snapshot_folder <> N'' AND @alt_snapshot_folder IS NOT NULL
       AND LOWER(@use_ftp collate SQL_Latin1_General_CP1_CS_AS) = N'true'
    BEGIN
        RAISERROR(21146, 16, -1)
        RETURN (1)
    END

    /* 
    ** Parameter check: @use_ftp
    ** Must be 'true' or 'false'
    */
    IF LOWER(@use_ftp collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
    BEGIN
        RAISERROR (14148, 16, -1, '@use_ftp')
        RETURN (1)
    END
    
    IF LOWER(@use_ftp collate SQL_Latin1_General_CP1_CS_AS) = 'true'
    BEGIN
        SELECT @use_ftp_bit = 1
    END
    ELSE
    BEGIN
        SELECT @use_ftp_bit = 0
    END


    /*
    ** Parameter check: @publication_type
    ** Must be 0 - Transactional or 1 - Snapshot
    */
    IF @publication_type NOT IN (0, 1)
    BEGIN
        RAISERROR (20033, 16, -1)
        RETURN (1)
    END

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

    declare @dts_package_location_id int

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

    -- Have to be a push, non updatable  subscription to set DTS package name
    if @dts_package_name is not null
    begin
        if  @update_mode_id != 0
        begin
            RAISERROR(21180, 16, -1)    
            RETURN (1)
        end
    end
    
    -- Copy the passwords to new value before attempting to encrypt
    set @distributor_enc_password = @distributor_password
    IF (@encrypted_distributor_password = 0)
        -- Encrypt the password
        BEGIN
            EXEC @retcode = master.dbo.xp_repl_encrypt @distributor_enc_password OUTPUT
            IF @@error <> 0 OR @retcode <> 0
                return 1
        END
	
    declare @dts_package_enc_password nvarchar(524)
    set @dts_package_enc_password = @dts_package_password

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

    /*
    ** Parameter Check: @offloadserver
    ** 1. If @offloadagent = 'true' then @offloadserver cannot be null.
    ** 2. Similar to the push case, we don't allow "remote" activation
    **    of agent on the local machine.
    */
    SELECT @offloadagent = LOWER(@offloadagent collate SQL_Latin1_General_CP1_CS_AS)
    IF @offloadagent NOT IN ('true', 'false')
    BEGIN
        RAISERROR (14148, 16, -1, '@offloadagent')
        RETURN (1)
    END

    IF @offloadagent = 'true'
    BEGIN
        SELECT @offload_agent_bit = 1
    END
    ELSE
    BEGIN
        SELECT @offload_agent_bit = 0
    END

    IF @offload_agent_bit = 1 AND (@offloadserver is NULL or
                               @offloadserver = N'')
    BEGIN
        RAISERROR(21215, 16, -1)
        RETURN (1)
    END
    
    IF UPPER(@offloadserver) = UPPER(@@SERVERNAME) AND
       @offload_agent_bit = 1
    BEGIN
        RAISERROR(21227, 16, -1)
        RETURN (1)
    END

    EXEC @retcode = sp_MSreplcheckoffloadserver @offloadserver
    IF @retcode <> 0 OR @@ERROR <> 0
        RETURN (1)

    /*
    ** Construct unique name
    */
    if @subscriber is NULL select @subscriber = ''
    if @subscriber_db is NULL select @subscriber_db = ''
    
    declare @job_existing bit
    if @job_name is null
    begin
        select @job_existing = 0
        SELECT @job_name = CONVERT(nvarchar(18),@publisher ) + '-' + CONVERT(nvarchar(18),@publisher_db) + '-' + 
                        CONVERT(nvarchar(18),@publication) + '-' + CONVERT(nvarchar(18),@subscriber) + '-' +
                        CONVERT(nvarchar(18),@subscriber_db) + '-' + CONVERT(nvarchar(36),newid())
    end
    else
        select @job_existing = 1

    -- Get property values.
    if @reserved = 'no_change_to_properties'
    begin
        -- Get the distributor value. It will be used in agent command line.
        select @distributor = distributor, 
            @enabled_for_syncmgr = case enabled_for_syncmgr
                when 0 then 'false'
                when 1 then 'true'
                end
        from MSsubscription_properties where
            UPPER(publisher) = UPPER(@publisher)
            and publisher_db =  @publisher_db
            and publication = @publication              
    end

    BEGIN TRAN

    /*
    ** If the publication is independent agent type or it is the first
    ** subscription on the non independent agent publications.
    */

    IF @independent_agent_id = 1 OR 
        NOT EXISTS (SELECT * FROM MSreplication_subscriptions WHERE
                            UPPER(@publisher) = UPPER(publisher) and
                            @publisher_db = publisher_db and
                            agent_id IS NOT NULL and
                            independent_agent = 0)
    BEGIN
        if @job_existing = 0
        begin
            /* Construct agent command */
            SELECT @command = '-Publisher ' + @publisher + ' '
            SELECT @command = @command + '-PublisherDB ' + QUOTENAME(@publisher_db) + ' '
            IF @independent_agent_id = 1
                SELECT @command = @command + '-Publication ' + QUOTENAME(@publication) + ' '

            SELECT @command = @command + '-Distributor ' + QUOTENAME(@distributor)  + ' '

            /*
            Use -Xdatabase to save command line space
            We can not use -Xserver for distribution because SQLExec will validate the server
            to be in sysservers.

            SELECT @command = @command + '-DistributionDB ' + QUOTENAME(@distribution_db)  + ' '
            */
            
            SELECT @command = @command + '-SubscriptionType ' + convert(nvarchar(10),@subscription_type_id)  + ' '
            SELECT @command = @command + '-Subscriber ' + QUOTENAME(@subscriber)  + ' '
        
            select @command = @command + '-SubscriberSecurityMode ' + 
                convert(nvarchar(10),@subscriber_security_mode) + ' '
            if @subscriber_login is not NULL
                select @command = @command + '-SubscriberLogin ' + quotename(@subscriber_login) + ' '
            if @subscriber_password is not NULL
            begin
                set @subscriber_enc_password = @subscriber_password
                exec @retcode = master.dbo.xp_repl_encrypt @subscriber_enc_password OUTPUT
                select @command = @command + '-SubscriberEncryptedPassword ' + quotename(@subscriber_enc_password) + ' '
            end

            SELECT @command = @command + '-SubscriberDB ' + QUOTENAME(@subscriber_db) + ' '

                    
            if @dts_package_name is not null
              select @command = @command + '-UseDTS '

            if @offload_agent_bit = 1 
                select @command = @command + N'-Offload ' + @offloadserver + N' '

            /* 
            ** make sure the command line is not truncated
            */
            /* Use datalength because len doesn't count the last space in @command */
            IF (datalength(@command) + datalength(@optional_command_line)) > 8000
            BEGIN
                RAISERROR(20018, 16, -1)
                RETURN(1)
            END

            SELECT @command = @command + @optional_command_line

            -- Get Distribution category name (assumes category_id = 10)
            select @category_name = name FROM msdb.dbo.syscategories where category_id = 10

            EXEC @retcode = dbo.sp_MSadd_repl_job
                    @name = @job_name,
                    @subsystem = 'Distribution',
                    @server = @@SERVERNAME,
                    @databasename = @distribution_db,
                    @enabled = 1,
                    @freqtype = @frequency_type,
                    @freqinterval = @frequency_interval,
                    @freqsubtype = @frequency_subday,
                    @freqsubinterval = @frequency_subday_interval,
                    @freqrelativeinterval = @frequency_relative_interval,
                    @freqrecurrencefactor = @frequency_recurrence_factor,
                    @activestartdate = @active_start_date,
                    @activeenddate = @active_end_date,
                    @activestarttimeofday = @active_start_time_of_day,
                    @activeendtimeofday = @active_end_time_of_day,
                    @command = @command,
                    @category_name = @category_name,
                    @retryattempts = 10,
                    @retrydelay = 1,
                    @job_id = @distribution_jobid OUTPUT

            IF @@ERROR <> 0 or @retcode <> 0
            BEGIN
                IF @@TRANCOUNT = 1
                    ROLLBACK TRAN
                ELSE
                    COMMIT TRAN   
                RETURN(1)
            END
        end
        else
        begin
            select @distribution_jobid = job_id from msdb..sysjobs_view where 
                name = @job_name collate database_default and
                UPPER(originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))
            if @distribution_jobid IS NULL
            begin
                -- Message from msdb.dbo.sp_verify_job_identifiers
                RAISERROR(14262, -1, -1, 'Job', @job_name)          
                IF @@TRANCOUNT = 1
                    ROLLBACK TRAN
                ELSE
                    COMMIT TRAN   
                RETURN(1)
            end
        end
    END

    if @reserved <> 'no_change_to_properties' and (@subscription_type_id = 1) OR (@subscription_type_id = 2)
    BEGIN
        IF NOT EXISTS (select * from MSsubscription_properties 
            where UPPER(publisher) = UPPER(@publisher)
              and publisher_db =  @publisher_db
              and publication = @publication) 
        BEGIN
            -- Publication type:
            -- 0  transactional
            -- 1  snapshot
            -- 2  merge (not allowed)

            INSERT INTO MSsubscription_properties 
            (publisher, publisher_db, publication, publication_type, 
             publisher_login,publisher_password, publisher_security_mode, 
             distributor, distributor_login, distributor_password, 
             distributor_security_mode, ftp_address, ftp_port, ftp_login, 
             ftp_password, alt_snapshot_folder, working_directory, use_ftp,
             dts_package_name, dts_package_password, dts_package_location, 
             offload_agent, offload_server, dynamic_snapshot_location)
            values (@publisher, @publisher_db, @publication, @publication_type, NULL, NULL, 1, 
                @distributor, @distributor_login, @distributor_enc_password, 
                @distributor_security_mode, null, null, null,
                null, @alt_snapshot_folder, @working_directory, @use_ftp_bit,
                @dts_package_name, @dts_package_enc_password, 
                @dts_package_location_id, @offload_agent_bit, @offloadserver, null)

            IF @@ERROR <> 0 
            BEGIN
                IF @@TRANCOUNT = 1
                    ROLLBACK TRAN
                ELSE
                    COMMIT TRAN           
                RETURN(1)
            END
        END
        ELSE
        BEGIN
            update MSsubscription_properties set
                distributor = @distributor,
                distributor_login = @distributor_login,
                distributor_password = @distributor_enc_password,
                distributor_security_mode = @distributor_security_mode,
                dts_package_name = @dts_package_name,
                dts_package_password = @dts_package_enc_password,
                dts_package_location = @dts_package_location_id
                where UPPER(publisher) = UPPER(@publisher)
                    and publisher_db =  @publisher_db
                    and publication = @publication
        END


        -- For dependent subscriptions we need to fix up all the
        -- shared properties  
        IF @independent_agent_id = 0 
        BEGIN

            EXEC @retcode = sp_MSfixupsharedagentproperties
                   @publisher = @publisher,
                   @publisher_db = @publisher_db,
                   @publication = @publication,
                   @distributor = @distributor,
                   @distributor_security_mode = @distributor_security_mode,
                   @distributor_login = @distributor_login,
                   @distributor_password = @distributor_enc_password,
                   @ftp_address = @ftp_address,
                   @ftp_port = @ftp_port,
                   @ftp_login = @ftp_login,
                   @ftp_password = @ftp_password,
                   @alt_snapshot_folder = @alt_snapshot_folder,
                   @working_directory = @working_directory,
                   @use_ftp = @use_ftp_bit,
                   @offload_agent = @offload_agent_bit,
                   @offload_server = @offloadserver
                    
            IF @retcode <> 0 OR @@ERROR <> 0
            BEGIN
            IF @@TRANCOUNT = 1
                ROLLBACK TRAN
            ELSE
                COMMIT TRAN           
            RETURN(1)
            END
        END

        IF @@ERROR <> 0 
        BEGIN
            IF @@TRANCOUNT = 1
                ROLLBACK TRAN
            ELSE
                COMMIT TRAN           
            RETURN(1)
        END
    END


    /* If we do not have independent agents , i.e. independent_agent=0, but there is
    already a row for that publisher and that publisher database with a NOT null 
    distribution_agent_id, then set the @distribution_jobid to that id.  Note that if
    there are no rows returned, the value of the variable does not change, which is what we want.
    There should never be more than one row ever returned for this query - but will use TOP 1
    to insist that is the case.
    */
    
    IF @independent_agent_id = 0
    BEGIN
        SELECT DISTINCT @distribution_jobid=agent_id, @job_name = distribution_agent 
          FROM MSreplication_subscriptions
         WHERE UPPER(publisher) = UPPER(@publisher) 
           AND publisher_db =  @publisher_db
           AND agent_id IS NOT NULL AND independent_agent=0
    
    END

    UPDATE MSreplication_subscriptions 
       SET distribution_agent = @job_name,
           agent_id = @distribution_jobid
     WHERE UPPER(publisher) = UPPER(@publisher) 
       AND publisher_db =  @publisher_db 
       AND publication =  @publication 
       AND (subscription_type = 1 /* pull*/ OR subscription_type = 2) /*anonymous*/

    IF @@ERROR <> 0 
    BEGIN
        IF @@TRANCOUNT = 1
            ROLLBACK TRAN
        ELSE
            COMMIT TRAN           
        RETURN(1)
    END 

    /* Conditional support for MobileSync */
    if LOWER(@enabled_for_syncmgr collate SQL_Latin1_General_CP1_CS_AS) = 'true'
    BEGIN
        /* Call sp_MSregistersubscription so that the subscription can be synchronized via MobileSync etc. */
        declare @subscription_id uniqueidentifier
        declare @failover_mode_id int
        set @subscription_id = convert(uniqueidentifier, @distribution_jobid)

        if @update_mode_id in (3,5) 
            select @failover_mode_id = 1
        else if @update_mode_id in (2,4)
            select @failover_mode_id = 2
        else
            select @failover_mode_id = 0
            
        exec @retcode = dbo.sp_MSregistersubscription @replication_type = 1,
                                    @publisher = @publisher,
                                    @publisher_db = @publisher_db,
                                    @publication = @publication,
                                    @subscriber = @subscriber,
                                    @subscriber_db = @subscriber_db,
                                    @subscriber_security_mode = @subscriber_security_mode,
                                    @subscriber_login = @subscriber_login,
                                    @subscriber_password = @subscriber_password,
                                    @distributor = @distributor,
                                    @subscription_id = @subscription_id,
                                    @independent_agent = @independent_agent_id,
                                    @subscription_type = @subscription_type_id,
                                    @failover_mode = @failover_mode_id
        IF @@ERROR <> 0 or @retcode <> 0
        BEGIN
            IF @@TRANCOUNT = 1
                ROLLBACK TRAN
            ELSE
                COMMIT TRAN           
            RETURN(1)
        END
    END

    COMMIT TRAN
    RETURN(0)
GO

grant execute on dbo.sp_addpullsubscription_agent to public
go

raiserror('Creating procedure sp_MSenumallsubscriptions', 0,1)
go

CREATE PROCEDURE sp_MSenumallsubscriptions(
@subscription_type	nvarchar(5) = 'push', 
@subscriber_db		sysname='%'
)AS

	set nocount on
	declare @current_db		sysname
	declare @retcode		int
	declare @proc			nvarchar(200)
	declare @db_status		int

	create table #MSenumallsubscriptions (
						publisher			sysname collate database_default not null,
						publisher_db		sysname collate database_default not null,
						publication			sysname collate database_default null,
						replication_type	int not NULL,
						subscription_type	int not NULL,
						last_updated		datetime null,
						subscriber_db		sysname collate database_default not null,
						update_mode			smallint null,
						last_sync_status	int null,
						last_sync_summary	sysname collate database_default null,
						last_sync_time		datetime null
						)

	declare #cur_db cursor local FAST_FORWARD FOR select DISTINCT name, status
		FROM master.dbo.sysdatabases where ((@subscriber_db = N'%' collate database_default) or (name = @subscriber_db collate database_default)) and
			has_dbaccess(name) = 1
	open #cur_db
	fetch #cur_db into @current_db, @db_status
	while (@@fetch_status <> -1)
	begin
		/*
		 * we only return subscriptions in db which is not in loading (0x20), suspect(0x100),
		 * offline(0x200), in recovering(0x80), shutdown(0x40000), not recovered(0x40)
		 */
		if (@db_status & 0x403e0) = 0
			begin
				select @proc = QUOTENAME(@current_db) + '.' + 'dbo.sp_MSenumsubscriptions ' 
				insert into #MSenumallsubscriptions exec @retcode = @proc  @subscription_type
				if @@ERROR<>0 or @retcode<>0
					begin
						close #cur_db
						deallocate #cur_db
						return (1)
					end
			end
		fetch next from #cur_db into @current_db, @db_status
	end
    select  distinct 'publisher'				= publisher,
            'publishing database'	= publisher_db,
            'publication'			= publication,
            'replication type'		= replication_type,
            'subscription type'     = subscription_type,
			'last updating time'	= convert(nvarchar(12), last_updated, 112) + 
                                          substring(convert(nvarchar(24), last_updated, 121), 11,13),
			'subscribing database'  = subscriber_db,
			'update_mode'           = update_mode,
			'last sync status'		= last_sync_status,
			'last sync summary'		= last_sync_summary,
			'last sync time'		= convert(nvarchar(12), last_sync_time, 112) + 
				substring(convert(nvarchar(24), last_sync_time, 121), 11,13)
	from #MSenumallsubscriptions
	--drop table #MSenumallsubscriptions
	close #cur_db
	deallocate #cur_db
	return (0)
Go
grant execute on dbo.sp_MSenumallsubscriptions to public
go 
exec dbo.sp_MS_marksystemobject sp_MSenumallsubscriptions
GO

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

create proc sp_MSinsertbeforeimageclause @pubid uniqueidentifier, @objid int, @tablenickstr nvarchar(12)  as
	set nocount on
	declare @cmdpiece nvarchar(4000)
	declare @before_objid int
	declare @sync_objid int
	declare @before_name sysname
	declare @collist nvarchar(4000)
	declare @vallist nvarchar(4000)
	declare @colname sysname
	declare @colordinal smallint
	declare @argname sysname

	-- Do we have a before table?
	select @before_objid = max(before_image_objid) from  sysmergearticles where objid = @objid and
			before_image_objid is not null
	select @before_name = OBJECT_NAME(@before_objid)

	select @sync_objid = sync_objid	from sysmergearticles where objid=@objid and pubid=@pubid

	if @before_name is null
		begin
			return 0
		end

	set @collist = ''
	-- Loop over columns to make the column list for the insert / select command
	declare col_cursor CURSOR LOCAL FAST_FORWARD for select name from syscolumns
	where id = @before_objid and name <> 'generation' and name <> 'system_delete' order by colid
	FOR READ ONLY

	open col_cursor
	set @vallist = ''
	fetch next from col_cursor into @colname
	while (@@fetch_status <> -1)
		begin
		--this column is not in vertical partitioning
		if not exists (select * from syscolumns where name=@colname and id=@sync_objid)
		begin
			fetch next from col_cursor into @colname
			continue
		end
		set @collist = @collist + QUOTENAME(@colname) + ', '
		exec sp_MSgetcolordinalfromcolname @objid, @sync_objid, @colname, @colordinal out
		select @argname = '@p' + rtrim(convert(nchar, @colordinal))
		set @vallist = @vallist + @argname + ', '
		fetch next from col_cursor into @colname
		end
	close col_cursor
	deallocate col_cursor

	-- Our list has all of the columns except generation since that gets set to a local variable
	-- Make the insert command
	set @cmdpiece = ' declare @gen_cur int select @gen_cur = max(gen_cur) from sysmergearticles where nickname = ' + @tablenickstr
	insert into #tempcmd (phase, cmdtext) values (8, @cmdpiece)
	--select @cmdpiece

	set @cmdpiece = ' insert into ' + QUOTENAME(@before_name) + ' ( ' + @collist +	
					' generation, system_delete) values (' + @vallist + ' @gen_cur, 1 )'
	insert into #tempcmd (phase, cmdtext) values (8, @cmdpiece)
	--select @cmdpiece

	return 0
	
go

exec dbo.sp_MS_marksystemobject sp_MSinsertbeforeimageclause  
go

grant exec on dbo.sp_MSinsertbeforeimageclause to public
go
create procedure sp_MShelpalterbeforetable
	@objid int,
	@biname sysname
AS
	declare @command nvarchar(4000)
	declare @retcode int
	declare @include int
	declare @tablenick int
	declare @colpat nvarchar(130)
	declare @colname nvarchar(130)
	declare @typename sysname
	declare @colid smallint
	declare @colidstr nvarchar(3)
	declare @status tinyint
	declare @len smallint
	declare @prec smallint
	declare @scale int
	declare @isnullable tinyint
	declare @bi_objid int
	set nocount on
	declare @cMaxIndexLength int

	set @cMaxIndexLength= 900  -- max index column size in SQL 2000

	select @tablenick = max(nickname) from sysmergearticles where objid = @objid
	if @tablenick is null 
		return (1)

	select @bi_objid = OBJECT_ID(@biname)		
	
	-- Loop over the columns and see which ones we include
	declare col_cursor CURSOR LOCAL FAST_FORWARD for select name, status, type_name(xtype), length,
		 prec, scale, isnullable, colid from syscolumns
	where id = @objid and iscomputed <> 1 and type_name(xtype) <> 'timestamp' order by colid
	FOR READ ONLY
	
	open col_cursor
	fetch next from col_cursor into @colname, @status, @typename, @len, @prec, @scale, @isnullable, @colid
	while (@@fetch_status <> -1)
		begin
		set @include = 0
		set @colpat = '%' + @colname + '%'

		if not exists (select * from syscolumns where id = @bi_objid and QUOTENAME(name) = QUOTENAME('system_delete'))
			begin
				set @command = 'alter table ' + @biname + ' ADD system_delete bit default(0) '
				execute ( @command )
				if @@ERROR<>0 
					goto errlabel

				/* grant select to system_delete column */
			   	exec ('grant select (system_delete) on ' + @biname + ' to public')
			   	if @@ERROR<>0 
					goto errlabel

					
			end

		exec ('grant select (generation), update(generation), delete on ' + @biname + ' to public')
			   	if @@ERROR<>0 
					goto errlabel

		-- Is this column already in the before image table?
		-- or the column is not in the vertical partitioning?
		if exists (select * from syscolumns where id = @bi_objid and name = @colname) OR
			exists (select * from sysmergearticles where objid=@objid and not exists (
				select * from syscolumns where id = sync_objid and name = @colname))
			begin
			goto fetchnext
			end

		-- does updating this column change membership in a partial replica? 
		if exists (select * from sysmergearticles 
			where objid = @objid and subset_filterclause like @colpat)
			set @include = 1
		else if exists (select * from sysmergesubsetfilters
			where art_nickname = @tablenick and join_filterclause like @colpat)
			set @include = 1
		else if exists (select * from sysmergesubsetfilters
			where join_nickname = @tablenick and join_filterclause like @colpat)
			set @include = 1

		-- If we want this column, map its type and insert a row to temp table
		if @include <> 1
			begin
			goto fetchnext
			end
		if @typename='nvarchar' or @typename='nchar' -- a unit of nchar takes 2 bytes
			set @len = @len/2
		exec @retcode = dbo.sp_MSmaptype @typename out, @len, @prec, @scale
		if @@ERROR<>0 or @retcode<>0 
			goto errlabel
		if @typename not in ('text', 'ntext','image')
		begin
			set @colname = QUOTENAME(@colname)
	
			-- Always make columns nullable when we add them because we might have
			-- existing rows in the before image table.

			set @command = 'alter table ' + @biname + ' ADD ' + @colname + ' ' + @typename + ' NULL '
		
			execute ( @command )
			if @@ERROR<>0 goto errlabel

			-- Insert a create index command if column is not too long
			if (@len <= @cMaxIndexLength)
			begin
				set @colidstr =convert(nvarchar(3), @colid)
	 			set @command = 'create index ' + @biname + '_' + @colidstr + ' on ' + @biname + ' (' + @colname + ')'
				execute ( @command )
				if @@ERROR<>0 goto errlabel
			end
		end
							
fetchnext:
		/* Repeat the loop with next column */
		fetch next from col_cursor into @colname, @status, @typename, @len, @prec, @scale, @isnullable, @colid
		end
	close col_cursor
	deallocate col_cursor	
	return 0
errlabel:
	close col_cursor
	deallocate col_cursor	
	return 1

go

exec dbo.sp_MS_marksystemobject sp_MShelpalterbeforetable 
go

SET ANSI_NULLS OFF 
GO
raiserror('Creating procedure sp_MSmakesystableviews', 0,1)
GO

-- Used by snapshot
create procedure sp_MSmakesystableviews (
	@publication sysname,
    @dynamic_snapshot_views_table_name sysname = null
    )
AS
	declare @guidstr 		nvarchar(40)
	declare @pubid  		uniqueidentifier
	declare @contentsview 	sysname 
	declare @tombstoneview 	sysname
	declare @genhistoryview	sysname
	declare @filtersview	sysname
	declare @piece			nvarchar(4000)
	declare @retcode smallint
	declare @dbname			sysname
	declare @art_count		int
	declare @skip_ctsv 		int
	declare	@command		nvarchar(500)
    declare @dynamic_filters bit
    declare @view_creation_command nvarchar(4000)
    declare @newid          uniqueidentifier

	/*
	** Check to see if current publication has permission
	*/
	exec @retcode=sp_MSreplcheck_publish
	if @retcode<>0 or @@ERROR<>0 return (1)
	set @skip_ctsv = 0
	select @pubid = pubid, @dynamic_filters = dynamic_filters from sysmergepublications where name = @publication and UPPER(publisher)=UPPER(@@SERVERNAME) and publisher_db=db_name()
	if @pubid is null
        BEGIN
			RAISERROR (20026, 16, -1, @publication)
            RETURN (1)
        END
	select @art_count=count(*) from sysmergearticles where pubid=@pubid
	if @art_count > 253
		set @skip_ctsv=1
    select @newid = newid()
	create table #temp_table_for_systable_view(contentsview sysname, tombstoneview sysname NULL, genhistoryview sysname NULL, filtersview sysname NULL)
	exec @retcode = dbo.sp_MSguidtostr @newid, @guidstr out
	if @@ERROR<>0 OR @retcode<>0 return (1)
	select @contentsview = 'cont' + @guidstr
	select @tombstoneview = 'ts' + @guidstr
	select @genhistoryview = 'gh' + @guidstr
	select @filtersview = 'filt' + @guidstr

	set @guidstr = '''' + convert(nchar(36), @pubid) + ''''
	
	exec @retcode = dbo.sp_MSuniqueobjectname @tombstoneview, @tombstoneview out
	if @@ERROR<>0 OR @retcode<>0 return (1)
	exec @retcode = dbo.sp_MSuniqueobjectname @contentsview, @contentsview out
	if @@ERROR<>0 OR @retcode<>0 return (1)
	exec @retcode = dbo.sp_MSuniqueobjectname @genhistoryview, @genhistoryview out
	if @@ERROR<>0 OR @retcode<>0 return (1)
	exec @retcode = dbo.sp_MSuniqueobjectname @filtersview, @filtersview out
	if @@ERROR<>0 OR @retcode<>0 return (1)
	
	insert #temp_table_for_systable_view values(@contentsview,@tombstoneview,@genhistoryview,@filtersview)

	if @skip_ctsv = 0
	begin
		/* generate view for MSmerge_contents qualified by the pubid */
	    /* For dynamically filtered publication, security check is performed in
	       the sync view of the base table */
		set @command = 'sp_MSmakectsview ' + QUOTENAME(@publication) + ' , ' + @contentsview + ' , ' + COALESCE(QUOTENAME(@dynamic_snapshot_views_table_name) collate database_default, N'null' collate database_default) 
		set @dbname = db_name()

		exec @retcode = master..xp_execresultset @command, @dbname
		if @@ERROR<>0 OR @retcode <>0 
			return (1)
	end
	else
	begin
		exec('create view ' + @contentsview + ' as select * from MSmerge_contents where 1 = 2')
		if @@ERROR<>0
			return (1)
	end
	/* 
	** generate the view for dbo.MSmerge_tombstone. In SP2 and Shiloh, the change was made to make the view 
	** return 0 rows since it is unnecessary and expensive to propagate the tombstones.
	** In order to leave all the other moving parts unchanged, we decided to let the view 
	** return 0 rows.
	*/
    select @view_creation_command = 'create view ' + @tombstoneview + ' as select * from dbo.MSmerge_tombstone where 1= 2 and
			tablenick in (select nickname from sysmergearticles where pubid = ' +
			@guidstr + ')'

    if @dynamic_filters = 1
    begin
        select @view_creation_command = @view_creation_command + ' and ((is_srvrolemember(''sysadmin'') = 1) or (is_member(''db_owner'') = 1) or (sessionproperty(''replication_agent'') = 1))'
    end

	exec (@view_creation_command)
	if @@ERROR <>0 
		begin
			return (1)
		end

	select @view_creation_command = 'create view ' + @genhistoryview + '(guidsrc, guidlocal, pubid, generation,
			art_nick, nicknames, coldate) as select DISTINCT guidsrc, guidlocal, CONVERT(uniqueidentifier, ' 
			+ @guidstr + '), generation, art_nick, nicknames, coldate  from dbo.MSmerge_genhistory
			where guidlocal <> ''00000000-0000-0000-0000-000000000000'' and (art_nick = 0 or art_nick is NULL or
					art_nick in (select nickname from sysmergearticles where pubid = ' +
			@guidstr + ')) '
    if @dynamic_filters = 1
    begin
        select @view_creation_command = @view_creation_command + ' and ((is_srvrolemember(''sysadmin'') = 1) or (is_member(''db_owner'') = 1) or (sessionproperty(''replication_agent'') = 1))'
    end

	exec (@view_creation_command)
	if @@ERROR <>0
		begin
			return (1)
		end

    select @view_creation_command = 'create view ' + @filtersview + ' as select * from sysmergesubsetfilters where pubid = ' +
			@guidstr

    if @dynamic_filters = 1
    begin
        select @view_creation_command = @view_creation_command + ' and ((is_srvrolemember(''sysadmin'') = 1) or (is_member(''db_owner'') = 1) or (sessionproperty(''replication_agent'') = 1))'
    end

	exec (@view_creation_command)
	if @@ERROR <>0
		begin
			return (1)
		end

    if @dynamic_filters = 1
    begin
        exec ('grant select on ' + @contentsview + ' to public')
		if @@error<>0 return(1)
        exec ('grant select on ' + @tombstoneview + ' to public')
		if @@error<>0 return(1)
        exec ('grant select on ' + @genhistoryview + ' to public')
		if @@error<>0 return(1)
        exec ('grant select on ' + @filtersview + ' to public')            
		if @@error<>0 return(1)
    end

	set nocount on
	/* we only generate per-article contents view for static publications */
	if @dynamic_filters=0
		begin
			exec @retcode = sp_MSgettablecontents @pubid
			if @@ERROR<>0 OR @retcode <>0 return (1)
 		end
	exec('select * from #temp_table_for_systable_view ')
	drop table #temp_table_for_systable_view

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

raiserror('Creating procedure sp_MSenumsubscriptions', 0,1)
go

CREATE PROCEDURE sp_MSenumsubscriptions(
@subscription_type	nvarchar(5) = 'push',
@publisher		sysname = '%',
@publisher_db 	sysname = '%'
)AS

	set nocount on
	declare @dbname		sysname
	declare @category 	int
	declare @proc			nvarchar(200)
	declare @retcode	int
	declare @cur_db		sysname
	declare @type_value	int

	select @type_value = 100
	
	if LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS)='push'
		select @type_value = 0
	else
		if LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS)='pull'
			select @type_value = 1
		else
			if LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS)='both'
				select @type_value = 2

	select @cur_db = db_name()
	
	create table #MSenumpushsubscriptions (
						publisher			sysname collate database_default not null,
						publisher_db		sysname collate database_default not null,
						publication			sysname collate database_default null,
						replication_type	int not NULL,
						subscription_type	int not NULL,
						last_updated		datetime null,
						subscriber_db		sysname collate database_default not null,
						update_mode			smallint null,
						last_sync_status	int null,
						last_sync_summary	sysname collate database_default null,
						last_sync_time		datetime null
						)

	if exists (select * from sysobjects where name='sysmergesubscriptions')
	begin
		-- return all subscriptions that this database is a subscriber to
		-- suppress all subscriptions that originate from this database.
		insert into #MSenumpushsubscriptions select p.publisher, p.publisher_db, p.name, 2, 
			s.subscription_type, s.last_sync_date, s.db_name, 
			NULL, s.last_sync_status, s.last_sync_summary, s.last_sync_date
			from sysmergepublications p, sysmergesubscriptions s
			where p.pubid = s.pubid 
			and (s.subscription_type=@type_value OR @type_value=2) 
			and s.pubid <> s.subid 
			and ((@publisher = N'%') or (p.publisher = @publisher))
           	and ((@publisher_db = N'%') or ( p.publisher_db = @publisher_db))
			and s.db_name = @cur_db
			and p.pubid not in 
				(select pubid from sysmergepublications pubs where 
					lower(pubs.publisher) = LOWER(@@servername) AND
					pubs.publisher_db = @cur_db)
	end

	if exists (select * from sysobjects where name='MSreplication_subscriptions')
	begin
		if  exists (select * from sysobjects where name='MSsubscription_properties') and
			exists (select * from sysobjects where name='MSsubscription_agents ') 
		begin
			-- update_mode in MSreplication_subscriptions table is not reliable.
			insert into #MSenumpushsubscriptions select s.publisher, s.publisher_db, s.publication,
				case isnull(p.publication_type,0) when 0 then 0 else 1 end,
				s.subscription_type,
				s.time, @cur_db, 
				-- NOTE: For Queued case: we will always return 2/3 for the 4/5 case
				-- since we overload update_mode based on queue_type
				case when isnull(a.update_mode,0) = 4 then 2
				when isnull(a.update_mode,0) = 5 then 3
				else isnull(a.update_mode,0)
				end,
				a.last_sync_status,
				a.last_sync_summary,
				a.last_sync_time
				from MSreplication_subscriptions s with (NOLOCK) 
				left outer join MSsubscription_agents a with (NOLOCK) 
					on (UPPER(s.publisher) = UPPER(a.publisher) and 
						s.publisher_db = a.publisher_db and 
						((s.publication = a.publication and 
						s.independent_agent = 1 and
						a.publication <> N'ALL') or
						(a.publication = N'ALL' and s.independent_agent = 0)) and
						s.subscription_type = a.subscription_type)
				left outer join MSsubscription_properties p with (NOLOCK)
					on (UPPER(s.publisher) = UPPER(p.publisher) and 
						s.publisher_db = p.publisher_db and 
						s.publication = p.publication and
						-- don't use property table for push. 
						s.subscription_type <> 0) 
				where
					((@publisher = N'%') OR (UPPER(s.publisher) = UPPER(@publisher))) AND
					((@publisher_db = N'%') or ( s.publisher_db = @publisher_db)) and
					((s.subscription_type = 0 and @type_value = 0) or
					-- For pull, return both pull and anonymous
					(s.subscription_type <> 0 and @type_value = 1) or
					@type_value = 2) 
		end
		-- Property table does not exists.
		else if exists (select * from sysobjects where name='MSsubscription_agents ') 
		begin
			-- update_mode in MSreplication_subscriptions table is not reliable.
			insert into #MSenumpushsubscriptions select s.publisher, s.publisher_db, s.publication,
				-- Property table does not exists. Say transactional.
				0,
				s.subscription_type,
				s.time, @cur_db, 
				-- NOTE: For Queued case: we will always return 2/3 for the 4/5 case
				-- since we overload update_mode based on queue_type
				case when isnull(a.update_mode,0) = 4 then 2
				when isnull(a.update_mode,0) = 5 then 3
				else isnull(a.update_mode,0)
				end,
				a.last_sync_status,
				a.last_sync_summary,
				a.last_sync_time
				from MSreplication_subscriptions s with (NOLOCK) 
				left outer join MSsubscription_agents a with (NOLOCK) 
					on (UPPER(s.publisher) = UPPER(a.publisher) and 
						s.publisher_db = a.publisher_db and 
						((s.publication = a.publication and 
						s.independent_agent = 1 and
						a.publication <> N'ALL') or
						(a.publication = N'ALL' and s.independent_agent = 0)) and
						s.subscription_type = a.subscription_type)
				where
					((@publisher = N'%') OR (UPPER(s.publisher) = UPPER(@publisher))) AND
					((@publisher_db = N'%') or ( s.publisher_db = @publisher_db)) and
					((s.subscription_type = 0 and @type_value = 0) or
					-- For pull, return both pull and anonymous
					(s.subscription_type <> 0 and @type_value = 1) or
					@type_value = 2) 			
		end
		-- Agents table does not exists.
		else if exists (select * from sysobjects where name='MSsubscription_properties ') 
		begin
			-- update_mode in MSreplication_subscriptions table is not reliable.
			insert into #MSenumpushsubscriptions select s.publisher, s.publisher_db, s.publication,
				case isnull(p.publication_type,0) when 0 then 0 else 1 end,
				s.subscription_type,
				s.time, @cur_db, 
				s.update_mode,
				NULL, --a.last_sync_status,
				NULL, --a.last_sync_summary,
				NULL  --a.last_sync_time	
				from MSreplication_subscriptions s with (NOLOCK) 
				left outer join MSsubscription_properties p with (NOLOCK)
					on (UPPER(s.publisher) = UPPER(p.publisher) and 
						s.publisher_db = p.publisher_db and 
						s.publication = p.publication and
						-- don't use property table for push. 
						s.subscription_type <> 0) 
				where
					((@publisher = N'%') OR (UPPER(s.publisher) = UPPER(@publisher))) AND
					((@publisher_db = N'%') or ( s.publisher_db = @publisher_db)) and
					((s.subscription_type = 0 and @type_value = 0) or
					-- For pull, return both pull and anonymous
					(s.subscription_type <> 0 and @type_value = 1) or
					@type_value = 2) 
		end
		-- Both table does not exists
		else
		begin
			-- update_mode in MSreplication_subscriptions table is not reliable.
			insert into #MSenumpushsubscriptions select s.publisher, s.publisher_db, s.publication,
				0,
				s.subscription_type,
				s.time, @cur_db, 
				s.update_mode,
				NULL, -- a.last_sync_status,
				NULL, -- a.last_sync_summary
				NULL  -- a.last_sync_time
				from MSreplication_subscriptions s with (NOLOCK)
				where
					((@publisher = N'%') OR (UPPER(s.publisher) = UPPER(@publisher))) AND
					((@publisher_db = N'%') or ( s.publisher_db = @publisher_db)) and
					((s.subscription_type = 0 and @type_value = 0) or
					-- For pull, return both pull and anonymous
					(s.subscription_type <> 0 and @type_value = 1) or
					@type_value = 2) 
		end
	
	end
	select * from #MSenumpushsubscriptions
	--drop table #MSenumpushsubscriptions
	return (0)
GO

grant execute on dbo.sp_MSenumsubscriptions to public
go 
exec dbo.sp_MS_marksystemobject sp_MSenumsubscriptions
GO


raiserror('Creating procedure sp_addmergepullsubscription_agent', 0,1)
GO
CREATE PROCEDURE sp_addmergepullsubscription_agent (
	@name							sysname = NULL,
	@publisher						sysname,  						/* Publisher server */
	@publisher_db					sysname,  						/* Publisher database */
	@publication 					sysname,	      				/* Publication name */
	@publisher_security_mode		int = 1,
	@publisher_login				sysname = NULL,
	@publisher_password				sysname = NULL,
	@publisher_encrypted_password	bit = 0,
	@subscriber						sysname = NULL,
	@subscriber_db					sysname = NULL,
	@subscriber_security_mode		int = NULL,						/* 0 standard; 1 integrated */
	@subscriber_login				sysname = NULL,
	@subscriber_password			sysname = NULL,
	@distributor					sysname = @@SERVERNAME,
	@distributor_security_mode		int = 0,						/* 0 standard; 1 integrated */
	@distributor_login				sysname = 'sa',
	@distributor_password			sysname = NULL,
	@encrypted_password				bit = 0,			/* distributor password encrypted or not */
    @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(255) = '',			/* Optional command line arguments */
    @merge_jobid					binary(16) = NULL OUTPUT,
    @enabled_for_syncmgr 			nvarchar(5) = 'false', /* Enabled for SYNCMGR: true or false */
	@ftp_address 					sysname = NULL,
	@ftp_port 						int = NULL,
	@ftp_login 						sysname = NULL,
	@ftp_password 					sysname = NULL,
    @alt_snapshot_folder  			nvarchar(255) = NULL,
    @working_directory    			nvarchar(255) = NULL,
    @use_ftp              			nvarchar(5) = 'false',
	@reserved 						nvarchar(100) = N'', -- Not default to null because null problems in conditional expressions.
	@use_interactive_resolver   	nvarchar(5) = 'false',
    @offloadagent         			nvarchar(5) = 'false',
    @offloadserver        			sysname = null,
	-- Used by DMO scripting
	@job_name						sysname = NULL,
    @dynamic_snapshot_location      nvarchar(260) = NULL
    ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */
    declare @command 				nvarchar(4000)
    declare @retcode 				int
	declare @database				sysname
	declare @repid					uniqueidentifier
	declare @pubid					uniqueidentifier
    declare @subscriber_srvid		int 
    declare @publisher_srvid		int 
	declare @name_id				nvarchar(50)
	declare @subscriber_typeid		int
	declare @subscription_type_id	int
	declare @category_name			sysname
	declare @platform_nt			binary
	DECLARE @subscriber_enc_password 	nvarchar(524)
	DECLARE @publisher_enc_password 	nvarchar(524)
	DECLARE @distributor_enc_password 	nvarchar(524)
    DECLARE @use_ftp_bit            	bit
	declare @use_interactive_bit		bit
    DECLARE @offload_agent_bit      	bit

	select @platform_nt = 0x1

    SELECT @use_ftp_bit = 0

	-- For attach check
	if exists (select * from sysobjects where name = 'MSrepl_restore_stage')
	begin
		raiserror(21211, 16, -1)
		return 1
	end

	-- Set null @optional_command_line to empty string to avoid string concat problem
	if @optional_command_line is null
		set @optional_command_line = ''
        else
                set @optional_command_line = N' ' + LTRIM( RTRIM(@optional_command_line) ) + N' '

	IF @distributor_password = N''
		select @distributor_password = NULL

	IF @publisher_password = N''
		select @publisher_password = NULL

	IF @ftp_password = N''
		select @ftp_password = NULL

	/*
	** Parameter Check: @subscriber and @subscriber_db
	*/

	if @subscriber IS NULL or rtrim(@subscriber) = ''
		SELECT @subscriber = @@SERVERNAME

	if @subscriber_db IS NULL or rtrim(@subscriber_db) = ''
		SELECT @subscriber_db = DB_NAME()
	
	EXECUTE @retcode = dbo.sp_validname @subscriber
    IF @@ERROR <> 0 OR @retcode <> 0
       RETURN (1)
	
    EXECUTE @retcode = dbo.sp_validname @subscriber_db
    IF @@ERROR <> 0 OR @retcode <> 0
       RETURN (1)

	-- Parameter check: @subscriber_security_mode	
	if @subscriber_security_mode is null
	begin
		if ( platform() & @platform_nt ) = @platform_nt
			select @subscriber_security_mode = 1
		else
			select @subscriber_security_mode = 0
	end	

    /* 
    ** Parameter check: @alt_snapshot_folder 
    ** @alt_snapshot_folder and @use_ftp are mutually exclusive    
    ** @dynamic_snapshot_location is incompatible with both 
    ** @alt_snapshot_folder and @use_ftp
    */

    IF @alt_snapshot_folder <> N'' AND @alt_snapshot_folder IS NOT NULL
    BEGIN
        IF LOWER(@use_ftp collate SQL_Latin1_General_CP1_CS_AS) = N'true'
        BEGIN
            RAISERROR(21146, 16, -1)
            RETURN (1)
        END
        IF @dynamic_snapshot_location <> N'' AND @dynamic_snapshot_location IS NOT NULL
        BEGIN
            RAISERROR(21341, 16, -1)
            RETURN (1)
        END
    END

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

    /* 
    ** Parameter check: @use_ftp
    ** Must be 'true' or 'false'
    */
    IF LOWER(@use_ftp collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
    BEGIN
        RAISERROR (14148, 16, -1, '@use_ftp')
        RETURN (1)
    END
    
    IF LOWER(@use_ftp collate SQL_Latin1_General_CP1_CS_AS) = 'true'
    BEGIN
        /*
        ** Ftp file transfer is incompatible with @dynamic_snapshot_location
        */
        IF @dynamic_snapshot_location <> N'' AND @dynamic_snapshot_location IS NOT NULL
        BEGIN
            RAISERROR (21342, 16, -1)
            RETURN (1)
        END

        SELECT @use_ftp_bit = 1

    END
    ELSE
    BEGIN
        SELECT @use_ftp_bit = 0
    END

    /*
    ** Parameter Check: @offloadserver
    ** If @offloadagent = 'true' then @offloadserver cannot be null
    */
    SELECT @offloadagent = LOWER(@offloadagent collate SQL_Latin1_General_CP1_CS_AS)
    IF @offloadagent NOT IN ('true', 'false')
    BEGIN
        RAISERROR (14148, 16, -1, '@offloadagent')
        RETURN (1)
    END

    IF @offloadagent = 'true'
    BEGIN
        SELECT @offload_agent_bit = 1
    END
    ELSE
    BEGIN
        SELECT @offload_agent_bit = 0
    END

    IF @offload_agent_bit = 1 AND (@offloadserver IS NULL or
                               @offloadserver = N'')
    BEGIN
        RAISERROR(21215, 16, -1)
        RETURN (1)
    END

    IF UPPER(@offloadserver) = UPPER(@@SERVERNAME) AND
       @offload_agent_bit = 1
    BEGIN
        RAISERROR(21227, 16, -1)
        RETURN (1)
    END

    EXEC @retcode = sp_MSreplcheckoffloadserver @offloadserver
    IF @retcode <> 0 OR @@ERROR <> 0
        RETURN (1)

    -- Make sure that there are no leading or trailing blanks
    -- in the dynamic snapshot location
    select @dynamic_snapshot_location = rtrim(ltrim(@dynamic_snapshot_location))

	if ( ( platform() & @platform_nt ) <> @platform_nt and @subscriber_security_mode = 1)
	begin
		RAISERROR(21038, 16, -1)
		RETURN (1)
	end

	select @subscription_type_id = 1 /* pull agent only */
	/*
	** Set Default schedule values if NULL is specified
	** The default are not implemented during parmeter defintion because this procedure
	** is can be called from sp_addmergesubscription.
	*/
	if @frequency_type is NULL
		set @frequency_type = 4		/* Daily */
	if @frequency_interval is NULL
		set @frequency_interval = 1
	if @frequency_relative_interval is NULL
		set @frequency_relative_interval = 1
	if @frequency_recurrence_factor is NULL
		set @frequency_recurrence_factor = 0
	if @frequency_subday is NULL
		set @frequency_subday = 8	/* Hour */
    if @frequency_subday_interval is NULL
		set @frequency_subday_interval = 1
    if @active_start_time_of_day is NULL
		set @active_start_time_of_day = 0
    if @active_end_time_of_day is NULL
		set @active_end_time_of_day = 235959
    if @active_start_date is NULL
		set @active_start_date = 0
    if @active_end_date is NULL
		set @active_end_date = 99991231

	select @subscriber_srvid = srvid from master..sysservers where UPPER(srvname) = UPPER(@@SERVERNAME) collate database_default
    IF @subscriber_srvid IS NULL
		BEGIN
		   RAISERROR (14010, 16, -1)
		   RETURN (1)     
		END
			
	select @pubid = pubid from sysmergepublications 
		where name = @publication and UPPER(publisher)=UPPER(@publisher) and publisher_db=@publisher_db
	IF @pubid is NULL
		begin
			RAISERROR (20026, 16, -1, @publication)
			RETURN (1)
		end
			
	select @repid = subid, @subscriber_typeid = subscriber_type from sysmergesubscriptions
			where srvid = @subscriber_srvid and pubid<>subid and pubid = @pubid and db_name = @subscriber_db

	if @subscriber_typeid = 3 set @subscription_type_id = 2 /* This corresponds to anonymous subscription */

	if (@subscription_type_id <> 0)
	begin
		if (@subscriber_security_mode = 0) and (@subscriber_login IS NULL or rtrim(@subscriber_login) = '')
		begin
			raiserror(3217, 16, -1, '@subscriber_login')
			return (1)
		end
	end

	if (@distributor_security_mode = 0) and (@distributor_login IS NULL or rtrim(@distributor_login) = '')
	begin
		raiserror(3217, 16, -1, '@distributor_login')
		return (1)
	end

	if (@publisher_security_mode = 0) and (@publisher_login IS NULL or rtrim(@publisher_login) = '')
	begin
		raiserror(3217, 16, -1, '@publisher_login')
		return (1)
	end

	IF NOT EXISTS (select * from sysobjects where name = 'MSsubscription_properties' and type = 'U')
	begin
		raiserror(14027, 16, -1, 'The subscription properties table ''MSsubscription_properties''')
		return (1)
	end
	
	declare @job_existing bit
	-- For scripting
	if @job_name is null
		select @job_existing = 0
	else
	begin
		select @job_existing = 1
		select @name = @job_name
	end

	/*
    ** Construct unique task name if @name = NULL
	*/
	IF @name IS NULL
		begin
			 SELECT @name = CONVERT(nvarchar(23),@publisher ) + '-' + CONVERT(nvarchar(23),@publisher_db) + '-' + 
						CONVERT(nvarchar(23),@publication) + '-' + CONVERT(nvarchar(23),@subscriber) + '-' +
						CONVERT(nvarchar(23), @subscriber_db) + '- 0'
		end
	else
	begin
		/*
		** validate name
		*/
		exec @retcode = dbo.sp_MSreplcheck_name @name
		if @@ERROR <> 0 or @retcode <> 0
			return(1)

	end

	-- Get property values.
	if @reserved = 'no_change_to_properties'
	begin
		-- Get the distributor value. It will be used in agent command line.
		select @distributor = distributor, 
			@enabled_for_syncmgr = case enabled_for_syncmgr
				when 0 then 'false'
				when 1 then	'true'
				end
			from MSsubscription_properties where
			UPPER(publisher) = UPPER(@publisher)
			and publisher_db =  @publisher_db
			and publication = @publication
	end

	begin tran
	save tran sp_addpullsub_agent

	if @job_existing = 0
	begin
		/* Construct task command */
		select @command = '-Publisher ' + QUOTENAME(@publisher) + ' -PublisherDB ' + QUOTENAME(@publisher_db) + ' '
		select @command = @command + '-Publication ' + QUOTENAME(@publication) + ' '
		select @command = @command + '-Subscriber ' + QUOTENAME(@@SERVERNAME)  + ' '
		select @command = @command + '-SubscriberDB ' + QUOTENAME(db_name()) + ' '
		SELECT @command = @command + '-SubscriptionType ' + convert(nvarchar(10), @subscription_type_id)  + ' '
		
		select @command = @command + '-SubscriberSecurityMode ' + 
			convert(nvarchar(10),@subscriber_security_mode) + ' '
		if @subscriber_login is not NULL
			select @command = @command + '-SubscriberLogin ' + quotename(@subscriber_login) + ' '
		if @subscriber_password is not NULL
		begin
			set @subscriber_enc_password = @subscriber_password
			exec @retcode = master.dbo.xp_repl_encrypt @subscriber_enc_password OUTPUT
			select @command = @command + '-SubscriberEncryptedPassword ' + quotename(@subscriber_enc_password) + ' '
		end
		
		select @command = @command + @optional_command_line
		select @command = @command + ' -Distributor ' + QUOTENAME(@distributor) + ' '

		if @offload_agent_bit = 1
			select @command = @command + N'-Offload ' + @offloadserver + N' '
		
        select @dynamic_snapshot_location = rtrim(ltrim(@dynamic_snapshot_location))
        if @dynamic_snapshot_location is not null and 
           @dynamic_snapshot_location <> N''
            select @command = @command + N'-DynamicSnapshotLocation ' + fn_replquotename(@dynamic_snapshot_location) collate database_default + N' '

		select @database = db_name()

		-- Get Merge category name (assumes category_id = 14)
		select @category_name = name FROM msdb.dbo.syscategories where category_id = 14

		EXEC @retcode = dbo.sp_MSadd_repl_job
				@name = @name,
				@subsystem = 'Merge',
				@server = @@SERVERNAME,
				@databasename = @database,
				@enabled = 1,
				@freqtype = @frequency_type,
				@freqinterval = @frequency_interval,
				@freqsubtype = @frequency_subday,
				@freqsubinterval = @frequency_subday_interval,
				@freqrelativeinterval = @frequency_relative_interval,
				@freqrecurrencefactor = @frequency_recurrence_factor,
				@activestartdate = @active_start_date,
				@activeenddate = @active_end_date,
				@activestarttimeofday = @active_start_time_of_day,
				@activeendtimeofday = @active_end_time_of_day,
				@command = @command,
				@retryattempts = 10,
				@retrydelay = 1,
				@category_name = @category_name,
				@job_id = @merge_jobid OUTPUT

		if @@ERROR <> 0 or @retcode <> 0 goto Rollback_tran
	end
	else
	begin
		select @merge_jobid = job_id from msdb..sysjobs_view where 
			name = @name collate database_default and
			UPPER(originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))
		if @merge_jobid IS NULL
		begin
			-- Message from msdb.dbo.sp_verify_job_identifiers
			RAISERROR(14262, -1, -1, 'Job', @name)          
			goto Rollback_tran
		end
	end


	if @reserved <> 'attach_subscription' and (@subscription_type_id = 1) OR (@subscription_type_id = 2)
	begin
		IF NOT EXISTS (select * from MSsubscription_properties 
            where UPPER(publisher) = UPPER(@publisher)
			and publisher_db =  @publisher_db
			and publication = @publication) 
		BEGIN
			
			-- Copy the passwords to new value before attempting to encrypt
			set @distributor_enc_password = @distributor_password
			set @publisher_enc_password = @publisher_password

			IF (@encrypted_password = 0)
			-- Encrypt the password
			BEGIN
				EXEC @retcode = master.dbo.xp_repl_encrypt @distributor_enc_password OUTPUT
				IF @@error <> 0 OR @retcode <> 0 goto Rollback_tran
			END
	
			IF (@publisher_encrypted_password = 0)
			-- Encrypt the password
			BEGIN
				EXEC @retcode = master.dbo.xp_repl_encrypt @publisher_enc_password OUTPUT
				IF @@error <> 0 OR @retcode <> 0 goto Rollback_tran
			END
	
			INSERT INTO MSsubscription_properties 
			(publisher, publisher_db, publication, publication_type, 
			 publisher_login,publisher_password, publisher_security_mode, 
			 distributor, distributor_login, distributor_password, 
			 distributor_security_mode, ftp_address, ftp_port, ftp_login, 
			 ftp_password, alt_snapshot_folder, working_directory, use_ftp,
             offload_agent, offload_server, dynamic_snapshot_location)
			values 
			(@publisher, @publisher_db, @publication, 2, @publisher_login, 
			 @publisher_enc_password, @publisher_security_mode, @distributor, 
			 @distributor_login, @distributor_enc_password, 
			 @distributor_security_mode, null, null, null,
			 null, @alt_snapshot_folder, @working_directory, @use_ftp_bit,
             @offload_agent_bit, @offloadserver, @dynamic_snapshot_location)
		END
	end

		
	/* Update merge joibd for this pull subscription */
	UPDATE MSmerge_replinfo set merge_jobid = @merge_jobid WHERE repid = @repid

	/* Update the distributor column in sysmergesubscriptions */
	UPDATE sysmergesubscriptions set 
			use_interactive_resolver = @use_interactive_bit,
			distributor = @distributor 
				WHERE subid = @repid
	
	/* Conditional support for MobileSync */
    if LOWER(@enabled_for_syncmgr collate SQL_Latin1_General_CP1_CS_AS) = 'true'
    BEGIN
		/* Call sp_MSregistersubscription so that the subscription can be synchronized via MobileSync etc. */
		exec @retcode = dbo.sp_MSregistersubscription @replication_type = 2,
									@publisher = @publisher,
									@publisher_security_mode = @publisher_security_mode,
									@publisher_login = @publisher_login,
									@publisher_password = @publisher_password,
									@publisher_db = @publisher_db,
									@publication = @publication,
								    @subscriber = @subscriber,
								    @subscriber_db = @subscriber_db,
									@subscriber_security_mode = @subscriber_security_mode,
									@subscriber_login = @subscriber_login,
									@subscriber_password = @subscriber_password,
									@distributor = @distributor,
									@distributor_security_mode = @distributor_security_mode,
									@distributor_login = @distributor_login,
									@distributor_password = @distributor_password,
								    @subscription_id = @repid,
									@subscription_type = @subscription_type_id,
									@use_interactive_resolver = @use_interactive_bit

		IF @@ERROR <> 0 or @retcode <> 0 goto Rollback_tran
	END

commit tran
	RETURN (0)
Rollback_tran:
	rollback tran sp_addpullsub_agent
	commit tran
	return(1)
GO

exec dbo.sp_MS_marksystemobject sp_addmergepullsubscription_agent
go

grant exec on dbo.sp_addmergepullsubscription_agent to public
go

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

raiserror('Creating procedure sp_MSget_load_hint', 0,1)
go
create procedure sp_MSget_load_hint @objname sysname, @type int
as
declare @objid int
declare @tabname sysname
declare @tabid int
declare @hint   nvarchar(2048)
declare @i int
declare @thiskey sysname
declare @indid smallint
declare @indstat int

select @objid = object_id( @objname )

if exists ( select * from sysobjects where id = @objid and type = 'V' )
begin
	select @tabid = depid from sysdepends where id = object_id( @objname )
	select @tabname = object_name( @tabid )
	
	select @indid = indid, @indstat = status 
	    from sysindexes where id = @tabid and ( status & @type ) = @type
	if @indid is not null
	begin
		create table #colnames ( idxcolname sysname collate database_default)

		select @thiskey = index_col( @tabname, @indid, 1 )
		select @i = 2
		while (@thiskey is not null )
		begin
			insert into #colnames( idxcolname ) values( @thiskey )
			select @thiskey = index_col(@tabname, @indid, @i)
			select @i = @i + 1
		end
		if exists ( select idxcolname from #colnames 
                     where idxcolname not in
	                    (select name from syscolumns where id = object_id( @objname ) ) )
		begin
			select @tabid = 0
		end	
		drop table #colnames
	end
	else
	begin
		select @tabid = 0
	end
end
else 
begin
	select @tabname = @objname
	select @tabid = object_id( @objname )

	select @indid = indid, @indstat = status 
	    from sysindexes where id = @tabid and ( status & @type ) = @type
	if @indid is null
	begin
		select @tabid = 0
	end
end

-- Check for computed columns in chosen index. If computed columns exist,
-- don't return a load hint.
if @indid is not null
begin
    select @i = 1
    select @thiskey = index_col( @tabname, @indid, @i )
    while @thiskey is not null
    begin
        if isnull( columnproperty( @tabid, @thiskey, 'IsComputed' ), 0 ) = 1
        begin
            select @indid = null,
                   @tabid = 0
            break
        end
        select @i = @i + 1
        select @thiskey = index_col( @tabname, @indid, @i )
    end
end

if @tabid <> 0
begin
	select @hint = 'ORDER( '+QUOTENAME(index_col( @tabname, @indid, 1 )) collate database_default
	select @thiskey = index_col( @tabname, @indid, 2 ) 
	select @i = 3
	while (@thiskey is not null )
	begin
		select @hint = @hint + ',' + QUOTENAME(@thiskey) collate database_default
		select @thiskey = index_col(@tabname, @indid, @i)
		select @i = @i + 1
	end

	select @hint = @hint + ' ASC)'

	select @hint, @indstat
end
GO

EXEC dbo.sp_MS_marksystemobject sp_MSget_load_hint
grant execute on dbo.sp_MSget_load_hint to public

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

raiserror('Creating procedure sp_MSadd_repl_job',0,1)
go

CREATE PROCEDURE sp_MSadd_repl_job
  @name                   nvarchar(200),
  @subsystem              nvarchar(60)  = 'TSQL',
  @server                 sysname  = NULL,
  @username               sysname  = NULL,
  @databasename           sysname  = NULL,
  @enabled                TINYINT      = 0,
  @freqtype               INT          = 2, -- 2 means OnDemand
  @freqinterval           INT          = 1,
  @freqsubtype            INT          = 1,
  @freqsubinterval        INT          = 1,
  @freqrelativeinterval   INT          = 1,
  @freqrecurrencefactor   INT          = 1,
  @activestartdate        INT          = 0,
  @activeenddate          INT          = 0,
  @activestarttimeofday   INT          = 0,
  @activeendtimeofday     INT          = 0,
  @nextrundate            INT          = 0,
  @nextruntime            INT          = 0,
  @runpriority            INT          = 0,
  @emailoperatorname      nvarchar(100) = NULL,
  @retryattempts          INT          = NULL,
  @retrydelay             INT          = 0,
  @command                nvarchar(4000)= NULL,
  @loghistcompletionlevel INT          = 2,
  @emailcompletionlevel   INT          = 0,
  @description            nvarchar(255) = NULL,
  @tagadditionalinfo      nvarchar(96)  = NULL,
  @tagobjectid            INT          = NULL,
  @tagobjecttype          INT          = NULL,
  @cmdexecsuccesscode     INT          = 0,
  @category_name          sysname = NULL, -- New for 7.0
  @failure_detection      BIT           = 0,
  @agent_id               INT           = NULL,
  @job_id BINARY(16) = NULL OUTPUT
AS
BEGIN
  DECLARE   @retval INT
  declare   @step_id int
  declare   @step_name nvarchar(100)
  declare   @step_command nvarchar(1024)
  declare   @on_fail_action tinyint
  declare   @on_success_action tinyint
  declare   @schedule_name nvarchar(100)
  declare   @comments nvarchar(100)

  SET NOCOUNT ON

  SELECT @retval = 1 -- 0 means success, 1 means failure
  set @step_id = 1
  set @on_fail_action = 2   -- Return failure
  set @on_success_action = 1    -- Return success
  set @step_command = NULL

  /*
  ** Set default retries to every minute for 10 minutes.
  **
  */
  if @retryattempts = NULL and @retrydelay = 0
  begin
     select @retryattempts = 10
     select @retrydelay = 1
  end

  BEGIN TRANSACTION
  save tran sp_MSadd_repl_job

    -- Drop the job if it already exists
    IF EXISTS (SELECT * FROM msdb..sysjobs_view WHERE name = @name collate database_default)
    begin
        exec @retval = msdb.dbo.sp_delete_job @job_name=@name
        if @@ERROR<>0 or @retval<>0
            goto UNDO
    end

    -- Add the job
    EXECUTE @retval = msdb.dbo.sp_add_job
      @job_name                   = @name,
      @enabled                    = @enabled,
      @start_step_id              = 1,
      @description                = @description,
      @category_name              = @category_name,
      @notify_level_eventlog      = @loghistcompletionlevel,
      @notify_level_email         = @emailcompletionlevel,
      @notify_email_operator_name = @emailoperatorname,
      @job_id = @job_id OUTPUT

    IF (@retval <> 0)
    BEGIN
        GOTO UNDO
    END

    -- Add startup message step
    if @failure_detection = 1
    begin

        select @step_name = 
            case UPPER(@subsystem collate SQL_Latin1_General_CP1_CS_AS)
                when N'SNAPSHOT' then formatmessage(21410)
                when N'LOGREADER' then formatmessage(20528)
                when N'DISTRIBUTION' then formatmessage(21411)
                when N'MERGE' then formatmessage(21412)
                when N'QUEUEREADER' then formatmessage(21422)
            end 
        select @comments = formatmessage(20529)

        -- Construct command based on subsystem type
        select @step_command =
            case UPPER(@subsystem collate SQL_Latin1_General_CP1_CS_AS) 
                WHEN 'SNAPSHOT' THEN
                N'sp_MSadd_snapshot_history @perfmon_increment = 0,  @agent_id = ' + 
                    convert (nvarchar(10), @agent_id) + N', @runstatus = 1,  
                    @comments = ''' + @comments + ''''
                WHEN 'LOGREADER' THEN
                N'sp_MSadd_logreader_history @perfmon_increment = 0, @agent_id = ' + 
                    convert (nvarchar(10), @agent_id) + N', @runstatus = 1, 
                    @comments = ''' + @comments + ''''
                WHEN 'DISTRIBUTION' THEN
                N'sp_MSadd_distribution_history @perfmon_increment = 0, @agent_id = ' + 
                    convert (nvarchar(10), @agent_id) + N', @runstatus = 1,  
                    @comments = ''' + @comments + ''''
                WHEN 'MERGE' THEN
                N'sp_MSadd_merge_history @perfmon_increment = 0, @agent_id = ' + 
                    convert (nvarchar(10),@agent_id) + N', @runstatus = 1,  
                    @comments = ''' + @comments + ''''
                WHEN 'QUEUEREADER' THEN
                N'sp_MSadd_qreader_history @perfmon_increment = 0, @agent_id = ' + 
                    convert (nvarchar(10), @agent_id) + N', @runstatus = 1,  
                    @comments = ''' + @comments + ''''                  
            end
    
        -- Add the job step
        EXECUTE @retval = msdb.dbo.sp_add_jobstep
          @job_id                = @job_id,
          @step_id               = @step_id,
          @step_name             = @step_name,
          @command               = @step_command,
          @cmdexec_success_code  = @cmdexecsuccesscode,
          @on_success_action     = 3,   -- Goto next step
          @on_fail_action        = 3,   -- Goto next step
          @server                = @server,
          @database_name         = @databasename,
          @database_user_name    = @username,
          @os_run_priority       = @runpriority

        IF (@retval <> 0)
        BEGIN
            GOTO UNDO
        END

        set @step_id = @step_id + 1
        set @on_fail_action = 3         -- Goto next step
    end

    -- Add the job step
    select @step_name = formatmessage(20530)
    EXECUTE @retval = msdb.dbo.sp_add_jobstep
      @job_id                = @job_id,
      @step_id               = @step_id,
      @step_name             = @step_name,
      @subsystem             = @subsystem,
      @command               = @command,
      @cmdexec_success_code  = @cmdexecsuccesscode,
      @on_success_action     = @on_success_action,
      @on_fail_action        = @on_fail_action,
      @server                = @server,
      @database_name         = @databasename,
      @database_user_name    = @username,
      @retry_attempts        = @retryattempts,
      @retry_interval        = @retrydelay,
      @os_run_priority       = @runpriority

    IF (@retval <> 0)
    BEGIN
        GOTO UNDO
    END

    -- Add failure message step
    if @failure_detection = 1
    begin

        set @step_id = @step_id + 1

        select @step_name = formatmessage(20531)

        -- Construct command
        select @step_command = N'sp_MSdetect_nonlogged_shutdown @subsystem = ''' + @subsystem +  N''', @agent_id = ' + convert (nvarchar(10),   @agent_id) 

        -- Add the job step
        EXECUTE @retval = msdb.dbo.sp_add_jobstep
          @job_id                = @job_id,
          @step_id               = @step_id,
          @step_name             = @step_name,
          @command               = @step_command,
          @cmdexec_success_code  = @cmdexecsuccesscode,
          @on_success_action     = 2,                   -- Always quit with failure
          @server                = @server,
          @database_name         = @databasename,
          @database_user_name    = @username,
          @os_run_priority       = @runpriority

        IF (@retval <> 0)
        BEGIN
            GOTO UNDO
        END
    end

    -- Add the job schedule
    IF (@activestartdate = 0)
      SELECT @activestartdate = NULL
    IF (@activeenddate = 0)
      SELECT @activeenddate = NULL
    
    -- But if @activeenddate is NOT NULL, then @activestartdate cannot be allowed to be NULL either.  Set it to today's date converted to the int format used yyyymmdd

    IF (@activeenddate IS NOT NULL AND @activestartdate IS NULL)
    SELECT @activestartdate=DATEPART(YYYY,getdate()) * 10000 + DATEPART(MM,getdate()) * 100 + DATEPART(DD,getdate())

    -- But never let startdate be > end date
    IF (@activestartdate > @activeenddate)
    SELECT @activestartdate=@activeenddate

    IF (@activestarttimeofday = 0)
      SELECT @activestarttimeofday = NULL
    IF (@activeendtimeofday = 0)
      SELECT @activeendtimeofday = NULL
    IF (@freqtype <> 0x2) -- OnDemand tasks simply have no schedule in 7.0
    BEGIN
      select @schedule_name = formatmessage(20532)

      EXECUTE @retval = msdb.dbo.sp_add_jobschedule
        @job_id                 = @job_id,
        @name                   = @schedule_name,
        @enabled                = 1,
        @freq_type              = @freqtype,
        @freq_interval          = @freqinterval,
        @freq_subday_type       = @freqsubtype,
        @freq_subday_interval   = @freqsubinterval,
        @freq_relative_interval = @freqrelativeinterval,
        @freq_recurrence_factor = @freqrecurrencefactor,
        @active_start_date      = @activestartdate,
        @active_end_date        = @activeenddate,
        @active_start_time      = @activestarttimeofday,
        @active_end_time        = @activeendtimeofday

      IF (@retval <> 0)
      BEGIN
        GOTO UNDO
      END
    END

    -- And finally, add the job server
    EXECUTE @retval = msdb.dbo.sp_add_jobserver @job_id = @job_id, @server_name  = '(local)'

    IF (@retval <> 0)
    BEGIN
      GOTO UNDO
    END

  COMMIT TRANSACTION

  -- If this is an autostart LogReader or Distribution or Merge job, add the [new] '-Continuous' paramter to the command
  IF (@freqtype = 0x40) AND ((UPPER(@subsystem collate SQL_Latin1_General_CP1_CS_AS) = 'LOGREADER') OR (UPPER(@subsystem collate SQL_Latin1_General_CP1_CS_AS) = 'DISTRIBUTION') OR 
            (UPPER(@subsystem collate SQL_Latin1_General_CP1_CS_AS) = 'MERGE') OR (UPPER(@subsystem collate SQL_Latin1_General_CP1_CS_AS) = 'QUEUEREADER'))
  BEGIN
    UPDATE msdb.dbo.sysjobsteps
    SET command = command + ' -Continuous'
    WHERE (job_id = @job_id)
      AND ((@failure_detection = 0 and step_id = 1) or (@failure_detection = 1 and step_id = 2))
  END  

  -- If this is an autostart job, start it now (for backwards compatibility with 6.x SQLExecutive behaviour)
  IF (@freqtype = 0x40)
    EXECUTE msdb.dbo.sp_start_job @job_id = @job_id, @error_flag = 0

  RETURN(0)

UNDO:
    rollback tran sp_MSadd_repl_job
    commit tran
    return(1)

END
go
exec sp_MS_marksystemobject sp_MSadd_repl_job

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

go
--
-- Name: sp_MSdroparticleconstraints
--
-- Description: This procedure is used by the Distribution Agent to purge all
--              the existing check, PK, UK, and FK constraints on a table
--              article at the Subscriber.
--
-- Parameters:  @destination_object sysname (mandatory)
--              @destination_owner sysname (mandatory)
-- 
-- Returns: 0 - succeeded
--          1 - failed
-- 
-- Security: Procedural security check is done inside this procedure
--           to make sure that the caller is either a member of the db_owner
--           role of the subscriber database or a member of the sysadmin role
--           of the subscription server 
--
print ''
print 'Creating procedure sp_MSdroparticleconstraints'
go
create procedure dbo.sp_MSdroparticleconstraints (
    @destination_object sysname,
    @destination_owner sysname
    )
as
begin
    set nocount on
    declare @retcode      int,
            @objid        int,
            @constid      int,
            @drop_command nvarchar(4000),
            @qualified_tablename nvarchar(540),
            @publish_bit  int,
            @mergepublish_bit int

    
    select @retcode             = 0,
           @objid               = null,
           @constid             = null,
           @drop_command        = null,
           @qualified_tablename = null,
           @publish_bit         = 1,
           @mergepublish_bit    = 128

    -- Security check
    exec @retcode = sp_MSreplcheck_subscribe
    if @retcode<>0 or @@error<>0
        return 1

    -- Get object id of the target table
    select @objid = id 
      from sysobjects
     where name = @destination_object
       and (user_name(uid) = @destination_owner 
            or @destination_owner is null 
            or @destination_owner = N'')

    -- If the object is not at the subscriber, there isn't anything we 
    -- can(or need to)  do
    if @objid is null 
        return 0

    select @qualified_tablename = 
            N'['+replace(@destination_object,N']',N']]')+N']'

    if @destination_owner is not null and @destination_owner<>N''
    begin
        select @qualified_tablename = 
            N'['+replace(@destination_owner,N']',N']]')+N'].'+@qualified_tablename
    end

    -- Skip constraint dropping for republished article
    if exists (select * 
                 from sysobjects 
                where id = @objid
                  and ((replinfo & @publish_bit <> 0) or
                       (replinfo & @mergepublish_bit <> 0)))
    begin
        return 0
    end

    declare hConst cursor local fast_forward for
     select constid 
       from sysconstraints 
      where id = @objid
      order by convert(int, objectproperty(constid,'IsForeignKey')) desc
    if @@error<>0
        return 1

    open hConst
    if @@error<>0
        return 1
    
    fetch hConst into @constid 
    while (@@fetch_status<>-1)
    begin
    
        if objectproperty(@constid,N'IsUniqueCnst') = 1 or
           objectproperty(@constid,N'IsPrimaryKey') = 1 or
           objectproperty(@constid,N'IsCheckCnst') = 1 or
           objectproperty(@constid,N'IsForeignKey') = 1
        begin
            select @drop_command = 
                N'alter table '+@qualified_tablename+' drop constraint '+object_name(@constid)
            exec(@drop_command)
            if @@error <> 0
                return 1
        end
        fetch hConst into @constid        
    end
    close hConst
    deallocate hConst

    return @retcode

end
go
exec sp_MS_marksystemobject sp_MSdroparticleconstraints
grant execute on sp_MSdroparticleconstraints to public 

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

raiserror('Creating procedure sp_MSagent_access_check',0,1)
go
CREATE PROCEDURE sp_MSagent_access_check (
    @job_id         VARBINARY(16),
    @agent_type     sysname = NULL -- 'distribution' or 'merge', case insensitive
) AS

    DECLARE @retcode int
    DECLARE @count   int

    IF @agent_type IS NOT NULL
    BEGIN
        IF LOWER(@agent_type collate SQL_Latin1_General_CP1_CS_AS) NOT IN (N'distribution', N'merge')
        BEGIN
            RAISERROR(21170, 16, -1)
            RETURN (1)
        END
    END

    SELECT @retcode = 0
    -- Create a list of owners of the subscriptions depending on the given 
    -- agent 
    CREATE TABLE #sub_owner_list (owner_name sysname collate database_default)

    -- Try querying the Transactional subscriptions for the owner 
    IF @agent_type IS NULL OR LOWER(@agent_type collate SQL_Latin1_General_CP1_CS_AS) = N'distribution'
    BEGIN

        IF EXISTS (SELECT * 
                     FROM sysobjects 
                    WHERE type in ('U', 'S') AND name = N'syssubscriptions')
        BEGIN
            INSERT INTO #sub_owner_list 
            SELECT DISTINCT login_name 
              FROM dbo.syssubscriptions 
             WHERE distribution_jobid = @job_id 
        END

        IF NOT EXISTS (SELECT * FROM #sub_owner_list) AND 
           @agent_type IS NOT NULL
        BEGIN
            RAISERROR(21167, 16, -1, 'Distribution')
            SELECT @retcode = 1
            GOTO FAILURE
        END 
    END

    IF (@agent_type IS NULL AND NOT EXISTS (SELECT * FROM #sub_owner_list)) OR
       LOWER(@agent_type collate SQL_Latin1_General_CP1_CS_AS) = N'merge' 
    BEGIN
        
        SELECT @count = COUNT(*)
          FROM sysobjects
         WHERE name IN (N'sysmergesubscriptions', N'MSmerge_replinfo')
        
        IF @count = 2        
        BEGIN
            INSERT INTO #sub_owner_list 
            SELECT DISTINCT sms.login_name 
              FROM dbo.MSmerge_replinfo mr
        INNER JOIN dbo.sysmergesubscriptions sms
                ON mr.repid = sms.subid
             WHERE mr.merge_jobid = @job_id
        END

        IF NOT EXISTS (SELECT * FROM #sub_owner_list) AND 
           @agent_type IS NOT NULL
        BEGIN
            RAISERROR(21167, 16, -1, 'Merge')
            SELECT @retcode = 1
            GOTO FAILURE
        END 
    END

    IF NOT EXISTS (SELECT * FROM #sub_owner_list) 
    BEGIN
        RAISERROR(21134, 16, -1)
        SELECT @retcode = 1
        GOTO FAILURE
    END

    IF suser_sname(suser_sid()) NOT IN (SELECT owner_name FROM #sub_owner_list) AND
       is_srvrolemember('sysadmin') <> 1 AND
       is_member('db_owner') <> 1
    BEGIN

        RAISERROR(21168, 16, -1)
        SELECT @retcode = 1
        GOTO FAILURE
    END

FAILURE:

    DROP TABLE #sub_owner_list
    RETURN @retcode

GO

EXEC dbo.sp_MS_marksystemobject sp_MSagent_access_check
--------------------------------------------------------------------------------
--. sp_MScopysnapshot
--------------------------------------------------------------------------------
if exists (select * from sysobjects 
		where name = 'sp_MScopysnapshot' 
				and type = 'P')
	  drop procedure sp_MScopysnapshot

raiserror('Creating procedure sp_MScopysnapshot', 0,1)
go
CREATE PROCEDURE sp_MScopysnapshot (
    @source_folder           nvarchar(255),
    @destination_folder      nvarchar(255)
    )
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @directory_exists bit    
    DECLARE @ftporuncdir      nvarchar(5)
    DECLARE @pubdir           nvarchar(255)
    DECLARE @timestampdir     nvarchar(255)
    DECLARE @bslashindex      int
    DECLARE @bslashindex2     int
    DECLARE @bslashcounter    int
    DECLARE @command          nvarchar(1000)
    DECLARE @retcode          int
    DECLARE @platform_nt      bit

    IF platform() & 0x1 = 0x1
        SELECT @platform_nt = 1
    ELSE
        SELECT @platform_nt = 0

    -- If @source_folder is NULL then either the snapshot has not been 
    -- generated or it has been cleaned up
    IF @source_folder IS NULL OR @source_folder = N''
    BEGIN
        RAISERROR(21289, 16, -1)
        RETURN (1)
    END

    -- Make sure that the @destination folder is not null
    IF @destination_folder IS NULL OR
       @destination_folder = N''
    BEGIN
        RAISERROR(21287, 16, -1)      
        RETURN (1)
    END

    -- Append backslash to @destination_folder if it is not 
    -- there already
    IF SUBSTRING(@destination_folder, LEN(@destination_folder), 1) <> N'\'
    BEGIN
        SELECT @destination_folder = @destination_folder + N'\'
    END

    -- Check if the destination folder exists 
    EXEC sp_MSget_file_existence @destination_folder, @directory_exists OUTPUT
    IF @directory_exists = 0
    BEGIN
        RAISERROR(21287, 16, -1)      
        RETURN (1)
    END    
    
    -- Parse out the last three components in the source folder 
    -- Note that the source_folder must have a trailing backslash
    SELECT @bslashindex = 1
    SELECT @bslashindex2 = 1
    SELECT @bslashcounter = 0
    WHILE (@bslashindex <> 0)
    BEGIN
        SELECT @bslashindex = CHARINDEX(N'\', @source_folder, @bslashindex + 1)
        SELECT @bslashcounter = @bslashcounter + 1
        IF @bslashcounter > 4
        BEGIN
            SELECT @bslashindex2 = CHARINDEX(N'\', @source_folder, @bslashindex2 + 1)
        END    
    END  

    SELECT @bslashindex = CHARINDEX(N'\', @source_folder, @bslashindex2 + 1)
    SELECT @ftporuncdir = SUBSTRING(@source_folder, @bslashindex2 + 1, @bslashindex - @bslashindex2 - 1)
    SELECT @bslashindex2 = @bslashindex

    SELECT @bslashindex = CHARINDEX(N'\', @source_folder, @bslashindex2 + 1)
    SELECT @pubdir = SUBSTRING(@source_folder, @bslashindex2 + 1, @bslashindex - @bslashindex2 - 1) 
    SELECT @bslashindex2 = @bslashindex

    SELECT @bslashindex = CHARINDEX(N'\', @source_folder, @bslashindex2 + 1)
    SELECT @timestampdir = SUBSTRING(@source_folder, @bslashindex2 + 1, @bslashindex - @bslashindex2 - 1) 
    SELECT @bslashindex2 = @bslashindex
        
    -- Create the subdirectory structure underneath the specified snapshot
    -- folder. Ignore errors for now, we will check whether the directory 
    -- is successfully created later on.

    -- Don't suppress output from xp_cmdshell so user knows what's going on
    -- in case something goes wrong 
    SELECT @destination_folder = @destination_folder + @ftporuncdir + '\'
    SELECT @command = 'mkdir "' + @destination_folder + '"'
    IF (@platform_nt = 1)
        SELECT @command = '" ' + @command + ' "'
    EXEC master..xp_cmdshell @command
    
    SELECT @destination_folder = @destination_folder + @pubdir + '\'
    SELECT @command = 'mkdir "' + @destination_folder + '"'
    IF (@platform_nt = 1)
        SELECT @command = '" ' + @command + ' "'
    EXEC master..xp_cmdshell @command

    SELECT @destination_folder = @destination_folder + @timestampdir + '\'
    SELECT @command = 'mkdir "' + @destination_folder + '"'
    IF (@platform_nt = 1)
        SELECT @command = '" ' + @command + ' "'
    EXEC master..xp_cmdshell @command

    -- Check if the real destination folder exists
    EXEC sp_MSget_file_existence @destination_folder, @directory_exists OUTPUT
    IF @directory_exists = 0
    BEGIN
        RAISERROR(21288, 16, -1)
        RETURN (1)
    END

    -- Do the actual copying
    SELECT @command = 'copy "' + @source_folder + '*.*" "' + @destination_folder + '"'    
    IF (@platform_nt = 1)
        SELECT @command = '" ' + @command + ' "'
    
    EXEC @retcode = master..xp_cmdshell @command
    
    IF @retcode <> 0
        RETURN (1)
    ELSE
        RETURN (0)
END
go
EXEC dbo.sp_MS_marksystemobject sp_MScopysnapshot
go

--------------------------------------------------------------------------------
--. sp_MSestimatesnapshotworkload
--------------------------------------------------------------------------------
if exists (select * from sysobjects
     where type = 'P '
            and name = 'sp_attachsubscription')
     drop procedure sp_attachsubscription
go
print ''
print 'Creating procedure sp_attachsubscription'
go
CREATE PROCEDURE sp_attachsubscription (
@dbname	sysname,
@filename nvarchar(260),
@subscriber_security_mode		int = NULL,						/* 0 standard; 1 integrated */
@subscriber_login				sysname = NULL,
@subscriber_password			sysname = NULL
)
AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */
	declare @cmd nvarchar(4000)
	declare @retcode int
	DECLARE @platform_nt binary
	declare @copy_created bit
    declare @exists bit

	select @platform_nt = 0x1	
	select @retcode = 0
	select @copy_created = 0
    select @exists = 0

	-- Parameter check: @subscriber_security_mode
	if @subscriber_security_mode is null
	begin
		if ( platform() & @platform_nt ) = @platform_nt
			select @subscriber_security_mode = 1
		else
			select @subscriber_security_mode = 0
	end	

	if ( ( platform() & @platform_nt ) <> @platform_nt and @subscriber_security_mode = 1 )
	begin
		RAISERROR(21038, 16, -1)
		RETURN (1)
	end

	if (@subscriber_security_mode = 0) and (@subscriber_login IS NULL or rtrim(@subscriber_login) = '')
		set @subscriber_login = 'sa'

	-- Check to make sure the database does not exists.
	if exists (select * from master..sysdatabases where name = @dbname collate database_default)
	begin
		raiserror(20621, 16, -1, @dbname)
		return (1)
	end

	-- Check to see if users has permissions to create database
	-- permissions() have to be run in master to return create db permission.
	declare @pm int
	exec @retcode = master.dbo.sp_executesql N'select @pm = permissions()', N'@pm int output', @pm output
	if @@error <> 0 or @retcode <> 0
		return 1
	if @pm & 1 = 0
	begin
		raiserror(20618, 16, -1)
		return 1
	end

	-- Decompress the file
	-- We have to copy the file to another location first. 
	-- (cannot use source as destination')
	declare @temp_copy nvarchar(260)
	declare @file_dir nvarchar(260)
	declare @file_name nvarchar(260)
	declare @dir_cmd nvarchar(260)
	-- Set @drive_cmd if needed 
	-- Set temp copy to be the file directory first
	-- Note @file_dir include '\'
	if (charindex('\', @filename, 1) = 0)
	begin
		select @file_dir = ''
		select @file_name = @filename
	end
	else
	begin
		select @file_dir = left(@filename,len(@filename) + 1 - 
			charindex('\', reverse(@filename), 1))
		select @file_name = right(@filename, len(@filename) - len(@file_dir))
	end
	-- Get guid name
    declare @guid_name nvarchar(36)
    select @guid_name =  convert (nvarchar(36), newid())
	select @temp_copy = @file_dir + @guid_name + '.tmp'


	-- copy file
	select @cmd = 'copy "' + @filename + '" "' + @temp_copy + '"'
	exec @retcode = master.dbo.xp_cmdshell @cmd, NO_OUTPUT
	if @@error <> 0 or @retcode <> 0
	begin
		raiserror(21248, 16, -1, @filename)
		select @retcode = 1
		goto Cleanup
	end	

    exec @retcode = dbo.sp_MSget_file_existence @temp_copy, @exists output
    if @@error <> 0 or @retcode <> 0
    begin
        select @retcode = 1
        goto Cleanup
    end
    if @exists = 0
    begin
		raiserror(21247, 16, -1, @temp_copy)        
        select @retcode = 1
        goto Cleanup
    end

	select @copy_created = 1

	-- decompress
	exec @retcode = master.dbo.xp_unpackcab 
		@cabfilename = @temp_copy,
		@destination_folder = @file_dir,
		@verbose_level = 0,
		@destination_file = @file_name,
		@suppress_messages = 1
		
	if @@error <> 0 
	begin
		select @retcode = 1
		goto Cleanup
	end

	if @retcode in (1030,1029,2005)
	begin
		raiserror(20609, 16, -1, @filename)
		select @retcode = 1
		goto Cleanup
	end	
	else if @retcode <> 0
	-- re-issue the command to get errors
	begin
		exec @retcode = master.dbo.xp_unpackcab 
			@cabfilename = @temp_copy,
			@destination_folder = @file_dir,
			@verbose_level = 0,
			@destination_file = @file_name,
			@suppress_messages = 0
		select @retcode = 1
		goto Cleanup
	end

	-- Attach
	exec @retcode = dbo.sp_attach_single_file_db
		@dbname = @dbname,
		@physname = @filename
	if @retcode<>0 or @@error<>0
	begin
		raiserror(21248, 16, -1, @filename)
		select @retcode = 1
		goto Cleanup
	end


	-- Prepare the database for detach. 2 things will be done
	-- 1. Set a flag to indicate that this is a prepare sub db for detach
	-- 2. For merge, create table to store sysservers info for later fixing up after attach
	select @cmd = quotename(@dbname) + '.dbo.sp_MSrestore_sub'
	exec @retcode = @cmd
		@subscriber_security_mode = @subscriber_security_mode,	
		@subscriber_login = @subscriber_login,
		@subscriber_password = @subscriber_password
	if @retcode<>0 or @@error<>0
	begin
		select @retcode = 1
		goto Cleanup
	end

Cleanup:
	if @retcode <> 0
	begin
		-- The files will be deleted if some thing failed. 
		if exists (select * from master..sysdatabases where name = @dbname collate database_default)
			begin
				select @cmd = 'drop database ' + quotename(@dbname)
				exec (@cmd)
			end				
	end

	if @temp_copy is not null
	begin
		-- Restore the original file, ignore errors
		if @retcode <> 0 and @copy_created = 1
		begin
			select @cmd = 'copy "' + @temp_copy + '" "' + @filename + '"'
			exec master.dbo.xp_cmdshell @cmd, NO_OUTPUT
		end

		-- Delete the temp file.
		select @cmd = 'del ' + quotename(@temp_copy,'"') 
		EXEC master..xp_cmdshell @cmd, NO_OUTPUT
	end

	return @retcode
go
EXEC dbo.sp_MS_marksystemobject sp_attachsubscription
grant execute on dbo.sp_attachsubscription to public
go

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

raiserror(15339,-1,-1,'sp_MSestimatesnapshotworkload')
go
create proc sp_MSestimatesnapshotworkload (
    @publication sysname
    )
as
begin
    set nocount on

    declare @taskload                       int
    declare @bigtaskload                    bigint
    declare @table_created                  bit
    declare @retcode                        int

    -- Publication info
    declare @pubid                          int
    declare @compress_snapshot              bit
    declare @snapshot_in_defaultfolder      bit
    declare @alt_snapshot_folder            nvarchar(255)
    declare @enabled_for_internet           bit
    declare @ftp_password                   nvarchar(524) 
    declare @ftp_subdirectory               nvarchar(255)
    declare @pre_snapshot_script            nvarchar(255)
    declare @post_snapshot_script           nvarchar(255)    

    -- Per publication summary stats
    declare @numarticles                    int
    declare @totalrowcount                  int   
    declare @needsysprescript               bit
    declare @copysnapshot                   bit
    declare @deletefiles                    bit
    declare @scriptproccost                 int
    declare @addcommandstotal               int
    declare @numscripts                     int
    declare @totalbcpcostper100             int
    declare @numprescriptcommands           int 

    select @numarticles = 0
    select @totalrowcount = 0
    select @needsysprescript = 0
    select @copysnapshot = 0
    select @deletefiles = 0
    select @scriptproccost = 0
    select @deletefiles = 0
    select @addcommandstotal = 0
    select @numscripts = 0
    select @totalbcpcostper100 = 0
    select @numprescriptcommands = 0

    -- Per article variables
    declare @artid                          int
    declare @schema_option                  int
    declare @creation_script                nvarchar(255)
    declare @type                           tinyint
    declare @objid                          int
    declare @rowcount                       int
    declare @name                           sysname
    declare @pre_creation_command           int

    select @table_created = 0

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

    select @pubid = null
    select @compress_snapshot = 0
    select @snapshot_in_defaultfolder = 0
    select @alt_snapshot_folder = null
    select @enabled_for_internet = 0
    select @ftp_password = null
    select @ftp_subdirectory = null
    select @pre_snapshot_script = null
    select @post_snapshot_script = null
    -- Validate publication and retrieve publication information 
    select @pubid = pubid, 
           @compress_snapshot = compress_snapshot,
           @snapshot_in_defaultfolder = snapshot_in_defaultfolder,
           @alt_snapshot_folder = alt_snapshot_folder,
           @enabled_for_internet = enabled_for_internet,
           @ftp_password = ftp_password,
           @ftp_subdirectory = ftp_subdirectory,
           @pre_snapshot_script = pre_snapshot_script,
           @post_snapshot_script = post_snapshot_script
           from syspublications
     where name = @publication
    if @pubid = null
    begin   
        raiserror(20026, 11, -1, @publication)
        return (1)
    end

    -- An artid of -1 means the task is a per publication
    -- task

    -- Task id mapping - This is the list of task whose completion can be 
    -- reported by the snapshot agent
    -- 0 - Total workload (not really a task)
    -- 1 - Schema script generation
    -- 2 - Trigger script generation 
    -- 3 - XProp script generation
    -- 4 - Bcp file generation
    -- 5 - Activating subscription (estimate number of subscriptions?)
    -- 6 - Adding snapshot commands
    -- 7 - System pre-script generation
    -- 8 - Flushing folder for scripts
    -- 9 - Constraint script generation
    -- 10 - Copying pre-script
    -- 11 - Copying post-script
    -- 12 - Copying custom schema creation script
    -- 13 - Index script generation
    -- 14 - Flushing the cabinet
    -- 15 - Adding rowguid column
    -- 16 - Setting article procs
    -- 17 - Adding merge triggers
    -- 18 - Generating system table scripts
    -- 19 - Generating system table bcp files
    -- 20 - Making publication generation
    -- 21 - Generating and setting conflict script
    -- 22 - Generating publication views

    -- Weights and overheads
    -- DMO Script generation - 7/script
    -- BCP 5 overhead + 5/100 rows
    -- Flush folder for bcp 2/100 rows
    -- Delete file 2
    -- System pre-snapshot script 1 overhead + 0.5/pre-creation command
    -- Copying user file 2
    -- Adding file to cabinet 1 for scripts and 1/100 rows in bcp file
    -- Loading article info 3
    -- Delete bcp file 2/100rows 
    -- Copy bcp file  2/100rows  
    -- Flush scripts folder 1/2 scripts
    -- Flushing cabinet per 5 scripts 1
    -- Flushing cabinet per 500 rows 1

    -- Here is the list of cost factors 
    declare @addcommandcost     int
    declare @dmoscriptcost      int
    declare @bcpoverhead        int
    declare @bcpcostper100      int
    declare @flushper100        int
    declare @flushperscript     int
    declare @delfilecost        int
    declare @syspreoverhead     int
    declare @syspreper2commands int
    declare @addbcptocabper100  int
    declare @addscripttocab     int
    declare @copyfilecost       int
    declare @loadartinfo        int    
    declare @deletebcpper100    int
    declare @copybcpper100      int
    declare @flushper2scripts   int
    declare @flushcabper5scripts int
    declare @flushcabper500bcprows int
    declare @maxint             int

    select @addcommandcost = 2
    select @dmoscriptcost = 7
    select @bcpcostper100 = 20
    select @bcpoverhead = 2
    select @flushper100 = 2
    select @flushperscript = 3
    select @delfilecost = 2
    select @syspreoverhead = 1
    select @syspreper2commands = 1
    select @addbcptocabper100 = 1
    select @addscripttocab = 1
    select @copyfilecost = 2
    select @loadartinfo = 3    
    select @deletebcpper100 = 1
    select @copybcpper100 = 2
    select @flushper2scripts = 1
    select @flushcabper5scripts = 1
    select @flushcabper500bcprows = 1
    select @maxint = 2147483647

    create table #workload_breakdown
    (
        name        sysname collate database_default,
        artid       int,
        taskid      int,
        taskload    int
    )
    if @@error <> 0
        goto Failure

    select @table_created = 1

    -- Per publication work load estimate that can be done using publication
    -- properties alone
    
    -- Adding snapshot header commands
    
    -- Snapshot header begins + Snapshot header ends +
    -- directory command + Snapshot trailer command = 3 * @addcommandcost
    select @addcommandstotal = 4
    
    -- Alternate snapshot folder = 1 * @addcommandcost
    if @alt_snapshot_folder is not null and
       @alt_snapshot_folder <> N'' and
       @snapshot_in_defaultfolder = 1
    begin
        select @addcommandstotal = @addcommandstotal + @addcommandcost
    end

    -- Ftp commands
    if @enabled_for_internet = 1
    begin
        -- ftp_address and ftp_port must be there +=2 * @addcommandcost
        select @addcommandstotal = @addcommandstotal + 2 * @addcommandcost

        -- ftp_password += 1 * @addcommandcost
        if @ftp_password is not null and
           @ftp_password <> N''
        begin
            select @addcommandstotal = @addcommandstotal + @addcommandcost
        end
        
        -- ftp_subdirectory += 1 * @addcommandcost
        if @ftp_subdirectory is not null and
           @ftp_subdirectory <> N''
        begin
            select @addcommandstotal = @addcommandstotal + 1 * @addcommandcost
        end
    end

    -- Compressed archive path
    if @compress_snapshot = 1
    begin
        select @addcommandstotal = @addcommandstotal + 1 * @addcommandcost
    end    

    -- Snapshot trailer command
    select @addcommandstotal = @addcommandstotal +  @addcommandcost

    -- Need to copy files?
    if @alt_snapshot_folder is not null and @alt_snapshot_folder <> N''
       and @snapshot_in_defaultfolder = 1 and @compress_snapshot = 0
    begin
        select @copysnapshot = 1
    end

    -- Compute per-script file post processing cost and bcp cost per 100 rows
    -- Need to delete files?
    if (@alt_snapshot_folder is null or @alt_snapshot_folder <> N''
       or @snapshot_in_defaultfolder = 0) and @compress_snapshot = 1
    begin
        select @deletefiles = 1
    end

    select @totalbcpcostper100 = @bcpcostper100
    -- Copy file?
    if @copysnapshot = 1
    begin
        select @scriptproccost = @scriptproccost + @copyfilecost
        select @totalbcpcostper100 = @totalbcpcostper100 + @copybcpper100
    end

    -- Add file to cabinet?
    if @compress_snapshot = 1
    begin
        select @scriptproccost = @scriptproccost + @addscripttocab 
        select @totalbcpcostper100 = @totalbcpcostper100 + @addbcptocabper100
    end

    -- Delete file?
    if @deletefiles = 1
    begin 
        select @scriptproccost = @scriptproccost + @delfilecost
        select @totalbcpcostper100 = @totalbcpcostper100 + @deletebcpper100
    end

    -- Pre/Post-snapshot scripts, has to be computed after the 
    -- the per-script post-processing cost is calculated 
    if @pre_snapshot_script is not null and
       @pre_snapshot_script <> N''
    begin
        -- Cost of copying the script file
        select @taskload = @copyfilecost + @scriptproccost
        insert #workload_breakdown values (N'', -1, 10, @taskload)        
        -- Cost of adding the pre-snapshot command
        select @addcommandstotal = @addcommandstotal + @addcommandcost
        -- Increment scripts counter
        select @numscripts = @numscripts + 1
    end

    if @post_snapshot_script is not null and
       @post_snapshot_script <> N''
    begin
        -- Cost of copying the script file
        select @taskload = @copyfilecost + @scriptproccost
        insert #workload_breakdown values (N'', -1, 11, @taskload)
        -- Cost of adding the post-snapshot command
        select @addcommandstotal = @addcommandstotal + @addcommandcost
        -- Increment scripts counter
        select @numscripts = @numscripts + 1
    end
           
    -- Estimate the break down of per article tasks 
    declare hCarticles cursor local fast_forward
    for select artid, creation_script, objid, convert(int, schema_option), type, name, pre_creation_cmd  
          from sysextendedarticlesview where pubid = @pubid 

    open hCarticles
   
    fetch hCarticles into @artid, @creation_script, @objid, @schema_option, @type, @name, @pre_creation_command
    while (@@fetch_status <> -1)
    begin
        if @type = 0x40 -- Schema view articles, may require index scripting
        begin
            select @taskload = 0
            -- See if a custom creation script will be used 
            if @schema_option = 0 and @creation_script is not null and
               @creation_script <> N''
            begin
                -- Script generation
                select @taskload = @copyfilecost + @scriptproccost
                insert #workload_breakdown values (@name, @artid, 12, @taskload)
                -- Cost of adding commands for the custom script
                select @addcommandstotal = @addcommandstotal + @addcommandcost
                -- Increment scripts counter
                select @numscripts = @numscripts + 1
                -- No need to worry about index script
            end
            else
            begin
                -- Script generation 
                select @taskload = @dmoscriptcost + @scriptproccost
                -- Must script out schema script
                insert #workload_breakdown values (@name, @artid, 1, @taskload)
                -- Cost of adding the command
                select @addcommandstotal = @addcommandstotal + @addcommandcost
                -- Increment scripts counter
                select @numscripts = @numscripts + 1

                -- Index script?
                if (@schema_option & 0x50) <> 0x0
                begin
                    -- Cost of script generation
                    select @taskload = @dmoscriptcost + @scriptproccost
                    insert #workload_breakdown values (@name, @artid, 13, @taskload)
                    -- Cost of adding command for the script
                    select @addcommandstotal = @addcommandstotal + @addcommandcost
                    -- Increment scripts counter
                    select @numscripts = @numscripts + 1
                end

                -- Trigger script?
                if (@schema_option & 0x100) <> 0x0
                begin
                    -- Cost of script generation
                    select @taskload = @dmoscriptcost + @scriptproccost
                    insert #workload_breakdown values (@name, @artid, 2, @taskload)
                    -- Cost of adding command for the script
                    select @addcommandstotal = @addcommandstotal + @addcommandcost
                    -- Increment scripts counter
                    select @numscripts = @numscripts + 1
                end 

                -- XProp script?
                if (@schema_option & 0x2000) <> 0x0
                begin
                    -- Cost of script generation 
                    select @taskload = @dmoscriptcost + @scriptproccost
                    insert #workload_breakdown values (@name, @artid, 3, @taskload)
                    -- Cost of adding command
                    select @addcommandstotal = @addcommandstotal + @addcommandcost
                    -- Increment scripts counter
                    select @numscripts = @numscripts + 1
                end
            end        
        end
        else if @type in (0x08, 0x18, 0x20, 0x80) -- Regular schema or proc exec articles 
        begin
            select @taskload = 0
            -- See if a custom creation script will be used 
            if @schema_option = 0 and @creation_script is not null and
               @creation_script <> N''
            begin
                -- Script generation
                select @taskload = @copyfilecost + @scriptproccost
                insert #workload_breakdown values (@name, @artid, 12, @taskload)
                -- Cost of adding commands for the custom script
                select @addcommandstotal = @addcommandstotal + @addcommandcost
                -- Increment scripts counter
                select @numscripts = @numscripts + 1
            end
            else 
            begin
                -- Script generation 
                select @taskload = @dmoscriptcost + @scriptproccost
                -- Must script out schema script
                insert #workload_breakdown values (@name, @artid, 1, @taskload)
                -- Cost of adding the command
                select @addcommandstotal = @addcommandstotal + @addcommandcost
                -- Increment scripts counter
                select @numscripts = @numscripts + 1

                -- XProp script?
                if (@schema_option & 0x2000) <> 0x0
                begin
                    -- Cost of script generation 
                    select @taskload = @dmoscriptcost + @scriptproccost
                    insert #workload_breakdown values (@name, @artid, 3, @taskload)
                    -- Cost of adding command
                    select @addcommandstotal = @addcommandstotal + @addcommandcost
                -- Increment scripts counter
                select @numscripts = @numscripts + 1
                end
            end
        end
        else -- Log based articles, requires bcp
        begin
           select @taskload = 0
           -- See if a custom creation script will be used 
            if @schema_option = 0 and @creation_script is not null and
               @creation_script <> N''
            begin
                -- Script generation
                select @taskload = @copyfilecost + @scriptproccost
                insert #workload_breakdown values (@name, @artid, 12, @taskload)
                -- Cost of adding commands for the custom script
                select @addcommandstotal = @addcommandstotal + @addcommandcost
                -- Increment scripts counter
                select @numscripts = @numscripts + 1
                -- No need to worry about index script
            end
            else
            begin
                -- Script generation 
                select @taskload = @dmoscriptcost + @scriptproccost
                -- Must script out schema script
                insert #workload_breakdown values (@name, @artid, 1, @taskload)
                -- Cost of adding the command
                select @addcommandstotal = @addcommandstotal + @addcommandcost
                -- Increment scripts counter
                select @numscripts = @numscripts + 1

                -- Index script is not optional for logbased articles
                -- Cost of script generation
                select @taskload = @dmoscriptcost + @scriptproccost
                insert #workload_breakdown values (@name, @artid, 13, @taskload)
                -- Cost of adding command for the script
                select @addcommandstotal = @addcommandstotal + @addcommandcost
                -- Increment scripts counter
                select @numscripts = @numscripts + 1

                -- Constraint script?
                if (@schema_option & 0xf00) <> 0x0
                begin
                    -- Cost of script generation
                    select @taskload = @dmoscriptcost + @scriptproccost
                    insert #workload_breakdown values (@name, @artid, 9, @taskload)
                    -- Cost of adding command for the script
                    select @addcommandstotal = @addcommandstotal + @addcommandcost
                    -- Increment scripts counter
                    select @numscripts = @numscripts + 1
                end
    
                -- Trigger script?
                if (@schema_option & 0x100) <> 0x0
                begin
                    -- Cost of script generation
                    select @taskload = @dmoscriptcost + @scriptproccost
                    insert #workload_breakdown values (@name, @artid, 2, @taskload)
                    -- Cost of adding command for the script
                    select @addcommandstotal = @addcommandstotal + @addcommandcost
                    -- Increment scripts counter
                    select @numscripts = @numscripts + 1
                end 
        
                -- XProp script?
                if (@schema_option & 0x2000) <> 0x0
                begin
                    -- Cost of script generation 
                    select @taskload = @dmoscriptcost + @scriptproccost
                    insert #workload_breakdown values (@name, @artid, 3, @taskload)
                    -- Cost of adding command
                    select @addcommandstotal = @addcommandstotal + @addcommandcost
                    -- Increment scripts counter
                    select @numscripts = @numscripts + 1
                end
            end        
        
            -- Get fast row count from sysindexes for bcp op estimation
            select @rowcount = 
                    case when isnull(rowcnt,0) > @maxint then @maxint / 10000
                         when isnull(rowcnt,0) > @maxint / 10000 then rowcnt / 10000
                         else isnull(rowcnt,0)
                    end
               from sysindexes 
             where id = @objid 
               and indid in (0,1)

            -- Increment the total row count
            select @totalrowcount = @totalrowcount + @rowcount        

            -- BCP computation            
            -- Bcp file generation
            select @taskload = @bcpoverhead + (@rowcount * @totalbcpcostper100) / 100 
            insert #workload_breakdown values (@name, @artid, 4, @taskload)
        end    

        -- System pre-snapshot script required?
        if @type in (0x40, 0x80) or ((@type & 0x1)<>0 and @pre_creation_command =  3)
        begin
            select @needsysprescript = 1
            -- Incrememt the number of pre-script command counter
            select @numprescriptcommands = @numprescriptcommands + 1
        end

        -- Increment the article count
        select @numarticles = @numarticles + 1

        fetch hCarticles into @artid, @creation_script, @objid, @schema_option, @type, @name, @pre_creation_command
    end
    
    -- System pre-snapshot script processing cost 
    if @needsysprescript = 1
    begin
        select @taskload = @syspreoverhead + 
                (@numprescriptcommands * @syspreper2commands) / 2 +
               @scriptproccost
        insert #workload_breakdown values (N'', -1, 7, @taskload)         
        select @numscripts = @numscripts + 1
    end

    -- Cost of flushing folder for the scripts 
    if @compress_snapshot = 1
    begin
        select @taskload = (@numscripts * @flushper2scripts) / 2
        insert #workload_breakdown values (N'', -1, 8, @taskload) 
    end

    -- Synctran commands cost?
        

    -- Subscription activation cost?


    -- Cost for flushing the cabinet
    if @compress_snapshot = 1
    begin
        select @taskload = (@numscripts * @flushcabper5scripts / 5) +
                           (@totalrowcount * @flushcabper500bcprows / 500)
        insert #workload_breakdown values (N'', -1, 14, @taskload)
    end

    -- Add the total add commands cost
    insert #workload_breakdown values (N'', -1, 6, @addcommandstotal)

    -- Compute the total workload and put that in the 
    -- workload break down
    select @bigtaskload = sum(convert(bigint,taskload)) from #workload_breakdown
    select @taskload =
            case when @bigtaskload > @maxint then @maxint
                 else @bigtaskload
            end
    insert #workload_breakdown values (N'', -1, 0, @taskload) 

    select name, taskid, taskload from #workload_breakdown order by artid, taskid asc
    drop table #workload_breakdown
    return 0

Failure:
    if @table_created = 1
       drop table #workload_breakdown
    return 1

end
go
exec dbo.sp_MS_marksystemobject sp_MSestimatesnapshotworkload
grant execute on dbo.sp_MSestimatesnapshotworkload to public

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

raiserror(15339,-1,-1,'sp_MSestimatemergesnapshotworkload')
GO
create proc sp_MSestimatemergesnapshotworkload (
    @publication sysname
    )
as
begin
    set nocount on

    declare @taskload                       int
    declare @bigtaskload                    bigint
    declare @table_created                  bit
    declare @retcode                        int

    -- constant
    declare @zerouuid                       uniqueidentifier
    select @zerouuid = convert(uniqueidentifier, N'{00000000-0000-0000-0000-000000000000}')
    -- Publication info
    declare @pubid                          uniqueidentifier
    declare @compress_snapshot              bit
    declare @snapshot_in_defaultfolder      bit
    declare @alt_snapshot_folder            nvarchar(255)
    declare @enabled_for_internet           bit
    declare @ftp_password                   nvarchar(524) 
    declare @ftp_subdirectory               nvarchar(255)
    declare @pre_snapshot_script            nvarchar(255)
    declare @post_snapshot_script           nvarchar(255)    
    declare @dynamic_filters                bit

    -- Per publication summary stats
    declare @numarticles                    int
    declare @totalrowcount                  int   
    declare @needsysprescript               bit
    declare @copysnapshot                   bit
    declare @deletefiles                    bit
    declare @scriptproccost                 int
    declare @addcommandstotal               int
    declare @numscripts                     int
    declare @totalbcpcostper100             int
    declare @numprescriptcommands           int 
    declare @numunsyncedarticles            int
    declare @numtablearticles               int
    declare @systablesrowcount              int
    declare @maxint                         int

    select @numarticles = 0
    select @totalrowcount = 0
    select @needsysprescript = 0
    select @copysnapshot = 0
    select @deletefiles = 0
    select @scriptproccost = 0
    select @deletefiles = 0
    select @addcommandstotal = 0
    select @numscripts = 0
    select @totalbcpcostper100 = 0
    select @numprescriptcommands = 0
    select @numunsyncedarticles = 0
    select @numtablearticles = 0
    select @systablesrowcount = 0
    select @maxint = 2147483647

    -- Per article variables
    declare @artid                          uniqueidentifier
    declare @schema_option                  int
    declare @creation_script                nvarchar(255)
    declare @type                           tinyint
    declare @objid                          int
    declare @rowcount                       int
    declare @name                           sysname
    declare @status                         int


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

    select @table_created = 0

    select @pubid = null
    select @compress_snapshot = 0
    select @snapshot_in_defaultfolder = 0
    select @alt_snapshot_folder = null
    select @enabled_for_internet = 0
    select @ftp_password = null
    select @ftp_subdirectory = null
    select @pre_snapshot_script = null
    select @post_snapshot_script = null
    -- Validate publication and retrieve publication information 
    select @pubid = pubid, 
           @compress_snapshot = compress_snapshot,
           @snapshot_in_defaultfolder = snapshot_in_defaultfolder,
           @alt_snapshot_folder = alt_snapshot_folder,
           @enabled_for_internet = enabled_for_internet,
           @ftp_password = ftp_password,
           @ftp_subdirectory = ftp_subdirectory,
           @pre_snapshot_script = pre_snapshot_script,
           @post_snapshot_script = post_snapshot_script,
           @dynamic_filters = dynamic_filters
           from sysmergepublications
     where name = @publication
       and upper(@@servername) = publisher
       and publisher_db = db_name()
    if @pubid = null
    begin   
        raiserror(20026, 11, -1, @publication)
        return (1)
    end

    -- An artid of -1 means the task is a per publication
    -- task

    -- Task id mapping - This is the list of task whose completion can be 
    -- reported by the snapshot agent
    -- 0 - Total workload (not really a task)
    -- 1 - Schema script generation
    -- 2 - Trigger script generation 
    -- 3 - XProp script generation
    -- 4 - Bcp file generation
    -- 5 - Activating subscription (estimate number of subscriptions?)
    -- 6 - Adding snapshot commands
    -- 7 - System pre-script generation
    -- 8 - Flushing folder for scripts
    -- 9 - Constraint script generation
    -- 10 - Copying pre-script
    -- 11 - Copying post-script
    -- 12 - Copying custom schema creation script
    -- 13 - Index script generation
    -- 14 - Flushing the cabinet
    -- 15 - Adding rowguid column
    -- 16 - Setting article procs
    -- 17 - Adding merge triggers
    -- 18 - Generating system table scripts
    -- 19 - Generating system table bcp files
    -- 20 - Making publication generation
    -- 21 - Generating and setting conflict script
    -- 22 - Generating publication views
    
    -- Weights and overheads
    -- DMO Script generation - 7/script
    -- BCP 5 overhead + 5/100 rows
    -- Flush folder for bcp 2/100 rows
    -- Delete file 2
    -- System pre-snapshot script 1 overhead + 0.5/pre-creation command
    -- Copying user file 2
    -- Adding file to cabinet 1 for scripts and 1/100 rows in bcp file
    -- Loading article info 3
    -- Delete bcp file 2/100rows 
    -- Copy bcp file  2/100rows  
    -- Flush scripts folder 1/2 scripts
    -- Flushing cabinet per 5 scripts 1
    -- Flushing cabinet per 500 rows 1
    -- Script execution 3
    -- Adding rowguid columns 20/100 rows
    -- Adding merge triggers 7
    -- Setting article procs 5

    -- Here is the list of cost factors 
    declare @addcommandcost     int
    declare @dmoscriptcost      int
    declare @bcpoverhead        int
    declare @bcpcostper100      int
    declare @flushper100        int
    declare @flushperscript     int
    declare @delfilecost        int
    declare @syspreoverhead     int
    declare @syspreper2commands int
    declare @addbcptocabper100  int
    declare @addscripttocab     int
    declare @copyfilecost       int
    declare @loadartinfo        int    
    declare @deletebcpper100    int
    declare @copybcpper100      int
    declare @flushper2scripts   int
    declare @flushcabper5scripts int
    declare @flushcabper500bcprows int
    declare @scriptexeccost int
    declare @rowguidper100 int
    declare @addmergetriggers int
    declare @setartprocs int
    declare @pubviewperarticle int
    declare @genperarticle int

    select @addcommandcost = 2
    select @dmoscriptcost = 7
    select @bcpcostper100 = 20
    select @bcpoverhead = 2
    select @flushper100 = 2
    select @flushperscript = 3
    select @delfilecost = 2
    select @syspreoverhead = 1
    select @syspreper2commands = 1
    select @addbcptocabper100 = 1
    select @addscripttocab = 1
    select @copyfilecost = 2
    select @loadartinfo = 3    
    select @deletebcpper100 = 1
    select @copybcpper100 = 2
    select @flushper2scripts = 1
    select @flushcabper5scripts = 1
    select @flushcabper500bcprows = 1
    select @scriptexeccost = 3
    select @rowguidper100 = 20
    select @addmergetriggers = 3
    select @pubviewperarticle = 2
    select @genperarticle = 2
    select @setartprocs = 5

    create table #workload_breakdown
    (
        name        sysname collate database_default,
        artid       uniqueidentifier,
        taskid      int,
        taskload    int
    )
    if @@error <> 0
        goto Failure

    select @table_created = 1

    -- Per publication work load estimate that can be done using publication
    -- properties alone
    
    -- Adding snapshot header commands
    
    -- Snapshot header begins + Snapshot header ends +
    -- directory command + Snapshot trailer command = 3 * @addcommandcost
    select @addcommandstotal = 4
    
    -- Alternate snapshot folder = 1 * @addcommandcost
    if @alt_snapshot_folder is not null and
       @alt_snapshot_folder <> N'' and
       @snapshot_in_defaultfolder = 1
    begin
        select @addcommandstotal = @addcommandstotal + @addcommandcost
    end

    -- Ftp commands
    if @enabled_for_internet = 1
    begin
        -- ftp_address and ftp_port must be there +=2 * @addcommandcost
        select @addcommandstotal = @addcommandstotal + 2 * @addcommandcost

        -- ftp_password += 1 * @addcommandcost
        if @ftp_password is not null and
           @ftp_password <> N''
        begin
            select @addcommandstotal = @addcommandstotal + @addcommandcost
        end
        
        -- ftp_subdirectory += 1 * @addcommandcost
        if @ftp_subdirectory is not null and
           @ftp_subdirectory <> N''
        begin
            select @addcommandstotal = @addcommandstotal + 1 * @addcommandcost
        end
    end

    -- Compressed archive path
    if @compress_snapshot = 1
    begin
        select @addcommandstotal = @addcommandstotal + 1 * @addcommandcost
    end    

    -- Snapshot trailer command
    select @addcommandstotal = @addcommandstotal +  @addcommandcost

    -- Need to copy files?
    if @alt_snapshot_folder is not null and @alt_snapshot_folder <> N''
       and @snapshot_in_defaultfolder = 1 and @compress_snapshot = 0
    begin
        select @copysnapshot = 1
    end

    -- Compute per-script file post processing cost and bcp cost per 100 rows
    -- Need to delete files?
    if (@alt_snapshot_folder is null or @alt_snapshot_folder <> N''
       or @snapshot_in_defaultfolder = 0) and @compress_snapshot = 1
    begin
        select @deletefiles = 1
    end

    select @totalbcpcostper100 = @bcpcostper100
    -- Copy file?
    if @copysnapshot = 1
    begin
        select @scriptproccost = @scriptproccost + @copyfilecost
        select @totalbcpcostper100 = @totalbcpcostper100 + @copybcpper100
    end

    -- Add file to cabinet?
    if @compress_snapshot = 1
    begin
        select @scriptproccost = @scriptproccost + @addscripttocab 
        select @totalbcpcostper100 = @totalbcpcostper100 + @addbcptocabper100
    end

    -- Delete file?
    if @deletefiles = 1
    begin 
        select @scriptproccost = @scriptproccost + @delfilecost
        select @totalbcpcostper100 = @totalbcpcostper100 + @deletebcpper100
    end

    -- Pre/Post-snapshot scripts, has to be computed after the 
    -- the per-script post-processing cost is calculated 
    if @pre_snapshot_script is not null and
       @pre_snapshot_script <> N''
    begin
        -- Cost of copying the script file
        select @taskload = @copyfilecost + @scriptproccost
        insert #workload_breakdown values (N'', @zerouuid, 10, @taskload)
        -- Cost of adding the pre-snapshot command
        select @addcommandstotal = @addcommandstotal + @addcommandcost
        -- Increment scripts counter
        select @numscripts = @numscripts + 1
    end

    if @post_snapshot_script is not null and
       @post_snapshot_script <> N''
    begin
        -- Cost of copying the script file
        select @taskload = @copyfilecost + @scriptproccost
        insert #workload_breakdown values (N'', @zerouuid, 11, @taskload)
        -- Cost of adding the post-snapshot command
        select @addcommandstotal = @addcommandstotal + @addcommandcost
        -- Increment scripts counter
        select @numscripts = @numscripts + 1
    end
           
    -- Estimate the break down of per article tasks 
    declare hCarticles cursor local fast_forward
    for select artid, creation_script, objid, convert(int, schema_option), type, name,
               status  
          from sysmergeextendedarticlesview where pubid = @pubid 

    open hCarticles
   
    fetch hCarticles into @artid, @creation_script, @objid, @schema_option, @type, @name,
        @status
    while (@@fetch_status <> -1)
    begin
        if @type = 0x40 -- Schema view articles, may require index scripting
        begin
            select @taskload = 0
            -- See if a custom creation script will be used 
            if @schema_option = 0 and @creation_script is not null and
               @creation_script <> N''
            begin
                -- Script generation
                select @taskload = @copyfilecost + @scriptproccost
                insert #workload_breakdown values (@name, @artid, 12, @taskload)
                -- Cost of adding commands for the custom script
                select @addcommandstotal = @addcommandstotal + @addcommandcost
                -- Increment scripts counter
                select @numscripts = @numscripts + 1
                -- No need to worry about index script
            end
            else
            begin
                -- Script generation 
                select @taskload = @dmoscriptcost + @scriptproccost
                -- Must script out schema script
                insert #workload_breakdown values (@name, @artid, 1, @taskload)
                -- Cost of adding the command
                select @addcommandstotal = @addcommandstotal + @addcommandcost
                -- Increment scripts counter
                select @numscripts = @numscripts + 1

                -- DRI script containing the view's indexes?
                if (@schema_option & 0x50) <> 0x0
                begin
                    -- Cost of script generation
                    select @taskload = @dmoscriptcost + @scriptproccost
                    insert #workload_breakdown values (@name, @artid, 9, @taskload)
                    -- Cost of adding command for the script
                    select @addcommandstotal = @addcommandstotal + @addcommandcost
                    -- Increment scripts counter
                    select @numscripts = @numscripts + 1
                end

                -- Trigger script?
                if (@schema_option & 0x100) <> 0x0
                begin
                    -- Cost of script generation
                    select @taskload = @dmoscriptcost + @scriptproccost
                    insert #workload_breakdown values (@name, @artid, 2, @taskload)
                    -- Cost of adding command for the script
                    select @addcommandstotal = @addcommandstotal + @addcommandcost
                    -- Increment scripts counter
                    select @numscripts = @numscripts + 1
                end 

                -- XProp script?
                if (@schema_option & 0x2000) <> 0x0
                begin
                    -- Cost of script generation 
                    select @taskload = @dmoscriptcost + @scriptproccost
                    insert #workload_breakdown values (@name, @artid, 3, @taskload)
                    -- Cost of adding command
                    select @addcommandstotal = @addcommandstotal + @addcommandcost
                    -- Increment scripts counter
                    select @numscripts = @numscripts + 1
                end
            end        
        end
        else if @type in (0x08, 0x18, 0x20, 0x80) -- Regular schema articles 
        begin
            select @taskload = 0
            -- See if a custom creation script will be used 
            if @schema_option = 0 and @creation_script is not null and
               @creation_script <> N''
            begin
                -- Script generation
                select @taskload = @copyfilecost + @scriptproccost
                insert #workload_breakdown values (@name, @artid, 12, @taskload)
                -- Cost of adding commands for the custom script
                select @addcommandstotal = @addcommandstotal + @addcommandcost
                -- Increment scripts counter
                select @numscripts = @numscripts + 1
            end
            else 
            begin
                -- Script generation 
                select @taskload = @dmoscriptcost + @scriptproccost
                -- Must script out schema script
                insert #workload_breakdown values (@name, @artid, 1, @taskload)
                -- Cost of adding the command
                select @addcommandstotal = @addcommandstotal + @addcommandcost
                -- Increment scripts counter
                select @numscripts = @numscripts + 1

                -- XProp script?
                if (@schema_option & 0x2000) <> 0x0
                begin
                    -- Cost of script generation 
                    select @taskload = @dmoscriptcost + @scriptproccost
                    insert #workload_breakdown values (@name, @artid, 3, @taskload)
                    -- Cost of adding command
                    select @addcommandstotal = @addcommandstotal + @addcommandcost
                -- Increment scripts counter
                select @numscripts = @numscripts + 1
                end
            end
        end
        else -- Merge table articles, requires bcp
        begin
           select @taskload = 0
           -- See if a custom creation script will be used 
            if @schema_option = 0 and @creation_script is not null and
               @creation_script <> N''
            begin
                -- Script generation
                select @taskload = @copyfilecost + @scriptproccost
                insert #workload_breakdown values (@name, @artid, 12, @taskload)
                -- Cost of adding commands for the custom script
                select @addcommandstotal = @addcommandstotal + @addcommandcost
                -- Increment scripts counter
                select @numscripts = @numscripts + 1
                -- No need to worry about index script
            end
            else
            begin
                -- Script generation 
                select @taskload = @dmoscriptcost + @scriptproccost
                -- Must script out schema script
                insert #workload_breakdown values (@name, @artid, 1, @taskload)
                -- Cost of adding the command
                select @addcommandstotal = @addcommandstotal + @addcommandcost
                -- Increment scripts counter
                select @numscripts = @numscripts + 1

                -- Constraint script is not optional for merge table article
                -- Cost of script generation
                select @taskload = @dmoscriptcost + @scriptproccost
                insert #workload_breakdown values (@name, @artid, 9, @taskload)
                -- Cost of adding command for the script
                select @addcommandstotal = @addcommandstotal + @addcommandcost
                -- Increment scripts counter
                select @numscripts = @numscripts + 1
    
                -- Trigger script?
                if (@schema_option & 0x100) <> 0x0
                begin
                    -- Cost of script generation
                    select @taskload = @dmoscriptcost + @scriptproccost
                    insert #workload_breakdown values (@name, @artid, 2, @taskload)
                    -- Cost of adding command for the script
                    select @addcommandstotal = @addcommandstotal + @addcommandcost
                    -- Increment scripts counter
                    select @numscripts = @numscripts + 1
                end 
        
                -- XProp script?
                if (@schema_option & 0x2000) <> 0x0
                begin
                    -- Cost of script generation 
                    select @taskload = @dmoscriptcost + @scriptproccost
                    insert #workload_breakdown values (@name, @artid, 3, @taskload)
                    -- Cost of adding command
                    select @addcommandstotal = @addcommandstotal + @addcommandcost
                    -- Increment scripts counter
                    select @numscripts = @numscripts + 1
                end
            end        

            -- Conflict script generation and setting
            -- Cost of script generation and execution 
            select @taskload = @dmoscriptcost + @scriptexeccost + @scriptproccost
            insert #workload_breakdown values (@name, @artid, 21, @taskload)

            -- Get fast row count from sysindexes for bcp op estimation
            select @rowcount = 
                    case when isnull(rowcnt,0) > @maxint then @maxint / 10000
                         when isnull(rowcnt,0) > @maxint / 10000 then rowcnt / 10000
                         else isnull(rowcnt,0)
                    end
              from sysindexes 
             where id = @objid and indid in (0,1)

            -- For unsynced articles, we need to 
            -- 1) Add rowguid column 
            -- 2) Set article procs
            -- 3) Add merge triggers
            if @status = 1
            begin
                -- Add rowguid column - use article row count to estimate the cost
                select @taskload = (@rowcount * @rowguidper100) / 100
                insert #workload_breakdown values (@name, @artid, 15, @taskload) 
      
                -- Set article procs
                select @taskload = @setartprocs
                insert #workload_breakdown values (@name, @artid, 16, @taskload)

                -- Add merge triggers    
                select @taskload = @addmergetriggers 
                insert #workload_breakdown values (@name, @artid, 17, @taskload)

                -- Increment the number of unsynced articles
                select @numunsyncedarticles = @numunsyncedarticles + 1
            end

            -- Increment the total row count
            select @totalrowcount = @totalrowcount + @rowcount        

            -- BCP computation            
            -- Bcp file generation
            select @taskload = @bcpoverhead + (@rowcount * @totalbcpcostper100) / 100 
            insert #workload_breakdown values (@name, @artid, 4, @taskload)

            -- Increment the table articles counter
            select @numtablearticles = @numtablearticles + 1

        end    

        -- System pre-snapshot script required?
        if @type in (0x40, 0x80)
        begin
            select @needsysprescript = 1
            -- Incrememt the number of pre-script command counter
            select @numprescriptcommands = @numprescriptcommands + 1
        end

        -- Increment the article count
        select @numarticles = @numarticles + 1

        fetch hCarticles into @artid, @creation_script, @objid, @schema_option, @type, @name,
            @status
    end
        
    -- The cost of making publication views depends upon the 
    -- number of unsynced articles
    if @numunsyncedarticles <> 0
    begin
        select @taskload = @numunsyncedarticles * @pubviewperarticle 
        insert #workload_breakdown values (N'', @zerouuid, 22, @taskload) 
    end

    -- Make publication generation - roughly proportional to the number of table
    -- articles we have
    select @taskload = @numtablearticles * @genperarticle
    insert #workload_breakdown values (N'', @zerouuid, 20, @taskload)

    -- Merge system tables processing 
    -- Script files
    select @taskload = 4 * (@dmoscriptcost + @scriptproccost)
    insert #workload_breakdown values (N'', @zerouuid, 18, @taskload)

    -- Cost of adding the commands
    select @addcommandcost = @addcommandcost + 4

    -- Bcp files - Generated only when the publication is not enabled for
    -- dynamic filters
    if @dynamic_filters = 0
    begin
        if exists (select * from sysobjects where name = N'MSmerge_contents')
        begin
            select @rowcount = 
                    case when isnull(rowcnt,0) > @maxint then @maxint / 10000
                         when isnull(rowcnt,0) > @maxint / 10000 then rowcnt / 10000
                         else isnull(rowcnt,0)       
                    end
              from sysindexes 
             where id = object_id('dbo.MSmerge_contents') 
               and indid in (0,1)
            select @systablesrowcount = @systablesrowcount + @rowcount
        end
        if exists (select * from sysobjects where name = N'MSmerge_tombstones')
        begin
            select @rowcount = 
                    case when isnull(rowcnt,0) > @maxint then @maxint / 10000
                         when isnull(rowcnt,0) > @maxint / 10000 then rowcnt / 10000
                         else isnull(rowcnt,0)       
                    end
              from sysindexes 
             where id = object_id('dbo.MSmerge_tombstoness') 
               and indid in (0,1)
            select @systablesrowcount = @systablesrowcount + @rowcount
        end
        if exists (select * from sysobjects where name = N'MSmerge_genhistory')
        begin
            select @rowcount = 
                    case when isnull(rowcnt,0) > @maxint then @maxint / 10000
                         when isnull(rowcnt,0) > @maxint / 10000 then rowcnt / 10000
                         else isnull(rowcnt,0)       
                    end
              from sysindexes 
             where id = object_id('dbo.MSmerge_genhistory') 
               and indid in (0,1)
            select @systablesrowcount = @systablesrowcount + @rowcount
        end
    end
    if exists (select * from sysobjects where name = N'sysmergesubsetfilters')
    begin
        select @rowcount = count(*) from sysmergesubsetfilters
        select @systablesrowcount = @systablesrowcount + @rowcount 
    end

    select @taskload = (4 * @bcpoverhead) + (@systablesrowcount * @totalbcpcostper100) / 100 
    insert #workload_breakdown values (N'', @zerouuid, 19, @taskload)
    select @totalrowcount = @totalrowcount + @systablesrowcount

    -- System pre-snapshot script processing cost 
    if @needsysprescript = 1
    begin
        select @taskload = @syspreoverhead + 
                (@numprescriptcommands * @syspreper2commands) / 2 +
               @scriptproccost
        insert #workload_breakdown values (N'', @zerouuid, 7, @taskload)         
        select @numscripts = @numscripts + 1
    end

    -- Cost of flushing folder for the scripts 
    if @compress_snapshot = 1
    begin
        select @taskload = (@numscripts * @flushper2scripts) / 2
        insert #workload_breakdown values (N'', @zerouuid, 8, @taskload) 
    end

    -- Cost for flushing the cabinet
    if @compress_snapshot = 1
    begin
        select @taskload = (@numscripts * @flushcabper5scripts / 5) +
                           (@totalrowcount * @flushcabper500bcprows / 500)
        insert #workload_breakdown values (N'', @zerouuid, 14, @taskload)
    end

    -- Add the total add commands cost
    insert #workload_breakdown values (N'', @zerouuid, 6, @addcommandstotal)

    -- Compute the total workload and put that in the 
    -- workload break down
    select @bigtaskload = sum(convert(bigint,taskload)) from #workload_breakdown
    select @taskload =
            case when @bigtaskload > @maxint then @maxint
            else @bigtaskload
            end
    insert #workload_breakdown values (N'', @zerouuid, 0, @taskload) 

    select name, taskid, taskload from #workload_breakdown order by artid, taskid asc
    drop table #workload_breakdown
    return 0

Failure:
    if @table_created = 1
       drop table #workload_breakdown
    return 1
end
go
exec dbo.sp_MS_marksystemobject sp_MSestimatemergesnapshotworkload
grant execute on dbo.sp_MSestimatemergesnapshotworkload to public

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

raiserror('Creating procedure sp_addsynctriggers', 0,1)
go

CREATE PROCEDURE sp_addsynctriggers (
	@sub_table       sysname,			-- table name 
	@sub_table_owner sysname,			-- table owner
	@publisher      sysname,            -- publishing server name
	@publisher_db   sysname,            -- publishing database name. If NULL then same as current db
	@publication	sysname,			-- publication name.
	@ins_proc       sysname,
	@upd_proc       sysname,
	@del_proc       sysname,
	@cftproc		sysname,
	@proc_owner		sysname,
	@identity_col   sysname = 'NULL', 
	@ts_col         sysname = 'NULL',
	@filter_clause  nvarchar(4000) = 'NULL',
	@primary_key_bitmap  varbinary(4000),
	@identity_support bit = 0,
	@independent_agent bit = 0
	,@distributor	sysname				-- distribution server name
)
AS
BEGIN
    set nocount on

	declare @db						sysname
			,@trigname				sysname
			,@ins_trig				sysname
			,@upd_trig				sysname
			,@del_trig				sysname
			,@qual_ins_trig			nvarchar(540)
			,@qual_upd_trig			nvarchar(540)
			,@qual_del_trig			nvarchar(540)
			,@dbname				sysname
			,@ccols					int
			,@cnt					int
			,@retcode				int
			,@cmd					nvarchar(4000)
			,@merge_pub_object_bit	int
			,@synctran_bit			int
			,@object_id				int
			,@bitmap_str				varchar(8000)
			,@constraint_name			sysname
			,@quoted_name			nvarchar(540)
			,@qualname				nvarchar(540)
			,@loctrancount 			int

	select 	@merge_pub_object_bit 	= 128
			,@synctran_bit			= 256

	--  Security Check
    EXEC @retcode = dbo.sp_MSreplcheck_subscribe
    IF @@ERROR <> 0 or @retcode <> 0
	RETURN(1)


    -- Dist Agent executes this sproc with 'implicit transasctions on'.
    -- We take care of our own transactions boundaries to get out of tran 
	set implicit_transactions off
	select @loctrancount = @@trancount
    while @@trancount > 0 commit tran

	-- check valid server and database setting
	-- 1. nested trigger have to be on
	if exists (select * from master..sysconfigures where config = 115 and value = 0)
	begin
        raiserror(21081, 16, 1)
        return (1)
	end

	-- 2. db option: recursive trigger have to be off
	if DATABASEPROPERTY(db_name(), N'IsRecursiveTriggersEnabled') <> 0
	begin
        raiserror(21082, 16, 1)
        return (1)
	end	
	
	-- 2. db compatibility level have to be 7.0
	if exists (select * from master..sysdatabases where dbid = db_id() and 
		cmptlevel < 70)
	begin
        raiserror(21083, 16, 1)
        return (1)
	end	
	
	if lower(@sub_table_owner) = N'null'
		select @qualname = QUOTENAME(@sub_table)		
	else
		select @qualname = QUOTENAME(@sub_table_owner) + N'.' + QUOTENAME(@sub_table)

	-- Verify that table exists 
	select @object_id = object_id (@qualname)
	if @object_id is null
    begin
        raiserror(20507, 16, 1, @qualname, 'sp_addsynctriggers')
        return (1)
    end

	-- Add default to timestamp, identity and version guid column
	if OBJECTPROPERTY(@object_id, 'tablehastimestamp') <> 1
    begin
	    select @constraint_name = 'MSrepl_synctran_ts_default_' + convert(nvarchar(10), @object_id)
	    select @quoted_name = quotename(@ts_col)
	    if @ts_col is not null and @ts_col not in ('null','NULL') and not exists
		    (select * from sysobjects where name = @constraint_name)
	    begin
		    exec ('alter table ' + @qualname + 
			    ' add constraint ' + @constraint_name + 
			    ' default 0 for ' + @quoted_name )
		    if @@ERROR<>0 return 1
	    end
    end
	
    if OBJECTPROPERTY(@object_id, 'tablehasidentity') <> 1
    begin
	    select @constraint_name = 'MSrepl_synctran_identity_default_' + convert(nvarchar(10), @object_id)
	    select @quoted_name = quotename(@identity_col)
	    if @identity_col is not null and @identity_col not in ('null','NULL') and not exists
		    (select * from sysobjects where name = @constraint_name)
	    begin
		    exec ('alter table ' + @qualname + 
			    ' add constraint ' + @constraint_name + 
			    ' default 0 for ' + @quoted_name )
		    if @@ERROR<>0 return 1
	    end
    end

	-- The default constraint is transfered with snapshot already for native publication.
	-- Need to detect to see if default constraint already there.
	select @quoted_name = 'msrepl_tran_version'
    declare @colid int
    select @colid = colid from syscolumns where 
        id = @object_id and
        name = @quoted_name
	if not exists (select * from sysconstraints where
        id = @object_id and 
        colid = @colid and
		status & 5 = 5) -- default
    begin 
	    select @constraint_name = 'MSrepl_tran_version_default_' + convert(nvarchar(10), @object_id)
		exec ('alter table ' + @qualname + 
			' add constraint ' + @constraint_name + 
			' default newid() for ' + @quoted_name )
		if @@ERROR<>0 return 1
    end

	-- If MSsubscription_properties table does not exists, create one.
	if (LOWER(@cftproc) = 'null')
	begin
		exec @retcode = dbo.sp_MScreate_sub_tables
			@tran_sub_table = 0,
			@property_table = 1,
			@sqlqueue_table = 0
	end
	else
	begin
		exec @retcode = dbo.sp_MScreate_sub_tables
			@tran_sub_table = 0,
			@property_table = 1,
			@sqlqueue_table = 1
	end
	IF @@ERROR <> 0 or @retcode <> 0
		RETURN(1)

	-- If no entry in MSsubscription_properties for this publication, add one.
	IF NOT EXISTS (select * from MSsubscription_properties 
            where UPPER(publisher) = UPPER(@publisher)
				and publisher_db =  @publisher_db
				and publication = @publication) 
	BEGIN
		-- Use status rpc for local publisher
		declare @security_mode int
		declare @login sysname
		if UPPER(@@servername) = UPPER(@publisher)
		begin
			select @security_mode = 2
		end
		else
		begin
			select @security_mode = 0
			select @login = 'sa'
		end

		exec @retcode = dbo.sp_link_publication
			@publisher = @publisher,
			@publisher_db = @publisher_db,
			@publication = @publication,
			@security_mode = @security_mode,
			@login = @login,
			@password = NULL,
			@distributor = @distributor
		IF @@ERROR <> 0 or @retcode <> 0
			RETURN(1)
	END
	  
    if exists (select * from sysobjects where 
		replinfo & @merge_pub_object_bit <> 0 and
		id = @object_id)
	begin
        raiserror(21063, 16, 1, @qualname)
        return (1)
	end
	
    -- Get agent_id
    declare @agent_id int

	-- First try to get the agent id initialized by the distribution agent
	declare @login_time datetime
	select @login_time = login_time from master..sysprocesses where spid = @@spid

	select @agent_id = id from MSsubscription_agents where
		spid = @@spid and
		login_time = @login_time

	-- If row not found, the current call is not from a distribution agent. Uses
	-- are creating trigger manually using the script generated by
	-- sp_script_synctran_triggers.
	-- Get the row using the publication name. However, it is possible that there are
	-- more than one qualifed rows with different subscription_type, for example
	-- pull and push subscriptions to share agent publications or subscriptions that has
	-- not been cleaned up.
	if @agent_id is null
	begin
		declare @num_dup_rows int
		select @agent_id = avg(id), @num_dup_rows = count(*) from MSsubscription_agents where
			publisher = @publisher and
			publisher_db = @publisher_db and
			publication = case @independent_agent 
				when 0 then N'ALL'
				else @publication
				end and
			-- We know the subscription must be updateble. This
			-- is to reduce the chance of dup rows.
			update_mode <> 0

		if @num_dup_rows > 1
		begin
			-- Raise subscription already exist error
			-- This should rarely happen.
			RAISERROR (14058, 16, -1)
			return(1)
		end

		if @agent_id is null
		begin
			raiserror(20588, 16, -1)
			return(1)
		end
	end


	/*
	**	Create system table MSreplication_objects if it does not exist
	*/
	IF NOT EXISTS (SELECT * FROM sysobjects WHERE 
		type = 'U' AND name = 'MSreplication_objects')
		BEGIN
			CREATE TABLE dbo.MSreplication_objects
			(
			publisher sysname NULL,
			publisher_db sysname NULL,
       		publication sysname NULL, 
			object_name	sysname NOT NULL,
			object_type	char(2) NOT NULL
			)
			IF @@ERROR <> 0
				begin
					rollback transaction 
					return (1)
				end
			
			CREATE UNIQUE CLUSTERED INDEX ucMSreplication_objects ON dbo.MSreplication_objects(object_name)
			EXEC dbo.sp_MS_marksystemobject 'dbo.MSreplication_objects'
		END


	-- Drop all replication triggers on the source object
	-- We should drop all because we don't support updatable subscriptions to 
	-- multiple publications on same dest table.
	declare object_cursor CURSOR LOCAL FAST_FORWARD for 
		select o.object_name, so.id from MSreplication_objects o, sysobjects so where
			o.object_name = so.name and
			so.parent_obj = @object_id and
			o.object_type = 'T'

	declare @old_id int 
	declare @old_name sysname

	OPEN object_cursor
    FETCH object_cursor INTO @old_name, @old_id
 	WHILE (@@fetch_status <> -1)
	BEGIN
        -- Cleanup identity range table
        declare @parent_obj int
        select @parent_obj = 0
		select @parent_obj = parent_obj from sysobjects where id = @old_id
        if exists (select * from sysobjects where name = 'MSsub_identity_range')
            delete MSsub_identity_range where objid = @parent_obj

        -- Drop the trigger
        exec @retcode = dbo.sp_MSdrop_object 
			@object_id = @old_id
		if @retcode <> 0 or @@error <> 0
			goto UNDO
		delete from MSreplication_objects where object_name=@old_name
		FETCH object_cursor INTO @old_name, @old_id
	END
    CLOSE object_cursor
    DEALLOCATE object_cursor

	-- Generate trigger names
        select @trigname = RTRIM(SUBSTRING(@sub_table,1,110))
	select @ins_trig = N'trg_MSsync_ins_' + @trigname 
	select @upd_trig = N'trg_MSsync_upd_' + @trigname 
	select @del_trig = N'trg_MSsync_del_' + @trigname 

	-- check uniqueness of names and revert to ugly guid-based name if friendly name already exists
	if exists (select name from sysobjects where name in (@ins_trig, @upd_trig, @del_trig))
        begin
            declare @guid_name nvarchar(36)
            select @guid_name =  convert (nvarchar(36), newid())
		select @ins_trig = 'trg_MSsync_ins_' + @guid_name
		select @upd_trig = 'trg_MSsync_upd_' + @guid_name
		select @del_trig = 'trg_MSsync_del_' + @guid_name
        end

	-- Get qual names
	if lower(@sub_table_owner) = N'null'
	begin
		select @qual_ins_trig = @ins_trig
		select @qual_upd_trig = @upd_trig
		select @qual_del_trig = @del_trig
	end
	else
	begin
		select @qual_ins_trig = QUOTENAME(@sub_table_owner) + N'.' + @ins_trig
		select @qual_upd_trig = QUOTENAME(@sub_table_owner) + N'.' + @upd_trig
		select @qual_del_trig = QUOTENAME(@sub_table_owner) + N'.' + @del_trig
	end


	exec @retcode = master..xp_varbintohexstr @primary_key_bitmap, @bitmap_str output
	if @retcode <> 0 or @@error <> 0
		return 1



/*
	exec ('if exists (select * from sysobjects where name = N''' + @ins_trig + N''' and xtype = N''TR'')
        drop trigger ' + @ins_trig)
    exec ('if exists (select * from sysobjects where name = N''' + @upd_trig + N''' and xtype = N''TR'')
        drop trigger ' + @upd_trig)
    exec ('if exists (select * from sysobjects where name = N''' + @del_trig + N''' and xtype = N''TR'')
        drop trigger ' + @del_trig)

*/
    -- We are now going to create triggers, so start a transaction
    begin tran
        -- Call out to individual create trigger routines
	    select @dbname = db_name()
	    select @cmd = 'sp_MSscript_sync_ins_trig ' + 
		    convert( nvarchar, @object_id )  + ', N' + 
			quotename(@publisher, '''')     + ', N' + 
			quotename(@publisher_db, '''')  + ', N' + 
			quotename(@publication, '''')   + ', N' + 
		    quotename(@ins_trig, '''')      + ', N' + 
			quotename(@ins_proc, '''')      + ', N' + 
			quotename(@proc_owner, '''')	+ ', N' + 
			quotename(@cftproc, '''')       + ', ' + 
			convert(nvarchar(10), @agent_id) + ', N' + 
			quotename(@identity_col, '''') + ', N' + 
			quotename(@ts_col, '''')        
		if @filter_clause in ('NULL', 'null')
		    select @cmd = @cmd + ', null' 
		else 
            select @cmd = @cmd + ', N''' + replace (@filter_clause,'''','''''')  + ''''
									
		-- Set primary key bitmap
		select @cmd = @cmd + ', ' + @bitmap_str

	    exec @retcode = master..xp_execresultset @cmd, @dbname
		IF @@ERROR <> 0 or @retcode <> 0
			goto UNDO
	    select @cmd = 'sp_MSscript_sync_upd_trig ' + 
		    convert( nvarchar, @object_id )  + ', N' + 
			quotename(@publisher, '''')     + ', N' + 
			quotename(@publisher_db, '''')  + ', N' + 
			quotename(@publication, '''')   + ', N' + 
		    quotename(@upd_trig, '''')      + ', N' + 
			quotename(@upd_proc, '''')      + ', N' + 
			quotename(@proc_owner, '''')	+ ', N' + 
			quotename(@cftproc, '''')       + ', ' + 
			convert(nvarchar(10), @agent_id)  + ', N' + 
			quotename(@identity_col, '''')  + ', N' + 
			quotename(@ts_col, '''')        
		if @filter_clause in ('NULL', 'null')
		    select @cmd = @cmd + ', null' 
		else 
            select @cmd = @cmd + ', N''' + replace (@filter_clause,'''','''''')  + ''''
									
		-- Set primary key bitmap
		select @cmd = @cmd + ', ' + @bitmap_str

		exec @retcode = master..xp_execresultset @cmd, @dbname
		IF @@ERROR <> 0 or @retcode <> 0
			goto UNDO

	    select @cmd = 'sp_MSscript_sync_del_trig ' + 
		    convert( nvarchar, @object_id )  + ', N' + 
			quotename(@publisher, '''')     + ', N' + 
			quotename(@publisher_db, '''')  + ', N' + 
			quotename(@publication, '''')   + ', N' + 
		    quotename(@del_trig, '''')      + ', N' + 
			quotename(@del_proc, '''')      + ', N' + 
			quotename(@proc_owner, '''')	+ ', N' + 
			quotename(@cftproc, '''')       + ', ' + 
			convert(nvarchar(10), @agent_id) + ', N' + 
			quotename(@identity_col, '''')  + ', N' + 
			quotename(@ts_col, '''')        
		if @filter_clause in ('NULL', 'null')
		    select @cmd = @cmd + ', null' 
		else 
            select @cmd = @cmd + ', N''' + replace (@filter_clause,'''','''''')  + ''''			

		-- Set primary key bitmap
		select @cmd = @cmd + ', ' + @bitmap_str

	    exec @retcode = master..xp_execresultset @cmd, @dbname
		IF @@ERROR <> 0 or @retcode <> 0
			goto UNDO

		-- Drop old entries before insert. The triggers with those names
		-- are created as above.
		delete MSreplication_objects where object_name = @ins_trig
		IF @@ERROR <> 0
			goto UNDO
		delete MSreplication_objects where object_name = @upd_trig
		IF @@ERROR <> 0
			goto UNDO
		delete MSreplication_objects where object_name = @del_trig
		IF @@ERROR <> 0
			goto UNDO

		-- Mark procedures as system procs so they don't show up in the UI
		exec dbo.sp_MS_marksystemobject @qual_ins_trig
		IF @@ERROR <> 0
			goto UNDO

		insert into MSreplication_objects(publisher, publisher_db, publication, object_name, object_type)
					values(@publisher, @publisher_db, @publication, @ins_trig, 'T')
		IF @@ERROR <> 0
			goto UNDO

		exec dbo.sp_MS_marksystemobject @qual_upd_trig
		IF @@ERROR <> 0
			goto UNDO
				
		insert into MSreplication_objects(publisher, publisher_db, publication, object_name, object_type)
					values(@publisher, @publisher_db, @publication, @upd_trig, 'T')
		IF @@ERROR <> 0
			goto UNDO

		exec dbo.sp_MS_marksystemobject @qual_del_trig
		IF @@ERROR <> 0
			goto UNDO

		insert into MSreplication_objects(publisher, publisher_db, publication, object_name, object_type)
					values(@publisher, @publisher_db, @publication, @del_trig, 'T')
		IF @@ERROR <> 0
			goto UNDO

		-- Mark the table for warnings in BCP
		update sysobjects set replinfo = replinfo | @synctran_bit where
			id = @object_id
		IF @@ERROR <> 0
			goto UNDO

		-- Set up identity range table
		if @identity_support <> 0
		begin
			if not exists (select * from sysobjects where name = 'MSsub_identity_range')
			begin
				create table dbo.MSsub_identity_range (
					objid int not null,
					range bigint not null,
					last_seed bigint not null,
					threshold int not null)
				IF @@ERROR <> 0
					goto UNDO
			
				CREATE UNIQUE CLUSTERED INDEX ucMSsub_identity_range ON dbo.MSsub_identity_range (objid)
				
				EXEC dbo.sp_MS_marksystemobject 'MSsub_identity_range'
				IF @@ERROR <> 0
					goto UNDO
			end
			if not exists (select * from MSsub_identity_range where objid = @object_id)
			begin
				-- add zero at the beginning.
				insert into MSsub_identity_range (objid, range, last_seed, threshold) values
					(@object_id, 0, 0, 0)
				IF @@ERROR <> 0
					goto UNDO
			end
		end

	-- commit tran
	commit tran

	-- Set trigger firing order for insert
	exec dbo.sp_settriggerorder @qual_ins_trig,'first','insert'
	exec dbo.sp_settriggerorder @qual_upd_trig,'first','update'
	exec dbo.sp_settriggerorder @qual_del_trig,'first','delete'
	
    -- Ignore errors.
    exec dbo.sp_MSsub_cleanup_orphans

	-- restore the trancount if necessary
	if (@loctrancount > 0)
	begin
		while (@@trancount < @loctrancount)
			begin tran
	end

	return (0)
UNDO:
	if @@trancount <> 0
		rollback tran
	return(1)
END
go

grant exec on dbo.sp_addsynctriggers to public

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

raiserror('Creating procedure sp_MSscript_where_clause', 0,1)
go
create procedure sp_MSscript_where_clause (
	@objid		  int,
	@columns      binary(32), 
	@clause_type  varchar(15) = 'pk_new', -- 'new pk', 'old pk', 'upd version', 'upd rc', 'trg pk', 'qcft_comp', 'new_pk_q', 'subwins_check'
	@ts_col       sysname = NULL,
	@indent       int = 0,
	@op_type      char(3) = NULL, -- 'ins', 'del', 'upd'
	@primary_key_bitmap varbinary(4000) = null )
as
BEGIN
	declare	@cmd			nvarchar(4000)
			,@colname		sysname
			,@ccoltype		sysname
			,@spacer		nvarchar(20)
			,@indkey		int
			,@indid			int
			,@key			sysname
			,@rc			int
			,@this_col		int
			,@art_col		int
			,@src_cols		int
			,@total_col		int
			,@col			sysname
			,@qualname		nvarchar(512)
			,@curparam		nvarchar(20)
			,@retcode		int
			,@fcreatedcolmap	bit
	declare @colmap table (relativeorder int identity(1,1), colid int)

	select @spacer = N' '
		,@cmd = N''
		,@indkey = 1
		,@indid = 0
		,@fcreatedcolmap = 0
	exec sp_MSget_qualified_name @objid, @qualname OUTPUT
	select @src_cols = max(colid)
			,@total_col = count(colid)
		from syscolumns where id = @objid
	exec dbo.sp_MSpad_command @cmd output, @indent
	if (@clause_type = 'qcft_comp')
		select @cmd = @cmd + N' '' where '
	else
		select @cmd = @cmd + N'where'
	exec dbo.sp_MSflush_command @cmd output, 1, @indent

	if @clause_type in ('new pk','old pk','upd version','trg pk','version pk','qcft_comp','new_pk_q','subwins_check')
	begin
		if @primary_key_bitmap is null
		begin
			exec @indid = dbo.sp_MStable_has_unique_index @objid
			if @indid is null
			begin
				raiserror('Debug: Cannot find unique index', 16, -1)
				return (1)
			end
		end

		--
		-- check if column Id match relative column order
		-- for specific trigger operations
		--
		if ((@total_col < @src_cols) and (@clause_type = 'trg pk') and 
			(@columns is null) and (@primary_key_bitmap is not null))
		begin
			--
			-- this table may have altered columns, so when we need to 
			-- set a mapping for using the bitmaps properly as the bitmap
			-- always refers relative column order
			--
			insert into @colmap (colid)
				select colid from syscolumns where id = @objid order by colid
			if (@@error != 0)
			begin
				raiserror('Could not create column mapping', 16, -1)
				return (1)
			end
			select @fcreatedcolmap = 1
		end
		
		while (1=1)
		begin
			--
			-- get the column position
			--
	        	if @primary_key_bitmap is null 
	        	begin
		             	select @key = index_col(@qualname, @indid, @indkey)
		             	if @key is null
		             		break
		             	--exec dbo.sp_MSget_col_position @objid, @columns, @key, @col output, @this_col output
		             	exec dbo.sp_MSget_col_position @objid, @columns, @key, @col output, NULL, 0, NULL, @this_col output
	        	end
	        	else
	        	begin
	        		exec dbo.sp_MSget_map_position @primary_key_bitmap, @indkey, @col output, @this_col output
	        		if @this_col is null
	        			break

				--
				-- set the actual column id for this relative order in the PK bitmap if necessary
				--
				if (@fcreatedcolmap = 1)
				begin
					select @this_col = colid
						,@col = 'c' + convert(sysname, colid) 
					from @colmap 
					where relativeorder = @this_col 
				end
	        	end

			--
			-- Get column name
			--
			exec @retcode = dbo.sp_MSget_colinfo @objid, @this_col, @columns, 0, @key output, @ccoltype output
			if (@retcode = 1)
			begin
				--
				-- this column not used for replication
				-- continue
				--
				select @indkey = @indkey + 1
				continue
			end
		
			if @clause_type = 'new pk'
			begin
			    if ColumnProperty(@objid, @key, 'IsIdentity') = 1
			        select @cmd = @cmd + @spacer + quotename(@key) + N' = @@identity'
			    else
			        select @cmd = @cmd + @spacer + quotename(@key) + N' = @' + @col

			    select @spacer = ' and '
			end
			else if @clause_type in ('upd version', 'subwins_check')
			begin
			    select @cmd = @cmd + @spacer + quotename(@key) + N' = @' + @col + N'_old'
			    select @spacer = ' and '
			end
			else if @clause_type = 'version pk'
			begin
			    select @cmd = @cmd + @spacer + @qualname + '.' + quotename(@key) + N' = inserted.' + quotename(@key)
			    select @spacer = ' and
	'
			end
			else if @clause_type in ('trg pk', 'old pk')
			begin				
				if @op_type = 'ins'
					select @cmd = @cmd + @spacer + quotename(@key) + N' = @' + @col + N'_old'
				else
					-- The vars correspoding to pk were set in sp_MSscript
					-- _pkvar_assignment.
					select @cmd = @cmd + @spacer + quotename(@key) + N' = @' + @col 
				select @spacer = ' and
	'
			end
			else if (@clause_type = 'qcft_comp')
			begin
				--
				-- Conflict compensation generation
				-- This is a special case - we generate
				-- and exec string for the WHERE clause
				--
				if (@op_type = 'ins')
					select @curparam = N'@' + @col
				else if (@op_type = 'del')
					select @curparam = N'@' + @col + N'_old'
				else
					select @curparam = N'ISNULL(@' + @col + N', @' + @col + N'_old)'
				
				select @cmd = @cmd + @spacer + quotename(@key)
				
				if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'varchar')
					select @cmd = @cmd + N' = '' + '''''''' + master.dbo.fn_MSgensqescstr(' + @curparam + N') collate database_default + '''''''' '
				else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'nvarchar')
					select @cmd = @cmd + N' = '' + ''N'''''' + master.dbo.fn_MSgensqescstr(' + @curparam + N') collate database_default + '''''''' '
				else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'char')
					select @cmd = @cmd + N' = '' + '''''''' + master.dbo.fn_MSgensqescstr(CAST(RTRIM(' + @curparam + N') as nvarchar)) collate database_default + '''''''' '
				else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'nchar')
					select @cmd = @cmd + N' = '' + ''N'''''' + master.dbo.fn_MSgensqescstr(CAST(RTRIM(' + @curparam + N') as nvarchar)) collate database_default + '''''''' '
				else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('binary','varbinary'))
					select @cmd = @cmd + N' = '' + master.dbo.fn_varbintohexstr(' + @curparam + N') collate database_default ' 
				else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('bit','bigint','int','smallint','tinyint','float','real','decimal','numeric'))
					select @cmd = @cmd + N' = '' + CAST(' + @curparam + N' as nvarchar) '
				else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('money','smallmoney'))
					select @cmd = @cmd + N' = '' + CONVERT(nvarchar(40),' + @curparam + N', 2) '
				else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'uniqueidentifier')
					select @cmd = @cmd + N' = '' + '''''''' + CAST(' + @curparam + N' as nvarchar(40)) + '''''''' '
				else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('datetime','smalldatetime'))
					select @cmd = @cmd + N' = '' + '''''''' + CONVERT(nvarchar(40), ' + @curparam + N', 112) + N'' '' + CONVERT(nvarchar(40), ' + @curparam + N', 114) + '''''''' '	
				else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'sql_variant')
					select @cmd = @cmd + N' = '' + master.dbo.fn_sqlvarbasetostr(' + @curparam + N' ) collate database_default '
				else
					select @cmd = @cmd + N' = '' + CAST(' + @curparam + N' as nvarchar) '
				
				select @spacer = ' + '' and  '
			end
			else if @clause_type = 'new_pk_q'
			begin
				--
				-- new value of primary key, ignore identity
				-- used for scripting in synctran procs
				--
				select @cmd = @cmd + @spacer + quotename(@key) + N' = @' + @col
				select @spacer = ' and '
			end
			select @indkey = @indkey + 1

			-- flush command if necessary
			exec dbo.sp_MSflush_command @cmd output, 1, @indent
		end -- end of while loop
	    
		-- add version col as necessary
		if ((@clause_type in ('upd version','subwins_check')) and (@ts_col is not null))
		begin
			--
			-- @ts_col is version col actually.
			-- check for special cases for queued processing
			--
			exec dbo.sp_MSget_col_position @objid, @columns, @ts_col, @col output
			if (@clause_type = 'subwins_check')
				select @cmd = @cmd + @spacer + @ts_col + N' = @' + @col
			else
				select @cmd = @cmd + @spacer + @ts_col + N' = @' + @col + N'_old'

			--
			-- save off command fragment
			--
			exec dbo.sp_MSflush_command @cmd output, 1, @indent
		end
	end -- end of if clause_type
	-- 'upd rc' is used for column value conflict detection. It is no longer used.
	else if @clause_type = 'upd rc'
	begin
		select @this_col = 1, @art_col = 1
		while @this_col <= @src_cols
		begin
			exec @rc = dbo.sp_MSget_colinfo @objid, @this_col, @columns, 0, @colname output, @ccoltype output
			if @rc = 0
			begin
				select @cmd = @cmd +  @spacer + '(' + @colname + N' = @c' + convert(varchar(4), @this_col) + N'_old or (' 
				select @cmd = @cmd + @colname + ' is null and @c' + convert(varchar(4), @this_col) + N'_old is null)) '
				select @spacer = N' and 
	'
				exec dbo.sp_MSflush_command @cmd output, 0, @indent
			end
			exec dbo.sp_MSflush_command @cmd output, 1, @indent
			select @this_col = @this_col + 1
		end

		-- save off cmd fragment
		exec dbo.sp_MSflush_command @cmd output, 1, @indent
	end
	
END
go

grant execute on dbo.sp_MSscript_where_clause to public

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

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

create procedure sp_MSarticlecleanup
	(@pubid uniqueidentifier, @artid uniqueidentifier)
as
	set nocount on
	declare @source_table 	nvarchar(270)
	declare @conflict_table nvarchar(270)
	declare @ownername 		sysname
	declare @objectname 	sysname
	declare @tablenick		int
	declare @objid 			int
	declare @sync_objid 	int
	declare @view_type		int
	declare @tsview			nvarchar(50)
	declare @guidstr		nvarchar(50)
	declare @csview			nvarchar(50)
	declare @viewname		nvarchar(270)
	declare @retcode		smallint
	declare @qualified_name	nvarchar(270)
	declare @bi_tablename	sysname
	declare	@bi_viewname	sysname
	declare @bi_procname	sysname
	declare @constraintname	sysname
	declare @merge_pub_markcolumn_bit int
	declare @merge_pub_unmarkcolumn_bit int

	-- to be called after article is set up in a subscriber

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

	select @merge_pub_markcolumn_bit = 0x4000
	select @merge_pub_unmarkcolumn_bit = ~@merge_pub_markcolumn_bit

	select @objid = max(objid) from sysmergearticles where artid = @artid

	-- get owner name, and table name
	select @objectname = name, @ownername = user_name(uid)
		from sysobjects	where id = @objid

	-- construct the qualified table name
	select @source_table = QUOTENAME(@ownername) + '.' + QUOTENAME(@objectname)

	exec @retcode=sp_MSguidtostr @artid, @guidstr out
	if @retcode<>0 or @@ERROR<>0 return (1)
	
	-- get the insert, update and conflict proc names from sysmergearticles
	select 	@sync_objid = sync_objid, 
			@view_type = view_type, 
			@tablenick = nickname,
			@bi_tablename = object_name(before_image_objid),
			@bi_viewname = object_name(before_view_objid),
			@conflict_table = conflict_table 
		from sysmergearticles where
			pubid = @pubid and artid = @artid

	/*
	** We are not owner_qualifed this conflict table because it is created by snapshot agent
	*/
	select @qualified_name = QUOTENAME(@ownername) + '.' + QUOTENAME(@conflict_table)

	/* Drop the conflict table */
	if (@conflict_table IS NOT NULL) and exists (select * from sysobjects where
			name = @conflict_table and type = 'U')
		begin
			exec ('drop table ' + @qualified_name)
			if @@ERROR<>0 return (1)
		end


	/* If there is a before image table, drop it and its cleanup proc */
	if (@bi_tablename is not null)
		begin
		set @bi_procname = @bi_tablename + '_clean'

		if exists (select * from sysobjects where
			name = @bi_procname and type = 'P')
			begin
			exec ('drop proc ' + @bi_procname)
			if @@ERROR<>0 return (1)
			end
		exec ('drop table ' + @bi_tablename)
		if @@ERROR<>0 return (1)
		end
	/* If there is a before image view, drop it */
	if (@bi_viewname is not null)
		begin
		exec ('drop view ' + @bi_viewname)
		if @@ERROR<>0 return (1)
		end
		
	/* Drop the article procs */
	exec @retcode=sp_MSdroparticleprocs @pubid, @artid
	if @@ERROR<>0 or @retcode<>0 return (1)

	/* Drop the article triggers */
	exec @retcode=sp_MSdroparticletriggers @source_table
	if @@ERROR<>0 or @retcode<>0 return (1)

	exec @retcode=sp_MSunmarkreplinfo @objectname, @ownername
	if @@ERROR<>0 or @retcode<>0 return (1)

	/* so that columns can be dropped */
	update syscolumns set colstat=colstat & @merge_pub_unmarkcolumn_bit where id=@objid
	if @@ERROR<>0 return (1)

	/* If the article's has a temporary ( view type = 2) or a permanent view (view_type = 1 ) drop the sync object */
	if (@view_type = 1 OR @view_type = 2)
		begin
			select @viewname = sysobjects.name from sysobjects where 
				ObjectProperty (sysobjects.id, 'IsView') = 1 
				and ObjectProperty (sysobjects.id, 'IsMSShipped') = 1 
				and sysobjects.id = @sync_objid
			if @viewname IS NOT NULL
				begin
					select @ownername = user_name(uid) from sysobjects where name=@viewname
					set @viewname = QUOTENAME(@ownername) + '.' + QUOTENAME(@viewname)
					exec ('drop view ' + @viewname)
					if @@ERROR<>0 return (1)
				end
		end

	/*
	** Drop the views created for MSmerge_contents and MSmerge_tombstone before dropping these two tables
	*/
	set @csview = 'ctsv_' + @guidstr
	set @tsview = 'tsvw_' + @guidstr
	if EXISTS (select * from sysobjects where name=@csview and type='V')
	BEGIN
		select @ownername = user_name(uid) from sysobjects where  name=@csview
		select @viewname = QUOTENAME(@ownername) + '.' + QUOTENAME(@csview)
		exec ('drop view ' + @viewname)
			if @@ERROR<>0 return (1)
	END
		
	if EXISTS (select * from sysobjects where name=@tsview and type='V')
	BEGIN
		select @ownername = user_name(uid) from sysobjects where  name=@tsview
		select @viewname = QUOTENAME(@ownername) + '.' + QUOTENAME(@tsview)
		exec ('drop view ' + @viewname)
			if @@ERROR<>0 return (1)
	END

	select @constraintname = 'repl_identity_range_pub_' + convert(nvarchar(36), @artid)
	select @constraintname = REPLACE(@constraintname, '-', '_')
	if exists (select * from sysobjects where name = @constraintname and xtype='C')
	begin
		exec ('alter table '+ @source_table + ' drop constraint ' + @constraintname)
		if @@ERROR<>0
			return (1)
	end

	select @constraintname = 'repl_identity_range_repub_' + convert(nvarchar(36), @artid)
	select @constraintname = REPLACE(@constraintname, '-', '_')
	if exists (select * from sysobjects where name = @constraintname and xtype='C')
	begin
		exec ('alter table '+ @source_table + ' drop constraint ' + @constraintname)
		if @@ERROR<>0
			return (1)
	end

	/* Delete from contents, tombstone, delete conflicts; Ignore errors that occur */
	delete from MSmerge_contents where tablenick = @tablenick
	delete from MSmerge_tombstone where tablenick = @tablenick
	/* Delete rows from MSmerge_genhistory - if this is the last table that refers to them */
	if not exists (select * from sysmergearticles where nickname = @tablenick and pubid <> @pubid)
		delete from MSmerge_genhistory where art_nick = @tablenick

	delete from MSmerge_delete_conflicts where tablenick = @tablenick
	delete from MSrepl_identity_range where objid=@objid
GO

exec dbo.sp_MS_marksystemobject sp_MSarticlecleanup
grant execute on dbo.sp_MSarticlecleanup to public

--------------------------------------------------------------------------------
--. fn_repladjustcolumnmap
--------------------------------------------------------------------------------
if exists ( select * from sysobjects
    where type = 'FN'
    and name = 'fn_repladjustcolumnmap' )
    drop function system_function_schema.fn_repladjustcolumnmap	
    
raiserror('Creating function fn_repladjustcolumnmap', 0,1)
go
create function system_function_schema.fn_repladjustcolumnmap
(
	@objid	int
	,@total_col int
	,@inmap varbinary(4000)
) 
returns varbinary(4000)
as
begin
	declare @colmap table (relativeorder int identity(1,1), colid int)
	declare
		@outmap varbinary(4000)
		,@relpos int
		,@colid int
		,@bytepos int
		,@bitpos int
		,@num_bytes int

	--
	-- initialize
	--
	select @outmap = 0
	insert into @colmap (colid)
		select colid from syscolumns where id = @objid order by colid
	if (@@error != 0)
	begin
		return cast(NULL as varbinary(4000))
	end

	--
	-- for each column in the column map
	--
	declare #colmap_cursor cursor local fast_forward for 
		select relativeorder, colid from @colmap 
		order by relativeorder
	for read only

	open #colmap_cursor
	fetch #colmap_cursor into @relpos, @colid
	while (@@fetch_status = 0)
	begin
		--
		-- select the absolute column position
		--
		select @bytepos = 1 + ((@colid-1) / 8)
			,@bitpos =  power(2, (@colid-1) % 8 ) 

		if (substring(@inmap, @bytepos, 1) & @bitpos = @bitpos)
		begin
			--
			-- set the relative position for this column in the output
			--
			select @bytepos = 1 + ((@relpos-1) / 8)
				,@bitpos =  power(2, (@relpos-1) % 8 )
				,@num_bytes = @total_col / 8 + 1

			select @outmap = substring(@outmap, 1, (@bytepos - 1)) + 
				(convert(binary(1), substring(@outmap, @bytepos, 1) | convert(tinyint,@bitpos))) + 
				substring(@outmap, (@bytepos + 1), (@num_bytes - @bytepos))
		end

		-- fetch next column mapping
		fetch #colmap_cursor into @relpos, @colid		
	end
	close #colmap_cursor
	deallocate #colmap_cursor

	return @outmap
end
go
grant execute on system_function_schema.fn_repladjustcolumnmap to public

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

raiserror('Creating procedure sp_MSscript_begintrig1', 0,1)
go
create procedure sp_MSscript_begintrig1 (
	@trigname      sysname
	,@objid         int
	,@procname      sysname
	,@filter_clause nvarchar(4000)
	,@op_type       char(3) = 'ins' -- ins, upd, del
	,@fisqueued	  bit = 0 -- 1 = Queued subscription
)
as
BEGIN
	declare @cmd       nvarchar(4000)
			,@start     int
			,@sub_len   int
			,@qualname  nvarchar(512)

    exec sp_MSget_qualified_name @objid, @qualname OUTPUT

	-- construct trigger name
	select @cmd = N'create trigger ' + QUOTENAME(@trigname) + N' on ' + @qualname + N' for '
	select @cmd = case
					when (@op_type = 'ins') then @cmd + N'insert '
					when (@op_type = 'upd') then @cmd + N'update '
					when (@op_type = 'del') then @cmd + N'delete '
				end
	select @cmd = @cmd + N'not for replication
as
'
	insert into #proctext(procedure_text) values (@cmd)

	--
	-- declare common local variables
	--
	--		,@update_mode char(40)
	--		,@failover_mode char(10)
	--
	insert into #proctext(procedure_text) values (N'
	declare 	@rc int
		,@retcode int
		,@connect_string nvarchar(2000)
		,@rpc_proc nvarchar(4000)
		,@rpc_types nvarchar(4000)
		,@update_mode_id int
		,@bitmap varbinary(4000)
		,@version_guid uniqueidentifier
		,@trigger_op char(10) ')

		--
		-- queued specific declarations
		--
	insert into #proctext(procedure_text) values (N'
		,@failover_mode_id int
		,@queue_server sysname
		,@queue_id sysname
		,@tran_id varchar(255)
		,@subscriber sysname
		,@subscriber_db sysname 
		,@partial_cmd bit
		,@start_offset int
		,@end_offset int
		,@vb_buffer varbinary(8000)
		,@vb_bufferlen int		
		')

	-- script variables used to retrieve data from inserted table
	if @op_type in ('ins', 'upd')
	begin
		insert into #proctext(procedure_text) values(N'
	declare ')
	        exec dbo.sp_MSscript_params @objid, null, null, 0, null
	end
	insert into #proctext(procedure_text) values(N'
	declare ')
	exec dbo.sp_MSscript_params @objid, null, '_old',  0, null

	-- Set @rc, @subscriber and @subscriber_db
	-- Optimization. Return immediately if no row changed
	select @cmd = N'

	select @rc = @@ROWCOUNT, @subscriber = @@SERVERNAME, @subscriber_db = db_name() 
	if @rc = 0 
		return 
	set nocount on '
	insert into #proctext(procedure_text) values(@cmd)
		
	-- set column update bitmap for update trigger
	if @op_type in ('upd')
	begin
		declare 	@num_bytes		int
				,@i_byte			int
				,@i_bit			tinyint
				,@len_before nvarchar(10)
				,@len_after nvarchar(10)
				,@index1 nvarchar(10)
				,@index2 nvarchar(10)
				,@this_col 		int
				,@max_col			int
				,@total_col		int

		--
		-- check if the subscriber table was altered for column changes
		--
		select @max_col = max(colid)
				,@total_col = count(colid)
			from syscolumns where id = @objid
		if (@total_col = @max_col)
		begin
			--
			-- actual colid and relative column order are same
			--
			insert into #proctext(procedure_text) values(N'
	select @bitmap = columns_updated() 
	')
		end
		else
		begin
			--
			-- we need to convert the columns_updated bitmap to 
			-- a bitmap that contains relative column information
			--
			select @cmd = N'
	select @bitmap = fn_repladjustcolumnmap( ' + cast(@objid as sysname) + N', ' +
				cast(@total_col as sysname) + N', columns_updated())'
			insert into #proctext(procedure_text) values(@cmd)
			select @cmd = N'				
	if (@bitmap is null) 
	begin
		raiserror(''fn_repladjustcolumnmap could not create column mapping'', 16, 1)
		goto FAILURE
	end
	'
			insert into #proctext(procedure_text) values(@cmd)
		end

	        --
	        -- Mark the version bit in the bitmap as updated.
	        --
	        select @cmd = N'
	' + '-- set the bit for msrepl_tran_version'
		insert into #proctext(procedure_text) values(@cmd)

		-- get actual column id
		exec dbo.sp_MSget_col_position @objid, null, 'msrepl_tran_version', @this_col = @this_col output
		if (@total_col < @max_col)
		begin
			--
			-- this table has altered columns, create a mapping between
			-- relative column position and actual column id
			--
			declare @colmap table (relativeorder int identity(1,1), colid int)
			insert into @colmap (colid)
				select colid from syscolumns where id = @objid order by colid
			if (@@error != 0)
			begin
				raiserror('Could not create column mapping', 16, -1)
				return (1)
			end

			--
			-- get the relative column position for msrepl_tran_version
			--
			select @this_col = relativeorder from @colmap where colid = @this_col 
		end
	
		select @num_bytes = @total_col / 8 + 1
			,@i_byte = 1 + (@this_col-1) / 8
			,@i_bit  = power(2, (@this_col-1) % 8 )

		select @len_before = convert(nvarchar(10), @i_byte -1)
			,@index1 = convert(nvarchar(10), @i_byte)
			,@index2 = convert(nvarchar(10), @i_byte + 1)
			,@len_after = convert(nvarchar(10), @num_bytes - @i_byte)

		select @cmd = N'
	select @bitmap = substring(@bitmap, 1, ' + @len_before + 
            ') + (convert(binary(1), substring(@bitmap, ' + @index1 + 
            ', 1) | convert(tinyint,' + convert(nvarchar(10), @i_bit) + 
            '))) + substring(@bitmap, ' + @index2 + 
            ', ' + @len_after + 
            ') '
		exec dbo.sp_MSflush_command @cmd, 1
	end

	select @cmd = N'
	select @version_guid = newid() '
	insert into #proctext(procedure_text) values(@cmd) 

	-- Partition check statement
	if @filter_clause IS NOT NULL
	begin
		declare @retcode int
		exec @retcode = sp_MSsubst_filter_names NULL, N'inserted', @filter_clause output
		if @retcode <> 0 or @@error <> 0
			return 1	    
				
		select @cmd = N'
	if exists (select * from inserted where not ('
		insert into #proctext(procedure_text) values(@cmd) 
		    
		-- break filter_clause into chunks of 255
		select @start = 1
		while @start < len(@filter_clause)
		begin
			if len(@filter_clause) < 255
				select @sub_len = len(@filter_clause)
			else
				select @sub_len = 255
			select @cmd = substring(@filter_clause, @start, @sub_len)
			exec dbo.sp_MSflush_command @cmd output, 1
			select @start = @start + @sub_len
		end
				
		select @cmd = N'))
	 begin 
	     exec sp_MSreplraiserror 21034
	     goto FAILURE 
	 end '
		insert into #proctext(procedure_text) values(@cmd) 
	end

	-- trigger nesting checks
	if @op_type in ('upd')
	begin
		select @cmd = N'
	' + '-- trigger nesting check
	exec @retcode = dbo.sp_check_sync_trigger @@procid, @trigger_op OUTPUT 
	if (@retcode = 1)
		return '
		insert into #proctext(procedure_text) values(@cmd) 
	end

END
go

grant execute on dbo.sp_MSscript_begintrig1 to public

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

raiserror('Creating procedure sp_MSscript_trigger_variables', 0,1)
go
create procedure sp_MSscript_trigger_variables
(
	@objid int,
	@prefix char(1) = null, -- null or '@'
	@postfix varchar(4) = null,
	@indent int = 0,
	@spacer nvarchar(1) = N' ', 
	@bOutput_params tinyint = 0,  -- declare output params if necessary
	@identity_col sysname = null,
	@ts_col sysname = null,
	@include_type bit = 0,
	@set_nulls	bit = 0,
	@op_type char(3) = 'ins', -- 'ins, 'upd', 'del'
	@is_new		bit = 0,
	@primary_key_bitmap varbinary(4000) = NULL,
	@no_output bit = 0
)
as
begin
	declare @cmd          nvarchar(4000)
			,@colname      sysname
			,@ccoltype     sysname
			,@src_cols     int
			,@this_col     int
			,@total_col		int
			,@indkey			int
			,@fcreatedcolmap	bit
			,@rc           int
			,@column		  nvarchar(4000)
	declare @colmap table (relativeorder int identity(1,1), colid int)

	-- script cursor select variables
	select @cmd = N''
			,@indkey = 1
			,@fcreatedcolmap = 0
	select @src_cols = max(colid)
			,@total_col = count(colid)
		from syscolumns where id = @objid
	exec dbo.sp_MSpad_command @cmd output, @indent

	--
	-- check if column Id match relative column order
	-- for trigger scripting
	--
	if (@total_col < @src_cols)
	begin
		--
		-- this table may have altered columns, so when we need to 
		-- set a mapping for using the bitmaps properly as the bitmap
		-- always refers relative column order
		--
		insert into @colmap (colid)
			select colid from syscolumns where id = @objid order by colid
		if (@@error != 0)
		begin
			raiserror('Could not create column mapping', 16, -1)
			return (1)
		end
		select @fcreatedcolmap = 1
	end

	while (@indkey <= @total_col)
	begin
		--
		-- set the actual column id for this relative order in the bitmap if necessary
		--
		if (@fcreatedcolmap = 1)
		begin
			select @this_col = colid from @colmap 
				where relativeorder = @indkey 
		end
		else
		begin
			select @this_col = @indkey
		end

		--
		-- Get column name
		-- Don't include timestamp or computed columns        
		--
		exec @rc = dbo.sp_MSget_colinfo @objid, @this_col, null, 0, @colname output, @ccoltype output
		if @rc = 0 and EXISTS (select name from syscolumns where id=@objid and colid=@this_col and iscomputed<>1 and xtype <> 189)
		begin
			if @prefix is null
			begin
			
				if (@set_nulls = 1)
				begin
					-- Optimization:
					-- Get null or actual column name
					-- Note: the output is quoted.
					exec dbo.sp_MSget_synctran_column 
						@ts_col = @ts_col,
						@op_type = @op_type, -- 'ins, 'upd', 'del'
						@is_new = @is_new,
						@primary_key_bitmap = @primary_key_bitmap,
						@colname = @colname,
						@this_col = @this_col,
						@column = @column output,
						@from_proc = 0, 
						@coltype = @ccoltype, 
						@type = NULL,
						@art_col = @indkey
					select @cmd = @cmd + @spacer + @column + isnull(@postfix, N'')
				end
				else
				begin
					-- set null is false
					select @cmd = @cmd + @spacer + N'[' + @colname + isnull(@postfix, N'') + N']'
				end
			end
			else
			begin
				-- prefix was specified
				select @cmd = @cmd + @spacer + isnull(@prefix, N'') 
					+ N'c' + RTRIM(convert(nvarchar(4), @this_col)) + isnull(@postfix, N'')
			end

			if (@include_type = 1)
			begin
				declare @typestring nvarchar(100)
				exec dbo.sp_MSget_type @objid, @this_col, @colname output, @typestring OUTPUT
				select @cmd = @cmd +  N' ' + @typestring 
			end

			-- new vars of type timestamp and identity are declared as output params
			if (@bOutput_params = 1 and (@ccoltype = N'timestamp' or ColumnProperty(@objid, @colname, 'IsIdentity') = 1))
				or (@colname = @identity_col or @colname = @ts_col)
			begin
				-- YWU: Do this to avoid output in cursor declaration statement.
				-- The right thing seems to be set output only when bOutput_params is set
				-- but it seems not the way this sp is called.
				if @set_nulls = 0 and @no_output = 0
					select @cmd = @cmd + N' output'
			end

			select @spacer = N','
		end -- if rc=0 and exists ...
		
		exec dbo.sp_MSflush_command @cmd output, 0, @indent
		select @indkey = @indkey + 1
	end -- while () loop

	exec dbo.sp_MSflush_command @cmd output, 1, @indent
	insert into #proctext(procedure_text) values(N'
') 

end
go

grant execute on dbo.sp_MSscript_trigger_variables to public

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

raiserror('Creating procedure sp_MSscript_trigger_assignment', 0,1)
go

create procedure sp_MSscript_trigger_assignment (
	@objid int
	,@postfix char(4) = null
	,@indent int = 0
	,@ts_col sysname
	,@op_type char(3) -- 'ins, 'upd', 'del'
	,@is_new	bit
	,@primary_key_bitmap varbinary(4000) = null
)
as
BEGIN
	declare @cmd          nvarchar(4000)
			,@colname      sysname
			,@spacer       nvarchar(1)
			,@ccoltype     sysname
			,@src_cols     int
			,@this_col     int
			,@total_col		int
			,@indkey			int
			,@fcreatedcolmap	bit
			,@rc           int
			,@column		  nvarchar(4000)
	declare @colmap table (relativeorder int identity(1,1), colid int)

	-- initialize
	select @spacer = N' '
			,@indkey = 1
			,@fcreatedcolmap = 0
	select @src_cols = max(colid)
			,@total_col = count(colid)
		from syscolumns where id = @objid
	exec dbo.sp_MSpad_command @cmd output, @indent

	--
	-- check if column Id match relative column order
	-- for trigger scripting
	--
	if (@total_col < @src_cols)
	begin
		--
		-- this table may have altered columns, so when we need to 
		-- set a mapping for using the bitmaps properly as the bitmap
		-- always refers relative column order
		--
		insert into @colmap (colid)
			select colid from syscolumns where id = @objid order by colid
		if (@@error != 0)
		begin
			raiserror('Could not create column mapping', 16, -1)
			return (1)
		end
		select @fcreatedcolmap = 1
	end

	while (@indkey <= @total_col)
	begin
		--
		-- set the actual column id for this relative order in the bitmap if necessary
		--
		if (@fcreatedcolmap = 1)
		begin
			select @this_col = colid from @colmap 
				where relativeorder = @indkey 
		end
		else
		begin
			select @this_col = @indkey
		end

		--
		-- Get column name
		--
		exec @rc = dbo.sp_MSget_colinfo @objid, @this_col, null, 0, @colname output, @ccoltype output
		if @rc = 0  and EXISTS (select name from syscolumns where id=@objid and @this_col=colid and iscomputed<>1)
		begin
			-- Optimization:
			-- Get null or actual column name
			-- Note: the output is quoted.
			exec dbo.sp_MSget_synctran_column 
				@ts_col = @ts_col,
				@op_type = @op_type, -- 'ins, 'upd', 'del'
				@is_new = @is_new,
				@primary_key_bitmap = @primary_key_bitmap,
				@colname = @colname,
				@this_col = @this_col,
				@column = @column output,
				@from_proc = 0, 
				@coltype = NULL, 
				@type = NULL,
				@art_col = @indkey

			select @cmd = @cmd + @spacer + N'@c' + 
				convert(sysname, @this_col) + isnull(@postfix, N'') + 
				N' = ' + @column  
				
			select @spacer = N','
		end
		exec dbo.sp_MSflush_command @cmd output, 1, @indent
		select @indkey = @indkey + 1
	end
	exec dbo.sp_MSflush_command @cmd output, 1, @indent

END
go

grant execute on dbo.sp_MSscript_trigger_assignment to public

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

raiserror('Creating procedure sp_MSget_synctran_column', 0,1)
go
create procedure sp_MSget_synctran_column
(
	@ts_col sysname,
	@op_type char(3), -- 'ins, 'upd', 'del'
	@is_new	bit,
	@primary_key_bitmap varbinary(4000) = null,
	@colname sysname,
	@this_col int, -- position in source object
	@column nvarchar(4000) output,
	@from_proc bit = 0,
	@coltype sysname = NULL,
	@type	varchar(10) = NULL,
	@art_col int = NULL -- position in the partition.
)
as
begin
	declare @bytestr      nvarchar(10)
			,@bitstr       nvarchar(10)
			,@typed_null	  nvarchar(255)

	--
	-- if @art_col is not NULL then it means one of the following:
	-- 1) If we are scripting for triggers - then it means the subscriber
	-- 	table (destination table) was altered and column id do not match
	-- 	relative column order(as specified in PK bitmap or columns_updated()).
	--	The @art_col will represent the relative column order in the bitmap
	--	and @this_col will represent the actual column id
	--
	-- 2) If we are scripting for synctran procedures on publisher - then 
	--	@art_col represents the relative index of the column that is being
	--	replicated and @this_col represents the actual column id of the column
	--
	-- if @art_col is NULL - then we will set it to the value of @this_col
	--
	if (@art_col is NULL)
		select @art_col = @this_col
		
	select @typed_null = case when (@coltype is null) then N'NULL'
					else N'convert(' + @coltype + N', NULL)' end

	-- Optimization:
	-- If the column value is not needed, we set the corresponding
	-- param to null to reduce the network traffic. Here is the rule:

	-- For new values in update trigger,
	--	Set the param to column value or null depending on whether or
	--  or the column is updated.
	-- For old values 
	--	if ts col is replicated and the current column is not the ts col
	--	and the column is not in primary key, set the param to null
	-- For other cases
	--	set the param to column values.

	-- Called by proc
	if @type = 'pk_var'
	begin
		select @bytestr = convert( nvarchar, 1 + (@art_col-1) / 8 )
		select @bitstr =  convert( nvarchar, power(2, (@art_col-1) % 8 ) )

		select @column = N'case substring(@bitmap,' 
			+ @bytestr + N',1) & ' + @bitstr +  
			N' when ' + @bitstr + N' then ' + N'@c'+ convert( nvarchar, @this_col ) + 
			N' else ' + N'@c'+ convert( nvarchar, @this_col )  + N'_old end'
	end
	else if (@from_proc = 1)
	begin
		select @bytestr = convert( nvarchar, 1 + (@art_col-1) / 8 )
		select @bitstr =  convert( nvarchar, power(2, (@art_col-1) % 8 ) )

		select @column = N'case substring(@bitmap,' 
			+ @bytestr + N',1) & ' + @bitstr +  
			N' when ' + @bitstr + N' then ' + N'@c'+ convert( nvarchar, @this_col ) + 
			N' else [' + @colname + N'] end'
	end
	-- Called in trigger,
	else if (@is_new = 1) and (@op_type = 'upd')
	begin
		-- @bitmap is set using columns_updated() at the beginning
		-- of the trigger.
		select @bytestr = convert( nvarchar, 1 + (@art_col-1) / 8 )
		select @bitstr =  convert( nvarchar, power(2, (@art_col-1) % 8 ) )
		
		select @column = N'case substring(@bitmap,' + @bytestr + N',1) & ' + @bitstr +  
			N' when ' + @bitstr + N' then [' + @colname + N'] ' + 
			N' else ' + @typed_null 
			+' end'
	end
	else if ((@is_new = 0) and 
		(@ts_col is not null and @colname not in (@ts_col, N'msrepl_tran_version')) and
		(@primary_key_bitmap is not null and 
		(substring(@primary_key_bitmap, 1 + (@art_col-1) / 8 , 1) & power(2, (@art_col-1) % 8 )) = 0))
		select @column =  @typed_null
	else 
		select @column = N'[' + @colname  + N'] '
	
	-- Add a new line
	select @column = @column + N'
	'
end
go

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

raiserror('Creating procedure sp_MSscript_pkvar_assignment', 0,1)
go
create procedure sp_MSscript_pkvar_assignment
(
	@objid		  int,
	@columns      binary(32), 
	@indent       int = 0,
	@identity_col sysname = NULL, -- Not null value used by trigger scripting
	@ts_col       sysname = NULL,  -- Not null value used by trigger scripting
	@primary_key_bitmap varbinary(4000) = null
)
as
begin
	-- This stored procedure will assign the '_old' var to new var
	-- based on @bitmap. This is to avoid using case statement
	-- in the where clause in the synctran pub proc, which
	-- will cause a table scan.
	declare @cmd          nvarchar(4000)
		,@spacer       nvarchar(20)
		,@indkey       int
		,@indid        int
		,@this_col     int
		,@col          sysname
		,@qualname     nvarchar(512)
		,@column		  nvarchar(255)
		,@key	      sysname
		,@src_cols	  int
		,@total_col		int
		,@fcreatedcolmap	bit
		,@art_col int -- relative position of column
	declare @colmap table (relativeorder int identity(1,1), colid int)

	select @spacer = N'select '
		,@cmd = N''
		,@indkey = 1
		,@indid = 0
		,@fcreatedcolmap = 0
	exec sp_MSget_qualified_name @objid, @qualname OUTPUT
	select @src_cols = max(colid)
			,@total_col = count(colid)
		from syscolumns where id = @objid
	exec dbo.sp_MSpad_command @cmd output, @indent
	exec dbo.sp_MSflush_command @cmd output, 1, @indent

	if @primary_key_bitmap is null
	begin
		exec @indid = dbo.sp_MStable_has_unique_index @objid
		if @indid is null
		begin
			raiserror('Debug: Cannot find unique index', 16, -1)
			return (1)
		end
	end

	--
	-- check if column Id match relative column order
	-- for trigger scripting
	--
	if ((@total_col < @src_cols) and 
		(@columns is null) and (@primary_key_bitmap is not null))
	begin
		--
		-- this table may have altered columns, so when we need to 
		-- set a mapping for using the bitmaps properly as the bitmap
		-- always refers relative column order
		--
		insert into @colmap (colid)
			select colid from syscolumns where id = @objid order by colid
		if (@@error != 0)
		begin
			raiserror('Could not create column mapping', 16, -1)
			return (1)
		end
		select @fcreatedcolmap = 1
	end

	while (1=1)
	begin
		if @primary_key_bitmap is null 
		begin
			select @key = index_col(@qualname, @indid, @indkey)
			if @key is null
				break
			exec dbo.sp_MSget_col_position @objid, @columns, @key, @col output, @this_col output
		end
		else
		begin
			exec dbo.sp_MSget_map_position @primary_key_bitmap, @indkey, @col output, @this_col output
			if @this_col is null
				break

    			--
    			-- set the actual column id for this relative order in the PK bitmap if necessary
    			--
			if (@fcreatedcolmap = 1)
			begin
				select @art_col = @this_col
				select @this_col = colid
					,@col = 'c' + convert(sysname, colid) 
				from @colmap 
				where relativeorder = @art_col 
			end
			else
			begin
				select @art_col = NULL
			end

			-- Get column name
			exec dbo.sp_MSget_colinfo @objid, @this_col, @columns, 0, @key output
		end

		select @indkey = @indkey + 1

		if @key in (@identity_col, @ts_col)
			continue

		select @cmd = @spacer + N'@c' + convert(nvarchar(10), @this_col)

		-- Get the new values for the columns in primary key.
		exec dbo.sp_MSget_synctran_column 
			@ts_col = null,
			@op_type = null , -- 'ins, 'upd', 'del'
			@is_new = null,
			@primary_key_bitmap = null,
			@colname = null,
			@this_col = @this_col,
			@column = @column output,
			@from_proc = 0,
			@coltype = null,
			@type = 'pk_var',
			@art_col = @art_col
			select @cmd = @cmd + N' = ' + @column 
			select @spacer = ',
	'

		-- flush command if necessary
		exec dbo.sp_MSflush_command @cmd output, 1, @indent
	end

end
go
grant exec on dbo.sp_MSscript_pkvar_assignment to public

--------------------------------------------------------------------------------
--.	Common system objects (replcom.sql)
--------------------------------------------------------------------------------
if exists (select * from sysobjects
	where type = 'P'
        and name = 'sp_MSdrop_distributor_alerts_and_responses')
        drop procedure sp_MSdrop_distributor_alerts_and_responses
go

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

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

raiserror('Creating procedure sp_MSdrop_distributor_alerts_and_responses', 0,1)
go
create procedure sp_MSdrop_distributor_alerts_and_responses
as

    declare @name           nvarchar(100)
    declare @alert_id       int
    declare @retcode        int

    --
    -- Delete alerts and response jobs
    --

    -- Drop Replication Checkup Agent
    select @name = formatmessage(20533)
    IF EXISTS (SELECT * FROM msdb..sysjobs_view WHERE name = @name collate database_default and
        UPPER(originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName'))))
    BEGIN
        EXEC @retcode = msdb.dbo.sp_delete_job  @job_name = @name
        IF @@ERROR <> 0 or @retcode <> 0
        BEGIN
            return (1)            
        END
    END

    -- Drop Reinit subscription response job
    set @name = formatmessage(20570)
    IF EXISTS (SELECT * FROM msdb..sysjobs_view WHERE name = @name collate database_default and
        UPPER(originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName'))))
    BEGIN
        EXEC @retcode = msdb.dbo.sp_delete_job  @job_name = @name
        IF @@ERROR <> 0 or @retcode <> 0
        BEGIN
            return (1)            
        END
    END

    -- Drop the alerts
    set @alert_id = 14150 -- success alert
    set @name=formatmessage(20540)
    if exists (select * from msdb.dbo.sysalerts where message_id=@alert_id)
    begin
        select @name=name from msdb.dbo.sysalerts where message_id=@alert_id
        exec @retcode = msdb.dbo.sp_delete_alert @name
        if @@error <> 0 or @retcode <> 0
            return (1)            
    end
    
    set @alert_id = 14151 -- failure alert
    set @name = formatmessage(20536)
    if exists (select * from msdb.dbo.sysalerts where message_id=@alert_id)
    begin
        select @name=name from msdb.dbo.sysalerts where message_id=@alert_id
        exec @retcode = msdb.dbo.sp_delete_alert @name
        if @@error <> 0 or @retcode <> 0
            return (1)            
    end
    
    set @alert_id = 14152 -- retry alert
    set @name = formatmessage(20537)
    if exists (select * from msdb.dbo.sysalerts where message_id=@alert_id)
    begin
        select @name=name from msdb.dbo.sysalerts where message_id=@alert_id
        exec @retcode = msdb.dbo.sp_delete_alert @name
        if @@error <> 0 or @retcode <> 0
            return (1)            
    end

    set @alert_id = 14153 -- warnning alert
    set @name = formatmessage(20540)
    if exists (select * from msdb.dbo.sysalerts where message_id=@alert_id)
    begin
        select @name=name from msdb.dbo.sysalerts where message_id=@alert_id
        exec @retcode = msdb.dbo.sp_delete_alert @name
        if @@error <> 0 or @retcode <> 0
            return (1)            
    end

    -- Remove Validation Failure Alert
    set @alert_id = 20574 
    set @name = formatmessage(20565)
    if exists (select * from msdb.dbo.sysalerts where message_id=@alert_id)
    begin
        select @name=name from msdb.dbo.sysalerts where message_id=@alert_id
        exec @retcode = msdb.dbo.sp_delete_alert @name
        if @@error <> 0 or @retcode <> 0
            return (1)            
    end

    -- Remove Validation Sucess Alert
    set @alert_id = 20575
    set @name = formatmessage(20566)
    if exists (select * from msdb.dbo.sysalerts where message_id=@alert_id)
    begin
        select @name=name from msdb.dbo.sysalerts where message_id=@alert_id
        exec @retcode = msdb.dbo.sp_delete_alert @name
        if @@error <> 0 or @retcode <> 0
            return (1)            
    end
    
    -- Remove Reinitialized after Validation Failure
    set @alert_id = 20525 -- checksum alert
    set @name = formatmessage(20573)
    if exists (select * from msdb.dbo.sysalerts where message_id=@alert_id)
    begin
        select @name=name from msdb.dbo.sysalerts where message_id=@alert_id
        exec @retcode = msdb.dbo.sp_delete_alert @name
        if @@error <> 0 or @retcode <> 0
            return (1)            
    end

    -- Remove subscription reinitialized after validation failure
    set @alert_id = 20572 -- corresponding to formatmessage(20566),  
    set @name=formatmessage(20573)
    if exists (select * from msdb.dbo.sysalerts where message_id=@alert_id)
    begin
        select @name=name from msdb.dbo.sysalerts where message_id=@alert_id
        exec @retcode = msdb.dbo.sp_delete_alert @name
        if @@error <> 0 or @retcode <> 0
            return (1)            
    end

    -- Remove Shutdown request Alert
    set @alert_id = 20578 --  Custom agent shutdown message
    set @name=formatmessage(20578)
    if exists (select * from msdb.dbo.sysalerts where message_id=@alert_id)
    begin
        select @name=name from msdb.dbo.sysalerts where message_id=@alert_id
        exec @retcode = msdb.dbo.sp_delete_alert @name
        if @@error <> 0 or @retcode <> 0
            return (1)            
    end

GO

raiserror('Creating procedure sp_adddistributiondb', 0,1)
go

CREATE PROCEDURE sp_adddistributiondb (
    @database sysname,
    @data_folder nvarchar(255) = NULL,
    @data_file nvarchar(255) = NULL,            /* physical file name */
    @data_file_size int = 2,                    /* Default: 2MB */            
    @log_folder nvarchar(255) = NULL,
    @log_file nvarchar(255) = NULL,             /* physical file name */
    @log_file_size int = 0,
    @min_distretention int = 0,                 /* min distribution retention period in hours */
    @max_distretention int = 72,                /* max distribution retention period in hours */
    @history_retention int = 48,                /* history retention period in hours */
    @security_mode int = 0,                     /* distributor login security 0 standard 1 integrated */
    @login sysname = 'sa',                      /* standard login name */
    @password sysname = NULL,                   /* standard login password */
    @createmode int = 0,  /* 0: use create db for attach (recommended), 
                            1: create db or use existing but no attach (this is the old way), 
                            2: create for instdist and detach only */
	@from_scripting bit = 0
    ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */
    DECLARE @data_path nvarchar(512)
    DECLARE @log_path nvarchar(512)
    
    DECLARE @data_path_quoted_for_copy nvarchar(512)
    DECLARE @log_path_quoted_for_copy nvarchar(512)

    DECLARE @logical_data_file nvarchar(255)
    DECLARE @logical_log_file nvarchar(255)
    DECLARE @canneddbdata_file nvarchar(255)
    DECLARE @canneddblog_file nvarchar(255)
    DECLARE @filecopy_cmd nvarchar(255)
    DECLARE @file_exists bit
    DECLARE @data_file_preexists int
    DECLARE @log_file_preexists int
	DECLARE @osql_path nvarchar(260)
    DECLARE @osql_cmd nvarchar(1000)
	DECLARE @osql_for_nt int
    DECLARE @devnum int
    --DECLARE @num_pages int
    DECLARE @retcode int
    DECLARE @reg_key nvarchar(255)
    DECLARE @agentname nvarchar(100)
    DECLARE @command nvarchar (2048)
    DECLARE @distbit int
    DECLARE @install_path nvarchar(255)
    DECLARE @mssql_data_path nvarchar(255)
    DECLARE @on_clause nvarchar(512)
    DECLARE @logon_clause nvarchar(512)
    DECLARE @distproc nvarchar(255)
    DECLARE @major_version int
    DECLARE @db_exists bit
    DECLARE @trunc_log_bit int
    DECLARE @description nvarchar(100)
    DECLARE @category_name sysname
    DECLARE @createmode_attach int
    DECLARE @createmode_noattach int
    DECLARE @createmode_fordetach int

    --DECLARE @filegrowth nvarchar(10)
    DECLARE @data_file_size_str nvarchar(10)
    DECLARE @log_file_size_str nvarchar(10)
    DECLARE @platform_nt binary

    --DECLARE @max_datafile_size int
    --DECLARE @max_logfile_size int
    
	IF @password = N''
		select @password = NULL

    select @platform_nt = 0x1
    --select @filegrowth = N'512KB'

    -- on error, delete the data and log files only if they didn't pre-exist.
    -- by default, assume they pre-exist.
    select @data_file_preexists = 1
    select @log_file_preexists = 1
    select @file_exists = 0

    if (@data_file_size IS NULL) or (@data_file_size = 0)
        select @data_file_size_str = N'512KB'
    else
        select @data_file_size_str = convert(nvarchar(10), @data_file_size)

    if (@log_file_size IS NULL) or (@log_file_size = 0)
        select @log_file_size_str = N'512KB'
    else
        select @log_file_size_str = convert(nvarchar(10), @log_file_size)
    
    --if (@data_file_size > 16)
    --  select @max_datafile_size = @data_file_size
    --else
    --  select @max_datafile_size = 16
    
    --if (@log_file_size > 16)
    --  select @max_logfile_size = @log_file_size
    --else
    --  select @max_logfile_size = 16

    select @createmode_attach = 0, @createmode_noattach = 1, @createmode_fordetach = 2
    SELECT @trunc_log_bit = 8
    SELECT @distbit = 16

    if (@createmode <> @createmode_fordetach)
    begin
    
        /* 
        ** Check if replication components are installed on this server
        */
        exec @retcode = dbo.sp_MS_replication_installed
        if (@retcode <> 1)
        begin
            return (1)
        end
    
        /* 
        ** Check for invalid security modes
        */
        IF @security_mode < 0 OR @security_mode > 1
        BEGIN
            RAISERROR(14109, 16, -1)
            RETURN (1)
        END

        IF ( ( @platform_nt != platform() & @platform_nt ) and @security_mode = 1)
        BEGIN
            RAISERROR(21038, 16, -1)
            RETURN (1)
        END
    
        /* 
        ** Check for invalid retention values 
        */
        IF @min_distretention < 0 OR @max_distretention < 0 
        BEGIN
            RAISERROR(14106, 16, -1)
            RETURN (1)
        END
        IF @min_distretention > @max_distretention
        BEGIN
            RAISERROR(14107, 16, -1) 
            RETURN (1)
        END

        /*
        ** Check to make sure this is a distributor
        */
        IF NOT EXISTS (SELECT * FROM master..sysservers
              WHERE UPPER(datasource) = UPPER(@@SERVERNAME) collate database_default
                 AND srvstatus & 8 <> 0)
        BEGIN
            RAISERROR (14114, 16, -1, @@SERVERNAME)
            RETURN(1)
        END
    
        /*
        ** Check if database is already configured as a distributor database
        */
        IF EXISTS (SELECT * FROM msdb..MSdistributiondbs WHERE name = @database collate database_default)
        BEGIN
            RAISERROR (14119, 16, -1, @database)
            RETURN(1)
        END    
    end

    /* 
    ** Get path to osql client (TOOLS) directory
    */
    EXECUTE @retcode = master.dbo.sp_MSgettools_path @osql_path OUTPUT
    IF ( @retcode <> 0 ) or ( @@ERROR <> 0 ) or ( @osql_path is NULL ) or ( @osql_path = '' )
    BEGIN
        GOTO UNDO       
    END

    /* 
    ** Get path to version specific INSTALL directory
    */
	exec @retcode = master.dbo.sp_MSget_setup_paths
		@sql_path = @install_path output,
		@data_path = @mssql_data_path output
    IF @retcode <> 0 or @install_path is NULL or @install_path='' or @mssql_data_path = ''
    BEGIN
        GOTO UNDO       
    END

    IF @data_folder IS NULL or @data_folder = ''
        select @data_folder = @mssql_data_path + '\DATA'

    IF @log_folder IS NULL or @log_folder = ''
        select @log_folder = @mssql_data_path + '\DATA'

    IF @data_file IS NULL
        SELECT @data_file = @database + '.MDF'

    IF @log_file IS NULL
        SELECT @log_file = @database + '.LDF'

    if substring(@data_folder, len(@data_folder), 1) = '\'
    select @data_folder = substring (@data_folder, 1, len(@data_folder) -1)
    if substring(@log_folder, len(@log_folder), 1) = '\'
    select @log_folder = substring (@log_folder, 1, len(@log_folder) -1)

    SELECT @data_path = @data_folder + '\' + @data_file
    SELECT @log_path = @log_folder + '\' + @log_file

    SELECT @data_path_quoted_for_copy = '"' + @data_folder + '\' + @data_file + '"'
    SELECT @log_path_quoted_for_copy = '"' + @log_folder + '\' + @log_file + '"'

    select @logical_data_file = @database

    /* 
    ** Truncate the logical log file name back to 128 characters
    ** long so the 'CREATE DATABASE' statement won't complain.
    */
    /* LEN(@logical_log_file) = LEN(@database) + LEN('_log') and
       LEN(@logical_log_file) <= 128 implies 
       LEN(@database) <=124 */
    IF (LEN(@database) > 124)
        SELECT @logical_log_file = SUBSTRING(@database, 1, 124) + '_log'  
    ELSE 
        SELECT @logical_log_file = @database + '_log'

    if (@createmode = @createmode_attach)
    begin
        select @canneddbdata_file = @mssql_data_path + '\DATA\DISTMDL.MDF'
        select @canneddblog_file = @mssql_data_path + '\DATA\DISTMDL.LDF'

        exec dbo.sp_MSget_file_existence @canneddbdata_file, @file_exists OUTPUT
        if (@file_exists = 0)
        begin
            /* Fallback to mode where instdist.sql needs to be run */
            select @createmode = @createmode_noattach
        end

        exec dbo.sp_MSget_file_existence @canneddblog_file, @file_exists OUTPUT
        if (@file_exists = 0)
        begin
            /* Fallback to mode where instdist.sql needs to be run */
            select @createmode = @createmode_noattach
        end
    end

    /*
    ** Create the distributor database if it does not exist
    */
    IF NOT EXISTS (SELECT * from master..sysdatabases WHERE name = @database collate database_default) AND (@createmode <> @createmode_attach)
    BEGIN

        -- Note: Use system's default file growth.
        IF @logical_data_file IS NOT NULL AND NOT EXISTS (SELECT * FROM master..sysdevices WHERE name = @logical_data_file collate database_default)
        BEGIN
            SELECT @on_clause = ' ON (NAME =''' + @logical_data_file + ''',FILENAME=''' + REPLACE( @data_path, '''', '''''' ) + 
                ''', SIZE=' + @data_file_size_str + ', MAXSIZE = UNLIMITED)'
        END

        IF @logical_log_file IS NOT NULL AND NOT EXISTS (SELECT * FROM master..sysdevices WHERE name = @logical_log_file collate database_default)
        BEGIN
            SELECT @logon_clause = ' LOG ON (NAME =''' + @logical_log_file + ''',FILENAME=''' + REPLACE( @log_path, '''', '''''' ) + 
                ''', SIZE=' + @log_file_size_str + ', MAXSIZE= UNLIMITED)'          
        END

        /*
        ** Create distributor database
        */
        SELECT @command = 'USE master  CREATE DATABASE ' +  QUOTENAME(@database) + 
            + isnull(@on_clause, ' ') + isnull(@logon_clause, ' ')

        EXEC (@command)
        IF @@ERROR <> 0
            RETURN (1)
        SELECT @db_exists = 0
    END
    ELSE IF NOT EXISTS (SELECT * from master..sysdatabases WHERE name = @database collate database_default) AND (@createmode = @createmode_attach)
    BEGIN
    /* DO THE CREATE DATABASE FOR ATTACH STUFF */
        
        exec dbo.sp_MSget_file_existence @data_path, @data_file_preexists OUTPUT
        if (@data_file_preexists = 1)
        begin
            raiserror(5170, 16, -1, @data_path)
            return 1
        end
    
        SELECT @on_clause = ' ON (NAME = ''' + @logical_data_file + ''', FILENAME=''' + REPLACE( @data_path, '''', '''''' ) + ''')'
        
        exec dbo.sp_MSget_file_existence @log_path, @log_file_preexists OUTPUT
        if (@log_file_preexists = 1)
        begin
            raiserror(5170, 16, -1, @log_path)
            return 1
        end

        SELECT @logon_clause = ' LOG ON (NAME = ''' + @logical_log_file + ''', FILENAME=''' + REPLACE( @log_path, '''', '''''' ) + ''')'

        select @filecopy_cmd = 'copy "' + @canneddbdata_file + '" ' + @data_path_quoted_for_copy
        EXEC @retcode = master..xp_cmdshell @filecopy_cmd, NO_OUTPUT
        IF @retcode <> 0 OR @@ERROR <> 0
        BEGIN
            RAISERROR (14113, 16, -1, @filecopy_cmd, 'instdist.out')
            return (1)
        END

        select @filecopy_cmd = 'copy "' + @canneddblog_file + '" ' + @log_path_quoted_for_copy
        EXEC @retcode = master..xp_cmdshell @filecopy_cmd, NO_OUTPUT
        IF @retcode <> 0 OR @@ERROR <> 0
        BEGIN
            RAISERROR (14113, 16, -1, @filecopy_cmd, 'instdist.out')
            return (1)
        END

        /*
        ** Create distributor database
        */
        SELECT @command = 'USE master  CREATE DATABASE ' +  QUOTENAME(@database) + 
            + @on_clause + @logon_clause + ' FOR ATTACH'

        EXEC (@command)
        IF @@ERROR <> 0
        begin
            RETURN (1)
        end
        dbcc dbreindexall(@database, 240) with no_infomsgs

        SELECT @db_exists = 0
    END
    ELSE
    BEGIN
        SELECT @db_exists = 1
    END

    -- Must make the dist db owned by sa so that the sps in it can select from
    -- security cache tables in tempdb by owership chain rule.
    declare @retcode2 int
    select @retcode2 = 0
    select @distproc = QUOTENAME(@database) + '.dbo.sp_executesql'
    SELECT @command = 
        -- If the db is created by sa or from attach, sa is dbo already.
        -- sp_changedbowner will fail is the new owner is an user in the db already.
        ' if not exists (select * from sysusers where sid = 0x01) ' +
        ' exec @retcode2 = dbo.sp_changedbowner ''sa'''
    EXEC @retcode = @distproc @command, N'@retcode2 int output', @retcode2 output
    IF @retcode <> 0 or @retcode2 <> 0 or @@ERROR <> 0
    BEGIN
        GOTO UNDO
    END

    /* Set the database option truncate log on checkpoint & turn off autoclose which is default of win9x*/
    IF EXISTS (SELECT * FROM master.dbo.sysdatabases WHERE 
        name = @database collate database_default AND
        (status & @trunc_log_bit) = 0 )   -- if its not already marked
    BEGIN
        EXEC @retcode = dbo.sp_dboption @database, 'trunc. log on chkpt.', 'true'
        IF @retcode <> 0 OR @@ERROR <> 0
        BEGIN
            GOTO UNDO
        END
    END

    EXEC @retcode = dbo.sp_dboption @database, 'autoclose', 'false'
        IF @retcode <> 0 OR @@ERROR <> 0
        BEGIN
            GOTO UNDO
        END

    /*
    **
    ** Update sysdatabase category bit
    ** This is to prevent user from dropping the database.
    **/
    if (@createmode <> @createmode_fordetach)
    begin
        UPDATE master..sysdatabases SET category = category | @distbit WHERE name = @database collate database_default
        IF @@ERROR <> 0
        BEGIN
            GOTO UNDO
        END
    end

    /* 
    ** Install instdist.sql
    */

    if (@createmode <> @createmode_attach) OR (@db_exists = 1)
    begin
		if (( platform() & @platform_nt = @platform_nt ))
			select @osql_for_nt = 1
		else
			select @osql_for_nt = 0

        -- Always use integrated security on WINNT since @login passed-in is for remote 
        -- subscriber and may not have enough privilege to apply the script
        IF (@security_mode = 1 or @osql_for_nt = 1) AND NOT (@security_mode = 0 AND @createmode = 2)
		BEGIN
            SELECT @osql_cmd = '" "' + @osql_path + '\binn\osql" -E '  
			if serverproperty('instancename') is not null
				SELECT @osql_cmd = @osql_cmd + ' -S"' + @@SERVERNAME + '" '
		END
        ELSE
		BEGIN
        -- cannot specify -S w/ -E for local execution, SID does not map 
		if (@osql_for_nt = 1)
            SELECT @osql_cmd = '" "' + @osql_path + '\binn\osql" -U"' + @login + '" -P"' + 
                isnull(@password,'') + '" -S"' + @@SERVERNAME + '" '
		else
			SELECT @osql_cmd = '"'   + @osql_path + '\binn\osql" -U"' + @login + '" -P"' + 
                isnull(@password,'') + '" -S"' + @@SERVERNAME + '" '
		END
    
        select @osql_cmd = @osql_cmd + '-l60 -t60 '

        -- We must use -b option to make osql return error code !!
        SELECT @osql_cmd = @osql_cmd + 
            ' -d"' + @database + '" -b ' +
            ' -i' + '"' + @install_path + '\install\instdist.sql"' + 
            ' -o' + '"' + @install_path + '\install\instdist.out"'

		if (@osql_for_nt = 1)
		BEGIN
	        SELECT @osql_cmd = @osql_cmd + ' "'
		END
    
        EXEC @retcode = master..xp_cmdshell @osql_cmd
        IF @retcode <> 0 OR @@ERROR <> 0
        BEGIN
            RAISERROR (14113, 16, -1, @osql_cmd, 'instdist.out')
            GOTO UNDO       
        END
    end
    
    if (@createmode <> @createmode_fordetach)
    begin
        /* Set db_existed bit in MSrepl_version */
        IF @db_exists = 1
        BEGIN
            SELECT @distproc = 'UPDATE ' +
                @database + '..MSrepl_version SET db_existed = 0x1'
    
            EXEC(@distproc)
            IF @@ERROR <> 0
            BEGIN
                GOTO UNDO       
            END
        END

        DELETE msdb.dbo.MSdistributiondbs WHERE name = @database collate database_default
        IF @@ERROR <> 0
        BEGIN
            GOTO UNDO       
        END

        INSERT INTO msdb.dbo.MSdistributiondbs VALUES (
            @database, @min_distretention, @max_distretention, @history_retention
            )
        IF @@ERROR <> 0
        BEGIN
            GOTO UNDO       
        END

        -- This login need db_owner priviledge to call sps in distribution db
        declare @distributor_login sysname
        select @distributor_login = 'distributor_admin'

        select @command = quotename(@database) + '.dbo.sp_MSrepl_dbrole'
        exec @retcode = @command 'db_owner', @distributor_login, 'add'
        IF @@error <> 0 OR @retcode <> 0
            GOTO UNDO

		if @from_scripting = 0
		begin
			/*
			** Create the history cleanup agent.
			*/
			SELECT @agentname = formatmessage (20567, @database)
			SELECT @command =  'EXEC dbo.sp_MShistory_cleanup @history_retention = ' + 
				CONVERT(nvarchar(12), @history_retention)

			IF EXISTS (SELECT * FROM msdb..sysjobs_view WHERE name = @agentname collate database_default and
				UPPER(originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName'))))
			BEGIN
				EXEC @retcode = msdb.dbo.sp_delete_job 
					@job_name = @agentname
				IF @@ERROR <> 0 or @retcode <> 0
				BEGIN
					GOTO UNDO
				END
			END

			set @description = formatmessage(20535)

			-- Get History Cleanup category name (assumes category_id = 12)
			select @category_name = name FROM msdb.dbo.syscategories where category_id = 12

			EXECUTE @retcode = dbo.sp_MSadd_repl_job @agentname,
			@subsystem = 'TSQL',
			@server = @@SERVERNAME,
			@databasename = @database,
			@description = @description,
			@freqtype = 4,    
			@freqsubtype = 4,         
			@freqsubinterval = 10,    /* Number of minutes between runs */ 
			@command = @command,
			@enabled = 1,
			@retryattempts = 0,
			@loghistcompletionlevel = 0,
			@category_name = @category_name
    
			IF @@ERROR <> 0 or @retcode <> 0
			BEGIN
				GOTO UNDO
			END

			/*
			** Create the distribution cleanup agent.
			*/
			SELECT @agentname = formatmessage (20568, @database)
			SELECT @command =  'EXEC dbo.sp_MSdistribution_cleanup @min_distretention = ' + 
				CONVERT(nvarchar(12), @min_distretention) + ', @max_distretention = ' +
				CONVERT(nvarchar(12), @max_distretention)

			IF EXISTS (SELECT * FROM msdb..sysjobs_view WHERE name = @agentname collate database_default and
				UPPER(originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName'))))
			BEGIN
				EXEC @retcode = msdb.dbo.sp_delete_job 
					@job_name = @agentname
				IF @@ERROR <> 0 or @retcode <> 0
				BEGIN
					GOTO UNDO
				END
			END

			set @description = formatmessage(20541)
			-- Get Distribution Cleanup category name (assumes category_id = 11)
			select @category_name = name FROM msdb.dbo.syscategories where category_id = 11
    
			EXECUTE @retcode = msdb.dbo.sp_MSadd_repl_job @agentname,
			@subsystem = 'TSQL',
			@server = @@SERVERNAME,
			@databasename = @database,
			@description = @description,
			@freqtype = 4,    
			@freqsubtype = 4,         
			@freqsubinterval = 10,    /* Number of minutes between runs */ 
			@command = @command,
			@retryattempts = 0,
			@enabled = 0,
			@loghistcompletionlevel = 0,
			@category_name = @category_name,
			-- Start  and end time is 5 min off from the history cleanup, which use the default.
			@activestarttimeofday = 000500,
			@activeendtimeofday   = 000459

			IF @@ERROR <> 0 or @retcode <> 0
			BEGIN
				GOTO UNDO
			end
		end
    end
    else
    begin
        /*detach */
        dbcc detachdb(@database)
    end
    
    RETURN(0)

UNDO:

    IF @db_exists = 0
        EXECUTE dbo.sp_dropdistributiondb @database

    /* Need to do it since sp_dropdistributiondb will fail in some cases */
    UPDATE master..sysdatabases SET category = category & ~@distbit WHERE name = @database collate database_default
    
    DELETE msdb.dbo.MSdistributiondbs where name = @database collate database_default

    /* drop the database and ignore error */
    IF @db_exists = 0 AND
        EXISTS (SELECT * from master..sysdatabases WHERE name = @database collate database_default)
    BEGIN
        SELECT @command = 'USE master  DROP DATABASE ' +  QUOTENAME(@database) 
        EXEC (@command)
    END

    if (@createmode = @createmode_attach)
    begin
        if (@data_file_preexists = 0)
        begin
            select @command = 'del ' + @data_path_quoted_for_copy
            exec master..xp_cmdshell @command
            --ignore errors
        end
        if (@log_file_preexists = 0)
        begin
            select @command = 'del ' + @log_path_quoted_for_copy
            exec master..xp_cmdshell @command
            --ignore errors
        end
    end
        
    RETURN(1)        
GO
 
raiserror('Creating procedure sp_MScreate_replication_checkup_agent', 0,1)
go
create procedure sp_MScreate_replication_checkup_agent
@heartbeat_interval int = 10    -- minutes
as
    declare @command nvarchar(100)
    declare @retcode int
    declare @interval int
    declare @job_name nvarchar(100)
    declare @description nvarchar(100)
    declare @category_name sysname

    select @command = 'sp_replication_agent_checkup @heartbeat_interval = ' +
        convert(nvarchar(10), @heartbeat_interval)      
        
    -- Create job if it already exists
    select @job_name = formatmessage(20533)

    IF EXISTS (SELECT * FROM msdb..sysjobs_view WHERE name = @job_name collate database_default and
        UPPER(originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName'))))
    BEGIN
        EXEC @retcode = msdb.dbo.sp_delete_job  @job_name = @job_name
        IF @@ERROR <> 0 or @retcode <> 0
        BEGIN
            return (1)            
        END
    END

    -- Create new job
    set @interval = convert(int, @heartbeat_interval)
    set @description = formatmessage(20534)

    -- Get Checkup category name (assumes category_id = 16)
    select @category_name = name FROM msdb.dbo.syscategories where category_id = 16
    EXECUTE @retcode = dbo.sp_MSadd_repl_job 
            @name = @job_name,
            @subsystem = 'TSQL', 
            @enabled = 1, 
            @command = @command,
            @description = @description,
            @freqtype = 4,
            @freqinterval = 1,
            @freqsubtype = 4,
            @freqsubinterval = @interval,
            @retryattempts = 0,
            @category_name = @category_name
    if @@ERROR <> 0 or @retcode <> 0
        return (1)
go

raiserror('Creating procedure sp_vupgrade_subpass', 0,1)
GO
create procedure sp_vupgrade_subpass
as
begin
	/* 
	 * Upgrade replication ftp_password and distributor_passwords
	 * in subscriber database.
	 *
	 * Setup version upgrade procedure call order:
	 *	sp_vupgrade_replication -> sp_vupgrade_subscription_databases -> sp_vupgrade_subpass
	*/
	begin transaction

	/*
	 * MSsubscription_properties
	*/
	if exists (select name from sysobjects where name='MSsubscription_properties')
	begin
		if not exists (select * from syscolumns where id = Object_Id('MSsubscription_properties') and name = 'ftp_password' and length = '1048')
		begin
			declare @dbname sysname
			declare @cmptlevel tinyint
			set @dbname = db_name()
			select @cmptlevel = cmptlevel from master..sysdatabases where name = @dbname collate database_default
			if @cmptlevel < 70
			begin
                                raiserror (15048, -1, -1, 70, 70, 70, 80)
			end
			else
			begin
			        /*
				 * alter ftp_password column from sysname to nvarchar(524)
				*/
				exec( 'alter table MSsubscription_properties alter column ftp_password nvarchar(524)' )
				exec( 'alter table MSsubscription_properties alter column distributor_password nvarchar(524)' )
				exec( 'alter table MSsubscription_properties alter column publisher_password nvarchar(524)' )
				exec( 'alter table MSsubscription_properties alter column dts_package_password nvarchar(524)' )
			end

			/*
			 * convert all the ftp_passwords to new encryption
			 */
			declare @ftp_password nvarchar(524)
			declare @distributor_password nvarchar(524)
			declare @publisher_password nvarchar(524)
			declare @dts_package_password nvarchar(524)
			declare @retcode int

			declare cur_MSsubscription_properties CURSOR LOCAL FORWARD_ONLY for 
			select ftp_password, distributor_password, publisher_password, dts_package_password
			from MSsubscription_properties
			for update of ftp_password, distributor_password, publisher_password, dts_package_password
		
			open cur_MSsubscription_properties
			fetch next from cur_MSsubscription_properties into @ftp_password, @distributor_password, @publisher_password, @dts_package_password
			while ( @@fetch_status <> -1 )
			begin
				EXEC @retcode = master.dbo.xp_repl_convert_encrypt @ftp_password OUTPUT
				IF @@error <> 0 OR @retcode <> 0
				begin
					rollback transaction
					return 1
				end
      				
				EXEC @retcode = master.dbo.xp_repl_convert_encrypt @distributor_password OUTPUT
				IF @@error <> 0 OR @retcode <> 0
				begin
					rollback transaction
					return 1
				end
	
				EXEC @retcode = master.dbo.xp_repl_convert_encrypt @publisher_password OUTPUT
				IF @@error <> 0 OR @retcode <> 0
				begin
					rollback transaction
					return 1
				end

				EXEC @retcode = master.dbo.xp_repl_convert_encrypt @dts_package_password OUTPUT
				IF @@error <> 0 OR @retcode <> 0
				begin
					rollback transaction
					return 1
				end
       	
				update MSsubscription_properties
				set ftp_password=@ftp_password, distributor_password=@distributor_password, publisher_password=@publisher_password, dts_package_password=@dts_package_password
				where current of cur_MSsubscription_properties
		
			        fetch next from cur_MSsubscription_properties into @ftp_password, @distributor_password, @publisher_password, @dts_package_password
			end
		end
	end
	commit transaction
end
GO
exec sp_MS_marksystemobject 'sp_vupgrade_subpass'
go

raiserror('Creating procedure sp_vupgrade_replmsdb', 0,1)
go
create procedure sp_vupgrade_replmsdb 
as
begin
/* 
 * Process schema and metadata changes specific to a msdb database. Updates datatype
 * mappings for heterogeneous replication.
 *
 * Setup version upgrade procedure call order:
 *	sp_vupgrade_replication -> sp_vupgrade_replmsdb (only called at distributors)
*/

	set nocount on 

	declare @retcode int
			,@profile_id int
			,@table_name sysname
			,@profile_name nvarchar(100)
			,@profile_desc nvarchar(100)

	-- raiserror('sp_vupgrade_replmsdb', 0,1)
	
	-- Drop and regenerate agent parameters and associated values from system profiles.
	select @profile_id = 1
	while (@profile_id < 8)
	begin

		exec @retcode = dbo.sp_drop_agent_parameter @profile_id
	    if (@retcode = 1)
	        return (1)

		exec @retcode = dbo.sp_generate_agent_parameter @profile_id
	    if (@retcode = 1)
	        return (1)

		select @profile_id = @profile_id + 1

	end
	
	declare hUserProfile CURSOR LOCAL FAST_FORWARD FOR
        select distinct profile_id from msdb..MSagent_profiles where profile_id >= 8 for read only   
	open hUserProfile 
	fetch hUserProfile into @profile_id

	while @@fetch_status <> -1
    	begin
		exec @retcode = dbo.sp_drop_agent_parameter @profile_id, @parameter_name = '-ReadBatchThreshold'
		if (@retcode = 1)
		begin
			close hUserProfile 
			deallocate hUserProfile 
	        	return (1)
		end

		fetch hUserProfile into @profile_id
    	end    
    	close hUserProfile 
    	deallocate hUserProfile 

	--
	-- Add 8.0 agent profiles
	--

    /* 
	** Distribution agent : Synchronization Manager Profile
	*/
    select @profile_id = NULL
		,@profile_name = formatmessage(20550) -- Dist SyncMgr Profile
		,@profile_desc = formatmessage(20551)
	select @profile_id = profile_id from msdb..MSagent_profiles 
		where agent_type = 3 and profile_name = @profile_name
	if (@profile_id is null)
	begin
		--
		-- Add profile
		--
		select @profile_id = NULL

		exec @retcode = dbo.sp_add_agent_profile
				@profile_id = @profile_id OUT,
				@profile_name = @profile_name,
				@agent_type = 3,   -- 1-Snapshot, 2-Logreader, 3-Distribution, 4-Merge
				@profile_type = 0,   -- 0-System, 1-Custom 
				@description = @profile_desc,
				@default = 0
		if (@retcode = 1 or @@ERROR <> 0)
			return (1)
	end
	else
	begin
		--
		-- Profile exists - drop the parameters for regeneration
		--
		exec @retcode = dbo.sp_drop_agent_parameter @profile_id
		if (@retcode = 1)
			return (1)
	end
    exec @retcode = dbo.sp_generate_agent_parameter 
		@profile_id = 10,
		@real_profile_id = @profile_id
    if (@retcode = 1 or @@ERROR <> 0)
		return (1)


	-- Queue default profile
	select @profile_id = NULL
	select @profile_id = profile_id from msdb..MSagent_profiles 
		where agent_type = 9 and def_profile = 1
	if (@profile_id is null)
	begin
		--
		-- Add profile
		--
		select @profile_id = NULL
				,@profile_name = formatmessage(20545) -- Default QueueReader Profile
				,@profile_desc = formatmessage(20589)

		exec @retcode = dbo.sp_add_agent_profile
			@profile_id = @profile_id OUT,
			@profile_name = @profile_name,
			@agent_type = 9,
			@profile_type = 0, 
			@description = @profile_desc,
			@default = 1
		if (@retcode = 1 or @@ERROR <> 0)
			return (1)
	end
	else
	begin
		--
		-- Profile exists - drop the parameters for regeneration
		--
		exec @retcode = dbo.sp_drop_agent_parameter @profile_id
		if (@retcode = 1)
			return (1)
	end
	exec @retcode = dbo.sp_generate_agent_parameter 
		@profile_id = 11,
		@real_profile_id = @profile_id
	if (@retcode = 1 or @@ERROR <> 0)
		return (1)

	--
	-- Add 'skip data consistency error' profile
	--
	select @profile_id = NULL
		,@profile_name = formatmessage(20599) -- Default Distribution Profile
		,@profile_desc = formatmessage(20600)
	select @profile_id = profile_id from msdb..MSagent_profiles 
		where agent_type = 3 and profile_name = @profile_name
	if (@profile_id is null)
	begin
		--
		-- Add profile
		--
		select @profile_id = NULL

		exec @retcode = dbo.sp_add_agent_profile
				@profile_id = @profile_id OUT,
				@profile_name = @profile_name,
				@agent_type = 3,   -- 1-Snapshot, 2-Logreader, 3-Distribution, 4-Merge
				@profile_type = 0,   -- 0-System, 1-Custom 
				@description = @profile_desc,
				@default = 0
		if (@retcode = 1 or @@ERROR <> 0)
			return (1)
	end
	else
	begin
		--
		-- Profile exists - drop the parameters for regeneration
		--
		exec @retcode = dbo.sp_drop_agent_parameter @profile_id
		if (@retcode = 1)
			return (1)
	end
	exec @retcode = dbo.sp_generate_agent_parameter 
		@profile_id = 14,
		@real_profile_id = @profile_id
	if (@retcode = 1 or @@ERROR <> 0)
		return (1)
		
    /* 
	** Merge agent : Non default profile for disconnected scenarios ( unreliable link ) 
	*/
    set @profile_id = NULL
	set @profile_name = formatmessage(20548) -- Non-Default Merge Profile
    set @profile_desc = formatmessage(20549)

	select @profile_id = profile_id from msdb..MSagent_profiles where agent_type = 4 and profile_name = @profile_name
	if (@profile_id is null)
	begin
	    exec @retcode = dbo.sp_add_agent_profile
            @profile_id = @profile_id OUT,
            @profile_name = @profile_name,
            @agent_type = 4,   -- 1-Snapshot, 2-Logreader, 3-Distribution, 4-Merge
            @profile_type = 0,   -- 0-System, 1-Custom 
            @description = @profile_desc,
            @default = 0
	end
	else
	begin
		--
		-- Profile exists - drop the parameters for regeneration
		--
		exec @retcode = dbo.sp_drop_agent_parameter @profile_id
		if (@retcode = 1)
			return (1)
	end

	exec @retcode = dbo.sp_generate_agent_parameter 7, @profile_id
	if (@retcode = 1 or @@ERROR <> 0)
		return (1)

    /* 
	** Merge agent : Non default profile for verbose histroy
	*/
    set @profile_id = NULL
    set @profile_name = formatmessage(20546) -- Verbose Merge Profile
    set @profile_desc = formatmessage(20547)

	select @profile_id = profile_id from msdb..MSagent_profiles where agent_type = 4 and profile_name = @profile_name
	if (@profile_id is null)
	begin
	    exec @retcode = dbo.sp_add_agent_profile
            @profile_id = @profile_id OUT,
            @profile_name = @profile_name,
            @agent_type = 4,   -- 1-Snapshot, 2-Logreader, 3-Distribution, 4-Merge
            @profile_type = 0,   -- 0-System, 1-Custom 
            @description = @profile_desc,
            @default = 0
	end
	else
	begin
		--
		-- Profile exists - drop the parameters for regeneration
		--
		exec @retcode = dbo.sp_drop_agent_parameter @profile_id
		if (@retcode = 1)
			return (1)
	end

	exec @retcode = dbo.sp_generate_agent_parameter 8, @profile_id
	if (@retcode = 1 or @@ERROR <> 0)
		return (1)

    /* 
	** Merge agent : Synchronization Manager Profile
	*/
    set @profile_id = NULL
    set @profile_name = formatmessage(20550) -- SyncMgr Profile
    set @profile_desc = formatmessage(20551)

	select @profile_id = profile_id from msdb..MSagent_profiles where agent_type = 4 and profile_name = @profile_name
	if (@profile_id is null)
	begin
	    exec @retcode = dbo.sp_add_agent_profile
            @profile_id = @profile_id OUT,
            @profile_name = @profile_name,
            @agent_type = 4,   -- 1-Snapshot, 2-Logreader, 3-Distribution, 4-Merge
            @profile_type = 0,   -- 0-System, 1-Custom 
            @description = @profile_desc,
            @default = 0
	end
	else
	begin
		--
		-- Profile exists - drop the parameters for regeneration
		--
		exec @retcode = dbo.sp_drop_agent_parameter @profile_id
		if (@retcode = 1)
			return (1)
	end

	exec @retcode = dbo.sp_generate_agent_parameter 9, @profile_id
	if (@retcode = 1 or @@ERROR <> 0)
		return (1)

	 /* 
	** Merge agent : Rowcount Validation profile  
	*/
    set @profile_id = NULL
    set @profile_name = formatmessage(21308) -- Rowcount Validation Profile
    set @profile_desc = formatmessage(21309) -- Rowcount Validation Profile Description

	select @profile_id = profile_id from msdb..MSagent_profiles where agent_type = 4 and profile_name = @profile_name
	if (@profile_id is null)
	begin
	    exec @retcode = dbo.sp_add_agent_profile
            @profile_id = @profile_id OUT,
            @profile_name = @profile_name,
            @agent_type = 4,   -- 1-Snapshot, 2-Logreader, 3-Distribution, 4-Merge
            @profile_type = 0,   -- 0-System, 1-Custom 
            @description = @profile_desc,
            @default = 0
	end
	else
	begin
		--
		-- Profile exists - drop the parameters for regeneration
		--
		exec @retcode = dbo.sp_drop_agent_parameter @profile_id
		if (@retcode = 1)
			return (1)
	end

	exec @retcode = dbo.sp_generate_agent_parameter 12, @profile_id
	if (@retcode = 1 or @@ERROR <> 0)
		return (1)

    /* 
	** Merge agent : Rowcount & Checksum Validation profile  
	*/
    set @profile_id = NULL
    set @profile_name = formatmessage(21310) -- Rowcount & Checksum Validation Profile
    set @profile_desc = formatmessage(21311) -- Rowcount & Checksum Validation Profile Description
	
	select @profile_id = profile_id from msdb..MSagent_profiles where agent_type = 4 and profile_name = @profile_name
	if (@profile_id is null)
	begin
	    exec @retcode = dbo.sp_add_agent_profile
            @profile_id = @profile_id OUT,
            @profile_name = @profile_name,
            @agent_type = 4,   -- 1-Snapshot, 2-Logreader, 3-Distribution, 4-Merge
            @profile_type = 0,   -- 0-System, 1-Custom 
            @description = @profile_desc,
            @default = 0
	end
	else
	begin
		--
		-- Profile exists - drop the parameters for regeneration
		--
		exec @retcode = dbo.sp_drop_agent_parameter @profile_id
		if (@retcode = 1)
			return (1)
	end

	exec @retcode = dbo.sp_generate_agent_parameter 13, @profile_id
	if (@retcode = 1 or @@ERROR <> 0)
		return (1)

	/* 
	** Merge agent : High volume server-to-server profile
	*/
    set @profile_id = NULL
    set @profile_name = formatmessage(20616) -- High volume server-to-server profile
    set @profile_desc = formatmessage(20617) -- High volume server-to-server profile Description

	select @profile_id = profile_id from msdb..MSagent_profiles where agent_type = 4 and profile_name = @profile_name
	if (@profile_id is null)
	begin
	    exec @retcode = dbo.sp_add_agent_profile
            @profile_id = @profile_id OUT,
            @profile_name = @profile_name,
            @agent_type = 4,   -- 1-Snapshot, 2-Logreader, 3-Distribution, 4-Merge
            @profile_type = 0,   -- 0-System, 1-Custom 
            @description = @profile_desc,
            @default = 0
	end
	else
	begin
		--
		-- Profile exists - drop the parameters for regeneration
		--
		exec @retcode = dbo.sp_drop_agent_parameter @profile_id
		if (@retcode = 1)
			return (1)
	end

	exec @retcode = dbo.sp_generate_agent_parameter 15, @profile_id
	if (@retcode = 1 or @@ERROR <> 0)
		return (1)

	-- Add MSdatatype_mappings table and default mappings
    if not exists( select * from msdb..sysobjects where name = 'MSdatatype_mappings' 
        and xtype = 'U')
    begin
