May 16, 2012

sp_MShelp_replication_status (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_replication_status(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, int @agent_type
, bit @exclude_anonymous)

MetaData:

 create procedure sys.sp_MShelp_replication_status  
(
@publisher sysname = '%',
@publisher_db sysname = '%',
@publication sysname = '%',
@agent_type int = 0,
@exclude_anonymous bit = 0
)
as
begin
set nocount on
declare @return_code int,
@return_status int,
@return_timestamp datetime,
@alt_return_status int,
@alt_return_timestamp datetime,
@cmd varchar(4000),
@db_name sysname,
@distbit int,
@allow_queued_tran bit,
@anonymous_mask int,
@anonymous_hide int,
@tran_pubtype int,
@snapshot_agent int,
@logreader_agent int,
@distrib_agent int,
@merge_agent int,
@queue_agent int

select @return_code = 0,
@return_status = 0,
@return_timestamp = 0,
@alt_return_status = 0,
@alt_return_timestamp = 0,
@cmd = null,
@db_name = null,
@distbit = 16,
@allow_queued_tran = 0,
@anonymous_mask = 0x80000000,
@tran_pubtype = 0,
@snapshot_agent = 1,
@logreader_agent = 2,
@distrib_agent = 3,
@merge_agent = 4,
@queue_agent = 9

-- Security check
if not((is_srvrolemember('sysadmin') = 1) or (is_member ('db_owner') = 1) or (isnull(is_member('replmonitor'), 0) = 1))
begin
goto Err_Handler
end

-- do some parameter checking

-- @agent_type is only supported with all wildcards
if @agent_type <> 0 and (@publisher <> '%' or @publisher_db <> '%' or @publication <> '%')
begin
goto Err_Handler
end

-- if no distribution dbs then exit
if not exists(select name from master.dbo.sysdatabases where category & @distbit <> 0 and has_dbaccess(name) = 1)
begin
goto Done
end

select @anonymous_hide = case when (@exclude_anonymous = 0) then 0x00000000 else @anonymous_mask end

-- drop the temp table
if object_id('tempdb.dbo.#tmp_replication_status') is not null
begin
drop table #tmp_replication_status
end

-- create the temp table
create table #tmp_replication_status
(
publisher sysname,
publisher_db sysname,
publication sysname,
publication_type int,
agent_type int,
status int,
agent_id int,
agent_name sysname,
job_id uniqueidentifier null,
time_stamp datetime null,
publisher_srvid int null
)

-- load tmp replication_status table
exec @return_code = sys.sp_MSload_tmp_replication_status @agent_type = @agent_type
if @@error <> 0 or @return_code <> 0
goto Err_Handler

-- now lets retrieve the status and timestamp to be returned
if @publisher = '%'
begin
select @return_status = max(status),
@return_timestamp = max(time_stamp)
from #tmp_replication_status
where agent_type & @anonymous_hide = 0
end
else if @publication = '%'
begin
select @return_status = max(status),
@return_timestamp = max(time_stamp)
from #tmp_replication_status
where agent_type & @anonymous_hide = 0
and publisher = upper(@publisher)
-- process queued agents
select @db_name = distribution_db
from msdb.dbo.MSdistpublishers
where name = @publisher collate database_default

select @alt_return_status = max(status),
@alt_return_timestamp = max(time_stamp)
from #tmp_replication_status
where agent_type = @queue_agent
and publisher = upper(@@servername)
and publisher_db = @db_name

if @alt_return_status > @return_status
select @return_status = @alt_return_status

if @alt_return_timestamp > @return_timestamp
select @return_timestamp = @alt_return_timestamp
end
else
begin
select @return_status = max(status),
@return_timestamp = max(time_stamp)
from #tmp_replication_status
where agent_type & @anonymous_hide = 0
and publisher = upper(@publisher)
and publisher_db = @publisher_db
and publication = @publication

-- if this is a transactional publication then include logreader info
if exists(select publication_type
from #tmp_replication_status
where agent_type = @snapshot_agent
and publisher = UPPER(@publisher)
and publisher_db = @publisher_db
and publication = @publication
and publication_type = @tran_pubtype)
begin
select @alt_return_status = status,
@alt_return_timestamp = time_stamp
from #tmp_replication_status
where agent_type = @logreader_agent
and publisher = UPPER(@publisher)
and publisher_db = @publisher_db
and publication = 'ALL'

if @alt_return_status > @return_status
select @return_status = @alt_return_status

if @alt_return_timestamp > @return_timestamp
select @return_timestamp = @alt_return_timestamp

-- process the Queue reader entry separately and check if we need to pick them
select @db_name = distribution_db
from msdb.dbo.MSdistpublishers
where name = @publisher collate database_default

select @cmd = quotename(@db_name) + N'.dbo.sp_MSispublicationqueued'
exec @return_code = @cmd
@publisher = @publisher,
@publisher_db = @publisher_db,
@publication = @publication,
@allow_queued_tran = @allow_queued_tran output
if @return_code <> 0 and @@error <> 0
goto Err_Handler

if @allow_queued_tran = 1
begin
select @alt_return_status = max(status),
@alt_return_timestamp = max(time_stamp)
from #tmp_replication_status
where agent_type = @queue_agent
and publisher = upper(@@servername)
and publisher_db = @db_name

if @alt_return_status > @return_status
select @return_status = @alt_return_status

if @alt_return_timestamp > @return_timestamp
select @return_timestamp = @alt_return_timestamp
end
end
end

-- return result set
select 'status' = isnull(@return_status, 0),
'timestamp' = isnull(left(replace(replace(replace(replace(convert(varchar, @return_timestamp, 121), '-', ''), ' ', ''), ':', ''), '.', ''), 16), '0000000000000000')

Done:
return 0

Err_Handler:

return 1
end

No comments:

Post a Comment

Total Pageviews