CREATE PROCEDURE sys.sp_MSupgrade_distdb_security_metadata
AS
BEGIN
DECLARE @retcode int,
@agent_id int,
@job_id uniqueidentifier,
@job_step_id int,
@job_step_uid uniqueidentifier,
@command_line nvarchar(3200),
@publisher sysname,
@publisher_security_mode int,
@publisher_login sysname,
@publisher_password nvarchar(524),
@subscriber sysname,
@subscriber_security_mode int,
@subscriber_login sysname,
@subscriber_password nvarchar(524),
@junk_password nvarchar(524)
-- setup a junk_password to be used for security_mode 1
SELECT @junk_password = NEWID()
EXEC @retcode = sys.sp_MSsafe_repl_encrypt @junk_password OUTPUT
IF @@ERROR <> 0 OR @retcode <> 0
RETURN 1
BEGIN TRANSACTION tr_upgrade_distdb
SAVE TRANSACTION tr_upgrade_distdb
-- SNAPSHOT AGENT
IF OBJECT_ID(N'MSsnapshot_agents', N'U') IS NOT NULL
BEGIN
/*
* Here we loop through each snapshot agent to update the
* replication meta-data and the jobstep command-line...
*/
DECLARE cursorSnapshotAgents CURSOR LOCAL FAST_FORWARD FOR
SELECT mssa.id,
syss.name,
sjs.job_id,
sjs.step_id,
sjs.step_uid,
sjs.command
FROM MSsnapshot_agents mssa
JOIN sys.servers syss
ON syss.server_id = mssa.publisher_id
LEFT JOIN msdb.dbo.sysjobs_view sjv
ON sjv.job_id = mssa.job_id
LEFT JOIN msdb.dbo.sysjobsteps sjs
ON sjv.job_id = sjs.job_id
WHERE sjv.category_id = 15
AND sjs.subsystem = N'Snapshot'
-- Make sure we only upgrade items that are new
AND mssa.publisher_security_mode IS NULL
AND mssa.publisher_login IS NULL
AND mssa.publisher_password IS NULL
AND mssa.job_step_uid IS NULL
OPEN cursorSnapshotAgents
FETCH cursorSnapshotAgents into @agent_id, @publisher, @job_id, @job_step_id, @job_step_uid, @command_line
WHILE @@FETCH_STATUS <> -1
BEGIN
SELECT @publisher_security_mode = NULL,
@publisher_login = NULL,
@publisher_password = NULL
/*
* Retrieve command-line publisher login info
*/
SELECT @publisher_security_mode = CAST(arguments as int)
FROM sys.fn_getparametervalues(@command_line)
WHERE LOWER(parameters collate SQL_Latin1_General_CP1_CS_AS) = N'publishersecuritymode'
SELECT @publisher_login = arguments
FROM sys.fn_getparametervalues(@command_line)
WHERE LOWER(parameters collate SQL_Latin1_General_CP1_CS_AS) = N'publisherlogin'
SELECT @publisher_password = arguments
FROM sys.fn_getparametervalues(@command_line)
WHERE LOWER(parameters collate SQL_Latin1_General_CP1_CS_AS) = N'publisherencryptedpassword'
IF @publisher_password IS NULL
BEGIN
SELECT @publisher_password = arguments
FROM sys.fn_getparametervalues(@command_line)
WHERE LOWER(parameters collate SQL_Latin1_General_CP1_CS_AS) = N'publisherpassword'
EXEC @retcode = sys.sp_MSsafe_repl_encrypt @publisher_password OUTPUT
IF @@ERROR <> 0 OR @retcode <> 0
GOTO UNDO
END
/*
* Set the actual values to be used in the meta-data update
*/
-- if security_mode is NULL then we have to set the values
IF @publisher_security_mode IS NULL
BEGIN
-- if the security_mode is null and login is provided set sec to 0
IF @publisher_login IS NOT NULL
BEGIN
SELECT @publisher_security_mode = 0
END
-- else use the default publisher login info if possible
ELSE IF OBJECT_ID(N'msdb.dbo.MSdistpublishers', N'U') IS NOT NULL
BEGIN
SELECT @publisher_security_mode = security_mode,
@publisher_login = login,
@publisher_password = password
FROM msdb.dbo.MSdistpublishers
WHERE UPPER(name) = UPPER(@publisher)
AND distribution_db = DB_NAME()
END
END
-- at this point if security_mode is NULL or 1 set the values
IF @publisher_security_mode IS NULL
OR @publisher_security_mode = 1
BEGIN
SELECT @publisher_security_mode = 1,
@publisher_login = N'',
@publisher_password = @junk_password
END
/*
* Remove/Update command-line parameters
*/
-- remove the publisher login parameters from the command-line
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'PublisherSecurityMode')
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'PublisherLogin')
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'PublisherPassword')
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'PublisherEncryptedPassword')
-- remove the distributor login parameters from the command-line
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'DistributorLogin')
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'DistributorPassword')
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'DistributorEncryptedPassword')
-- ensure that the DistributorSecurityMode on command-line is 1
SELECT @command_line = sys.fn_updateparameterwithargument(@command_line, N'DistributorSecurityMode', N'1')
/*
* Update meta-data table and jobstep command-line
*/
UPDATE MSsnapshot_agents
SET publisher_security_mode = @publisher_security_mode,
publisher_login = @publisher_login,
publisher_password = @publisher_password,
job_step_uid = @job_step_uid
WHERE id = @agent_id
UPDATE msdb.dbo.sysjobsteps
SET command = @command_line,
database_name = DB_NAME()
WHERE job_id = @job_id
AND (step_uid = @job_step_uid
OR step_id = @job_step_id)
FETCH cursorSnapshotAgents into @agent_id, @publisher, @job_id, @job_step_id, @job_step_uid, @command_line
END
CLOSE cursorSnapshotAgents
DEALLOCATE cursorSnapshotAgents
END
-- LOGREADER AGENTS
IF OBJECT_ID(N'MSlogreader_agents', N'U') IS NOT NULL
BEGIN
/*
* Here we loop through each logreader agent to update the
* replication meta-data and the jobstep command-line...
*/
DECLARE cursorLogreaderAgents CURSOR LOCAL FAST_FORWARD FOR
SELECT msla.id,
syss.name,
sjs.job_id,
sjs.step_id,
sjs.step_uid,
sjs.command
FROM MSlogreader_agents msla
JOIN sys.servers syss
ON syss.server_id = msla.publisher_id
LEFT JOIN msdb.dbo.sysjobs_view sjv
ON sjv.job_id = msla.job_id
LEFT JOIN msdb.dbo.sysjobsteps sjs
ON sjv.job_id = sjs.job_id
WHERE sjv.category_id = 13
AND sjs.subsystem = N'LogReader'
-- Make sure we only upgrade items that are new
AND msla.publisher_security_mode IS NULL
AND msla.publisher_login IS NULL
AND msla.publisher_password IS NULL
AND msla.job_step_uid IS NULL
OPEN cursorLogreaderAgents
FETCH cursorLogreaderAgents into @agent_id, @publisher, @job_id, @job_step_id, @job_step_uid, @command_line
WHILE @@FETCH_STATUS <> -1
BEGIN
SELECT @publisher_security_mode = NULL,
@publisher_login = NULL,
@publisher_password = NULL
/*
* Retrieve command-line publisher login info
*/
SELECT @publisher_security_mode = CAST(arguments as int)
FROM sys.fn_getparametervalues(@command_line)
WHERE LOWER(parameters collate SQL_Latin1_General_CP1_CS_AS) = N'publishersecuritymode'
SELECT @publisher_login = arguments
FROM sys.fn_getparametervalues(@command_line)
WHERE LOWER(parameters collate SQL_Latin1_General_CP1_CS_AS) = N'publisherlogin'
SELECT @publisher_password = arguments
FROM sys.fn_getparametervalues(@command_line)
WHERE LOWER(parameters collate SQL_Latin1_General_CP1_CS_AS) = N'publisherencryptedpassword'
IF @publisher_password IS NULL
BEGIN
SELECT @publisher_password = arguments
FROM sys.fn_getparametervalues(@command_line)
WHERE LOWER(parameters collate SQL_Latin1_General_CP1_CS_AS) = N'publisherpassword'
EXEC @retcode = sys.sp_MSsafe_repl_encrypt @publisher_password OUTPUT
IF @@ERROR <> 0 OR @retcode <> 0
GOTO UNDO
END
/*
* Set the actual values to be used in the meta-data update
*/
-- if security_mode is specified on the cmd then use the values
IF @publisher_security_mode IS NULL
BEGIN
-- if the security_mode is null and login is provided set sec to 0
IF @publisher_login IS NOT NULL
BEGIN
SELECT @publisher_security_mode = 0
END
-- else use the default publisher login info if possible
ELSE IF OBJECT_ID(N'msdb.dbo.MSdistpublishers', N'U') IS NOT NULL
BEGIN
SELECT @publisher_security_mode = security_mode,
@publisher_login = login,
@publisher_password = password
FROM msdb.dbo.MSdistpublishers
WHERE UPPER(name) = UPPER(@publisher)
AND distribution_db = DB_NAME()
END
END
-- at this point if security_mode is NULL or 1 set the values
IF @publisher_security_mode IS NULL
OR @publisher_security_mode = 1
BEGIN
SELECT @publisher_security_mode = 1,
@publisher_login = N'',
@publisher_password = @junk_password
END
/*
* Remove/Update command-line parameters
*/
-- remove the publisher login parameters from the command-line
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'PublisherSecurityMode')
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'PublisherLogin')
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'PublisherPassword')
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'PublisherEncryptedPassword')
-- remove the distributor login parameters from the command-line
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'DistributorLogin')
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'DistributorPassword')
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'DistributorEncryptedPassword')
-- ensure that the DistributorSecurityMode on command-line is 1
SELECT @command_line = sys.fn_updateparameterwithargument(@command_line, N'DistributorSecurityMode', N'1')
/*
* Update meta-data table and jobstep command-line
*/
UPDATE MSlogreader_agents
SET publisher_security_mode = @publisher_security_mode,
publisher_login = @publisher_login,
publisher_password = @publisher_password,
job_step_uid = @job_step_uid
WHERE id = @agent_id
UPDATE msdb.dbo.sysjobsteps
SET command = @command_line,
database_name = DB_NAME()
WHERE job_id = @job_id
AND (step_uid = @job_step_uid
OR step_id = @job_step_id)
FETCH cursorLogreaderAgents into @agent_id, @publisher, @job_id, @job_step_id, @job_step_uid, @command_line
END
CLOSE cursorLogreaderAgents
DEALLOCATE cursorLogreaderAgents
END
-- QREADER AGENTS
IF OBJECT_ID(N'MSqreader_agents', N'U') IS NOT NULL
BEGIN
/*
* Here we loop through each qreader agent to update the
* replication meta-data and the jobstep command-line...
*/
DECLARE cursorQreaderAgents CURSOR LOCAL FAST_FORWARD FOR
SELECT msqa.id,
sjs.job_id,
sjs.step_id,
sjs.step_uid,
sjs.command
FROM MSqreader_agents msqa
LEFT JOIN msdb.dbo.sysjobs_view sjv
ON sjv.job_id = msqa.job_id
LEFT JOIN msdb.dbo.sysjobsteps sjs
ON sjv.job_id = sjs.job_id
WHERE sjv.category_id = 19
AND sjs.subsystem = N'QueueReader'
-- Make sure we only upgrade items that are new
AND msqa.job_step_uid IS NULL
OPEN cursorQreaderAgents
FETCH cursorQreaderAgents into @agent_id, @job_id, @job_step_id, @job_step_uid, @command_line
WHILE @@FETCH_STATUS <> -1
BEGIN
/*
* Remove/Update command-line parameters
*/
-- remove the publisher login parameters from the command-line
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'PublisherSecurityMode')
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'PublisherLogin')
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'PublisherPassword')
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'PublisherEncryptedPassword')
-- remove the distributor login parameters from the command-line
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'DistributorLogin')
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'DistributorPassword')
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'DistributorEncryptedPassword')
-- ensure that the DistributorSecurityMode on command-line is 1
SELECT @command_line = sys.fn_updateparameterwithargument(@command_line, N'DistributorSecurityMode', N'1')
/*
* Update meta-data table and jobstep command-line
*/
UPDATE MSqreader_agents
SET job_step_uid = @job_step_uid
WHERE id = @agent_id
UPDATE msdb.dbo.sysjobsteps
SET command = @command_line,
database_name = DB_NAME()
WHERE job_id = @job_id
AND (step_uid = @job_step_uid
OR step_id = @job_step_id)
FETCH cursorQreaderAgents into @agent_id, @job_id, @job_step_id, @job_step_uid, @command_line
END
CLOSE cursorQreaderAgents
DEALLOCATE cursorQreaderAgents
END
-- PUSH DISTRIBUTION AGENTS
IF OBJECT_ID(N'MSdistribution_agents', N'U') IS NOT NULL
BEGIN
/*
* Here we loop through each distribution agent to update the
* replication meta-data and the jobstep command-line...
*/
DECLARE cursorDistributionAgents CURSOR LOCAL FAST_FORWARD FOR
SELECT msda.id,
syss1.name,
syss2.name,
sjs.job_id,
sjs.step_id,
sjs.step_uid,
sjs.command
FROM MSdistribution_agents msda
JOIN sys.servers syss1
ON syss1.server_id = msda.publisher_id
JOIN sys.servers syss2
ON syss2.server_id = msda.subscriber_id
LEFT JOIN msdb.dbo.sysjobs_view sjv
ON sjv.job_id = msda.job_id
LEFT JOIN msdb.dbo.sysjobsteps sjs
ON sjv.job_id = sjs.job_id
WHERE sjv.category_id = 10
AND sjs.subsystem = N'Distribution'
-- Make sure we only upgrade items that are new
AND msda.subscriber_security_mode IS NULL
AND msda.subscriber_login IS NULL
AND msda.subscriber_password IS NULL
AND msda.job_step_uid IS NULL
OPEN cursorDistributionAgents
FETCH cursorDistributionAgents into @agent_id, @publisher, @subscriber, @job_id, @job_step_id, @job_step_uid, @command_line
WHILE @@FETCH_STATUS <> -1
BEGIN
SELECT @subscriber_security_mode = NULL,
@subscriber_login = NULL,
@subscriber_password = NULL
/*
* Retrieve command-line subscriber login info
*/
SELECT @subscriber_security_mode = CAST(arguments as int)
FROM sys.fn_getparametervalues(@command_line)
WHERE LOWER(parameters collate SQL_Latin1_General_CP1_CS_AS) = N'subscribersecuritymode'
SELECT @subscriber_login = arguments
FROM sys.fn_getparametervalues(@command_line)
WHERE LOWER(parameters collate SQL_Latin1_General_CP1_CS_AS) = N'subscriberlogin'
SELECT @subscriber_password = arguments
FROM sys.fn_getparametervalues(@command_line)
WHERE LOWER(parameters collate SQL_Latin1_General_CP1_CS_AS) = N'subscriberencryptedpassword'
IF @subscriber_password IS NULL
BEGIN
SELECT @subscriber_password = arguments
FROM sys.fn_getparametervalues(@command_line)
WHERE LOWER(parameters collate SQL_Latin1_General_CP1_CS_AS) = N'subscriberpassword'
EXEC @retcode = sys.sp_MSsafe_repl_encrypt @subscriber_password OUTPUT
IF @@ERROR <> 0 OR @retcode <> 0
GOTO UNDO
END
/*
* Set the actual values to be used in the meta-data update
*/
-- if security_mode is specified on the cmd then use the values
IF @subscriber_security_mode IS NULL
BEGIN
-- if the security_mode is null and login is provided set sec to 0
IF @subscriber_login IS NOT NULL
BEGIN
SELECT @subscriber_security_mode = 0
END
-- else use the default subscriber login info if possible
ELSE IF OBJECT_ID(N'MSsubscriber_info', N'U') IS NOT NULL
BEGIN
SELECT @subscriber_security_mode = security_mode,
@subscriber_login = login,
@subscriber_password = password
FROM MSsubscriber_info
WHERE UPPER(publisher) = UPPER(@publisher)
AND UPPER(subscriber) = UPPER(@subscriber)
END
END
-- at this point if security_mode is NULL or 1 set the values
IF @subscriber_security_mode IS NULL
OR @subscriber_security_mode = 1
BEGIN
SELECT @subscriber_security_mode = 1,
@subscriber_login = N'',
@subscriber_password = @junk_password
END
/*
* Remove/Update command-line parameters
*/
-- remove the subscriber login parameters from the command-line
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'SubscriberSecurityMode')
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'SubscriberLogin')
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'SubscriberPassword')
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'SubscriberEncryptedPassword')
-- remove the distributor login parameters from the command-line
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'DistributorLogin')
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'DistributorPassword')
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'DistributorEncryptedPassword')
-- ensure that the DistributorSecurityMode on command-line is 1
SELECT @command_line = sys.fn_updateparameterwithargument(@command_line, N'DistributorSecurityMode', N'1')
/*
* Update meta-data table and jobstep command-line
*/
UPDATE MSdistribution_agents
SET subscriber_security_mode = @subscriber_security_mode,
subscriber_login = @subscriber_login,
subscriber_password = @subscriber_password,
job_step_uid = @job_step_uid
WHERE id = @agent_id
UPDATE msdb.dbo.sysjobsteps
SET command = @command_line,
database_name = DB_NAME()
WHERE job_id = @job_id
AND (step_uid = @job_step_uid
OR step_id = @job_step_id)
FETCH cursorDistributionAgents into @agent_id, @publisher, @subscriber, @job_id, @job_step_id, @job_step_uid, @command_line
END
CLOSE cursorDistributionAgents
DEALLOCATE cursorDistributionAgents
END
-- PUSH MERGE AGENTS
IF OBJECT_ID(N'MSmerge_agents', N'U') IS NOT NULL
BEGIN
/*
* Here we loop through each merge agent to update the
* replication meta-data and the jobstep command-line...
*/
DECLARE cursorMergeAgents CURSOR LOCAL FAST_FORWARD FOR
SELECT msma.id,
syss.name,
msma.subscriber_name,
sjs.job_id,
sjs.step_id,
sjs.step_uid,
sjs.command
FROM MSmerge_agents msma
JOIN sys.servers syss
ON syss.server_id = msma.publisher_id
LEFT JOIN msdb.dbo.sysjobs_view sjv
ON sjv.job_id = msma.job_id
LEFT JOIN msdb.dbo.sysjobsteps sjs
ON sjv.job_id = sjs.job_id
WHERE sjv.category_id = 14
AND sjs.subsystem = N'Merge'
-- Make sure we only upgrade items that are new
AND msma.publisher_security_mode IS NULL
AND msma.publisher_login IS NULL
AND msma.publisher_password IS NULL
AND msma.subscriber_security_mode IS NULL
AND msma.subscriber_login IS NULL
AND msma.subscriber_password IS NULL
AND msma.job_step_uid IS NULL
OPEN cursorMergeAgents
FETCH cursorMergeAgents into @agent_id, @publisher, @subscriber, @job_id, @job_step_id, @job_step_uid, @command_line
WHILE @@FETCH_STATUS <> -1
BEGIN
SELECT @publisher_security_mode = NULL,
@publisher_login = NULL,
@publisher_password = NULL,
@subscriber_security_mode = NULL,
@subscriber_login = NULL,
@subscriber_password = NULL
/*
* Retrieve command-line publisher and subscriber login info
*/
SELECT @publisher_security_mode = CAST(arguments as int)
FROM sys.fn_getparametervalues(@command_line)
WHERE LOWER(parameters collate SQL_Latin1_General_CP1_CS_AS) = N'publishersecuritymode'
SELECT @publisher_login = arguments
FROM sys.fn_getparametervalues(@command_line)
WHERE LOWER(parameters collate SQL_Latin1_General_CP1_CS_AS) = N'publisherlogin'
SELECT @publisher_password = arguments
FROM sys.fn_getparametervalues(@command_line)
WHERE LOWER(parameters collate SQL_Latin1_General_CP1_CS_AS) = N'publisherencryptedpassword'
IF @publisher_password IS NULL
BEGIN
SELECT @publisher_password = arguments
FROM sys.fn_getparametervalues(@command_line)
WHERE LOWER(parameters collate SQL_Latin1_General_CP1_CS_AS) = N'publisherpassword'
EXEC @retcode = sys.sp_MSsafe_repl_encrypt @publisher_password OUTPUT
IF @@ERROR <> 0 OR @retcode <> 0
GOTO UNDO
END
SELECT @subscriber_security_mode = CAST(arguments as int)
FROM sys.fn_getparametervalues(@command_line)
WHERE LOWER(parameters collate SQL_Latin1_General_CP1_CS_AS) = N'subscribersecuritymode'
SELECT @subscriber_login = arguments
FROM sys.fn_getparametervalues(@command_line)
WHERE LOWER(parameters collate SQL_Latin1_General_CP1_CS_AS) = N'subscriberlogin'
SELECT @subscriber_password = arguments
FROM sys.fn_getparametervalues(@command_line)
WHERE LOWER(parameters collate SQL_Latin1_General_CP1_CS_AS) = N'subscriberencryptedpassword'
IF @subscriber_password IS NULL
BEGIN
SELECT @subscriber_password = arguments
FROM sys.fn_getparametervalues(@command_line)
WHERE LOWER(parameters collate SQL_Latin1_General_CP1_CS_AS) = N'subscriberpassword'
EXEC @retcode = sys.sp_MSsafe_repl_encrypt @subscriber_password OUTPUT
IF @@ERROR <> 0 OR @retcode <> 0
GOTO UNDO
END
/*
* Set the actual values to be used in the meta-data update
*/
-- if security_mode is specified on the cmd then use the values
IF @publisher_security_mode IS NULL
BEGIN
-- if the security_mode is null and login is provided set sec to 0
IF @publisher_login IS NOT NULL
BEGIN
SELECT @publisher_security_mode = 0
END
-- else use the default publisher login info if possible
ELSE IF OBJECT_ID(N'msdb.dbo.MSdistpublishers', N'U') IS NOT NULL
BEGIN
SELECT @publisher_security_mode = security_mode,
@publisher_login = login,
@publisher_password = password
FROM msdb.dbo.MSdistpublishers
WHERE UPPER(name) = UPPER(@publisher)
AND distribution_db = DB_NAME()
END
END
-- at this point if security_mode is NULL or 1 set the values
IF @publisher_security_mode IS NULL
OR @publisher_security_mode = 1
BEGIN
SELECT @publisher_security_mode = 1,
@publisher_login = N'',
@publisher_password = @junk_password
END
-- if security_mode is specified on the cmd then use the values
IF @subscriber_security_mode IS NULL
BEGIN
-- if the security_mode is null and login is provided set sec to 0
IF @subscriber_login IS NOT NULL
BEGIN
SELECT @subscriber_security_mode = 0
END
-- else use the default subscriber login info if possible
ELSE IF OBJECT_ID(N'MSsubscriber_info', N'U') IS NOT NULL
BEGIN
SELECT @subscriber_security_mode = security_mode,
@subscriber_login = login,
@subscriber_password = password
FROM MSsubscriber_info
WHERE UPPER(publisher) = UPPER(@publisher)
AND UPPER(subscriber) = UPPER(@subscriber)
END
END
-- at this point if security_mode is NULL or 1 set the values
IF @subscriber_security_mode IS NULL
OR @subscriber_security_mode = 1
BEGIN
SELECT @subscriber_security_mode = 1,
@subscriber_login = N'',
@subscriber_password = @junk_password
END
/*
* Remove/Update command-line parameters
*/
-- remove the publisher login parameters from the command-line
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'PublisherSecurityMode')
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'PublisherLogin')
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'PublisherPassword')
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'PublisherEncryptedPassword')
-- remove the subscriber login parameters from the command-line
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'SubscriberSecurityMode')
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'SubscriberLogin')
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'SubscriberPassword')
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'SubscriberEncryptedPassword')
-- remove the distributor login parameters from the command-line
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'DistributorLogin')
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'DistributorPassword')
SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'DistributorEncryptedPassword')
-- ensure that the DistributorSecurityMode on command-line is 1
SELECT @command_line = sys.fn_updateparameterwithargument(@command_line, N'DistributorSecurityMode', N'1')
/*
* Update meta-data table and jobstep command-line
*/
UPDATE MSmerge_agents
SET publisher_security_mode = @publisher_security_mode,
publisher_login = @publisher_login,
publisher_password = @publisher_password,
subscriber_security_mode = @subscriber_security_mode,
subscriber_login = @subscriber_login,
subscriber_password = @subscriber_password,
job_step_uid = @job_step_uid
WHERE id = @agent_id
UPDATE msdb.dbo.sysjobsteps
SET command = @command_line,
database_name = DB_NAME()
WHERE job_id = @job_id
AND (step_uid = @job_step_uid
OR step_id = @job_step_id)
FETCH cursorMergeAgents into @agent_id, @publisher, @subscriber, @job_id, @job_step_id, @job_step_uid, @command_line
END
CLOSE cursorMergeAgents
DEALLOCATE cursorMergeAgents
END
COMMIT TRANSACTION tr_upgrade_distdb
RETURN 0
UNDO:
ROLLBACK TRANSACTION tr_upgrade_distdb
COMMIT TRANSACTION
RETURN 1
END
