June 7, 2012

sp_replmonitorhelpmergesession (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_replmonitorhelpmergesession(nvarchar @agent_name
, int @hours
, int @session_type
, nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication)

MetaData:

 create procedure sys.sp_replmonitorhelpmergesession  
(
@agent_name nvarchar(100)= NULL,
@hours int = 0, -- @hours < 0 will return TOP 100, otherwise look back at only @hours --
@session_type int = 1, -- 1 for succeed/retry sessions, otherwise failure sessions --
@publisher sysname = NULL, -- used to read subscriber-side monitoring tables.
@publisher_db sysname = NULL, -- used to read subscriber-side monitoring tables.
@publication sysname = NULL -- used to read subscriber-side monitoring tables.
)
as
begin
set nocount on

declare @min_time datetime
declare @retcode int
declare @succeed int
declare @running int
declare @retry int
declare @failure int
declare @isdistdb bit
declare @agent_id int
declare @max_session_id int
declare @max_running_session_id int

select @succeed = 2
select @retry = 5
select @failure = 6
select @running = 3

--
-- 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

select @isdistdb = sys.fn_MSrepl_isdistdb(DB_NAME())

if @isdistdb = 1 and @agent_name is null
begin
raiserror(14043, 16, -1, '@agent_name', 'sp_replmonitorhelpmergesession')
return 1
end
else if @isdistdb = 0 and (@publisher is null or @publisher_db is null or @publication is null)
begin
-- subscriber-side monitoring
if @publisher is null
raiserror(14043, 16, -1, '@publisher', 'sp_replmonitorhelpmergesession')
if @publisher_db is null
raiserror(14043, 16, -1, '@publisher_db', 'sp_replmonitorhelpmergesession')
if @publication is null
raiserror(14043, 16, -1, '@publication', 'sp_replmonitorhelpmergesession')
return 1
end

if @isdistdb = 1
begin

select @agent_id = id from dbo.MSmerge_agents where name = @agent_name

select top 1 @max_session_id = session_id from dbo.MSmerge_sessions with (NOLOCK)
where agent_id = @agent_id
order by session_id desc

select @max_running_session_id = @max_session_id

select top 1 @max_running_session_id = session_id from dbo.MSmerge_sessions with (NOLOCK)
where agent_id = @agent_id and runstatus = @running
order by session_id desc

IF @hours < 0
BEGIN
select top 100
sessions.session_id as Session_id,
case when sessions.runstatus = 3 and sessions.session_id <> @max_running_session_id and sys.fn_replmerge_get_errormsgcounts(sessions.session_id) <> 0 then @failure
when sessions.runstatus = 3 and sessions.session_id <> @max_running_session_id and sys.fn_replmerge_get_errormsgcounts(sessions.session_id) = 0 then @succeed
when sessions.runstatus is null then 0 else sessions.runstatus end as Status,
sessions.start_time as StartTime,
sessions.end_time as EndTime,
sessions.duration as Duration,
sys.fn_replmerge_get_cmdcounts(sessions.session_id, 1) as UploadedCommands,
sys.fn_replmerge_get_cmdcounts(sessions.session_id, 2) as DownloadedCommands,
sys.fn_replmerge_get_errormsgcounts(sessions.session_id) as ErrorMessages,
(
select top 1 re.id from dbo.MSrepl_errors re
where re.session_id = sessions.session_id
order by re.id desc
) as ErrorID,
sessions.percent_complete as PercentageDone,
sessions.time_remaining as TimeRemaining,
sessions.current_phase_id as CurrentPhase,
(
select top 1 rh.comments from dbo.MSmerge_history rh
where rh.session_id = sessions.session_id
order by rh.session_id desc, rh.timestamp desc
) as LastMessage,
-- IsSpidActive = case when exists (select * from sys.dm_exec_sessions sp where sp.login_time = sessions.spid_login_time
-- and sp.session_id = sessions.spid) then 1 else 0 end
IsSpidActive = 1
from dbo.MSmerge_sessions sessions
where sessions.agent_id = @agent_id
and sessions.session_id <= @max_session_id
and
(
(
@session_type = 1 and
(
sessions.runstatus = @succeed
or sessions.runstatus = @retry
or sessions.runstatus = @running
or sessions.session_id = @max_session_id
)
)
or sessions.runstatus = @failure
)
order by sessions.start_time desc
END
ELSE
BEGIN
IF @hours = 0
BEGIN
select @min_time = NULL
END
ELSE
BEGIN
select @min_time = dateadd(hour, -@hours, getdate())
END
-- phase is ReconcilerMsg.lPhase: UPLOAD_PHASE as 2 and DOWNLOAD_PHASE as 3
-- dbo.MSmerge_articlehistory.phase_id
select
sessions.session_id as Session_id,
case when sessions.runstatus = 3 and sessions.session_id <> @max_running_session_id and sys.fn_replmerge_get_errormsgcounts(sessions.session_id) <> 0 then @failure
when sessions.runstatus = 3 and sessions.session_id <> @max_running_session_id and sys.fn_replmerge_get_errormsgcounts(sessions.session_id) = 0 then @succeed
when sessions.runstatus is null then 0 else sessions.runstatus end as Status,
sessions.start_time as StartTime,
sessions.end_time as EndTime,
sessions.duration as Duration,
sys.fn_replmerge_get_cmdcounts(sessions.session_id, 1) as UploadedCommands,
sys.fn_replmerge_get_cmdcounts(sessions.session_id, 2) as DownloadedCommands,
sys.fn_replmerge_get_errormsgcounts(sessions.session_id) as ErrorMessages,
(
select top 1 re.id from dbo.MSrepl_errors re
where re.session_id = sessions.session_id
order by re.id desc
) as ErrorID,
sessions.percent_complete as PercentageDone,
sessions.time_remaining as TimeRemaining,
sessions.current_phase_id as CurrentPhase,
(
select top 1 rh.comments from dbo.MSmerge_history rh
where rh.session_id = sessions.session_id
order by rh.session_id desc, rh.timestamp desc
) as LastMessage,
-- IsSpidActive = case when exists (select * from sys.dm_exec_sessions sp where sp.login_time = sessions.spid_login_time
-- and sp.session_id = sessions.spid) then 1 else 0 end
IsSpidActive = 1
from dbo.MSmerge_sessions sessions
where sessions.agent_id = @agent_id
and sessions.session_id <= @max_session_id
and
(
(
@session_type = 1 and
(
sessions.runstatus = @succeed
or sessions.runstatus = @retry
or sessions.runstatus = @running
or sessions.session_id = @max_session_id
)
)
or sessions.runstatus = @failure
)
and (sessions.end_time >= @min_time or @min_time IS NULL)
order by sessions.start_time desc
END
end
else
begin
exec @retcode = sys.sp_MShelp_subscriberside_history
@hours=@hours, @session_type=@session_type, @publisher = @publisher, @publisher_db = @publisher_db, @publication = @publication
if @@error <> 0 or @retcode <> 0
return 1
end

return 0
end

No comments:

Post a Comment

Total Pageviews