May 7, 2012

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

MetaData:

 --   
-- Name:
-- sp_MSadd_tracer_token
--
-- Description:
-- This procedure will log the parent tracer record token row (MStracer_tokens).
-- A tracer record parent token row is defined as the record which
-- tracks the amount of time it took from when the tracer was inserted
-- into the log to when the logreader picked it up and inserted it into
-- at the distribution database. We call it the parent because there is
-- there is only one per tracer token inserted into the publisher.
--
-- Parameters:
-- @publisher sysname publisher name
-- @publisher_db sysname publisher database
-- @publication sysname publication
-- @tracer_id int outputs the newly inserted tracer token id
--
-- Returns:
-- 0 - succeeded
-- 1 - failed
--
-- Result:
-- None
--
-- Security:
-- Sysadmin (never run by dist agent)
-- Requires Certificate signature for catalog access
--
create procedure sys.sp_MSadd_tracer_token
(
@publisher sysname,
@publisher_db sysname,
@publication sysname,
@tracer_id int output,
@subscribers_found bit = 0 output
)
as
begin
set nocount on

declare @retcode int,
@publication_id int,
@SNAPSHOT_PUB 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,
@tracer_id = NULL,
@SNAPSHOT_PUB = 1,
@subscribers_found = 0

-- retrieve the publication id and ensure no snapshot pub is selected
select @publication_id = msp.publication_id
from dbo.MSpublications msp
join sys.servers ss
on msp.publisher_id = ss.server_id
where UPPER(ss.name) = UPPER(@publisher)
and msp.publisher_db = @publisher_db
and msp.publication = @publication
and msp.publication_type <> @SNAPSHOT_PUB

if @publication_id is NULL
begin
-- Publication 'publication' does not exist.
raiserror(21200, 16, -1, @publication)
return 1
end

-- check if there are any active distribution agents for.
if not exists(select *
from sys.fn_activedistributionagentids(@publication_id))
begin
return 0
end

-- now we know that we have subscribers
select @subscribers_found = 1

begin transaction tr_sp_MSadd_tracer_token
save transaction tr_sp_MSadd_tracer_token

-- now perform an insert for the tracer token
insert into MStracer_tokens
(
publication_id,
publisher_commit
) values
(
@publication_id,
getdate()
)
if @@error <> 0
goto Err_Handler

-- set the output tracer_id value
select @tracer_id = scope_identity()

commit transaction tr_sp_MSadd_tracer_token

return 0

Err_Handler:
rollback transaction tr_sp_MSadd_tracer_token
commit transaction

return 1
end

No comments:

Post a Comment

Total Pageviews