June 7, 2012

sp_replmonitorhelpmergesubscriptionmoreinfo (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_replmonitorhelpmergesubscriptionmoreinfo(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, nvarchar @subscriber
, nvarchar @subscriber_db)

MetaData:

   
create procedure sys.sp_replmonitorhelpmergesubscriptionmoreinfo(
@publisher sysname, -- Publisher server --
@publisher_db sysname, -- Publisher database --
@publication sysname, -- Publication name --
@subscriber sysname, -- Subscriber server --
@subscriber_db sysname -- Subscription database --
)AS
begin
declare @publisher_id smallint
--
-- security check
-- only replmonitor can execute this
--
if not (is_member(N'db_owner') = 1 or isnull(is_member(N'replmonitor'),0) = 1)
begin
raiserror(14260, 16, -1)
return (1)
end

select @publisher_id = srvid from master.dbo.sysservers where
UPPER(srvname) = UPPER(@publisher)

if @@rowcount < 1 or @@error <> 0
begin
raiserror(25002, 16, -1)
return (1)
end

select sub.subscriber_db as SubscriberDatabase,
@publisher+'.'+@publisher_db as PublicationLocation,
sub.subscription_type as SubscriptionType,
case when sub.subscription_type=1 then @publisher else UPPER(servers.srvname) end as MergeAgentLocation,
agents.name as AgentName,
sys.fn_add_units_to_date(pub.retention, pub.retention_period_unit, sessions.start_time) as TimeToExpire,
(
select ss1.start_time
from dbo.MSmerge_sessions ss1
where ss1.agent_id=agents.id and ss1.runstatus=8 -- REPL_STATUS_VALIDATION_PASS
and ss1.timestamp = (
select max(ss2.timestamp)
from dbo.MSmerge_sessions ss2
where ss2.agent_id=agents.id and ss2.runstatus=8)) as LastSuccessfullValidationTime,
(
select ss1.start_time
from dbo.MSmerge_sessions ss1
where ss1.agent_id=agents.id and ss1.runstatus in (7,8)
and ss1.timestamp = (
select max(ss2.timestamp)
from dbo.MSmerge_sessions ss2
where ss2.agent_id=agents.id and ss2.runstatus in (7,8))) as LastAttemptedValidationTime
from dbo.MSpublications pub,
dbo.MSmerge_subscriptions sub,
master.dbo.sysservers servers,
dbo.MSmerge_agents agents,
dbo.MSmerge_sessions sessions,
(
select agent_id, max(start_time) as maxtime
from MSmerge_sessions group by agent_id
) as latest
where pub.publisher_id=@publisher_id and
pub.publication=@publication and
pub.publisher_db=@publisher_db and
pub.publication_type=2 and -- merge
sub.publisher_id=pub.publisher_id and
sub.publisher_db=pub.publisher_db and
sub.publication_id=pub.publication_id and
UPPER(sub.subscriber)=UPPER(@subscriber) and
sub.subscriber_db=@subscriber_db and
agents.publisher_id=@publisher_id and
agents.publisher_db=pub.publisher_db and
agents.publication=pub.publication and
UPPER(agents.subscriber_name)=UPPER(sub.subscriber) and
agents.subscriber_db=sub.subscriber_db and
sessions.timestamp = (select max(timestamp)from dbo.MSmerge_sessions where agent_id=agents.id) and
sessions.agent_id=agents.id and
sessions.agent_id=latest.agent_id and
latest.maxtime=sessions.start_time and
UPPER(servers.srvname) <> 'REPL_DISTRIBUTOR'
order by agents.name
end

No comments:

Post a Comment

Total Pageviews