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

sp1_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

--------------------------------------------------------------------------------
--.	Common system objects (replcom.sql)
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
--. sp_MSdrop_replcom
--------------------------------------------------------------------------------
grant exec on xp_mapdown_bitmap to public
go

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

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

create procedure sp_MSdrop_replcom
as
    if exists (select * from sysobjects 
        where type = 'P'
            and name = 'sp_MScreate_distributor_tables')
        drop procedure sp_MScreate_distributor_tables

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

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

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

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

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

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

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

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

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

    IF EXISTS (SELECT * FROM sysobjects
            WHERE type = 'P'
                AND name = 'sp_MScreate_dist_tables')
        DROP PROCEDURE sp_MScreate_dist_tables

	IF EXISTS (SELECT * FROM sysobjects
			WHERE type = 'P'
				AND name = 'sp_MSupdate_mqserver_distdb')
		DROP PROCEDURE sp_MSupdate_mqserver_distdb

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


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

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

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

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

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

    if exists (select * from sysobjects
            where type = 'P'
                and name = 'sp_helppublicationsync')    /* Remove old version */
        drop procedure sp_helppublicationsync

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    if exists (select * from sysobjects
            where type = 'P'
                and name = 'sp_helpdistributor_properties')
        drop procedure sp_helpdistributor_properties
 
    if exists (select * from sysobjects
            where type = 'X'
                and name = 'sp_repldone')
        exec dbo.sp_dropextendedproc 'sp_repldone'

    if exists (select * from sysobjects
            where type = 'X'
                and name = 'sp_repltrans')
        exec dbo.sp_dropextendedproc 'sp_repltrans'

    if exists (select * from sysobjects
            where type = 'X'
                and name = 'sp_replcmds')
        exec dbo.sp_dropextendedproc 'sp_replcmds'

    if exists (select * from sysobjects
            where type = 'X'
                and name = 'sp_replcounters')
        exec dbo.sp_dropextendedproc 'sp_replcounters'

    if exists (select * from sysobjects
            where type = 'X'
                and name = 'sp_replflush')
        exec dbo.sp_dropextendedproc 'sp_replflush'

    if exists (select * from sysobjects
            where type = 'X'
                 and name = 'sp_replpostcmd' )
        exec dbo.sp_dropextendedproc 'sp_replpostcmd'

    if exists (select * from sysobjects
            where type = 'X'
                 and name = 'sp_replpostschema' )
        exec dbo.sp_dropextendedproc 'sp_replpostschema'

    if exists (select * from sysobjects
            where type = 'X'
                 and name = 'sp_replincrementlsn' )
        exec dbo.sp_dropextendedproc 'sp_replincrementlsn'

    if exists (select * from sysobjects
            where type = 'X'
                 and name = 'sp_replupdateschema' )
        exec dbo.sp_dropextendedproc 'sp_replupdateschema'

    if exists (select * from sysobjects
            where type = 'X'
                 and name = 'sp_replsetoriginator' )
        exec dbo.sp_dropextendedproc 'sp_replsetoriginator'

    if exists (select * from sysobjects
            where type = 'X'
                 and name = 'sp_replsetsyncstatus' )
        exec dbo.sp_dropextendedproc 'sp_replsetsyncstatus'

    if exists (select * from sysobjects
            where type = 'X'
                 and name = 'sp_replpostsyncstatus' )
        exec dbo.sp_dropextendedproc 'sp_replpostsyncstatus'

    if exists (select * from sysobjects
            where type = 'X'
                and name = 'xp_enumdsn')
        exec dbo.sp_dropextendedproc 'xp_enumdsn'

    if exists (select * from sysobjects
            where type = 'X'
                and name = 'xp_oledbinfo')
        exec dbo.sp_dropextendedproc 'xp_oledbinfo'

    if exists (select * from sysobjects
            where type = 'X'
                and name = 'xp_dsninfo')
        exec dbo.sp_dropextendedproc 'xp_dsninfo'

    if exists (select * from sysobjects
            where type = 'X'
                and name = 'xp_repl_encrypt')
        exec dbo.sp_dropextendedproc 'xp_repl_encrypt'

    if exists (select * from sysobjects
            where type = 'X'
                and name = 'xp_repl_convert_encrypt')
        exec dbo.sp_dropextendedproc 'xp_repl_convert_encrypt'

    if exists (select * from sysobjects
            where type = 'X'
                and name = 'xp_repl_help_connect')
        exec dbo.sp_dropextendedproc 'xp_repl_help_connect'

     if exists (select * from sysobjects
            where type = 'X'
                and name = 'xp_replproberemsrv')
        exec dbo.sp_dropextendedproc 'xp_replproberemsrv'

    -- sp_helpsubscriber is removed permanently from the system.
    if exists (select * from sysobjects
        where type = 'P'
                and name = 'sp_helpsubscriber')
        drop procedure sp_helpsubscriber

	-- sp_MSrepl_encrypt obsolete; use xp_repl_encrypt
    if exists (select * from sysobjects
        where type = 'P'
                and name = 'sp_MSrepl_encrypt')
        drop procedure sp_MSrepl_encrypt

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

	-- Common conflict viewer support procs
    if exists (select * from sysobjects
            where type = 'P'
                and name = 'sp_MShelpconflictpublications')
        drop procedure sp_MShelpconflictpublications

    -- Procs for managing dynamic snapshot views
    if exists (select * from sysobjects
            where type = 'P'
                and name = 'sp_MScleanupdynsnapshotvws')
        drop procedure sp_MScleanupdynsnapshotvws

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

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

    if exists (select * from sysobjects
            where type = 'FN'
                and name = 'fn_replgetagentcommandlinefromjobid')
        drop function system_function_schema.fn_replgetagentcommandlinefromjobid
    
    if exists (select * from sysobjects
            where type = 'P'
                and name = 'sp_replproberemoteserver')
        drop procedure sp_replproberemoteserver

    if exists (select * from sysobjects
            where type = 'FN'
                and name = 'fn_getpersistedservernamecasevariation'
                and user_id('system_function_schema') = uid)
        drop function system_function_schema.fn_getpersistedservernamecasevariation

GO

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

-- This procedure is called to upgrade the MSMQ related entries in 
-- MSdistribution_agents in distribution database - this processing 
-- cannot be done directly inside sp_MScreate_dist_tables or sp_vupgrade_distdb
-- since we need the queue_server column for the processing and we can
-- get a syntax error if the table exists without the column
--
-- Assumption : MSsubscription_agents has been created 
--				and column queue_server exists
--
raiserror('Creating procedure sp_MSupdate_mqserver_distdb', 0,1)
go
create procedure sp_MSupdate_mqserver_distdb
as
begin
	if exists (select * from MSdistribution_agents
		where queue_id IS NOT NULL and queue_id != N'mssqlqueue' and queue_server IS NULL)
	begin
		--
		-- we have entries for active Queued subscriptions using MSMQ that need upgrade
		--
		declare @queue_server sysname

		--
		-- prepare the queue server name
		--
		select @queue_server = @@servername
		if (charindex(N'\', @queue_server) > 0)
			select @queue_server = substring(@queue_server, 1, charindex(N'\', @queue_server) - 1)

		--
		-- update the queue_server column for these entries
		--
		update MSdistribution_agents
		set queue_server = @queue_server
		where queue_id IS NOT NULL and queue_id != N'mssqlqueue' and queue_server IS NULL
	end
end
go


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

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

CREATE PROCEDURE sp_helpreplicationdb
        @dbname sysname = '%', @type sysname = 'pub'
    AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    DECLARE @retcode int, @typebit int

    if (lower(@type collate SQL_Latin1_General_CP1_CS_AS) like 'pub%')
       select @typebit = 1
    else if (lower(@type collate SQL_Latin1_General_CP1_CS_AS) like 'sub%')
       select @typebit = 2
    else
    begin
       raiserror(14091,-1,-1)
       return 1
    end

    /*
    ** Parameter Check:  @dbname.
    ** Check to make sure that the database name conforms to the rules
    ** for identifiers.
    */

    IF @dbname <> '%'
       BEGIN
          EXECUTE @retcode = dbo.sp_validname @dbname

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

    /*
    ** Show databases with this option enabled.
    */

    SELECT name
      FROM master..sysdatabases
     WHERE ((@dbname = N'%') or (name = @dbname collate database_default)) 
       AND (category & @typebit) <> 0
go
 
grant execute on dbo.sp_helpreplicationdb to public
go


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

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

create proc sp_MSUpgradeConflictTable
	@skip_procgen bit = 0 
AS
declare @retcode int
declare @conflict_table sysname
declare @pubid uniqueidentifier 
declare @artid uniqueidentifier 
declare @ins_conflict_proc sysname
declare @conflict_id	int
declare @objid			int
declare @qual_conflict	nvarchar(132)


if exists( select * from sysobjects where name = 'MSmerge_delete_conflicts' ) and 
	not exists (select * from syscolumns where id = object_id('MSmerge_delete_conflicts') and name = 'create_time')
begin
	alter table MSmerge_delete_conflicts add create_time datetime not null default getdate()
end

declare AC CURSOR LOCAL FAST_FORWARD for select conflict_table, objid, ins_conflict_proc, pubid, artid from sysmergearticles 
open AC
fetch AC into @conflict_table, @objid, @ins_conflict_proc, @pubid, @artid
while (@@fetch_status<>-1)
begin
	if @conflict_table is NOT null
	begin
		select @conflict_id = object_id(@conflict_table)
		select @qual_conflict=QUOTENAME(@conflict_table)
		/*
		** Upgrade conflict table so that it can get cleaned up later on
		*/
		if @conflict_id is not NULL and not exists (select name from syscolumns where name='MSrepl_create_time' and id=@conflict_id)
		begin
			exec ('alter table ' + @qual_conflict + ' add MSrepl_create_time datetime not null default getdate() ')
			if @@ERROR<>0
			begin
				close AC
				deallocate AC
				return (1)
			end
			/*
			** use create_time value for the new column before dropping create_time column, after making 
			** sure create_time is not from user table.
			*/
			if exists (select name from syscolumns where name='create_time' and id=@conflict_id) and
				not exists (select name from syscolumns where name='create_time' and id=@objid) 
			begin
				exec ('update ' + @qual_conflict + ' set MSrepl_create_time=create_time')
				if @@ERROR<>0
				begin
					close AC
					deallocate AC
					return (1)
				end
				declare @colid 					int
				declare @default_constraint		sysname
				
				select @colid = colid from syscolumns where name='create_time' and id=@conflict_id
				select @default_constraint=object_name(constid) from sysconstraints where id=@conflict_id and colid=@colid
				/*
				** drop default constrait on create_time column
				*/
				if @default_constraint is not NULL
				begin
					exec ('alter table ' + @qual_conflict + ' drop constraint ' + @default_constraint)
					if @@ERROR<>0
					begin
						close AC
						deallocate AC
						return (1)
					end
				end
				
				exec ('alter table ' + @qual_conflict + ' drop column create_time')
				if @@ERROR<>0
				begin
					close AC
					deallocate AC
					return (1)
				end
			end
			if @ins_conflict_proc is not NULL and object_id(@ins_conflict_proc) is not NULL 
			begin
				exec ('drop procedure ' + @ins_conflict_proc)
				if @@ERROR<>0
		        begin
					close AC
					deallocate AC	        
	    	    	return (1)
	    	    end
				--force to regenerate conflict insert proc to pick up the new column name
				update sysmergearticles set ins_conflict_proc=NULL where artid=@artid and pubid=@pubid
				if @@ERROR<>0
		        begin
					close AC
					deallocate AC	        
	    	    	return (1)
	    	    end

				-- Do not upgrade conflict tables if this is called from sp_restoredbreplication, as 
				-- the entire replication will be striped out.
				if @skip_procgen=0
				begin
					exec @retcode = sp_MSgetconflictinsertproc @artid, @pubid, 0 	
					if @@ERROR<>0 or @retcode<>0
			        begin
						close AC
						deallocate AC	        
		    	    	return (1)
		    	    end
	    	    end
			end
		end

		if not exists (select * from sysindexes where id = @conflict_id and keys is not null)
		begin
			declare @rgcol sysname
			declare @indname sysname
			declare @quotedname sysname
			declare @owner sysname
			
			select @owner=user_name(uid) from sysobjects where id = @objid
			select @rgcol = QUOTENAME(name) from syscolumns where id = @objid and
    	            ColumnProperty(id, name, 'isrowguidcol') = 1
        	select @indname = 'uc_' + @conflict_table
	        set @indname = QUOTENAME(@indname)
    	    set @quotedname = QUOTENAME(@owner) + '.' + QUOTENAME(@conflict_table)
        	exec ('Create unique clustered index ' + @indname + ' on ' + @quotedname +
        		' (' + @rgcol + ', origin_datasource)' )
	        if @@ERROR <> 0
	        begin
				close AC
				deallocate AC	        
    	    	return (1)
    	    end
		end
	end
	fetch next from AC into @conflict_table, @objid, @ins_conflict_proc, @pubid, @artid
end
close AC
deallocate AC
return (0)
GO
exec dbo.sp_MS_marksystemobject sp_MSUpgradeConflictTable
Go

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

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

CREATE PROCEDURE sp_dropsubscriber (
    @subscriber sysname,        /* The name of the subscriber */
    @reserved nvarchar(50) = NULL,
    @ignore_distributor bit = 0
        ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    DECLARE @db_name sysname
    DECLARE @foundSubscription int
    DECLARE @command nvarchar(255)
    DECLARE @transpublishdb_bit int
    DECLARE @mergepublishdb_bit int
    DECLARE @distributor sysname
    DECLARE @distribdb sysname
    DECLARE @distproc nvarchar (300)
    DECLARE @retcode int
    DECLARE @type nvarchar(10)

    SELECT @transpublishdb_bit = 1
    SELECT @mergepublishdb_bit = 4


    /*
    ** Parameter Check:  @subscriber.
    ** Check to make sure that the subscriber exists.
    */

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

    EXECUTE @retcode = dbo.sp_validname @subscriber

    IF @retcode <> 0
    RETURN (1)

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

        BEGIN
            RAISERROR (14048, 16, -1, @subscriber)
            RETURN (1)
        END

    if lower(@subscriber) = 'all'
    begin
        DECLARE hCdropsubscriber_all CURSOR LOCAL FAST_FORWARD FOR
            SELECT srvname
                     FROM master..sysservers
                    WHERE srvstatus & 4 <> 0
        FOR READ ONLY
        OPEN hCdropsubscriber_all
        FETCH hCdropsubscriber_all INTO @subscriber
    
        WHILE (@@fetch_status <> -1)
        BEGIN
            EXECUTE @retcode  = dbo.sp_dropsubscriber
                @subscriber = @subscriber,    
                @ignore_distributor = @ignore_distributor,
                @reserved = @reserved
            IF @@ERROR <> 0 OR @retcode <> 0
            BEGIN
                CLOSE hCdropsubscriber_all
                DEALLOCATE hCdropsubscriber_all
                RETURN (1)
            END
            FETCH hCdropsubscriber_all INTO @subscriber
        end
        
        CLOSE hCdropsubscriber_all
        DEALLOCATE hCdropsubscriber_all

        return(0)
    end


    /*
    ** There should be no subscription by the subscriber
    ** Open a cursor the published databases.
    **
    */
    DECLARE hCdropsubscriber CURSOR LOCAL FAST_FORWARD FOR
            SELECT name, N'tran' FROM master..sysdatabases      
                WHERE (category & @transpublishdb_bit) <> 0 
        UNION
            select name, N'merge' from master..sysdatabases      
                WHERE (category & @mergepublishdb_bit) <> 0 
    FOR READ ONLY

    OPEN hCdropsubscriber
    FETCH hCdropsubscriber INTO @db_name, @type
    
    WHILE (@@fetch_status <> -1)
    BEGIN        
		IF LOWER(@reserved collate SQL_Latin1_General_CP1_CS_AS) = 'drop_subscriptions'
        BEGIN
            if @type = 'tran'
            begin
                SELECT @command = quotename(@db_name) + '.dbo.sp_dropsubscription'
                EXECUTE @retcode  = @command 
                    @publication = 'all', 
                    @article = 'all', 
                    @subscriber = @subscriber,
                    @ignore_distributor = @ignore_distributor
            end
            if @type = 'merge'
            begin
                SELECT @command = quotename(@db_name) + '.dbo.sp_dropmergesubscription'
                EXECUTE @retcode  = @command 
                    @publication = 'all', 
                    @subscriber = @subscriber,
                    @subscription_type = 'both',
                    @ignore_distributor = @ignore_distributor
            end
            IF @@ERROR <> 0 OR @retcode <> 0
            BEGIN
                CLOSE hCdropsubscriber
                DEALLOCATE hCdropsubscriber
                RETURN (1)
            END
        END
        
		-- Set @foundSubscription to not null so that sp_helpsubscription will not display
		-- result set
		select @foundSubscription = 0

        if @type = 'tran'
        begin
            SELECT @command = quotename(@db_name) + '.dbo.sp_helpsubscription'
            EXECUTE @retcode  = @command @publication = '%', @article = '%', 
                @subscriber = @subscriber,
                @found = @foundSubscription  OUTPUT

            IF @@ERROR <> 0 OR @retcode <> 0
            BEGIN
                CLOSE hCdropsubscriber
                DEALLOCATE hCdropsubscriber
                RETURN (1)
            END
        end
        else
        begin
            SELECT @command = quotename(@db_name) + '.dbo.sp_helpmergesubscription'
            EXECUTE @retcode  = @command @publication = '%', 
                @subscriber = @subscriber,
                @subscription_type = 'both',
                @found = @foundSubscription  OUTPUT

            IF @@ERROR <> 0 OR @retcode <> 0
            BEGIN
                CLOSE hCdropsubscriber
                DEALLOCATE hCdropsubscriber
                RETURN (1)
            END
        end


        IF @foundSubscription <> 0
        BEGIN
            CLOSE hCdropsubscriber
            DEALLOCATE hCdropsubscriber
            RAISERROR ( 14144,  16, -1, @subscriber, @db_name)
            RETURN (1)
        END

        FETCH hCdropsubscriber INTO @db_name, @type

    END
    
    CLOSE hCdropsubscriber
    DEALLOCATE hCdropsubscriber

    /*
    ** Drop the subsubscriber_info in the distribution database
    */
    /*
    ** if @ignore_distributor = 1, we are in bruteforce cleanup mode, don't do RPC.
    */
    if @ignore_distributor = 0
    begin


        /*
        ** Get distribution server information for remote RPC
        ** agent verification.
        */
        EXEC @retcode = dbo.sp_helpdistributor 
            @rpcsrvname = @distributor OUTPUT,
            @distribdb = @distribdb OUTPUT
        IF @@error <> 0 OR @retcode <> 0
            BEGIN
            RAISERROR (14071, 16, -1)
               RETURN (1)
        END
        
        /*
        ** Insert information into MSsubscriber_info in the distribution db
        */
        if @distribdb is not null
        begin
            SELECT @distproc = RTRIM(@distributor) + '.' + RTRIM(@distribdb) + '.dbo.sp_MSdrop_subscriber_info'
            EXEC @retcode = @distproc
                 @publisher = @@SERVERNAME,
                 @subscriber = @subscriber

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

    -- We have already validated the existence of the server at this point
    SELECT @subscriber = fn_getpersistedservernamecasevariation(@subscriber) collate database_default

    /*
    ** Turn off the subscriber server option.
    */
    EXECUTE @retcode = dbo.sp_serveroption @subscriber, 'sub', false
    IF @@ERROR <> 0 OR @retcode <> 0
    BEGIN
       RAISERROR (14047, 16, -1, @subscriber)
       RETURN (1)
    END

    RAISERROR (14062, 10, -1)
go


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

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

CREATE PROCEDURE sp_MScreate_dist_tables
AS

   /*
   ** Important:  
   ** We use varbinary(16) for xact_id and xact_seqno, we don't want ending nulls
   ** to be truncated by the server
   **
   ** Also, in MSrepl_commands, we don't want ending space to be truncated.  
   */
   SET ANSI_PADDING ON

   IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'MSrepl_version' and type = 'U')
   BEGIN
      /****************************************************************************/
      raiserror('Creating table MSrepl_version', 0,1)
      /****************************************************************************/
      CREATE TABLE dbo.MSrepl_version
      (
      major_version int NOT NULL,
      minor_version int NOT NULL,
      revision int NOT NULL,
      db_existed bit NULL
      )
	  CREATE UNIQUE CLUSTERED INDEX ucMSrepl_version ON dbo.MSrepl_version
					(major_version, minor_version, revision)
    
      EXEC dbo.sp_MS_marksystemobject 'MSrepl_version'
      
      INSERT INTO MSrepl_version VALUES (7,0,0,0)
   END

   IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'MSpublisher_databases' and type = 'U')
   BEGIN
      /****************************************************************************/
      raiserror('Creating table MSpublisher_databases', 0,1)
      /****************************************************************************/
      CREATE TABLE dbo.MSpublisher_databases(
        publisher_id smallint NOT NULL,
        publisher_db sysname NULL,
        id int identity NOT NULL,                   
    )
      EXEC dbo.sp_MS_marksystemobject 'MSpublisher_databases'

      raiserror('Creating clustered index ucMSpublisher_databases', 0,1)
      CREATE UNIQUE CLUSTERED INDEX ucMSpublisher_databases ON dbo.MSpublisher_databases
     (publisher_id, publisher_db, id)
   END

   IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'MSpublications' and type = 'U')
   BEGIN
      /****************************************************************************/
      raiserror('Creating table MSpublications', 0,1)
      /****************************************************************************/
      CREATE TABLE dbo.MSpublications (
        publisher_id smallint NOT NULL,
        publisher_db sysname NULL,
        publication sysname NOT NULL,
        publication_id int identity NOT NULL,   -- This id IS NOT the same as the SQL Server publisher's
        publication_type int NOT NULL,          -- 0 = Snapshot 1 = Transactional
        thirdparty_flag bit NOT NULL,           -- 0 = SQL Server 1 = Third Party
        independent_agent bit NOT NULL,
        immediate_sync bit NOT NULL,
        allow_push bit NOT NULL,
        allow_pull bit NOT NULL,
        allow_anonymous bit NOT NULL,
        description nvarchar(255) NULL,
        vendor_name nvarchar(100) NULL,
        retention	int NULL,
		sync_method int default 0 NOT NULL,
		allow_subscription_copy bit default 0 not null,
		thirdparty_options int null,
		allow_queued_tran bit default 0	not null
		)
      EXEC dbo.sp_MS_marksystemobject 'MSpublications'

	 -- publication_id needs to be the first columns in the index. It
	 -- is used in sp_MSmaximum_cleanup_seqno.
      raiserror('Creating clustered index ucMSpublications', 0,1)
      CREATE UNIQUE CLUSTERED INDEX ucMSpublications ON dbo.MSpublications
     (publication_id,  publication, publisher_db, publisher_id)

   END
   ELSE
   BEGIN
      IF NOT EXISTS (select * from syscolumns
        where name = 'sync_method'
        and id=object_id('MSpublications'))
      BEGIN
         ALTER TABLE MSpublications ADD sync_method int default 0 not null
      END

      IF NOT EXISTS (select * from syscolumns
        where name = 'allow_subscription_copy'
        and id=object_id('MSpublications'))
      BEGIN
         ALTER TABLE MSpublications ADD allow_subscription_copy bit default 0 not null
      END
      IF NOT EXISTS (select * from syscolumns
        where name = 'thirdparty_options'
        and id=object_id('MSpublications'))
      BEGIN
         ALTER TABLE MSpublications ADD thirdparty_options int null
      END

      IF NOT EXISTS (select * from syscolumns
        where name = 'allow_queued_tran'
        and id=object_id('MSpublications'))
      BEGIN
         ALTER TABLE MSpublications ADD allow_queued_tran bit default 0	not null
      END
   END


   IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'MSarticles' and type = 'U')
   BEGIN
      /****************************************************************************/
      raiserror('Creating table MSarticles', 0,1)
      /****************************************************************************/
    CREATE TABLE dbo.MSarticles (
        publisher_id smallint NOT NULL,
        publisher_db sysname NULL,
        publication_id int NOT NULL,
        article sysname NOT NULL,
        article_id int NOT NULL,                            -- This id is the same as a SQL Server Publisher's
        destination_object sysname NULL,
        source_owner    sysname NULL,
        source_object sysname NULL,
        description nvarchar(255) NULL
    )   

      EXEC dbo.sp_MS_marksystemobject 'MSarticles'

      raiserror('Creating clustered index ucMSarticles', 0,1)
      CREATE UNIQUE CLUSTERED INDEX ucMSarticles ON dbo.MSarticles
     (publisher_db, publisher_id, article_id, article, publication_id)
   END

	IF NOT EXISTS( SELECT * FROM sysobjects WHERE name = 'MSsync_states' )
	BEGIN
		CREATE TABLE dbo.MSsync_states
		(
			publisher_id smallint not null,
			publisher_db sysname not null,
			publication_id int not null 
		)
		EXEC dbo.sp_MS_marksystemobject 'MSsync_states'

		CREATE UNIQUE CLUSTERED INDEX ucMSsyncstates on dbo.MSsync_states
		( publisher_id, publisher_db, publication_id )
	END

   IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'MSsubscriptions' and type = 'U')
   BEGIN
      /****************************************************************************/
      raiserror('Creating table MSsubscriptions', 0,1)
      /****************************************************************************/
       CREATE TABLE dbo.MSsubscriptions
      (
        publisher_database_id int NOT NULL,         -- Used to reference MSrepl_transactions and MSrepl_commands
        publisher_id smallint NOT NULL,
        publisher_db sysname NOT NULL,
        publication_id int NOT NULL,
        article_id int NOT NULL,
        subscriber_id smallint NOT NULL,
        subscriber_db sysname NOT NULL,
        subscription_type int NOT NULL,         -- 0 = push, 1 = pull, 2 = anonymous 
        sync_type tinyint NOT NULL,                 -- 1 = automatic 2 = no sync
        status tinyint NOT NULL,                        -- 0 = inactive, 1 = subscribed, 2 = active 
        subscription_seqno varbinary(16) NOT NULL,  -- publisher's database sequence number 
        snapshot_seqno_flag bit NOT NULL,               -- 1 if subscription_seqno is the snapshot seqno
        independent_agent bit NOT NULL,             -- Value carried over from MSpublications
        subscription_time datetime NOT NULL, 
        loopback_detection bit NOT NULL,  
        agent_id int NOT NULL,
        update_mode tinyint NOT NULL,				-- 0 = read only, 1 = sync tran, 2 = queued tran, 
													-- 3 = failover, 4 = sqlqueued tran, 5 = sqlqueued failover
        publisher_seqno varbinary(16) NOT NULL,
		ss_cplt_seqno   varbinary(16) NOT NULL
      )

      EXEC dbo.sp_MS_marksystemobject 'MSsubscriptions'

      raiserror('Creating clustered index ucMSsubscirptions', 0,1)
      CREATE UNIQUE CLUSTERED INDEX ucMSsubscriptions ON dbo.MSsubscriptions
      (agent_id, article_id)

	  -- Index used by sp_MSdelete_publisherdb_trans
      raiserror('Creating index iMSsubscriptions', 0,1)
      CREATE INDEX iMSsubscriptions ON dbo.MSsubscriptions
      (publisher_database_id, article_id, subscriber_id, subscriber_db, publication_id, publisher_db, publisher_id)
    END
    ELSE
    BEGIN
		if not exists ( select * from syscolumns 
						where id = object_id( N'MSsubscriptions' ) 
						and name = N'ss_cplt_seqno' )
		begin				
			exec ('alter table MSsubscriptions add ss_cplt_seqno varbinary(16) NULL')
			exec ('update MSsubscriptions set ss_cplt_seqno = publisher_seqno')
			exec ('alter table MSsubscriptions alter column ss_cplt_seqno varbinary(16) NOT NULL')
		end
    END

   -- For beta 3 upgrade, we need to create new index here.
   if not exists (select * from sysindexes where name = 'iMSsubscriptions2')
   begin
	  -- Index used by sp_MSdelete_publisherdb_trans
      raiserror('Creating index iMSsubscriptions2', 0,1)
      CREATE INDEX iMSsubscriptions2 ON dbo.MSsubscriptions
      (publisher_database_id, subscription_seqno)
   end

   
   IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'MSmerge_subscriptions' and type = 'U')
   BEGIN
      /****************************************************************************/
      raiserror('Creating table MSmerge_subscriptions', 0,1)
      /****************************************************************************/
       CREATE TABLE dbo.MSmerge_subscriptions
      (
        publisher_id smallint NOT NULL,
        publisher_db sysname NULL,
        publication_id int NOT NULL,
        subscriber_id smallint NOT NULL,
        subscriber_db sysname NULL,
        subscription_type int NULL,         -- 0 = push, 1 = pull, 2 = anonymous 
        sync_type tinyint NOT NULL,                 -- 1 = automatic 2 = no sync
        status tinyint NOT NULL,                        -- 0 = inactive, 1 = subscribed, 2 = active 
        subscription_time datetime NOT NULL
      )

      EXEC dbo.sp_MS_marksystemobject 'MSmerge_subscriptions'

      raiserror('Creating clustered index ucMSmerge_subscriptions', 0,1)
      CREATE UNIQUE CLUSTERED INDEX ucMSmerge_subscriptions ON dbo.MSmerge_subscriptions
     (publisher_id, publisher_db, publication_id, subscriber_id, subscriber_db)
   END


   IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'MSrepl_transactions' and type = 'U')
   BEGIN
      /****************************************************************************/
      raiserror('Creating table MSrepl_transactions', 0,1)
      /****************************************************************************/
      CREATE TABLE dbo.MSrepl_transactions
      (
      publisher_database_id int NOT NULL,
      xact_id varbinary(16) NULL,
      xact_seqno varbinary (16 )  NOT NULL,
      entry_time datetime  NOT NULL
      )

      EXEC dbo.sp_MS_marksystemobject 'MSrepl_transactions'

      raiserror('Creating clustered index usMSrepl_transactions', 0,1)
      CREATE UNIQUE CLUSTERED INDEX ucMSrepl_transactions ON dbo.MSrepl_transactions
         (publisher_database_id, xact_seqno)
         WITH STATISTICS_NORECOMPUTE

      CREATE STATISTICS stat_publisher_database_id
         ON MSrepl_transactions (publisher_database_id)
         WITH NORECOMPUTE

      CREATE STATISTICS stat_xact_id
         ON MSrepl_transactions (xact_id)
         WITH NORECOMPUTE

      CREATE STATISTICS stat_xact_seqno
         ON MSrepl_transactions (xact_seqno)
         WITH NORECOMPUTE

      CREATE STATISTICS stat_entry_time
         ON MSrepl_transactions (entry_time)
         WITH NORECOMPUTE

   END

   IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'MSrepl_commands' and type = 'U')
   BEGIN

      /****************************************************************************/
      raiserror('Creating table MSrepl_commands', 0,1)
      /****************************************************************************/


      CREATE TABLE dbo.MSrepl_commands (
      publisher_database_id int not null,
      xact_seqno varbinary(16) not null,
      type int not null, 
      article_id int not null,
      originator_id int not null,
      command_id int not null,
      partial_command bit not null,
      command varbinary(1024) NULL
      )


      EXEC dbo.sp_MS_marksystemobject 'MSrepl_commands'

      raiserror('Creating clusterd index ucMSrepl_commands', 0,1)
      CREATE UNIQUE CLUSTERED INDEX ucMSrepl_commands ON dbo.MSrepl_commands
         (publisher_database_id, xact_seqno, command_id)
         WITH STATISTICS_NORECOMPUTE

      CREATE STATISTICS stat_xact_seqno
         ON MSrepl_commands (xact_seqno)
         WITH NORECOMPUTE

      CREATE STATISTICS stat_type
         ON MSrepl_commands (type)
         WITH NORECOMPUTE

      CREATE STATISTICS stat_article_id
         ON MSrepl_commands (article_id)
         WITH NORECOMPUTE

      CREATE STATISTICS stat_originator_id
         ON MSrepl_commands (originator_id)
         WITH NORECOMPUTE

      CREATE STATISTICS stat_command_id
         ON MSrepl_commands (command_id)
         WITH NORECOMPUTE

      CREATE STATISTICS stat_partial_command
         ON MSrepl_commands (partial_command)
         WITH NORECOMPUTE

   END


   IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'MSrepl_originators' and type = 'U')
   BEGIN
      /****************************************************************************/
      raiserror('Creating table MSrepl_orginators', 0,1)
      /****************************************************************************/
      CREATE TABLE dbo.MSrepl_originators
      (
      id int identity not null,
      publisher_database_id int not null,
      srvname   sysname not null,
      dbname    sysname not null
      )

      EXEC dbo.sp_MS_marksystemobject 'MSrepl_originators'

      raiserror('Creating clustered index usMSrepl_originators', 0,1)
      CREATE UNIQUE CLUSTERED INDEX ucMSrepl_originators ON dbo.MSrepl_originators
         (publisher_database_id, srvname, dbname)
   END

   IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'MSsubscriber_info' and type = 'U')
   BEGIN
      /****************************************************************************/
      raiserror('Creating table MSsubscriber_info', 0,1)
      /****************************************************************************/
      CREATE TABLE dbo.MSsubscriber_info
      (
      publisher  sysname NOT NULL,
      subscriber  sysname NOT NULL,
      type tinyint NOT NULL,           /* 0: MS SQL Server 1: ODBC Data Source */
      login sysname NULL,
      password nvarchar(524) NULL,
      description nvarchar(255) NULL,
      security_mode int NOT NULL
      )

      EXEC dbo.sp_MS_marksystemobject 'MSsubscriber_info'

      raiserror('Creating clustered index ucMSsubscriber_info', 0,1)
      CREATE UNIQUE CLUSTERED INDEX ucMSsubscriber_info ON dbo.MSsubscriber_info
     (publisher, subscriber)
   END
   ELSE
   BEGIN
      IF NOT EXISTS (select * from syscolumns
        where name = 'description'
        and id=object_id('MSsubscriber_info'))
      BEGIN
         
         ALTER TABLE MSsubscriber_info ADD description nvarchar (255) NULL
         UPDATE MSsubscriber_info SET description = 'SQL Server 6.0'
      END
   END

   IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'MSsubscriber_schedule' and type = 'U')
   BEGIN
         raiserror('Creating table MSsubscriber_schedule', 0,1)
         
        CREATE TABLE dbo.MSsubscriber_schedule
        (
            publisher  sysname NOT NULL,
            subscriber  sysname NOT NULL,
            agent_type      smallint NOT NULL,   -- 0 for distribution agent, 1 for merge agent
            frequency_type int NOT NULL,
            frequency_interval int NOT NULL,
            frequency_relative_interval int NOT NULL,
            frequency_recurrence_factor int NOT NULL,
            frequency_subday int NOT NULL,
            frequency_subday_interval int NOT NULL,
            active_start_time_of_day int NOT NULL,
            active_end_time_of_day int NOT NULL,
            active_start_date int NOT NULL,
            active_end_date int NOT NULL
        )
      EXEC dbo.sp_MS_marksystemobject 'MSsubscriber_schedule'

        CREATE UNIQUE CLUSTERED INDEX ucMSsubscriber_schedule ON dbo.MSsubscriber_schedule
     (publisher, subscriber, agent_type)
     
    END

   IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'MSsnapshot_history' and type = 'U')
   BEGIN
        /****************************************************************************/
         raiserror('Creating table MSsnapshot_history', 0,1)
        /****************************************************************************/
        CREATE TABLE dbo.MSsnapshot_history 
        (
        agent_id int NOT NULL,
        runstatus int NOT NULL,
        start_time datetime NOT NULL,        
        time datetime NOT NULL,    
        duration int NOT NULL,            
        comments nvarchar(255) NOT NULL,

        -- Session summary statistics
        delivered_transactions int NOT NULL,
        delivered_commands int NOT NULL,
        delivery_rate float NOT NULL,

        error_id int NOT NULL,
        timestamp NOT NULL
    )

      EXEC dbo.sp_MS_marksystemobject 'MSsnapshot_history'

     raiserror('Creating clustered index ucMSsnapshot_history', 0,1)
    CREATE UNIQUE CLUSTERED INDEX ucMSsnapshot_history ON dbo.MSsnapshot_history
        (agent_id, timestamp, start_time, time)

	if not exists (select * from sysindexes where name = 'nc1MSsnapshot_history')
	begin
		 raiserror('Creating clustered index nc1MSsnapshot_history', 0,1)
		create nonclustered index nc1MSsnapshot_history on MSsnapshot_history(agent_id, start_time) 
	end	

   END
       
   IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'MSlogreader_history' and type = 'U')
   BEGIN
        /****************************************************************************/
        raiserror('Creating table MSlogreader_history', 0,1)
        /****************************************************************************/    
        CREATE TABLE dbo.MSlogreader_history
        (
        agent_id int NOT NULL,
        runstatus int NOT NULL,
        start_time datetime NOT NULL,
        time datetime NOT NULL,
        duration int NOT NULL,
        comments nvarchar(255) NOT NULL,
        xact_seqno varbinary(16) NULL,

        -- Session summary statistics
        delivery_time int NOT NULL,
        delivered_transactions int NOT NULL,
        delivered_commands int NOT NULL,
        average_commands int NOT NULL,
        delivery_rate float NOT NULL,
        delivery_latency int NOT NULL,

        error_id int NOT NULL,
        timestamp NOT NULL
        )

      EXEC dbo.sp_MS_marksystemobject 'MSlogreader_history'

    raiserror('Creating clustered index ucMSlogreader_history', 0,1)
    CREATE UNIQUE CLUSTERED INDEX ucMSlogreader_history ON dbo.MSlogreader_history
        (agent_id, timestamp, runstatus, start_time, time) 

	if not exists (select * from sysindexes where name = 'nc1MSlogreader_history')
	begin
	    raiserror('Creating nonclustered index nc1MSlogreader_history', 0,1)
		create nonclustered index nc1MSlogreader_history on MSlogreader_history(agent_id, start_time) 
	end	

   END

   IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'MSdistribution_history' and type = 'U')
   BEGIN
        /****************************************************************************/
          raiserror('Creating table MSdistribution_history', 0,1)
        /****************************************************************************/    

        CREATE TABLE dbo.MSdistribution_history
        (
        agent_id int NOT NULL,
        runstatus int NOT NULL,
        start_time datetime NOT NULL,
        time datetime NOT NULL,
        duration int NOT NULL,
        comments nvarchar(255) NOT NULL,
        xact_seqno varbinary(16) NULL,

        -- Current statistics
        current_delivery_rate float NOT NULL,
        current_delivery_latency int NOT NULL,

        -- Session summary statistics
        delivered_transactions int NOT NULL,
        delivered_commands int NOT NULL,
        average_commands int NOT NULL,
        delivery_rate float NOT NULL,       
        delivery_latency int NOT NULL, 

        -- Summary statistics across all sessions
        total_delivered_commands int NOT NULL, 

        error_id int NOT NULL,
        updateable_row bit NOT NULL,
        timestamp NOT NULL
        )

    EXEC dbo.sp_MS_marksystemobject 'MSdistribution_history'

    raiserror('Creating clustered index ucMSdistribution_history', 0,1)
    CREATE UNIQUE CLUSTERED INDEX ucMSdistribution_history ON dbo.MSdistribution_history
        (agent_id, timestamp, runstatus, start_time, time) 


	if not exists (select * from sysindexes where name = 'nc1MSdistribution_history')
	begin
	    raiserror('Creating clustered index nc1MSdistribution_history', 0,1)
		create nonclustered index nc1MSdistribution_history on MSdistribution_history(agent_id, start_time) 
	end	

   END

   IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'MSmerge_history' and type = 'U')
   BEGIN
        /****************************************************************************/
      raiserror('Creating table MSmerge_history', 0,1)
        /****************************************************************************/    

        CREATE TABLE dbo.MSmerge_history
        (
        agent_id                    int             NOT NULL,
        runstatus               int             NOT NULL,
        start_time              datetime        NOT NULL,
        time                    datetime        NOT NULL,
        duration                int             NOT NULL,
        comments                nvarchar(255)   NOT NULL,
        delivery_time               int         NOT NULL,
        delivery_rate               float       NOT NULL,
        publisher_insertcount       int         NULL,
        publisher_updatecount       int         NULL,
        publisher_deletecount       int         NULL,
        publisher_conflictcount     int         NULL,
        subscriber_insertcount      int         NULL,
        subscriber_updatecount      int         NULL,
        subscriber_deletecount      int         NULL,
        subscriber_conflictcount    int         NULL,
        error_id                    int         NOT NULL,
        timestamp                               NOT NULL,
		updateable_row				bit			NOT NULL
        )        

    EXEC dbo.sp_MS_marksystemobject 'MSmerge_history'

    raiserror('Creating clustered index ucMSmerge_history', 0,1)
    CREATE UNIQUE CLUSTERED INDEX ucMSmerge_history ON dbo.MSmerge_history
        (agent_id, timestamp, runstatus) 
        
	if not exists (select * from sysindexes where name = 'nc1MSmerge_history')
		begin
			raiserror('Creating nonclustered index nc1MSmerge_history', 0,1)
			create nonclustered index nc1MSmerge_history on MSmerge_history(agent_id, runstatus, start_time) 
		end
   END
   ELSE
   BEGIN
      IF NOT EXISTS (select * from syscolumns
        where name = 'updateable_row'
        and id=object_id('MSmerge_history'))
      BEGIN 
         ALTER TABLE MSmerge_history ADD updateable_row bit default 1 NOT NULL
      END

	  if not exists (select * from sysindexes where name = 'nc1MSmerge_history' and id=object_id('MSmerge_history'))
	  begin
			raiserror('Creating nonclustered index nc1MSmerge_history', 0,1)
			create nonclustered index nc1MSmerge_history on MSmerge_history(agent_id, runstatus, start_time) 
	  end	
   END


   IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'MSrepl_errors' and type = 'U')
   BEGIN
        /****************************************************************************/
        raiserror('Creating table MSrepl_errors', 0,1)
        /****************************************************************************/    

        CREATE TABLE dbo.MSrepl_errors
        (
        id                  int NOT NULL,
        time                datetime NOT NULL,
        error_type_id       int NULL,
        source_type_id      int NULL,
        source_name         nvarchar(100) NULL,
        error_code          sysname NULL,
        error_text          ntext NULL,
		xact_seqno			varbinary(16) NULL,
		command_id			int NULL
        )

      EXEC dbo.sp_MS_marksystemobject 'MSrepl_errors'

    raiserror('Creating clustered index ucMSrepl_errors', 0,1)
    CREATE CLUSTERED INDEX ucMSrepl_errors ON dbo.MSrepl_errors
        (id, time) -- WITH ALLOW_DUP_ROW
   END
   ELSE
   BEGIN
      IF NOT EXISTS (select * from syscolumns
        where name = 'xact_seqno'
        and id=object_id('MSrepl_errors'))
         ALTER TABLE MSrepl_errors ADD xact_seqno varbinary(16) NULL

      IF NOT EXISTS (select * from syscolumns
        where name = 'command_id'
        and id=object_id('MSrepl_errors'))
         ALTER TABLE MSrepl_errors ADD command_id int NULL
   END

   IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'MSsnapshot_agents' and type = 'U')
   BEGIN
        /****************************************************************************/
        raiserror('Creating table MSsnapshot_agents', 0,1)
        /****************************************************************************/    

        CREATE TABLE dbo.MSsnapshot_agents
        (
        id                  int IDENTITY NOT NULL,
        name                nvarchar(100) NOT NULL,
        publisher_id        smallint NOT NULL,
        publisher_db        sysname NOT NULL,
        publication         sysname NOT NULL,
        publication_type    int NOT NULL,       -- 0 transactional 1 snapshot 2 merge
        local_job           bit NOT NULL,
        job_id              binary(16) NULL,
        profile_id  int     NOT NULL
        )

      EXEC dbo.sp_MS_marksystemobject 'MSsnapshot_agents'

    raiserror('Creating clustered index ucMSsnapshot_agents', 0,1)
    CREATE CLUSTERED INDEX ucMSsnapshot_agents ON dbo.MSsnapshot_agents
        (publication, publisher_db, publisher_id) 

    raiserror('Creatingindex iMSsnapshot_agents', 0,1)
    CREATE UNIQUE INDEX iMSsnapshot_agents ON dbo.MSsnapshot_agents
        (id)
   END

   IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'MSlogreader_agents' and type = 'U')
   BEGIN
        /****************************************************************************/
        raiserror('Creating table MSlogreader_agents', 0,1)
        /****************************************************************************/    

        CREATE TABLE dbo.MSlogreader_agents
        (
        id                  int IDENTITY NOT NULL,
        name                nvarchar(100) NOT NULL,
        publisher_id        smallint NOT NULL,
        publisher_db        sysname NOT NULL,
        publication         sysname NOT NULL,-- Not used for SQL Server publisher
        local_job           bit NOT NULL,
        job_id              binary(16) NULL,
        profile_id  int     NOT NULL
        )

      EXEC dbo.sp_MS_marksystemobject 'MSlogreader_agents'

    raiserror('Creating clustered index ucMSlogreader_agents', 0,1)
    CREATE CLUSTERED INDEX ucMSlogreader_agents ON dbo.MSlogreader_agents
        (publisher_db, publisher_id) 

    raiserror('Creatingindex iMSlogreader_agents', 0,1)
    CREATE UNIQUE INDEX iMSlogreader_agents ON dbo.MSlogreader_agents
        (id)
   END

   IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'MSdistribution_agents' and type = 'U')
   BEGIN
        /****************************************************************************/
        raiserror('Creating table MSdistribution_agents', 0,1)
        /****************************************************************************/    

        CREATE TABLE dbo.MSdistribution_agents
        (
		id                  int IDENTITY NOT NULL,
		name                nvarchar(100) NOT NULL,
		publisher_database_id int NOT NULL,
		publisher_id        smallint NOT NULL,
		publisher_db        sysname NOT NULL,
		publication         sysname NOT NULL,
		subscriber_id       smallint NULL, 
		subscriber_db       sysname NULL,
		subscription_type   int NOT NULL,
		local_job           bit NULL,
		job_id              binary(16) NULL,
		subscription_guid   binary(16) NOT NULL,
		profile_id  int     NOT NULL,
		anonymous_subid     uniqueidentifier NULL,
		subscriber_name     sysname NULL,
		virtual_agent_id    int NULL,
		anonymous_agent_id  int NULL,
		creation_date		datetime default (getdate()) not NULL,
		queue_id			sysname null,
        queue_status        int default 0 not null,
        offload_enabled     bit default 0 NOT NULL,
        offload_server      sysname NULL,
		dts_package_name	sysname NULL,
		dts_package_password nvarchar(524) NULL,
		dts_package_location int default 0 not null,
		sid					varbinary(85) default suser_sid() not null,
		queue_server		sysname NULL
        )

        EXEC dbo.sp_MS_marksystemobject 'MSdistribution_agents'

        raiserror('Creating clustered index ucMSdistribution_agents', 0,1)
        CREATE UNIQUE CLUSTERED INDEX ucMSdistribution_agents ON dbo.MSdistribution_agents
            (id)

        raiserror('Creatingindex iMSdistribution_agents', 0,1)
        CREATE INDEX iMSdistribution_agents ON dbo.MSdistribution_agents
            (publication, publisher_db, publisher_id, subscriber_id, subscriber_db, anonymous_subid) 
   END

	if not exists (select * from syscolumns where 
		id = object_id('MSdistribution_agents') and
		name = 'creation_date')
	begin
		alter table MSdistribution_agents add creation_date datetime default (getdate()) not null
	end 

	if not exists (select * from syscolumns where 
		id = object_id('MSdistribution_agents') and
		name = 'offload_enabled')
	begin
		alter table MSdistribution_agents add offload_enabled bit default 0 not null
	end 

	if not exists (select * from syscolumns where 
		id = object_id('MSdistribution_agents') and
		name = 'offload_server')
	begin
		alter table MSdistribution_agents add offload_server sysname null
	end 

	if not exists (select * from syscolumns where 
		id = object_id('MSdistribution_agents') and
		name = 'queue_id')
	begin
		alter table MSdistribution_agents add queue_id sysname null
	end 

	if not exists (select * from syscolumns where 
		id = object_id('MSdistribution_agents') and
		name = 'queue_status')
	begin
		alter table MSdistribution_agents add queue_status int default 0 not null
	end 

	if not exists (select * from syscolumns where 
		id = object_id('MSdistribution_agents') and
		name = 'dts_package_name')
	begin
		alter table MSdistribution_agents add dts_package_name sysname null
	end 
	if not exists (select * from syscolumns where 
		id = object_id('MSdistribution_agents') and
		name = 'dts_package_password')
	begin
		alter table MSdistribution_agents add dts_package_password nvarchar(524) null
	end 

	if not exists (select * from syscolumns where 
		id = object_id('MSdistribution_agents') and
		name = 'dts_package_location')
	begin
		alter table MSdistribution_agents add dts_package_location int default 0 not null
	end 

	if not exists (select * from syscolumns where 
		id = object_id('MSdistribution_agents') and
		name = 'sid')
	begin
		-- set sid to be the upgrade user. db_owner or sysadmin
		-- can drop the agent entry
		alter table MSdistribution_agents add sid varbinary(85) default suser_sid() not null
	end 

	if not exists (select * from syscolumns where 
		id = object_id('MSdistribution_agents') and
		name = 'queue_server')
	begin
		alter table MSdistribution_agents add queue_server sysname null
		EXEC dbo.sp_MSupdate_mqserver_distdb
	end 
	
   IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'MSmerge_agents' and type = 'U')
   BEGIN
        /****************************************************************************/
        raiserror('Creating table MSmerge_agents', 0,1)
        /****************************************************************************/    

        CREATE TABLE dbo.MSmerge_agents
        (
		id                  int IDENTITY NOT NULL,
		name                nvarchar(100) NOT NULL,
		publisher_id        smallint NOT NULL,
		publisher_db        sysname NOT NULL,
		publication         sysname NOT NULL,
		subscriber_id       smallint NULL,
		subscriber_db       sysname NULL,
		local_job           bit NULL,
		job_id              binary(16) NULL,
		profile_id          int NULL,
		anonymous_subid     uniqueidentifier NULL,
		subscriber_name     sysname NULL,
		creation_date		datetime default (getdate()) not NULL,
        offload_enabled     bit default 0 NOT NULL,
        offload_server      sysname NULL,
		sid					varbinary(85) default suser_sid() not null
        )

      EXEC dbo.sp_MS_marksystemobject 'MSmerge_agents'

    raiserror('Creating clustered index ucMSmerge_agents', 0,1)
    CREATE CLUSTERED INDEX ucMSmerge_agents ON dbo.MSmerge_agents
        (publication, publisher_db, publisher_id, subscriber_id, subscriber_db, anonymous_subid) 
    
    raiserror('Creatingindex iMSmerge_agents', 0,1)
    CREATE UNIQUE INDEX iMSmerge_agents ON dbo.MSmerge_agents
        (id)
	END


	-- Need to re_visit the indexing of this table
	if not exists (select * from sysobjects where name = 'MSrepl_identity_range')
		begin		
			raiserror('Creating table MSrepl_identity_range',0,1)
			
			create table dbo.MSrepl_identity_range (
			publisher				sysname not NULL, 
			publisher_db			sysname	not NULL,
			tablename				sysname not NULL,
			identity_support		int NULL,
			next_seed				bigint NULL, --resource control
			pub_range				bigint NULL, --publisher range
			range					bigint NULL, -- set by sp_addmergearticle
			max_identity			bigint NULL, --resource control
			threshold				int	NULL,	--in percentage, set by sp_addmergearticle
			current_max				bigint NULL	--max value for current check constraint,set by sp_addmergearticle
			)
			exec dbo.sp_MS_marksystemobject MSrepl_identity_range

			grant select on MSrepl_identity_range to public
		end

	if not exists (select * from syscolumns where 
		id = object_id('MSmerge_agents') and
		name = 'creation_date')
	begin
		alter table MSmerge_agents add creation_date datetime default (getdate()) not null
	end 

	if not exists (select * from syscolumns where 
		id = object_id('MSmerge_agents') and
		name = 'offload_enabled')
	begin
		alter table MSmerge_agents add offload_enabled bit default 0 not null
	end 

	if not exists (select * from syscolumns where 
		id = object_id('MSmerge_agents') and
		name = 'offload_server')
	begin
		alter table MSmerge_agents add offload_server sysname null
	end 

	if not exists (select * from syscolumns where 
		id = object_id('MSmerge_agents') and
		name = 'sid')
	begin
		-- set sid to be the upgrade user. db_owner or sysadmin
		-- can drop the agent entry
		alter table MSmerge_agents add sid varbinary(85) default suser_sid() not null
	end 

	IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'MSpublication_access' and type = 'U')
	BEGIN
		/****************************************************************************/
		raiserror('Creating table MSpublication_access', 0,1)
		/****************************************************************************/    

		CREATE TABLE dbo.MSpublication_access
		(
		publication_id      int NULL,       -- Publication_id is unique in distribution database.
		login               sysname NOT NULL
											-- Logins in the publication access list, they nust
											-- exist at both publisher and distributor side.            
		)

		EXEC dbo.sp_MS_marksystemobject 'MSpublication_access'

		raiserror('Creating clustered index ucMSpublication_access', 0,1)
		CREATE UNIQUE CLUSTERED INDEX ucMSpublication_access ON dbo.MSpublication_access
			(publication_id, login) 
	END

	-- For b3 upgrade
	if NOT EXISTS (select * from syscolumns where name='retention' and id=object_id('MSpublications'))
	begin
		alter table MSpublications add retention int NULL 
		UPDATE msdb..MSdistributiondbs set max_distretention=72 where
			name = db_name() collate database_default
	end

	-- drop default_access column
	if exists (select * from syscolumns where id = object_id('MSpublications') and
		name = 'default_access')
	begin
		alter table MSpublications drop column default_access
	end 

	-- Drop publisher_id column
	if EXISTS (select * from syscolumns where name='publisher_id' and id=object_id('MSpublication_access'))
	begin
		drop index MSpublication_access.ucMSpublication_access

		alter table MSpublication_access drop column publisher_id

		raiserror('Creating clustered index ucMSpublication_access', 0,1)
		CREATE CLUSTERED INDEX ucMSpublication_access ON dbo.MSpublication_access
		(publication_id, login) 
	end

	IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'MSqreader_agents' and type = 'U')
	BEGIN
		/****************************************************************************/
		raiserror('Creating table MSqreader_agents', 0,1)
		/****************************************************************************/    

		CREATE TABLE dbo.MSqreader_agents
		(
		id                  int IDENTITY NOT NULL,
		name                nvarchar(100) NULL,
		job_id              binary(16) NULL,
		profile_id  		int NULL
		)

      EXEC dbo.sp_MS_marksystemobject 'MSqreader_agents'

    raiserror('Creating unique index ucMSqreader_agents', 0,1)
    CREATE UNIQUE INDEX ucMSqreader_agents ON dbo.MSqreader_agents
        (id)
   END

	-- add columns for existing table
	if not exists (select * from syscolumns where 
		id = object_id('MSqreader_agents') and
		name = 'profile_id')
	begin
		alter table dbo.MSqreader_agents add profile_id int NULL
	end 
   

	IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'MSqreader_history' and type = 'U')
	BEGIN
	/****************************************************************************/
	raiserror('Creating table MSqreader_history', 0,1)
	/****************************************************************************/    

	CREATE TABLE dbo.MSqreader_history
	(
	agent_id int NOT NULL,
	publication_id int NULL,
	runstatus int NOT NULL,
	start_time datetime NOT NULL,
	time datetime NOT NULL,
	duration int NOT NULL,
	comments nvarchar(255) NOT NULL,
	transaction_id nvarchar(40) NULL, 
	transaction_status int NULL,
	transactions_processed int NULL DEFAULT 0,
	commands_processed int NULL DEFAULT 0,
	delivery_rate float NOT NULL DEFAULT 0.0,
	transaction_rate float NOT NULL DEFAULT 0.0,
	subscriber sysname NULL,
	subscriberdb sysname NULL,
	error_id int NULL,    
	timestamp NOT NULL
	)

	EXEC dbo.sp_MS_marksystemobject 'MSqreader_history'

	raiserror('Creating clustered index ucMSqreader_history', 0,1)
	CREATE CLUSTERED INDEX ucMSqreader_history ON dbo.MSqreader_history
	(agent_id, timestamp, runstatus, start_time, time) 
	END

	-- alter column publication_id
	if not exists (select * from syscolumns where 
		id = object_id('MSqreader_history') and
		name = 'publication_id')
	begin
		alter table dbo.MSqreader_history alter column publication_id int NULL
	end

	-- add columns for existing table
	if not exists (select * from syscolumns where 
		id = object_id('MSqreader_history') and
		name = 'error_id')
	begin
		alter table dbo.MSqreader_history add error_id int NULL
	end 

	if not exists (select * from syscolumns where 
		id = object_id('MSqreader_history') and
		name = 'transactions_processed')
	begin
		alter table dbo.MSqreader_history add transactions_processed int NULL DEFAULT 0
	end 

	if not exists (select * from syscolumns where 
		id = object_id('MSqreader_history') and
		name = 'delivery_rate')
	begin
		alter table dbo.MSqreader_history add delivery_rate float NOT NULL DEFAULT 0.0
	end 

	if not exists (select * from syscolumns where 
		id = object_id('MSqreader_history') and
		name = 'transaction_rate')
	begin
		alter table dbo.MSqreader_history add transaction_rate float NOT NULL DEFAULT 0.0
	end 

	IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'MSrepl_backup_lsns' and type = 'U')
	BEGIN
		/****************************************************************************/
		raiserror('Creating table MSrepl_backup_lsns', 0,1)
		/****************************************************************************/    

		CREATE TABLE dbo.MSrepl_backup_lsns
		(
		publisher_database_id int NOT NULL,
		valid_xact_id varbinary(16) NULL,
		valid_xact_seqno varbinary (16 ) NULL,
		next_xact_id varbinary(16) NULL,
		next_xact_seqno varbinary (16 )  NULL
		)

		EXEC dbo.sp_MS_marksystemobject 'MSrepl_backup_lsns'

		raiserror('Creating clustered index ucMSrepl_backup_lsns', 0,1)
		CREATE UNIQUE CLUSTERED INDEX ucMSrepl_backup_lsns ON dbo.MSrepl_backup_lsns
			(publisher_database_id)

		if exists (select * from MSpublisher_databases)
		begin
			/****************************************************************************/
			raiserror('Upgrading MSrepl_backup_lsns', 0,1)
			/****************************************************************************/    
			insert into MSrepl_backup_lsns select d.id, 
				NULL, NULL, NULL, NULL from MSpublisher_databases d
			-- No need to set the lsns in the table since the 'sync with backup' option is 
			-- new in 8.0
		end

	END
GO

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

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

CREATE PROCEDURE sp_changedistributor_property (
    @property sysname     = NULL,       /* The property to change */
    @value nvarchar(255)  = NULL        /* The new property value */
    ) AS

    declare @retcode int
    declare @new_heartbeat_interval int 

    -- Check to make sure the local server is a distributor
    if not exists (SELECT * FROM master..sysservers
              WHERE  srvstatus & 8 <> 0 and
              UPPER(datasource) = UPPER(@@servername) collate database_default)
    begin
        RAISERROR (14114, 16, -1, @@SERVERNAME)
        return(1)
    end

    -- Return list of properties if @property is NULL
    if @property is NULL
    begin
        create table #tab1 (properties sysname collate database_default not null)
        insert into #tab1 values ('heartbeat_interval')
        select * from #tab1
        return (0)
    end

    if @property is NULL
    begin
        if exists (select name from msdb..sysobjects where name = 'MSdistributor')
        begin
            select property from msdb..MSdistributor
            return 0
        end
        return 1
    end

    -- Check for valid property
    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('heartbeat_interval')
    BEGIN
        RAISERROR (14154, 16, -1)
        RETURN (1)
    END

    if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'heartbeat_interval'
    begin
        if exists (select name from msdb..sysobjects where name = 'MSdistributor')  
        begin
            update msdb..MSdistributor set value = @value where property = @property
            if @@error <> 0
                return 1
            select @new_heartbeat_interval = CONVERT(int, @value)
            exec @retcode = dbo.sp_MScreate_replication_checkup_agent @heartbeat_interval = 
                @new_heartbeat_interval
            if @@error <> 0 or @retcode <> 0
                return 1
        end
        else 
            return 1
    end

    return (0)

GO


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


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

CREATE PROCEDURE sp_changedistributiondb (
    @database sysname,
    @property sysname     = NULL,     /* The property to change */
    @value nvarchar(255)     = NULL      /* The new property value */
    ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */
    DECLARE @distributor sysname
    DECLARE @min_distretention     int
    DECLARE @max_distretention     int
    DECLARE @retcode             int
    DECLARE @new_min_distretention     int
    DECLARE @new_max_distretention     int
    DECLARE @new_history_retention     int
    DECLARE @agentname nvarchar(100)
    DECLARE @command nvarchar(255)
    DECLARE @security_mode int
    DECLARE @distbit int

    SELECT @distbit = 16

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

    IF @property IS NULL
        BEGIN
            CREATE TABLE #tab1 (properties sysname collate database_default not null)
            INSERT INTO #tab1 VALUES ('min_distretention')
            INSERT INTO #tab1 VALUES ('max_distretention')
            INSERT INTO #tab1 VALUES ('history_retention')
            SELECT * FROM #tab1
            RETURN (0)
        END

    /*
    ** Parameter Check:  @property.
    ** Check to make sure that @property is a valid property in
    ** sysarticles.
    */
    IF @property IS NULL OR LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) NOT IN 
                                                    ('min_distretention',
                                                     'max_distretention',
                                                     'history_retention')
             
        BEGIN

            RAISERROR (14115, 16, -1, 
            '''min_distretention'', ''max_distretention'' or ''history_retention''')
            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 configured as a distributor database
    */
    IF NOT EXISTS (SELECT * FROM master..sysdatabases
              WHERE name = @database collate database_default
                 AND category & @distbit <> 0)
    BEGIN
        RAISERROR (14117, 16, -1, @database)
        RETURN(1)
    END
    
    /*
    ** Change the property.
    */
    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'min_distretention'
        BEGIN
            IF @value IS NULL
                BEGIN
                    RAISERROR (14043, 16, -1, '@value') 
                    RETURN (1)
                END

            /*
            ** Set the MinDistRetention registry key value
            */
            SELECT @new_min_distretention = CONVERT(int, @value)

            /*
            ** Get MaxDistRetention value
            */
            SELECT @max_distretention = max_distretention FROM msdb..MSdistributiondbs
                WHERE name = @database collate database_default
                
            /* 
            ** Check for invalid retention values 
            */
            IF @new_min_distretention < 0
                BEGIN
                       RAISERROR(14106, 16, -1)
                    RETURN (1)
                END
            IF @new_min_distretention > @max_distretention
                BEGIN
                    RAISERROR(14107, 16, -1) 
                    RETURN (1)
                END

            UPDATE msdb..MSdistributiondbs SET min_distretention = @new_min_distretention
                WHERE name = @database collate database_default
            IF @@error <> 0 
                BEGIN
                    RETURN (1)
                END

            /*
            ** Update Distribution Cleanup agent 
            */
    		select @agentname = name from msdb..sysjobs j, msdb..sysjobsteps s where 
			j.job_id = s.job_id and
			j.category_id = 11 and
			s.database_name = @database collate database_default
        
			SELECT @command =  'EXEC dbo.sp_MSdistribution_cleanup @min_distretention = ' + 
                CONVERT(nvarchar(12), @new_min_distretention) + ', @max_distretention = ' +
                CONVERT(nvarchar(12), @max_distretention)
            EXEC @retcode = msdb.dbo.sp_update_jobstep @job_name = @agentname, @step_id = 1, 
                @command = @command
            IF @@error <> 0 OR @retcode <> 0
               RETURN(1)

        END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'max_distretention'
        BEGIN
            IF @value IS NULL
                BEGIN
                    RAISERROR (14043, 16, -1, '@value') 
                    RETURN (1)
                END

            /*
            ** Set the MaxDistRetention registry key value
            */
            SELECT @new_max_distretention = CONVERT(int, @value)

            /*
            ** Get MinDistRetention value
            */
            SELECT @min_distretention = min_distretention FROM msdb..MSdistributiondbs
                WHERE name = @database collate database_default

            /* 
            ** Check for invalid retention values 
            */
            IF @new_max_distretention < 0
                BEGIN
                       RAISERROR(14106, 16, -1)
                    RETURN (1)
                END
            IF @new_max_distretention < @min_distretention
                BEGIN
                    RAISERROR(14107, 16, -1) 
                    RETURN (1)
                END

            UPDATE msdb..MSdistributiondbs SET max_distretention = @new_max_distretention
                WHERE name = @database collate database_default
            IF @@error <> 0 
                BEGIN
                    RETURN (1)
                END
            /*
            ** Update Distribution Cleanup agent 
            */
			select @agentname = name from msdb..sysjobs j, msdb..sysjobsteps s where 
				j.job_id = s.job_id and
				j.category_id = 11 and
				s.database_name = @database collate database_default
            SELECT @command =  'EXEC dbo.sp_MSdistribution_cleanup @min_distretention = ' + 
                CONVERT(nvarchar(12), @min_distretention) + ', @max_distretention = ' +
                CONVERT(nvarchar(12), @new_max_distretention)
            EXEC @retcode = msdb.dbo.sp_update_jobstep @job_name = @agentname, @step_id = 1, 
                @command = @command
            IF @@error <> 0 OR @retcode <> 0
               RETURN(1)
        END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'history_retention'
        BEGIN
            IF @value IS NULL
                BEGIN
                    RAISERROR (14043, 16, -1, '@value') 
                    RETURN (1)
                END

            /*
            ** Set the HistoryRetention registry key value
            */
            SELECT @new_history_retention = CONVERT(int, @value)

            UPDATE msdb..MSdistributiondbs SET history_retention = @new_history_retention
                WHERE name = @database collate database_default
            IF @@error <> 0 
                BEGIN
                    RETURN (1)
                END

            /*
            ** Update History Cleanup agent 
            */
			select @agentname = name from msdb..sysjobs j, msdb..sysjobsteps s where 
				j.job_id = s.job_id and
				j.category_id = 12 and
				s.database_name = @database collate database_default
            SELECT @command =  'EXEC dbo.sp_MShistory_cleanup @history_retention = ' + 
                CONVERT(nvarchar(12), @new_history_retention)
            EXEC @retcode = msdb.dbo.sp_update_jobstep @job_name = @agentname, @step_id = 1, 
                @command = @command
            IF @@error <> 0 OR @retcode <> 0
               RETURN(1)

        END




    /*
    ** Return succeed.
    */
    RAISERROR (14105, 10, -1, @property)
    RETURN (0)
go
 
--------------------------------------------------------------------------------
--. sp_adddistpublisher
--------------------------------------------------------------------------------
if exists (select * from sysobjects
        where type = 'P'
            and name = 'sp_adddistpublisher')
    drop procedure sp_adddistpublisher
go

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

CREATE PROCEDURE sp_adddistpublisher (
    @publisher sysname,      /* publisher server name */
    @distribution_db sysname,
    @security_mode int = NULL,
    @login sysname = 'sa',
    @password sysname = NULL,
    @working_directory nvarchar(255),
    @trusted nvarchar(5) = NULL,
    @encrypted_password bit = 0,
    @thirdparty_flag bit = 0
        ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    DECLARE @retcode int
    DECLARE @reg_key nvarchar(255)
    DECLARE @distbit int
    DECLARE @active_value int
    DECLARE @server_added bit
    DECLARE @proc nvarchar(255)
    declare @fExists int
    declare @command nvarchar(255)
    declare @trusted_id bit
    declare @platform_nt binary
	declare @qv_replication varchar(10)
	declare @qv_replication_unlimited integer
	declare @qv_value_replication integer
    declare @enc_password nvarchar(524)

    select @platform_nt = 0x1
	select @qv_replication = '2745196162', @qv_replication_unlimited = 0

    SELECT @distbit = 16
    SELECT @server_added = 0

    /* 
    ** Check if replication components are installed on this server
    */
    exec @retcode = dbo.sp_MS_replication_installed
    if (@retcode <> 1)
    begin
        return (1)
    end

    IF @working_directory IS NULL or ltrim(rtrim(@working_directory)) = ' '
    BEGIN
        RAISERROR (14043, 16, -1, '@working_directory')
        return (1)
    END


    /*
    ** Parameter Check:  @publisher.
    ** Check to make sure that the publisher is not NULL and that it
    ** conforms to the rules for identifiers.
    */

    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)

	IF @password = N''
		select @password = NULL

    /* On REPLICATION_LIMITED server, only local publisher is supported.
     * Note: The login and password registered for local publisher will be used for 
     * local agents to login to distributor, thus local publisher has to be installed first.
     * We choose not to support remote dist publshers on REPLICATION_LIMITED server altogether.
     * On NT, local agents will always use integrated security to log into 
     * distributor
	 * Today, REPLICATION_LIMITED means desktop but we check specific sku entry just in case
	*/
	exec @qv_value_replication = master.dbo.sp_MSinstance_qv @qv_replication	

	if ( @qv_value_replication != @qv_replication_unlimited ) and ( UPPER(@publisher) <> UPPER(@@servername) )
    begin
        -- remote dist publisher is not supported on this server version
        raiserror(21041,16,-1)
        return (1)
    end


    -- Set default security
    IF @security_mode IS NULL
    BEGIN
        IF (UPPER(@publisher) = UPPER(@@SERVERNAME) and ( platform() & @platform_nt = @platform_nt ) )
            SELECT @security_mode = 1
        ELSE
            SELECT @security_mode = 0
    END

    /* 
    ** Check for invalid security mode
    */
    IF @security_mode < 0 OR @security_mode > 1
        BEGIN
            RAISERROR(14109, 16, -1)
            return (1)
        END

    IF (UPPER(@publisher) = UPPER(@@SERVERNAME) and ( @platform_nt != platform() & @platform_nt ) and @security_mode = 1)
    BEGIN
        RAISERROR(21038, 16, -1)
        RETURN (1)
    END

    -- Encrypt the password
    select @enc_password = @password
    IF @encrypted_password = 0
    BEGIN
        EXEC @retcode = master.dbo.xp_repl_encrypt @enc_password OUTPUT
        IF @@error <> 0 OR @retcode <> 0
            return 1
    END

    -- Validate the working directory 
    -- Remove heading and trailing spaces
    select @working_directory = RTRIM(LTRIM(@working_directory))
    
    -- if the last char is '\', remove it.
    if substring(@working_directory, len(@working_directory),1) = '\'
        select @working_directory = substring(@working_directory, 1,
            len(@working_directory)-1)

    -- Don't do validation if it is a UNC path due to security problem.
    -- If the server is started as a service using local system account, we
    -- don't have access to the UNC path.
    if substring(@working_directory, 1,2) <> '\\'
    begin
        select @command = 'dir "' + @working_directory + '"'
        exec @retcode = master..xp_cmdshell @command, 'no_output'
        if @@error <> 0
            return (1)
        if @retcode <> 0 
        begin
            raiserror (21037, 16, -1, @working_directory)
            return (1)
        end
    end

    /*
    ** Parameter Check:  @trusted
    */

    if @trusted is null
    begin
        if UPPER(@publisher) = UPPER(@@servername)
            select @trusted = 'false'
        else
            select @trusted = 'true'
    end

    IF LOWER(@trusted collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@trusted')
            RETURN (1)
        END

    IF LOWER(@trusted collate SQL_Latin1_General_CP1_CS_AS) = 'true' SELECT @trusted_id = 1
    ELSE SELECT @trusted_id = 0

    /*
    ** 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 configured as a distributor database
    */
    IF NOT EXISTS (SELECT * FROM msdb..MSdistributiondbs WHERE name = @distribution_db collate database_default)
    BEGIN
        RAISERROR (14117, 16, -1, @distribution_db)
        return (1)
    END

    /* Check if publisher is already defined. */
    IF EXISTS (SELECT *
         FROM msdb..MSdistpublishers
        WHERE UPPER(name) = UPPER(@publisher) collate database_default)

    BEGIN
        RAISERROR (14074, 16, -1, @publisher)
        RETURN (1)
    END



    IF NOT EXISTS (SELECT *
             FROM master..sysservers
            WHERE UPPER(srvname) = UPPER(@publisher) collate database_default)

    /* Add the server if it does not exist. */
    BEGIN
        EXECUTE @retcode = dbo.sp_addserver @publisher
        IF @@error <> 0 OR @retcode <> 0
        BEGIN
            RAISERROR (14075, 16, -1)
            GOTO UNDO
        END
        SELECT @server_added = 1
    END
    ELSE
    BEGIN
        SELECT @publisher = fn_getpersistedservernamecasevariation(@publisher) collate database_default
    END
     
    /*
    ** Set the Active  value.
    ** If the @publisher is local, set it to true.
    ** Otherwise, set it to false
    */
    IF UPPER(@publisher) = UPPER(@@SERVERNAME)
        SELECT @active_value = 1
    ELSE
        SELECT @active_value = 0


    DELETE msdb.dbo.MSdistpublishers where UPPER(name) = UPPER(@publisher) collate database_default
    IF @@ERROR <> 0
    BEGIN
        GOTO UNDO       
    END

    INSERT INTO msdb.dbo.MSdistpublishers VALUES (
        @publisher, @distribution_db, @working_directory,
        @security_mode, @login, @enc_password, @active_value, @trusted_id, @thirdparty_flag)
    IF @@ERROR <> 0
    BEGIN
        GOTO UNDO       
    END

    -- Add distributor_admin to distribution_admin non trusted mapping
    exec @fExists = dbo.sp_MSIfExistsRemoteLogin @publisher, null, 
        'distributor_admin'
    if( @fExists = 0 )
    BEGIN

        EXECUTE @retcode = dbo.sp_addremotelogin @publisher, 'distributor_admin', 
            'distributor_admin'
        IF @@error <> 0 OR @retcode <> 0
        BEGIN
            RAISERROR (14075, 16, -1)
            GOTO UNDO
        END
    END

    -- For 6x publisher, we still need the trusted sa to sa.
    -- For 6x publisher upgrading to 7.0, distributor_admin to distributor_admin need to be trusted.
    -- add remotelogin of SA if it doesn't already exist
    -- If there's a mapping for remote login sa already, we cannot map it to distributor_admin
    -- this is the case of server upgraded from 6.5.
    exec @fExists = dbo.sp_MSIfExistsRemoteLogin @publisher, null, 'sa'
    if( @fExists = 0 )
    BEGIN
        EXECUTE @retcode = dbo.sp_addremotelogin @publisher, 'distributor_admin', 'sa'
        IF @@error <> 0 OR @retcode <> 0
        BEGIN
            RAISERROR (14075, 16, -1)
            GOTO UNDO
        END
    END
    if @trusted_id = 1
    begin
        exec @fExists = dbo.sp_MSIfExistsRemoteLogin @publisher, 'distributor_admin', 'sa'
        if( @fExists = 1 )
        BEGIN
            EXECUTE @retcode = dbo.sp_remoteoption @publisher, 'distributor_admin', 'sa', trusted, true
            IF @@error <> 0 OR @retcode <> 0
            BEGIN
                RAISERROR (14075, 16, -1)
                GOTO UNDO
            END
        END

        EXECUTE @retcode = dbo.sp_remoteoption @publisher, 'distributor_admin', 'distributor_admin', trusted, true
        IF @@error <> 0 OR @retcode <> 0
        BEGIN
            RAISERROR (14075, 16, -1)
            GOTO UNDO
        END
    END


    /* Add remotelogin enabling the 'probe' of the publisher to
    ** RPC for distribution counter information.
    */
/* SECURITY ********************************
    IF NOT EXISTS (SELECT *
              FROM master..sysremotelogins srl,
               master..sysservers ss
             WHERE UPPER(ss.srvname) = UPPER(@publisher) collate database_default
           AND srl.remoteserverid = ss.srvid
           AND srl.remoteusername = 'probe'
       AND srl.suid = 10)    -- 'probe' 
    exec @fExists = dbo.sp_MSIfExistsRemoteLogin @publisher, 'probe', 'probe'
    if (@fExists = 0)
    BEGIN
       EXECUTE @retcode = dbo.sp_addremotelogin @publisher, 'probe', 'probe'
       IF @@error <> 0 OR @retcode <> 0
       BEGIN
        RAISERROR (14075, 16, -1)
        GOTO UNDO
       END
    END
*********************************/
    
    RETURN(0)

UNDO:
    -- If the server is marked, drop it
    IF EXISTS (SELECT *
         FROM msdb..MSdistpublishers
        WHERE UPPER(name) = UPPER(@publisher) collate database_default)
        EXEC dbo.sp_dropdistpublisher @publisher

    IF @server_added = 1
        EXEC dbo.sp_dropserver @publisher

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

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

CREATE PROCEDURE sp_changedistpublisher (
    @publisher sysname,
    @property sysname     = NULL,     /* The property to change */
    @value nvarchar(255)     = NULL      /* The new property value */
    ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */
    DECLARE @retcode int
    DECLARE @new_database sysname
    DECLARE @new_security_mode int
    DECLARE @new_login sysname
    DECLARE @new_password nvarchar(524)
    DECLARE @distbit int
    DECLARE @new_active int
    DECLARE @new_trusted bit
    DECLARE @command nvarchar(255)
    declare @distribdb sysname
    DECLARE @platform_nt binary

    SELECT @platform_nt = 0x1
    SELECT @distbit = 16
    
    /*
    ** Parameter Check:  @property.
    ** If the @property parameter is NULL, print the options.
    */

    IF @property IS NULL
        BEGIN
            CREATE TABLE #tab1 (properties sysname collate database_default not null)
            INSERT INTO #tab1 VALUES ('distribution_db')
            INSERT INTO #tab1 VALUES ('working_directory')
            INSERT INTO #tab1 VALUES ('security_mode')
            INSERT INTO #tab1 VALUES ('login')
            INSERT INTO #tab1 VALUES ('password')
            INSERT INTO #tab1 VALUES ('active')
            INSERT INTO #tab1 VALUES ('trusted')
            SELECT * FROM #tab1
            RETURN (0)
        END

    /*
    ** Parameter Check:  @property.
    ** Check to make sure that @property is a valid property in
    ** msdb.dbo.MSdistpublishers.
    */
    IF @property IS NULL OR LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) NOT IN 
        ('distribution_db',
         'working_directory',
         'security_mode',
         'login',
         'password',
         'active',
         'trusted')
        BEGIN
            RAISERROR (14115, 16, -1, 
            '''distribution_db'', ''working_directory'', ''security_mode'', ''login'', ''password'', ''active'', or ''trusted''')
            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

    -- Get the distribution db name.
    select @distribdb = distribution_db from msdb..MSdistpublishers where
        UPPER(name) = UPPER(@publisher) collate database_default

    /*
    ** Change the property.
    */
    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'distribution_db'
        BEGIN
            IF @value IS NULL
                BEGIN
                    RAISERROR (14043, 16, -1, '@value') 
                    RETURN (1)
                END

            IF @value <> @distribdb and 
                EXISTS (SELECT * FROM msdb.dbo.MSdistpublishers WHERE
                UPPER(name) = UPPER(@publisher) collate database_default and active = 1)
            BEGIN
                RAISERROR (21046, 16, -1)
                RETURN (1)
            END

            /*
            ** Check if database is configured as a distributor database
            */
            IF NOT EXISTS (SELECT * FROM master..sysdatabases
              WHERE name = @value collate database_default
                 AND category & @distbit <> 0)
                BEGIN
                    RAISERROR (14117, 16, -1, @new_database)
                    RETURN(1)
                END

            UPDATE msdb..MSdistpublishers SET distribution_db = @value
                WHERE UPPER(name) = UPPER(@publisher) collate database_default
            IF @@error <> 0 
                BEGIN
                    RETURN (1)
                END
        END
   
    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'working_directory'
        BEGIN
            IF @value IS NULL
                BEGIN
                    RAISERROR (14043, 16, -1, '@value') 
                    RETURN (1)
                END

            
            -- Validate the working directory 
            -- Remove heading and trailing spaces
            select @value = RTRIM(LTRIM(@value))

            -- if the last char is '\', remove it.

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

            -- Don't do validation if it is a UNC path due to security problem.
            -- If the server is started as a service using local system account, we
            -- don't have access to the UNC path.
            if substring(@value, 1,2) <> '\\'
            begin
                select @command = 'dir ' + @value
                exec @retcode = master..xp_cmdshell @command, 'no_output'
                if @@error <> 0
                    return 1
                if @retcode <> 0 
                begin
                    raiserror (21037, 16, -1, @value)
                    return 1
                end
            end

            UPDATE msdb..MSdistpublishers SET working_directory = @value
                WHERE UPPER(name) = UPPER(@publisher) collate database_default
            IF @@error <> 0 
                BEGIN
                    RETURN (1)
                END
        END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'security_mode'
        BEGIN
            IF @value IS NULL
                BEGIN
                    RAISERROR (14043, 16, -1, '@value') 
                    RETURN (1)
                END

            /*
            ** Set the SecurityMode registry key value
            */
            SELECT @new_security_mode = CONVERT(int, @value)

            /* 
            ** Check for invalid values 
            */
            IF @new_security_mode < 0 OR @new_security_mode > 1
                BEGIN
                    RAISERROR(14109, 16, -1)
                    RETURN (1)
                END

            IF (UPPER(@publisher) = UPPER(@@SERVERNAME) and ( @platform_nt != platform() & @platform_nt ) and @new_security_mode = 1)
            BEGIN
                RAISERROR(21038, 16, -1)
                RETURN (1)
            END

            UPDATE msdb..MSdistpublishers SET security_mode = @new_security_mode
                WHERE UPPER(name) = UPPER(@publisher) collate database_default
            IF @@error <> 0 
                BEGIN
                    RETURN (1)
                END
        END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'login'
        BEGIN
            IF @value IS NULL
                BEGIN
                    RAISERROR (14043, 16, -1, '@value') 
                    RETURN (1)
                END

            /*
            ** Set the Login registry key value
            */
            SELECT @new_login = CONVERT(sysname, @value)

            UPDATE msdb..MSdistpublishers SET login = @new_login
                WHERE UPPER(name) = UPPER(@publisher) collate database_default
            IF @@error <> 0 
                BEGIN
                    RETURN (1)
                END
        END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'password'
        BEGIN
            /*
            ** Set the Password registry key value
            */
            SELECT @new_password = CONVERT(nvarchar(524), @value)

            -- Encrypt the password
            EXEC @retcode = master.dbo.xp_repl_encrypt @new_password OUTPUT
            IF @@error <> 0 OR @retcode <> 0
                RETURN (1)

            UPDATE msdb..MSdistpublishers SET password = @new_password
                WHERE UPPER(name) = UPPER(@publisher) collate database_default
            IF @@error <> 0 
                BEGIN
                    RETURN (1)
                END
         END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'active'
        BEGIN
            /*
            ** Check for a valid  value.
            */

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

            IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'
            begin
                -- Clean up the database in case of the remote publisher is reinstalling publishing.
                SELECT @command = @distribdb + '.dbo.sp_MSdistpublisher_cleanup'
                exec @retcode = @command @publisher
                if @retcode <> 0 or @@error <> 0
                    return 1
                SELECT @new_active = 1
            end
            ELSE
            BEGIN
                SELECT @new_active = 0
            END

            /*
            ** Set the Active registry key value
            */
            UPDATE msdb..MSdistpublishers SET active = @new_active
                WHERE UPPER(name) = UPPER(@publisher) collate database_default
            IF @@error <> 0
                BEGIN
                    RETURN (1)
                END

        END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'trusted'
        BEGIN
            /*
            ** Check for a valid  value.
            */

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

            declare @fExists int

            IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'
            begin
                SELECT @new_trusted = 1
                exec @fExists = dbo.sp_MSIfExistsRemoteLogin @publisher, 'distributor_admin', 'sa'
                if( @fExists = 1 )
                BEGIN
                    EXECUTE @retcode = dbo.sp_remoteoption @publisher, 'distributor_admin', 'sa', trusted, true
                    IF @@error <> 0 OR @retcode <> 0
                    BEGIN
                        RAISERROR (14075, 16, -1)
                        RETURN (1)
                    END
                END


                EXECUTE @retcode = dbo.sp_remoteoption @publisher, 'distributor_admin', 
                    'distributor_admin', trusted, true
                IF @@error <> 0 OR @retcode <> 0
                BEGIN
                    RAISERROR (14075, 16, -1)
                    RETURN (1)
                END
            end
            ELSE
            BEGIN
                SELECT @new_trusted = 0
     
                exec @fExists = dbo.sp_MSIfExistsRemoteLogin @publisher, 'distributor_admin', 'sa'
                if( @fExists = 1 )
                BEGIN
                    EXECUTE @retcode = dbo.sp_remoteoption @publisher, 'distributor_admin', 'sa', trusted, false
                    IF @@error <> 0 OR @retcode <> 0
                    BEGIN
                        RAISERROR (14075, 16, -1)
                        RETURN (1)
                    END
                END


                EXECUTE @retcode = dbo.sp_remoteoption @publisher, 'distributor_admin',
                    'distributor_admin', trusted, 'false'
                IF @@error <> 0 OR @retcode <> 0
                BEGIN
                    RAISERROR (14075, 16, -1)
                    RETURN (1)
                END
            END

            /*
            ** Set the trusted property
            */
            UPDATE msdb..MSdistpublishers SET trusted = @new_trusted
                WHERE UPPER(name) = UPPER(@publisher) collate database_default
            IF @@error <> 0
                BEGIN
                    RETURN (1)
                END
        END

  
    /*
    ** Return succeed.
    */
    RAISERROR (21035, 10, -1, @property)

DONE:    
    RETURN (0)
go


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

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

-- Drop a/all parameter(s) of a/all profile(s) from the MSagent_parameters table
create procedure sp_drop_agent_parameter (
    @profile_id int,
    @parameter_name     sysname = '%'
)
as
    set nocount on

    declare @default            bit
    declare @agent_type         int
    declare @retstatus          int
    select @retstatus = 0

    if @parameter_name is null
    BEGIN
        RAISERROR (14043, 16, -1, '@parameter_name')
        RETURN (1)
    END

    select @agent_type = agent_type, @default=def_profile
    from msdb..MSagent_profiles
    where profile_id = @profile_id

    if @default is null
    BEGIN
        RAISERROR (20066, 16, -1) -- profile not defined
        RETURN (1)
    END
    
    select @parameter_name = lower(@parameter_name collate SQL_Latin1_General_CP1_CS_AS)

    if left(@parameter_name,1) in (N'-', N'/')
        select @parameter_name = right(@parameter_name,len(@parameter_name)-1)

    /* Delete the parameters of the profile */
    delete  msdb..MSagent_parameters 
    where   right(lower(parameter_name collate SQL_Latin1_General_CP1_CS_AS),len(parameter_name)-1) like 
            @parameter_name collate SQL_Latin1_General_CP1_CS_AS
    and     profile_id = @profile_id

    if @@error <> 0
    begin
        select @retstatus = 1
        goto UNDO
    end

UNDO:
    
    return @retstatus
GO


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

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

create procedure sp_generate_agent_parameter(
	@profile_id     int,		-- Fixed profile id used
	@real_profile_id int = NULL -- Used by version upgrade 
)
as
	declare @retcode    int

	-- If real profile id is not passed in. Use the fix profile id.
	if @real_profile_id is NULL
		select @real_profile_id = @profile_id

        /* 
	** Snapshot agent 
	*/
	if (@profile_id = 1)
        begin
		exec @retcode = dbo.sp_add_agent_parameter 
			    @profile_id = @real_profile_id,
				@parameter_name = '-BcpBatchSize',
				@parameter_value = '100000'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-HistoryVerboseLevel',
				@parameter_value = '2'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-LoginTimeout',
				@parameter_value = '15'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-MaxBcpThreads',
				@parameter_value = '1'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-QueryTimeout',
				@parameter_value = '300'        -- 5 minutes 
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE
        end

        /* 
	** Logreader agent 
	*/
	else if (@profile_id = 2)
        begin
  
		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-HistoryVerboseLevel',
				@parameter_value = '1'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-LoginTimeout',
				@parameter_value = '15'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-PollingInterval',
				@parameter_value = '10'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-QueryTimeout',
				@parameter_value = '300'        -- 5 minutes
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-ReadBatchSize',
				@parameter_value = '500'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

        end

        /* 
	** Logreader agent - Verbose History Profile
	*/
	else if (@profile_id = 3)
        begin

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-HistoryVerboseLevel',
				@parameter_value = '2'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-LoginTimeout',
				@parameter_value = '15'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-PollingInterval',
				@parameter_value = '10'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-QueryTimeout',
				@parameter_value = '300'        -- 5 minutes
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-ReadBatchSize',
				@parameter_value = '500'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

        end

        /* 
	** Distribution agent 
	*/
	else if (@profile_id = 4)
	  begin

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-BcpBatchSize',
				@parameter_value = '100000'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-CommitBatchSize',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-CommitBatchThreshold',
				@parameter_value = '1000'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-HistoryVerboseLevel',
				@parameter_value = '1'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-LoginTimeout',
				@parameter_value = '15'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-MaxBcpThreads',
				@parameter_value = '1'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-MaxDeliveredTransactions',
				@parameter_value = '0'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-PollingInterval',
				@parameter_value = '10'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-QueryTimeout',
				@parameter_value = '300'        -- 5 minutes 
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-TransactionsPerHistory',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-SkipErrors',
				@parameter_value = ''
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

        exec @retcode = dbo.sp_add_agent_parameter
                @profile_id = @real_profile_id,
                @parameter_name = '-KeepAliveMessageInterval',
                @parameter_value = '300'
        if (@retcode = 1 or @@ERROR <> 0)
            goto FAILURE

	    end

    /*
	** Distribution Agent Verbose History Profile
	*/
	else if (@profile_id = 5)
	  begin

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-BcpBatchSize',
				@parameter_value = '100000'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-CommitBatchSize',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-CommitBatchThreshold',
				@parameter_value = '1000'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-HistoryVerboseLevel',
				@parameter_value = '2'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-LoginTimeout',
				@parameter_value = '15'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-MaxBcpThreads',
				@parameter_value = '1'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-MaxDeliveredTransactions',
				@parameter_value = '0'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-PollingInterval',
				@parameter_value = '10'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-QueryTimeout',
				@parameter_value = '300'        -- 5 minutes 
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-TransactionsPerHistory',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-SkipErrors',
				@parameter_value = ''
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

        exec @retcode = dbo.sp_add_agent_parameter
                @profile_id = @real_profile_id,
                @parameter_name = '-KeepAliveMessageInterval',
                @parameter_value = '300'
        if (@retcode = 1 or @@ERROR <> 0)
            goto FAILURE

        end
    /* 
	** Merge agent : Default profile for well connected scenarios 
	*/
	else if (@profile_id = 6)
        begin
		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-PollingInterval',
				@parameter_value = '60'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-StartQueueTimeout',
				@parameter_value = '0'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-MaxDownloadChanges',
				@parameter_value = '0'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-MaxUploadChanges',
				@parameter_value = '0'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-ValidateInterval',
				@parameter_value = '60'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-LoginTimeout',
				@parameter_value = '15'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-QueryTimeout',
				@parameter_value = '300'        
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-UploadGenerationsPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-DownloadGenerationsPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-UploadReadChangesPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-DownloadReadChangesPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-UploadWriteChangesPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-DownloadWriteChangesPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-Validate',
				@parameter_value = '0'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-FastRowCount',
				@parameter_value = '1'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE
        
		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-HistoryVerboseLevel',
				@parameter_value = '1'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-ChangesPerHistory',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
			    @profile_id = @real_profile_id,
				@parameter_name = '-BcpBatchSize',
				@parameter_value = '100000'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
			    @profile_id = @real_profile_id,
				@parameter_name = '-NumDeadlockRetries',
				@parameter_value = '5'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
			    @profile_id = @real_profile_id,
				@parameter_name = '-SrcThreads',
				@parameter_value = '3'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
			    @profile_id = @real_profile_id,
				@parameter_name = '-DestThreads',
				@parameter_value = '4'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

        exec @retcode = dbo.sp_add_agent_parameter
                @profile_id = @real_profile_id,
                @parameter_name = '-KeepAliveMessageInterval',
                @parameter_value = '300'
        if (@retcode = 1 or @@ERROR <> 0)
            goto FAILURE

        exec @retcode = dbo.sp_add_agent_parameter
                @profile_id = @real_profile_id,
                @parameter_name = '-MetadataRetentionCleanup',
                @parameter_value = '1'
        if (@retcode = 1 or @@ERROR <> 0)
            goto FAILURE

        end

    /* 
	** Merge agent : Non default profile for disconnected scenarios ( unreliable link ) 
	*/
	else if (@profile_id = 7)
        begin

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-PollingInterval',
				@parameter_value = '60'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-StartQueueTimeout',
				@parameter_value = '0'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-MaxDownloadChanges',
				@parameter_value = '0'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-MaxUploadChanges',
				@parameter_value = '0'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE
			
		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-ValidateInterval',
				@parameter_value = '60'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-LoginTimeout',
				@parameter_value = '15'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-QueryTimeout',
				@parameter_value = '300'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-UploadGenerationsPerBatch',
				@parameter_value = '20'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-DownloadGenerationsPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-UploadReadChangesPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-DownloadReadChangesPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-UploadWriteChangesPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-DownloadWriteChangesPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-Validate',
				@parameter_value = '0'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE
        
		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-FastRowCount',
				@parameter_value = '1'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-HistoryVerboseLevel',
				@parameter_value = '1'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-ChangesPerHistory',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
			    @profile_id = @real_profile_id,
				@parameter_name = '-BcpBatchSize',
				@parameter_value = '100000'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
			    @profile_id = @real_profile_id,
				@parameter_name = '-KeepAliveMessageInterval',
				@parameter_value = '300'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
			    @profile_id = @real_profile_id,
				@parameter_name = '-NumDeadlockRetries',
				@parameter_value = '5'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
			    @profile_id = @real_profile_id,
				@parameter_name = '-SrcThreads',
				@parameter_value = '3'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
			    @profile_id = @real_profile_id,
				@parameter_name = '-DestThreads',
				@parameter_value = '4'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

        exec @retcode = dbo.sp_add_agent_parameter
                @profile_id = @real_profile_id,
                @parameter_name = '-MetadataRetentionCleanup',
                @parameter_value = '1'
        if (@retcode = 1 or @@ERROR <> 0)
            goto FAILURE

        end

        /* 
	** Merge agent : Default profile for well connected scenarios - Verbose history logging 
	*/
	else if (@profile_id = 8)
        begin
		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-PollingInterval',
				@parameter_value = '60'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE
			
		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-StartQueueTimeout',
				@parameter_value = '0'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE
			
		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-MaxDownloadChanges',
				@parameter_value = '0'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-MaxUploadChanges',
				@parameter_value = '0'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-ValidateInterval',
				@parameter_value = '60'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-LoginTimeout',
				@parameter_value = '15'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-QueryTimeout',
				@parameter_value = '300'        
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-UploadGenerationsPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-DownloadGenerationsPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-UploadReadChangesPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-DownloadReadChangesPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-UploadWriteChangesPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-DownloadWriteChangesPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-Validate',
				@parameter_value = '0'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-FastRowCount',
				@parameter_value = '1'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE
        
		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-HistoryVerboseLevel',
				@parameter_value = '3'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-ChangesPerHistory',
				@parameter_value = '50'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
			    @profile_id = @real_profile_id,
				@parameter_name = '-BcpBatchSize',
				@parameter_value = '100000'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
			    @profile_id = @real_profile_id,
				@parameter_name = '-NumDeadlockRetries',
				@parameter_value = '5'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
			    @profile_id = @real_profile_id,
				@parameter_name = '-SrcThreads',
				@parameter_value = '3'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
			    @profile_id = @real_profile_id,
				@parameter_name = '-DestThreads',
				@parameter_value = '4'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

        exec @retcode = dbo.sp_add_agent_parameter
                @profile_id = @real_profile_id,
                @parameter_name = '-KeepAliveMessageInterval',
                @parameter_value = '300'
        if (@retcode = 1 or @@ERROR <> 0)
            goto FAILURE

        exec @retcode = dbo.sp_add_agent_parameter
                @profile_id = @real_profile_id,
                @parameter_name = '-MetadataRetentionCleanup',
                @parameter_value = '1'
        if (@retcode = 1 or @@ERROR <> 0)
            goto FAILURE

        end

    /* 
	** Merge agent : (Synchronization Manager Profile)
	*/
	else if (@profile_id = 9)
        begin
		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-PollingInterval',
				@parameter_value = '60'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-StartQueueTimeout',
				@parameter_value = '0'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-MaxDownloadChanges',
				@parameter_value = '0'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-MaxUploadChanges',
				@parameter_value = '0'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE
			
		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-ValidateInterval',
				@parameter_value = '60'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-LoginTimeout',
				@parameter_value = '15'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-QueryTimeout',
				@parameter_value = '300'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-UploadGenerationsPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-DownloadGenerationsPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-UploadReadChangesPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-DownloadReadChangesPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-UploadWriteChangesPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-DownloadWriteChangesPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-Validate',
				@parameter_value = '0'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE
        
		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-FastRowCount',
				@parameter_value = '1'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-HistoryVerboseLevel',
				@parameter_value = '1'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-ChangesPerHistory',
				@parameter_value = '50'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
			    @profile_id = @real_profile_id,
				@parameter_name = '-BcpBatchSize',
				@parameter_value = '1000'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
			    @profile_id = @real_profile_id,
				@parameter_name = '-NumDeadlockRetries',
				@parameter_value = '5'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
			    @profile_id = @real_profile_id,
				@parameter_name = '-SrcThreads',
				@parameter_value = '3'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
			    @profile_id = @real_profile_id,
				@parameter_name = '-DestThreads',
				@parameter_value = '4'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

        exec @retcode = dbo.sp_add_agent_parameter
                @profile_id = @real_profile_id,
                @parameter_name = '-KeepAliveMessageInterval',
                @parameter_value = '300'
        if (@retcode = 1 or @@ERROR <> 0)
            goto FAILURE

        exec @retcode = dbo.sp_add_agent_parameter
                @profile_id = @real_profile_id,
                @parameter_name = '-MetadataRetentionCleanup',
                @parameter_value = '1'
        if (@retcode = 1 or @@ERROR <> 0)
            goto FAILURE

        end

    /* 
	** Distribution agent (Synchronization Manager Profile)
	*/
	else if (@profile_id = 10)
        begin
		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-BcpBatchSize',
				@parameter_value = '1000'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-CommitBatchSize',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-CommitBatchThreshold',
				@parameter_value = '1000'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-HistoryVerboseLevel',
				@parameter_value = '1'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-LoginTimeout',
				@parameter_value = '15'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-MaxBcpThreads',
				@parameter_value = '1'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-MaxDeliveredTransactions',
				@parameter_value = '0'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-PollingInterval',
				@parameter_value = '10'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-QueryTimeout',
				@parameter_value = '300'        -- 5 minutes 
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-TransactionsPerHistory',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-SkipErrors',
				@parameter_value = ''
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

        exec @retcode = dbo.sp_add_agent_parameter
                @profile_id = @real_profile_id,
                @parameter_name = '-KeepAliveMessageInterval',
                @parameter_value = '300'
        if (@retcode = 1 or @@ERROR <> 0)
            goto FAILURE

        end
	
    /* 
	** Queue Reader agent default profile
	*/
	else if (@profile_id = 11)
    begin
		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-HistoryVerboseLevel',
				@parameter_value = '1'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-PollingInterval',
				@parameter_value = '10'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-LoginTimeout',
				@parameter_value = '15'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-QueryTimeout',
				@parameter_value = '300'        -- 5 minutes 
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE
	end
	  
    /* 
	** Merge agent : Rowcount Validation profile for well connected scenarios 
	*/
	else if (@profile_id = 12)
        begin
		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-PollingInterval',
				@parameter_value = '60'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE


		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-StartQueueTimeout',
				@parameter_value = '0'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-MaxDownloadChanges',
				@parameter_value = '0'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-MaxUploadChanges',
				@parameter_value = '0'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-ValidateInterval',
				@parameter_value = '60'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-LoginTimeout',
				@parameter_value = '15'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-QueryTimeout',
				@parameter_value = '300'        
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-UploadGenerationsPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-DownloadGenerationsPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-UploadReadChangesPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-DownloadReadChangesPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-UploadWriteChangesPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-DownloadWriteChangesPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-Validate',
				@parameter_value = '1'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-FastRowCount',
				@parameter_value = '1'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE
        
		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-HistoryVerboseLevel',
				@parameter_value = '1'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-ChangesPerHistory',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE
		exec @retcode = dbo.sp_add_agent_parameter 
			    @profile_id = @real_profile_id,
				@parameter_name = '-BcpBatchSize',
				@parameter_value = '100000'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
			    @profile_id = @real_profile_id,
				@parameter_name = '-NumDeadlockRetries',
				@parameter_value = '5'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
			    @profile_id = @real_profile_id,
				@parameter_name = '-SrcThreads',
				@parameter_value = '3'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
			    @profile_id = @real_profile_id,
				@parameter_name = '-DestThreads',
				@parameter_value = '4'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE
			
        exec @retcode = dbo.sp_add_agent_parameter
                @profile_id = @real_profile_id,
                @parameter_name = '-KeepAliveMessageInterval',
                @parameter_value = '300'
        if (@retcode = 1 or @@ERROR <> 0)
            goto FAILURE

        exec @retcode = dbo.sp_add_agent_parameter
                @profile_id = @real_profile_id,
                @parameter_name = '-MetadataRetentionCleanup',
                @parameter_value = '1'
        if (@retcode = 1 or @@ERROR <> 0)
            goto FAILURE

        end

    /* 
    ** Merge agent : Rowcount & Checksum Validation profile for well connected scenarios 
    */
	else if (@profile_id = 13)
        begin
		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-PollingInterval',
				@parameter_value = '60'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-StartQueueTimeout',
				@parameter_value = '0'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-MaxDownloadChanges',
				@parameter_value = '0'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-MaxUploadChanges',
				@parameter_value = '0'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-ValidateInterval',
				@parameter_value = '60'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-LoginTimeout',
				@parameter_value = '15'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-QueryTimeout',
				@parameter_value = '300'        
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-UploadGenerationsPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-DownloadGenerationsPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-UploadReadChangesPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-DownloadReadChangesPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-UploadWriteChangesPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-DownloadWriteChangesPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-Validate',
				@parameter_value = '3'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-FastRowCount',
				@parameter_value = '1'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE
        
		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-HistoryVerboseLevel',
				@parameter_value = '1'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-ChangesPerHistory',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE
		exec @retcode = dbo.sp_add_agent_parameter 
			    @profile_id = @real_profile_id,
				@parameter_name = '-BcpBatchSize',
				@parameter_value = '100000'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
			    @profile_id = @real_profile_id,
				@parameter_name = '-NumDeadlockRetries',
				@parameter_value = '5'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE
			
		exec @retcode = dbo.sp_add_agent_parameter 
			    @profile_id = @real_profile_id,
				@parameter_name = '-SrcThreads',
				@parameter_value = '3'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
			    @profile_id = @real_profile_id,
				@parameter_name = '-DestThreads',
				@parameter_value = '4'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

        exec @retcode = dbo.sp_add_agent_parameter
                @profile_id = @real_profile_id,
                @parameter_name = '-KeepAliveMessageInterval',
                @parameter_value = '300'
        if (@retcode = 1 or @@ERROR <> 0)
            goto FAILURE

        exec @retcode = dbo.sp_add_agent_parameter
                @profile_id = @real_profile_id,
                @parameter_name = '-MetadataRetentionCleanup',
                @parameter_value = '1'
        if (@retcode = 1 or @@ERROR <> 0)
            goto FAILURE

        end

    /* 
    ** Distribution agent, skip error profile
    */
	else if (@profile_id = 14)
	  begin

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-BcpBatchSize',
				@parameter_value = '100000'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-CommitBatchSize',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-CommitBatchThreshold',
				@parameter_value = '1000'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-HistoryVerboseLevel',
				@parameter_value = '1'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-LoginTimeout',
				@parameter_value = '15'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-MaxBcpThreads',
				@parameter_value = '1'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-MaxDeliveredTransactions',
				@parameter_value = '0'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-PollingInterval',
				@parameter_value = '10'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-QueryTimeout',
				@parameter_value = '300'        -- 5 minutes 
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-TransactionsPerHistory',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-SkipErrors',
				@parameter_value = '2601:2627:20598'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

        exec @retcode = dbo.sp_add_agent_parameter
                @profile_id = @real_profile_id,
                @parameter_name = '-KeepAliveMessageInterval',
                @parameter_value = '300'
        if (@retcode = 1 or @@ERROR <> 0)
            goto FAILURE

        end

    /* 
    ** Merge agent : Rowcount Validation profile for high volumn server-to-server
    */
        else if (@profile_id = 15)
        begin
		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-PollingInterval',
				@parameter_value = '60'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-StartQueueTimeout',
				@parameter_value = '0'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-MaxDownloadChanges',
				@parameter_value = '0'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-MaxUploadChanges',
				@parameter_value = '0'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-ValidateInterval',
				@parameter_value = '60'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-LoginTimeout',
				@parameter_value = '15'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-QueryTimeout',
				@parameter_value = '600'        
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-UploadGenerationsPerBatch',
				@parameter_value = '2000'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-DownloadGenerationsPerBatch',
				@parameter_value = '2000'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-UploadReadChangesPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-DownloadReadChangesPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-UploadWriteChangesPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-DownloadWriteChangesPerBatch',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-Validate',
				@parameter_value = '0'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-FastRowCount',
				@parameter_value = '1'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE
        
		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-HistoryVerboseLevel',
				@parameter_value = '1'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
				@profile_id = @real_profile_id,
				@parameter_name = '-ChangesPerHistory',
				@parameter_value = '100'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
			    @profile_id = @real_profile_id,
				@parameter_name = '-BcpBatchSize',
				@parameter_value = '100000'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
			    @profile_id = @real_profile_id,
				@parameter_name = '-NumDeadlockRetries',
				@parameter_value = '5'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
			    @profile_id = @real_profile_id,
				@parameter_name = '-SrcThreads',
				@parameter_value = '3'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

		exec @retcode = dbo.sp_add_agent_parameter 
			    @profile_id = @real_profile_id,
				@parameter_name = '-DestThreads',
				@parameter_value = '4'
		if (@retcode = 1 or @@ERROR <> 0)
			goto FAILURE

        exec @retcode = dbo.sp_add_agent_parameter
                @profile_id = @real_profile_id,
                @parameter_name = '-KeepAliveMessageInterval',
                @parameter_value = '300'
        if (@retcode = 1 or @@ERROR <> 0)
            goto FAILURE

        exec @retcode = dbo.sp_add_agent_parameter
                @profile_id = @real_profile_id,
                @parameter_name = '-MetadataRetentionCleanup',
                @parameter_value = '1'
        if (@retcode = 1 or @@ERROR <> 0)
            goto FAILURE

        end

    return 0
FAILURE:
    return 1
go


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

raiserror('Creating procedure sp_MSvalidate_agent_parameter', 0,1)
go
create procedure sp_MSvalidate_agent_parameter (
    @profile_id      int,
    @parameter_name  sysname,
    @parameter_value nvarchar(255)
)
as
    declare @agent_type  int
    declare @original_parameter_name sysname
    declare @numeric_value int

    -- Make sure parameters are non-null
    if @profile_id is null
    BEGIN
        RAISERROR (14043, 16, -1, '@profile_id')
        RETURN (1)
    END

    if @parameter_name is null 
    BEGIN
        RAISERROR (14043, 16, -1, '@parameter_name')
        RETURN (1)
    END

    IF @parameter_value is null
    BEGIN
        RAISERROR (14043, 16, -1, '@parameter_value')
        RETURN (1)
    END

    select @original_parameter_name = @parameter_name
    
    select @agent_type = agent_type
    from msdb..MSagent_profiles 
    where profile_id = @profile_id

	-- Parameter name validation
    if (substring(@parameter_name, 1, 1) <> '/' and 
        substring(@parameter_name, 1, 1) <> '-')
    begin
        return 1
    end

    select @parameter_name = lower(substring(@parameter_name, 2, len(@parameter_name) - 1) collate SQL_Latin1_General_CP1_CS_AS)

    -- Snapshot agent - agent_type = 1
    if (@agent_type = 1)
    begin
        if not @parameter_name in ( 
            N'bcpbatchsize',   
            N'historyverboselevel',
            N'logintimeout',
            N'maxbcpthreads',
            N'querytimeout',
            N'startqueuetimeout'
            )    
        begin
            raiserror(21111, 16, -1, @original_parameter_name)
            return 1
        end
    end
    -- Logreader - agent_type = 2
    else if (@agent_type =2)  
    begin
        if not lower(@parameter_name collate SQL_Latin1_General_CP1_CS_AS) in ( 
            N'historyverboselevel',
            N'logintimeout',
            N'pollinginterval',
            N'querytimeout',
            N'readbatchsize'
            )    
        begin
            raiserror(21112, 16, -1, @original_parameter_name)
            return 1
        end
    end
    -- Distribution agent - agent_type = 3
    else if (@agent_type = 3)
    begin
        if not @parameter_name in ( 
            N'bcpbatchsize',   
            N'commitbatchsize',   
            N'commitbatchthreshold',   
            N'historyverboselevel',
            N'logintimeout',
            N'maxbcpthreads',
            N'maxdeliveredtransactions',
            N'pollinginterval',
            N'querytimeout',
            N'transactionsperhistory',
            N'skiperrors',
            N'keepalivemessageinterval',
            N'useinprocloader'            
            )    
        begin
            raiserror(21113, 16, -1, @original_parameter_name)
            return 1
        end
    end
    -- Merge agent - agent_type = 4
    else if (@agent_type = 4)
    begin 
        if not @parameter_name in ( 
            N'startqueuetimeout',
            N'pollinginterval',   
            N'validateinterval',   
            N'logintimeout',   
            N'querytimeout',
            N'maxuploadchanges',
            N'maxdownloadchanges',
            N'uploadgenerationsperbatch',   
            N'downloadgenerationsperbatch',   
            N'uploadreadchangesperbatch',   
            N'downloadreadchangesperbatch',   
            N'uploadwritechangesperbatch',
            N'downloadwritechangesperbatch',
            N'validate',   
            N'fastrowcount',   
            N'historyverboselevel',
            N'changesperhistory',
            N'bcpbatchsize',
            N'numdeadlockretries',
            N'keepalivemessageinterval',
            N'srcthreads',
            N'destthreads',
            N'useinprocloader',
            N'metadataretentioncleanup'
            )    
        begin
            raiserror(21114, 16, -1, @original_parameter_name)
            return 1
        end
    end
    -- Qreader agent - agent_type = 9
    else if (@agent_type = 9)  
    begin
        if not lower(@parameter_name collate SQL_Latin1_General_CP1_CS_AS) in ( 
            N'resolverstate',
            N'sqlqueuemode',
            N'historyverboselevel',
            N'pollinginterval',
            N'logintimeout',
            N'querytimeout'
            )    
        begin
            raiserror(21112, 16, -1, @original_parameter_name)
            return 1
        end
    end
    else if @agent_type is null
    begin
        raiserror (20066, 16, -1)   -- profile not defined
        return 1
    end
    else
    begin
        -- MSagent_parameters table corruption
        return 1
    end

    -- Parameter value validation
    if (@parameter_name = N'bcpbatchsize')
    begin
        select @numeric_value = convert(int, @parameter_value)
        if @@error <> 0 or @numeric_value < 1
        begin
            raiserror(21115, 16, -1, @parameter_value, @original_parameter_name)
            return 1     
        end
    end
    else if (@parameter_name = N'commitbatchsize')
    begin
        select @numeric_value = convert(int, @parameter_value)
        if @@error <> 0 or @numeric_value < 1
        begin
            raiserror(21115, 16, -1, @parameter_value, @original_parameter_name)
            return 1     
        end                        
    end
    else if (@parameter_name = N'commitbatchthreshold')
    begin
        select @numeric_value = convert(int, @parameter_value)
        if @@error <> 0 or @numeric_value < 1
        begin
            raiserror(21115, 16, -1, @parameter_value, @original_parameter_name)
            return 1     
        end                        
    end
    else if (@parameter_name = N'downloadgenerationsperbatch')
    begin
        select @numeric_value = convert(int, @parameter_value)
        if @@error <> 0 or @numeric_value < 1
        begin
            raiserror(21115, 16, -1, @parameter_value, @original_parameter_name)
            return 1     
        end 
		if @numeric_value > 2000
		begin
			raiserror(14266, 16, -1, 'DownloadGenerationsPerBatch', '1 - 2000') 
			return 1
		end                      
    end
    else if (@parameter_name = N'downloadreadchangesperbatch')
    begin
        select @numeric_value = convert(int, @parameter_value)
        if @@error <> 0 or @numeric_value < 1
        begin
            raiserror(21115, 16, -1, @parameter_value, @original_parameter_name)
            return 1     
        end    
		if @numeric_value > 2000
		begin
			raiserror(14266, 16, -1, 'DownloadReadChangesPerBatch', '1 - 2000') 
			return 1
		end                      
    end
    else if (@parameter_name = N'downloadwritechangesperbatch')
    begin
        select @numeric_value = convert(int, @parameter_value)
        if @@error <> 0 or @numeric_value < 1
        begin
            raiserror(21115, 16, -1, @parameter_value, @original_parameter_name)
            return 1     
        end 
		if @numeric_value > 2000
		begin
			raiserror(14266, 16, -1, 'DownloadWriteChangesPerBatch', '1 - 2000') 
			return 1
		end                         
    end
    else if (@parameter_name = N'fastrowcount')
    begin
        select @numeric_value = convert(int, @parameter_value)
        if @@error <> 0 or not (@numeric_value in (1,2,3))
        begin
            raiserror(21116, 16, -1, @parameter_value, @original_parameter_name)
            return 1     
        end                        
    end
    else if (@parameter_name = N'historyverboselevel')
    begin
        select @numeric_value = convert(int, @parameter_value)
        if @@error <> 0 or not (@numeric_value in (0,1,2,3))
        begin
            raiserror(21117, 16, -1, @parameter_value, @original_parameter_name)
            return 1     
        end                        
    end
    else if (@parameter_name = N'logintimeout')
    begin
        select @numeric_value = convert(int, @parameter_value)
        if @@error <> 0 or @numeric_value < 1
        begin
            raiserror(21115, 16, -1, @parameter_value, @original_parameter_name)
            return 1     
        end                        
    end
    else if (@parameter_name = N'maxbcpthreads')
    begin
        select @numeric_value = convert(int, @parameter_value)
        if @@error <> 0 or @numeric_value < 1
        begin
            raiserror(21115, 16, -1, @parameter_value, @original_parameter_name)
            return 1     
        end                        
    end
    else if (@parameter_name = N'maxdeliveredtransactions')
    begin
        select @numeric_value = convert(int, @parameter_value)
        if @@error <> 0 or @numeric_value < 0
        begin
            raiserror(21119, 16, -1, @parameter_value, @original_parameter_name)
            return 1     
        end                        
    end
    else if (@parameter_name = N'pollinginterval')
    begin
        select @numeric_value = convert(int, @parameter_value)
        if @@error <> 0 or @numeric_value < 1
        begin
            raiserror(21115, 16, -1, @parameter_value, @original_parameter_name)
            return 1     
        end                        
    end
    else if (@parameter_name = N'querytimeout')
    begin
        select @numeric_value = convert(int, @parameter_value)
        if @@error <> 0 or @numeric_value < 1
        begin
            raiserror(21115, 16, -1, @parameter_value, @original_parameter_name)
            return 1     
        end                        
    end
    else if (@parameter_name = N'readbatchsize')
    begin
        select @numeric_value = convert(int, @parameter_value)
        if @@error <> 0 or @numeric_value < 1
        begin
            raiserror(21115, 16, -1, @parameter_value, @original_parameter_name)
            return 1     
        end                        
    end
    else if (@parameter_name = N'transactionsperhistory')
    begin
        select @numeric_value = convert(int, @parameter_value)
        if @@error <> 0 or @numeric_value not between 0 and 10000
        begin
            raiserror(211118, 16, -1, @parameter_value, @original_parameter_name)
            return 1     
        end                        
    end
    else if (@parameter_name = N'uploadgenerationsperbatch')
    begin
        select @numeric_value = convert(int, @parameter_value)
        if @@error <> 0 or @numeric_value < 1
        begin
            raiserror(21115, 16, -1, @parameter_value, @original_parameter_name)
            return 1     
        end 
		if @numeric_value > 2000
		begin
			raiserror(14266, 16, -1, 'UploadGenerationsPerBatch', '1 - 2000') 
			return 1
		end                         
    end
    else if (@parameter_name = N'uploadreadchangesperbatch')
    begin
        select @numeric_value = convert(int, @parameter_value)
        if @@error <> 0 or @numeric_value < 1
        begin
            raiserror(21115, 16, -1, @parameter_value, @original_parameter_name)
            return 1     
        end     
		if @numeric_value > 2000
		begin
			raiserror(14266, 16, -1, 'UploadReadChangesPerBatch', '1 - 2000') 
			return 1
		end                     
    end
    else if (@parameter_name = N'uploadwritechangesperbatch')
    begin
        select @numeric_value = convert(int, @parameter_value)
        if @@error <> 0 or @numeric_value < 1
        begin
            raiserror(21115, 16, -1, @parameter_value, @original_parameter_name)
            return 1     
        end 
		if @numeric_value > 2000
		begin
			raiserror(14266, 16, -1, 'UploadWriteChangesPerBatch', '1 - 2000') 
			return 1
		end                         
    end
    else if (@parameter_name = N'validate')
    begin
        select @numeric_value = convert(int, @parameter_value)
        if @@error <> 0 or not (@numeric_value in (0,1,2,3))
        begin
            raiserror(21117, 16, -1, @parameter_value, @original_parameter_name)
            return 1     
        end                        
    end
    else if (@parameter_name = N'validateinterval')
    begin
        select @numeric_value = convert(int, @parameter_value)
        if @@error <> 0 or @numeric_value < 1
        begin
            raiserror(21115, 16, -1, @parameter_value, @original_parameter_name)
            return 1     
        end                        
    end
    else if (@parameter_name = N'skiperrors')
    begin
		-- Empty string is valid.
		if @parameter_value <> N''
		begin
			-- Valid format: 11:22:33
			if	@parameter_value like '%[^0-9:]%' or
				@parameter_value like ':%' or
				@parameter_value like '%:' or
				@parameter_value like '%::%'
			begin
				raiserror(20601, 16, -1)
				return 1
			end
			-- cannot has number of errors equals to or more than 11
			if	@parameter_value like '%:%:%:%:%:%:%:%:%:%:%'
			begin
				raiserror(20602, 16, -1)
				return 1
			end
		end
    end
    -- Parameter value validation
    else if (@parameter_name = N'numdeadlockretries')
    begin
        select @numeric_value = convert(int, @parameter_value)
        if @@error <> 0 or @numeric_value < 1
        begin
            raiserror(21115, 16, -1, @parameter_value, @original_parameter_name)
            return 1     
        end
		if @numeric_value > 100
		begin
			raiserror(14266, 16, -1, 'NumDeadlockRetries', '1 - 100') 
			return 1
		end                         
    end
	else if (@parameter_name = N'srcthreads')
    begin
        select @numeric_value = convert(int, @parameter_value)
        if @@error <> 0 or @numeric_value < 1
        begin
            raiserror(21115, 16, -1, @parameter_value, @original_parameter_name)
            return 1     
        end                        
    end
	else if (@parameter_name = N'destthreads')
    begin
        select @numeric_value = convert(int, @parameter_value)
        if @@error <> 0 or @numeric_value < 1
        begin
            raiserror(21115, 16, -1, @parameter_value, @original_parameter_name)
            return 1     
        end                        
    end
    else if (@parameter_name = N'keepalivemessageinterval')
    begin
        select @numeric_value = convert(int, @parameter_value)
        if @@error <> 0 or @numeric_value < 30
        begin
            raiserror(21405, 16, -1, @parameter_value, @original_parameter_name, 30)            
            return 1
        end
    end
    else if (@parameter_name = N'useinprocloader')
    begin
        select @numeric_value = convert(int, @parameter_value)
        if @@error <> 0 or @numeric_value not in (0, 1) or rtrim(@parameter_value) = N''
        begin
            raiserror(21406, 16, -1, @parameter_value, @original_parameter_name)
            return 1
        end
    end
    else if (@parameter_name = N'startqueuetimeout')
    begin
        select @numeric_value = convert(int, @parameter_value)
        if @@error <> 0 or (@numeric_value < 300 and @numeric_value <> 0) or rtrim(@parameter_value) = N''
        begin
            raiserror(21404, 16, -1, @parameter_value, @original_parameter_name)
            return 1
        end
    end
    else if (@parameter_name = N'resolverstate')
    begin
        select @numeric_value = convert(int, @parameter_value)
        if @@error <> 0 or @numeric_value not in (1,2,3)
        begin
            raiserror(21115, 16, -1, @parameter_value, @original_parameter_name)
            return 1     
        end                        
    end
    else if (@parameter_name = N'sqlqueuemode')
    begin
        select @numeric_value = convert(int, @parameter_value)
        if @@error <> 0 or @numeric_value not in (0,1,2)
        begin
            raiserror(21115, 16, -1, @parameter_value, @original_parameter_name)
            return 1     
        end                        
    end
    
    return 0
go 

EXEC dbo.sp_MS_marksystemobject 'sp_MSvalidate_agent_parameter'
go

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

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

-- Add a row into the "MSagent_parameters" table
create procedure sp_add_agent_parameter (
    @profile_id int,
    @parameter_name         sysname,
    @parameter_value        nvarchar(255)
)
as
    declare @slash_parameter_name sysname
    declare @dash_parameter_name sysname
    declare @retcode int
    
    set nocount on

    select @parameter_name = RTRIM(@parameter_name)

	-- Append leading '-' when not given
    if (substring(@parameter_name, 1, 1) <> '/' and 
        substring(@parameter_name, 1, 1) <> '-')
    BEGIN
		select @parameter_name = N'-' + @parameter_name
	END

    -- Call proc to validate parameter value
    exec @retcode = dbo.sp_MSvalidate_agent_parameter 
                        @profile_id = @profile_id, 
                        @parameter_name = @parameter_name,
                        @parameter_value = @parameter_value
    if @retcode <> 0
        RETURN(1)


    select @slash_parameter_name = lower(stuff(@parameter_name, 1, 1, N'/') collate SQL_Latin1_General_CP1_CS_AS)
    select @dash_parameter_name = lower(stuff(@parameter_name, 1, 1, N'-') collate SQL_Latin1_General_CP1_CS_AS)
     

    /* A parameter may be defined only once per profile */
    if exists ( select * from msdb..MSagent_parameters 
            where profile_id = @profile_id
            and (lower(parameter_name collate SQL_Latin1_General_CP1_CS_AS) = @slash_parameter_name collate SQL_Latin1_General_CP1_CS_AS  
            or lower(parameter_name collate SQL_Latin1_General_CP1_CS_AS) = @dash_parameter_name collate SQL_Latin1_General_CP1_CS_AS))
    BEGIN
        RAISERROR (20067, 16, -1, @parameter_name)  -- The parameter name ''%s'' already exists for the specified profile
        RETURN (1)
    END

    insert into msdb..MSagent_parameters
    values (@profile_id, @parameter_name, @parameter_value) ;

    if @@error <> 0
        return(1)
GO

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

raiserror('Creating procedure sp_change_agent_parameter', 0,1)
go
-- Change one parameter of a profile from the MSagent_parameters table
create procedure sp_change_agent_parameter(
    @profile_id int,
    @parameter_name         sysname,
    @parameter_value        nvarchar(255)
)AS
    declare @slash_parameter_name sysname
    declare @dash_parameter_name sysname
    declare @retcode int

    select @parameter_name = LTRIM(RTRIM(@parameter_name))
    select @parameter_value = LTRIM(RTRIM(@parameter_value))

    -- DMO sends in empty sting as '(null)'.
	if @parameter_value = N'(null)'
		select @parameter_value = N''

	-- Append leading '-' when not given
    if (substring(@parameter_name, 1, 1) <> '/' and 
        substring(@parameter_name, 1, 1) <> '-')
    BEGIN
		select @parameter_name = N'-' + @parameter_name
	END

	-- Call proc to validate parameter value
    exec @retcode = dbo.sp_MSvalidate_agent_parameter 
                        @profile_id = @profile_id, 
                        @parameter_name = @parameter_name,
                        @parameter_value = @parameter_value
    if @retcode <> 0
        RETURN(1)


    select @slash_parameter_name = lower(stuff(@parameter_name, 1, 1, N'/') collate SQL_Latin1_General_CP1_CS_AS)
    select @dash_parameter_name = lower(stuff(@parameter_name, 1, 1, N'-') collate SQL_Latin1_General_CP1_CS_AS)
   
    IF exists ( select * from msdb..MSagent_parameters 
            where profile_id = @profile_id and 
                (lower(parameter_name collate SQL_Latin1_General_CP1_CS_AS) = @slash_parameter_name collate SQL_Latin1_General_CP1_CS_AS or
                 lower(parameter_name collate SQL_Latin1_General_CP1_CS_AS) = @dash_parameter_name collate SQL_Latin1_General_CP1_CS_AS))
    BEGIN
        update msdb..MSagent_parameters set value = @parameter_value where 
            profile_id = @profile_id and 
            (lower(parameter_name collate SQL_Latin1_General_CP1_CS_AS) = @slash_parameter_name collate SQL_Latin1_General_CP1_CS_AS or
            lower(parameter_name collate SQL_Latin1_General_CP1_CS_AS) = @dash_parameter_name collate SQL_Latin1_General_CP1_CS_AS) 

        if @@error <> 0
            return(1)
    END
GO


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

raiserror('Creating procedure sp_change_agent_profile', 0,1)
go
-- Change one parameter of a profile from the MSagent_profiles table
create procedure sp_change_agent_profile(
    @profile_id         int,
    @property           sysname,
    @value              nvarchar(3000)
)AS

    IF lower(@property collate SQL_Latin1_General_CP1_CS_AS) not in ('description')
    BEGIN
        RAISERROR(21183, 16, -1, @property)
        RETURN (1)
    END

    IF exists ( select * from msdb..MSagent_profiles where profile_id = @profile_id)
    BEGIN
        update msdb..MSagent_profiles set description = @value where profile_id = @profile_id
        if @@error <> 0
            return(1)
    END
GO


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

raiserror('Creating procedure sp_MSfix_6x_tasks', 0,1)
go
CREATE PROCEDURE sp_MSfix_6x_tasks (
	@publisher sysname = NULL
) AS

    SET NOCOUNT ON

    DECLARE @distributor sysname
    DECLARE @distproc nvarchar (255)
	declare @retcode int

	-- If @publisher is null redirect the call to distributor
	if @publisher is null
	begin
		/*
		** Get distribution server information for remote RPC
		** agent verification.
		*/
		EXEC @retcode = dbo.sp_helpdistributor @rpcsrvname = @distributor OUTPUT

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

		/*
		** RPC distributor
		*/
		SELECT @distproc = RTRIM(@distributor) + '.master.dbo.sp_MSfix_6x_tasks'

		EXECUTE @retcode = @distproc
			@publisher = @@SERVERNAME
		IF @@ERROR <> 0 or @retcode <> 0
			return (1)
	end
	else
	begin

		declare @category_id int
		declare @category_name sysname
		declare @server sysname
		declare @databasename sysname
		declare @name sysname
		declare @distdb sysname
		declare @job_id uniqueidentifier
		declare @sSubsystem sysname

        -- Drop entry in systasks first.
        DECLARE hCtasks CURSOR LOCAL FAST_FORWARD FOR
            SELECT name FROM msdb.dbo.systasks_view st WHERE 
                
                -- drop distribution agents.
                (st.name LIKE @publisher + '_' + '%' + '_' + '%' AND 
                LOWER(subsystem collate SQL_Latin1_General_CP1_CS_AS) = 'distribution' AND
                server = @@SERVERNAME) OR
                                
                (LOWER(subsystem collate SQL_Latin1_General_CP1_CS_AS) = 'logreader' AND
                server = @publisher) OR 
                
                (LOWER(subsystem collate SQL_Latin1_General_CP1_CS_AS) = 'snapshot' AND
                server = @publisher) OR

                (st.name LIKE '%' + '_' + '%' + '_Cleanup' AND 
				st.command LIKE '%' + 'sp_replcleanup' + '%' AND
				LOWER(subsystem collate SQL_Latin1_General_CP1_CS_AS) = 'tsql')
            FOR READ ONLY

        OPEN hCtasks
        FETCH hCtasks INTO @name

        WHILE (@@fetch_status <> -1)
        BEGIN
            
            EXEC @retcode = msdb.dbo.sp_droptask @name = @name
            if @retcode <> 0 or @@error <> 0
                return(1)
            FETCH hCtasks INTO @name
        END

		-- Now spin through each old replication job and fixup categories names
		declare hcJobsToFix CURSOR LOCAL FAST_FORWARD for 
				select distinct j.job_id, j.name, s.subsystem, 
					s.server, s.database_name
					from msdb.dbo.sysjobs j left join msdb.dbo.sysjobsteps s
						on j.job_id = s.job_id
					where j.category_id = 0 and s.step_id = 1 and lower(s.subsystem collate SQL_Latin1_General_CP1_CS_AS) in ( 'snapshot', 'logreader', 'distribution' )
				for read only

		open hcJobsToFix 
		fetch hcJobsToFix into @job_id, @name, @sSubsystem,
			@server,@databasename

		while (@@fetch_status <> -1 )
		begin
			-- Note, have to make it a transaction, once the category_id is changed,
			-- the task will never be picked up again.
			begin tran 

				-- Get Distribution category name (assumes category_id = 10)
				select @category_id =
					case lower(@sSubsystem collate SQL_Latin1_General_CP1_CS_AS) 
						when 'snapshot' then 15
						when 'distribution' then 10
						when 'logreader' then 13
						else 0
					end

				select @category_name = name FROM msdb.dbo.syscategories where category_id = @category_id
				
				-- raiserror( 'Would update %s to category %d based on subsystem value %s', -1, 10, @nJobName, @iCategory, @sSubsystem )
				exec @retcode = msdb.dbo.sp_update_job @job_id = @job_id, 
					@category_name = @category_name
				if @retcode <> 0 or @@error <> 0
					goto UNDO


				-- Add the replication agent for monitoring
				SELECT @distdb = distribution_db from msdb..MSdistpublishers where UPPER(name) = UPPER(@server) collate database_default
				IF (@category_id = 13) -- Logreader
				BEGIN
					SELECT @distproc = @distdb + '.dbo.sp_MSadd_logreader_agent'
					EXECUTE @retcode = @distproc
						@name = @name,
						@publisher = @server,
						@publisher_db = @databasename,
						@publication = '',  
						@local_job = 1,
						@job_existing = 1,
						@job_id = @job_id

					IF (@retcode <> 0 or @@error<>0)
						goto UNDO
				END
				ELSE 
				IF (@category_id = 15) -- Snapshot
				BEGIN
                    DECLARE @publication sysname

                    SELECT @publication = NULL
                    EXECUTE master.dbo.sp_MSget_publication_from_taskname
                                            @taskname = @name,
                                            @publisher = @server,
                                            @publisherdb = @databasename,
                                            @publication = @publication OUTPUT

                    IF (@publication IS NOT NULL )
                    BEGIN    			
					    SELECT @distproc = @distdb + '.dbo.sp_MSadd_publication'
					    EXECUTE @retcode = @distproc
						    @publisher = @server,
						    @publisher_db = @databasename,
						    @publication = @publication,
						    @publication_type = 0 -- Transactional
					    IF (@retcode <> 0 or @@error<>0)
						    goto UNDO

					    SELECT @distproc = @distdb + '.dbo.sp_MSadd_snapshot_agent'
					    EXECUTE @retcode = @distproc
						    @name = @name,
						    @publisher = @server,
						    @publisher_db = @databasename,
						    @publication = @publication,
						    @local_job = 1,
						    @job_existing = 1,
						    @snapshot_jobid = @job_id
					    IF (@retcode <> 0 or @@error<>0)
						    goto UNDO
                    END
				END
			commit tran
			fetch hcJobsToFix into @job_id, @name, @sSubsystem,
				@server,@databasename
		end			
		close hcJobsToFix
		deallocate hcJobsToFix
	end
	return (0)
UNDO:
	if @@trancount <> 0
		rollback tran
	return (1)
GO

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

/*
 * Name :       sp_MShelpconflictpublications
 * Description: This sp returns a list of publications or subscriptions in the current
 *    database that may have conflicts. Results are ordered by publication
 *    name.
 * Parameters:  1. publication_type( sysname; '%'==ALL (default) | 'merge' | 'queued' )
 * Output Result Set has the following structure:
 *  ----------------------------------------------------------------------------------
 *      Name                Datatype                Description
 *  ----------------------------------------------------------------------------------
 *  a. name					(sysname)			Publication name
 *  b. publication_type		(varchar(9))		'merge' | 'queued' | 'immediate' (reserved)
 *  c. merge_pub_id			(uniqueidentifier)	Merge publication identifier
 *  d. queued_pub_id		(integer)			Queued publication identifier (I would prefer to name this as tran_pub_id)
 *  e. sub_agent_id			(integer)			Unique publication identifier on a tran subscriber
 *  NOTE: In case of queued tran publications, either d or e will have a value at any time
 *  and this will also indicate, if we are processing a subscriber of a publication (d 
 *  will be NULL and e will have a value) or if we are processing the publisher side (d
 *  will have a value and e will be NULL)
*/
raiserror('Creating procedure sp_MShelpconflictpublications', 0,1)
GO

CREATE PROCEDURE sp_MShelpconflictpublications ( @publication_type varchar(9) ='%' )
AS 
BEGIN

	SET nocount ON
	
	-- validate publication type
	IF NOT( lower(@publication_type collate SQL_Latin1_General_CP1_CS_AS) IN ('%', 'merge', 'queued') )
	BEGIN
		raiserror( 'invalid publication type', 16, -1 )
		RETURN (1)
	END
	
	-- temp table to store combined result set
	CREATE TABLE #result_list ( publication sysname collate database_default, publication_type VARCHAR(9) DEFAULT 'merge', 
								merge_pub_id UNIQUEIDENTIFIER NULL, 
								queued_pub_id INTEGER NULL, sub_agent_id INTEGER NULL, publisher sysname collate database_default NULL, publisher_db sysname collate database_default NULL)

	-- fetch merge results into temp table; need not affect any rows
	IF ( lower(@publication_type collate SQL_Latin1_General_CP1_CS_AS) IN ('%', 'merge') )
	BEGIN
		IF EXISTS( SELECT * FROM sysobjects WHERE name = 'sysmergepublications' )
			INSERT #result_list ( publication, merge_pub_id, publisher, publisher_db)
				EXEC sp_MShelpmergeconflictpublications

		-- may return 18757 (not merge published) and that is ok if @publication_type = ALL
		IF ( @@ERROR <> 0) AND ( @@ERROR = 18757 AND @publication_type = '%' ) 
			GOTO FAILURE
				
	END

	-- fetch tran results into temp table
	IF ( lower(@publication_type collate SQL_Latin1_General_CP1_CS_AS) IN ('%', 'queued') )
	BEGIN
		INSERT #result_list  ( publication, publication_type, merge_pub_id , queued_pub_id, sub_agent_id )
			EXEC sp_MShelptranconflictpublications
		IF ( @@ERROR <> 0) 
			GOTO FAILURE

	END

	-- return combined result list
	SELECT * FROM #result_list

	IF EXISTS( SELECT * FROM sysobjects WHERE type = 'U' AND name = '#result_list' )
		DROP TABLE #result_list

	RETURN (0)

FAILURE:

	IF EXISTS( SELECT * FROM sysobjects WHERE type = 'U' AND name = '#result_list' )
		DROP TABLE #result_list

	RETURN (1)
	
END
GO

GRANT EXECUTE ON dbo.sp_MShelpconflictpublications TO PUBLIC
GO

--------------------------------------------------------------------------------
--. fn_replgetagentcommandlinefromjobid
--------------------------------------------------------------------------------
if exists (select * from sysobjects
        where type = 'FN'
            and name = 'fn_replgetagentcommandlinefromjobid')
    drop function system_function_schema.fn_replgetagentcommandlinefromjobid
go

raiserror('Creating function system_function_schema.fn_replgetagentcommandlinefromjobid',0,1)
go
--
-- Name: fn_replgetagentcommandlinefromjobid
--
-- Description: This function retrieves the command line  
--              of the replication agent with the 
--              specified job_id.
--
-- Parameters: @agenttype nvarchar(16)
--             @job_id uniqueidentifier
-- 
-- Returns: nvarchar(3200) 
--
-- Notes: This function can return null if the 
--        the given job_id doesn't belong to a
--        replication agent of the specified type.
--
-- Security: Admin access only
-- 
create function system_function_schema.fn_replgetagentcommandlinefromjobid (
    @agenttype nvarchar(16),
    @job_id uniqueidentifier
    ) returns nvarchar(3200)
as
begin
    declare @commandline nvarchar(3200)
    select @commandline = null
    select @agenttype = upper(@agenttype collate SQL_Latin1_General_CP1_CS_AS)
    select @commandline = command 
      from msdb.dbo.sysjobsteps
     where job_id = @job_id
       and upper(subsystem collate SQL_Latin1_General_CP1_CS_AS) = @agenttype collate SQL_Latin1_General_CP1_CS_AS

    return @commandline
end
go


--------------------------------------------------------------------------------
--. sp_replproberemoteserver
--------------------------------------------------------------------------------
if exists (select * from sysobjects
        where type = 'P'
            and name = 'sp_replproberemoteserver')
    drop procedure sp_replproberemoteserver
go
raiserror('Creating procedure sp_replproberemoteserver', 0,1)
go
--
-- Name: sp_replproberemoteserver
--
-- Description: This is a lightweight wrapper for calling xp_replproberremsrv
--              with the added nicety of looking up the agent command line
--              using the given jobid. This procedure supports two different
--              modes of operation based on the @no_rpc parameter. If the 
--              @no_rpc parameter is 1, this procedure will not attempt to make
--              rpc call to the Distributor. The local mode is mainly useful 
--              for verifying remote pull subscription agent.
-- 
-- Parameters: @remoteservername sysname (mandatory) 
--             @agent_type nvarchar(16) (optional, default = null) 
--             @agent_jobid uniqueidentifier (optional default = null)
--             @no_rpc bit (optional default = 0)
--
-- Notes: If @job_id is null, only activation verification will be carried
--        out.
--
-- Security: Execute permission of this procedure is granted to public
--
-- Result: 'probe_succeeded' 0 or 1
--
-- Returns: 0    - succeeded
--          <> 0 - failed
--
create procedure dbo.sp_replproberemoteserver (
    @remoteservername sysname,
    @agent_type       nvarchar(16) = null,
    @agent_jobid      uniqueidentifier = null,
    @no_rpc           bit = 0
    ) 
as
begin
    set nocount on
    declare @retcode        int
    declare @commandline    nvarchar(3200)
    declare @distributor    sysname
    declare @distributiondb sysname     
    declare @distproc       nvarchar(255)
    declare @succeeded      nvarchar(10)

    select @retcode = 0
    select @commandline = null
    select @succeeded = null

    if @agent_type is null
    begin
        select @agent_type = 'distribution'
    end
    
    -- @remoteservername cannot be null or empty
    select @remoteservername = rtrim(ltrim(@remoteservername))
    if @remoteservername is null or
       @remoteservername = N''
    begin
        raiserror(21263,16,-1,'@remoteservername')
        return 1
    end     
    
    -- @agent_type must be 'distribution' or 'merge'
    if @agent_type not in (N'distribution', N'merge')
    begin
        raiserror(21182,16,-1) 
        return 1
    end

    -- Get Distributor information
    if @no_rpc = 0
    begin
        exec @retcode = dbo.sp_helpdistributor @rpcsrvname = @distributor output,
                                          @distribdb = @distributiondb output
        if @@error <> 0 or @retcode <> 0
            return 1
    end
    else
    begin
        select @distributor = @@servername
    end

    if upper(@@servername) <> upper(@distributor)
    begin
        select @distproc = ltrim(rtrim(@distributor)) + '.' + 
            'master.dbo.sp_replproberemoteserver'
        exec @retcode = @distproc @remoteservername, 
                                  @agent_type, 
                                  @agent_jobid, 
                                  1
        return @retcode
    end        
    else
    begin
        -- If the given @job_id is not null, try to get
        -- the agent command line
        if @agent_jobid is not null 
        begin
            select @commandline = fn_replgetagentcommandlinefromjobid(
                @agent_type,
                @agent_jobid) collate database_default

            if @commandline is null
            begin
                raiserror(21361,6,-1, @agent_type) 
                select 'probe_succeeded' = 0
                return 1
            end

        end 
 
        exec @retcode = master.dbo.xp_replproberemsrv 
            @remoteservername, 
            @agent_type, 
            @succeeded output,
            @commandline 

        if lower(@succeeded collate SQL_Latin1_General_CP1_CS_AS) = N'true'  
            select 'probe_succeeded' = 1
        else
            select 'probe_succeeded' = 0

        return @retcode
    end

    select 'probe_succeeded' = 0

    return 1
end
go
exec sp_MS_marksystemobject sp_replproberemoteserver
go
grant execute on dbo.sp_replproberemoteserver to public
go

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

create procedure sp_MScreate_distributor_tables
as
    declare @profile_id     int
    declare @retcode    int
    declare @profile_name nvarchar(100)
    declare @profile_desc nvarchar(100)

    /* Create MSpublishers table */
    IF EXISTS (SELECT * FROM msdb..sysobjects WHERE name = 'MSdistpublishers'
        and xtype = 'U')
    begin
        DROP TABLE msdb..MSdistpublishers
        if @@error<> 0 goto FAILURE
    end

    /* Create MSdistributiondbs table */
    IF EXISTS (SELECT * FROM msdb..sysobjects WHERE name = 'MSdistributiondbs'
        and xtype = 'U')
    begin
        DROP TABLE msdb..MSdistributiondbs
        if @@error<> 0 goto FAILURE
    end

    /* create MSdistributor table */
    IF EXISTS (SELECT * FROM msdb..sysobjects WHERE name = 'MSdistributor'
        and xtype = 'U')
    begin
        DROP TABLE msdb..MSdistributor
        if @@error<> 0 goto FAILURE
    end

    /* create sysreplicationalerts table */
    IF EXISTS (SELECT * FROM msdb..sysobjects WHERE name = 'sysreplicationalerts'
        and xtype = 'U')
    begin
        DROP TABLE msdb..sysreplicationalerts
        if @@error<> 0 goto FAILURE
    end
    
    /* create MSagent_profiles table */
    IF EXISTS (SELECT * FROM msdb..sysobjects WHERE name = 'MSagent_profiles'
        and xtype = 'U')
    begin
        DROP TABLE msdb..MSagent_profiles
        if @@error<> 0 goto FAILURE
    end

    /* create MSagent_parameters table */
    IF EXISTS (SELECT * FROM msdb..sysobjects WHERE name = 'MSagent_parameters' 
        and xtype = 'U')
    begin
        DROP TABLE msdb..MSagent_parameters
        if @@error<> 0 goto FAILURE
    end

    /* create MSdatatype_mappings table */
    IF EXISTS (SELECT * FROM msdb..sysobjects WHERE name = 'MSdatatype_mappings' 
        and xtype = 'U')
    begin
        DROP TABLE msdb..MSdatatype_mappings
        if @@error<> 0 goto FAILURE
    end

    
    CREATE TABLE msdb.dbo.MSdistpublishers
    (
        name                sysname     NOT NULL,
        distribution_db     sysname     NOT NULL,
        working_directory   nvarchar(255) NOT NULL,
        security_mode       int         NOT NULL,
        login               sysname     NOT NULL,
        password            nvarchar(524) NULL,
        active              bit         NOT NULL,
        trusted             bit         NOT NULL,
        thirdparty_flag     bit         NOT NULL
    )
    if @@error<> 0 goto FAILURE

	CREATE UNIQUE CLUSTERED INDEX uc1MSdistpublishers 
		ON msdb.dbo.MSdistpublishers(name)
	if @@error<> 0 goto FAILURE


    CREATE TABLE msdb.dbo.MSdistributiondbs
    (
        name            sysname     NOT NULL,
        min_distretention   int     NOT NULL,
        max_distretention   int     NOT NULL,
        history_retention   int     NOT NULL
    )
    if @@error<> 0 goto FAILURE

	CREATE UNIQUE CLUSTERED INDEX uc1MSdistributiondbs 
			ON msdb.dbo.MSdistributiondbs(name)
	if @@error<> 0 goto FAILURE



    CREATE TABLE msdb.dbo.MSdistributor 
    ( 
        property        sysname     NOT NULL, 
        value           nvarchar(3000)  NULL
    )
    if @@error<> 0 goto FAILURE
	
	CREATE UNIQUE CLUSTERED INDEX uc1MSdistributor 
			ON msdb.dbo.MSdistributor(property)
	if @@error<> 0 goto FAILURE

    CREATE TABLE msdb.dbo.sysreplicationalerts
    (
        alert_id            int identity(1,1) NOT NULL,
        status              int NOT NULL,
        agent_type          int NULL,
        agent_id            int NULL,
        error_id            int NULL,
        alert_error_code    int NULL,
        time                datetime NOT NULL,
        publisher           sysname NULL,
        publisher_db        sysname NULL,
        publication         sysname NULL,
        publication_type    int NULL,
        subscriber          sysname NULL,
        subscriber_db       sysname NULL,
        article             sysname NULL,
        destination_object  sysname NULL,
        source_object       sysname NULL,
        alert_error_text    ntext NULL
    )
    if @@error<> 0 goto FAILURE

    CREATE UNIQUE CLUSTERED INDEX ucsysreplicationalerts ON msdb.dbo.sysreplicationalerts(alert_id)
    if @@error<> 0 goto FAILURE

    CREATE TABLE msdb.dbo.MSagent_profiles
    (
        profile_id    int           NOT NULL IDENTITY,
        profile_name  sysname       NOT NULL,
        agent_type          int             NOT NULL, -- 1-Snapshot, 2-Logreader,
                                                      -- 3-Distribution, 4-Merge,
                                                      -- 9-Qreader
        type                int             NOT NULL, -- 0-System, 1-Custom
        description         nvarchar(3000)  NULL,
        def_profile   bit               NOT NULL
    )
    if @@error<> 0 goto FAILURE


    CREATE UNIQUE CLUSTERED INDEX ucMSagent_profiles ON msdb.dbo.MSagent_profiles
        (profile_name, profile_id, agent_type)
    if @@error<> 0 goto FAILURE

    CREATE TABLE msdb.dbo.MSagent_parameters
    (
        profile_id     int          NOT NULL,
        parameter_name       sysname        NOT NULL,
        value                nvarchar(255)  NOT NULL
    )
    if @@error<> 0 goto FAILURE

    CREATE UNIQUE CLUSTERED INDEX ucMSagent_parameters ON msdb.dbo.MSagent_parameters
        (parameter_name, profile_id)
    if @@error<> 0 goto FAILURE

    /* 
	** Create default / non default profiles 
	** for all the agents 
	*/

    /* 
	** Snapshot agent 
	*/
    set @profile_id = NULL
    set @profile_name = formatmessage(20545) -- Default Snapshot Profile
    set @profile_desc = NULL

    exec @retcode = dbo.sp_add_agent_profile
            @profile_id = @profile_id OUT,
            @profile_name = @profile_name,
            @agent_type = 1,   -- 1-Snapshot, 2-Logreader, 3-Distribution, 4-Merge
            @profile_type = 0,   -- 0-System, 1-Custom 
            @description = @profile_desc,
            @default = 1
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

	exec @retcode = dbo.sp_generate_agent_parameter @profile_id
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    /* 
	** Logreader agent 
	*/
    set @profile_id = NULL
    set @profile_name = formatmessage(20545) -- Default LogReader Profile
    set @profile_desc = NULL

    exec @retcode = dbo.sp_add_agent_profile
            @profile_id = @profile_id OUT,
            @profile_name = @profile_name,
            @agent_type = 2,   -- 1-Snapshot, 2-Logreader, 3-Distribution, 4-Merge
            @profile_type = 0,   -- 0-System, 1-Custom 
            @description = @profile_desc,
            @default = 1
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    exec @retcode = dbo.sp_generate_agent_parameter @profile_id
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    /* 
	** Logreader agent - Verbose History Profile
	*/
    set @profile_id = NULL
    set @profile_name = formatmessage(20546) -- LogReader Verbose History Profile
    set @profile_desc = formatmessage(20547)

    exec @retcode = dbo.sp_add_agent_profile
            @profile_id = @profile_id OUT,
            @profile_name = @profile_name,
            @agent_type = 2,   -- 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)
        goto FAILURE

    exec @retcode = dbo.sp_generate_agent_parameter @profile_id
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    /* 
	** Distribution agent 
	*/
    set @profile_id = NULL
    set @profile_name = formatmessage(20545) -- Default Distribution Profile
    set @profile_desc = 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 = 1
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    exec @retcode = dbo.sp_generate_agent_parameter @profile_id
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    /*
	** Distribution Agent Verbose History Profile
	*/
    set @profile_id = NULL
    set @profile_name = formatmessage(20546) -- Distribution Verbose History Profile
    set @profile_desc = formatmessage(20547)

    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)
        goto FAILURE

    exec @retcode = dbo.sp_generate_agent_parameter @profile_id
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE
 
    /* 
	** Merge agent : Default profile for well connected scenarios 
	*/
    set @profile_id = NULL
    set @profile_name = formatmessage(20545) -- Default Merge Profile
    set @profile_desc = NULL

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

    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    exec @retcode = dbo.sp_generate_agent_parameter @profile_id
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

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

    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

    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    exec @retcode = dbo.sp_generate_agent_parameter @profile_id
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

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

    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

    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    exec @retcode = dbo.sp_generate_agent_parameter @profile_id
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

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

    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

    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    exec @retcode = dbo.sp_generate_agent_parameter @profile_id
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

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

    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)
        goto FAILURE

    exec @retcode = dbo.sp_generate_agent_parameter @profile_id
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

	/* 
	** Qreader agent (default profile)
	*/
	set @profile_id = NULL
	set @profile_name = formatmessage(20545) -- Default QueueReader Profile
    set @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)
		goto FAILURE

	exec @retcode = dbo.sp_generate_agent_parameter @profile_id
	if (@retcode = 1 or @@ERROR <> 0)
		goto FAILURE

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

    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

    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    exec @retcode = dbo.sp_generate_agent_parameter @profile_id
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

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

    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

    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    exec @retcode = dbo.sp_generate_agent_parameter @profile_id
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE


    /* 
	** Distribution agent, skip error profile
	*/
    set @profile_id = NULL
    set @profile_name = formatmessage(20599) -- Default Distribution Profile
    set @profile_desc = formatmessage(20600)

    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)
        goto FAILURE

    exec @retcode = dbo.sp_generate_agent_parameter @profile_id
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

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

    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

    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    exec @retcode = dbo.sp_generate_agent_parameter @profile_id
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    create table msdb.dbo.MSdatatype_mappings 
    (
    dbms_name           sysname NOT NULL,
    sql_type            sysname NOT NULL,
    dest_type           sysname NOT NULL,
    dest_prec           int     NOT NULL,
    dest_create_params  int     NOT NULL,
    dest_nullable       bit     NOT NULL
    )

	-- MS Jet
    exec dbo.sp_add_datatype_mapping 'MS Jet', 'binary' , 'binary', 255, 4, 1
    exec dbo.sp_add_datatype_mapping 'MS Jet', 'varbinary' , 'varbinary', 255, 4, 1
    exec dbo.sp_add_datatype_mapping 'MS Jet', 'binary' , 'image', 1073741824, 0, 1
    exec dbo.sp_add_datatype_mapping 'MS Jet', 'varbinary' , 'image', 1073741824, 0, 1
	exec dbo.sp_add_datatype_mapping 'MS Jet', 'sql_variant' , 'longtext', 1073741824, 0, 1
    exec dbo.sp_add_datatype_mapping 'MS Jet', 'varchar' , 'varchar', 255, 4, 1
    exec dbo.sp_add_datatype_mapping 'MS Jet', 'varchar' , 'longtext', 1073741824, 0, 1
    exec dbo.sp_add_datatype_mapping 'MS Jet', 'nchar' , 'nchar', 255, 4, 1
    exec dbo.sp_add_datatype_mapping 'MS Jet', 'nchar' , 'longtext', 1073741824, 0, 1
    exec dbo.sp_add_datatype_mapping 'MS Jet', 'char' , 'char', 255, 4, 1
    exec dbo.sp_add_datatype_mapping 'MS Jet', 'char' , 'longtext', 1073741824, 0, 1
    exec dbo.sp_add_datatype_mapping 'MS Jet', 'nvarchar' , 'nchar varying', 255, 4, 1
    exec dbo.sp_add_datatype_mapping 'MS Jet', 'nvarchar' , 'longtext', 1073741824, 0, 1
    exec dbo.sp_add_datatype_mapping 'MS Jet', 'datetime' , 'datetime', 255, 0, 1
    exec dbo.sp_add_datatype_mapping 'MS Jet', 'smalldatetime' , 'datetime', 255, 0, 1
    exec dbo.sp_add_datatype_mapping 'MS Jet', 'decimal' , 'decimal', 255, 3, 1
    exec dbo.sp_add_datatype_mapping 'MS Jet', 'numeric' , 'decimal', 255, 3, 1
    exec dbo.sp_add_datatype_mapping 'MS Jet', 'float' , 'float', 255, 0, 1
    exec dbo.sp_add_datatype_mapping 'MS Jet', 'real' , 'real', 255, 0, 1
    exec dbo.sp_add_datatype_mapping 'MS Jet', 'bigint' , 'decimal', 255, 0, 1
    exec dbo.sp_add_datatype_mapping 'MS Jet', 'int' , 'int', 255, 0, 1
    exec dbo.sp_add_datatype_mapping 'MS Jet', 'smallint' , 'smallint', 255, 0, 1
    exec dbo.sp_add_datatype_mapping 'MS Jet', 'tinyint' , 'byte', 255, 0, 1
    exec dbo.sp_add_datatype_mapping 'MS Jet', 'money' , 'currency', 255, 0, 1
    exec dbo.sp_add_datatype_mapping 'MS Jet', 'smallmoney' , 'currency', 255, 0, 1
    exec dbo.sp_add_datatype_mapping 'MS Jet', 'bit' , 'bit', 255, 0, 1
    exec dbo.sp_add_datatype_mapping 'MS Jet', 'sysname' , 'nchar varying', 255, 4, 1
    exec dbo.sp_add_datatype_mapping 'MS Jet', 'timestamp' , 'binary', 255, 4, 1
    exec dbo.sp_add_datatype_mapping 'MS Jet', 'uniqueidentifier' , 'guid', 255, 0, 1
    exec dbo.sp_add_datatype_mapping 'MS Jet', 'text' , 'longtext', 1073741824, 0, 1
    exec dbo.sp_add_datatype_mapping 'MS Jet', 'ntext' , 'longtext', 1073741824, 0, 1
    exec dbo.sp_add_datatype_mapping 'MS Jet', 'image' , 'image', 1073741824, 0, 1

	-- Oracle
    exec dbo.sp_add_datatype_mapping 'Oracle', 'binary' , 'raw', 255, 4, 1
    exec dbo.sp_add_datatype_mapping 'Oracle', 'varbinary' , 'raw', 255, 4, 1
    exec dbo.sp_add_datatype_mapping 'Oracle', 'binary' , 'long raw', 2147483647, 0, 1
    exec dbo.sp_add_datatype_mapping 'Oracle', 'varbinary' , 'long raw', 2147483647, 0, 1
	exec dbo.sp_add_datatype_mapping 'Oracle', 'sql_variant' , 'long', 2147483647, 0, 1
    --exec dbo.sp_add_datatype_mapping 'Oracle', 'varchar' , 'char', 255, 4, 1
    exec dbo.sp_add_datatype_mapping 'Oracle', 'varchar' , 'varchar2', 2000, 4, 1
    exec dbo.sp_add_datatype_mapping 'Oracle', 'varchar' , 'long', 2147483647, 0, 1
    exec dbo.sp_add_datatype_mapping 'Oracle', 'nchar' , 'char', 255, 4, 1
    exec dbo.sp_add_datatype_mapping 'Oracle', 'nchar' , 'varchar2', 2000, 4, 1
    exec dbo.sp_add_datatype_mapping 'Oracle', 'nchar' , 'long', 2147483647, 0, 1
    exec dbo.sp_add_datatype_mapping 'Oracle', 'char' , 'char', 255, 4, 1
    exec dbo.sp_add_datatype_mapping 'Oracle', 'char' , 'varchar2', 2000, 4, 1
    exec dbo.sp_add_datatype_mapping 'Oracle', 'char' , 'long', 2147483647, 0, 1
    --exec dbo.sp_add_datatype_mapping 'Oracle', 'nvarchar' , 'char', 255, 4, 1
    exec dbo.sp_add_datatype_mapping 'Oracle', 'nvarchar' , 'varchar2', 2000, 4, 1
    exec dbo.sp_add_datatype_mapping 'Oracle', 'nvarchar' , 'long', 2147483647, 0, 1
    exec dbo.sp_add_datatype_mapping 'Oracle', 'datetime' , 'date', 255, 0, 1
    exec dbo.sp_add_datatype_mapping 'Oracle', 'smalldatetime' , 'date', 255, 0, 1
    exec dbo.sp_add_datatype_mapping 'Oracle', 'decimal' , 'number', 255, 3, 1
    exec dbo.sp_add_datatype_mapping 'Oracle', 'numeric' , 'number', 255, 3, 1
    exec dbo.sp_add_datatype_mapping 'Oracle', 'float' , 'float', 255, 0, 1
    exec dbo.sp_add_datatype_mapping 'Oracle', 'real' , 'float', 255, 0, 1
    exec dbo.sp_add_datatype_mapping 'Oracle', 'bigint' , 'number', 255, 3, 1
    exec dbo.sp_add_datatype_mapping 'Oracle', 'int' , 'number', 255, 3, 1
    exec dbo.sp_add_datatype_mapping 'Oracle', 'smallint' , 'number', 255, 3, 1
    exec dbo.sp_add_datatype_mapping 'Oracle', 'tinyint' , 'number', 255, 3, 1
    exec dbo.sp_add_datatype_mapping 'Oracle', 'money' , 'number', 255, 3, 1
    exec dbo.sp_add_datatype_mapping 'Oracle', 'smallmoney' , 'number', 255, 3, 1
    exec dbo.sp_add_datatype_mapping 'Oracle', 'bit' , 'number', 255, 3, 1
    exec dbo.sp_add_datatype_mapping 'Oracle', 'sysname' , 'char', 255, 4, 1
    exec dbo.sp_add_datatype_mapping 'Oracle', 'timestamp' , 'raw', 255, 4, 1
    exec dbo.sp_add_datatype_mapping 'Oracle', 'uniqueidentifier' , 'char', 255, 4, 1
    exec dbo.sp_add_datatype_mapping 'Oracle', 'text' , 'long', 2147483647, 0, 1
    exec dbo.sp_add_datatype_mapping 'Oracle', 'ntext' , 'long', 2147483647, 0, 1
    exec dbo.sp_add_datatype_mapping 'Oracle', 'image' , 'long raw', 2147483647, 0, 1

	-- MS SSCE
	exec dbo.sp_add_datatype_mapping 'MS SSCE', 'binary' , 'binary', 255, 4, 1
	exec dbo.sp_add_datatype_mapping 'MS SSCE', 'varbinary' , 'varbinary', 255, 4, 1
	exec dbo.sp_add_datatype_mapping 'MS SSCE', 'binary' , 'image', 1073741824, 0, 1
	exec dbo.sp_add_datatype_mapping 'MS SSCE', 'varbinary' , 'image', 1073741824, 0, 1
	exec dbo.sp_add_datatype_mapping 'MS SSCE', 'sql_variant' , 'ntext', 1073741824, 0, 1
	exec dbo.sp_add_datatype_mapping 'MS SSCE', 'varchar' , 'national char varying', 255, 4, 1
	exec dbo.sp_add_datatype_mapping 'MS SSCE', 'varchar' , 'ntext', 1073741824, 0, 1
	exec dbo.sp_add_datatype_mapping 'MS SSCE', 'nchar' , 'nchar', 255, 4, 1
	exec dbo.sp_add_datatype_mapping 'MS SSCE', 'nchar' , 'ntext', 1073741824, 0, 1
	exec dbo.sp_add_datatype_mapping 'MS SSCE', 'char' , 'nchar', 255, 4, 1
	exec dbo.sp_add_datatype_mapping 'MS SSCE', 'char' , 'ntext', 1073741824, 0, 1
	exec dbo.sp_add_datatype_mapping 'MS SSCE', 'nvarchar' , 'national char varying', 255, 4, 1
	exec dbo.sp_add_datatype_mapping 'MS SSCE', 'nvarchar' , 'ntext', 1073741824, 0, 1
	exec dbo.sp_add_datatype_mapping 'MS SSCE', 'datetime' , 'datetime', 255, 0, 1
	exec dbo.sp_add_datatype_mapping 'MS SSCE', 'smalldatetime' , 'datetime', 255, 0, 1
	exec dbo.sp_add_datatype_mapping 'MS SSCE', 'decimal' , 'numeric', 255, 3, 1
	exec dbo.sp_add_datatype_mapping 'MS SSCE', 'numeric' , 'numeric', 255, 3, 1
	exec dbo.sp_add_datatype_mapping 'MS SSCE', 'float' , 'float', 255, 0, 1
	exec dbo.sp_add_datatype_mapping 'MS SSCE', 'real' , 'real', 255, 0, 1
	exec dbo.sp_add_datatype_mapping 'MS SSCE', 'bigint' , 'bigint', 255, 0, 1
	exec dbo.sp_add_datatype_mapping 'MS SSCE', 'int' , 'int', 255, 0, 1
	exec dbo.sp_add_datatype_mapping 'MS SSCE', 'smallint' , 'smallint', 255, 0, 1
	exec dbo.sp_add_datatype_mapping 'MS SSCE', 'tinyint' , 'tinyint', 255, 0, 1
	exec dbo.sp_add_datatype_mapping 'MS SSCE', 'money' , 'money', 255, 0, 1
	exec dbo.sp_add_datatype_mapping 'MS SSCE', 'smallmoney' , 'money', 255, 0, 1
	exec dbo.sp_add_datatype_mapping 'MS SSCE', 'bit' , 'bit', 255, 0, 1
	exec dbo.sp_add_datatype_mapping 'MS SSCE', 'sysname' , 'national char varying', 255, 4, 1
	exec dbo.sp_add_datatype_mapping 'MS SSCE', 'timestamp' , 'binary', 255, 4, 1
	exec dbo.sp_add_datatype_mapping 'MS SSCE', 'uniqueidentifier' , 'uniqueidentifier', 255, 0, 1
	exec dbo.sp_add_datatype_mapping 'MS SSCE', 'text' , 'ntext', 1073741824, 0, 1
	exec dbo.sp_add_datatype_mapping 'MS SSCE', 'ntext' , 'ntext', 1073741824, 0, 1
	exec dbo.sp_add_datatype_mapping 'MS SSCE', 'image' , 'image', 1073741824, 0, 1

	--DB2/400
	exec dbo.sp_add_datatype_mapping 'DB2/400', 'bit', 'SMALLINT', 1,  0, 1
	exec dbo.sp_add_datatype_mapping 'DB2/400', 'tinyint', 'SMALLINT', 3, 0, 1
	exec dbo.sp_add_datatype_mapping 'DB2/400', 'smallint', 'SMALLINT', 5, 0, 1
	exec dbo.sp_add_datatype_mapping 'DB2/400', 'int', 'INT', 10, 0, 1

	exec dbo.sp_add_datatype_mapping 'DB2/400', 'char', 'CHAR', 8000, 4, 1
	exec dbo.sp_add_datatype_mapping 'DB2/400', 'varchar', 'VARCHAR', 8000, 4, 1
	exec dbo.sp_add_datatype_mapping 'DB2/400', 'smalldatetime', 'TIMESTAMP', 26, 0, 1
	exec dbo.sp_add_datatype_mapping 'DB2/400', 'datetime', 'TIMESTAMP', 26, 0, 1
	
	exec dbo.sp_add_datatype_mapping 'DB2/400', 'real', 'REAL', 24, 0, 1
	exec dbo.sp_add_datatype_mapping 'DB2/400', 'decimal', 'DECIMAL', 31, 3, 1
	exec dbo.sp_add_datatype_mapping 'DB2/400', 'double precision', 'DOUBLE', 53, 0, 1
	exec dbo.sp_add_datatype_mapping 'DB2/400', 'float', 'FLOAT', 53, 0, 1
	exec dbo.sp_add_datatype_mapping 'DB2/400', 'numeric', 'NUMERIC', 31, 3, 1

	exec dbo.sp_add_datatype_mapping 'DB2/400', 'smallmoney', 'DECIMAL', 10, 3, 1
	exec dbo.sp_add_datatype_mapping 'DB2/400', 'money', 'DECIMAL', 19, 3, 1

	exec dbo.sp_add_datatype_mapping 'DB2/400', 'varbinary', 'VARCHAR () FOR BIT DATA', 8000, 4, 1
	exec dbo.sp_add_datatype_mapping 'DB2/400', 'binary', 'CHAR () FOR BIT DATA', 8000, 4, 1

	exec dbo.sp_add_datatype_mapping 'DB2/400', 'timestamp', 'CHAR () FOR BIT DATA', 8, 4, 1
	exec dbo.sp_add_datatype_mapping 'DB2/400', 'uniqueidentifier', 'CHAR', 38, 4, 1
	exec dbo.sp_add_datatype_mapping 'DB2/400', 'image', 'VARCHAR () FOR BIT DATA', 32739, 4, 1
	exec dbo.sp_add_datatype_mapping 'DB2/400', 'text', 'VARCHAR', 32739, 4, 1

	--DB2/MVS
	exec dbo.sp_add_datatype_mapping 'DB2/MVS', 'bit', 'SMALLINT', 1,  0, 1
	exec dbo.sp_add_datatype_mapping 'DB2/MVS', 'tinyint', 'SMALLINT', 3, 0, 1
	exec dbo.sp_add_datatype_mapping 'DB2/MVS', 'smallint', 'SMALLINT', 5, 0, 1
	exec dbo.sp_add_datatype_mapping 'DB2/MVS', 'int', 'INT', 10, 0, 1

	exec dbo.sp_add_datatype_mapping 'DB2/MVS', 'char', 'CHAR', 254, 4, 1
	exec dbo.sp_add_datatype_mapping 'DB2/MVS', 'varchar', 'VARCHAR', 4045, 4, 1
	exec dbo.sp_add_datatype_mapping 'DB2/MVS', 'char', 'VARCHAR', 4045, 4, 1
	exec dbo.sp_add_datatype_mapping 'DB2/MVS', 'smalldatetime', 'TIMESTAMP', 26, 0, 1
	exec dbo.sp_add_datatype_mapping 'DB2/MVS', 'datetime', 'TIMESTAMP', 26, 0, 1

	exec dbo.sp_add_datatype_mapping 'DB2/MVS', 'real', 'REAL', 24, 0, 1
	exec dbo.sp_add_datatype_mapping 'DB2/MVS', 'decimal', 'DECIMAL', 31, 3, 1
	exec dbo.sp_add_datatype_mapping 'DB2/MVS', 'double precision', 'DOUBLE', 53, 0, 1
	exec dbo.sp_add_datatype_mapping 'DB2/MVS', 'float', 'FLOAT', 53, 0, 1
	exec dbo.sp_add_datatype_mapping 'DB2/MVS', 'numeric', 'NUMERIC', 31, 3, 1

	exec dbo.sp_add_datatype_mapping 'DB2/MVS', 'smallmoney', 'DECIMAL', 10, 3, 1
	exec dbo.sp_add_datatype_mapping 'DB2/MVS', 'money', 'DECIMAL', 19, 3, 1

	exec dbo.sp_add_datatype_mapping 'DB2/MVS', 'varbinary', 'VARCHAR () FOR BIT DATA', 4045, 4, 1
	exec dbo.sp_add_datatype_mapping 'DB2/MVS', 'binary', 'CHAR () FOR BIT DATA', 254, 4, 1

	exec dbo.sp_add_datatype_mapping 'DB2/MVS', 'timestamp', 'CHAR () FOR BIT DATA', 8, 4, 1
	exec dbo.sp_add_datatype_mapping 'DB2/MVS', 'uniqueidentifier', 'CHAR', 38, 4, 1
	exec dbo.sp_add_datatype_mapping 'DB2/MVS', 'image', 'VARCHAR () FOR BIT DATA', 4045, 4, 1
	exec dbo.sp_add_datatype_mapping 'DB2/MVS', 'text', 'VARCHAR', 4045, 4, 1

	--DB2/NT
	exec dbo.sp_add_datatype_mapping 'DB2/NT', 'bit', 'SMALLINT', 1,  0, 1
	exec dbo.sp_add_datatype_mapping 'DB2/NT', 'tinyint', 'SMALLINT', 3, 0, 1
	exec dbo.sp_add_datatype_mapping 'DB2/NT', 'smallint', 'SMALLINT', 5, 0, 1
	exec dbo.sp_add_datatype_mapping 'DB2/NT', 'int', 'INT', 10, 0, 1

	exec dbo.sp_add_datatype_mapping 'DB2/NT', 'char', 'CHAR', 254, 4, 1
	exec dbo.sp_add_datatype_mapping 'DB2/NT', 'varchar', 'VARCHAR', 4000, 4, 1
	exec dbo.sp_add_datatype_mapping 'DB2/NT', 'char', 'VARCHAR', 4000, 4, 1
	exec dbo.sp_add_datatype_mapping 'DB2/NT', 'smalldatetime', 'TIMESTAMP', 26, 0, 1
	exec dbo.sp_add_datatype_mapping 'DB2/NT', 'datetime', 'TIMESTAMP', 26, 0, 1

	exec dbo.sp_add_datatype_mapping 'DB2/NT', 'real', 'REAL', 24, 0, 1
	exec dbo.sp_add_datatype_mapping 'DB2/NT', 'decimal', 'DECIMAL', 31, 3, 1
	exec dbo.sp_add_datatype_mapping 'DB2/NT', 'double precision', 'DOUBLE', 53, 0, 1
	exec dbo.sp_add_datatype_mapping 'DB2/NT', 'float', 'FLOAT', 53, 0, 1
	exec dbo.sp_add_datatype_mapping 'DB2/NT', 'numeric', 'NUMERIC', 31, 3, 1

	exec dbo.sp_add_datatype_mapping 'DB2/NT', 'smallmoney', 'DECIMAL', 10, 3, 1
	exec dbo.sp_add_datatype_mapping 'DB2/NT', 'money', 'DECIMAL', 19, 3, 1

	exec dbo.sp_add_datatype_mapping 'DB2/NT', 'varbinary', 'VARCHAR () FOR BIT DATA', 4000, 4, 1
	exec dbo.sp_add_datatype_mapping 'DB2/NT', 'binary', 'CHAR () FOR BIT DATA', 254, 4, 1

	exec dbo.sp_add_datatype_mapping 'DB2/NT', 'timestamp', 'CHAR () FOR BIT DATA', 8, 4, 1
	exec dbo.sp_add_datatype_mapping 'DB2/NT', 'uniqueidentifier', 'CHAR', 38, 4, 1
	exec dbo.sp_add_datatype_mapping 'DB2/NT', 'image', 'VARCHAR () FOR BIT DATA', 4000, 4, 1
	exec dbo.sp_add_datatype_mapping 'DB2/NT', 'text', 'VARCHAR', 4000, 4, 1

	--DB2/6000
	exec dbo.sp_add_datatype_mapping 'DB2/6000', 'bit', 'SMALLINT', 1,  0, 1
	exec dbo.sp_add_datatype_mapping 'DB2/6000', 'tinyint', 'SMALLINT', 3, 0, 1
	exec dbo.sp_add_datatype_mapping 'DB2/6000', 'smallint', 'SMALLINT', 5, 0, 1
	exec dbo.sp_add_datatype_mapping 'DB2/6000', 'int', 'INT', 10, 0, 1

	exec dbo.sp_add_datatype_mapping 'DB2/6000', 'char', 'CHAR', 254, 4, 1
	exec dbo.sp_add_datatype_mapping 'DB2/6000', 'varchar', 'VARCHAR', 4000, 4, 1
	exec dbo.sp_add_datatype_mapping 'DB2/6000', 'char', 'VARCHAR', 4000, 4, 1
	exec dbo.sp_add_datatype_mapping 'DB2/6000', 'smalldatetime', 'TIMESTAMP', 26, 0, 1
	exec dbo.sp_add_datatype_mapping 'DB2/6000', 'datetime', 'TIMESTAMP', 26, 0, 1

	exec dbo.sp_add_datatype_mapping 'DB2/6000', 'real', 'REAL', 24, 0, 1
	exec dbo.sp_add_datatype_mapping 'DB2/6000', 'decimal', 'DECIMAL', 31, 3, 1
	exec dbo.sp_add_datatype_mapping 'DB2/6000', 'double precision', 'DOUBLE', 53, 0, 1
	exec dbo.sp_add_datatype_mapping 'DB2/6000', 'float', 'FLOAT', 53, 0, 1
	exec dbo.sp_add_datatype_mapping 'DB2/6000', 'numeric', 'NUMERIC', 31, 3, 1

	exec dbo.sp_add_datatype_mapping 'DB2/6000', 'smallmoney', 'DECIMAL', 10, 3, 1
	exec dbo.sp_add_datatype_mapping 'DB2/6000', 'money', 'DECIMAL', 19, 3, 1

	exec dbo.sp_add_datatype_mapping 'DB2/6000', 'varbinary', 'VARCHAR () FOR BIT DATA', 4000, 4, 1
	exec dbo.sp_add_datatype_mapping 'DB2/6000', 'binary', 'CHAR () FOR BIT DATA', 254, 4, 1

	exec dbo.sp_add_datatype_mapping 'DB2/6000', 'timestamp', 'CHAR () FOR BIT DATA', 8, 4, 1
	exec dbo.sp_add_datatype_mapping 'DB2/6000', 'uniqueidentifier', 'CHAR', 38, 4, 1
	exec dbo.sp_add_datatype_mapping 'DB2/6000', 'image', 'VARCHAR () FOR BIT DATA', 4000, 4, 1
	exec dbo.sp_add_datatype_mapping 'DB2/6000', 'text', 'VARCHAR', 4000, 4, 1

    return 0
FAILURE:
    return 1
go

--------------------------------------------------------------------------------
--.	Common system objects (replsys.sql)
--------------------------------------------------------------------------------

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

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

--
-- proc that is invoked by distribution agent to populate
-- MSsubscription_articles with all the necessary article
-- this is done currently only for queued publications 
--
CREATE PROCEDURE sp_addqueued_artinfo (
	@artid				int
	,@article			sysname
	,@publisher 		sysname
	,@publisher_db		sysname
	,@publication		sysname
	,@dest_table		sysname
	,@owner				sysname
	,@cft_table			sysname
	,@columns			binary(32))
AS
BEGIN
	set nocount on
	declare 	@agent_id int
				,@update_mode int
				,@retcode int

	--
	-- Create system table MSsubscription_articles if it does not exist
	--
	IF NOT EXISTS (SELECT * FROM sysobjects WHERE 
		type = 'U' AND name = 'MSsubscription_articles')
	BEGIN
		CREATE TABLE dbo.MSsubscription_articles
		(
			agent_id		int NOT NULL,		-- related entry in MSsubscription_agents
			artid			int NOT NULL,		-- article id
			article			sysname,			-- article name
			dest_table		sysname,			-- destination table
			owner			sysname,
			cft_table		sysname,			-- conflict table
			columns			binary(32)
		)
		IF (@@ERROR != 0)
		begin
			raiserror('Debug:sp_addqueued_artinfo - create MSsubscription_articles failed', 16, 1)
			return (1)
		end

		CREATE UNIQUE CLUSTERED INDEX ucMSsubscription_articles ON dbo.MSsubscription_articles(agent_id, artid)
		IF (@@ERROR != 0)
		begin
			raiserror('Debug:sp_addqueued_artinfo - create index for MSsubscription_articles failed', 16, 1)
			return (1)
		end
		
		EXEC dbo.sp_MS_marksystemobject 'MSsubscription_articles'
	END

	--
	-- insert the row for the given article
	--
	select @agent_id = id, @update_mode = update_mode
	from MSsubscription_agents 
	where UPPER(publisher) = UPPER(@publisher) and 
		publisher_db = @publisher_db and 
		publication = @publication 

	if (@agent_id IS NULL)
	begin
		raiserror('Debug:sp_addqueued_artinfo - agent_id is null for [%s].[%s].[%s]', 
			16, 1, @publisher, @publisher_db, @publication)
		return (1)
	end

	-- If the subscription is read only or immediate, no need to article info.
	if @update_mode in (0,1)
		return 0

	--
	-- If MSMQ Queued mode - check if the subscriber is compliant for MSMQ 2.0
	--
	if (@update_mode in (2,3))
	begin
		if ((platform() & 0x1) != 0x1)
		begin
			--
			-- Win 9X platform
			--
			raiserror(21334, 16, 1, '2.0')
			return (1)
		end

		--
		-- Now we use xp_MSver to detect NT OS version
		-- MSMQ subscription only allowed for platforms that support MSMQ 2.0
		-- version 5.0.2195 or higher
		--
		create table #tosversion ( propid int, propname sysname collate database_default, value int, charvalue nvarchar(255) collate database_default)
		insert into #tosversion (propid, propname, value, charvalue)
			exec master.dbo.xp_msver N'WindowsVersion'

		declare @vervalue int
			,@lobyte tinyint
			,@hibyte tinyint
			,@loword smallint
			,@hiword smallint

		--
		-- low order byte of low order word = OSmajor, high order byte of low order word = OSminor
		-- high order word = OSbuild
		--
		select @vervalue = value from #tosversion where propname = N'WindowsVersion'
		select @loword = (@vervalue & 0xffff)
				,@hiword = (@vervalue / 0x10000) & 0xffff
		select @lobyte = @loword & 0xff
				,@hibyte = (@loword / 100) & 0xff
		drop table #tosversion

		--
		-- check for OS major version
		--
		if (@lobyte < 5)
		begin
			raiserror(21334, 16, 2, '2.0')
			return (1)
		end

		--
		-- check for OS build version
		--
		if (@lobyte = 5 and @hiword < 2195)
		begin
			raiserror(21334, 16, 3, '2.0')
			return (1)
		end
	end

	--
	-- Check for owner - use current user for NULL value
	--
	if (@owner IS NULL or lower(@owner) = N'null' collate database_default)
		select @owner = user_name()

	if exists (select * from MSsubscription_articles where agent_id = @agent_id and artid = @artid)
		delete MSsubscription_articles where agent_id = @agent_id and artid = @artid
		
	insert into MSsubscription_articles(agent_id, artid, article, dest_table, owner, cft_table, columns)
		values (@agent_id, @artid, @article, @dest_table, @owner, @cft_table, @columns)

	IF (@@ERROR != 0)
	begin
		raiserror('Debug:sp_addqueued_artinfo - insert failed', 16, 1)
		return (1)
	end

	--
	-- Do the queue initialization here
	-- this way we can initialize Snapshot/Logbased queued tran from one place
	--
	exec @retcode = dbo.sp_MSreset_queue @publisher, @publisher_db, @publication, @artid
	IF (@retcode != 0 or @@ERROR != 0)
	begin
		raiserror('Debug:sp_addqueued_artinfo - sp_MSreset_queue failed', 16, 1)
		return (1)
	end
	
	--
	-- all done
	--
	return 0	
END
go
grant exec on dbo.sp_addqueued_artinfo to public
go

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

create procedure sp_setreplfailovermode (
	@publisher sysname,
	@publisher_db sysname,
	@publication sysname,
    @failover_mode nvarchar (10), 
    @override tinyint = 0)
as
begin
	set nocount on
	declare	@failover_mode_id tinyint, 
			@current_failover_mode_id bit,
			@retcode int,
			@queue_id sysname,
			@fqueue_empty int,
			@update_mode int,
			@queue_server sysname

	if @failover_mode not in (N'immediate', N'sync', N'queued')
	begin
		raiserror (21184, 16, 1, '@failover_mode', 'immediate', 'sync', 'queued')
		return 1			
	end
	else
	begin
		if @failover_mode = 'immediate' or @failover_mode = 'sync'
			select @failover_mode_id = 0
		else if @failover_mode = 'queued'
			select @failover_mode_id = 1
	end

	--
	-- Only valid to get/set failover_mode, 
	-- if update_mode is failover (3,5)
	--
	select	@queue_id = queue_id, 
			@queue_server = queue_server,
			@current_failover_mode_id = failover_mode,
			@update_mode = update_mode
	from MSsubscription_agents
	where UPPER(publisher) = UPPER(@publisher) and 
		publisher_db = @publisher_db and
		publication = @publication and
		update_mode in (3,5)

	-- no such row exists
	if (@current_failover_mode_id is NULL)		
	begin
		raiserror (21185, 16, 1)
		return 1
	end

	-- should have a queue entry
	if (@queue_id is NULL)
	begin
		raiserror(21186, 16, 1, @publisher)
		return 1
	end

	--
	-- do the transition
	--
	if ((@current_failover_mode_id = 0 and @failover_mode_id = 0) or
			(@current_failover_mode_id = 1 and @failover_mode_id = 1))
	begin
		--
		-- Going from immediate to immediate, queued to queued is no-op
		--
		raiserror (21187, 16, 1)
	end
	else if (@current_failover_mode_id = 0 and @failover_mode_id = 1)
	begin
		--
		-- Going from immediate to queued : update MSsubscription_agents
		--
		update MSsubscription_agents
			set failover_mode = @failover_mode_id
			where UPPER(publisher) = UPPER(@publisher) and 
				publisher_db = @publisher_db and
				publication = @publication and
				update_mode in (3,5)

		raiserror (21188, 10, 1, 'immediate', 'queued')
	end
	else if (@current_failover_mode_id = 1 and @failover_mode_id = 0)
	begin
		--
		-- Going from queued to immediate : if override is not set
		-- then check if the queue is empty and then allow if empty.
		-- If override is set, just update MSsubscription_agents
		--
		if (@override = 0)
		begin
			if (@update_mode = 3)
			begin
				--
				-- MSMQ processing
				-- prefix the queue_id with queue server in direct format
				-- and then perform peek in the queue
				--
				select @queue_id = N'DIRECT=OS:' + @queue_server + N'\PRIVATE$\' + @queue_id
				
				exec @retcode = master.dbo.xp_peekqueue @queue_id, @fqueue_empty output, 0
				if (@@error != 0 or @retcode != 0)
				begin
					raiserror('sp_setreplfailovermode(debug): xp_peekqueue execution failed', 16, 1)
					return 1
				end

				if (@fqueue_empty != 1)
				begin
					raiserror(21189, 16, 1, @queue_id)
					return 1
				end
			end
			else
			begin
				--
				-- SQL Queue processing
				--
				if exists (select * from MSreplication_queue 
					where UPPER(publisher) = UPPER(@publisher) and 
							publisher_db = @publisher_db and
							publication = @publication )
				begin
					raiserror(21189, 16, 2, @queue_id)
					return 1
				end
			end
		end
		else
		begin
			raiserror(21190, 10, 1, 'queued', 'immediate')
		end

		--
		-- update MSsubscription_agents
		--
		update MSsubscription_agents
			set failover_mode = @failover_mode_id
			where publisher = @publisher and 
				publisher_db = @publisher_db and
				publication = @publication and
				update_mode in (3,5)

		raiserror (21188, 10, 1, 'queued', 'immediate')
	end

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

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

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

--
-- sp_MSupdate_mqserver_subdb
--
-- This procedure is called to upgrade the MSMQ related entries in 
-- MSsubscription_agents in subscription database - this processing 
-- cannot be done directly inside sp_MScreate_sub_tables since we need the 
-- queue_server column for the processing and we can get a syntax error 
-- if the table exists without the column
--
-- Assumption : MSsubscription_agents has been created 
--				and column queue_server exists
--
raiserror('Creating procedure sp_MSupdate_mqserver_subdb', 0,1)
GO
create procedure sp_MSupdate_mqserver_subdb
as
begin
	if exists (select * from dbo.MSsubscription_agents
		where queue_id IS NOT NULL and queue_id != N'mssqlqueue' and queue_server IS NULL)
	begin
		--
		-- we have entries for active Queued subscriptions using MSMQ that need upgrade
		--
		declare @queue_server sysname
				,@publisher sysname
				,@publisher_db sysname
				,@publication sysname

		DECLARE hC CURSOR LOCAL FAST_FORWARD FOR 
			select publisher, publisher_db, publication
			from dbo.MSsubscription_agents
			where queue_id IS NOT NULL 
				and queue_id != N'mssqlqueue' 
				and queue_server IS NULL

		OPEN hC
		FETCH hC INTO @publisher, @publisher_db, @publication
		WHILE (@@fetch_status != -1)
		BEGIN
			--
			-- the table MSsubscription_properties has to exist;
			-- prepare the queue server name from distributor name
			-- strip the instance name if necessary
			--
			select @queue_server = ISNULL(distributor, @@servername)
			from dbo.MSsubscription_properties
			where UPPER(publisher) = UPPER(@publisher) and
						publisher_db = @publisher_db and
						publication = @publication

			if (charindex(N'\', @queue_server) > 0)
				select @queue_server = substring(@queue_server, 1, charindex(N'\', @queue_server) - 1)

			--
			-- set the queue_server for this entry
			--
			update dbo.MSsubscription_agents
			set queue_server = @queue_server
			where UPPER(publisher) = UPPER(@publisher) and
						publisher_db = @publisher_db and
						publication = @publication

			--
			-- get next entry
			--
			FETCH hC INTO @publisher, @publisher_db, @publication
		END
		CLOSE hC
		DEALLOCATE hC
	end
end
go
exec dbo.sp_MS_marksystemobject sp_MSupdate_mqserver_subdb
go

grant exec on dbo.sp_MSupdate_mqserver_subdb to public
go

--------------------------------------------------------------------------------
--. fn_replinttobitstring
--------------------------------------------------------------------------------
if exists (select * from sysobjects 
    where type = 'FN'
    and name = N'fn_replinttobitstring' )
    drop function system_function_schema.fn_replinttobitstring
go

raiserror('Creating function fn_replinttobitstring', 0,1)
go

--
-- Name: fn_replinttobitstring
-- 
-- Description: This function returns the bit pattern in string format for 
--              the specified 32-bit integer. For example, select 
--              fn_replinttobitstring(0x80000001) would yield
--              '10000000000000000000000000000001'
--
-- Parameter:   @number int
--
-- Returns:     char(32)
--
-- Security:    Execute permission of this function is granted to public.
--
create function system_function_schema.fn_replinttobitstring(
    @number int
    ) returns char(32)
as
begin

    declare @bitstring nvarchar(32)

    if (@number & 0x80000000) <> 0 select @bitstring = '1' else select @bitstring = '0'
    if (@number & 0x40000000) <> 0 select @bitstring = @bitstring + '1' else select @bitstring = @bitstring + '0'
    if (@number & 0x20000000) <> 0 select @bitstring = @bitstring + '1' else select @bitstring = @bitstring + '0'
    if (@number & 0x10000000) <> 0 select @bitstring = @bitstring + '1' else select @bitstring = @bitstring + '0'
    if (@number & 0x08000000) <> 0 select @bitstring = @bitstring + '1' else select @bitstring = @bitstring + '0'
    if (@number & 0x04000000) <> 0 select @bitstring = @bitstring + '1' else select @bitstring = @bitstring + '0'
    if (@number & 0x02000000) <> 0 select @bitstring = @bitstring + '1' else select @bitstring = @bitstring + '0'
    if (@number & 0x01000000) <> 0 select @bitstring = @bitstring + '1' else select @bitstring = @bitstring + '0'
    if (@number & 0x00800000) <> 0 select @bitstring = @bitstring + '1' else select @bitstring = @bitstring + '0'
    if (@number & 0x00400000) <> 0 select @bitstring = @bitstring + '1' else select @bitstring = @bitstring + '0'
    if (@number & 0x00200000) <> 0 select @bitstring = @bitstring + '1' else select @bitstring = @bitstring + '0'
    if (@number & 0x00100000) <> 0 select @bitstring = @bitstring + '1' else select @bitstring = @bitstring + '0'
    if (@number & 0x00080000) <> 0 select @bitstring = @bitstring + '1' else select @bitstring = @bitstring + '0'
    if (@number & 0x00040000) <> 0 select @bitstring = @bitstring + '1' else select @bitstring = @bitstring + '0'
    if (@number & 0x00020000) <> 0 select @bitstring = @bitstring + '1' else select @bitstring = @bitstring + '0'
    if (@number & 0x00010000) <> 0 select @bitstring = @bitstring + '1' else select @bitstring = @bitstring + '0'
    if (@number & 0x00008000) <> 0 select @bitstring = @bitstring + '1' else select @bitstring = @bitstring + '0'
    if (@number & 0x00004000) <> 0 select @bitstring = @bitstring + '1' else select @bitstring = @bitstring + '0'
    if (@number & 0x00002000) <> 0 select @bitstring = @bitstring + '1' else select @bitstring = @bitstring + '0'
    if (@number & 0x00001000) <> 0 select @bitstring = @bitstring + '1' else select @bitstring = @bitstring + '0'
    if (@number & 0x00000800) <> 0 select @bitstring = @bitstring + '1' else select @bitstring = @bitstring + '0'
    if (@number & 0x00000400) <> 0 select @bitstring = @bitstring + '1' else select @bitstring = @bitstring + '0'
    if (@number & 0x00000200) <> 0 select @bitstring = @bitstring + '1' else select @bitstring = @bitstring + '0'
    if (@number & 0x00000100) <> 0 select @bitstring = @bitstring + '1' else select @bitstring = @bitstring + '0'
    if (@number & 0x00000080) <> 0 select @bitstring = @bitstring + '1' else select @bitstring = @bitstring + '0'
    if (@number & 0x00000040) <> 0 select @bitstring = @bitstring + '1' else select @bitstring = @bitstring + '0'
    if (@number & 0x00000020) <> 0 select @bitstring = @bitstring + '1' else select @bitstring = @bitstring + '0'
    if (@number & 0x00000010) <> 0 select @bitstring = @bitstring + '1' else select @bitstring = @bitstring + '0'
    if (@number & 0x00000008) <> 0 select @bitstring = @bitstring + '1' else select @bitstring = @bitstring + '0'
    if (@number & 0x00000004) <> 0 select @bitstring = @bitstring + '1' else select @bitstring = @bitstring + '0'
    if (@number & 0x00000002) <> 0 select @bitstring = @bitstring + '1' else select @bitstring = @bitstring + '0'
    if (@number & 0x00000001) <> 0 select @bitstring = @bitstring + '1' else select @bitstring = @bitstring + '0'

    return @bitstring
end
go
grant execute on system_function_schema.fn_replinttobitstring to public
go

--------------------------------------------------------------------------------
--. fn_replbitstringtoint
--------------------------------------------------------------------------------
if exists (select * from sysobjects 
    where type = 'FN'
    and name = N'fn_replbitstringtoint' )
    drop function system_function_schema.fn_replbitstringtoint
go

raiserror('Creating function fn_replbitstringtoint', 0,1)
go

--
-- Name: fn_replbitstringtoint
--
-- Description: This function returns the integer value of the specified
--              32-bit bit pattern in string format.
--
-- Parameter:   @bitstring char(32)
--
-- Notes:
--
-- Returns:     int
--
-- Security:    Execute permission of this function is granted to public.
--
create function system_function_schema.fn_replbitstringtoint(
    @bitstring char(32)
    ) returns int
as
begin
    declare @number int 
    select @number = 0
    if (substring(@bitstring,1,1) = '1') select @number = @number | 0x80000000 
    if (substring(@bitstring,2,1) = '1') select @number = @number | 0x40000000 
    if (substring(@bitstring,3,1) = '1') select @number = @number | 0x20000000 
    if (substring(@bitstring,4,1) = '1') select @number = @number | 0x10000000 
    if (substring(@bitstring,5,1) = '1') select @number = @number | 0x08000000 
    if (substring(@bitstring,6,1) = '1') select @number = @number | 0x04000000 
    if (substring(@bitstring,7,1) = '1') select @number = @number | 0x02000000 
    if (substring(@bitstring,8,1) = '1') select @number = @number | 0x01000000 
    if (substring(@bitstring,9,1) = '1') select @number = @number | 0x00800000 
    if (substring(@bitstring,10,1) = '1') select @number = @number | 0x00400000 
    if (substring(@bitstring,11,1) = '1') select @number = @number | 0x00200000 
    if (substring(@bitstring,12,1) = '1') select @number = @number | 0x00100000 
    if (substring(@bitstring,13,1) = '1') select @number = @number | 0x00080000 
    if (substring(@bitstring,14,1) = '1') select @number = @number | 0x00040000 
    if (substring(@bitstring,15,1) = '1') select @number = @number | 0x00020000 
    if (substring(@bitstring,16,1) = '1') select @number = @number | 0x00010000 
    if (substring(@bitstring,17,1) = '1') select @number = @number | 0x00008000 
    if (substring(@bitstring,18,1) = '1') select @number = @number | 0x00004000 
    if (substring(@bitstring,19,1) = '1') select @number = @number | 0x00002000 
    if (substring(@bitstring,20,1) = '1') select @number = @number | 0x00001000 
    if (substring(@bitstring,21,1) = '1') select @number = @number | 0x00000800 
    if (substring(@bitstring,22,1) = '1') select @number = @number | 0x00000400 
    if (substring(@bitstring,23,1) = '1') select @number = @number | 0x00000200 
    if (substring(@bitstring,24,1) = '1') select @number = @number | 0x00000100 
    if (substring(@bitstring,25,1) = '1') select @number = @number | 0x00000080 
    if (substring(@bitstring,26,1) = '1') select @number = @number | 0x00000040 
    if (substring(@bitstring,27,1) = '1') select @number = @number | 0x00000020 
    if (substring(@bitstring,28,1) = '1') select @number = @number | 0x00000010 
    if (substring(@bitstring,29,1) = '1') select @number = @number | 0x00000008 
    if (substring(@bitstring,30,1) = '1') select @number = @number | 0x00000004 
    if (substring(@bitstring,31,1) = '1') select @number = @number | 0x00000002 
    if (substring(@bitstring,32,1) = '1') select @number = @number | 0x00000001 
    return @number
end
go
grant execute on system_function_schema.fn_replbitstringtoint to public
go


--------------------------------------------------------------------------------
--. fn_replrotr
--------------------------------------------------------------------------------
if exists (select * from sysobjects 
    where type = 'FN'
    and name = N'fn_replrotr' )
    drop function system_function_schema.fn_replrotr
go

raiserror('Creating function fn_replrotr', 0,1)
go

--
-- Name: fn_replrotr
--
-- Description: This is just a T-SQL implementation of the C function
--              _rotr().
--
-- Parameter:  @number int
--             @shift  int
--
-- Notes:      The effective shift will the positive remainder of @shift 
--             divided by 32. _rotl() can be implemented by passing in 
--             a negative shift.
--
-- Returns:    int
--
-- Security:   Execute permission of this function is granted to public.
--
create function system_function_schema.fn_replrotr(
    @number int,
    @shift int
    ) returns int
as
begin
    declare @bitstring char(32)
    select @shift = @shift % 32
    if @shift < 0
        select @shift = @shift + 32

    if (@shift != 0)
    begin
        select @bitstring = fn_replinttobitstring(@number)   
        select @bitstring = substring(@bitstring,32-@shift+1,@shift) + substring(@bitstring,1,32-@shift) 
        select @number = fn_replbitstringtoint(@bitstring)
    end
    return @number
end
go
grant execute on system_function_schema.fn_replrotr to public


--------------------------------------------------------------------------------
--. fn_repltrimleadingzerosinhexstr
--------------------------------------------------------------------------------
if exists (select * from sysobjects 
    where type = 'FN'
    and name = N'fn_repltrimleadingzerosinhexstr' )
    drop function system_function_schema.fn_repltrimleadingzerosinhexstr
go

raiserror('Creating function fn_repltrimleadingzerosinhexstr', 0,1)
go

-- 
-- Name: fn_repltrimleadingzerosinhexstr
--
-- Description: This function is used for trimming the leading zeros 
--              of a 32-bit hexadecimal string. For example,
--              select fn_repltrimleadingzerosinhexstr('0000abcd') would 
--              yield 'abcd'. A notable special case would be select
--              fn_repltrimleadingzerosinhexstr('00000000') which yields
--              the string '0' instead of ''.
--
-- Parameter:   @hexstr  nchar(8) - @hexstr is expected to be a full 
--              hexadecimal representation of a 32-bit integer without the
--              0x prefix
--
-- Notes: 
--
-- Returns:     nvarchar(8)
-- 
-- Security:    Execute permission of this function is granted to public.
--
create function system_function_schema.fn_repltrimleadingzerosinhexstr (
    @hexstr nchar(8)
    ) returns nvarchar(8)
as
begin
    declare @trimmed_hexstr nvarchar(8),
            @i int    
    select @i = 1
    while @i < 8
    begin
        if substring(@hexstr,@i,1) <> N'0'
            break;
        select @i = @i + 1
    end    
    select @trimmed_hexstr = substring(@hexstr,@i,8-@i+1)
    return @trimmed_hexstr
end
go
grant execute on system_function_schema.fn_repltrimleadingzerosinhexstr to public
go


--------------------------------------------------------------------------------
--. fn_replgenerateshorterfilenameprefix
--------------------------------------------------------------------------------
if exists (select * from sysobjects 
    where type = 'FN'
    and name = N'fn_replgenerateshorterfilenameprefix' )
    drop function system_function_schema.fn_replgenerateshorterfilenameprefix
go

raiserror('Creating function fn_replgenerateshorterfilenameprefix', 0,1)
go

--
-- Name: fn_replgenerateshorterfilenameprefix
--
-- Description: This function mimics the algorithm used by the snapshot 
--              agent to shorten any overly long component of a publication
--              snapshot folder with "overly long" defined as more than
--              30 characters in length (as specified in the @truncated_len
--              parameter of this function). 
--                
-- Parameters:  @prefix nvarchar(4000)
--              @truncated_len int
--
-- Notes:
--
-- Returns: nvarchar(4000)
--
-- Security: Execute permission on this function is granted to public.
--
create function system_function_schema.fn_replgenerateshorterfilenameprefix(
    @prefix nvarchar(4000),
    @truncated_len int
    ) returns nvarchar(4000)
as
begin
    declare @HashValueIndex int,
            @HashValue int,
            @CurrentByte tinyint,
            @TruncatedString varbinary(8000),
            @i int,
            @TruncatedStringLen int,
            @prefix_to_be_returned nvarchar(4000)

    select @prefix_to_be_returned = @prefix
    if (len(@prefix) > @truncated_len)
    begin
        select @TruncatedString = convert(varbinary(8000),substring(@prefix,@truncated_len-8+1,len(@prefix)-@truncated_len+8))
        select @TruncatedStringLen = len(@TruncatedString)
        select @HashValue = 0
        select @i = 1
        select @HashValueIndex = 0
        while @i <= @TruncatedStringLen
        begin
            select @CurrentByte = convert(tinyint,substring(@TruncatedString,@i,1))
            select @HashValue = @HashValue ^ fn_replrotr(@CurrentByte, -(8*@HashValueIndex))
            select @HashValue = fn_replrotr(@HashValue, @HashValueIndex + 1)
            if @CurrentByte != 0
            begin
                select @HashValueIndex = (@HashValueIndex + 1) % 4
            end
            select @i = @i + 1
        end
        select @prefix_to_be_returned = substring(@prefix,1,@truncated_len-8) 
        select @prefix_to_be_returned = @prefix_to_be_returned + lower(fn_repltrimleadingzerosinhexstr(substring(master.dbo.fn_varbintohexstr(convert(varbinary(4),@HashValue)) collate database_default,3,8)) collate database_default)
    end
    return @prefix_to_be_returned
end
go
grant execute on system_function_schema.fn_replgenerateshorterfilenameprefix to public      
go

--------------------------------------------------------------------------------
--. fn_replcomposepublicationsnapshotfolder
--------------------------------------------------------------------------------
if exists (select * from sysobjects 
    where type = 'FN'
    and name = N'fn_replcomposepublicationsnapshotfolder' )
    drop function system_function_schema.fn_replcomposepublicationsnapshotfolder
go

raiserror('Creating function fn_replcomposepublicationsnapshotfolder', 0,1)
go

--
-- Name: fn_replcomposepublicationsnapshotfolder
--
-- Description: This mimics the algorithm used by the snapshot agent for 
--              composing the name of a publication's snapshot folder.
--
-- Parameters:  @publisher    sysname
--              @publisher_db sysname 
--              @publication  sysname
--
-- Notes: 
--
-- Returns: nvarchar(255)
--
-- Security: Execute permission of this function is granted to public.
--
create function system_function_schema.fn_replcomposepublicationsnapshotfolder(
    @publisher     sysname,
    @publisher_db  sysname,
    @publication   sysname
) returns nvarchar(255)
as
begin
    declare @snapshot_folder_name nvarchar(255)
    
    select @snapshot_folder_name = fn_replgenerateshorterfilenameprefix(@publisher,30) collate database_default + N'_' +
                                   fn_replgenerateshorterfilenameprefix(@publisher_db,30) collate database_default + N'_' +
                                   fn_replgenerateshorterfilenameprefix(@publication,30) collate database_default
    return @snapshot_folder_name
end
go
grant execute on system_function_schema.fn_replcomposepublicationsnapshotfolder to public
go

--------------------------------------------------------------------------------
--. xp_updatelineage (obsolete xp - will be removed)
--------------------------------------------------------------------------------
if exists (select * from sysobjects
		where name = 'xp_updatelineage' and xtype = 'X')
	execute dbo.sp_dropextendedproc 'xp_updatelineage'
go

--------------------------------------------------------------------------------
--. xp_initcolvs (obsolete xp - will be removed)
--------------------------------------------------------------------------------
if exists (select * from sysobjects
		where name = 'xp_initcolvs' and xtype = 'X')
	execute dbo.sp_dropextendedproc 'xp_initcolvs'
go

--------------------------------------------------------------------------------
--. xp_updatecolvbm (obsolete xp - will be removed)
--------------------------------------------------------------------------------
if exists (select * from sysobjects
		where name = 'xp_updatecolvbm' and xtype = 'X')
	execute dbo.sp_dropextendedproc 'xp_updatecolvbm'
go


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

--
