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

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

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

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

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

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

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

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

    declare hConst cursor local fast_forward for
     select constid 
       from sysconstraints 
      where id = @objid
    if @@error<>0
        return 1

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

    return @retcode

end
go

exec sp_MS_marksystemobject sp_MSdroparticleconstraints
go

grant execute on sp_MSdroparticleconstraints to public 
go


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

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

--
-- Name: fn_generateparameterpattern
--
-- Description: Given the name of a parameter (excluding the leading / or -), 
--              this function will return the pattern for matching the
--              parameter in a case-insensitive manner. For example
--  
--              select fn_generateparameterpattern(N'Ouptut')
-- 
--              will return
--
--              %[-/][Oo][Uu][Tt][Pp][Uu][Tt]%
--
-- Parameters: @parameter nvarchar(200)
--             
-- Notes: The leading / or - should be omitted from @parameter
--
-- Returns: nvarchar(806)
--
-- Security: Execute permission of this function is granted to public 
--
print 'Creating function fn_generateparameterpattern'
print ''
go
create function system_function_schema.fn_generateparameterpattern(
    @parameter nvarchar(200)
    ) returns nvarchar(806)
begin
    declare @pattern          nvarchar(806)
    declare @parameter_length int
    declare @i                int
    declare @c                nvarchar(1)

    select @pattern = N'%[-/]'
    select @i = 1
    select @parameter_length = len(@parameter)
    
    while @i <= @parameter_length
    begin
    select @c = substring(@parameter, @i, 1)        
    select @pattern = @pattern + N'[' + upper(@c collate SQL_Latin1_General_CP1_CS_AS) + lower(@c collate SQL_Latin1_General_CP1_CS_AS) + N']'
        select @i = @i + 1
    end 
    select @pattern = @pattern + '%'
    return @pattern
end
go

grant execute on system_function_schema.fn_generateparameterpattern to public
go

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

	/* This is to make sure that the varbinary columns do not get padded */

	set ANSI_PADDING off

	DECLARE @exist bit 
	DECLARE @validsubs  int
	select @exist = 1
	
	begin tran
	save transaction MScreate_mergesystables

	exec dbo.sp_MScheckvalidsystables  @validsubs  output
	if @validsubs = 0
		exec dbo.sp_MSdrop_mergesystables
		
	if not exists (select * from sysobjects where name = 'sysmergepublications')
		begin

			raiserror('Creating table sysmergepublications',0,1)
			
			create table dbo.sysmergepublications
			(	publisher				sysname NOT NULL default @@servername,
				publisher_db			sysname NOT NULL default db_name(),
				name					sysname				NOT NULL,
				description				nvarchar(255) 		NULL,
				retention				int					NULL,
				publication_type 		tinyint				NULL,
				pubid 					uniqueidentifier 	NOT NULL,
				designmasterid			uniqueidentifier 	NULL,
				parentid				uniqueidentifier 	NULL,
				sync_mode				tinyint				NULL,
				allow_push				int					NULL,
				allow_pull				int					NULL,
				allow_anonymous			int					NULL,
				centralized_conflicts	int					NULL,
				status					tinyint				NULL,
				snapshot_ready			tinyint				NULL,
		        enabled_for_internet    bit                	NOT NULL default 0,
				dynamic_filters			bit					NOT NULL default 0,
                -- portable snapshot support
                snapshot_in_defaultfolder   bit             NOT NULL default 1,
                alt_snapshot_folder         nvarchar(255)   NULL,
                -- Pre/post - snapshot commands
                pre_snapshot_script         nvarchar(255)   NULL,
                post_snapshot_script        nvarchar(255)   NULL,
                -- Snapshot compression
                compress_snapshot           bit             NOT NULL default 0,
                -- Post 7.0 Ftp support
                ftp_address                 sysname         NULL,
                ftp_port                    int             NOT NULL default 21,
                ftp_subdirectory            nvarchar(255)   NULL,
                ftp_login                   sysname         NULL default N'anonymous',
                ftp_password                nvarchar(524)   NULL,
                conflict_retention			int				NULL,
                keep_before_values			int				NULL default 0,
				allow_subscription_copy		bit 			NULL default 0, 	
				allow_synctoalternate		bit 			NULL default 0,
				validate_subscriber_info	nvarchar(500)	NULL,
				ad_guidname					sysname			NULL,
                backward_comp_level			int				not NULL default 10, --7.0 RTM
				max_concurrent_merge		int				not NULL default 0,
                max_concurrent_dynamic_snapshots int        not NULL default 0
			)
			if @@ERROR <> 0
				goto Error
			
			CREATE UNIQUE NONCLUSTERED INDEX nc1sysmergepublications 
				ON sysmergepublications(pubid)
			if @@ERROR <> 0
				goto Error

			exec dbo.sp_MS_marksystemobject sysmergepublications
			if @@ERROR <> 0
				goto Error
			-- grant select on sysmergepublications to public
		end

	if not exists (select * from sysobjects where name = 'MSmerge_errorlineage')
		begin		
			raiserror('Creating table MSmerge_errorlineage',0,1)
			
			create table dbo.MSmerge_errorlineage (
			tablenick		int NOT NULL,
			rowguid			uniqueidentifier NOT NULL,
			lineage			varbinary(255)
			)
			exec dbo.sp_MS_marksystemobject MSmerge_errorlineage
			if @@ERROR <> 0
				goto Error
			create unique clustered index uc1errorlineage on MSmerge_errorlineage(tablenick, rowguid)
			if @@ERROR <> 0
				goto Error
			
			grant select on MSmerge_errorlineage to public
		end

	-- this table exists at distribution/db and subscriber databse both
	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 (
			objid					int not NULL primary key,
			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
			if @@ERROR <> 0
				goto Error

			grant select on MSrepl_identity_range to public
		end

	if not exists (select * from sysobjects where name = 'sysmergearticles')
		begin
		
			raiserror('Creating table sysmergearticles',0,1)

			
			create table dbo.sysmergearticles (
				name					sysname				NOT NULL,
		        type        			tinyint       	 	NULL,
				objid					int					NOT NULL, 
				sync_objid				int					NOT NULL,
				view_type 				tinyint				NULL,
				artid					uniqueidentifier	NOT NULL,
				description    			nvarchar(255)    	NULL,
				pre_creation_command	tinyint				NULL,
				pubid					uniqueidentifier 	NOT NULL,
				nickname				int					NOT NULL,
				column_tracking			int					NOT NULL,
				status					tinyint				NULL,
				conflict_table			sysname				NULL,
				creation_script			nvarchar(255)		NULL,
				conflict_script			nvarchar(255)		NULL,
				article_resolver		nvarchar(255)		NULL,
				ins_conflict_proc		sysname				NULL,
				insert_proc				sysname				NULL,
				update_proc				sysname				NULL,
				select_proc				sysname				NULL,
		        schema_option 			binary(8) 			NULL,
				destination_object		sysname				NOT NULL,
				destination_owner		sysname				NULL,
				resolver_clsid			nvarchar(50)		NULL,
				subset_filterclause    	nvarchar(1000)  	NULL,
				missing_col_count		int					NULL,
				missing_cols			varbinary(128)		NULL,
				excluded_cols			varbinary(128)		NULL,
				excluded_col_count		int					not NULL default 0,
				columns        			varbinary(128)   	NULL,
				resolver_info			nvarchar(255)		NULL,
				view_sel_proc			nvarchar(290)		NULL,
				gen_cur					int					NULL,
				vertical_partition		int					not NULL default 0,
				identity_support		int					not NULL default 0,
				before_image_objid		int					NULL,
				before_view_objid		int					NULL,
				verify_resolver_signature 		int			NULL,
				allow_interactive_resolver		bit			NOT NULL default 0,
				fast_multicol_updateproc		bit			NOT NULL default 0,
				check_permissions		int					NOT NULL default 0,
				maxversion_at_cleanup	int					NOT NULL default 1
                -- Note: Please update sysmergeextendedarticlesview whenever
                -- there is a schema change in sysmergearticles
			)
		
			if @@error<>0
				goto Error
			else
				begin
					
					create unique clustered index uc1sysmergearticles
						on sysmergearticles(artid, pubid) 
					if @@ERROR <> 0
						goto Error
					
					if not exists (select * from sysindexes where name = 'nc1sysmergearticles')
					begin
						create nonclustered index nc1sysmergearticles on sysmergearticles(nickname) 
						if @@ERROR <> 0
						return 1
					end
				end
			exec dbo.sp_MS_marksystemobject sysmergearticles
			if @@ERROR <> 0
				goto Error
			grant select on sysmergearticles to public
			
		end

    if not exists (select * from sysobjects where name = 'sysmergeschemaarticles')
        begin
            
            -- The extended merge articles table is for storing 
            -- schema only article information. 
            -- The current supported schema only articles are
            -- stored procs and views.
            raiserror('Creating table sysmergeschemaarticles',0,1)

            create table dbo.sysmergeschemaarticles 
            (   name                    sysname             NOT NULL,
                type                    tinyint             NULL,
                objid                   int                 NOT NULL,
                artid                   uniqueidentifier    NOT NULL,
                description             nvarchar(255)       NULL,
                pre_creation_command    tinyint             NULL,
                pubid                   uniqueidentifier    NOT NULL,
                status                  tinyint             NULL,
                creation_script         nvarchar(255)       NULL,
                schema_option           binary(8)           NULL,
                destination_object      sysname             NOT NULL,
                destination_owner       sysname             NULL
                -- Note: Please update sysmergeextendedarticlesview whenever
                -- there is a schema change in sysmergeschemaarticles
            )
            
            if @@error<>0
                goto Error
            else
                begin
                    create unique clustered index uc1sysmergeschemaarticles
                        on sysmergeschemaarticles(artid, pubid)
                        if @@ERROR <> 0
                            goto Error
                end

            exec dbo.sp_MS_marksystemobject sysmergeschemaarticles
            if @@ERROR <> 0
                goto Error

        end

    if exists (select * from sysobjects where name = 'sysmergeextendedarticlesview')
    begin
        drop view dbo.sysmergeextendedarticlesview
    end
    exec ('create view dbo.sysmergeextendedarticlesview
               as
           select name, type, objid, sync_objid, view_type, artid, description, pre_creation_command, pubid,
				  nickname, column_tracking, status, conflict_table, creation_script, conflict_script, article_resolver,
				  ins_conflict_proc, insert_proc, update_proc, select_proc, schema_option, destination_object,
				  resolver_clsid, subset_filterclause, missing_col_count, missing_cols, columns, resolver_info,
				  view_sel_proc, gen_cur, excluded_cols, excluded_col_count, vertical_partition, identity_support,
			      destination_owner, before_image_objid, before_view_objid, verify_resolver_signature, 
				  allow_interactive_resolver, fast_multicol_updateproc, check_permissions, maxversion_at_cleanup
		   from sysmergearticles
	       union all
           select name, type, objid, NULL, NULL, artid, description, pre_creation_command, pubid, 
				  NULL, NULL, status, NULL, creation_script, NULL, NULL, 
				  NULL, NULL, NULL, NULL, schema_option, destination_object, 
				  NULL, NULL, NULL, NULL, NULL, NULL, 
				  NULL, NULL, NULL, NULL, NULL, NULL, 
				  destination_owner, NULL, NULL, NULL, 
				  0, 0, 0, NULL
				  from sysmergeschemaarticles
                  go')            
    if @@error <> 0
        goto Error

    exec dbo.sp_MS_marksystemobject sysmergeextendedarticlesview

	if not exists (select * from sysobjects	where name = 'sysmergesubscriptions')
		begin
		
			raiserror('Creating table sysmergesubscriptions',0,1)

			
			create table dbo.sysmergesubscriptions
			(
				subid     			uniqueidentifier 	NOT NULL,
				partnerid 			uniqueidentifier 	NOT NULL,
				datasource_type		int					NOT NULL,
				datasource_path		nvarchar(255)		NULL,
				srvid	     		int					NOT NULL,
				db_name		  		sysname		 		NOT NULL
				constraint 			unique_pubsrvdb 	unique nonclustered (pubid, srvid, db_name),
				pubid				uniqueidentifier 	NULL,
				status    			tinyint     		NOT NULL,
				subscriber_type		int					NOT NULL,
				subscription_type	int					NOT NULL,
				priority			real				NOT NULL,
				sync_type 			tinyint				NOT NULL,	-- 1 = automatic 2 = no sync
				description			nvarchar(255)		NULL,
				login_name			sysname				NOT NULL,
				last_validated		datetime			NULL,
				subscriber_server	sysname				NULL,
				use_interactive_resolver		bit		NOT NULL default 0,
				publication			sysname			 	NULL,
				distributor			sysname			 	NULL,
				validation_level	int					not NULL default 0,
				resync_gen			int					not NULL default -1,
				attempted_validate	datetime			NULL,
				last_sync_date		datetime			NULL,
				last_sync_status	int					NULL,
				last_sync_summary	sysname				NULL
			)
		
			if @@error<>0
				goto Error
			else
				begin
					create unique clustered index uc1sysmergesubscriptions 
						on sysmergesubscriptions (subid) 
					if @@ERROR<>0
						goto Error

					create index nc2sysmergesubscriptions on sysmergesubscriptions (srvid, db_name)
					if @@ERROR<>0
						goto Error
				end
			exec dbo.sp_MS_marksystemobject sysmergesubscriptions
			if @@ERROR <> 0
				goto Error
			grant select on sysmergesubscriptions to public
			
		end

	if not exists (select * from sysobjects	where name = 'MSmerge_replinfo')
		begin
			raiserror('Creating table MSmerge_replinfo',0,1)
			
			create table dbo.MSmerge_replinfo
			(
				repid     				uniqueidentifier 	NOT NULL,
				replnickname			int					NOT NULL,
				recgen					int     			NULL,
				recguid					uniqueidentifier 	NULL,
				sentgen					int     			NULL,
				sentguid				uniqueidentifier 	NULL,
				schemaversion			int     			NULL,
				schemaguid				uniqueidentifier 	NULL,
				merge_jobid				binary(16)			NULL,
				snapshot_jobid			binary(16)			NULL
			)
		
			if @@ERROR <> 0
				goto Error
			else
				begin
					create unique clustered index uc1MSmerge_replinfo
						on MSmerge_replinfo (repid)
					if @@ERROR <> 0
						goto Error
				end
			exec dbo.sp_MS_marksystemobject MSmerge_replinfo
			if @@ERROR <> 0
				goto Error
			grant select on MSmerge_replinfo to public
			
		end

	if not exists (select * from sysobjects	where name = 'MSmerge_tombstone')
		begin
			raiserror('Creating table MSmerge_tombstone',0,1)
			
			create table dbo.MSmerge_tombstone
			(
				rowguid			uniqueidentifier rowguidcol NOT NULL,
				tablenick		int				NOT NULL,
				type			tinyint			NOT NULL,
				lineage			varbinary(249)	NOT NULL,
				generation		int				NOT NULL,
				reason			nvarchar(255)	NOT NULL,
			)

			if @@ERROR <> 0
				goto Error
			else
			begin
				create unique clustered index uc1MSmerge_tombstone
					on MSmerge_tombstone (tablenick DESC, rowguid) 
				if @@ERROR <> 0	goto Error
				
				create index nc2MSmerge_tombstone
					on MSmerge_tombstone (generation) 
				if @@ERROR <> 0	goto Error
			end	
	
			 exec dbo.sp_MS_marksystemobject MSmerge_tombstone
			 if @@ERROR <> 0
				goto Error
			grant select on MSmerge_tombstone to public
			
		end

	if not exists (select * from sysobjects	where name = 'MSmerge_contents')
		begin
			raiserror('Creating table MSmerge_contents',0,1)
			
			create table dbo.MSmerge_contents
			(
				tablenick		int					NOT NULL,
				rowguid			uniqueidentifier rowguidcol   NOT NULL,
				generation		int					NOT NULL,
				partchangegen	int					NULL,
				joinchangegen	int					NULL,
				lineage			varbinary(249)		NOT NULL,
				colv1			varbinary(2048)		NULL,
			)
		
			if @@ERROR <> 0
				goto Error
			else
			begin
				create unique clustered index uc1SycContents on MSmerge_contents(tablenick, rowguid)
				if @@ERROR <> 0 goto Error
				create index nc2MSmerge_contents on MSmerge_contents(generation) 
				if @@ERROR <> 0 goto Error
				create index nc3MSmerge_contents on MSmerge_contents(partchangegen) 
				if @@ERROR <> 0 goto Error
				create index nc4MSmerge_contents on MSmerge_contents(rowguid) 
				if @@ERROR <> 0 goto Error
			end
			exec dbo.sp_MS_marksystemobject MSmerge_contents
			if @@ERROR <> 0
				goto Error
			grant select on MSmerge_contents to public
			
		end				

	if not exists (select * from sysobjects	where name = 'MSmerge_genhistory')
		begin
			raiserror('Creating table MSmerge_genhistory',0,1)
			
			create table dbo.MSmerge_genhistory
			(
				guidsrc 		uniqueidentifier 	NOT NULL,
				guidlocal 		uniqueidentifier 	NOT NULL,
				pubid			uniqueidentifier 	NULL,
				generation 		int					NOT NULL,
				art_nick		int					NULL,
				nicknames		varbinary(1000)		NOT NULL,
				coldate 		datetime			NOT NULL
			)
					
			if @@ERROR <> 0
				goto Error

			create clustered index c1MSmerge_genhistory on MSmerge_genhistory(generation) 
			if @@ERROR <> 0
				goto Error

			create unique index unc1MSmerge_genhistory on MSmerge_genhistory(guidsrc, pubid) 
			if @@ERROR <> 0
				goto Error

			create  index nc2MSmerge_genhistory on MSmerge_genhistory(guidlocal) 
			if @@ERROR <> 0
				goto Error

			CREATE INDEX nc4MSmerge_genhistory ON MSmerge_genhistory(coldate)		
			if @@ERROR <> 0
				goto Error

			 exec dbo.sp_MS_marksystemobject MSmerge_genhistory
			 if @@ERROR <> 0
				goto Error
			grant select on MSmerge_genhistory to public
			
		end

	if not exists (select * from sysobjects	where name = 'MSmerge_delete_conflicts')
		begin

			raiserror('Creating table MSmerge_delete_conflicts',0,1)
			
			create table dbo.MSmerge_delete_conflicts
			(
				tablenick			int				NOT NULL,
				rowguid				uniqueidentifier rowguidcol NOT NULL,
				origin_datasource	nvarchar(255)	NULL,
				conflict_type		int NULL,
				reason_code			int NULL,
				reason_text			nvarchar(720) 	NULL,
				pubid				uniqueidentifier NULL,
				create_time			datetime not null default getdate()
			)
		
			if @@ERROR <> 0
				goto Error
			else
				begin
					create clustered index uc1MSmerge_delete_conflicts on MSmerge_delete_conflicts(tablenick, rowguid) 
					if @@ERROR <> 0
						goto Error
				end					

			CREATE UNIQUE NONCLUSTERED INDEX nc1MSmerge_delete_conflicts 
					ON MSmerge_delete_conflicts(tablenick, rowguid, origin_datasource)
			if @@ERROR <> 0
						goto Error

			exec dbo.sp_MS_marksystemobject MSmerge_delete_conflicts
			if @@ERROR <> 0
				goto Error
			grant select on MSmerge_delete_conflicts to public
			
		end

	if not exists (select * from sysobjects	where name = 'sysmergeschemachange')
		begin
		
			raiserror('Creating table sysmergeschemachange',0,1)
			
			create table dbo.sysmergeschemachange
			(
				pubid			uniqueidentifier 	NOT NULL,
				artid			uniqueidentifier 	NULL,
				schemaversion 	int 	   		NOT NULL,
				schemaguid    	uniqueidentifier 	NOT NULL,
				schematype		int		  		NOT NULL,
				schematext		nvarchar(2000) 	NOT NULL
			)
		
			if @@ERROR <> 0
				goto Error
			else
				begin
					create unique clustered index schemachangeversion on sysmergeschemachange(schemaversion, pubid) 
					if @@ERROR <> 0
						goto Error
				end
			exec dbo.sp_MS_marksystemobject sysmergeschemachange
			if @@ERROR <> 0
				goto Error
			grant select on sysmergeschemachange to public
			
		end

	if not exists (select * from sysobjects where name = 'sysmergesubsetfilters')
		begin
			raiserror('Creating table sysmergesubsetfilters',0,1)

			
			create table dbo.sysmergesubsetfilters (
                filtername              sysname		         NOT NULL,
                join_filterid			int					identity NOT NULL,
				pubid					uniqueidentifier	NOT NULL,
				artid					uniqueidentifier	NOT NULL,
				art_nickname			int					NOT NULL,
				join_articlename		sysname NOT NULL,
				join_nickname 			int					NOT NULL,
				join_unique_key			int					NOT NULL,
				expand_proc				sysname					NULL,
				join_filterclause    	nvarchar(1000)  		NULL
			)
			if @@ERROR <> 0
				goto Error

			CREATE UNIQUE NONCLUSTERED INDEX nc1sysmergesubsetfilters 
				ON sysmergesubsetfilters(join_filterid, pubid)
			if @@ERROR <> 0
				goto Error

			exec dbo.sp_MS_marksystemobject sysmergesubsetfilters
			if @@ERROR <> 0
				goto Error
			grant select on sysmergesubsetfilters to public
			
		end

	if @@error <> 0
		goto Error

    if not exists (select * from sysobjects where name = 'MSdynamicsnapshotviews')
        begin
            raiserror('Creating table MSdynamicsnapshotviews',0,1)
            
            create table dbo.MSdynamicsnapshotviews (
                dynamic_snapshot_view_name sysname primary key
            )
            if @@ERROR <> 0
                goto Error
            exec dbo.sp_MS_marksystemobject MSdynamicsnapshotviews
        end

    if not exists (select * from sysobjects where name = 'MSdynamicsnapshotjobs')
        begin
            raiserror('Creating table MSdynamicsnapshotjobs',0,1)
        
            create table dbo.MSdynamicsnapshotjobs (
                id int identity, 
                name sysname not null unique,
                pubid uniqueidentifier not null,
                job_id uniqueidentifier not null,
                dynamic_filter_login sysname null,
                dynamic_filter_hostname sysname null,
                dynamic_snapshot_location nvarchar(255) not null
            )
            if @@ERROR <> 0
                goto Error

            exec dbo.sp_MS_marksystemobject MSdynamicsnapshotjobs

            create unique clustered index uciMSdynamicsnapshotjobs on 
                dbo.MSdynamicsnapshotjobs(job_id, pubid)
            
            if @@ERROR <> 0
                goto Error


        end
    
    if @@error <> 0
        goto Error

    if not exists (select * from sysobjects where name = 'MSmerge_altsyncpartners')
        begin
            raiserror('Creating table MSmerge_altsyncpartners',0,1)
        
            create table dbo.MSmerge_altsyncpartners (
                subid 				uniqueidentifier 	not null,
                alternate_subid 	uniqueidentifier 	not null,
                description			nvarchar(255)		NULL

            )
            if @@ERROR <> 0
                goto Error

            exec dbo.sp_MS_marksystemobject MSmerge_altsyncpartners

            create unique clustered index uciMSmerge_altsyncpartners on 
                dbo.MSmerge_altsyncpartners(subid, alternate_subid)
            
            if @@ERROR <> 0
                goto Error


        end
    

	if exists (select * from master..sysobjects where type='P' and name='sp_MScreatedebuginfrastructure')
	begin
		declare @retval int
		exec @retval= dbo.sp_MScreatedebuginfrastructure
		if @@ERROR <> 0 or @retval <> 0 goto Error
	end
	
	commit transaction
	return (0)
Error:
	if @@trancount > 0
	begin
		ROLLBACK TRANSACTION MScreate_mergesystables
		COMMIT TRANSACTION
	end
	RAISERROR (20008, 16, -1)
	return (1)
go

exec dbo.sp_MS_marksystemobject sp_MScreate_mergesystables 
go

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

create procedure sp_MSdeletecontents
	@tablenick int,
	@rowguid uniqueidentifier
AS
	declare @nick int
	declare @reason nvarchar(255)
	declare @lineage varbinary(255)

	/*
	** permission check
	*/
	declare @retcode	int
	declare @objid		int
	declare @owner		sysname
	declare @artid		uniqueidentifier
	declare @guidstr	nvarchar(32)	
	declare @deltrigger	nvarchar(270)
	declare @oldmaxversion int
	
	select @objid = objid, @artid=artid from sysmergearticles where nickname=@tablenick
	select @owner =user_name(uid) from sysobjects where id=@objid
	
	exec @retcode=sp_MSguidtostr @artid, @guidstr out
		if @retcode<>0 or @@ERROR<>0 return (1)

	set @deltrigger = QUOTENAME(@owner) + '.del_' + @guidstr

	if trigger_nestlevel(object_id(@deltrigger)) = 0
		begin
			raiserror(14126, 16, -1)
			return (1) -- current user does not have 'delete' permission to underlying table
		end

	exec dbo.sp_MSgetreplnick @nickname = @nick out
	if (@@error <> 0) or @nick IS NULL 
        begin
        RAISERROR (14055, 11, -1)
        RETURN(1)
        end                 

	select @oldmaxversion= maxversion_at_cleanup from sysmergearticles where nickname = @tablenick
	select @lineage = lineage from MSmerge_contents (UPDLOCK ROWLOCK index = 1) 
		where tablenick = @tablenick and rowguid = @rowguid
	set @lineage= { fn UPDATELINEAGE(@lineage, @nick, @oldmaxversion+1) }
	select @reason = 'user delete'

	insert into MSmerge_tombstone (rowguid, tablenick, type, lineage, generation, reason)
		values (@rowguid, @tablenick, 1, @lineage, 0, @reason)
	if @@error <> 0
		begin
		raiserror (20041, 16, -1)
		return (1)
		end
	delete from MSmerge_contents where
		tablenick = @tablenick and rowguid = @rowguid
	if @@error <> 0
		begin
		raiserror (20041, 16, -1)
		return (1)
		end 
go
exec dbo.sp_MS_marksystemobject sp_MSdeletecontents
go		
grant exec on dbo.sp_MSdeletecontents to public
go


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

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

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

  SET NOCOUNT ON

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

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

  BEGIN TRANSACTION
  save tran sp_MSadd_repl_job

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

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

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

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

		select @step_name = formatmessage(20528)
		select @comments = formatmessage(20529)

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

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

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

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

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

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

		set @step_id = @step_id + 1

	 	select @step_name = formatmessage(20531)

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

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

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

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

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

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

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

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

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

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

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

  COMMIT TRANSACTION

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

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

  RETURN(0)

UNDO:
	rollback tran sp_MSadd_repl_job
	commit tran
	return(1)

END
go


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

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

create procedure sp_MSgenreplnickname
	@srcguid	uniqueidentifier,	-- Source Guid
	@repnick	int	output	-- nickname
as
	declare @binguid binary(16)
	declare	@nickname int
	set @binguid = convert(binary(16), @srcguid)
	select @nickname = convert(int, convert(binary(4),substring( convert(nchar(8),@binguid), 1, 2)))
	while exists (select replnickname from MSmerge_replinfo where replnickname = @nickname) or
			(@nickname > 0 and @nickname <= 256)
	begin
		select @nickname = @nickname + 1
	end
	select @repnick = @nickname
go

exec dbo.sp_MS_marksystemobject sp_MSgenreplnickname
go
grant execute on dbo.sp_MSgenreplnickname to public

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

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

CREATE PROCEDURE sp_replicationoption (
@optname		sysname,
@value			nvarchar(5),
@security_mode	int = 0,
@login			sysname = 'sa',
@password		sysname = NULL,
@reserved       nvarchar(20) = NULL
)
AS
	DECLARE @optbit bit
	DECLARE @osql_path nvarchar(260)
    DECLARE @osql_cmd1 nvarchar (255)
    DECLARE @osql_cmd_full nvarchar (255)
	DECLARE @osql_for_nt int

	DECLARE @install_path nvarchar (255)

    DECLARE @retcode int
	DECLARE @undo_install nvarchar(20)
	DECLARE @no_scripts nvarchar(10)
    DECLARE @platform_nt binary
    
    SELECT @platform_nt = 0x1

    if	is_srvrolemember('sysadmin') <> 1
        BEGIN
            RAISERROR (15232, 14, -1)
            RETURN (1)
        END
	
	SELECT @no_scripts = 'no_scripts'

	SELECT @undo_install = 'undo_install'

	IF db_name() <> 'master'
	BEGIN
		RAISERROR(5001, 16,-1)
		GOTO FAILURE
	END

	IF LOWER(@optname collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('transactional','merge')
	BEGIN
		RAISERROR(21014, 16, -1)
		GOTO FAILURE
	END
	IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true','false')
	BEGIN
		RAISERROR(14137,16,-1)
		GOTO FAILURE
	END
	
	IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'
		SELECT @optbit = 1
	ELSE
		SELECT @optbit = 0

	/*
	** Check if the option is set as required already
	*/
	IF @reserved <> @undo_install AND EXISTS (SELECT * FROM MSreplication_options
		WHERE	optname  = @optname
		AND		value = @optbit)
	BEGIN
		IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'
			RAISERROR (21015, 10, -1, @optname)		
		ELSE
			RAISERROR (21016, 10, -1, @optname)
		GOTO FAILURE
	END

	/* Install replication */
	IF @optbit = 1
	BEGIN

		IF LOWER(@reserved collate SQL_Latin1_General_CP1_CS_AS) = @no_scripts
			GOTO NO_SCRIPTS

		-- Set the flag for platform
		IF (( platform() & @platform_nt = @platform_nt ))
			SELECT @osql_for_nt = 1
		ELSE
			SELECT @osql_for_nt = 0

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

		/* 
		** Get installation path -- instance specific (INSTALL) directory
		*/
		exec @retcode = master.dbo.sp_MSget_setup_paths
			@sql_path = @install_path output
		IF @@ERROR<> 0 OR @retcode <> 0 or @install_path is NULL or @install_path=''
			BEGIN
				GOTO FAILURE       
			END

		/* 
		** Install replcom.sql and repltran.sql
		*/
		IF @security_mode = 1
		begin
			SELECT @osql_cmd1 = '"' + @osql_path + '\binn\osql" -E ' 
			if serverproperty('instancename') is not null
				SELECT @osql_cmd1 = @osql_cmd1 + ' -S"' + @@SERVERNAME + '" '
		end
		ELSE
			-- cannot specify -S w/ -E for local execution, SID does not map
			SELECT @osql_cmd1 = '"' + @osql_path + '\binn\osql" -U"' + @login + '" -P"' + 
				isnull(@password,'') + '" -S"' + @@SERVERNAME + '" '

		select @osql_cmd1 = @osql_cmd1 + '-l30 -t30 '

		-- Install replcom.sql 
		-- Only apply replcom.sql if it was not applied before.
		-- '-b' option will make osql stop at errors and return error code
		-- We must use this option.
		IF NOT EXISTS (SELECT * FROM MSreplication_options
			WHERE value = 1)
		BEGIN
			-- Initialize the Command
			IF (@osql_for_nt = 1)
				SELECT @osql_cmd_full = '" '
			ELSE
				SELECT @osql_cmd_full = ' '

			SELECT @osql_cmd_full = @osql_cmd_full +
				@osql_cmd1 + 
				' -dmaster' +  ' -b ' +
				' -i' + '"' + @install_path + '\install\replcom.sql"' + 
				' -o' + '"' + @install_path + '\install\replcom.out"' 

			IF (@osql_for_nt = 1)
				SELECT @osql_cmd_full = @osql_cmd_full + ' "'

			EXEC @retcode = master..xp_cmdshell @osql_cmd_full
			IF @@ERROR<> 0 OR @retcode <> 0
			BEGIN
				RAISERROR (14113, 16, -1, @osql_cmd_full, 'replcom.out')
				GOTO UNDO_INSTALL     
			END
		END

		IF LOWER(@optname collate SQL_Latin1_General_CP1_CS_AS) = 'transactional'
		BEGIN
			-- Install repltran.sql
			IF (@osql_for_nt = 1)
				SELECT @osql_cmd_full = '" '
			ELSE
				SELECT @osql_cmd_full = ' '

			SELECT @osql_cmd_full = @osql_cmd_full +
				@osql_cmd1 + 
				' -dmaster' +  ' -b ' +
				' -i' + '"' + @install_path + '\install\repltran.sql"' + 
				' -o' + '"' + @install_path + '\install\repltran.out"' 

			IF (@osql_for_nt = 1)
				SELECT @osql_cmd_full = @osql_cmd_full + ' "'

			EXEC @retcode = master..xp_cmdshell @osql_cmd_full
			IF @@ERROR<> 0 OR @retcode <> 0
			BEGIN
				RAISERROR (14113, 16, -1, @osql_cmd_full, 'repltran.out')
				GOTO UNDO_INSTALL     
			END

		END

		IF LOWER(@optname collate SQL_Latin1_General_CP1_CS_AS) = 'merge'
		BEGIN
			-- Install replmerg.sql 
			IF (@osql_for_nt = 1)
				SELECT @osql_cmd_full = '" '
			ELSE
				SELECT @osql_cmd_full = ' '

			SELECT @osql_cmd_full = @osql_cmd_full +
				@osql_cmd1 + 
				' -dmaster' + ' -b ' +
				' -i' + '"' + @install_path + '\install\replmerg.sql"' + 
				' -o' + '"' + @install_path + '\install\replmerg.out"' 
			
			IF (@osql_for_nt = 1)
				SELECT @osql_cmd_full = @osql_cmd_full + ' "'

			EXEC @retcode = master..xp_cmdshell @osql_cmd_full 
			
			IF  @@ERROR<> 0 OR @retcode <> 0
			BEGIN
				RAISERROR (14113, 16, -1, @osql_cmd_full, 'replmerg.out')
				GOTO UNDO_INSTALL     
			END
		END

NO_SCRIPTS:

		UPDATE MSreplication_options SET value = @optbit
			WHERE optname = @optname
		IF @@ERROR <> 0 
		BEGIN
			GOTO UNDO_INSTALL    
		END

	END
	/* Uninstall replication */
	ELSE
	BEGIN

		/* 
		** Make sure no distributor installed before dropping 
		** replication stored procedures
		*/
		IF EXISTS (SELECT * FROM master..sysservers
              WHERE  srvstatus & 8 <> 0)
		BEGIN
			RAISERROR (21021, 16, -1)
			RETURN(1)
		END

		UPDATE MSreplication_options SET value = @optbit
			WHERE optname = @optname
		IF @@ERROR <> 0 
		BEGIN
			GOTO FAILURE
		END

	END

	RETURN(0)

UNDO_INSTALL:
	/* This is needed to drop the stored procedures that were created. */
	EXEC dbo.sp_replicationoption @optname =  @optname, 
		@value = 'false', @reserved = @undo_install

FAILURE:
	RETURN(1)
GO
exec dbo.sp_MS_marksystemobject sp_replicationoption
go
grant execute on dbo.sp_replicationoption to public
go	

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

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

CREATE PROCEDURE sp_helpreplicationoption (
@optname		sysname = NULL
)
AS
	DECLARE @optbit bit
	DECLARE @retcode int


	IF @optname IS NOT NULL AND LOWER(@optname collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('transactional','merge')
	BEGIN
		RAISERROR(21014, 16, -1)
		GOTO FAILURE
	END
	
	SELECT optname, value, major_version, minor_version, revision 
		FROM master..MSreplication_options
		WHERE
			optname = @optname OR
			@optname = NULL

	RETURN(0)

FAILURE:
	RETURN(1)
GO
exec dbo.sp_MS_marksystemobject sp_helpreplicationoption
go
grant execute on dbo.sp_helpreplicationoption to public
go



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

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

create procedure sp_reinitmergepullsubscription
	@publisher		sysname  = 'all',
	@publisher_db	sysname  = 'all',
	@publication	sysname  = 'all',
	@upload_first	nvarchar(5) = 'FALSE'
AS
	declare @schemaversion			int
	declare @schematype				smallint
	declare @schemaguid				uniqueidentifier
	declare @pubname				sysname
	declare @publisher_name			sysname
	declare @retcode				int
	declare @pubdb					sysname
    declare @pubid					uniqueidentifier
    declare @subid					uniqueidentifier
    declare @artid					uniqueidentifier
	/*
	** Replace 'all' with '%'
	*/
	set nocount on
	if LOWER(@publication) = 'all'
		SELECT @publication = '%'

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

	if LOWER(@publisher_db) = 'all'
		SELECT @publisher_db = '%'
	select @retcode = 0
	/*
	** At subscriber side, we need to qualify the publication with server name and database name
	*/
    IF NOT EXISTS (SELECT * FROM sysmergepublications WHERE name LIKE @publication 
    	and ((@publisher = N'%') or (UPPER(publisher) = UPPER(@publisher))) 
    	and ((@publisher_db = N'%') or (publisher_db = @publisher_db)) )
    	
        BEGIN
        IF @publication = '%'
                RAISERROR (14008, 11, -1)
        ELSE
                RAISERROR (20026, 11, -1, @publication)
        RETURN (1)
        END

	if LOWER(@upload_first collate SQL_Latin1_General_CP1_CS_AS) not in ('true', 'false')
	begin
		raiserror(14148, 16, -1, '@upload_first')
		return (1)
	end
	Declare SYN_CUR CURSOR LOCAL FAST_FORWARD FOR 
		select subs.subid, pubs.name, pubs.publisher, pubs.publisher_db
			from sysmergepublications pubs, sysmergesubscriptions subs
				where pubs.name LIKE  @publication
					AND ((@publisher = '%') OR (UPPER(pubs.publisher) = UPPER(@publisher)))
                                        AND ((@publisher_db = N'%') OR (pubs.publisher_db = @publisher_db))
					AND pubs.pubid=subs.pubid
					AND subs.pubid<>subs.subid
	FOR READ ONLY
	open SYN_CUR
	fetch SYN_CUR into @subid, @pubname, @publisher_name, @pubdb
	while (@@fetch_status<>-1)
	BEGIN	
		
		-- do not update schemaversion if the initial snapshot has never been applied.
		update MSmerge_replinfo set schemaversion=-1, recgen = NULL, recguid=NULL, 
			sentgen=NULL, sentguid = NULL where repid=@subid and schemaversion is not null

		if (@@rowcount <> 0)
		begin
			if LOWER(@upload_first collate SQL_Latin1_General_CP1_CS_AS)='true'
				update sysmergesubscriptions set status=5 where subid=@subid
			else
				exec @retcode=sp_MSCleanupForPullReinit @publication=@pubname,
											  @publisher=@publisher_name,
											  @publisher_db=@pubdb
			if @retcode<>0 or @@ERROR<>0
			begin
				return (1)
			end
		end

		fetch next from SYN_CUR into @subid, @pubname, @publisher_name, @pubdb	
			-- so that it won't be treated as a new susbscription	
	END
	close SYN_CUR
	deallocate SYN_CUR
	-- Forget that publisher ever sent us any generations.  They must be resent.
	-- Publication cleanup will remove the genhistory rows.

	if @publication = '%'
		-- get them all
		update MSmerge_replinfo set recgen = NULL, recguid=NULL, sentgen=NULL, sentguid = NULL
	else
		begin
		select @pubid = pubid from sysmergepublications where name = @publication
		update MSmerge_replinfo set recgen = NULL, recguid=NULL, sentgen=NULL, sentguid = NULL
			where repid in (select subid from sysmergesubscriptions where pubid = @pubid)
		end
GO

exec dbo.sp_MS_marksystemobject sp_reinitmergepullsubscription
go	
grant execute on dbo.sp_reinitmergepullsubscription to public
go


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

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

CREATE PROCEDURE sp_MSmergesubscribedb(
	@value sysname
	) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */
	declare @retcode 				int
	

    /*
    ** Security Check
    */

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


	/*
	** Initialization
	*/


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

	IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'
		BEGIN		
			execute @retcode = dbo.sp_MScreate_mergesystables
			if @@ERROR <> 0 or @retcode <> 0
				begin
					return (1)
				end
		END
	
	-- We assume we will do nothing about disabling a subscriber
	
GO

exec dbo.sp_MS_marksystemobject sp_MSmergesubscribedb
go

grant execute on dbo.sp_MSmergesubscribedb to public
go 


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

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

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

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

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

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

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

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


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

CREATE PROCEDURE sp_addpullsubscription (
	@publisher sysname,
	@publisher_db sysname,
	@publication sysname,    /* publication name */
	@independent_agent nvarchar(5) = 'true',  /* true or false */
	@subscription_type nvarchar(9) = 'anonymous',     /* subscription_type, pull or anonymous */
	@description nvarchar(100) = NULL,
	-- SyncTran
	@update_mode          nvarchar(15) = 'read only', -- 'sync tran', 'queued tran', 'failover'
	@immediate_sync		bit = 1
) AS
BEGIN

    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    DECLARE @retcode int
    DECLARE @subscription_type_id int   /* 1 = pull, 2 = anonymous */
	DECLARE @independent_agent_id bit
	-- SyncTran
	DECLARE @update_mode_id     tinyint 

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

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

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

    /*
    ** Initializations.
    */

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

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

    EXECUTE @retcode = dbo.sp_validname @publisher

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

    /*
    ** Parameter Check: @publisher_db
    */

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

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

    EXECUTE @retcode = dbo.sp_validname @publisher_db

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

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

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

    EXECUTE @retcode = dbo.sp_validname @publication

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

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

    IF LOWER(@independent_agent collate SQL_Latin1_General_CP1_CS_AS) = 'true' SELECT @independent_agent_id = 1
    ELSE SELECT @independent_agent_id = 0
    /*
    ** Parameter Check:  @subscription_type
    ** The @status value can be:
    **
    **      type_id            type
    **      ======            ========
	**			 0			  push
    **           1            pull
    **           2            anonymous
	**
	**	Note: @subscription_type = push is only used by distribution agents
    */
	
    IF @subscription_type IS NULL OR LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('push', 'pull','anonymous')
    BEGIN
        RAISERROR (20016, 16, -1)
        RETURN (1)
    END


    IF LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) = 'pull'    
		SELECT @subscription_type_id = 1
    ELSE IF LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) = 'anonymous'  
		SELECT @subscription_type_id = 2
	ELSE
		SELECT @subscription_type_id = 0

	IF @independent_agent_id = 0 AND @subscription_type_id = 2
	BEGIN
		RAISERROR (21026, 16, -1)
		RETURN (1)
	END

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

	-- Anonymous subscriptions should not be allowed to 
	-- subscribe with 'synctran option'	
    IF LOWER(@update_mode collate SQL_Latin1_General_CP1_CS_AS) in ('sync tran', 'queued tran', 'failover') and
        @subscription_type_id = 2 
    begin					  
       	RAISERROR (21057, 16, -1)
		RETURN (1)
	end

	select @update_mode_id = 
	case
		when LOWER(@update_mode collate SQL_Latin1_General_CP1_CS_AS) = 'sync tran' then 1
		when LOWER(@update_mode collate SQL_Latin1_General_CP1_CS_AS) = 'queued tran' then 4
		when LOWER(@update_mode collate SQL_Latin1_General_CP1_CS_AS) = 'failover' then 5
		else 0
    end


	begin tran
    save TRAN addpullsubscription
	
    /* 
	** Check to see if MSreplication_subscriptions and MSsubscription_properties 
	** tables exists.
	** If not, create it.
	*/

	exec @retcode = dbo.sp_MScreate_sub_tables
		@tran_sub_table = 1,
		@property_table = 1
	IF @@ERROR <> 0 or @retcode <> 0
		goto UNDO
	
	/*
    ** Check to make sure that the subscription does not already exist
    */
	declare @sub_type int
	select @sub_type = subscription_type from MSreplication_subscriptions 
                WHERE UPPER(publisher) = UPPER(@publisher) AND
                      publisher_db  = @publisher_db AND
					  publication = @publication

	-- It is not allowed to subscribe to the same publication twice even with
	-- differnet subscription type. 
	-- Raise special error for push
	if @sub_type is not null
		if @sub_type = 0
		begin
			raiserror (20594, 16, -1)
			GOTO UNDO			
		end
		else
		begin
			RAISERROR (14058, 16, -1)
			GOTO UNDO
		end
  
    /*
    ** Add the subscription
    */

    INSERT MSreplication_subscriptions  (publisher,
                                    publisher_db,
                                    publication,
									independent_agent,
                                    subscription_type,
                                    distribution_agent,
									description,
									time,
									transaction_timestamp,
									-- SyncTran
									update_mode,
									immediate_sync)
       VALUES (@publisher,
                   @publisher_db,
                   @publication,
				   @independent_agent_id,    
                   @subscription_type_id,
                   NULL,
				   @description,
				   getdate(),
				   0,
				   -- SyncTran
				   @update_mode_id,
				   @immediate_sync
				   )
    IF @@ERROR <> 0
    BEGIN
       RAISERROR (14057, 16, -1)
       GOTO UNDO
    END

    COMMIT TRAN

	RETURN (0)

UNDO:
    IF @@TRANCOUNT > 0
	begin
        ROLLBACK TRAN addpullsubscription
        COMMIT TRAN   
	end
	return 1
END
go
 
grant execute on dbo.sp_addpullsubscription to public
go


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

CREATE PROCEDURE sp_MSupdatesharedagentproperties (
    @publisher sysname,
    @publisher_db sysname,
    @publication sysname,
    @property    sysname,
    @strvalue    nvarchar(256) = NULL,
    @intvalue    int = NULL,
    @subscription_type int     
    )
AS


    -- Note publisher/publisher_db/publication uniquely identifies 
    -- a subscription in MSreplication_subscriptions 
    CREATE TABLE #subscriptions
    (
        publication sysname collate database_default
    )

    -- Compute the list of affected subscriptions
    INSERT INTO #subscriptions SELECT publication FROM
    MSreplication_subscriptions WHERE UPPER(publisher) = UPPER(@publisher)
    AND publisher_db = @publisher_db AND publication <> @publication
    AND independent_agent = 0 AND subscription_type = @subscription_type

    IF (SELECT COUNT(*) FROM #subscriptions) > 0
    BEGIN 

        IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'distributor'
        BEGIN
            UPDATE MSsubscription_properties SET distributor = @strvalue 
             WHERE UPPER(publisher) = UPPER(@publisher)  
               AND publisher_db = @publisher_db
               AND publication in (SELECT publication FROM #subscriptions)   
        END
        ELSE IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'distributor_security_mode'
        BEGIN
            UPDATE MSsubscription_properties SET distributor_security_mode = @intvalue 
             WHERE UPPER(publisher) = UPPER(@publisher)  
               AND publisher_db = @publisher_db
               AND publication in (SELECT publication FROM #subscriptions)   
        END
        ELSE IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'distributor_login'
        BEGIN
            UPDATE MSsubscription_properties SET distributor_login = @strvalue 
             WHERE UPPER(publisher) = UPPER(@publisher)  
               AND publisher_db = @publisher_db
               AND publication in (SELECT publication FROM #subscriptions)   
        END
        ELSE IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'distributor_password'
        BEGIN
            UPDATE MSsubscription_properties SET distributor_password = @strvalue 
             WHERE UPPER(publisher) = UPPER(@publisher)  
               AND publisher_db = @publisher_db
               AND publication in (SELECT publication FROM #subscriptions)   
        END
        ELSE IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'distributor_password'
        BEGIN
            UPDATE MSsubscription_properties SET distributor_password = @strvalue 
             WHERE UPPER(publisher) = UPPER(@publisher)  
               AND publisher_db = @publisher_db
               AND publication in (SELECT publication FROM #subscriptions)   
        END
        ELSE IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'ftp_address'
        BEGIN
            UPDATE MSsubscription_properties SET ftp_address = @strvalue 
             WHERE UPPER(publisher) = UPPER(@publisher)  
               AND publisher_db = @publisher_db
               AND publication in (SELECT publication FROM #subscriptions)   
        END
        ELSE IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'ftp_port'
        BEGIN
            UPDATE MSsubscription_properties SET ftp_port = @intvalue 
             WHERE UPPER(publisher) = UPPER(@publisher)  
               AND publisher_db = @publisher_db
               AND publication in (SELECT publication FROM #subscriptions)   
        END
        ELSE IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'ftp_login'
        BEGIN
            UPDATE MSsubscription_properties SET ftp_login = @strvalue 
             WHERE UPPER(publisher) = UPPER(@publisher)  
               AND publisher_db = @publisher_db
               AND publication in (SELECT publication FROM #subscriptions)   
        END
        ELSE IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'ftp_password'
        BEGIN
            UPDATE MSsubscription_properties SET ftp_password = @strvalue 
             WHERE UPPER(publisher) = UPPER(@publisher)  
               AND publisher_db = @publisher_db
               AND publication in (SELECT publication FROM #subscriptions)   
        END
        ELSE IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'alt_snapshot_folder'
        BEGIN
            UPDATE MSsubscription_properties SET alt_snapshot_folder = @strvalue 
             WHERE UPPER(publisher) = UPPER(@publisher)  
               AND publisher_db = @publisher_db
               AND publication in (SELECT publication FROM #subscriptions)   
        END
        ELSE IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'working_directory'
        BEGIN
            UPDATE MSsubscription_properties SET working_directory = @strvalue 
             WHERE UPPER(publisher) = UPPER(@publisher)  
               AND publisher_db = @publisher_db
               AND publication in (SELECT publication FROM #subscriptions)   
        END
        ELSE IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'use_ftp'
        BEGIN
            UPDATE MSsubscription_properties SET use_ftp = @intvalue 
             WHERE UPPER(publisher) = UPPER(@publisher)  
               AND publisher_db = @publisher_db
               AND publication in (SELECT publication FROM #subscriptions)   
        END
        ELSE IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'offload_agent'
        BEGIN
            UPDATE MSsubscription_properties SET offload_agent = @intvalue
             WHERE UPPER(publisher) = UPPER(@publisher)
               AND publisher_db = @publisher_db
               AND publication in (SELECT publication FROM #subscriptions)
        END
        ELSE IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'offload_server'
        BEGIN
            UPDATE MSsubscription_properties SET offload_server = @strvalue
             WHERE UPPER(publisher) = UPPER(@publisher)
               AND publisher_db = @publisher_db
               AND publication in (SELECT publication FROM #subscriptions)
        END
    END

    DROP TABLE #subscriptions

    IF @@ERROR <> 0
        RETURN 1
    ELSE
        RETURN 0
GO 

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

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

    SET NOCOUNT ON

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

    /*
    ** Initialization
    */

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

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

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

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

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

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

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


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

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

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

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


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


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

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

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

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

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

    RETURN(0)

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

    return(1)   
GO

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

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

    SET NOCOUNT ON

    /*
    ** Declarations.
    */

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

    /*
    ** Security Check
    */

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

    /*
    ** Initializations.
    */

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

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

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

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

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

    EXECUTE @retcode = dbo.sp_validname @publisher

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

    /*
    ** Parameter Check: @publisher_db
    */

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

    EXECUTE @retcode = dbo.sp_validname @publisher_db

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

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

    EXECUTE @retcode = dbo.sp_validname @publication

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

    declare @dts_package_location_id int

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

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

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

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

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

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

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

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

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

    BEGIN TRAN

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

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

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

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

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

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

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

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

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

            SELECT @command = @command + @optional_command_line

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

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

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

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

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

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


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

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

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


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

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

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

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

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

    COMMIT TRAN
    RETURN(0)
GO

grant execute on dbo.sp_addpullsubscription_agent to public
go


--------------------------------------------------------------------------------
--. sp_change_subscription_properties 
--------------------------------------------------------------------------------
if exists (select * from sysobjects
		where type = 'P' and name = 'sp_change_subscription_properties')
	drop procedure sp_change_subscription_properties
go
raiserror('Creating procedure sp_change_subscription_properties', 0,1)
go
CREATE PROCEDURE sp_change_subscription_properties
        @publisher sysname, 
		@publisher_db sysname, 
		@publication sysname, 
		@property sysname,
		@value nvarchar(1000),
        @publication_type int = NULL
    AS
    SET NOCOUNT ON

	DECLARE @command nvarchar(2000)
	DECLARE @column_to_update nvarchar(64)
	DECLARE @value_string nvarchar(255)
    DECLARE @independent_agent bit
	DECLARE @retcode int
    DECLARE @subscription_type int
    DECLARE @intvalue int
    DECLARE @value_bit bit
    DECLARE @offload_agent bit
    DECLARE @offload_server sysname 
    DECLARE @dbname sysname
    DECLARE @local_publication sysname
    DECLARE @agentid binary(16)
    DECLARE @local_publisher sysname
    DECLARE @cursor_opened bit 
    DECLARE @cursor_allocated bit
    DECLARE @agenttype nvarchar(20)
    DECLARE @commandline nvarchar(3200)

    SELECT @cursor_opened = 0
    SELECT @cursor_allocated = 0
    SELECT @retcode = 0    
	/*
    ** Security Check
    */

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

	IF NOT EXISTS (select * from sysobjects where name = 'MSsubscription_properties' and type = 'U')
	begin
		raiserror(14027, 16, -1, 'The subscription properties table ''MSsubscription_properties''')
		return (1)
	end
	
	if (@publisher is null)
	begin
		raiserror(14043, 16, -1, '@publisher')
		return (1)
	end

	if(@publisher_db is null)
	begin
		raiserror(14043, 16, -1, '@publisher_db')
		return (1)
	end
	
	if (@publication is null) or (@publication = '')
	begin
		select @publication = '%'
	end


    -- Expand '%' into individual subscriptions using a cursor
    IF @publication = N'%'
    BEGIN

        BEGIN TRANSACTION subscription_properties_exp
        DECLARE hsubprop CURSOR LOCAL FAST_FORWARD FOR
            SELECT publication 
              FROM MSreplication_subscriptions
             WHERE UPPER(publisher) = UPPER(@publisher)
               AND publisher_db = @publisher_db
               -- NOTE:  publication like '%' is implicit
        IF @@ERROR <> 0
            GOTO ExpFailure
        SELECT @cursor_allocated = 1
    
        OPEN hsubprop
        IF @@ERROR <> 0
            GOTO ExpFailure    
        SELECT @cursor_opened = 1

        FETCH hsubprop INTO @local_publication
        WHILE (@@FETCH_STATUS <> 0)
        BEGIN
                            
            EXEC @retcode = sp_change_subscription_properties
                @publisher = @publisher,
                @publisher_db = @publisher_db,
                @publication = @local_publication,
                @property = @property,
                @value = @value,
                @publication_type = @publication_type
            IF @retcode <> 0 OR @@ERROR <> 0
                GOTO ExpFailure
            FETCH hsubprop INTO @local_publication
        END

        CLOSE hsubprop
        DEALLOCATE hsubprop
        COMMIT TRANSACTION subscription_properties_exp
        IF @@ERROR <> 0
            GOTO ExpFailure

        RETURN (0)
ExpFailure:

        IF @cursor_opened = 1
            CLOSE hsubprop
        IF @cursor_allocated = 1
            DEALLOCATE hsubprop
        ROLLBACK TRANSACTION subscription_properties_exp        
        RETURN (1)
    END    

    -- Check for existence of the specified pull subscription
    SELECT @agentid = NULL
    SELECT @local_publisher = NULL
    SELECT @dbname = DB_NAME()

    IF @publication_type IN (0,1) OR @publication_type IS NULL
    BEGIN
        IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('MSreplication_subscriptions'))
        BEGIN
            SELECT @local_publisher = publisher, 
                   @agentid = agent_id
              FROM MSreplication_subscriptions
             WHERE UPPER(publisher) = UPPER(@publisher)
               AND publisher_db = @publisher_db
               AND publication = @publication 
        END

    END

    IF @publication_type = 2 OR (@publication_type IS NULL AND
        @local_publisher IS NULL)
    BEGIN
        IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('sysmergepublications'))
        BEGIN

            SELECT @local_publisher = mp.publisher,
                   @agentid = mr.merge_jobid
              FROM sysmergepublications mp
            INNER JOIN sysmergesubscriptions ms
                ON mp.pubid = ms.pubid 
               AND UPPER(mp.publisher) = UPPER(@publisher) 
               AND mp.publisher_db = @publisher_db
               AND mp.name = @publication 
               AND ms.db_name = @dbname
               AND UPPER(ms.subscriber_server) = UPPER(@@SERVERNAME)
            INNER JOIN MSmerge_replinfo mr
                ON ms.subid = mr.repid
        END
    END 

    IF @local_publisher IS NULL
    BEGIN
        RAISERROR(21226, 16, -1, @dbname)
        RETURN (1)
    END

    SELECT @offload_agent = NULL
    SELECT @offload_server = NULL
    SELECT @offload_agent = offload_agent, 
           @offload_server = offload_server,
           @publication_type = publication_type             
      FROM MSsubscription_properties 
     WHERE UPPER(publisher) = UPPER(@publisher)
       AND publisher_db = @publisher_db
       AND publication = @publication
    
    -- If the subscription does not have a corresponding entry in MSsubscription_properties, 
    -- the subscription is probably created by the ActiveX control. In this case there isn't much
    -- we can do about it so we just return 0.
    IF @offload_agent IS NULL
    BEGIN
        RETURN (0)
    END      
    
	IF (@property IS NULL)
	begin
		raiserror(14043, 16, -1, '@property')
		return (1)
	end
	ELSE IF (lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'publisher_login')
		select @column_to_update = 'publisher_login'
	ELSE IF (lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'publisher_password')
	begin
		select @column_to_update = 'publisher_password'
		EXEC @retcode = master.dbo.xp_repl_encrypt @value OUTPUT
		IF @@error <> 0 OR @retcode <> 0
			return 1
	end
	ELSE IF (lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'publisher_security_mode')
		select @column_to_update = 'publisher_security_mode'
	ELSE IF (lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'distributor')
		select @column_to_update = 'distributor'
	ELSE IF (lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'distributor_login')
		select @column_to_update = 'distributor_login'
	ELSE IF (lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'distributor_password')
	begin
		select @column_to_update = 'distributor_password'
		EXEC @retcode = master.dbo.xp_repl_encrypt @value OUTPUT
		IF @@error <> 0 OR @retcode <> 0
			return 1
	end
	ELSE IF (lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'distributor_security_mode')
		select @column_to_update = 'distributor_security_mode'
	ELSE IF (lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'ftp_address')
		select @column_to_update = 'ftp_address'
	ELSE IF (lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'ftp_port')
		select @column_to_update = 'ftp_port'
	ELSE IF (lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'ftp_login')
		select @column_to_update = 'ftp_login'
	ELSE IF (lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'ftp_password')
		select @column_to_update = 'ftp_password'
    ELSE IF (lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'alt_snapshot_folder')
    BEGIN
        select @column_to_update = 'alt_snapshot_folder'
    END
    ELSE IF (lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'working_directory')
    BEGIN
        select @column_to_update = 'working_directory'
    END
    ELSE IF (lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'use_ftp')
        select @column_to_update = 'use_ftp'
    ELSE IF (lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'dts_package_name')
        select @column_to_update = 'dts_package_name'
    ELSE IF (lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'dts_package_password')
	begin
        select @column_to_update = 'dts_package_password'
		EXEC @retcode = master.dbo.xp_repl_encrypt @value OUTPUT
		IF @@error <> 0 OR @retcode <> 0
			return 1
	end
    ELSE IF (lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'dts_package_location')
        select @column_to_update = 'dts_package_location'
    ELSE IF (lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'offload_agent')
    BEGIN
        select @column_to_update = 'offload_agent'
    END
    ELSE IF (lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'offload_server')
    BEGIN
        select @column_to_update = 'offload_server'
        exec @retcode = sp_MSreplcheckoffloadserver @value
        if @retcode <> 0 or @@error <> 0
                return(1)
    END
    ELSE IF (lower(@property collate SQL_Latin1_General_CP1_CS_AS) = N'dynamic_snapshot_location') and @publication_type = 2
    BEGIN
        select @column_to_update = N'dynamic_snapshot_location'
    END
	ELSE
	BEGIN
		raiserror (3217, 16, -1, '@property')
		return(1)
	END

    IF @column_to_update in ('use_ftp', 'offload_agent')
    BEGIN
        IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = N'true'
        BEGIN
            SELECT @value_bit = 1
        END
        ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = N'false'
        BEGIN
            SELECT @value_bit = 0
        END
        ELSE
        BEGIN 
            RAISERROR(14137, 16, -1)
        END
    END

	IF (lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'distributor_security_mode') OR (lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'publisher_security_mode') 
	BEGIN
		IF NOT ( @value = 0 or @value = 1 or (@value = 2 and lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'publisher_security_mode' )) 
		BEGIN
			raiserror(3217, 16, -1, '@value')
			return(1)
		END
        select @value_string = convert(nvarchar(1), @value)
	END
	ELSE IF (lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'dts_package_location')
	BEGIN
		IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = N'distributor' 
			select @value_string = '0'
		ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = N'subscriber' 
			select @value_string = '1'
		ELSE 
		begin
			raiserror(20587, 16, -1, 'dts_package_location', 'sp_change_subscription_properties')
			return(1)
		end

	END
	ELSE
    BEGIN
        IF @value IS NULL
        BEGIN
            select @value_string = 'null'
        END
        ELSE
        BEGIN
		    select @value_string = 'N' + quotename(rtrim(@value),'''')
        END
	END    

    -- Ignore ftp_address, ftp_port, ftp_login, ftp_password on post 7.0 servers
    IF (@column_to_update IN (N'ftp_address', N'ftp_port', N'ftp_login', N'ftp_password'))
    BEGIN
        RETURN (0)
    END

    -- If the 'offload_agent' property is 1, 'offload_server' property must not be
    -- null or empty. Make sure that this is the case
    IF (@column_to_update = N'offload_agent' AND 
        @value_bit = 1 AND 
        (@offload_server IS NULL OR @offload_server = N''))
    BEGIN
        RAISERROR(21215, 16, -1)
        RETURN(1)
    END

    -- If we are enabling agent offload for this pull subscription, 
    -- we should make sure that the offload server is different from
    -- the Subscriber's server name
    IF (@column_to_update = N'offload_agent' AND
        @value_bit = 1 AND UPPER(@offload_server) = UPPER(@@SERVERNAME)) OR
       (@column_to_update = N'offload_server' AND
        @offload_agent = 1 AND 
        UPPER(@value) = UPPER(@@SERVERNAME))
    BEGIN
        RAISERROR(21227, 16, -1)
        RETURN (1)
    END
    
BEGIN TRANSACTION subscription_properties	
	IF (lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'use_ftp')
    begin
		update MSsubscription_properties set use_ftp = @value_bit where 
			UPPER(publisher) = UPPER(@publisher) and 
			publisher_db = @publisher_db and
			publication = @publication 
        IF @@error <> 0 OR @retcode <> 0
            GOTO Failure
    end
    else if (lower(@property collate SQL_Latin1_General_CP1_CS_AS) = 'offload_agent')
    begin
        update MSsubscription_properties set offload_agent = @value_bit where
            UPPER(publisher) = UPPER(@publisher) and
            publisher_db = @publisher_db and
            publication = @publication
        IF @@error <> 0 OR @retcode <> 0
            GOTO Failure

        -- Call helper procedure to add or remove the '-Offload' parameter 
        -- from the agent's command line in msdb. By now, we should 
        -- have the publication type and the agent jodid.

        -- Don't do anything if the offload_agent property hasn't been changed
        IF @offload_agent <> @value_bit
        BEGIN
            IF @publication_type IN (0,1)    
            BEGIN
                SELECT @agenttype = N'distribution'
            END
            ELSE
            BEGIN
                SELECT @agenttype = N'merge'
            END    

            -- Add or remove the Offload parameter based on the new value
            -- of the offload_agent property 
            IF @value_bit = 0
            BEGIN
                EXEC @retcode = sp_MSremoveoffloadparameter
                        @job_id = @agentid,
                        @agenttype = @agenttype

                IF @@ERROR <> 0 OR @retcode <> 0
                    GOTO Failure 
            END
            ELSE
            BEGIN
                EXEC @retcode = sp_MSaddoffloadparameter 
                        @job_id = @agentid,
                        @offloadserver = @offload_server,
                        @agenttype = @agenttype

                IF @@ERROR <> 0 OR @retcode <> 0
                    GOTO Failure 
            END
        END
    end
    else if lower(@property collate SQL_Latin1_General_CP1_CS_AS) = N'dynamic_snapshot_location'
    begin
        select @value = rtrim(ltrim(@value))

        update MSsubscription_properties 
           set dynamic_snapshot_location = @value
         where upper(publisher) = upper(@publisher) 
           and publisher_db = @publisher_db 
           and publication = @publication
        if @@error <> 0 or @retcode <> 0
            goto Failure

        -- Call helper functions to add/update or remove the -DynamicSnapshotLocationParameter
        if @value is null or @value = N''
        begin
            update msdb.dbo.sysjobsteps 
               set command = fn_removeparameterwithargument(command, N'DynamicSnapshotLocation') collate database_default
             where job_id = @agentid
               and lower(subsystem collate SQL_Latin1_General_CP1_CS_AS) = N'merge'
        end
        else
        begin

            select @commandline = command 
              from msdb.dbo.sysjobsteps
             where job_id = @agentid
               and lower(subsystem collate SQL_Latin1_General_CP1_CS_AS) = N'merge'

            select @commandline = fn_updateparameterwithargument(@commandline, N'DynamicSnapshotLocation', fn_replquotename(@value)) collate database_default

            -- Need to remove existing alternate snapshot folder parameters
            -- and file transfer type parameters

            select @commandline = fn_removeparameterwithargument(@commandline, N'FileTransferType') collate database_default
            
            select @commandline = fn_removeparameterwithargument(@commandline, N'AltSnapshotFolder') collate database_default

            update msdb.dbo.sysjobsteps
               set command = @commandline
             where job_id = @agentid
               and lower(subsystem collate SQL_Latin1_General_CP1_CS_AS) = N'merge' 

        end
    end
    else    
	begin
		-- Password is encrypted. Must prefix every string with N' 
		-- otherwise, the chars will be convert to '???'
		select @command = N'update MSsubscription_properties set ' + @column_to_update + '= ' + @value_string
				+ ' where UPPER(publisher) = UPPER(' + quotename(@publisher,'''') 
				+ ') and publisher_db = ' + quotename(@publisher_db, '''')
				+ ' and publication = ' + quotename(@publication,'''')
		EXEC (@command)
        IF @@error <> 0 OR @retcode <> 0
            GOTO Failure
	end


    -- If the offload_server property is chnaged, we have to modify the agent's
    -- command line if the agent has already been enabled for remote activation 		
    IF @column_to_update = N'offload_server' AND @offload_agent = 1
    BEGIN
        IF @publication_type IN (0,1)
        BEGIN
            SELECT @agenttype = N'distribution'
        END
        ELSE
        BEGIN
            SELECT @agenttype = N'merge'
        END

        EXEC @retcode = sp_MSaddoffloadparameter
            @job_id = @agentid,
            @offloadserver = @value,
            @agenttype = @agenttype        
        IF @retcode <> 0 OR @@ERROR <> 0
            GOTO Failure
    END

    IF (@publication_type = 0 or @publication_type = 1) AND @publication <> N'%' AND
        @column_to_update IN ('distributor',
                              'distributor_login',
                              'distributor_password',
                              'distributor_security_mode',
                              'ftp_address',
                              'ftp_port',
                              'ftp_login',
                              'ftp_password',
                              'alt_snapshot_folder',
                              'working_dir',
                              'use_ftp',
                              'offload_agent',
                              'offload_server')
    BEGIN
        SELECT @subscription_type = subscription_type
          FROM MSreplication_subscriptions
         WHERE UPPER(publisher) = UPPER(@publisher)
           AND publisher_db = @publisher_db
           AND publication = @publication
        IF @@error <> 0 OR @retcode <> 0
            GOTO Failure

        -- Update the property for all shared subscriptions
        IF @column_to_update IN ('distributor_security_mode', 
                                 'ftp_port')
        BEGIN
            SELECT @intvalue = CONVERT(int, @value)
            EXEC @retcode = sp_MSupdatesharedagentproperties 
                    @publisher = @publisher,
                    @publisher_db = @publisher_db,
                    @publication = @publication,
                    @property = @column_to_update,
                    @intvalue = @intvalue,
                    @subscription_type = @subscription_type
        END
        ELSE IF @column_to_update IN ('use_ftp', 'offload_agent')
        BEGIN
            EXEC @retcode = sp_MSupdatesharedagentproperties 
                    @publisher = @publisher,
                    @publisher_db = @publisher_db,
                    @publication = @publication,
                    @property = @column_to_update,
                    @intvalue = @value_bit,
                    @subscription_type = @subscription_type
        END
        ELSE 
        BEGIN
            EXEC @retcode = sp_MSupdatesharedagentproperties 
                    @publisher = @publisher,
                    @publisher_db = @publisher_db,
                    @publication = @publication,
                    @property = @column_to_update,
                    @strvalue = @value,
                    @subscription_type = @subscription_type
        END  
    
        IF @@error <> 0 OR @retcode <> 0
            GOTO Failure
    END

    -- Ftp and alternate snapshot folder are mutually 
    -- exclusive options but instead of raising an error
    -- when both of them are non-null, nullify the other 
    -- property when either one of them is set to non-null
    -- value since properties can only be set one at a time

    -- 'dynamic_snapshot_location' should also be added to the
    -- list of mutually exclusive properties


    IF (@column_to_update = 'ftp_address') AND (@value <> N''
       AND @value IS NOT NULL)
    BEGIN
        EXEC @retcode = sp_change_subscription_properties 
                @publisher = @publisher,
                @publisher_db = @publisher_db,
                @publication = @publication,
                @property = 'alt_snapshot_folder',
                @value = NULL

        IF @@error <> 0 OR @retcode <> 0
            GOTO Failure
    END

    IF (@column_to_update = 'use_ftp') AND (@value_bit = 1)
    BEGIN
        EXEC @retcode = sp_change_subscription_properties 
                @publisher = @publisher,
                @publisher_db = @publisher_db,
                @publication = @publication,
                @property = 'alt_snapshot_folder',
                @value = NULL

        IF @@error <> 0 OR @retcode <> 0
            GOTO Failure

        IF @publication_type = 2
        BEGIN
            EXEC @retcode = sp_change_subscription_properties
                    @publisher = @publisher,
                    @publisher_db = @publisher_db,
                    @publication = @publication,
                    @property = 'dynamic_snapshot_location',
                    @value = NULL
            IF @@error <> 0 OR @retcode <> 0
                GOTO Failure
        END
    END

    IF (@column_to_update = 'alt_snapshot_folder') AND (@value <> N''
       AND @value IS NOT NULL)
    BEGIN
        EXEC @retcode = sp_change_subscription_properties 
                @publisher = @publisher,
                @publisher_db = @publisher_db,
                @publication = @publication,
                @property = 'ftp_address',
                @value = NULL

        IF @@error <> 0 OR @retcode <> 0
            GOTO Failure

        EXEC @retcode = sp_change_subscription_properties 
                @publisher = @publisher,
                @publisher_db = @publisher_db,
                @publication = @publication,
                @property = 'use_ftp',
                @value = 'false'

        IF @@error <> 0 OR @retcode <> 0
            GOTO Failure

        IF @publication_type = 2
        BEGIN
            EXEC @retcode = sp_change_subscription_properties
                    @publisher = @publisher,
                    @publisher_db = @publisher_db,
                    @publication = @publication,
                    @property = 'dynamic_snapshot_location',
                    @value = NULL
            IF @@error <> 0 OR @retcode <> 0
                GOTO Failure
        END
    END


    IF (@column_to_update = 'dynamic_snapshot_location') AND (@value <> N''
        AND @value IS NOT NULL)
    BEGIN

        EXEC @retcode = sp_change_subscription_properties
                @publisher = @publisher,
                @publisher_db = @publisher_db,
                @publication = @publication,
                @property = 'ftp_address',
                @value = NULL
            
        IF @@error <> 0 OR @retcode <> 0
            GOTO Failure

        EXEC @retcode = sp_change_subscription_properties
                @publisher = @publisher,
                @publisher_db = @publisher_db,
                @publication = @publication,
                @property = 'use_ftp',
                @value = 'false'
            
        IF @@error <> 0 OR @retcode <> 0
            GOTO Failure

        EXEC @retcode = sp_change_subscription_properties
                @publisher = @publisher,
                @publisher_db = @publisher_db,
                @publication = @publication,
                @property = 'alt_snapshot_folder',
                @value = NULL
            
        IF @@error <> 0 OR @retcode <> 0
            GOTO Failure

    END


    -- Turn off the offload agent bit if the 
    -- the new value of offload server happens to be null
    -- or empty
    IF (@column_to_update = 'offload_server' AND 
        (@value IS NULL OR @value = N'') AND
        @offload_agent = 1)
    BEGIN
        EXEC @retcode = sp_change_subscription_properties
                @publisher = @publisher,
                @publisher_db = @publisher_db,
                @publication = @publication,
                @property = 'offload_agent',
                @value = 'false'
        IF @@error <> 0 OR @retcode <> 0
            GOTO Failure
    END

    IF @@error <> 0 OR @retcode <> 0
        GOTO Failure

    COMMIT TRANSACTION subscription_properties
    RETURN (0)
            
Failure:        
    ROLLBACK TRANSACTION subscription_properties
    RETURN (1)
GO
EXEC dbo.sp_MS_marksystemobject sp_change_subscription_properties
GO
grant execute on dbo.sp_change_subscription_properties to public
go

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


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

CREATE PROCEDURE sp_helppullsubscription (
    @publisher sysname = '%',
    @publisher_db sysname = '%',
    @publication sysname = '%',
	@show_push nvarchar(5) = 'false'
    ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */


    DECLARE @command nvarchar(255)
    DECLARE @name nvarchar(255)
	DECLARE @retcode int
	DECLARE @show_push_bit bit
	DECLARE @push int
	declare @subscriber			sysname
	declare @subscriber_db		sysname
	declare @publisher_local	sysname
	declare @publisher_db_local	sysname
	declare @publication_local	sysname
	declare @subscription_name 	nvarchar(1000)
	declare @regkey				nvarchar(1000)
	declare @syncmgr_keyexist				int
	declare @helpsubscriptioncursor_open	int


	SELECT @push = 0

	-- For attach
	if exists (select * from sysobjects where name = 'MSrepl_restore_stage')
		-- The database is attached from a subscription copy file without using
		-- sp_attachsubscription. Return nothing
		return 0

    /*
    ** Security Check
    */

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

    /*
    ** Initializations.
    */
	set @subscriber = @@SERVERNAME	 
	set @subscriber_db = DB_NAME()

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

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

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

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

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


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

	IF LOWER(@show_push collate SQL_Latin1_General_CP1_CS_AS) = 'false'				
		SELECT @show_push_bit = 0	
    IF LOWER(@show_push collate SQL_Latin1_General_CP1_CS_AS) = 'true'	
		SELECT @show_push_bit = 1


	IF NOT EXISTS (SELECT * FROM sysobjects WHERE 
		type = 'U' AND
		name = 'MSreplication_subscriptions')
		RETURN (0)

	IF NOT EXISTS (SELECT * FROM sysobjects WHERE 
		type = 'U' AND
		name = 'MSsubscription_properties')
		RETURN (0)

	IF NOT EXISTS (SELECT * FROM sysobjects WHERE 
		type = 'U' AND
		name = 'MSsubscription_agents')
		RETURN (0)

				
    /*
    ** Get the result
	**
	** Note: have to return meta data
    */
	    SELECT  'publisher'				= rs.publisher,
            'publisher database'	= rs.publisher_db,
            'publication'			= rs.publication,
			'independent_agent'		= rs.independent_agent,
            'subscription type'     = rs.subscription_type,
            'distribution agent'	= distribution_agent,
			'publication description'   = description,
			'last updating time'		= convert(nvarchar(12), time, 112) + 
                                          substring(convert(nvarchar(24), time, 121), 11,13),
			'subscription_name'	  	= rs.publisher + ':' + rs.publisher_db + ':' + rs.publication,
			'last transaction timestamp' = transaction_timestamp,
			-- SyncTran
			-- For Queued case, we will return 2/3 for values 4/5 so that
			-- SQL-DMO does not have to deal with the overloaded values of update_mode
			'update_mode'         = case 	when rs.update_mode = 4 then 2
											when rs.update_mode = 5 then 3
											else rs.update_mode
									end,
			'distribution agent job_id' = agent_id,
			'enabled for syncmgr' = enabled_for_syncmgr,
    		'subscription guid' = subscription_guid,
			'subid ' = subid,
			'immediate_sync' = immediate_sync,
	        'publisher_login' = publisher_login,
            'publisher_password' = publisher_password,
            'publisher_security_mode' = publisher_security_mode,
            'distributor' = distributor,
            'distributor_login' = distributor_login,
            'distributor_password' = distributor_password,
            'distributor_security_mode' = distributor_security_mode,
            'ftp_address' = ftp_address,
            'ftp_port' = ftp_port,
            'ftp_login' = ftp_login,
            'ftp_password' = ftp_password,
            'alt_snapshot_folder' = alt_snapshot_folder,
            'working_directory' = working_directory,
            'use_ftp' = use_ftp,
            'publication_type' = publication_type,
			'dts_package_name' = dts_package_name,
			'dts_package_location' = dts_package_location,
            'offload_agent' = offload_agent,
            'offload_server' = offload_server,
			a.last_sync_status, 
			a.last_sync_summary,
			'last_sync_time' = convert(nvarchar(12), a.last_sync_time, 112) + 
				substring(convert(nvarchar(24), a.last_sync_time, 121), 11,13)
       from MSreplication_subscriptions rs
       left outer join MSsubscription_properties sp 
                    on (UPPER(rs.publisher) = UPPER(sp.publisher) and rs.publisher_db = sp.publisher_db and rs.publication = sp.publication) 
       left outer join MSsubscription_agents a
                    on (UPPER(rs.publisher) = UPPER(a.publisher) and 
						rs.publisher_db = a.publisher_db and 
						((rs.publication = a.publication and 
						rs.independent_agent = 1 and
						a.publication <> N'ALL') or
						(a.publication = N'ALL' and rs.independent_agent = 0)) and
						rs.subscription_type = a.subscription_type)
                where ((@publisher = N'%') OR (UPPER(rs.publisher) = UPPER(@publisher))) AND
                      ((@publisher_db = N'%') OR (rs.publisher_db = @publisher_db)) AND
                      ((@publication = N'ALL' AND rs.independent_agent = 0) OR rs.publication LIKE @publication) AND
					  (rs.subscription_type <> @push OR
					   @show_push_bit = 1)
        ORDER BY rs.publisher, rs.publisher_db, rs.publication  
GO

grant execute on dbo.sp_helppullsubscription to public
go

--------------------------------------------------------------------------------
--. sp_addmergepullsubscription 
--------------------------------------------------------------------------------
if exists (select * from sysobjects
	where type = 'P '
			and name = 'sp_addmergepullsubscription')
	drop procedure sp_addmergepullsubscription
go
raiserror('Creating procedure sp_addmergepullsubscription', 0,1)
GO
 
CREATE PROCEDURE sp_addmergepullsubscription (
	@publication 				sysname,	      			/* Publication name */
	@publisher					sysname = @@SERVERNAME,  	/* Publisher server */
	@publisher_db				sysname = NULL,  			/* Publication database */
	@subscriber_type 			nvarchar(15) = 'local',			/* Subscriber type */ 
	@subscription_priority 		real 		= NULL,				/* Subscription priority */
    @sync_type 					nvarchar(15) = 'automatic', /* subscription sync type */
	@description 				nvarchar(255) = NULL			  
 	) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */
    declare @retcode				int
    declare	@subscriber_db			sysname
    declare @subnickname			int
    declare @subscriber_srvid		int 
    declare @publisher_srvid		int 
    declare @priority 				real
    declare @subid	 				uniqueidentifier
    declare @subscriber_typeid 		smallint
    declare @subscription_type	 	smallint
    declare @command 				nvarchar(255)
    declare @inactive				tinyint 
	declare @global 				tinyint		/* subscriber type is global */
	declare @push 					tinyint		/* subscription type is push */
	 
    declare @sync_typeid 			tinyint
	declare @nosync 				tinyint		
	declare @automatic				tinyint			 
	declare @pubid					uniqueidentifier			 
	declare @partnerid				uniqueidentifier			 
    declare @parentid               uniqueidentifier

	/*
    ** Initializations.
    */

    SET @nosync 			= 2       /* Const: synchronization type 'none' */
    SET @automatic 			= 1       /* Const: synchronization type 'automatic' */
	SET @inactive 			= 0
    SET @global 			= 1
	SET @push 				= 0   


	set @pubid 				= newid()
	set @partnerid			= @pubid
	
	set @parentid			= '00000000-0000-0000-0000-000000000000'
	 
	/* 
	** Check if replication components are installed on this server
	*/
	exec @retcode = dbo.sp_MS_replication_installed
	if (@retcode <> 1)
	begin
		return (1)
	end

    /*
    ** Security Check.
    */

	EXEC @retcode = dbo.sp_MSreplcheck_subscribe
	if @@ERROR <> 0 or @retcode <> 0
		return(1)

	/*
	**	Check to see if merge system tables exist. Create them unless they already
	**  exist.
	*/
	IF not exists (select name from sysobjects where name='sysmergesubscriptions')
		BEGIN
			execute @retcode = dbo.sp_MScreate_mergesystables
				if @@ERROR <> 0 or @retcode <> 0
					begin
						return (1)
					end
		END	

	if UPPER(@publisher) = UPPER(@@SERVERNAME) and @publisher_db = db_name()
		begin
			raiserror(21126, 16, -1)
			return (1)
		end

	if exists (select pubid from sysmergepublications where UPPER(publisher) = UPPER(@@SERVERNAME) and publisher_db=db_name()) and @subscriber_type in ('local', 'anonymous')
		begin
            declare @dbname sysname
            select @dbname = DB_NAME()
    	    raiserror(21258, 16, -1, @dbname)
			return (1)
		end
	/* 
	** When adding a pull subscription, if a push subscription for that publication already exists, 
	** we will raise error and fail
	*/
	IF EXISTS (select name from sysmergepublications where name = @publication and UPPER(publisher)=UPPER(@publisher) and publisher_db=@publisher_db)
	BEGIN
		select @pubid=pubid from sysmergepublications 
			where name = @publication and UPPER(publisher)=UPPER(@publisher) and publisher_db=@publisher_db
		IF EXISTS (select subid from sysmergesubscriptions where pubid=@pubid and subid<>@pubid and subscription_type=0)
			begin
				RAISERROR (21317, 16, -1, @publication)
				return (1)
			end
		ELSE
			begin
				IF EXISTS (select status from sysmergesubscriptions where pubid=@pubid and status =	2)
					begin
						select @subid = subid from sysmergesubscriptions where pubid=@pubid and subid<>pubid
						delete sysmergesubscriptions where pubid=@pubid
						delete MSmerge_replinfo where repid = @subid
					end
				ELSE 
					begin
						RAISERROR (14058, 16, -1)
						return (1)
					end
			end
	END

	set @partnerid = @pubid
     
  	/*
    ** Assign parameter values appropriately for the local server database
	*/
	set @subscriber_db = DB_NAME()
	select @subscriber_srvid = 0

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

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

    EXECUTE @retcode = dbo.sp_validname @publisher
    IF @@ERROR <> 0 OR @retcode <> 0
       RETURN (1)
	/*
	** Validate that the publisher is a valid server
	*/
	select @publisher_srvid = srvid from master..sysservers where UPPER(srvname) = UPPER(@publisher) collate database_default
    IF @publisher_srvid IS NULL
		BEGIN
		    EXECUTE @retcode = dbo.sp_addserver @publisher
	        IF @@error <> 0 OR @retcode <> 0
    			BEGIN
		    		RAISERROR (14010, 16, -1)
			       	RETURN (1)
	            END
	        ELSE
	        	select @publisher_srvid = srvid from master..sysservers where UPPER(srvname) = UPPER(@publisher) collate database_default
		END


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

    IF LOWER(@publisher_db) = 'all'
    BEGIN
        RAISERROR (14136, 16, -1)
        RETURN (1)
    END
	
	/*
	** Check to see if the publication name is already used in the subscription 
	** database - This is the case where we are resubscribing to the same publication.
	** Execute dbo.sp_MSpublicationcleanup to cleanup all all the defunct rows
	
	** if exists (select * from sysmergepublications where name = @publication)
	** begin
	**	exec @retcode = dbo.sp_MSpublicationcleanup 
	**    IF @@ERROR <> 0 OR @retcode <> 0
	**    	BEGIN
	**			RAISERROR (20025, 16, -1, @publication)
	**	    	RETURN (1)
	**		END				    	
	** end
	*/

    /*
    ** Parameter Check: @subscriber_type.
    ** Set subscriber_typeid based on the @subscriber_type specified.
    **
    **   subscriber_type     subscriber_type
    **   =================    ===============
    **             1     		global
    **             2     		local
    **             3     		anonymous
    **             NO support for republisher for B 3
    */
    if LOWER(@subscriber_type collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('local', 'global', 'anonymous')
        BEGIN
		  	RAISERROR (20023, 16, -1)
			RETURN (1)
        END

	set @subscription_type = 1 /* pull by default */
    if LOWER(@subscriber_type collate SQL_Latin1_General_CP1_CS_AS) IN ('global')
        set @subscriber_typeid = 1
    else if LOWER(@subscriber_type collate SQL_Latin1_General_CP1_CS_AS) IN ('local')
        set @subscriber_typeid = 2
	else if LOWER(@subscriber_type collate SQL_Latin1_General_CP1_CS_AS) IN ('anonymous')       
		begin
			/* For anonymous subscribers set subscription type appropriately */
	        set @subscriber_typeid = 3
	        set @subscription_type = 2
		end	        

	/* 
	** Assign priority appropriately - choose 0.99 times the minimum priority
	** of the global replicas.
	*/
	if (@subscription_priority > 100.0 or @subscription_priority < 0.0)
		set @subscription_priority = NULL		
	if (@subscription_priority IS NULL)
		begin
			select @priority = 0.99 * min(priority) from sysmergesubscriptions where subscriber_type  = 1 /* global/loopback */
			if (@priority IS NOT NULL)
				select @subscription_priority = @priority
			if (@subscription_priority IS NULL) 
				select @subscription_priority = 0.0
		end
	/*
	** For local and anonymous subscriptions the priority is 0.0
	*/
    if LOWER(@subscriber_type collate SQL_Latin1_General_CP1_CS_AS) IN ('local', 'anonymous')
		select @subscription_priority = 0.0

	/*
 	** If the subscription already exists, don't add it.
	
 	** if EXISTS (select db_name, srvid
 	**		FROM sysmergesubscriptions
    ** 		WHERE db_name = @subscriber_db
    ** 		AND srvid = @subscriber_srvid                	       
    **    	AND pubid = @pubid)
	**	BEGIN
	**		RAISERROR (14058, 16, -1)
	**		RETURN (1)
 	**	END
	*/

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


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


   IF LOWER(@sync_type collate SQL_Latin1_General_CP1_CS_AS) = 'automatic'
   BEGIN
		SET @sync_typeid = @automatic
   END
   ELSE
   BEGIN
        SET @sync_typeid = @nosync
   END


	/*
	** UNDONE: Validate that the publisher is of type 'republisher'
	*/

	begin tran
    save TRAN addmergepullsubscription
		
		/* Generate a guid for the Subscriber ID */
		set @subid = newid()

		/* Look for existing nickname from any other subscription */
		exec dbo.sp_MSgetreplnick NULL, NULL , NULL,  @subnickname out
		if (@@error <> 0) 
	        begin
            goto FAILURE
	        end                 


		/* Generate a new replica nickname from the @subid */
		if (@subnickname is null)
			EXECUTE dbo.sp_MSgenreplnickname @subid, @subnickname output

	
		/* 
		** Check to see if MSsubscription_properties table exists.
		** If not, create it.
		*/
		exec @retcode = dbo.sp_MScreate_sub_tables
			@tran_sub_table = 0,
			@property_table = 1,
			@sqlqueue_table = 0

		IF @@ERROR <> 0 or @retcode <> 0
			goto FAILURE

		/*
		** The subscription doesn't exist, so let's add it to sysmergesubscriptions 
		*/
		INSERT sysmergesubscriptions (subid, partnerid, datasource_type, srvid, db_name, 
				pubid, status, subscriber_type, subscription_type, priority, sync_type, 
				description, login_name, subscriber_server, publication)
					VALUES (@subid,
							@partnerid,
							0,
		    				@subscriber_srvid,
		    				@subscriber_db,
		    				@pubid,
		    				@inactive,
		    				@subscriber_typeid,
							@subscription_type, 	/* for a pull/anon subscription */
		    				@subscription_priority,
		    				@sync_typeid,
		    				@description,
		    				suser_sname(suser_sid()),
		    				@@servername,
		    				@publication)			
		if @@ERROR <> 0
			BEGIN
				GOTO FAILURE
			END

		/* Add a self-subscribed subscription to represent the publication */
			
		insert sysmergepublications(publisher, publisher_db, pubid, name, parentid) 
			values(@publisher, @publisher_db, @pubid, @publication, @parentid)
		if @@ERROR <> 0
			begin
				goto FAILURE
			end
		insert sysmergesubscriptions(subid, partnerid, datasource_type, srvid, db_name, 
				pubid, subscriber_type, subscription_type, status, priority, sync_type, description, login_name, subscriber_server, publication, distributor)
			values (@pubid, @pubid, 0, @publisher_srvid, @publisher_db, 
				@pubid, @global, @push, @inactive, 100.0, @sync_typeid, @description, suser_sname(suser_sid()), @publisher, @publication, @publisher)  
						
		if @@ERROR <> 0
			begin
				goto FAILURE
			end
					
		/*
		**  Add row for subscription in MSmerge_replinfo.
		*/
		insert MSmerge_replinfo(repid, replnickname)
				values (@subid, @subnickname)
		if @@ERROR <> 0
			BEGIN
				GOTO FAILURE
			END

    COMMIT TRAN
	return (0)

FAILURE:
	RAISERROR (14057, 16, -1)
	if @@trancount > 0
	begin
		ROLLBACK TRANSACTION addmergepullsubscription
		COMMIT TRANSACTION
	end
	RETURN (1)

go
exec dbo.sp_MS_marksystemobject sp_addmergepullsubscription 
go

grant execute on dbo.sp_addmergepullsubscription to public
go
 
--------------------------------------------------------------------------------
--. sp_changemergepullsubscription 
--------------------------------------------------------------------------------
if exists (select * from sysobjects
	where type = 'P '
			and name = 'sp_changemergepullsubscription')
	drop procedure sp_changemergepullsubscription
go
raiserror('Creating procedure sp_changemergepullsubscription', 0,1)
GO

CREATE PROCEDURE sp_changemergepullsubscription (
	@publication 		sysname = '%',	/* Publication name */
	@publisher			sysname = '%',  /* Publisher server */
	@publisher_db		sysname = '%', 	/* Publication database */
    @property 			sysname = NULL, /* The property to change */
    @value 				nvarchar(255) = NULL	/* The new property value */
    ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */
    declare @subscriber_srvid	 	int
    declare @publisher_srvid	 	int
	declare @retcode 				int
	declare	@pubid 					uniqueidentifier
	declare	@subid 					uniqueidentifier
	declare	@partnerid 				uniqueidentifier
    declare @sync_typeid 			tinyint
    declare @nosync 				tinyint
    declare @automatic 				tinyint
     
    declare @artid					uniqueidentifier
	declare @subscriber				sysname
	declare @subscriber_db			sysname
    declare @schematype				int
	declare @schemaversion			int
	declare @schemaguid				uniqueidentifier
    declare @db_name				sysname
    declare @subscriber_type		int
    declare @schematext				nvarchar(2000)
	declare @use_interactive_bit	bit
	declare @use_interactive_int	int
	declare @enabled_for_syncmgr	int
	declare @regkey					nvarchar(1000)
	declare @snapshot_ready			int
    /*
    ** Initializations.
    */
    SET @subscriber 	= @@SERVERNAME
    SET @subscriber_db	= DB_NAME()
    SET @nosync 		= 2     /* Const: synchronization type 'none' */
    SET @automatic 		= 1     /* Const: synchronization type 'automatic' */

	select @subscriber_srvid = srvid from master..sysservers where UPPER(srvname) = UPPER(@subscriber) collate database_default
    IF @subscriber_srvid IS NULL
		BEGIN
		   RAISERROR (14010, 16, -1)
		   RETURN (1)     
		END

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


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

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

    IF @property IS NULL
        BEGIN
            CREATE TABLE #tab1 (properties sysname collate database_default not null)
            INSERT INTO #tab1 VALUES ('sync_type')
            INSERT INTO #tab1 VALUES ('priority')
            INSERT INTO #tab1 VALUES ('description') 
            INSERT INTO #tab1 VALUES ('use_interactive_resolver') 			
            -- Properties delegated to sp_change_subscription_properties
            INSERT INTO #tab1 VALUES ('publisher_login')
            INSERT INTO #tab1 VALUES ('publisher_password')
            INSERT INTO #tab1 VALUES ('publisher_security_mode')
            INSERT INTO #tab1 VALUES ('distributor')
            INSERT INTO #tab1 VALUES ('distributor_login')
            INSERT INTO #tab1 VALUES ('distributor_password')
            INSERT INTO #tab1 VALUES ('distributor_security_mode')
            INSERT INTO #tab1 VALUES ('ftp_address')
            INSERT INTO #tab1 VALUES ('ftp_port')
            INSERT INTO #tab1 VALUES ('ftp_login')
            INSERT INTO #tab1 VALUES ('ftp_password')
            INSERT INTO #tab1 VALUES ('alt_snapshot_folder')
            INSERT INTO #tab1 VALUES ('working_directory')
            INSERT INTO #tab1 VALUES ('use_ftp')
            INSERT INTO #tab1 VALUES ('offload_agent')
            INSERT INTO #tab1 VALUES ('offload_server')
            INSERT INTO #tab1 VALUES ('dynamic_snapshot_location')
            select * FROM #tab1
            RETURN (0)
        END

    /*
    ** Parameter Check:  @publisher.
    ** Check to make sure we have a valid publisher.
    ** Should make sure that @publisher is non-null before using it to check whether @publication is valid
    */
    IF @publisher IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@publisher')
            RETURN (1)
        END

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

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

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

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

    /*
    ** Check to see if you have a local / global subscription on this publication
    */
    set @subid = NULL
	select @subid = subs1.subid, 
    @pubid = pubs.pubid, /* identified from publication name */
	@subscriber_type=subs1.subscriber_type,
    	@partnerid = subs2.subid from
		sysmergesubscriptions 	subs1,
	  	sysmergesubscriptions 	subs2,
		sysmergepublications 	pubs
		where subs1.srvid = @subscriber_srvid
			and subs1.db_name = @subscriber_db
			and subs2.srvid = @publisher_srvid
			and subs2.db_name = @publisher_db
			and subs1.pubid = subs2.subid
			and subs2.pubid = pubs.pubid
			and pubs.name = @publication
			and UPPER(pubs.publisher)=UPPER(@publisher)
			and pubs.publisher_db = @publisher_db

	if @subid IS NULL 
     	begin
            RAISERROR (14050, 11, -1)
            RETURN(1)
		end     			

    /*
    ** Parameter Check:  @property.
    ** Check to make sure that @property is a valid property in
    ** a merge pull subscription.
    */

	
    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) NOT IN 
                               ('sync_type', 
                                'priority', 
                                'description',
                                'publisher_login',
                                'publisher_password',
                                'publisher_security_mode',
                                'distributor',
                                'distributor_login',
                                'distributor_password',
                                'distributor_security_mode',
                                'ftp_address',
                                'ftp_port',
                                'ftp_login',
                                'ftp_password',
                                'alt_snapshot_folder',
                                'working_directory',
                                'use_ftp',
                                'use_interactive_resolver',
                                'offload_agent',
                                'offload_server',
                                'dynamic_snapshot_location')
        BEGIN
            RAISERROR (21348, 16, -1, @property)
            RETURN (1)
        END

    /*
    ** Change the property.
    */
    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'sync_type'
        BEGIN

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

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

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

	        IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'automatic'
		        SET @sync_typeid = @automatic
	        ELSE
		        SET @sync_typeid = @nosync

            /*
            ** Update the subscription with the new sync_type.
            */

            UPDATE sysmergesubscriptions
               	SET sync_type = @sync_typeid
             	WHERE subid = @subid
            IF @@ERROR <> 0
                BEGIN
                    RAISERROR (14053, 16, -1)
                    RETURN (1)
                END

        END
        
	IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'description'
        BEGIN
		UPDATE sysmergesubscriptions
               	SET description = @value
             	WHERE subid = @subid
            IF @@ERROR <> 0
                BEGIN
                    RAISERROR (14053, 16, -1) 
              
                    RETURN (1)
                END

        END

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

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

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

            /* Determine the bit value. */

	        IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'
		        SET @use_interactive_bit = 1
	        ELSE
		        SET @use_interactive_bit = 0
	
			/* Update the subscription with the new 'use_interactive' value. */

            UPDATE sysmergesubscriptions
               	SET use_interactive_resolver = @use_interactive_bit
             	WHERE subid = @subid
            IF @@ERROR <> 0
                BEGIN
                    RAISERROR (14053, 16, -1)
                    RETURN (1)
                END
			/* If the subscription is enable for Sync Manager, then update the reg value */
			exec sp_MSsubscription_enabled_for_syncmgr
				@publisher, @publisher_db, @publication, @subscriber, @subscriber_db, 
				@enabled_for_syncmgr OUT, @regkey OUT
            IF @@ERROR <> 0
                BEGIN
                    RAISERROR (14053, 16, -1)
                    RETURN (1)
                END
			if @enabled_for_syncmgr = 1
				begin
			        select @use_interactive_int = convert(int, @use_interactive_bit)
					EXECUTE @retcode = master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
			                               @regkey,
			                               'UseInteractiveResolver',
			                               'REG_DWORD',
			                                @use_interactive_int
					if @retcode <> 0 OR @@ERROR <> 0
		                BEGIN
		                    RAISERROR (14053, 16, -1)
		                    RETURN (1)
		                END
				end

        END
        

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

		select @db_name = db_name from sysmergesubscriptions
        	where (pubid=@pubid) and (subid=@pubid)
        IF @db_name <> db_name()
	        BEGIN
    	    	RAISERROR (20047, 16, -1)
        	    RETURN (1)
	        END

	    /* Only the original publisher can change priority of a global subscriptions */

    	IF @subscriber_type<>1  
    	    BEGIN
        	    RAISERROR (20044, 16, -1)  /* Local subscriber does not have priority*/
	            RETURN (1)
    	    END

		 
		BEGIN TRANSACTION
		save tran sp_changemergepullsubscription
        	exec dbo.sp_MSchange_priority @subid,  @value
            select @snapshot_ready=snapshot_ready from sysmergepublications where pubid = @pubid
	    	/* Insert the sp_MSchange_priority schema change only if the publication's snapshot is ready */
			if (@snapshot_ready > 0)
			begin
		     	select @schemaversion = schemaversion from sysmergeschemachange
				if (@schemaversion is NULL)
				set @schemaversion = 1
				else
					select @schemaversion = 1 + max(schemaversion) from sysmergeschemachange
				set @schemaguid = newid()
				set @artid = newid()
				set @schematype = 8 /* change priority */
				select @schematext = 'exec dbo.sp_MSchange_priority '+ '''' + convert(nchar(36),@subid) + '''' + ',' + '''' + @value + '''' 
			 	exec dbo.sp_MSinsertschemachange @pubid, @artid, @schemaversion, @schemaguid, @schematype, @schematext
                if @@ERROR <> 0 goto Rollback_tran
			end		 	
		COMMIT TRANSACTION

	END
	
    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) IN 
                           ('publisher_login',
                            'publisher_password',
                            'publisher_security_mode',
                            'distributor',
                            'distributor_login',
                            'distributor_password',
                            'distributor_security_mode',
                            'ftp_address',
                            'ftp_port',
                            'ftp_login',
                            'ftp_password',
                            'alt_snapshot_folder',
                            'working_directory',
                            'use_ftp',
                            'offload_agent',
                            'offload_server',
                            'dynamic_snapshot_location')
    BEGIN
        EXEC @retcode = sp_change_subscription_properties @publisher = @publisher,
                                                          @publisher_db = @publisher_db,
                                                          @publication = @publication,
                                                          @property = @property,
                                                          @value = @value,
                                                          @publication_type = 2
        IF @retcode <> 0
            RETURN 1
    END


    /*
    ** Return succeed.
    */

    RAISERROR (14054, 10, -1)
    RETURN (0)
Rollback_tran:
	rollback tran sp_changemergepullsubscription
	commit tran
	return(1)
go

exec dbo.sp_MS_marksystemobject sp_changemergepullsubscription 
go

grant execute on dbo.sp_changemergepullsubscription to public
go

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

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

CREATE PROCEDURE sp_helpmergepullsubscription(
	@publication 		sysname = '%',		/* Publication name */
	@publisher			sysname = '%',  	/* Publisher server */
	@publisher_db		sysname = '%',  	/* Publication database */
	@subscription_type	nvarchar(10) = 'pull'	/* Show only pull subscriptions */
	)AS
	
    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    declare @retcode 			int
    declare @srvid	 			int
	declare	@pubid 				uniqueidentifier
	declare	@subid 				uniqueidentifier
	declare	@partnerid 			uniqueidentifier
    declare @cursor_open		int
	declare @subscriber			sysname
	declare @subscriber_db		sysname
    declare @subscription_set 	nvarchar(10)
	declare @publisher_local	sysname
	declare @publisher_db_local	sysname
	declare @publication_local	sysname
	declare @subscription_name 	nvarchar(1000)
	declare @regkey				nvarchar(1000)
	declare @syncmgr_keyexist				int
	declare @helpsubscriptioncursor_open	int
	

    /*
    ** Initializations.
    */
	set @cursor_open 			= 0					

	select @publisher_db = RTRIM(@publisher_db)
	select @publication = RTRIM(@publication)
	
	-- For attach
	if exists (select * from sysobjects where name = 'MSrepl_restore_stage')
		-- The database is attached from a subscription copy file without using
		-- sp_attachsubscription. Return nothing
		return 0

	/*
	**	Calling sp_help* is all right whether current database is enabled for pub/sub or not
	*/

	IF not exists (select * from sysobjects where name='sysmergesubscriptions')
		RETURN (0)

	/* Security check */
	EXEC @retcode = dbo.sp_MSreplcheck_subscribe
	if @@ERROR <> 0 or @retcode <> 0
		return(1)

	set @subscriber = @@SERVERNAME	 
	set @subscriber_db = DB_NAME()
 
    /*
    ** Parameter Check: @publisher
    ** Check to make sure that the publisher is defined
    */
    IF @publisher <> '%' AND @publisher IS NOT NULL
    BEGIN
		EXECUTE @retcode = dbo.sp_validname @publisher

    	IF @@ERROR <> 0 OR @retcode <> 0
       		RETURN (1)
    END
 
    /*
    ** Parameter Check:  @publication.
    ** If the publication name is specified, check to make sure that it
    ** conforms to the rules for identifiers and that the publication
    ** actually exists.  Disallow NULL.
    */
    if @publication IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@publication')
            RETURN (1)
        END

	/*
    ** Parameter Check: @subscription_type.
    ** Set subscription_typeid based on the @subscription_type specified.
    **
    **   subscription_type    subscription_type
    **   =================    ===============
    **             0     		push
    **             1     		pull
    **			  0,1			both
    */
    if LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('push', 'pull', 'both')
        BEGIN
		  	RAISERROR (14128, 16, -1)
			RETURN (1)
        END
    IF LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) = 'both'
        set @subscription_set = '(0, 1)'
    else IF LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) = 'push'
        set @subscription_set = '(0)'
    else 
        set @subscription_set = '(1,2)'   -- including pull subscription and pull/anonymous subscription

	IF NOT EXISTS (SELECT * FROM sysobjects WHERE 
		type = 'U' AND
		name = 'MSsubscription_properties')
		RETURN (0)

    /*
    ** Get subscriptions
    */
	
	create table #helpmergepullsubscription
				(
					publication				sysname		collate database_default not null,
					publisher				sysname		collate database_default not null,
					publisher_db			sysname		collate database_default not null,
					subscriber				sysname		collate database_default not null,
					subscriber_db		 	sysname		collate database_default not null,
					status    				int     	NOT NULL,
					subscriber_type 		int		    NOT NULL,
					subscription_type 		int		    NOT NULL,
					priority				float(8)	NOT NULL,
					sync_type				tinyint		NOT NULL,
					description				nvarchar(255) 	collate database_default null,
					merge_jobid				binary(16)		NULL,
					enabled_for_syncmgr		int				NULL,
					last_updated	        nvarchar(26) 	collate database_default null,
                    use_interactive_resolver 	int			NULL,
                    subid					uniqueidentifier	not NULL,
                    last_sync_status		int				NULL,
                    last_sync_summary		sysname			collate database_default null
				)

    IF @publisher IS NULL and @publisher_db IS NULL
	    BEGIN
	    	select @subid = subid from sysmergesubscriptions where subid = partnerid
	    	set @partnerid = @subid
			-- show the loopback subscription
		    insert into #helpmergepullsubscription 
				select pubs.name, servers2.srvname, subs2.db_name, servers1.srvname, subs1.db_name, 
					subs1.status, subs1.subscriber_type, subs1.subscription_type, subs1.priority, 
					subs1.sync_type, subs1.description, replinfo.merge_jobid, 0, 
					convert(nvarchar(12), subs1.last_sync_date, 112) + substring(convert(nvarchar(24), subs1.last_sync_date, 121), 11,13), 
					subs1.use_interactive_resolver, @subid, subs1.last_sync_status, subs1.last_sync_summary
		    	FROM  	sysmergesubscriptions 	subs1,
		    		  	sysmergesubscriptions 	subs2,
		    		  	MSmerge_replinfo	 	replinfo,
	        			master.dbo.sysservers 	servers1,
	        			master.dbo.sysservers 	servers2,
						sysmergepublications 	pubs
				where subs1.subid = @subid
					and subs2.subid = @partnerid
					and pubs.pubid = subs1.pubid
					and servers1.srvid = subs1.srvid
					and servers2.srvid = subs2.srvid
					and subs1.subid = subs1.partnerid 
					and replinfo.repid = subs1.subid 
	    	
	    END
	else
		begin
			declare #cursor cursor local FAST_FORWARD FOR select DISTINCT sub.subid, sub.partnerid 
	        	FROM sysmergesubscriptions 	sub,
	    	    sysmergesubscriptions 		sub1, 
	            master.dbo.sysservers		ss,  
	            master.dbo.sysservers		ss1, 
	            sysmergepublications 		pub 
	         	WHERE ((@subscriber = N'%') OR (UPPER(ss.srvname) = UPPER(@subscriber) collate database_default)) 
					AND ((@publisher = N'%') OR (UPPER(ss1.srvname) = UPPER(@publisher) collate database_default)) 
					AND sub1.srvid = ss1.srvid 
	           		AND sub.srvid = ss.srvid  
	           		AND pub.name LIKE  @publication  
	           		AND sub.pubid = pub.pubid 
                                AND ((@subscriber_db = N'%') OR (sub.db_name = @subscriber_db collate database_default))
                                AND ((@publisher_db = N'%') OR (sub1.db_name = @publisher_db collate database_default))
					AND sub1.pubid = pub.pubid 
					AND (sub.subscription_type=1 or sub.subscription_type=2) 
				FOR READ ONLY

			open #cursor
			select @cursor_open = 1					
			fetch next from #cursor into @subid, @partnerid
			while (@@fetch_status <> -1)
				begin
				    insert into #helpmergepullsubscription 
						select pubs.name, servers2.srvname, subs2.db_name, servers1.srvname, subs1.db_name, 
							subs1.status, subs1.subscriber_type, subs1.subscription_type, subs1.priority, 
							subs1.sync_type, subs1.description, replinfo.merge_jobid, 0, 
							convert(nvarchar(12), subs1.last_sync_date, 112) + substring(convert(nvarchar(24), subs1.last_sync_date, 121), 11,13), 
							subs1.use_interactive_resolver, @subid, subs1.last_sync_status, subs1.last_sync_summary
				    	FROM  	sysmergesubscriptions 	subs1,
				    		  	sysmergesubscriptions 	subs2,
				    		  	MSmerge_replinfo	 	replinfo,
			        			master.dbo.sysservers	servers1,
			        			master.dbo.sysservers	servers2,
								sysmergepublications 	pubs
						where subs1.subid = @subid
							and subs2.subid = @partnerid
							and pubs.pubid = subs1.pubid
							and servers1.srvid = subs1.srvid
							and servers2.srvid = subs2.srvid
							and @subid <> @partnerid -- do not show the loopback subscription
							and replinfo.repid = subs1.subid 
		
					if @@ERROR <> 0
						begin
							set @retcode = 1
							goto DONE
						end					
				fetch next from #cursor into @subid, @partnerid
			end
		end
		declare #helpsubscriptioncursor CURSOR LOCAL FAST_FORWARD FOR 
			select DISTINCT publisher, publisher_db, publication
       			FROM #helpmergepullsubscription
				FOR READ ONLY
		create table #syncmgr_keyexist (syncmgr_keyexist int)
		open #helpsubscriptioncursor
		select @helpsubscriptioncursor_open = 1					
		fetch next from #helpsubscriptioncursor into @publisher_local, @publisher_db_local, @publication_local
		while (@@fetch_status <> -1)
		begin
			set @subscription_name = @publisher_local + ':' + @publisher_db_local + ':' + @publication_local + ':' + @subscriber + ':' + @subscriber_db
			/* Replace back slash with forward slash so that the key name is a valid REGISTRY key name */
			set @subscription_name = REPLACE(@subscription_name,'\','/')
			set @regkey = 'SOFTWARE\Microsoft\Microsoft SQL Server\80\Replication\Subscriptions\' + @subscription_name
			insert into #syncmgr_keyexist EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', @regkey
			select @syncmgr_keyexist = syncmgr_keyexist from #syncmgr_keyexist
			update #helpmergepullsubscription set enabled_for_syncmgr = @syncmgr_keyexist 
				where UPPER(publisher) = UPPER(@publisher_local)
					and publisher_db = @publisher_db_local
					and publication = @publication_local
			fetch next from #helpsubscriptioncursor into @publisher_local, @publisher_db_local, @publication_local
		end

	select 'subscription_name'= hs.publisher + ':' + hs.publisher_db + ':' + hs.publication collate database_default, hs.publication, hs.publisher,
            hs.publisher_db, hs.subscriber, hs.subscriber_db, hs.status, hs.subscriber_type, hs.subscription_type,
            hs.priority, hs.sync_type, hs.description, hs.merge_jobid, hs.enabled_for_syncmgr, hs.last_updated, sp.publisher_login,
            sp.publisher_password, sp.publisher_security_mode, sp.distributor, sp.distributor_login, 
            sp.distributor_password, sp.distributor_security_mode, 'ftp_address' = null, 'ftp_port' = 0, 
            'ftp_login' = null, 'ftp_password' = null, sp.alt_snapshot_folder, sp.working_directory, sp.use_ftp, 
            sp.offload_agent, sp.offload_server, hs.use_interactive_resolver, hs.subid, 
            sp.dynamic_snapshot_location, hs.last_sync_status, hs.last_sync_summary
				from #helpmergepullsubscription hs
     left outer join MSsubscription_properties sp
                  on hs.publisher = sp.publisher collate database_default
                 and hs.publisher_db = sp.publisher_db collate database_default
                 and hs.publication = sp.publication collate database_default
			order by hs.publisher, hs.publisher_db, hs.publication, hs.subscriber, hs.subscriber_db
    	 
	select @retcode = 0
DONE:
	if (@cursor_open = 1)
		begin
			close #cursor
			deallocate #cursor
		end			
	--drop table #helpmergepullsubscription

	return @retcode	
go
exec dbo.sp_MS_marksystemobject sp_helpmergepullsubscription
go

grant execute on dbo.sp_helpmergepullsubscription to public
go

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

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

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

    SET NOCOUNT ON

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

	select @platform_nt = 0x1

    SELECT @use_ftp_bit = 0

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

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

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

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

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

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

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

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

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

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

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

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

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

        SELECT @use_ftp_bit = 1

    END
    ELSE
    BEGIN
        SELECT @use_ftp_bit = 0
    END

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

	end

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

	begin tran
	save tran sp_addpullsub_agent

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

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

		select @database = db_name()

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

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

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


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

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

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

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

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

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

exec dbo.sp_MS_marksystemobject sp_addmergepullsubscription_agent
go

grant exec on dbo.sp_addmergepullsubscription_agent to public
go

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

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

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

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

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

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

                select @spacer = ' and '
            end
            else if @clause_type in ('upd version', 'subwins_check')
            begin
                select @cmd = @cmd + @spacer + quotename(@key) + N' = @' + @col + N'_old'
                select @spacer = ' and '
            end
            else if @clause_type = 'version pk'
            begin
                select @cmd = @cmd + @spacer + @qualname + '.' + quotename(@key) + N' = inserted.' + quotename(@key)
                select @spacer = ' and
     '
            end
            else if @clause_type in ('trg pk', 'old pk')
            begin
				if @op_type = 'ins'
					select @cmd = @cmd + @spacer + quotename(@key) + N' = @' + @col + N'_old'
				else
					-- The vars correspoding to pk were set in sp_MSscript
					-- _pkvar_assignment.
					select @cmd = @cmd + @spacer + quotename(@key) + N' = @' + @col 

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

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

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

    end
	-- 'upd rc' is used for column value conflict detection. It is no longer used.
    else if @clause_type = 'upd rc'
    begin
        select @this_col = 1, @art_col = 1

        while @this_col <= @src_cols
        begin
            exec @rc = dbo.sp_MSget_colinfo @objid, @this_col, @columns, 0, @colname output, @ccoltype output
	    	if @rc = 0
            begin
                select @cmd = @cmd +  @spacer + '(' + @colname + N' = @c' + convert(varchar(4), @this_col) + N'_old or (' 
                select @cmd = @cmd + @colname + ' is null and @c' + convert(varchar(4), @this_col) + N'_old is null)) '
                select @spacer = N' and 
     '
                exec dbo.sp_MSflush_command @cmd output, 0, @indent
            end

            exec dbo.sp_MSflush_command @cmd output, 1, @indent

            select @this_col = @this_col + 1
        end

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

grant execute on dbo.sp_MSscript_where_clause to public
go

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

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

    exec sp_MSget_qualified_name @objid, @qualname OUTPUT

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

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

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

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

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

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

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

        -- Mark the bit in the bitmap as updated.

	    exec dbo.sp_MSget_col_position @objid, null, 'msrepl_tran_version', @this_col = @this_col output
	    select @src_cols = count(*) from syscolumns where id = @objid
        select @num_bytes = @src_cols / 8 + 1

	    set @i_byte = 1 + (@this_col-1) / 8
	    set @i_bit  = power(2, (@this_col-1) % 8 )

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

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

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

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

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

grant execute on dbo.sp_MSscript_begintrig1 to public
go

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

raiserror('Creating procedure sp_MSscript_begintrig2', 0,1)
go
create procedure sp_MSscript_begintrig2 (
	@publisher   sysname
	,@publisher_db sysname
	,@publication sysname
	,@objid       int
	,@op_type     char(3) = 'ins' -- ins, upd, del
	,@agent_id    int
	,@fisqueued	  bit = 0 -- 1 = Queued subscription
)
as
BEGIN
	declare @cmd nvarchar(4000)
			,@queue_server sysname

	if @op_type = 'ins' 
	begin
		insert into #proctext(procedure_text) values(N'
	if Objectproperty(@@procid,''TriggerInsertOrder'') != 1	
	begin
		declare @trigname sysname
		select @trigname = object_name(@@procid)

		raiserror (21128, 16, 1, @trigname)
		goto FAILURE
	end ')
	end
	else if @op_type = 'upd' 
	begin
		insert into #proctext(procedure_text) values(N'
	if Objectproperty(@@procid,''TriggerUpdateOrder'') != 1	
	begin
		declare @trigname sysname
		select @trigname = object_name(@@procid)

		raiserror (21129, 16, 1, @trigname)
		goto FAILURE
	end ')
	end
	else if @op_type = 'del' 
	begin
		insert into #proctext(procedure_text) values(N'
	if Objectproperty(@@procid,''TriggerDeleteOrder'') != 1
	begin
		declare @trigname sysname
		select @trigname = object_name(@@procid)

		raiserror (21130, 16, 1, @trigname)
		goto FAILURE
	end ')
	end

	--
	-- scripting for debug messages
	--
	select @cmd = N'
	select @update_mode_id = update_mode 
	from dbo.MSsubscription_agents where id = ' + convert(nvarchar(10), @agent_id) + N' '
	insert into #proctext(procedure_text) values (@cmd)

	--
	-- continue with scripting
	--
	select @cmd = N'
	' + N'--
	' + N'-- initialize and validate based on update mode
	' + N'--
	if @update_mode_id = 1
	begin
		exec @retcode = dbo.sp_MSget_publisher_rpc @@procid, @connect_string output
		if @retcode <>0 or @@error <> 0 goto FAILURE
	end'
	insert into #proctext(procedure_text) values (@cmd)

	--
	-- continue with scripting
	--
	if (@fisqueued = 1)
	begin
		--
		-- Queued specific scripting
		--
		select @cmd = N'
	else if @update_mode_id in (2,4)
	begin
		select @queue_server = queue_server, @queue_id = queue_id from dbo.MSsubscription_agents where id = ' + convert(nvarchar(10), @agent_id) + N'
		if (@queue_id is NULL) 
			goto FAILURE
	end'
		insert into #proctext(procedure_text) values (@cmd)

		--
		-- continue with scripting
		--
		select @cmd = N'
	else if @update_mode_id in (3,5)
	begin
		select @queue_server = queue_server, @queue_id = queue_id from dbo.MSsubscription_agents 
			where id = ' + convert(nvarchar(10), @agent_id) + N' and failover_mode = 1 '
		insert into #proctext(procedure_text) values (@cmd)

		--
		-- continue with scripting
		--
		select @cmd = N'
		if (@queue_id is NULL)
		begin
			exec @retcode = dbo.sp_MSget_publisher_rpc @@procid, @connect_string output
			if @retcode <>0 or @@error <> 0 
				goto FAILURE
		end
	end'
		insert into #proctext(procedure_text) values (@cmd)
	end
	
	--
	-- continue with scripting
	--
	select @cmd = N'
	else if @update_mode_id = 0
	begin
		raiserror (''read only mode - no updates allowed'', 16, 1)
		goto FAILURE
	end
	else
	begin
		raiserror(''invalid update mode %d'', 16, 1, @update_mode_id)
		goto FAILURE
	end '
	insert into #proctext(procedure_text) values (@cmd)

	if (@fisqueued = 1)
	begin		
		select @cmd = N'
	' + N'--
	' + N'-- set queue prefix for MSMQ cases
	' + N'--
	if (@update_mode_id in (2,3))
	begin
		select @queue_id = N''DIRECT=OS:'' + @queue_server + N''\PRIVATE$\'' + @queue_id 
	end'
		insert into #proctext(procedure_text) values (@cmd)

		--
		-- Get failover mode scripting
		--
	    select @cmd = N'
	' + N'--
	' + N'-- get failover mode
	' + N'--
	if @update_mode_id in (3,5)
	begin
		select @failover_mode_id = failover_mode
		from dbo.MSsubscription_agents where id = '
		+ convert(nvarchar(10), @agent_id) + ' and update_mode in (3,5)
	end'
		insert into #proctext(procedure_text) values (@cmd)
    end
    
    --
	-- Start the distributed tran and get the transaction id as we may use
	-- it for queue sends
	--
    select @cmd = N'
	' + N'--
	' + N'-- begin tran or dist tran based on update mode
	' + N'--
	if (@update_mode_id in (4,5))
		BEGIN TRAN
	else
		BEGIN DISTRIBUTED TRAN
	'
	insert into #proctext(procedure_text) values (@cmd)
		
	if (@fisqueued = 1)
	begin
		select @cmd = N'
	' + N'--
	' + N'-- save the transaction token for later use
	' + N'--
	exec sp_getbindtoken @out_token = @tran_id OUTPUT , @for_xp_flag = 1 '
		insert into #proctext(procedure_text) values (@cmd)
	end

	--
	-- all done with scripting
	--
	return 0
END
go

grant execute on dbo.sp_MSscript_begintrig2 to public
go

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

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

CREATE PROCEDURE sp_helpreplicationdboption (
	@dbname sysname = '%', @type sysname = 'replication allowed'
)
AS
BEGIN
	SET NOCOUNT ON

	/*
	** Declarations.
	*/

	DECLARE @retcode int
			,@typebit int
			,@distbit int -- bit to distinguish distribution databases
			,@dbowner bit
			,@dbreadonly bit
			,@replication_db sysname

	SELECT @distbit = 16
			,@dbowner = 0
			,@dbreadonly = 0

	-- don't do security check.

	if (lower(@type collate SQL_Latin1_General_CP1_CS_AS) like 'publish%')
		select @typebit = 1
	else if (lower(@type collate SQL_Latin1_General_CP1_CS_AS) like 'subscribe%')
		select @typebit = 2
	else if (lower(@type collate SQL_Latin1_General_CP1_CS_AS) like 'merge publish%')
		select @typebit = 4
	else if (lower(@type collate SQL_Latin1_General_CP1_CS_AS) like 'merge subscribe%')
		select @typebit = 8
	else if (lower(@type collate SQL_Latin1_General_CP1_CS_AS) like 'replication allowed%')
		select @typebit = 0
	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.
	*/
	CREATE TABLE #replicationdbs (name sysname collate database_default not null, id int identity NOT NULL, transpublish bit not null, mergepublish bit not null, dbowner bit not null, dbreadonly bit not null)
	if @typebit <> 0
	begin
		INSERT INTO #replicationdbs (name, transpublish, mergepublish, dbowner, dbreadonly)
			SELECT name, 
				case when (category & 1) <> 0 then 1 else 0 end,
				case when (category & 4) <> 0 then 1 else 0 end,
				@dbowner,
				DATABASEPROPERTY(name, N'IsReadOnly')
			FROM master.dbo.sysdatabases
			WHERE ((@dbname = N'%') OR (name = @dbname collate database_default))
				AND (category & @typebit) <> 0 
				AND (HAS_DBACCESS ( name ) = 1)

		DECLARE hCdboinfo CURSOR LOCAL FAST_FORWARD FOR
			SELECT name FROM #replicationdbs
			FOR READ ONLY
		OPEN hCdboinfo
		FETCH hCdboinfo INTO @replication_db
		WHILE (@@fetch_status <> -1)
		BEGIN
			EXEC @retcode = sp_MSrepl_isdbowner @replication_db
			IF (@retcode IS NOT NULL) AND (@retcode <> 0)
			BEGIN
				UPDATE #replicationdbs set dbowner = 1 where name = @replication_db
			END
			FETCH hCdboinfo INTO @replication_db
		END
		CLOSE hCdboinfo
		DEALLOCATE hCdboinfo
	end            
	else
	begin
		DECLARE @db_category int

		/* Filter out distribution databases */
		DECLARE hC  CURSOR LOCAL FAST_FORWARD FOR 
			SELECT name, category, DATABASEPROPERTY(name, N'IsReadOnly')
			FROM master.dbo.sysdatabases 
			WHERE ((@dbname = N'%') OR (name = @dbname collate database_default)) 
				AND (category & @distbit) = 0 
				AND (HAS_DBACCESS ( name ) = 1)
			FOR READ ONLY
		OPEN hC
		FETCH hC INTO @replication_db, @db_category, @dbreadonly
		WHILE (@@fetch_status <> -1)
		BEGIN
			if @replication_db NOT IN ('master','model','tempdb','msdb','MSSQLWeb')
			BEGIN
				EXEC @retcode = sp_MSrepl_isdbowner @replication_db
				IF (@retcode IS NOT NULL) AND (@retcode <> 0)
				BEGIN
					SELECT @dbowner = 1
				END
				ELSE
				BEGIN
					SELECT @dbowner = 0
				END
				INSERT INTO #replicationdbs (name, transpublish, mergepublish, dbowner, dbreadonly) 
				VALUES (@replication_db,
						case when (@db_category & 1) <> 0 then 1 else 0 end,
						case when (@db_category & 4) <> 0 then 1 else 0 end,
						@dbowner, @dbreadonly)
			END
			FETCH hC INTO @replication_db, @db_category, @dbreadonly
		END
		CLOSE hC
		DEALLOCATE hC
	end
	SELECT * FROM #replicationdbs
	-- DROP TABLE #replicationdbs
END
go

grant execute on dbo.sp_helpreplicationdboption to public
go


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

CREATE PROCEDURE sp_MSCleanupForPullReinit (
	@publication 		sysname,
	@publisher_db		sysname,
	@publisher     		sysname = @@servername
	) AS
	declare @pubid 		uniqueidentifier
	declare @artid 		uniqueidentifier
	declare @retcode	smallint

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

	/* This only gets called after database is enable to subscribe, so sysmergepublications should exist */
	select @pubid = pubid FROM sysmergepublications 
		WHERE name = @publication and UPPER(publisher)=UPPER(@publisher) and publisher_db = @publisher_db

	/* Normal case - nothing to cleanup, just return */
	if @pubid is null
		return (1)	

	begin transaction
	save tran cleanupforreinit
	/* 
	** Make sure you NULL out gen_cur for other articles that share this table 
	** since we are deleting the genhistroy row for that generation 
	*/
	update sysmergearticles set gen_cur=NULL where gen_cur in
		(select generation from MSmerge_genhistory where pubid = @pubid)
	if @@ERROR<>0 
		goto Error
	delete from MSmerge_genhistory where pubid = @pubid
	if @@ERROR<>0 
		goto Error
	delete from sysmergesubsetfilters where pubid=@pubid
	if @@ERROR<>0 
		goto Error
	delete from sysmergeschemachange where pubid = @pubid
	if @@ERROR<>0 
		goto Error
	delete from MSmerge_contents where tablenick in (select nickname from sysmergearticles where pubid=@pubid)
	if @@ERROR<>0 
		goto Error
	delete from MSmerge_tombstone where tablenick in (select nickname from sysmergearticles where pubid=@pubid)
	if @@ERROR<>0 
		goto Error
	commit tran 
	return (0)

Error:
	rollback tran cleanupforreinit
	commit tran 
	return (1)
GO
exec dbo.sp_MS_marksystemobject sp_MSCleanupForPullReinit
go

grant execute on dbo.sp_MSCleanupForPullReinit to public
go

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

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

create procedure sp_MScleanup_conflict
@pubid					uniqueidentifier,
@conflict_retention	int = NULL
AS
	declare @pubname			sysname
	declare @valid_date			datetime
	declare @conflict_table		sysname
	declare @conflict_id		int
	declare @retention_string		nvarchar(100)
	declare @pubidstr			nvarchar(100)
	declare @retcode			int
	
	--if no conflict_retention value is specified, query local table and find out.
	if @conflict_retention is NULL
		select @conflict_retention=conflict_retention from sysmergepublications where pubid=@pubid

	--do not do any cleanup if conflict retention value is 0
	else if @conflict_retention = 0
		return (0)

	select @pubname=name from sysmergepublications where pubid=@pubid
	select @pubidstr = convert(nvarchar(40), @pubid)
	select @valid_date = dateadd(day, -@conflict_retention, getdate())
	select @retention_string = convert(nvarchar, @conflict_retention)
	/*
	** Security Check
	*/
    EXEC @retcode = dbo.sp_MSreplcheck_publish
    IF @@ERROR <> 0 or @retcode <> 0
        return (1)


	if exists (select name from syscolumns where name='create_time' and id = object_id('MSmerge_delete_conflicts'))
	begin
		delete from MSmerge_delete_conflicts where create_time < @valid_date and pubid=@pubid
		if @@ERROR<>0
			return (1)
	end
	
	declare AC CURSOR LOCAL FAST_FORWARD for select conflict_table from sysmergearticles where pubid=@pubid
	open AC
	fetch AC into @conflict_table
	while (@@fetch_status<>-1)
	begin
		if @conflict_table is NOT null
			begin

			select @conflict_id = object_id(@conflict_table)
			select @conflict_table=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 ' + @conflict_table + ' add MSrepl_create_time datetime not NULL default getdate() ')
						if @@ERROR<>0
							goto FAILURE
					end

			if @conflict_id is not NULL and @retention_string is not NULL and exists (select name from syscolumns where name='MSrepl_create_time' and id=@conflict_id)
					begin
						exec ('delete from ' + @conflict_table + ' where datediff(dd, MSrepl_create_time, getdate()) > ' + @retention_string + ' and pubid = ''' + @pubidstr + '''')
						if @@ERROR<>0
							goto FAILURE
					end
			end
		fetch next from AC into @conflict_table
	end
	close AC
	deallocate AC
	return (0)
FAILURE:
	close AC
	deallocate AC
	return (1)
GO
exec dbo.sp_MS_marksystemobject sp_MScleanup_conflict
go

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

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

CREATE PROCEDURE sp_MSpublicationcleanup (
	@publication 		sysname,
	@publisher_db		sysname,
	@publisher	       	sysname = @@servername
	) AS
	declare @pubid 		uniqueidentifier
	declare @artid 		uniqueidentifier
	declare @retcode	smallint
    declare @objectname sysname
    declare @objectowner sysname

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

	/* This only gets called after database is enable to subscribe, so sysmergepublications should exist */
	select @pubid = pubid FROM sysmergepublications 
		WHERE name = @publication and UPPER(publisher)=UPPER(@publisher) and publisher_db = @publisher_db

	/* Normal case - nothing to cleanup, just return */
	if @pubid is null
		return (1)

	/* 
	** If we are deleting the last publication in the database, ie there are articles in
	** sysmergearticles with a different pubid then the one being dropped
	** then remove all the rows in MSmerge_genhistory, MSmerge_contents and MSmerge_tombstone 
	** use a truncate table in order to make the operation non logged and hence efficient
	*/
	if not exists (select * from sysmergearticles where pubid <> @pubid)
		begin
			truncate table MSmerge_genhistory
			truncate table MSmerge_contents
			truncate table MSmerge_tombstone
		end

	begin transaction
	save tran MSpublicationcleanup
	/* Clean up the articles for this publication, and delete the row */
	select @artid = artid FROM sysmergearticles WHERE pubid = @pubid
	while @artid is not null
		begin
		if not exists (select * from sysmergearticles WHERE artid = @artid and pubid <> @pubid)
			begin
				exec @retcode=sp_MSarticlecleanup @pubid, @artid
				if @retcode<>0 or @@ERROR<>0 
					goto Error
			end
		delete from sysmergearticles where artid = @artid and pubid = @pubid
		if @@ERROR<>0 
			goto Error
		set @artid = NULL
		select @artid = artid FROM sysmergearticles WHERE pubid = @pubid
		end
		
    /* Unmark all schema articles in this publication */
    if exists (select * from sysobjects where name = 'sysmergeschemaarticles')
    begin
        declare hschemaarticle_cur cursor local fast_forward for
            select destination_object, destination_owner from sysmergeschemaarticles where
            pubid = @pubid 
        for read only
        open hschemaarticle_cur
        fetch hschemaarticle_cur into @objectname, @objectowner
        while (@@fetch_status<>-1)
        begin
                exec  sp_MSunmarkschemaobject @objectname, @objectowner    
				if @retcode<>0 or @@ERROR<>0 
					goto Error
                fetch hschemaarticle_cur into @objectname, @objectowner
        end
        close hschemaarticle_cur
        deallocate hschemaarticle_cur

        /* Delete all schema article rows for this publication*/
        delete from sysmergeschemaarticles where pubid = @pubid
		if @@ERROR<>0 
			goto Error
    end
    
	/* Now clean up any traces in other system tables */

	/* 
	** Make sure you NULL out gen_cur for other articles that share this table 
	** since we are deleting the genhistroy row for that generation 
	*/
	update sysmergearticles set gen_cur=NULL where gen_cur in
		(select generation from MSmerge_genhistory where pubid = @pubid)
	if @@ERROR<>0 
		goto Error
	delete from MSmerge_genhistory where pubid = @pubid
	if @@ERROR<>0 
		goto Error
	delete from MSmerge_replinfo where repid in (select subid from sysmergesubscriptions where pubid = @pubid and status <> 2)
	if @@ERROR<>0 
		goto Error
    delete from sysmergesubsetfilters where pubid = @pubid
	if @@ERROR<>0 
		goto Error
	delete from sysmergesubscriptions where pubid = @pubid and status <> 2
	if @@ERROR<>0 
		goto Error
	delete from sysmergepublications where pubid = @pubid
	if @@ERROR<>0 
		goto Error
	delete from sysmergeschemachange where pubid = @pubid
	if @@ERROR<>0 
		goto Error

	commit tran 
	return (0)

Error:
	rollback tran MSpublicationcleanup
	commit tran 
	return (1)
GO
exec dbo.sp_MS_marksystemobject sp_MSpublicationcleanup
go

grant execute on dbo.sp_MSpublicationcleanup to public
go

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

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

CREATE PROCEDURE sp_mergesubscription_cleanup (
	@publisher		sysname,
	@publisher_db	sysname,
	@publication 	sysname
	) AS
	declare @pubid 				uniqueidentifier
	declare @artid 				uniqueidentifier
	declare @retcode			smallint
	declare @subscription_type  int
    declare @objid              int
    declare @objectname         sysname
    declare @objectowner        sysname

	/*
	** if there is nothing to cleanup, then just return.
	*/
	if not exists (select * from sysobjects where name='sysmergesubscriptions')
		return (0)
		
	/* This only gets called after database is enable to subscribe, so sysmergepublications should exist */
	select @pubid = pubid FROM sysmergepublications 
		WHERE name = @publication and UPPER(publisher)=UPPER(@publisher) and publisher_db=@publisher_db

	/* Normal case - nothing to cleanup, just return */
	if @pubid is null
		return (1)

	select @subscription_type = subscription_type from sysmergesubscriptions where pubid=@pubid and subid<>pubid

	/* This procedure is not intended to be used for cleanning-up pull/anonymous subscriptions */
	if @subscription_type > 0
		begin
			raiserror(20091, 16, -1)
			return (1)
		end

	/* Clean up the articles for this publication, and delete the row */
	select @artid = artid FROM sysmergearticles WHERE pubid = @pubid
	while @artid is not null
		begin
		if not exists (select * from sysmergearticles WHERE artid = @artid and pubid <> @pubid)
			begin
				exec @retcode=sp_MSarticlecleanup @pubid, @artid
				if @retcode<>0 or @@ERROR<>0 return (1)
			end
		delete from sysmergearticles where artid = @artid and pubid = @pubid
		set @artid = NULL
		select @artid = artid FROM sysmergearticles WHERE pubid = @pubid
		end
		
    /* Cleanup the schema articles */

    /* Unmark all schema article objects unconditionally */
    if exists (select * from sysobjects where name = 'sysmergeschemaarticles')
    begin
        declare hschemaarticle_cur cursor local fast_forward for
            select destination_object, destination_owner from sysmergeschemaarticles where
            pubid = @pubid 
        for read only
        open hschemaarticle_cur
        fetch hschemaarticle_cur into @objectname, @objectowner
        while (@@fetch_status<>-1)
        begin
                -- Ignore errors
                exec  sp_MSunmarkschemaobject @objectname, @objectowner    
                fetch hschemaarticle_cur into @objectname, @objectowner
        end
        close hschemaarticle_cur
        deallocate hschemaarticle_cur

        /* Delete all schema article rows for this publication*/
        delete from sysmergeschemaarticles where pubid = @pubid
    end

	/* 
	** Make sure you NULL out gen_cur for other articles that share this table 
	** since we are deleting the genhistroy row for that generation 
	*/
	update sysmergearticles set gen_cur=NULL where gen_cur in
		(select generation from MSmerge_genhistory where pubid = @pubid)

	/* Now clean up any traces in other system tables */
	delete from MSmerge_genhistory where pubid = @pubid 
	delete from MSmerge_replinfo where repid in (select subid from sysmergesubscriptions where pubid = @pubid)
	delete from sysmergesubscriptions where pubid = @pubid
	delete from sysmergepublications where pubid = @pubid
	delete from sysmergeschemachange where pubid = @pubid
	/* 
	** If last subscription is dropped and the DB is not enabled for publishing,
	** then remove the merge system tables
	*/
	IF (not exists (select * from sysmergesubscriptions )) 
		AND (select category & 4 FROM master..sysdatabases WHERE name = DB_NAME() collate database_default )=0
	BEGIN
		execute @retcode = dbo.sp_MSdrop_mergesystables
		if @@ERROR <> 0 or @retcode <> 0
		begin
			return (1)
		end
	END	
GO
exec dbo.sp_MS_marksystemobject sp_mergesubscription_cleanup
go
grant execute on dbo.sp_mergesubscription_cleanup to public
go


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

raiserror('Creating procedure sp_subscription_cleanup', 0,1)
GO
CREATE PROCEDURE sp_subscription_cleanup (
	@publisher		sysname,
	@publisher_db	sysname,
	@publication	sysname = NULL,
	@reserved		nvarchar(10) = NULL
)
AS
BEGIN

	declare @object_name sysname
			,@object_type char(2)
			,@independent_agent bit
			,@retcode int
			,@synctran_bit int
			,@parent_obj int
			,@object_id int
			,@cmd nvarchar(4000)

	select @synctran_bit			= 256

	/*
    ** Security Check
    */

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

	if @publication = '' OR @publication is NULL
		select @independent_agent = 0
	else
		select @independent_agent = 1

	IF exists (select name from sysobjects where name = 'MSreplication_objects')
	BEGIN
		declare object_cursor CURSOR LOCAL FAST_FORWARD for 
			select DISTINCT object_name, object_type from MSreplication_objects o
					where (UPPER(o.publisher) = UPPER(@publisher) and
			  		  o.publisher_db = @publisher_db and
				  	  o.publication = @publication) or
						@reserved = 'drop_all'

		OPEN object_cursor
    	FETCH object_cursor INTO @object_name, @object_type
 	    WHILE (@@fetch_status <> -1)
    	      BEGIN
        	      IF @object_type = 'T' 
				  begin
					select @parent_obj = NULL
					select @parent_obj = parent_obj, @object_id = id from
						sysobjects where name = @object_name 
					if @parent_obj is not null
					begin
						-- Unmark synctran bit
						update sysobjects set replinfo = replinfo & ~@synctran_bit where 
							id = @parent_obj and
							(replinfo & @synctran_bit) <> 0
						IF @@ERROR <> 0 
							GOTO UNDO
						exec @retcode = dbo.sp_MSdrop_object 
							@object_id = @object_id
						if @retcode <> 0 or @@error <> 0
							goto UNDO

						-- Clean up identity range entry
						-- Since we only support one trigger per subscriber table
						-- we assume identity range row can not be reused by multiple
						-- subscriptions.
						IF EXISTS(select * from sysobjects where type='U' and name = 'MSsub_identity_range')
						BEGIN
							if exists (select * from MSsub_identity_range where objid = @parent_obj)
							begin
								-- Drop the identity range constraits.
								exec @retcode = dbo.sp_MSreseed
									@objid =  @parent_obj,
									-- range or seed can be anything
									@next_seed = 10,
									@range = 10,
									@is_publisher = -1,
									@check_only = 1,
									@drop_only = 1
								IF @retcode <> 0 or @@ERROR <> 0 
									GOTO UNDO

								delete MSsub_identity_range where objid = @parent_obj
								IF @@ERROR <> 0 
									GOTO UNDO
							end

							IF NOT EXISTS (SELECT * FROM MSsub_identity_range)
							BEGIN
								DROP TABLE MSsub_identity_range
								IF @@ERROR <> 0 
									GOTO UNDO
							END
						END
					end
				  end
        	      delete from MSreplication_objects where object_name=@object_name
            	  FETCH object_cursor INTO @object_name, @object_type
         	 END
    	CLOSE object_cursor
    	DEALLOCATE object_cursor
    	
		if not exists (select * from MSreplication_objects) 
		begin
    		drop table MSreplication_objects
			IF @@ERROR <> 0 
				GOTO UNDO
		end
    	
	END

	--
	-- cleanup queued conflict tables
	--
	IF exists (select name from dbo.sysobjects where name = 'MSsubscription_agents')
	BEGIN
		declare		@agent_id int
					,@cft_table sysname
					,@owner sysname

		--
		-- first get the agent(s) for this queued subscription(s) and 
		-- 
		declare #agent_cursor CURSOR LOCAL FAST_FORWARD for 
			select id from dbo.MSsubscription_agents 
			where ((UPPER(publisher) = UPPER(@publisher) and 
					publisher_db = @publisher_db and 
					publication = @publication) or (@reserved = 'drop_all')) and
					update_mode in (2,3,4,5)

		open #agent_cursor
		fetch #agent_cursor into @agent_id
		while (@@fetch_status != -1)
		begin
			--
			-- drop the conflict table for each article in this subscription
			--
			if exists (select name from dbo.sysobjects where name = 'MSsubscription_articles')
			begin
			
				declare #object_cursor CURSOR LOCAL FAST_FORWARD for 
					select owner, cft_table from dbo.MSsubscription_articles
					where agent_id = @agent_id

				OPEN #object_cursor
				FETCH #object_cursor INTO @owner, @cft_table
				WHILE (@@fetch_status != -1)
				BEGIN
					--
					-- drop the conflict table(s) for this article - ignore errors
					--
					select @cmd = case 
						when (@owner IS NULL) then
							N'if exists (select * from dbo.sysobjects where name = N''' + 
							master.dbo.fn_MSgensqescstr(@cft_table) collate database_default + ''') drop table ' + 
							quotename(master.dbo.fn_MSgensqescstr(@cft_table)) collate database_default
						else
							N'if exists (select * from dbo.sysobjects where name = N''' + 
							master.dbo.fn_MSgensqescstr(@cft_table) collate database_default + ''') drop table ' + 
							quotename(master.dbo.fn_MSgensqescstr(@owner)) collate database_default + N'.' +
							quotename(master.dbo.fn_MSgensqescstr(@cft_table)) collate database_default
						end
					execute ( @cmd )

					-- get next row
					FETCH #object_cursor INTO @owner, @cft_table
				END
				CLOSE #object_cursor
				DEALLOCATE #object_cursor

				--
				-- delete entries from MSsubscription_articles for this agent id
				--
				delete dbo.MSsubscription_articles where agent_id = @agent_id

				--
				-- drop MSsubscription_articles if empty (should we do it)
				-- 
				IF NOT EXISTS (SELECT * FROM dbo.MSsubscription_articles)
				BEGIN
					DROP TABLE dbo.MSsubscription_articles
					IF @@ERROR != 0 
						GOTO UNDO
				END
			end

			--
			-- get the next agent
			--
			fetch #agent_cursor into @agent_id
		end
		close #agent_cursor
		deallocate #agent_cursor
	END

	--
	-- clean discarded queued transactions
	--
	if exists (select name from dbo.sysobjects where name = 'MSreplication_queue')
	begin
		delete dbo.MSreplication_queue
		where (UPPER(publisher) = UPPER(@publisher) and 
				publisher_db = @publisher_db and 
				publication = @publication) or 
				@reserved = 'drop_all'
	end
	
	IF exists (select name from sysobjects where name = 'MSreplication_subscriptions')
	BEGIN
	delete from MSreplication_subscriptions 
		where (UPPER(publisher) = UPPER(@publisher) AND
			  publisher_db = @publisher_db AND
			  -- Drop the subscription as long as the publication name matches even if
			  -- the publication is not independent agent
			  -- This behaviour is expected by sp_droppullsubscription
			  -- -- independent_agent = @independent_agent and 
			  ((@independent_agent=0 and independent_agent = 0) or 
			  publication = @publication))
			  or @reserved = 'drop_all'
 
		IF NOT EXISTS (SELECT * FROM MSreplication_subscriptions)
		BEGIN
			DROP TABLE MSreplication_subscriptions
			IF @@ERROR <> 0 
				GOTO UNDO
		END
	END


	IF exists (select name from sysobjects where name = 'MSsubscription_agents')
	BEGIN
		delete from MSsubscription_agents
		where (UPPER(publisher) = UPPER(@publisher) AND
			  publisher_db = @publisher_db AND
			  -- Drop the subscription as long as the publication name matches even if
			  -- the publication is not independent agent
			  -- This behaviour is expected by sp_droppullsubscription
			  (publication = @publication or
			   (@independent_agent=0 and publication = N'ALL')))
			  or @reserved = 'drop_all'

		-- Delete the agent entry if no corresponding rows found in MSreplication_subscription
		-- table.
		-- This is to cleanup share agent entry.
		-- This behaviour is expected by sp_droppullsubscription
		if object_id('MSreplication_subscriptions') is not null
		begin
			if not exists (select * from MSreplication_subscriptions s where
					s.publisher = @publisher and
					s.publisher_db = @publisher_db and
					s.independent_agent = 0)
				delete from MSsubscription_agents where
					publisher = @publisher and
					publisher_db = @publisher_db and
					publication = N'ALL'
		end
		else
			delete  MSsubscription_agents

		IF NOT EXISTS (SELECT * FROM MSsubscription_agents)
		BEGIN
			DROP TABLE MSsubscription_agents
			IF @@ERROR <> 0 
				GOTO UNDO
		END
	END

	IF EXISTS(select * from sysobjects where type=N'U' and name = 'MSsubscription_properties')
	BEGIN
		DELETE FROM MSsubscription_properties 
		WHERE (UPPER(publisher) = UPPER(@publisher)	AND
		publisher_db  = @publisher_db AND
		publication = @publication) 
		or @reserved = 'drop_all'

		IF @@ERROR <> 0 
			GOTO UNDO

		IF NOT EXISTS (SELECT * FROM MSsubscription_properties)
		BEGIN
			exec @retcode = dbo.sp_MSsub_cleanup_prop_table
			IF @@ERROR <> 0 or @retcode <> 0
				GOTO UNDO
		END
	END

    -- Ignore errors.
    exec dbo.sp_MSsub_cleanup_orphans
	
    return (0)
            
UNDO:
	return(1)
END
GO
exec dbo.sp_MS_marksystemobject sp_subscription_cleanup 
go

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

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

create procedure sp_vupgrade_replication ( @login sysname = N'sa', @password sysname = N'', @ver_old int = 517, @force_remove tinyint = 0, @security_mode bit = 0 )
as
begin
/* 
 * Dispatcher proc for handling schema and metadata changes during setup initiated version upgrade 
 * for replication components. Any schema changes to replication system tables may require modifications 
 * here to maintain upgrade path. All modifications called in these procs are within "if exists" checks
 * making them repeatable for debugging and to support incremental upgrades (e.g. Beta 1 to Beta 2 to RTM)
 * 
 * If server is a distributor, run new instdist.sql against all distribution dbs.
 *
 * This proc gets called by setup at the end of an install over an existing version.
*/

	set nocount on 

	declare @dbname sysname
	declare @has_dbaccess bit
	declare @install_path nvarchar(255)
	declare @osql_path nvarchar(260)
	declare @osql_cmd nvarchar(512)
	declare @osql_for_nt int
	declare @retcode int
    declare @platform_nt binary
	declare @db_distbit int
	declare @ver_min 			int

	select @db_distbit = 16
    select @platform_nt = 0x1

	-- raiserror('sp_vupgrade_replication', 0,1) with nowait

	/*
	 * obsolete check; ver check was to prevent repl upgrade from
	 * versions prior to SQL7.0 Beta 3; check is removed by setting @ver_min = -1
	*/
	select @ver_min= -1 -- change if later wish to support a minimum upgrade version
	if ( @ver_old < @ver_min ) or ( @force_remove = 1 )
		exec dbo.sp_removesrvreplication
	else
	begin

		/* 
		 * always need to run instdist.sql to update distribution databases on a distributor
		 * setup must restart in non-single user mode so we can shell out to run instdist.sql scripts
		*/
		if exists( select * from master..sysdatabases where category & @db_distbit = @db_distbit )
		begin

			/* 
			** Get installation path -- osql client (TOOLS) path
			*/
			EXECUTE @retcode = master.dbo.sp_MSgettools_path @osql_path OUTPUT
			IF ( @@ERROR <> 0 ) OR ( @retcode <> 0 ) or ( @osql_path is NULL ) or ( @osql_path = '' )
			BEGIN
				RETURN (1)
			END

			/* 
			** Get installation path -- instance specific (INSTALL) directory
			*/
			exec @retcode = master.dbo.sp_MSget_setup_paths
				@sql_path = @install_path output
			IF @@ERROR<> 0 OR @retcode <> 0 or @install_path is NULL or @install_path=N''
			BEGIN
				RETURN (1)
			END

			-- Set the flag for platform
			if (( platform() & @platform_nt = @platform_nt ))
				select @osql_for_nt = 1
			else
				select @osql_for_nt = 0

			declare cur_distdb CURSOR LOCAL FAST_FORWARD for 
				select name, has_dbaccess(name) from master..sysdatabases 
					where category & @db_distbit = @db_distbit
				for read only
			
			open cur_distdb
			fetch cur_distdb into @dbname, @has_dbaccess
			while ( @@fetch_status <> -1 )
			begin

				-- if distribution database is available upgrade it; if offline error out
				if ( @has_dbaccess = 1 )
				begin
					raiserror( 21374, 0, 1, @dbname) with nowait

					/*
					 * Format osql cmd line appropriate for security mode and OS to run instdist.sql against
					 * each distribution database. Instdist.sql will recompile procs and will also do some
					 * schema and metadata upgrade of changed replication tables. Query timeout increased to
					 * make enough time for alter tables in instdist.sql run for upgrade to complete.
					*/
					if ( @osql_for_nt = 1 )
						select @osql_cmd = N'" "'
					else
						select @osql_cmd = N' "'
					
					-- Cannot specify -S w/ -E for local execution, SID does not map (nofix)
					if ( @security_mode = 1 and @osql_for_nt = 1 )
					begin
						select @osql_cmd = @osql_cmd + @osql_path + '\binn\osql" -E '
						if serverproperty('instancename') is not null
							select @osql_cmd = @osql_cmd + ' -S"' + @@SERVERNAME + '" '
					end
					else
						select @osql_cmd = @osql_cmd + @osql_path + '\binn\osql" -U' + isnull(@login, N'sa') + ' -P' + isnull(@password, N'') + ' -S"' + @@SERVERNAME + '" '

					select @osql_cmd = @osql_cmd + ' -l30 -t120 '
					select @osql_cmd = @osql_cmd + ' -b ' + ' -d' + @dbname
					select @osql_cmd = @osql_cmd +	' -i' + '"' + @install_path + '\install\instdist.sql"' + 
													' -o' + '"' + @install_path + '\install\instdist.out"'			

					if (@osql_for_nt = 1)
						select @osql_cmd = @osql_cmd + ' "'

	 				exec @retcode = master..xp_cmdshell @osql_cmd
					if @retcode <> 0 or @@error <> 0
					begin
						raiserror (14113, 16, -1, @osql_cmd, 'instdist.out')
					end

					/*
					 * Process schema and metadata changes for each distribution database
					*/

					select @dbname = quotename(@dbname)
					exec ('use '+ @dbname + ' exec dbo.sp_vupgrade_distdb')
					if @@error <> 0
						return(1)
				end
				else
				begin
					-- all distribution databases must be upgraded before continuing
					raiserror( 21378, 16, 1, @dbname) with nowait
				end
				
				fetch next from cur_distdb into @dbname, @has_dbaccess
			end
			close cur_distdb
			deallocate cur_distdb
		end

	
		-- Update subscription database schema
		exec @retcode = dbo.sp_vupgrade_subscription_databases
		if @retcode <> 0 or @@error <> 0
			return (1)


	end

	return (0)

end
go

exec dbo.sp_MS_marksystemobject sp_vupgrade_replication
go

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

raiserror('Creating procedure sp_vupgrade_distdb', 0,1)
go
create procedure sp_vupgrade_distdb 
as
begin
/* 
 * Process schema and metadata changes specific to a distribution database. Some schema modifications
 * may have already been made by the apply of instdist.sql against the distribution database. Both steps
 * are needed for a complete distribution database upgrade.
 *
 * Setup version upgrade procedure call order:
 *	sp_vupgrade_replication -> sp_vupgrade_distdb
*/

	set nocount on

	declare @table_name sysname
	declare @retcode integer

	-- raiserror('sp_vupgrade_publisher', 0,1) with nowait
	/*
	 * MSdistribution_agents
	*/
	if exists (select name from sysobjects where name='MSdistribution_agents')
	begin
		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
	end

end
go

exec dbo.sp_MS_marksystemobject sp_vupgrade_distdb
go


--------------------------------------------------------------------------------
--. sp_vupgrade_subscription_databases 
--------------------------------------------------------------------------------
if exists (select * from sysobjects 
		where name = 'sp_vupgrade_subscription_databases' 
				and type = 'P')
	  drop procedure sp_vupgrade_subscription_databases
go
raiserror('Creating procedure sp_vupgrade_subscription_databases', 0,1)
GO
create procedure sp_vupgrade_subscription_databases
as
begin
/* 
 * Process schema and metadata changes common to all databases. This proc loops
 * through each database and upgrades MSsubscription_properties, transactional tables
 * and merge tables.
 *
 * Setup version upgrade procedure call order:
 *	sp_vupgrade_replication -> sp_vupgrade_subscription_databases
*/
	set nocount on

	-- raiserror('sp_vupgrade_subscription_databases', 0,1) with nowait
	declare @dbname nvarchar(270), @has_dbaccess bit

	declare current_db CURSOR LOCAL FAST_FORWARD for 
		select N'[' + replace(name, N']', N']]') + N']', has_dbaccess(name) from master..sysdatabases 
			WHERE name <> N'master' collate database_default
			AND name <> N'tempdb' collate database_default
			AND name <> N'msdb' collate database_default
		for read only

	-- Note: dbname is quoted!
	open current_db
	fetch current_db into @dbname, @has_dbaccess
	while ( @@fetch_status <> -1 )
	begin

		-- upgrade repl tables in sub dbs if needed - sub dbs are not marked with subscribed status 
		-- skip any database in an offline state and write warning to upgrade log
		if ( @has_dbaccess = 1 )
		begin
			--
			-- NOTE : there are several things to process here
			-- for each upgrade - all these steps may NOT be necessary
			-- and should be commented/uncommented out as required for each upgrade
			-- 
			-- Current setting : SQL 2000 SP1 upgrade
			--
			raiserror( 21377, 0, 1, @dbname) with nowait
			-- exec ('use '+ @dbname + ' exec dbo.sp_vupgrade_MSsubscription_properties')
			exec ('use '+ @dbname + ' exec dbo.sp_vupgrade_subscription_tables')
			exec ('use '+ @dbname + ' exec dbo.sp_vupgrade_mergetables')
			-- exec ('use '+ @dbname + ' exec dbo.sp_vupgrade_subpass')
		end
		else
		begin
			raiserror( 21373, 11, 1, @dbname) with nowait
		end

		fetch next from current_db into @dbname, @has_dbaccess
	end
	close current_db
	deallocate current_db
end
go


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

raiserror('Creating procedure sp_vupgrade_mergetables', 0,1)
GO
create procedure sp_vupgrade_mergetables( @skip_procgen bit = 0 )
as
begin
/* 
 * Process schema and metadata changes common to transactional pub/sub databases.
 *
 * @skip_procgen is set by sp_restoredbreplication when calling this proc directly to update
 * system tables during restore of a down-level (e.g. - SQL7.0) database to current version
 * 
 * Setup version upgrade procedure call order:
 *	sp_vupgrade_replication -> sp_vupgrade_subscription_databases -> sp_vupgrade_mergetables
*/

	set nocount on 

	declare @artnick int
	declare @objid  int
	declare @col_track int
	declare @article sysname
	declare @pubname sysname
	declare @artid uniqueidentifier
	declare @pubid uniqueidentifier
    declare @qualified_name         nvarchar(257)
    declare @source_owner           sysname
    declare @source_object			sysname
	declare @table_name				sysname
	declare @retcode				integer
	declare @snapshot_ready			int
	declare @cmd 					nvarchar(4000)

	-- raiserror('sp_vupgrade_mergetables', 0,1)

	/*
	 * sysmergearticles
	*/
	if (exists (select * from sysobjects where name = 'sysmergearticles'))
	begin

        -- Set all invalid sysmergearticles.sync_objid to the corresponding 
        -- objid, this will allow regeneration of article procs to succeed
        update dbo.sysmergearticles 
           set sync_objid = objid 
         where object_name(sync_objid) is null

        if @@ERROR<>0
            return(1)

		exec @retcode = dbo.sp_MSUpgradeConflictTable @skip_procgen
		if @@ERROR<>0 or @retcode<>0
			return (1)

		if not exists (select * from syscolumns where id = object_id('sysmergearticles') and
						name = 'maxversion_at_cleanup')
		begin
			alter table sysmergearticles add maxversion_at_cleanup int NOT NULL default 1
			if @@ERROR <> 0 return 1
		end

		-- create view now that sysmergearticles is altered and sysmergeextendedarticles is created
		if exists (select * from sysobjects where name='sysmergeextendedarticlesview')
		begin
            drop view dbo.sysmergeextendedarticlesview
		end    

		-- cannot create view directly in proc
		exec ('create view dbo.sysmergeextendedarticlesview
		    	   as
               select name, type, objid, sync_objid, view_type, artid, description, pre_creation_command, pubid,
			   nickname, column_tracking, status, conflict_table, creation_script, conflict_script, article_resolver,
			   ins_conflict_proc, insert_proc, update_proc, select_proc, schema_option, destination_object,
			   resolver_clsid, subset_filterclause, missing_col_count, missing_cols, columns, resolver_info,
			   view_sel_proc, gen_cur, excluded_cols, excluded_col_count, vertical_partition, identity_support,
			   destination_owner, before_image_objid, before_view_objid, verify_resolver_signature, 
			   allow_interactive_resolver, fast_multicol_updateproc, check_permissions, maxversion_at_cleanup				   
			   from sysmergearticles
	           union all
               select name, type, objid, NULL, NULL, artid, description, pre_creation_command, pubid, 
			   NULL, NULL, status, NULL, creation_script, NULL, NULL, 
			   NULL, NULL, NULL, NULL, schema_option, destination_object, 
			   NULL, NULL, NULL, NULL, NULL, NULL, 
			   NULL, NULL, NULL, NULL, NULL, NULL, 
			   destination_owner, NULL, NULL, NULL, 
			   0, 0, 0, NULL 
			   from sysmergeschemaarticles
			   go')

	    exec dbo.sp_MS_marksystemobject sysmergeextendedarticlesview

		-- Do not regenerate views, procs if this is called from sp_restoredbreplication. Restore only
		-- needs to update schema, then it can call existing system procs to remove db replication cleanly
		if @skip_procgen = 0
		begin
			select @artnick = min(a.nickname) from sysmergearticles a inner join sysmergepublications p on p.pubid = a.pubid where p.snapshot_ready =1
			while @artnick is not null
			begin
				-- find base table to compute number of columns
				select @objid = objid, @col_track = column_tracking
					from sysmergearticles where nickname = @artnick

				-- regenerate the triggers
				select @source_owner = user_name(uid), @source_object = name from sysobjects where id = @objid
				select @qualified_name = QUOTENAME(@source_owner) + '.' + QUOTENAME(@source_object)
				exec dbo.sp_MSaddmergetriggers @qualified_name, NULL, @col_track

				/* Loop through all articles that this table is involved in and regenerate the article procs */
	            declare hcArtCursor CURSOR LOCAL FAST_FORWARD FOR select artid, pubid from sysmergearticles where nickname = @artnick order by artid, pubid
	            
    	        OPEN hcArtCursor
        	    FETCH hcArtCursor INTO @artid, @pubid
                WHILE (@@fetch_status <> -1)
                    BEGIN
                        select @pubname = name, @snapshot_ready = snapshot_ready from sysmergepublications where pubid = @pubid
                        -- regenerate procs, triggers, and views only for articles with snapshot ready
                        if @snapshot_ready>0
                            begin
                            	declare @rgcol nvarchar(270)
								declare @indname nvarchar(270)
								declare @quotedname nvarchar(270)
								declare @conflict_table sysname
								declare @conflict_table_id int
								declare @owner sysname
                                select @article = name, @conflict_table=conflict_table, @conflict_table_id=object_id(conflict_table)  
                                	from sysmergearticles where artid = @artid and pubid = @pubid

								--make sure conflict table has already got the indexes needed for performance enhancement
								--if not there we will add it up
 								if ( @conflict_table_id is not null) and not exists 
 									(select * from sysindexes where id = @conflict_table_id and keys is not null)
 								begin
							        select @owner=user_name(uid) from sysobjects where id= @conflict_table_id
									select @rgcol = QUOTENAME(name) from syscolumns 
										where id = @objid and ColumnProperty(id, name, 'isrowguidcol') = 1
							        select @indname = 'uc_' + @conflict_table
							        if len(@indname) > 128
        							begin
							            select @indname = substring(@indname,1,92) + convert(nvarchar(36), newid())
        							end
						        	set @indname = QUOTENAME(@indname)
						        	set @quotedname = QUOTENAME(@owner) + '.' + QUOTENAME(@conflict_table)
						        	exec ('Create unique clustered index ' + @indname + ' on ' + @quotedname +
        								' (' + @rgcol + ', origin_datasource)' )
	        						if @@error <> 0
    	    							return (1)
								end
                                
                                -- remake the articles procs
                                exec @retcode = dbo.sp_MSsetartprocs @publication = @pubname,   @article = @article, @force_flag = 1
                                if @@ERROR <>0 OR @retcode <>0 
                                    return (1)
                            END
                        FETCH hcArtCursor INTO @artid, @pubid
                    END                         
                CLOSE hcArtCursor
                DEALLOCATE hcArtCursor

				-- we no longer try to delete metadata rows that might have truncated colv1
				--  deleting can cause non-convergence problems where they previously didn't
				--  exist, so we will try to patch up any truncated colv1 values in the merge agent.
				
				-- find next article
				select @artnick = min(a.nickname) from sysmergearticles a inner join sysmergepublications p on p.pubid = a.pubid where p.snapshot_ready > 0 and a.nickname > @artnick
			end -- end colv metadata fixup, article proc and trigger re-gen

			-- Loop over publications and recreate the views, skipping publications where snapshot is not ready
			select @pubname = min(name) from sysmergepublications where UPPER(publisher)=UPPER(@@SERVERNAME) and publisher_db=db_name() and snapshot_ready > 0
			while @pubname is not null
			begin
				-- remake the publication views
				exec dbo.sp_MSpublicationview @pubname, 1
				select @pubname = min(name) from sysmergepublications where name > @pubname and UPPER(publisher)=UPPER(@@SERVERNAME) and publisher_db=db_name() and snapshot_ready > 0

			end
		end -- end @skip_procgen
	end -- end sysmergearticles modifications


	SELECT @table_name = N'MSmerge_tombstone'
	IF EXISTS ( SELECT * FROM sysobjects WHERE name = 'MSmerge_tombstone' )
	BEGIN
		IF EXISTS (SELECT * FROM sysindexes WHERE name = 'unc3MSmerge_tombstone' AND id = OBJECT_ID('MSmerge_tombstone'))
			drop index MSmerge_tombstone.unc3MSmerge_tombstone
		
	END

	SELECT @table_name = N'MSmerge_contents'
	IF EXISTS ( SELECT * FROM sysobjects WHERE name = 'MSmerge_contents' )
	BEGIN
		IF EXISTS (SELECT * FROM sysindexes WHERE name = 'nc2MSmerge_contents' AND id = OBJECT_ID('MSmerge_contents'))
			drop index MSmerge_contents.nc2MSmerge_contents

		IF EXISTS (SELECT * FROM sysindexes WHERE name = 'nc3MSmerge_contents' AND id = OBJECT_ID('MSmerge_contents'))
			drop index MSmerge_contents.nc3MSmerge_contents

		IF EXISTS (SELECT * FROM sysindexes WHERE name = 'nc4MSmerge_contents' AND id = OBJECT_ID('MSmerge_contents'))
			drop index MSmerge_contents.nc4MSmerge_contents

		IF EXISTS (SELECT * FROM sysindexes WHERE name = 'unc3SycContents' AND id = OBJECT_ID('MSmerge_contents'))
			drop index MSmerge_contents.unc3SycContents
		
		create index nc2MSmerge_contents on MSmerge_contents(generation)
		if @@ERROR <> 0 return 1

		create index nc3MSmerge_contents on MSmerge_contents(partchangegen)
		if @@ERROR <> 0 return 1

		create index nc4MSmerge_contents on MSmerge_contents(rowguid)
		if @@ERROR <> 0 return 1
	END

	-- before image tables
	declare @binames table (biname sysname)
	insert into @binames select name from sysobjects where xtype='U' and name like 'MS_bi%'
	declare @biname sysname
	set @biname= (select top 1 biname from @binames)
	while @biname is not null
	begin
		set @cmd= 'drop index ' + @biname + '.' + @biname + '_gen'
		exec dbo.sp_executesql @cmd
		set @cmd= 'create clustered index ' + @biname + '_gen on ' + @biname + '(generation)'
		exec dbo.sp_executesql @cmd
		delete from @binames where biname=@biname
		set @biname= (select top 1 biname from @binames)
	end

end

GO
exec sp_MS_marksystemobject 'sp_vupgrade_mergetables'
go

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

-- This proc is invoked on subscriber.
raiserror('Creating procedure sp_MSreset_queue', 0,1)
GO
CREATE PROCEDURE sp_MSreset_queue (
    @publisher      sysname,                    -- publishing server name
    @publisher_db   sysname,                    -- publishing database name. 
    @publication	sysname,    			    -- publication name,
	@artid 		int)
as
begin
	declare @subserver sysname
			,@subdbname sysname
			,@queue_id  sysname
			,@update_mode int
			,@retcode smallint
			,@vbartid varbinary(20)
			,@queue_server sysname

	set nocount on

	select 	@subserver = @@servername, 
			@subdbname = db_name(),
			@update_mode = update_mode, 
			@queue_id = queue_id,
			@queue_server = queue_server
	from MSsubscription_agents
		where UPPER(publisher) = UPPER(@publisher)
			and publisher_db =  @publisher_db
			and publication = @publication

	if (@update_mode in (2,3))
	begin
		--
		-- MSMQ processing
		-- prefix the queue_id with queue server in direct format
		-- and then perform the queue reset
		--						
		select @queue_id = N'DIRECT=OS:' + @queue_server + N'\PRIVATE$\' + @queue_id
		
		begin distributed tran
		exec @retcode = master.dbo.xp_resetqueue @queue_id, @subserver, 
							@subdbname, @publication, @artid
		if (@retcode != 0 or @@error != 0)
		begin
			if (@@trancount > 0)
				rollback tran
			return (1)	
		end
	end
	else if (@update_mode in (4,5))
	begin
		begin tran
		
		select @retcode = 0
		if (exists (select * from sysobjects 
			where name = 'MSreplication_queue'))
		begin
			--
			-- Strictly speaking we do no need
			-- to delete but, makes it easy for
			-- the queue reader agent
			-- Do not delete any reset messages
			--
			delete dbo.MSreplication_queue
				where publisher = UPPER(@publisher)
				and publisher_db =  @publisher_db
				and publication = @publication
				and tranid != N'sub-reset'
		end
		else
		begin
			--
			-- first queue subscription is being initialized
			-- create queue if necessary
			--
			exec @retcode = sp_MScreate_sub_tables
				@tran_sub_table = 0,
				@property_table = 0,
				@sqlqueue_table = 1
		end

		--
		-- for subscription reinitialization we
		-- need to insert a RESYNC command message
		--
		select @vbartid = cast(@artid as varbinary(20))
		insert into dbo.MSreplication_queue (publisher, publisher_db,
			publication,tranid, commandtype, data, datalen)
		values (UPPER(@publisher), @publisher_db, 
			@publication, N'sub-reset', 2, @vbartid, datalength(@vbartid))

		if ((@@error != 0) or (@retcode != 0))
		begin
			if (@@trancount > 0)
				rollback tran
			return (1)	
		end
	end

	commit tran
	return 0
end            
GO
exec dbo.sp_MS_marksystemobject sp_MSreset_queue
go
grant execute on dbo.sp_MSreset_queue to public
go

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

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

-- This proc is called by distribution agent.
CREATE PROCEDURE sp_MSset_subscription_properties (
    @publisher      sysname,                    -- publishing server name
    @publisher_db   sysname,                    -- publishing database name. If NULL then same as current db
    @publication	sysname,    			    -- publication name,
    @subscription_type int,
	@allow_subscription_copy bit,
	@queue_id sysname,
	@update_mode int,
	@attach_version binary(16),
	@queue_server sysname = NULL
)
AS
BEGIN
    set nocount on
	declare @retcode int

	--  Security Check
    EXEC @retcode = dbo.sp_MSreplcheck_subscribe
    IF @@ERROR <> 0 or @retcode <> 0
		RETURN(1)
	
    -- For non independent agent publications
	if @publication is null or @publication = ''
        set @publication = 'ALL'

	if @queue_server = N''
		select @queue_server = NULL
		
	if @queue_id = N''
		select @queue_id = NULL

	update MSsubscription_agents set      
        allow_subscription_copy = @allow_subscription_copy,
		update_mode = @update_mode,
		queue_id = @queue_id,
		queue_server = @queue_server,
		attach_version = @attach_version
		where UPPER(publisher) = UPPER(@publisher)
			and publisher_db =  @publisher_db
			and publication = @publication
            and subscription_type = @subscription_type

	--
	-- for queued pull subscriptions 
	-- update column update_mode in MSreplication_subscriptions
	-- as we never know the right update_mode until initialization
	--
	if ((@subscription_type = 1) and (@queue_id IS NOT NULL) and
		exists (select * from MSreplication_subscriptions 
			WHERE UPPER(publisher) = UPPER(@publisher) AND
				publisher_db  = @publisher_db AND
				publication = @publication AND
				subscription_type = @subscription_type))
	begin
		update MSreplication_subscriptions 
		set update_mode = @update_mode
		WHERE UPPER(publisher) = UPPER(@publisher) AND
			publisher_db  = @publisher_db AND
			publication = @publication AND
			subscription_type = @subscription_type
	end
END
go

exec dbo.sp_MS_marksystemobject sp_MSset_subscription_properties
go
grant execute on dbo.sp_MSset_subscription_properties to public
go


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

-- This proc is called by distribution agent.
CREATE PROCEDURE sp_MSset_sub_guid (
    @publisher      sysname,                    -- publishing server name
    @publisher_db   sysname,                    -- publishing database name. If NULL then same as current db
    @publication	sysname,    			    -- publication name,
    @subscription_type int,
	@subscription_guid binary(16),
	@queue_id		sysname,
	@queue_server	sysname = NULL
)
AS
BEGIN
    set nocount on
	declare @retcode int
	declare @independent_agent bit

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

    -- For non independent agent publications
	if @publication is null or @publication = ''
	begin
        set @publication = 'ALL'
		set @independent_agent = 0
	end
	else
		set @independent_agent = 1

	if @queue_id = N''
		select @queue_id = NULL

	if @queue_server = N''
		select @queue_server = NULL

	update MSsubscription_agents set      
        queue_id = @queue_id
        ,queue_server = @queue_server
		where UPPER(publisher) = UPPER(@publisher)
			and publisher_db =  @publisher_db
			and publication = @publication
            and subscription_type = @subscription_type

	update MSreplication_subscriptions set      
        [time] = getdate(),
		subscription_guid = @subscription_guid
		where UPPER(publisher) = UPPER(@publisher)
			and publisher_db =  @publisher_db
			and (@independent_agent = 0 or publication = @publication)
            and subscription_type = @subscription_type
			and independent_agent = @independent_agent
END
go

exec dbo.sp_MS_marksystemobject sp_MSset_sub_guid
go
grant execute on dbo.sp_MSset_sub_guid to public
go

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

CREATE PROCEDURE sp_MScreate_sub_tables (
@tran_sub_table bit = 0,
@property_table bit = 1,
@sqlqueue_table bit = 0
)
as
BEGIN
	set nocount on
	declare @retcode int

	IF @tran_sub_table = 1 and
		(NOT EXISTS (SELECT * FROM sysobjects WHERE 
		type = 'U' AND name = 'MSreplication_subscriptions')) 
	BEGIN
