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