May 7, 2012

sp_MSchange_distribution_agent_properties (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
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

Total Pageviews