May 16, 2012

sp_MShelp_repl_agent (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_MShelp_repl_agent(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, nvarchar @subscriber
, nvarchar @subscriber_db
, int @agent_type)

MetaData:

 create procedure sys.sp_MShelp_repl_agent  
(
@publisher sysname,
@publisher_db sysname,
@publication sysname = NULL,
@subscriber sysname = NULL,
@subscriber_db sysname = NULL,
@agent_type int -- 0: Distribution 1: ReplMerge 2: Logreader 3: Queued
)
as
begin
set nocount on

declare @retcode int,
@publisher_id int,
@subscriber_id int,
@publisher_security_mode int,
@publisher_login sysname,
@publisher_password nvarchar(524),
@subscriber_security_mode int,
@subscriber_login sysname,
@subscriber_password nvarchar(524)

-- make sure this is a replication agent.
if sessionproperty('replication_agent') != 1
begin
-- "You do not have sufficient permission to run this command."
raiserror(14260, 16, -1)
return 1
end

-- sysadmin or db_owner have access
if is_srvrolemember('sysadmin') != 1
and is_member('db_owner') != 1
begin
-- "You do not have sufficient permission to run this command."
raiserror(14260, 16, -1)
return 1
end

-- Has to be executed from distribution database
if sys.fn_MSrepl_isdistdb (db_name()) != 1
begin
-- "sp_MShelp_repl_agent can only be executed in the distribution database."
raiserror (21482, 16, -1, 'sp_MShelp_repl_agent', 'distribution')
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)

select @subscriber_security_mode = 1,
@subscriber_login = NULL,
@subscriber_password = NULL,
@publisher_security_mode = 1,
@publisher_login = NULL,
@publisher_password = NULL

-- Distribution and Queued Agent
if @agent_type in (0, 3)
begin
select msda.subscriber_security_mode,
msda.subscriber_login,
sys.fn_repldecryptver4(msda.subscriber_password)
from MSdistribution_agents msda
where msda.publisher_id = @publisher_id
and msda.publisher_db = @publisher_db
and (msda.publication = @publication
or lower(msda.publication) = N'all')
and msda.subscriber_id = @subscriber_id
and msda.subscriber_db = @subscriber_db
if @@error <> 0 return 1
end
-- Merge Agent
else if @agent_type = 1
begin
select msma.subscriber_security_mode,
msma.subscriber_login,
sys.fn_repldecryptver4(msma.subscriber_password),
msma.publisher_security_mode,
msma.publisher_login,
sys.fn_repldecryptver4(msma.publisher_password)
from dbo.MSmerge_agents msma
where msma.publisher_id = @publisher_id
and msma.publisher_db = @publisher_db
and msma.publication = @publication
and UPPER(msma.subscriber_name) = UPPER(@subscriber)
and msma.subscriber_db = @subscriber_db
if @@error <> 0 return 1
end
-- Logreader Agent
else if @agent_type = 2
begin
-- retrieve the publisher server and database name
select @publisher_security_mode = msla.publisher_security_mode,
@publisher_login = msla.publisher_login,
@publisher_password = sys.fn_repldecryptver4(msla.publisher_password)
from MSlogreader_agents msla
where msla.publisher_id = @publisher_id
and msla.publisher_db = @publisher_db
if @@error <> 0 return 1

select @publisher_security_mode,
@publisher_login,
@publisher_password
end

return 0
end

No comments:

Post a Comment

Total Pageviews