Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSupgrade_distdb_security_metadata

  No additional text.


Syntax
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

 
Last revision 2008RTM
See also

  sp_vupgrade_distdb (Procedure)
sp_vupgrade_replsecurity_metadata (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash