create procedure sys.sp_MSreset_queue (
@publisher sysname, -- publishing server name
@publisher_db sysname, -- publishing database name.
@publication sysname, -- publication name,
@artid int)
as
begin
declare @subserver sysname
,@subdbname sysname
,@queue_id sysname
,@update_mode int
,@retcode smallint
,@vbartid varbinary(20)
,@queue_server sysname
set nocount on
select @subserver = @@servername,
@subdbname = db_name(),
@update_mode = update_mode,
@queue_id = queue_id,
@queue_server = queue_server
from MSsubscription_agents
where UPPER(publisher) = UPPER(@publisher)
and publisher_db = @publisher_db
and publication = @publication
if (@update_mode in (2,3))
begin
-- MSMQ processing
-- prefix the queue_id with queue server in direct format
-- and then perform the queue reset
select @queue_id = N'DIRECT=OS:' + @queue_server + N'\PRIVATE$\' + @queue_id
begin distributed tran
exec @retcode = sys.xp_resetqueue @queue_id, @subserver,
@subdbname, @publication, @artid
if (@retcode != 0 or @@error != 0)
begin
if (@@trancount > 0)
rollback tran
return (1)
end
end
else if (@update_mode in (4,5))
begin
declare @tranid sysname
begin tran
-- process MSreplication_queue
select @retcode = 0
if (exists (select * from sys.objects
where name = 'MSreplication_queue'))
begin
-- Strictly speaking we do no need
-- to delete but, makes it easy for
-- the queue reader agent
-- Do not delete any reset messages
delete dbo.MSreplication_queue
where publisher = UPPER(@publisher)
and publisher_db = @publisher_db
and publication = @publication
and tranid not like N'sub-reset%'
end
else
begin
-- first queue subscription is being initialized
-- create queue if necessary
exec @retcode = sp_MScreate_sub_tables_internal
@tran_sub_table = 0,
@property_table = 0,
@sqlqueue_table = 1
end
if (@retcode != 0 or @@error != 0)
begin
if (@@trancount > 0)
rollback tran
return (1)
end
-- process MSrepl_queuedtraninfo
if (exists (select * from sys.objects
where name = 'MSrepl_queuedtraninfo'))
begin
-- Strictly speaking we do no need
-- to delete but, makes it easy for
-- the queue reader agent
-- Do not delete any reset messages
delete dbo.MSrepl_queuedtraninfo
where publisher = UPPER(@publisher)
and publisher_db = @publisher_db
and publication = @publication
and tranid not like N'sub-reset%'
end
else
begin
-- first queue subscription is being initialized
-- create the traninfo if necessary
exec @retcode = sp_MScreate_sub_tables_internal
@tran_sub_table = 0,
@property_table = 0,
@sqlqueue_table = 1
end
if (@retcode != 0 or @@error != 0)
begin
if (@@trancount > 0)
rollback tran
return (1)
end
-- for subscription reinitialization we
-- need to insert a RESYNC command message
select @vbartid = cast(@artid as varbinary(20))
,@tranid = N'sub-reset-' + cast(NEWID() as sysname)
insert into dbo.MSreplication_queue (publisher, publisher_db,
publication,tranid, commandtype, data, datalen)
values (UPPER(@publisher), @publisher_db,
@publication, @tranid, 2, @vbartid, datalength(@vbartid))
if ((@@error != 0) or (@retcode != 0))
begin
if (@@trancount > 0)
rollback tran
return (1)
end
-- add an entry in MSrepl_queuedtraninfo
insert into dbo.MSrepl_queuedtraninfo (publisher,publisher_db,publication,tranid,maxorderkey,commandcount)
values (UPPER(@publisher),@publisher_db,@publication,@tranid,@@identity,1)
end
commit tran
return 0
end