June 7, 2012

sp_replsqlqgetrows (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_replsqlqgetrows(nvarchar @publisher
, nvarchar @publisherdb
, nvarchar @publication
, int @batchsize)

MetaData:

 create procedure sys.sp_replsqlqgetrows   
(
@publisher sysname
,@publisherdb sysname
,@publication sysname
,@batchsize int = 1000
)
as
begin
set nocount on
set implicit_transactions off
declare @retcode int
declare @nomesgs TABLE (tranid sysname, datalen int, data varbinary(8000),
commandtype int, insertdate datetime, orderkey bigint, cmdstate bit)
--
-- Security check
--
exec @retcode = sp_MSreplcheck_subscribe
if @@error != 0 or @retcode != 0
return 1
--
-- does the queue table exist
--
if exists (select * from sys.objects where name = 'MSreplication_queue')
begin
declare @totcommandcount bigint
,@trancount bigint
,@tranid sysname
,@curtrancommandcount bigint
, @batchcount bigint
declare @trantab table (tranid sysname primary key)
--
-- does the tran info table exist
--
if not exists (select * from sys.objects where name = 'MSrepl_queuedtraninfo')
begin
--
-- tran info table does not exist - create and populate it
--
exec @retcode = sp_MScreate_sub_tables_internal
@tran_sub_table = 0,
@property_table = 0,
@sqlqueue_table = 1
if (@@error != 0 or @retcode != 0)
return 1
end
--
-- At this point both queue table and tran info table exist
-- check the command count
--
select @totcommandcount = sum(commandcount)
,@trancount = count(tranid)
from dbo.MSrepl_queuedtraninfo with (READPAST)
where publisher = UPPER(@publisher)
and publisher_db = @publisherdb
and publication = @publication

if (@trancount = 0)
begin
--
-- no committed transactions
-- send empty rowset
--
select * from @nomesgs
end
else
begin
--
-- prepare a list of transactions to read
--
select @batchcount = 0
declare #htcdataseq cursor local for
select tranid, commandcount
from dbo.MSrepl_queuedtraninfo with (READPAST)
where publisher = UPPER(@publisher)
and publisher_db = @publisherdb
and publication = @publication
order by maxorderkey asc
open #htcdataseq
fetch #htcdataseq into @tranid, @curtrancommandcount
if (@@error != 0)
return 1
while (@@fetch_status != -1)
begin
--
-- Are we done
--
if (@batchcount > @batchsize)
begin
--
-- we are done selecting the transactions to process
--
break
end
else
begin
--
-- include this transaction
-- update the batch counter
--
insert into @trantab (tranid) values (@tranid)
if (@@error != 0)
return 1
select @batchcount = @batchcount + @curtrancommandcount
end
--
-- fetch next transaction to process
--
fetch #htcdataseq into @tranid, @curtrancommandcount
end
close #htcdataseq
deallocate #htcdataseq
if (@@error != 0)
return 1
--
-- do the join for the select transactions
-- select the transactions in the order they were committed (maxorderkey ascending).
-- for each transaction - the commands are ordered using orderkey (ascending)
-- we do not want to take any locks to block the commit(delete) operations on queue
--
select q.tranid, q.datalen, q.data, q.commandtype, q.insertdate, q.orderkey, q.cmdstate
from (dbo.MSreplication_queue as q with (NOLOCK)
join (dbo.MSrepl_queuedtraninfo as t with (NOLOCK)
join @trantab as tt
on t.tranid = tt.tranid collate database_default)
on q.publisher = t.publisher
and q.publisher_db = t.publisher_db
and q.publication = t.publication
and q.tranid = t.tranid)
where t.publisher = UPPER(@publisher)
and t.publisher_db = @publisherdb
and t.publication = @publication
order by t.maxorderkey asc, q.orderkey asc
end -- else block for if (@trancount = 0)
end -- if block for exists MSreplication_queue
else
begin
--
-- Queue table does not exist
-- send empty rowset
--
select * from @nomesgs
end
--
-- check error
--
if (@@error != 0)
return 1
--
-- All done
--
return 0
end

No comments:

Post a Comment

Total Pageviews