May 16, 2012

sp_MShelp_publication (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_publication(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication)

MetaData:

      
CREATE PROCEDURE sys.sp_MShelp_publication (
@publisher sysname,
@publisher_db sysname = '%',
@publication sysname = '%')
as
BEGIN
set nocount on
declare @queued_agent sysname
declare @retcode int
declare @pubid int

SELECT @retcode = 0

--
-- Create a temp table of pubids identifying publications that the current user has access to
--
CREATE TABLE #accessiblepubs (pubid int)

DECLARE hC CURSOR LOCAL FAST_FORWARD FOR
SELECT publication_id FROM MSpublications WHERE ((@publication = N'%') or (publication = @publication))
OPEN hC
FETCH hC INTO @pubid
WHILE (@@fetch_status <> -1)
BEGIN
IF is_member(N'db_owner') <> 1 AND isnull(is_member(N'replmonitor'),0) <> 1
BEGIN
exec @retcode = sys.sp_MScheck_pull_access
@publication_id = @pubid,
@raise_fatal_error = 0
END
IF (is_member(N'db_owner') = 1) OR
(isnull(is_member(N'replmonitor'),0) = 1) OR
(@retcode = 0 AND @@error = 0)
INSERT INTO #accessiblepubs values(@pubid)

FETCH hC INTO @pubid
END
CLOSE hC
DEALLOCATE hC

if @publisher_db != '%' and @publication != '%' and not exists (select * from MSpublications where
publisher_id = (select srvid from master.dbo.sysservers where UPPER(srvname collate database_default) = UPPER(@publisher)) and
((@publisher_db = N'%') or (publisher_db = @publisher_db)) and
publication = @publication)
begin
raiserror(20026, 16, -1, @publication)
return(1)
end

select @queued_agent = name from MSqreader_agents

-- need to add new columns:
-- num_subscriptions, num_now_syncing, num_errors, num_warning
-- highest_perf_threshold, lowest_perf_threshold,
--
declare @currentdate datetime
set @currentdate=getdate()

declare @dymanic_info table(
publication_id int primary key,
num_subscriptions int,
num_now_syncing int,
num_errors int,
num_warning int,
merge_RunFastDurationThreshold int,
merge_SyncExpireTimeThreshold int,
WorstWarningDuration int,
WorstWarningExpiration int
);

-- first we select all the values returned from the functions calls
-- because other wise they will be called for every row maching the main query.
insert into @dymanic_info
select p.publication_id,
sys.fn_replgetNumMergesubscriptionsOnPublication(p.publisher_id, p.publisher_db, p.publication, p.publication_id) as num_subscriptions,
sys.fn_replgetNumMergesubscriptionsNowSyncingOnPublication(p.publisher_id, p.publisher_db, p.publication, p.publication_id) as num_now_syncing,
sys.fn_replgetNumOfErrorsMergesubscriptionsOnPublication(p.publisher_id, p.publisher_db, p.publication, p.publication_id) as num_errors,
sys.fn_replgetNumOfWarningsMergesubscriptionsOnPublication(
p.publisher_id,
p.publisher_db,
p.publication,
p.publication_id,
p.retention,
convert(int, sys.fn_replgetpublicationthreshold(p.publication_id, 4)),
convert(int, sys.fn_replgetpublicationthreshold(p.publication_id, 5)),
@currentdate
) as num_warning,
convert(int, sys.fn_replgetpublicationthreshold(p.publication_id, 5)) as merge_RunFastDurationThreshold,
sys.fn_replgetWorstWarningDurationMergesubscriptionsOnPublication(
p.publisher_id,
p.publisher_db,
p.publication,
p.publication_id) as WorstWarningDuration,
convert(int, sys.fn_replgetpublicationthreshold(p.publication_id, 4)) as merge_SyncExpireTimeThreshold,
sys.fn_replgetWorstWarningExpirationMergesubscriptionsOnPublication(
p.publisher_id,
p.publisher_db,
p.publication,
p.publication_id,
@currentdate) as WorstWarningExpiration
from MSpublications p
where
p.publisher_id = (select srvid from master.dbo.sysservers where UPPER(srvname collate database_default) = UPPER(@publisher))
and ((@publisher_db = N'%') or (p.publisher_db = @publisher_db)) and ((@publication = N'%') or (p.publication = @publication))




select p.publisher_db as publisher_db, p.publication as publication, p.publication_id as publication_id,
p.publication_type as publication_type,
-- thirdparty_flag,
independent_agent as independent_agent,
immediate_sync as immediate_sync, allow_push as allow_push,
allow_pull as allow_pull, allow_anonymous as allow_anonymous, 'snapshot_agent' = s.name,
'logreader_agent' = l.name, p.description as description, vendor_name as vendor_name
-- queue reader agent only if we have valid queued subscription
,'queuereader agent' = case
when (exists (select * from MSsubscriptions sub where sub.publisher_id = p.publisher_id and
sub.publisher_db = p.publisher_db and sub.publication_id = p.publication_id and
(sub.update_mode in (2,3,4,5,6,7))))
then @queued_agent else NULL end
,'thirdparty_options' = thirdparty_options
,'snapshot_jobid' = j.job_id
-- dynamic columns for repl monitor
,d.num_subscriptions as num_subscriptions
,d.num_now_syncing as num_now_syncing
,d.num_errors as num_errors
,d.num_warning as num_warning
,d.merge_RunFastDurationThreshold as merge_RunFastDurationThreshold
,d.WorstWarningDuration as WorstWarningDuration
,d.merge_SyncExpireTimeThreshold as merge_SyncExpireTimeThreshold
,p.retention as retention
,d.WorstWarningExpiration as WorstWarningExpiration
from #accessiblepubs a
JOIN @dymanic_info d
ON
d.publication_id = a.pubid

LEFT OUTER JOIN MSpublications p
ON
p.publication_id = a.pubid
LEFT OUTER JOIN MSsnapshot_agents s
ON
s.publisher_id = p.publisher_id and
s.publisher_db = p.publisher_db and
s.publication = p.publication
LEFT OUTER JOIN MSlogreader_agents l
ON
(l.publisher_id = p.publisher_id and
l.publisher_db = p.publisher_db and
p.publication_type = 0 and -- Only Transactional Publication has a Log Reader
(l.publication = p.publication OR
thirdparty_flag = 0))
LEFT OUTER JOIN msdb.dbo.sysjobs_view j
ON
s.job_id = j.job_id
where
p.publisher_id = (select srvid from master.dbo.sysservers where UPPER(srvname collate database_default) = UPPER(@publisher)) and
((@publisher_db = N'%') or (p.publisher_db = @publisher_db)) and
((@publication = N'%') or (p.publication = @publication))
order by p.publication

END

No comments:

Post a Comment

Total Pageviews