r_db
	end

	-- for tran pub, max_identity is null
	if @@ERROR<>0 OR exists (select * from MSrepl_identity_range where ABS(next_seed)>ABS(max_identity))
	begin
		raiserror(21195, 16, -1)
		goto UNDO
	end

	select @next_seed = next_seed, @range = range, @threshold = threshold from MSrepl_identity_range
		where tablename=@tablename and LOWER(publisher)=LOWER(@publisher) and publisher_db=@publisher_db
	
	commit tran
	return 0

UNDO:
    if @@TRANCOUNT > 0
    begin
        ROLLBACK TRAN sp_MSfetchAdjustidentityrange
        COMMIT TRAN
    end
    return(1)


')
    if @@error <> 0 return 1
    exec('exec dbo.sp_MS_marksystemobject sp_MSfetchAdjustidentityrange 

')
    if @@error <> 0 return 1
    exec('grant exec on dbo.sp_MSfetchAdjustidentityrange to public

')
    if @@error <> 0 return 1
    exec('
raiserror(15339,-1,-1,''sp_MSreinit_subscription'')

')
    if @@error <> 0 return 1
    exec('CREATE PROCEDURE sp_MSreinit_subscription
@publisher_name sysname,
@publisher_db sysname,
@publication sysname = ''all'',
@subscriber_name    sysname = ''all'',
@subscriber_db sysname = ''all''
as
    set nocount on
    declare @proc nvarchar(2048)
    declare @retcode int

    if @publisher_name IS NULL or NOT EXISTS (select * from master..sysservers as ss, MSpublications as msp
        where lower(ss.srvname) = lower(@publisher_name) and msp.publisher_id = ss.srvid)
    begin
        return (1)
    end

    if @publisher_db IS NULL
        select @publisher_db = ''''

    select @proc = @publisher_name + ''.'' + @publisher_db + ''.dbo.sp_reinitsubscription ''
    exec @retcode = @proc @publication, ''all'', @subscriber_name, @subscriber_db

    return (@retcode)

')
    if @@error <> 0 return 1
    exec('
   -- View for delivered and undelivered commands.   RHS  6-4-98
   -- Since the view is likely to change, just recreate it each time.
   IF EXISTS (SELECT * FROM sysobjects where name=''MSdistribution_status'' and type=''V'')
    DROP VIEW dbo.MSdistribution_status

    /****************************************************************************/
    raiserror(''Creating view MSdistribution_status'', 0,1)
        /****************************************************************************/    

')
    if @@error <> 0 return 1
    exec('
CREATE VIEW MSdistribution_status (article_id,agent_id,UndelivCmdsInDistDB,DelivCmdsInDistDB)
as
-- Note that this view does not account for (i.e. exclude from counts) commands that do not need to be delivered 
-- because of loopback or syncronous updating subscribers, nor subscriptions never activated.
-- It also may not be exact due to use of NOLOCK - so that it does not cause blocking or deadlock issues.
SELECT t.article_id,s.agent_id,
''UndelivCmdsInDistDB''=SUM(CASE WHEN xact_seqno > h.maxseq THEN 1 ELSE 0 END),
''DelivCmdsInDistDB''=SUM(CASE WHEN xact_seqno <=  h.maxseq THEN 1 ELSE 0 END)
FROM	(SELECT	article_id,publisher_database_id, xact_seqno
	FROM MSrepl_commands (NOLOCK) ) as t
JOIN (SELECT agent_id,article_id,publisher_database_id FROM MSsubscriptions (NOLOCK) ) AS s 
ON (t.article_id = s.article_id AND t.publisher_database_id=s.publisher_database_id )
JOIN (SELECT agent_id,''maxseq''= isnull(max(xact_seqno),0x0) FROM MSdistribution_history (NOLOCK) GROUP BY agent_id) as h
ON (h.agent_id=s.agent_id)
GROUP BY t.article_id,s.agent_id

')
    if @@error <> 0 return 1
    exec('    
EXEC dbo.sp_MS_marksystemobject ''MSdistribution_status''
-- As this view can add considerable overhead when queried, it intentionally is not granted public access by default.
-- A site may so grant it if it wants to of course>
GRANT SELECT ON MSdistribution_status to dbo

')
    if @@error <> 0 return 1
    exec('

raiserror(15339,-1,-1,''sp_browsereplcmds'')

')
    if @@error <> 0 return 1
    exec('
create procedure sp_browsereplcmds 
@xact_seqno_start nchar(22) = NULL, 
@xact_seqno_end nchar(22) = NULL,
@originator_id int = NULL,
@publisher_database_id int = NULL,
@article_id int = NULL,
@command_id int = NULL,
@results_table sysname = NULL
as
declare @query nvarchar( 4000 )
declare @dbname sysname

if( @command_id is not null )
begin
    if( @xact_seqno_start is null or @publisher_database_id is null )
    begin
        raiserror( 21110, 16, -1 )
        return 1
    end
    else if @xact_seqno_start != @xact_seqno_end 
    begin
        raiserror( 21109, 16, -1 )
        return 1
    end
end

if @results_table is not null
begin
	select @query = N''create table '' + @results_table + N''(''
	select @query = @query + N''xact_seqno varbinary(16) null,''
	select @query = @query + N''originator_id int null,''
	select @query = @query + N''publisher_database_id int null,''
	select @query = @query + N''article_id int null,''
	select @query = @query + N''type int null,''
	select @query = @query + N''command nvarchar( 1024 ) null )''

	exec ( @query )
	if @@error <> 0
	begin
		return 1
	end
end

if @xact_seqno_start is null
begin
    select @xact_seqno_start = N''0x00000000000000000000''
end
if @xact_seqno_end is null
begin
    select @xact_seqno_end = N''0xFFFFFFFFFFFFFFFFFFFF''
end

select @query = N''select xact_seqno, convert( int, originator_id ), convert( int, publisher_database_id ), convert(int, article_id), convert( int, type ), convert( int, partial_command ), command from MSrepl_commands ''

if @command_id is not null
begin
    select @query = @query + N''where xact_seqno = '' + @xact_seqno_start  
end
else
begin
    select @query = @query + N''where xact_seqno >= '' + @xact_seqno_start + N'' and xact_seqno <= '' + @xact_seqno_end 
end

if @originator_id is not null
begin
    select @query = @query + N'' and originator_id = '' + convert( nvarchar, @originator_id )
end

if @publisher_database_id is not null
begin
    select @query = @query + N'' and publisher_database_id = '' + convert( nvarchar, @publisher_database_id )
end

if @article_id is not null
begin
    select @query = @query + N'' and article_id = '' + convert( nvarchar, @article_id )
end

if @command_id is not null
begin
	-- No need to use article_id and originator_id
    select @query = @query + N'' and command_id >= '' + convert( nvarchar, @command_id )
    select @query = @query + N'' and command_id <= ( select min( command_id ) from MSrepl_commands c ''
    select @query = @query + N'' where c.xact_seqno = '' + @xact_seqno_start  
    select @query = @query + N'' and c.publisher_database_id = '' + convert( nvarchar, @publisher_database_id )
    select @query = @query + N'' and c.command_id >= '' + convert( nvarchar, @command_id )
    select @query = @query + N'' and c.partial_command = 0 )''  
end

select @query = @query + N'' order by originator_id, publisher_database_id, xact_seqno, article_id, command_id asc''

select @dbname = db_name()

if @results_table is not null
begin
	declare @q2 nvarchar(4000)
	select @query = replace( @query, '''''''', '''''''''''' )
	select @q2 = N''insert into '' + @results_table + N'' exec master..xp_printstatements N''''''+ @query + N'''''',N'''''' + @dbname  +''''''''
	exec (@q2)
end
else
begin
	exec master..xp_printstatements @query, @dbname
end

')
    if @@error <> 0 return 1
    exec('


raiserror(15339,-1,-1,''sp_dumpparamcmd'')

')
    if @@error <> 0 return 1
    exec('
create procedure sp_dumpparamcmd
@originator_id int,
@publisher_database_id int,
@article_id int,
@xact_seqno nchar(22)
as
declare @query nvarchar(2048)
declare @dbname sysname

select @query = N''select convert(int, partial_command) , command from MSrepl_commands ''
select @query = @query + ''where type = 30 and xact_seqno = '' + @xact_seqno
select @query = @query + N'' and originator_id = '' + convert( nvarchar, @originator_id )
select @query = @query + N'' and publisher_database_id = '' + convert( nvarchar, @publisher_database_id )
select @query = @query + N'' and article_id = '' + convert( nvarchar, @article_id )
select @query = @query + N'' order by command_id asc''
select @dbname = db_name()

exec master..xp_displayparamstmt @query, @dbname, 1

')
    if @@error <> 0 return 1
    exec('
raiserror(15339,-1,-1,''sp_MSbrowsesnapshotfolder'')

')
    if @@error <> 0 return 1
    exec('
create procedure sp_MSbrowsesnapshotfolder (
    @publisher     sysname,
    @publisher_db  sysname,
    @article_id    int,
    @subscriber    sysname = NULL,
    @subscriber_db sysname = NULL
    )
AS
    SET NOCOUNT ON


    DECLARE @alt_directory_type     INT
    DECLARE @directory_type         INT
    DECLARE @publisher_id           INT
    DECLARE @publisher_database_id  INT
    DECLARE @subscriber_id          INT
    DECLARE @snapshot_bit           INT
    DECLARE @snapshot_mask          INT
    DECLARE @xact_seqno             VARBINARY(16)
    DECLARE @sync_init              INT
    DECLARE @sync_done              INT

    SELECT @alt_directory_type = 25
    SELECT @directory_type = 7
    SELECT @subscriber_id = NULL
    SELECT @snapshot_bit = 0x80000000
    SELECT @snapshot_mask = ~@snapshot_bit
    SELECT @xact_seqno = NULL
    SELECT @sync_init = 37
    SELECT @sync_done = 38

    SELECT @publisher_id = srvid 
      FROM master.dbo.sysservers 
     WHERE UPPER(@publisher) = UPPER(srvname)

    SELECT @publisher_database_id = publisher_database_id 
      FROM MSsubscriptions
     WHERE publisher_id = @publisher_id
       AND publisher_db = @publisher_db
       AND article_id = @article_id  


    -- @subscriber is null implies @subscriber_db is null also because 
    -- this sp can only be called by sp_browsesnapshotfolder
    IF @subscriber IS NULL
    BEGIN
        SELECT @xact_seqno = MAX(xact_seqno) 
          FROM MSrepl_commands
         WHERE publisher_database_id = @publisher_database_id
           AND article_id = @article_id
           AND (type & @snapshot_bit) <> 0            
           AND (type & @snapshot_mask) <> @sync_init
           AND (type & @snapshot_mask) <> @sync_done
    END
    ELSE 
    BEGIN
        -- Both @subscriber and @subscriber_db are non-null
        SELECT @subscriber_id = srvid 
          FROM master.dbo.sysservers
         WHERE UPPER(@subscriber) = UPPER(srvname)
        
        IF @subscriber_id IS NULL
        BEGIN
            RAISERROR(21150, 16, -1)  
            RETURN 1
        END
        
        SELECT @xact_seqno = subscription_seqno 
          FROM MSsubscriptions
         WHERE publisher_database_id = @publisher_database_id
           AND publisher_id = @publisher_id 
           AND article_id = @article_id
           AND subscriber_id = @subscriber_id
           AND subscriber_db = @subscriber_db

        IF @xact_seqno IS NULL
        BEGIN
            RAISERROR(14055, 16, -1)
            RETURN 1
        END                  
    END

    SELECT ''snapshot_folder'' = CONVERT(NVARCHAR(255), command) 
      FROM MSrepl_commands 
     WHERE publisher_database_id = @publisher_database_id
       AND (type & @snapshot_bit) <> 0
       AND xact_seqno = @xact_seqno
       AND (((type & ~@snapshot_bit) = @alt_directory_type) OR
            ((type & ~@snapshot_bit) = @directory_type))
  ORDER BY command_id ASC
    IF @@ERROR <> 0
    BEGIN
        RETURN 1
    END
    ELSE
    BEGIN
        RETURN 0
    END

')
    if @@error <> 0 return 1
    exec('
raiserror(15339,-1,-1,''sp_MSchange_subscription_dts_info'')

')
    if @@error <> 0 return 1
    exec('
CREATE PROCEDURE sp_MSdist_adjust_identity
	@agent_id	int,
	@tablename	sysname
as	
	declare @publisher sysname
	declare @publisher_db sysname
	declare @publisher_id smallint
	declare @publication sysname
	declare @next_seed bigint
	declare @range bigint
	declare @threshold int
	declare @retcode int

	-- Security check.
    exec @retcode = dbo.sp_MScheck_pull_access
        @agent_id = @agent_id,
        @agent_type = 0 -- distribution agent
    if @@error <> 0 or @retcode <> 0
        return (1)

	select @publication = publication, @publisher_id = publisher_id,
		@publisher_db = publisher_db from MSdistribution_agents where
		id = @agent_id

	select @publisher = srvname from master..sysservers where 
		srvid = @publisher_id
	-- Get the publication id.
	-- Note: auto identity range is only supported for queued tran pub
	-- which implies independent agent.
	declare @publication_id int
	select @publication_id = publication_id from MSpublications	where
		publication =  @publication and
		publisher_id = @publisher_id and
		publisher_db = @publisher_db

	-- Get the source object name
	declare @src_object sysname
	select @src_object = source_object from MSarticles where
		publication_id = @publication_id and
		destination_object = @tablename

	if @src_object is not null
	begin
		exec @retcode = dbo.sp_MSfetchAdjustidentityrange
			@publisher = @publisher,
			@publisher_db = @publisher_db,
			@tablename = @src_object,
			@adjust_only = 1,
			@for_publisher = 0,
			@next_seed = @next_seed output,
			@range = @range output,
			@threshold = @threshold output
		if @@error <> 0 or @retcode <> 0
			return 1
	end

	-- Return nothing if the table is not found in the table collection served by
	-- the distribution agent.
	select ''next_seed'' = @next_seed, ''range'' = @range, ''threshold'' = @threshold
		where @next_seed is not null 

')
    if @@error <> 0 return 1
    exec('
raiserror(15339,-1,-1,''sp_MSdist_adjust_identity'')

')
    if @@error <> 0 return 1
    exec('
CREATE PROCEDURE sp_MSchange_subscription_dts_info (
	@job_id varbinary(16),
    @dts_package_name sysname,
	@dts_package_password nvarchar(524),
	@dts_package_location int,
	@change_password bit

    ) AS

    /*
    ** Declarations.
    */
	declare @subscriber sysname
	declare @publisher sysname
	declare @subscriber_id smallint
	declare @publisher_id smallint

    /*
    ** Initializations.
    */
    SET NOCOUNT ON
    
	select @subscriber_id = subscriber_id, @publisher_id = publisher_id 
		from MSdistribution_agents where 
		job_id = @job_id
	
	select @subscriber = srvname from master..sysservers where srvid = @subscriber_id
	select @publisher = srvname from master..sysservers where srvid = @publisher_id

	declare @oledb_subscriber tinyint
	select @oledb_subscriber = 3
	
	-- Only SQL Server and OLEDB subscriber support dts
	if not exists (select * from MSsubscriber_info where 
		UPPER(publisher) = UPPER(@publisher) and 
		UPPER(subscriber) = UPPER(@subscriber) and
		(type = 0 or type = @oledb_subscriber))
	begin
		raiserror(21170, 16, -1)
		return 1
	end
	
	
	update MSdistribution_agents set
		dts_package_name = case 
			when @dts_package_name is null then dts_package_name
			when @dts_package_name = N'''' then null
			else @dts_package_name
			end, 
		dts_package_password = case @change_password
			when 0 then dts_package_password
			else @dts_package_password
			end, 
		dts_package_location = case
			when @dts_package_location is null then dts_package_location
			else @dts_package_location
			end
		where
			job_id = @job_id

    RETURN (0)

')
    if @@error <> 0 return 1
    exec('
raiserror(15339,-1,-1,''sp_MSrefresh_anonymous'')

')
    if @@error <> 0 return 1
    exec('CREATE PROCEDURE sp_MSrefresh_anonymous (
@publication			sysname,
@publisher				sysname,
@publisher_db			sysname
)AS

declare @retcode 		int
declare @publisher_id	int
declare @success		int
declare @agent_id		int

SELECT @publisher_id = NULL 
SELECT @publisher_id = srvid FROM master..sysservers WHERE UPPER(@publisher) = UPPER(srvname)  
IF @publisher_id IS NULL
    BEGIN
        RAISERROR(21169, 16, -1, @publisher, @@SERVERNAME, @publisher)    
        return (1)
    END
select @success = 2 --agent history status

select @agent_id = id from MSmerge_agents where 
	anonymous_subid is not NULL and --touch anonymous only
	publication like @publication and 
	publisher_id=@publisher_id and 
	publisher_db=@publisher_db

set rowcount 1
update MSmerge_history set time=getdate() where agent_id=@agent_id and runstatus=@success
if @@ERROR<>0
	return (1)

')
    if @@error <> 0 return 1
    exec('
raiserror(15339,-1,-1,''sp_MScheck_pub_identity'')

')
    if @@error <> 0 return 1
    exec('CREATE PROCEDURE sp_MScheck_pub_identity (
@publisher			sysname,
@publisher_db		sysname,
@tablename			sysname, 
@current_max		bigint  = NULL OUTPUT,
@pub_range			bigint  = NULL OUTPUT,
@threshold			int  = NULL OUTPUT,
@range				bigint  = NULL OUTPUT,
@next_seed			bigint  = NULL OUTPUT,
@max_identity		bigint = NULL OUTPUT
)AS

	declare @retcode		int

	if @current_max is not NULL
	begin
		select @current_max=current_max from MSrepl_identity_range 
			where tablename=@tablename and LOWER(publisher)=LOWER(@publisher) and publisher_db=@publisher_db
		if @@ERROR<>0	
			return (1)
	end

	if @max_identity is not NULL
	begin
		select @max_identity=max_identity from MSrepl_identity_range 
			where tablename=@tablename and LOWER(publisher)=LOWER(@publisher) and publisher_db=@publisher_db
		if @@ERROR<>0	
			return (1)
	end

	if @pub_range is not NULL
	begin
		select @pub_range=pub_range from MSrepl_identity_range 
			where tablename=@tablename and LOWER(publisher)=LOWER(@publisher) and publisher_db=@publisher_db
		if @@ERROR<>0	
			return (1)
	end

	if @threshold is not NULL
	begin
		select @threshold=threshold from MSrepl_identity_range 
			where tablename=@tablename and LOWER(publisher)=LOWER(@publisher) and publisher_db=@publisher_db
		if @@ERROR<>0	
			return (1)
	end
	if @next_seed is not NULL
	begin
		select @next_seed=next_seed from MSrepl_identity_range 
			where tablename=@tablename and LOWER(publisher)=LOWER(@publisher) and publisher_db=@publisher_db
		if @@ERROR<>0	
			return (1)
	end
	if @range is not NULL
	begin
		select @range=range from MSrepl_identity_range 
			where tablename=@tablename and LOWER(publisher)=LOWER(@publisher) and publisher_db=@publisher_db
		if @@ERROR<>0	
			return (1)
	end

return (0)

')
    if @@error <> 0 return 1
    exec('
raiserror(15339,-1,-1,''sp_MSadjust_pub_identity'')

')
    if @@error <> 0 return 1
    exec('
CREATE PROCEDURE sp_MSadjust_pub_identity (
@publisher			sysname,
@publisher_db		sysname,
@tablename			sysname,
@current_max		bigint  = NULL,
@threshold			int  = NULL,
@range				bigint = NULL,
@next_seed			bigint  = NULL
)AS

	declare @retcode		int
	if @current_max is NOT NULL
	begin
		update MSrepl_identity_range set current_max=@current_max 
			where tablename=@tablename and LOWER(publisher)=LOWER(@publisher) and publisher_db=@publisher_db
		if @@ERROR<>0	
			return (1)
		if exists (select * from MSrepl_identity_range where max_identity is not NULL 
			and ABS(current_max) > ABS(max_identity))
		begin
			raiserror(21195, 16, -1)
			return (1)
		end
	end
	if @threshold is NOT NULL
	begin
	update MSrepl_identity_range set threshold=@threshold 
		where tablename=@tablename and LOWER(publisher)=LOWER(@publisher) and publisher_db=@publisher_db
	if @@ERROR<>0	
		return (1)
	end
	if @next_seed is NOT NULL
	begin
	update MSrepl_identity_range set next_seed=@next_seed 
		where tablename=@tablename and LOWER(publisher)=LOWER(@publisher) and publisher_db=@publisher_db
	if @@ERROR<>0
		return (1)
	end
	if @range is NOT NULL
	begin
	update MSrepl_identity_range set range=@range 
		where tablename=@tablename and LOWER(publisher)=LOWER(@publisher) and publisher_db=@publisher_db	
	if @@ERROR<>0
		return (1)
	end
return (0)

')
    if @@error <> 0 return 1
    exec('
raiserror(15339,-1,-1,''sp_MSinsert_identity'')

')
    if @@error <> 0 return 1
    exec('
CREATE PROCEDURE sp_MSinsert_identity (
	@publisher 			sysname,
	@publisher_db 		sysname,
	@tablename 			sysname,
	@identity_support	int,
	@pub_identity_range bigint,
	@identity_range 	bigint,
	@threshold 			int,
	@next_seed			bigint,
	@max_identity		bigint
    ) AS
    
	declare @pub_seed 		bigint	
	declare @current_max 	bigint
	if not exists (select * from MSrepl_identity_range 
			where tablename=@tablename and LOWER(publisher)=LOWER(@publisher) and publisher_db=@publisher_db)
	begin
		select @pub_seed = @pub_identity_range + @next_seed
		select @current_max = @pub_seed - 1
		insert MSrepl_identity_range(publisher, publisher_db, tablename, identity_support, next_seed, pub_range, range, max_identity, threshold, current_max	)
				values (@publisher, @publisher_db, @tablename, @identity_support, @pub_seed,  @pub_identity_range, @identity_range, @max_identity, @threshold, @current_max)
		if @@ERROR<>0
			return 1
	end
	else if 1 > 1 --disable this segment for now.
		begin --adjust republisher''s resource table
			select @pub_seed = @pub_identity_range - 1
			/* update range, threshold at republisher as needed. Max and next_seed has been set already. */
			update MSrepl_identity_range set	range = @identity_range,
												pub_range = @pub_identity_range,
												threshold=@threshold, 
												next_seed=next_seed + @pub_identity_range,
												current_max=next_seed + @pub_seed --new next_seed not yet picked up
			where tablename=@tablename and LOWER(publisher)=LOWER(@publisher) and publisher_db=@publisher_db
			if @@ERROR<>0
				return (1)
		end

')
    if @@error <> 0 return 1
    exec('
raiserror(15339,-1,-1,''sp_MSget_subscription_dts_info'')

')
    if @@error <> 0 return 1
    exec('
CREATE PROCEDURE sp_MSget_subscription_dts_info (
	@job_id varbinary(16)

    ) AS

    /*
    ** Declarations.
    */

    /*
    ** Initializations.
    */
    SET NOCOUNT ON

	select dts_package_name, dts_package_location 
		from MSdistribution_agents where
        job_id = @job_id

    RETURN (0)

')
    if @@error <> 0 return 1
    exec('
raiserror(15339, -1, -1, ''sp_MSenumdistributionagentproperties'')

')
    if @@error <> 0 return 1
    exec('
CREATE PROCEDURE sp_MSenumdistributionagentproperties (
    @publisher        sysname,
    @publisher_db     sysname,
    @publication      sysname
    ) AS
    DECLARE @independent_agent bit 
    DECLARE @publisher_id int  -- Server id of the Publisher with respect to the Distributor
   
    SELECT @publisher_id = NULL 
    SELECT @publisher_id = srvid 
      FROM master..sysservers
     WHERE UPPER(@publisher) = UPPER(srvname)  
    
    IF @publisher_id IS NULL
    BEGIN
        RAISERROR(21169, 16, -1, @publisher, @@SERVERNAME, @publisher)    
    END

    IF @publication <> ''%'' -- A publication name is specified 
    BEGIN
        -- Determine whether the given publication shares an 
        -- agent with other publicaitons in the same database
        -- or not
        SELECT @independent_agent = 0
        SELECT @independent_agent = independent_agent 
          FROM MSpublications
         WHERE publisher_id = @publisher_id
           AND publisher_db = @publisher_db
           AND publication = @publication  
        
        IF @independent_agent IS NULL
        BEGIN
            RAISERROR(20026, 16, -1)
        END

        IF @independent_agent = 0 
        BEGIN
            -- If the publication shares an agent with 
            -- other publications, change the publication
            -- name to ''ALL'' before querying the MSdistribution_agents
            -- table
            SELECT @publication = N''ALL''                
        END

        SELECT job_id, offload_enabled, offload_server, 
               dts_package_name, dts_package_location,
			   -- status of this agent, only useful in detecting
			   -- whether or not the agent has be deactivated by cleanup
			   -- If so, all the rows in MSsubscriptions will be 0
			   (select top 1 s.status from MSsubscriptions s where
					s.agent_id = a.id)
          FROM MSdistribution_agents a
         WHERE a.publisher_id = @publisher_id 
           AND a.publisher_db = @publisher_db
           AND a.publication = @publication
    END
    ELSE
    BEGIN
        SELECT job_id, offload_enabled, offload_server, 
               dts_package_name, dts_package_location,
			   -- status of this agent, only useful in detecting
			   -- whether or not the agent has be deactivated by cleanup
			   -- If so, all the rows in MSsubscriptions will be 0
			   (select top 1 s.status from MSsubscriptions s where
					s.agent_id = a.id)
          FROM MSdistribution_agents a
         WHERE a.publisher_id = @publisher_id 
           AND a.publisher_db = @publisher_db
    END

')
    if @@error <> 0 return 1
    exec('    
raiserror(15339, -1, -1, ''sp_MSenum_merge_agent_properties'')

')
    if @@error <> 0 return 1
    exec('CREATE PROCEDURE sp_MSenum_merge_agent_properties (
    @publisher        sysname,
    @publisher_db     sysname,
    @publication      sysname
    ) AS
    
    DECLARE @publisher_id int  -- Server id of the Publisher with respect to the Distributor
    declare @publication_id int
    declare @retcode int

    SELECT @publisher_id = NULL 
    SELECT @publisher_id = srvid 
      FROM master..sysservers
     WHERE UPPER(@publisher) = UPPER(srvname)  

    IF @publisher_id IS  NULL
    BEGIN
        RAISERROR(21169, 16, -1, @publisher, @@SERVERNAME, @publisher)    
    END

    select @publication_id = publication_id from 
        MSpublications where 
        publisher_id = @publisher_id and
        publisher_db = @publisher_db and
        publication = @publication
    if @publication_id is NULL
    begin
        raiserror (20026, 11, -1, @publication)
        return (1)
    end
    
    exec @retcode = dbo.sp_MScheck_pull_access
        @agent_type = 1, -- merge agent
        @publication_id = @publication_id
    if @@error <> 0 or @retcode <> 0
	begin    
		RAISERROR (15247, 11, -1)
		return (1)
	end

    SELECT job_id, offload_enabled, offload_server 
      FROM MSmerge_agents
     WHERE publisher_id = @publisher_id 
       AND publisher_db = @publisher_db
       AND (@publication = N''%'' OR publication = @publication)

')
    if @@error <> 0 return 1
    exec('
raiserror(15339, -1, -1, ''sp_dropanonymoussubscription'')

')
    if @@error <> 0 return 1
    exec('CREATE PROCEDURE sp_dropanonymoussubscription
@agent_id int,
@type int -- 1 tran sub, 2 merge sub
as
	declare @retcode int
	if @type not in (1,2)
	begin
		raiserror(20587, 16, -1, ''@type'', ''sp_dropanonymoussubscription'')
		return (1)
	end
	-- If tran
	if @type = 1
	begin
		if not exists (select * from MSdistribution_agents where
			id = @agent_id and
			subscriber_name is not null)
		begin
			RAISERROR (14055, 11, -1)
			return(1)
		end
		exec @retcode = dbo.sp_MSdrop_distribution_agentid @agent_id
		if @retcode <> 0 or @@error <> 0
			return 1
	end
	else
	begin
		if not exists (select * from MSmerge_agents where
			id = @agent_id and
			subscriber_name is not null)
		begin
			RAISERROR (14055, 11, -1)
			return(1)
		end
		exec @retcode = dbo.sp_MSdrop_merge_agentid @agent_id
		if @retcode <> 0 or @@error <> 0
			return 1
	end

')
    if @@error <> 0 return 1
    exec('
raiserror(15339, -1, -1, ''sp_MSdrop_anonymous_entry'')

')
    if @@error <> 0 return 1
    exec('CREATE PROCEDURE sp_MSdrop_anonymous_entry
@subid uniqueidentifier,
@login sysname,
@type int -- 1 tran sub, 2 merge sub
as
	declare @sid varbinary(85)
	declare @agent_id int, @retcode int
	set nocount on 
	if @type not in (1,2)
	begin
		raiserror(20587, 16, -1, ''@type'', ''sp_MSdrop_anonymous_entry'')
		return (1)
	end
	-- If tran
	if @type = 1
	begin
		select @sid = sid from MSdistribution_agents where
			anonymous_subid = @subid
	end
	else
	begin
		select @sid = sid from MSmerge_agents where
			anonymous_subid = @subid
	end
	if @sid is null
	begin
		RAISERROR (14055, 11, -1)
		return(1)
	end
	-- null login means no security check needed
	if @login is not null and suser_sid(@login) <> @sid
	begin
		set @login = suser_sname(@sid)
		RAISERROR (20596, 11, -1, @login)
		RETURN (1)
	end

	if @type = 1
	begin
		select @agent_id = id from MSdistribution_agents where anonymous_subid = @subid
		if @agent_id is not null
		begin
			exec @retcode = dbo.sp_MSdrop_distribution_agentid @agent_id
			if @retcode <> 0 or @@error <> 0
				return 1
		end
	end
	else
	begin
		select @agent_id = id from MSmerge_agents where anonymous_subid = @subid
		if @agent_id is not null
		begin
			exec @retcode = dbo.sp_MSdrop_merge_agentid @agent_id
			if @retcode <> 0 or @@error <> 0
				return 1
		end
	end

')
    if @@error <> 0 return 1
    exec('
raiserror(15339, -1, -1, ''sp_MSadddynamicsnapshotjobatdistributor'')

')
    if @@error <> 0 return 1
    exec('--
-- Name: sp_MSadddynamicsnapshotjobatdistributor
--
-- Description: This function is called by sp_MSaddmergedynamicsnapshotjob 
--              at the publisher to set up a dynamic snapshot job in msdb
--              at the distributor. 
-- Notes: This procedure will only perform implicit checking for scheduling 
--        parameters as most parameters are expected to have been checked 
--        in sp_MSaddmergedynamicsnapshotjob
--
-- Parameters: @regular_snapshot_jobid uniqueidentifier (mandatory)
--             @dynamic_filter_login sysname (optional, default null)
--             @dynamic_filter_hostname sysname (optional, default null)
--             @dynamic_snapshot_location nvarchar(255) (mandatory)
--             @dynamic_snapshot_jobid uniqueidentifier (mandatory)
--             Scheduling information:
--             @freqtype int (optional, default 4 == Daily)
--             @freqinterval int (optional, default 1 == Every day)
--             @freqsubtype int (optional, default 4 (Sub interval = Minute))
--             @freqsubinterval int (optional, default 5 == Every five minutes)
--             @freqrelativeinterval int (optional, default 1) 
--             @freqrecurrencefactor int (optional, default 0) 
--             @activestartdate int (optional, default 0 == Today)
--             @activeenddate int (optional, default 99991231) 
--             @activestarttimeofday int (optional, default 0 == Now)        
--             @activeendtimeofday int (optional, default 235959)
--
-- Returns: 0 - succeeded
--          1 - failed
--
-- Security: Only members of the ''sysadmin'' server role and members of the 
--           ''db_owner'' database role at the distributor can call this 
--           procedure. This procedure is intended to be called through 
--           the distributor_admin remote login in the case where
--           the distributor is a different machine from the publisher. 
--
create procedure sp_MSadddynamicsnapshotjobatdistributor (
    @regular_snapshot_jobid uniqueidentifier,
    @dynamic_filter_login sysname = null,
    @dynamic_filter_hostname sysname = null,
    @dynamic_snapshot_location nvarchar(255),
    @dynamic_snapshot_jobname sysname output,
    @dynamic_snapshot_jobid uniqueidentifier output,

    -- Scheduling information
    @freqtype               int = 4,
    @freqinterval           int = 1,
    @freqsubtype            int = 4,
    @freqsubinterval        int = 5,
    @freqrelativeinterval   int = 1, 
    @freqrecurrencefactor   int = 0,
    @activestartdate        int = 0,
    @activeenddate          int = 99991231,
    @activestarttimeofday   int = 0, 
    @activeendtimeofday     int = 235959
    )
as
begin
    set nocount on

    declare @retcode int
    declare @agent_command_line nvarchar(4000)
    declare @agent_id int
    declare @db_name sysname    
    declare @category_name sysname
    declare @nullchar nchar(20)

    declare @dynamicfilterloginparam nvarchar(50)
    declare @dynamicfilterhostnameparam nvarchar(50)
    declare @dynamicsnapshotlocationparam nvarchar(50)
    
    select @retcode = 0
    select @agent_command_line = null
    select @agent_id = null
    select @db_name = db_name()
    select @category_name = name 
      from msdb.dbo.syscategories 
     where category_id = 15
    select @nullchar = null

    select @dynamicfilterloginparam = N'' -DynamicFilterLogin ''
    select @dynamicfilterhostnameparam = N'' -DynamicFilterHostName ''
    select @dynamicsnapshotlocationparam = N'' -DynamicSnapshotLocation ''

    -- Get the regular snapshot agent command line
    select @agent_command_line = command 
      from msdb..sysjobsteps
     where job_id = @regular_snapshot_jobid
       and subsystem = N''Snapshot''
       and step_id = 2
        
    if @agent_command_line is null
    begin
        raiserror(21319, 11, -1)        
        return 1        
    end  
    
    -- Get the agent id    
    select @agent_id = id
      from MSsnapshot_agents
     where job_id = @regular_snapshot_jobid

    if @agent_id is null
    begin
        raiserror(21325, 11, -1)
        return 1
    end

    if @dynamic_snapshot_jobname is null or @dynamic_snapshot_jobname = N''''
    begin
        -- Get the job name of the regular snapshot job. This is going to be used
        -- to derive a uniquefied job name for the dynamic snapshot job
        select @dynamic_snapshot_jobname = name 
          from msdb..sysjobs 
         where job_id = @regular_snapshot_jobid
    
        select @dynamic_snapshot_jobname = N''dyn_'' + left(@dynamic_snapshot_jobname,88) + convert(nvarchar(36), newid()) 
    end

    if @dynamic_filter_login is not null and @dynamic_filter_login <> N''''
    begin
        select @agent_command_line = @agent_command_line + @dynamicfilterloginparam + N''['' + rtrim(@dynamic_filter_login) + N'']'' 
    end

    if @dynamic_filter_hostname is not null and @dynamic_filter_hostname <> N''''
    begin
        select @agent_command_line = @agent_command_line + @dynamicfilterhostnameparam + N''['' + rtrim(@dynamic_filter_hostname) + N'']'' 
    end

    -- @dynamic_snapshot_location is assumed to be non-empty
    select @agent_command_line = @agent_command_line + @dynamicsnapshotlocationparam + N''['' + @dynamic_snapshot_location + N'']''

    -- Add the job
    exec @retcode = dbo.sp_MSadd_repl_job
         @name = @dynamic_snapshot_jobname,
         @subsystem = ''Snapshot'',
         @server = @@servername,
         @databasename = @db_name,
         @enabled = 1,
         @freqtype = @freqtype,
         @freqinterval = @freqinterval,
         @freqsubtype = @freqsubtype,
         @freqsubinterval = @freqsubinterval,
         @freqrecurrencefactor = @freqrecurrencefactor,
         @activestartdate = @activestartdate,
         @activeenddate = @activeenddate,
         @activestarttimeofday = @activestarttimeofday,
         @activeendtimeofday = @activeendtimeofday,
         @nextrundate = 0,
         @nextruntime = 0,
         @runpriority = 0,
         @emailoperatorname = @nullchar,
         @retryattempts = 10,
         @retrydelay = 1,
         @command = @agent_command_line,
         @loghistcompletionlevel = 0,
         @emailcompletionlevel = 0,
         @description = @nullchar,
         @tagobjectid = 0,
         @tagobjecttype = 0,
         @category_name = @category_name,
         @failure_detection = 1,
         @agent_id = @agent_id,
         @job_id = @dynamic_snapshot_jobid output            

    return @retcode
end

')
    if @@error <> 0 return 1
    exec('
raiserror(15339, -1, -1, ''sp_MSdeleterepljob'')

')
    if @@error <> 0 return 1
    exec('--
-- Name: sp_MSdeleterepljob
--
-- Description: This is a lightweight wrapper of sp_delete_job to be used
--              by remote publishers.
--
-- Parameter: @job_id uniqueidentifier (mandatory)
--
-- Returns 0 - succeeded
--         1 - failed
-- 
-- Security: Only members of the sysadmin server role and members of the 
-- db_owner role of the distribution database can execute this function. This
-- procedure is intended to be called through the distribution_admin remote 
-- login from remote publishers
--
create procedure sp_MSdeleterepljob (
    @job_id uniqueidentifier
    )
as
begin
    set nocount on
    declare @retcode int
    declare @job_name sysname 

    select @retcode = 0
    exec @retcode = msdb..sp_delete_job @job_id = @job_id

    return @retcode
end

')
    if @@error <> 0 return 1
    exec('
raiserror(15339, -1, -1, ''sp_MSdeletefoldercontents'')

')
    if @@error <> 0 return 1
    exec('--
-- Name: sp_MSdeletefoldercontents
--
-- Description: This is a lighweight wrapper for deleting all files in the
--              specified directory. This procedure is meant to be called by 
--              a remote publisher for deleting files in the distributor''s 
--              context.
--
-- Parameter: @folder nvarchar(255) (mandatory)
--
-- Returns 0 - succeeded
--         1 - failed
--
-- Security: Only members of the sysadmin server role and members of the 
-- db_owner role of the distribution database can execute this function. This
-- procedure is intended to be called through the distribution_admin remote 
-- login from remote publishers
--
create procedure sp_MSdeletefoldercontents (
    @folder nvarchar(255)
    )
as
begin
    set nocount on

    declare @command_prefix nvarchar(4000)
    declare @command nvarchar(4000)
    declare @retcode int

    select @retcode = 0
    if len(@folder) = 0 or @folder is null
    begin
        return 0
    end

    -- \ terminate path
    if substring(@folder, len(@folder), 1) <> N''\''
    begin
        select @folder = @folder + N''\''
    end 

    if (platform() & 0x1) = 0x1 
    begin
        select @command_prefix = ''del /q /f "'' + fn_escapecmdshellsymbolsremovequotes(@folder) collate database_default
    end
    else
    begin
        -- Win9x ''del'' command does not support the /q and /f switches
        select @command_prefix = ''del "'' + fn_escapecmdshellsymbolsremovequotes(@folder) collate database_default
    end

    -- Hardwired merge system table files without embedded _
    set @command = @command_prefix + N''sysmergesubsetfilters*.sch"''
    exec @retcode = master..xp_cmdshell @command, no_output

    set @command = @command_prefix + N''sysmergesubsetfilters*.bcp"''
    exec @retcode = master..xp_cmdshell @command, no_output
    
    -- .sch extensio
    set @command = @command_prefix + N''*_*.sch"''
    exec @retcode = master..xp_cmdshell @command, no_output

    -- .bcp extension
    set @command = @command_prefix + N''*_*.bcp"''
    exec @retcode = master..xp_cmdshell @command, no_output

    -- .idx extension
    set @command = @command_prefix + N''*_*.idx"''
    exec @retcode = master..xp_cmdshell @command, no_output

    -- .sql extension
    set @command = @command_prefix + N''*_*.sql"''
    exec @retcode = master..xp_cmdshell @command, no_output

    -- snapshot.cab
    set @command = @command_prefix + N''snapshot.cab"''
    exec @retcode = master..xp_cmdshell @command, no_output

    -- .pre extension
    set @command = @command_prefix + N''snapshot.pre"''
    exec @retcode = master..xp_cmdshell @command, no_output

    -- .trg extension
    set @command = @command_prefix + N''*_*.trg"''
    exec @retcode = master..xp_cmdshell @command, no_output

    -- .xpp extension
    set @command = @command_prefix + N''*_*.xpp"''
    exec @retcode = master..xp_cmdshell @command, no_output

    -- .cft extension
    set @command = @command_prefix + N''*_*.cft"''
    exec @retcode = master..xp_cmdshell @command, no_output

    -- .dri extension
    set @command = @command_prefix + N''*_*.dri"''
    exec @retcode = master..xp_cmdshell @command, no_output

Failure:

    return @retcode

end

')
    if @@error <> 0 return 1
    exec('
CREATE PROCEDURE sp_MSinvalidate_snapshot
@publisher sysname,
@publisher_db sysname,
@publication sysname

as
    set nocount on
    declare @publisher_id smallint  
    declare @automatic tinyint
    declare @virtual smallint 
    declare @retcode int
    declare @active tinyint
		, @initiated tinyint
		, @subscribed tinyint
    declare @publication_id int

    select @automatic = 1
    select @virtual = - 1
    select @active = 2
		, @initiated = 3
		, @subscribed = 1

    -- Check if publisher is a defined as a distribution publisher in the current database
    exec @retcode = dbo.sp_MSvalidate_distpublisher @publisher, @publisher_id OUTPUT
    if @retcode <> 0
    begin
        return(1)
    end

    -- Make sure publication exists
    select @publication_id = publication_id
        from MSpublications where publication = @publication and
        publisher_id = @publisher_id and publisher_db = @publisher_db

	-- Set the virtual subscription status to be ''subscribed'' so that
	-- new subscription will wait for the next snapshot.
	update MSsubscriptions set status = @subscrib