June 7, 2012

sp_replmonitorhelppublicationthresholds (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_replmonitorhelppublicationthresholds(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, int @publication_type
, nvarchar @thresholdmetricname)

MetaData:

 create procedure sys.sp_replmonitorhelppublicationthresholds   
(
@publisher sysname -- cannot be null
,@publisher_db sysname -- cannot be null
,@publication sysname -- cannot be null
,@publication_type int = NULL -- NULL for wildcard, 0 transactional 1 snapshot 2 merge
,@thresholdmetricname sysname = NULL -- NULL for all entries
)
as
begin
set nocount on
declare @retcode int
,@publisher_id int
,@publication_id int

--
-- security check : replmonitor
--
if not (is_member(N'db_owner') = 1 or isnull(is_member(N'replmonitor'),0) = 1)
begin
raiserror(14260, 16, -1)
return (1)
end
--
-- security: Has to be executed from distribution database
--
if sys.fn_MSrepl_isdistdb (db_name()) != 1
begin
raiserror (21482, 16, -1, 'sp_replmonitorhelppublicationthresholds', 'distribution')
return 1
end
--
-- Validate @publisher
--
select @publisher_id = server_id
from sys.servers
where upper(name) = upper(@publisher)
if (@publisher_id is null)
begin
raiserror(21618, 16, -1, @publisher)
return 1
end
--
-- validate publication
--
if not exists (select publication_id
from dbo.MSpublications
where publisher_id = @publisher_id
and publisher_db = @publisher_db
and publication = @publication
and publication_type= case when (@publication_type is null) then publication_type else @publication_type end)
begin
raiserror(20026, 16, 1, @publication)
return 1
end
--
-- validate metric
--
if (@thresholdmetricname is not null)
begin
if not exists (select metric_id
from msdb.dbo.MSreplmonthresholdmetrics
where title = lower(@thresholdmetricname))
begin
raiserror(14200, 16, 2, '@thresholdmetricname')
return 1
end
end
--
-- prepare the resultset to display
--
if @publication_type IS null
select t.metric_id, m.title, cast(t.value as int) as value, t.shouldalert, t.isenabled
from (dbo.MSpublicationthresholds as t join dbo.MSpublications as p
on t.publication_id = p.publication_id
and p.publisher_id = @publisher_id
and p.publisher_db = @publisher_db
and p.publication = @publication)
join
msdb.dbo.MSreplmonthresholdmetrics as m
on t.metric_id = m.metric_id
and m.title = case when (@thresholdmetricname is null) then m.title
else lower(@thresholdmetricname) end
else
select t.metric_id, m.title, cast(t.value as int) as value, t.shouldalert, t.isenabled
from (dbo.MSpublicationthresholds as t join dbo.MSpublications as p
on t.publication_id = p.publication_id
and p.publisher_id = @publisher_id
and p.publisher_db = @publisher_db
and p.publication = @publication and p.publication_type=@publication_type)
join
msdb.dbo.MSreplmonthresholdmetrics as m
on t.metric_id = m.metric_id
and m.title = case when (@thresholdmetricname is null) then m.title
else lower(@thresholdmetricname) end
--
-- return
--
return 0
end

No comments:

Post a Comment

Total Pageviews