May 7, 2012

sp_MSadd_tracer_history (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_MSadd_tracer_history(int @tracer_id)

MetaData:

 --   
-- Name:
-- sp_MSadd_tracer_history
--
-- Description:
-- The children history rows (MStracer_history) track the amount
-- of time it took the distribution agent to retrieve the trace record
-- from the distribution db and apply at the subscriber. This procedure will
-- also insert a single row for each active subscription. In the anonymous case,
-- the subscription must have sync'd once for a row to be added. The row will
-- contain a null value for the subscriber_commit time until the distrib agent
-- applies the tracer token at the subscriber.
--
-- Parameters:
-- @tracer_id int newly inserted tracer token id
-- @subscribers_found bit output output val specifying whether or not subscribers were found
--
-- Returns:
-- 0 - succeeded
-- 1 - failed
--
-- Result:
-- None
--
-- Security:
-- Sysadmin (never run by dist agent)
--
create procedure sys.sp_MSadd_tracer_history
(
@tracer_id int
)
as
begin
set nocount on

declare @retcode int,
@publication_id int,
@agent_id int

--
-- security check
-- only sysadmin can execute this
--
if (isnull(is_srvrolemember('sysadmin'),0) = 0)
begin
raiserror(21089, 16, -1)
return (1)
end

select @retcode = 0,
@publication_id = NULL,
@agent_id = 0

-- retrieve the publication id to be used in the cursor for subscriptions
select @publication_id = publication_id
from MStracer_tokens
where tracer_id = @tracer_id

-- ensure that the given tracer_id is valid
if @publication_id is NULL
begin
-- we will not raiserror here because the caller will
return 1
end

begin transaction tr_sp_MSadd_tracer_history
save transaction tr_sp_MSadd_tracer_history

--
* Now insert a row in the tracer subscribers history table so that we
* only display information for the currently active subscriptions
--

-- we will insert a new history row for every active agent
declare add_tracer_sub_cursor cursor fast_forward for
select agent_id
from sys.fn_activedistributionagentids(@publication_id)

open add_tracer_sub_cursor

fetch next from add_tracer_sub_cursor into @agent_id

while (@@fetch_status <> -1)
begin
insert into MStracer_history
(
parent_tracer_id,
agent_id
)
values
(
@tracer_id,
@agent_id
)
if @@error <> 0
begin
goto Err_Handler
end

fetch next from add_tracer_sub_cursor into @agent_id
end

close add_tracer_sub_cursor
deallocate add_tracer_sub_cursor

commit transaction tr_sp_MSadd_tracer_history

return 0

Err_Handler:
close add_tracer_sub_cursor
deallocate add_tracer_sub_cursor

rollback transaction tr_sp_MSadd_tracer_history
commit transaction

return 1
end

No comments:

Post a Comment

Total Pageviews