CREATE PROCEDURE sys.sp_populateqtraninfo
as
begin
-- initiate local transaction
begin tran sp_populateqtraninfo
save tran sp_populateqtraninfo
-- see if we need to populate
if EXISTS (SELECT * FROM dbo.MSreplication_queue)
begin
-- There is data in queue
-- Populate MSrepl_queuedtraninfo based on what is there
declare @publisher sysname
,@publisherdb sysname
,@publication sysname
,@tranid sysname
,@orderkey bigint
,@commandcount bigint
declare #htctranseq cursor local for
select publisher, publisher_db, publication, tranid, orderkey
from dbo.MSreplication_queue with (READPAST)
order by orderkey asc
open #htctranseq
fetch #htctranseq into @publisher, @publisherdb, @publication, @tranid, @orderkey
IF @@ERROR <> 0
goto cleanup
while (@@fetch_status != -1)
begin
-- update/insert entry in MSrepl_queuedtraninfo for this transaction
if exists (select *
from dbo.MSrepl_queuedtraninfo
where publisher = UPPER(@publisher)
and publisher_db = @publisherdb
and publication = @publication
and tranid = @tranid)
begin
-- row for this transaction exists - update it
update dbo.MSrepl_queuedtraninfo
set maxorderkey = @orderkey
,commandcount = commandcount + 1
where publisher = UPPER(@publisher)
and publisher_db = @publisherdb
and publication = @publication
and tranid = @tranid
end
else
begin
-- row for this transaction does not exist - insert it
insert into dbo.MSrepl_queuedtraninfo (publisher,publisher_db,publication,tranid,maxorderkey,commandcount)
values (UPPER(@publisher),@publisherdb,@publication,@tranid,@orderkey,1)
end
IF @@ERROR <> 0
goto cleanup
-- get the next row
fetch #htctranseq into @publisher, @publisherdb, @publication, @tranid, @orderkey
end
close #htctranseq
deallocate #htctranseq
end
-- commit local transaction
commit tran sp_populateqtraninfo
-- all done
return 0
cleanup:
-- error cleanup
-- rollback local tran
rollback tran sp_populateqtraninfo
commit tran
return 1
end