May 7, 2012

sp_MSchange_merge_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_merge_agent_properties(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, nvarchar @subscriber
, nvarchar @subscriber_db
, nvarchar @property
, nvarchar @value)

MetaData:

   
--
-- Name:
-- sp_MSchange_merge_agent_properties
--
-- Description:
-- Update merge 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_merge_agent_properties
(
@publisher sysname,
@publisher_db sysname,
@publication sysname,
@subscriber sysname,
@subscriber_db sysname,
@property sysname,
@value nvarchar(524) -- if a password it should be encrypted
)
as
begin
set nocount on

declare @retcode int,
@publisher_id int,
@agent_id int,
@security_mode smallint,
@publication_id int,
@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)

-- retrieve the agent id
select @agent_id = id,
@job_id = convert(uniqueidentifier, job_id),
@job_step_uid = job_step_uid
from dbo.MSmerge_agents
where publisher_id = @publisher_id
and publisher_db = @publisher_db
and publication = @publication
and UPPER(subscriber_name) = UPPER(@subscriber)
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 = 14
and sjs.subsystem = 'Merge'
and sjs.database_name = db_name()
end

-- get publication id
select @publication_id = publication_id
from dbo.MSpublications
where publisher_id = @publisher_id
and publisher_db = @publisher_db
and publication = @publication

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

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)
return 1
end

select @security_mode = cast(@value as smallint)

if @security_mode = 1
begin
select @value = newid()

exec @retcode = sys.sp_MSreplencrypt @value OUTPUT
if @@error <> 0 or @retcode <> 0
return 1

update dbo.MSmerge_agents
set subscriber_security_mode = 1,
subscriber_login = N'',
subscriber_password = @value
where id = @agent_id
if @@error <> 0
return 1
end
else if @security_mode = 0
begin
update dbo.MSmerge_agents
set subscriber_security_mode = 0
where id = @agent_id
if @@error <> 0
return 1
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')
return 1
end
end
else if @property = N'subscriber_login'
begin
update dbo.MSmerge_agents
set subscriber_login = @value
where id = @agent_id
if @@error <> 0
return 1
end
else if @property = N'subscriber_password'
begin
exec @retcode = sys.sp_MSreplencrypt @value OUTPUT
if @@error <> 0 or @retcode <> 0
return 1

update dbo.MSmerge_agents
set subscriber_password = @value
where id = @agent_id
if @@error <> 0
return 1
end
else if @property = N'publisher_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)
return 1
end

select @security_mode = cast(@value as smallint)

if @security_mode = 1
begin
select @value = newid()

exec @retcode = sys.sp_MSreplencrypt @value OUTPUT
if @@error <> 0 or @retcode <> 0
return 1

update dbo.MSmerge_agents
set publisher_security_mode = 1,
publisher_login = N'',
publisher_password = @value
where id = @agent_id
if @@error <> 0
return 1
end
else if @security_mode = 0
begin
update dbo.MSmerge_agents
set publisher_security_mode = 0
where id = @agent_id
if @@error <> 0
return 1
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')
return 1
end
end
else if @property = N'publisher_login'
begin
update dbo.MSmerge_agents
set publisher_login = @value
where id = @agent_id
if @@error <> 0
return 1
end
else if @property = N'publisher_password'
begin
exec @retcode = sys.sp_MSreplencrypt @value OUTPUT
if @@error <> 0 or @retcode <> 0
return 1

update dbo.MSmerge_agents
set publisher_password = @value
where id = @agent_id
if @@error <> 0
return 1
end
else if @property = N'merge_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
return 1
end
else if @property = N'merge_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
return 1
end
else if @property = N'description'
begin
UPDATE dbo.MSmerge_subscriptions
SET description = @value
where publisher_id = @publisher_id and
publisher_db = @publisher_db and
publication_id = @publication_id and
UPPER(subscriber) = UPPER(@subscriber) and
subscriber_db = @subscriber_db
if @@error <> 0
return 1
end
else
begin
-- "Invalid property name '@property'."
raiserror (21348, 16, -1, @property)
return 1
end

return 0
end

No comments:

Post a Comment

Total Pageviews