The meta data is from an SQL 2012 Server.
I have posted alot more, find the whole list here.
Goto Definition or MetaData
Definition:
sys.sp_MSchange_distribution_agent_properties(nvarchar @publisher, nvarchar @publisher_db
, nvarchar @publication
, nvarchar @subscriber
, nvarchar @subscriber_db
, nvarchar @property
, nvarchar @value)
MetaData:
-- -- 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
No comments:
Post a Comment