create procedure sys.sp_MSrestore_sub_tran
(
    @subscriber_security_mode   int,
    @subscriber_login           sysname,
    @subscriber_password        nvarchar(524),
    @distributor_security_mode	int,
    @distributor_login sysname,
    @distributor_password sysname,
    @job_login nvarchar(257),
    @job_password sysname
)
AS
begin
    SET NOCOUNT ON
    -- Declarations.
    declare @push int
                ,@retcode int
                ,@publisher sysname          /* Expression used in the cursor */
                ,@publisher_db sysname     /* Expression used in the cursor */
                ,@publication sysname      /* Expression used in the cursor */

    -- Initialization
    select @push = 0
            ,@retcode = 0

    -- Reset agent_id and anonymous agent id and time
    update MSreplication_subscriptions set agent_id = NULL, subid = NULL,
        distribution_agent = NULL, time = getdate()
    if @@error <> 0
    begin
        select @retcode = 1
        goto Cleanup
    end

    -- Set attach_state value
    -- It is only used by pull subscription.
    -- The attach state is "attached but not processed".
    update MSsubscription_agents set attach_state = 1

	IF @job_login IS NOT NULL
		OR @job_password IS NOT NULL
		OR @subscriber_security_mode != 1
		OR @subscriber_login IS NOT NULL
		OR @subscriber_password IS NOT NULL		
		OR @distributor_security_mode != 1
		OR @distributor_login IS NOT NULL
		OR @distributor_password IS NOT NULL		
	BEGIN
			
	    -- Add the agents for pull or anonymous subscriptions.
	    DECLARE #hCsubscription CURSOR LOCAL FAST_FORWARD FOR
	        SELECT publisher, publisher_db, publication
	            FROM MSreplication_subscriptions where
	                publication is not NULL and
	                publication <> N'' and
	                subscription_type <> @push

	    FOR READ ONLY
	    OPEN #hCsubscription

	    FETCH #hCsubscription INTO @publisher, @publisher_db, @publication
	    WHILE (@@fetch_status <> -1)
	    BEGIN
	        EXECUTE @retcode  = sys.sp_addpullsubscription_agent
	            @publisher = @publisher,
	            @publisher_db = @publisher_db,
	            @publication = @publication,
	            @frequency_type  = 2 ,  /* 2== OnDemand */
	            @subscriber_security_mode = @subscriber_security_mode,
				@subscriber_login = @subscriber_login,
				@subscriber_password = @subscriber_password,
	            @distributor_security_mode = @distributor_security_mode,
	            @distributor_login = @distributor_login,
	            @distributor_password = @distributor_password,
	            @job_login = @job_login,
	            @job_password = @job_password

	        if @@error <> 0 or @retcode <> 0
	        begin
	            select @retcode = 1
	            goto Cleanup
	        end
	        FETCH #hCsubscription INTO @publisher, @publisher_db, @publication
	    END
	END
	
Cleanup:
    -- Nothing to cleanup
    return    @retcode
end