create procedure sys.sp_MSscript_ExecutionMode_stmt (
@publication sysname,
@article sysname,
@proctype int = 0) -- 0 insert, 1 delete, 2 update
as
begin
declare @cmd nvarchar(4000)
,@artid int
,@pubid int
,@queued_pub bit
select @pubid = pubid, @queued_pub = allow_queued_tran
from syspublications where name = @publication
select @artid = artid from sysarticles where name = @article and pubid = @pubid
-- script PAL check subscription validation and reinitialization check
-- PAL check will be done inside sp_MSgetarticlereinitvalue
select @cmd = N'
' + N'
' + N'-- Do PAL security check inside sp_MSgetarticlereinitvalue
' + N'-- Check for subscription validation
' + N'-- Check if we are in the process of Reinitialization
' + N'--'
insert into #proctext(procedure_text) values( @cmd )
select @cmd = N'
exec @retcode = sys.sp_MSgetarticlereinitvalue @orig_server, @orig_db, N''' +
sys.fn_replreplacesinglequote(@publication) collate database_default + N''', ' +
cast(@artid as nvarchar(5)) + N', @reinit output
if (@retcode != 0 or @@error != 0)
return -1 '
insert into #proctext(procedure_text) values( @cmd )
-- script the reinit check
select @cmd = N'
if (@reinit is null) -- Invalid subscription
begin
exec sys.sp_MSreplraiserror 21161
return -1
end
else if (@reinit = 1) -- Resync state
begin
if (@execution_mode = @immediate)
return -2'
if (@queued_pub = 1)
begin
select @cmd = @cmd + N'
else
return 4 -- Queued Resync state'
end
select @cmd = @cmd + N'
end
'
insert into #proctext(procedure_text) values( @cmd )
-- set loopback detection for immediate
select @cmd = N'
if (@execution_mode = @immediate)
begin
' + N'
' + N'-- For immediate
' + N'-- enable loopback detection
' + N'
exec @retcode = dbo.sp_replsetoriginator @orig_server, @orig_db, N'''
+ sys.fn_replreplacesinglequote(@publication) collate database_default + N'''
if (@retcode != 0 or @@error != 0)
return -1
end'
insert into #proctext(procedure_text) values( @cmd )
-- Queued specific scripting
if (@queued_pub = 1)
begin
-- Queued reinitialization mode execution
select @cmd = N'
if (@execution_mode = @QReinit)
begin
' + N'
' + N'-- For Queued reinitialization
' + N'-- Set the Queue and subscription for reinit
' + N'--'
insert into #proctext(procedure_text) values( @cmd )
select @cmd = N'
exec @retcode = sys.sp_reinitsubscription @publication = N'''
+ sys.fn_replreplacesinglequote(@publication) collate database_default + N''', @article = N''' +
sys.fn_replreplacesinglequote(@article) collate database_default + N''', @subscriber = @orig_server, @destination_db = @orig_db
if (@retcode != 0 or @@error != 0)
return -1
else
return 0
end'
insert into #proctext(procedure_text) values( @cmd )
end
-- all done
return 0
end