May 2, 2012

sp_MSadd_qreader_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_qreader_history(int @agent_id
, int @pubid
, int @runstatus
, nvarchar @comments
, nvarchar @transaction_id
, int @transaction_status
, int @transactions_processed
, int @commands_processed
, int @seconds_elapsed
, nvarchar @subscriber
, nvarchar @subscriberdb
, bit @perfmon_increment
, bit @log_error
, bit @update_existing_row
, bit @do_raiserror)

MetaData:

 CREATE PROCEDURE sys.sp_MSadd_qreader_history (  
@agent_id int,
@pubid int = NULL,
@runstatus int,
@comments nvarchar(1000) = NULL,
@transaction_id nvarchar(40) = NULL,
@transaction_status int = 0,
@transactions_processed int =0,
@commands_processed int = 0,
@seconds_elapsed int = 0,
@subscriber sysname = NULL,
@subscriberdb sysname = NULL,
@perfmon_increment bit = 1,
@log_error bit = 0,
@update_existing_row bit = 0,
@do_raiserror bit = 1)
AS
BEGIN
DECLARE @current_time datetime
,@start_time datetime
,@duration int
,@agent_name nvarchar(100)
,@publisher sysname
,@publisher_db sysname
,@publication sysname
,@lastrow_timestamp timestamp
,@retcode int
,@cmdprocessed_rate float
,@transaction_rate float
,@error_id int
,@idle int
,@succeed int
,@startup int
,@retry int
,@failure int
,@inprogress int
,@database sysname
,@statobjid int
,@agentclassname sysname
,@prev_runstatus int


--
-- security check
-- only db_owner can execute this
--
if (is_member ('db_owner') != 1)
begin
raiserror(14260, 16, -1)
return (1)
end
--
-- Status const defined in sqlrepl.h
--
select @startup = 1
,@succeed = 2
,@inprogress = 3
,@idle = 4
,@retry = 5
,@failure = 6

-- intializations
if (@pubid = 0)
select @pubid = NULL
if (@comments = '')
select @comments = NULL
if (@transaction_id = '')
select @transaction_id = NULL
if (@commands_processed is NULL)
select @commands_processed = 0
if (@subscriber = '')
select @subscriber = NULL
if (@subscriberdb = '')
select @subscriberdb = NULL

SELECT @database = db_name()
,@current_time = GETDATE()

-- Get named information
select @agent_name = name from MSqreader_agents where id = @agent_id
if (@agent_name IS NULL)
begin
--
-- When Queue reader is shutting down due to the last queued subscription
-- being dropped it may happen that before the Queue reader logs the shutdown
-- message, the subscription drop process deletes the agent entry from MSqreader_agents
--
select @agent_name = quotename(@@servername) + '.' + cast(db_id() as nvarchar)
end
if (@pubid is NULL)
begin
select @publisher = NULL
,@publisher_db = NULL
,@publication = NULL
end
else
begin
select @publisher = a.srvname, @publisher_db = b.publisher_db, @publication = b.publication
from master.dbo.sysservers a, dbo.MSpublications b
where
b.publisher_id= @pubid and
b.publisher_id = a.srvid
end

-- Update Perfmon counter
if @perfmon_increment = 1
begin
if @runstatus = @startup
dbcc incrementinstance ("SQL Replication Agents", "Running", "QueueReader", 1)
else if (@runstatus = @succeed or @runstatus = @retry or @runstatus = @failure)
dbcc incrementinstance ("SQL Replication Agents", "Running", "QueueReader", -1)
end

-- Get start_time for latest agent run

SELECT TOP 1
@prev_runstatus = runstatus,
@start_time = start_time,
@lastrow_timestamp = timestamp
FROM MSqreader_history with (rowlock)
WHERE
agent_id = @agent_id
ORDER BY timestamp DESC

-- If we are starting through SQL Agent - there might already be
-- a startup logged in history
IF (@runstatus = @startup)
begin
IF @prev_runstatus not IN (@startup, @retry)
BEGIN
SELECT @start_time = @current_time
END
end

if (@start_time is NULL)
begin
select @runstatus = @startup,
@start_time = @current_time
end

-- Calculate agent run duration
SELECT @duration = DATEDIFF(second, @start_time, @current_time)

-- Calculate rate of processing
IF (@seconds_elapsed IS NOT NULL and @seconds_elapsed > 0)
BEGIN
SELECT @cmdprocessed_rate = (@commands_processed * 1.0)/@seconds_elapsed
,@transaction_rate = (@transactions_processed * 1.0)/@seconds_elapsed
END
ELSE
BEGIN
SELECT @cmdprocessed_rate = 0.0
,@transaction_rate = 0.0
END

-- Set Perfmon counters
if @runstatus = @idle or @runstatus = @inprogress
begin
dbcc addinstance ("SQL Replication QueueReader", @agent_name)
-- dbcc incrementinstance ("SQL Replication QueueReader", "QueueReader:Delivered Cmds/sec", @agent_name, @cmdprocessed_rate)
-- dbcc incrementinstance ("SQL Replication QueueReader", "QueueReader:Delivered Trans/sec", @agent_name, @transaction_rate)
end

--
-- Set error id to 0 unless the user want to log errors associate with this
-- history message.
--
IF (@log_error = 1)
begin
select @runstatus = @failure
EXEC sys.sp_MSget_new_errorid @error_id OUTPUT
end
ELSE
SELECT @error_id = 0
--
-- @comments should contain message at all times
--
if (@comments is null)
select @comments = isnull(formatmessage(21020), N'Message 21020')
-- Insert idle record or update if history record is already 'idle'
IF (@runstatus = @idle or @update_existing_row = 1)
begin
-- Attempt to update the last row if it is IDLE
UPDATE MSqreader_history
SET publication_id = @pubid,
runstatus = @runstatus,
time = @current_time,
duration = @duration,
comments = @comments,
transaction_id = @transaction_id,
transaction_status = @transaction_status,
transactions_processed = @transactions_processed,
commands_processed = @commands_processed,
delivery_rate = @cmdprocessed_rate,
transaction_rate = @transaction_rate,
subscriber = @subscriber,
subscriberdb = @subscriberdb,
error_id = case @error_id when 0 then error_id else @error_id end
WHERE
agent_id = @agent_id and
timestamp = @lastrow_timestamp and
runstatus = @runstatus

-- Insert idle record if there is not one
if (@@ROWCOUNT = 0)
begin
INSERT INTO MSqreader_history(agent_id, publication_id, runstatus, start_time, time,
duration, comments, transaction_id, transaction_status,
transactions_processed, commands_processed, delivery_rate,
transaction_rate, subscriber, subscriberdb, error_id)
VALUES(@agent_id, @pubid, @runstatus, @start_time, @current_time,
@duration, @comments, @transaction_id, @transaction_status,
@transactions_processed, @commands_processed, @cmdprocessed_rate,
@transaction_rate, @subscriber, @subscriberdb, @error_id)
end
end
else
begin
INSERT INTO MSqreader_history(agent_id, publication_id, runstatus, start_time, time,
duration, comments, transaction_id, transaction_status,
transactions_processed, commands_processed, delivery_rate,
transaction_rate, subscriber, subscriberdb, error_id)
VALUES(@agent_id, @pubid, @runstatus, @start_time, @current_time,
@duration, @comments, @transaction_id, @transaction_status,
@transactions_processed, @commands_processed, @cmdprocessed_rate,
@transaction_rate, @subscriber, @subscriberdb, @error_id)
end

-- Raise the appropriate error
if (@do_raiserror = 1)
begin
select @agentclassname = formatmessage(14581)
exec sys.sp_MSrepl_raiserror @agentclassname, @agent_name, @runstatus, @comments
end

IF (@@ERROR != 0)
RETURN (1)
END

No comments:

Post a Comment

Total Pageviews