May 11, 2012

sp_MSenum_snapshot (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_MSenum_snapshot(nvarchar @name
, bit @show_distdb)

MetaData:

 create procedure sys.sp_MSenum_snapshot  
(
@name nvarchar(100) = '%',
@show_distdb bit = 0
)
as
begin
set nocount on

declare @publisher sysname
declare @publisher_db sysname
declare @publication sysname
declare @publication_id int
declare @snapshot_agent nvarchar(100)
declare @status int
declare @start_time nvarchar(24)
declare @time nvarchar(24)
declare @duration int
declare @comments nvarchar(255)
declare @delivered_transactions int
declare @delivered_commands int
declare @delivery_rate int
declare @publisher_id smallint
declare @error_id int
declare @job_id binary(16)
declare @local_job bit
declare @profile_id int
declare @agent_id int
declare @last_timestamp binary(8)
declare @suser_sname sysname
declare @host_name sysname

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

create table #snapshot_agent (name nvarchar(100) NOT NULL, status int NOT NULL,
publisher sysname NOT NULL, publisher_db sysname NOT NULL, publication sysname NOT NULL,
start_time nvarchar(24) NULL, time nvarchar(24) NULL, duration int NULL,
comments nvarchar(255) NULL, delivered_transactions int NULL, delivered_commands int NULL,
delivery_rate float NULL, error_id int NULL, job_id binary(16) NULL, local_job bit NOT NULL,
profile_id int NOT NULL, agent_id int NOT NULL, last_timestamp binary(8) NOT NULL,
dynamic_filter_login sysname NULL, dynamic_filter_hostname sysname NULL)


declare hC CURSOR LOCAL FAST_FORWARD FOR
select srvname, sa.publisher_db, sa.publication, sa.name, sa.publisher_id,
sa.local_job, sa.job_id, sa.profile_id, sa.id,
sa.dynamic_filter_login, sa.dynamic_filter_hostname
from
MSsnapshot_agents sa, master.dbo.sysservers
where
name LIKE @name and
srvid = sa.publisher_id
for read only

OPEN hC
FETCH hC INTO @publisher, @publisher_db, @publication, @snapshot_agent, @publisher_id,
@local_job, @job_id, @profile_id, @agent_id, @suser_sname, @host_name
WHILE (@@fetch_status <> -1)
begin

-- Get the publication id
select @publication_id = isnull(publication_id, 0) from dbo.MSpublications where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
publication = @publication

-- Stuff in the values for no history case
select @status = 0,
@start_time = NULL,
@time = NULL, @duration = NULL, @comments = NULL,
@delivered_commands = NULL,
@delivery_rate = NULL,
@error_id = NULL,
@last_timestamp = 0x00000000

-- Get the status of the agent
select @status = runstatus,
@start_time = sys.fn_replformatdatetime(start_time),
@time = sys.fn_replformatdatetime(time),
@duration = duration,
@comments = comments,
@delivered_transactions = delivered_transactions,
@delivered_commands = delivered_commands,
@delivery_rate = delivery_rate,
@error_id = error_id,
@last_timestamp = timestamp
from MSsnapshot_history with (READPAST)
where
agent_id = @agent_id and
timestamp = (select top 1 timestamp from MSsnapshot_history with (READPAST) where
agent_id = @agent_id
order by timestamp DESC)

insert into #snapshot_agent values (@snapshot_agent, @status, @publisher,
@publisher_db, @publication, @start_time, @time, @duration, @comments,
@delivered_transactions, @delivered_commands, @delivery_rate, @error_id, @job_id,
@local_job, @profile_id, @agent_id, @last_timestamp, @suser_sname, @host_name)

FETCH hC INTO @publisher, @publisher_db, @publication, @snapshot_agent,
@publisher_id, @local_job, @job_id, @profile_id, @agent_id, @suser_sname, @host_name
end

if @show_distdb = 0
select * from #snapshot_agent
else
select 'dbname' = DB_NAME(), * from #snapshot_agent

drop table #snapshot_agent
close hC
deallocate hC
end

No comments:

Post a Comment

Total Pageviews