Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSchange_distribution_agent_properties

  No additional text.


Syntax


-- Name:
--		sp_MSchange_distribution_agent_properties

-- Description:
--		Update distribution agent properties..

-- Parameters:
--	 	See the procedure definition.

-- Returns:
--		0 - succeeded
--      1 - failed

-- Result:
--		None

-- Security:
--		SA
-- Requires Certificate signature for catalog access

create procedure sys.sp_MSchange_distribution_agent_properties
(
	@publisher 		sysname,
	@publisher_db	sysname,
	@publication	sysname,
	@subscriber		sysname,
	@subscriber_db	sysname,
	@property		sysname,
	@value			nvarchar(524)	-- if a password it should NOT be encrypted
)
as
begin
	set nocount on

	declare @retcode 		int,
			@publisher_id	int,
			@subscriber_id	int,
			@agent_id		int,
			@security_mode 	smallint,
			@job_id			uniqueidentifier,
			@job_step_uid	uniqueidentifier
	
	-- should only be called by the admin link
	if is_srvrolemember('sysadmin') <> 1
	begin
		raiserror (14126, 16, -1)
		return 1
	end
	
	-- retrieve server ids
	select @publisher_id = srvid
		from master..sysservers
		where upper(srvname) = upper(@publisher)
	
	select @subscriber_id = srvid
		from master..sysservers
		where upper(srvname) = upper(@subscriber)
		
	-- retrieve the agent id
	select @agent_id = id,
			@job_id = convert(uniqueidentifier, job_id),
			@job_step_uid = job_step_uid
		from MSdistribution_agents
		where publisher_id = @publisher_id
			and publisher_db = @publisher_db
			and (publication = @publication
					or lower(publication) = N'all')
			and subscriber_id = @subscriber_id
			and subscriber_db = @subscriber_db

	-- retrieve the job_step_uid if not set
    if @job_id is NOT NULL
    	and @job_step_uid is NULL
    begin
		select @job_step_uid = sjs.step_uid
		from msdb.dbo.sysjobs_view as sjv
			join msdb.dbo.sysjobsteps as sjs
				on sjv.job_id = sjs.job_id
		where sjv.job_id = @job_id
			and sjv.master_server = 0
			and UPPER(sjv.originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))
			and sjv.category_id = 10
			and sjs.subsystem = 'Distribution'
			and sjs.database_name = db_name()
    end

	-- if the property is not in the list we return an error at the end
	select @property = lower(rtrim(ltrim(@property)))

	begin transaction tr_sp_change_dist_agent
	save transaction tr_sp_change_dist_agent
	
	-- update the agents table
	if @property = N'subscriber_security_mode'
	begin
		if isnumeric(@value) = 0
		begin
			-- '@value' is not a valid value for the 'subscriber_security_mode' parameter. The value must be 0 or 1.
			raiserror (21406, 16, -1, @value, @property)
			goto FAILURE
		end
		
		select @security_mode = cast(@value as smallint)

		if @security_mode = 1
		begin
			select @value = newid()
			
			-- must encrypt it prior to update
			exec @retcode = sys.sp_MSreplencrypt @value OUTPUT
	    	if @@error <> 0 or @retcode <> 0
	    		goto FAILURE
	    		
			update MSdistribution_agents
				set subscriber_security_mode = 1,
					subscriber_login = N'',
					subscriber_password = @value
				where id = @agent_id
			if @@error <> 0
				goto FAILURE
		end
		else if @security_mode = 0
		begin
			update MSdistribution_agents
				set subscriber_security_mode = 0
				where id = @agent_id
			if @@error <> 0
				goto FAILURE
		end
		else
		begin
			-- "'@value' is not a valid value for the '@property' parameter. The value must be 0 or 1."
			raiserror (21406, 16, -1, @value, @property)
			goto FAILURE
		end
	end
	else if @property = N'subscriber_login'
	begin
		update MSdistribution_agents
			set subscriber_login = @value
			where id = @agent_id
		if @@error <> 0
			goto FAILURE
	end
	else if @property = N'subscriber_password'
	begin
		-- must encrypt it prior to update
		exec @retcode = sys.sp_MSreplencrypt @value OUTPUT
    	if @@error <> 0 or @retcode <> 0
    		goto FAILURE

		-- password provided to this procedure should already be encrypted
		update MSdistribution_agents
			set subscriber_password = @value
			where id = @agent_id
		if @@error <> 0
			goto FAILURE
	end
	else if @property = N'distrib_job_login'
	begin
		-- update the Proxy Account login
		exec @retcode = sys.sp_MSchange_repl_job @id = @job_id,
												@step_uid = @job_step_uid,
												@login = @value
		if @@error != 0 or @retcode != 0
			goto FAILURE
	end
	else if @property = N'distrib_job_password'
	begin
		-- update the Proxy Account password
		exec @retcode = sys.sp_MSchange_repl_job @id = @job_id,
												@step_uid = @job_step_uid,
												@password = @value
		if @@error != 0 or @retcode != 0
			goto FAILURE
	end
	else if @property = N'subscriptionstreams'
	begin
		declare @subscriptionstreams tinyint
		set @subscriptionstreams = cast (@value as tinyint)
		if (@subscriptionstreams < 1 or @subscriptionstreams > 64)
		begin
			RAISERROR(14198, 16, -1, '@subscriptionstreams', '1..64')
			goto FAILURE
		end
		update MSdistribution_agents
			set subscriptionstreams = @subscriptionstreams
			where id = @agent_id
		if @@error <> 0
			goto FAILURE
	end
	else if @property = N'subscriber_type'
	begin
		declare @subscriber_type tinyint
		set @subscriber_type = cast (@value as tinyint)
		if @subscriber_type NOT IN ( 0, 1, 3)
		begin
			RAISERROR(14197, 16, -1, '@subscriber_type', '0, 1, 3')
			goto FAILURE
		end
		update MSdistribution_agents
			set subscriber_type = @subscriber_type
			where id = @agent_id
		if @@error <> 0
			goto FAILURE
	end
	else if @property = N'subscriber_provider'
	begin
		update MSdistribution_agents
			set subscriber_provider = @value
			where id = @agent_id
		if @@error <> 0
			goto FAILURE
	end
	else if @property = N'subscriber_datasource'
	begin
		update MSdistribution_agents
			set subscriber_datasrc = @value
			where id = @agent_id
		if @@error <> 0
			goto FAILURE
	end
	else if @property = N'subscriber_providerstring'
	begin
		update MSdistribution_agents
			set subscriber_provider_string = @value
			where id = @agent_id
		if @@error <> 0
			goto FAILURE
	end
	else if @property = N'subscriber_location'
	begin
		update MSdistribution_agents
			set subscriber_location = @value
			where id = @agent_id
		if @@error <> 0
			goto FAILURE
	end
	else if @property = N'subscriber_catalog'
	begin
		update MSdistribution_agents
			set subscriber_catalog = @value
			where id = @agent_id
		if @@error <> 0
			goto FAILURE
	end
	else
	begin
		-- "Invalid property name '@property'."
		raiserror (21348, 16, -1, @property)
		goto FAILURE
	end

	commit transaction tr_sp_change_dist_agent

	return 0
	
FAILURE:
	rollback transaction tr_sp_change_dist_agent
	commit transaction tr_sp_change_dist_agent

	return 1
end

 
Last revision 2008RTM
See also

  sp_MSrepl_addsubscription_article (Procedure)
sp_MSrepl_changesubscription (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