create procedure sys.sp_scriptdelproccore
(
@artid int -- id of the article we are processing
,@format tinyint -- 1 = CALL, 3 = XCALL, 4 = VCALL
,@mode tinyint -- 1 = regular scripting, 2 = snapshot reconciliation scripting, 3 = drop reconciliation procs only
,@publishertype tinyint=1 -- 1 = mssqlserver, 2 = heterogeneous
,@publisher sysname = NULL -- is only non-NULL if @publishertype = 2
,@usesqlclr bit=0
)
as
begin
declare @cmd nvarchar(4000)
,@dest_owner nvarchar(255)
,@dest_tabname sysname
,@src_objid int
,@del_cmd nvarchar(255)
,@dest_proc nvarchar(524)
,@this_col int
,@art_col int
,@isset int
,@isvalidcolumn tinyint
,@pubid int
,@typestring nvarchar(255)
,@colname sysname
,@spacer nvarchar(10)
,@updsub_check bit
,@qwhere_string nvarchar(4000)
,@commandprefix nvarchar(5)
,@repl_freq tinyint
,@publication sysname
,@userdefinedtypestobasetypes bit
,@xmltontext bit
,@maxtypestomatchingnonmaxtypes bit
,@newdatetimetypestostrings bit
,@hierarchyidtovarbinarymax bit
,@largeuserdefinedtypestovarbinarymax bit
,@spatialtypestovarbinarymax bit
,@schema_option bigint
,@maptimestamp bit
,@article sysname
,@unquoted_proc_name sysname
-- constants
,@callformat tinyint
,@xcallformat tinyint
,@vcallformat tinyint
,@regularmode tinyint
,@reconciliationmode tinyint
,@droponly tinyint
,@pubtypemssqlserver tinyint
,@pubtypeheterogeneous tinyint
-- initialize
set nocount on
select @callformat = 1
,@xcallformat = 3
,@vcallformat = 4
,@regularmode = 1
,@reconciliationmode = 2
,@droponly = 3
,@pubtypemssqlserver = 1
,@pubtypeheterogeneous = 2
,@maptimestamp = 1
,@hierarchyidtovarbinarymax = 0
,@largeuserdefinedtypestovarbinarymax = 0
,@spatialtypestovarbinarymax = 0
,@article = NULL
,@userdefinedtypestobasetypes = 0
,@xmltontext = 0
,@maxtypestomatchingnonmaxtypes = 0
,@newdatetimetypestostrings = 0
-- validate @artid
if not exists( select * from sysarticles where artid = @artid AND (type & 1) = 1 )
begin
raiserror (14155, 16, 1 )
return 1
end
-- validate @format
if (@format not in (@callformat, @xcallformat, @vcallformat))
begin
raiserror(21402, 16, 8, '@format')
return (1)
end
-- validate @mode
if (@mode not in (@regularmode, @reconciliationmode, @droponly))
begin
raiserror(21402, 16, 9, '@mode')
return (1)
end
-- validate @publishertype
if (@publishertype not in (@pubtypemssqlserver, @pubtypeheterogeneous))
begin
raiserror(21402, 16, 10, '@publishertype')
return (1)
end
-- validate @publisher
if (@publisher IS NULL) AND (@publishertype = @pubtypeheterogeneous)
begin
raiserror(21425, 16, 7)
return (1)
end
if (@publisher IS NOT NULL) AND (@publishertype = @pubtypemssqlserver)
begin
raiserror(21424, 16, 7)
return (1)
end
if (@publishertype = @pubtypemssqlserver)
begin
select @publisher = publishingservername()
end
-- create temp table for scripting
create table #proctext ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
-- get sysarticles information
select @dest_owner = case when dest_owner is NULL then N''
else quotename( dest_owner ) + N'.' end
,@dest_tabname = dest_table
,@src_objid = case when (@publishertype = @pubtypemssqlserver) then objid else artid end
,@del_cmd = del_cmd
,@pubid = pubid
,@schema_option = convert(bigint,schema_option)
,@article = name
from sysarticles
where artid = @artid
if (@schema_option & 0x0000000000000020) = 0x0000000000000020 -- UserDefinedTypesToBaseTypes
begin
set @userdefinedtypestobasetypes = 1
end
if (@schema_option & 0x0000000010000000) = 0x0000000010000000 -- XmlToNText
begin
set @xmltontext = 1
end
if (@schema_option & 0x0000000020000000) = 0x0000000020000000 -- MaxTypesToMatchingNonMaxTypes
begin
set @maxtypestomatchingnonmaxtypes = 1
end
if (@schema_option & 0x0000000000000008) = 0x0000000000000008 -- replicate timestamp as timestamp
begin
set @maptimestamp = 0
end
if (@schema_option & 0x0000000200000000) = 0x0000000200000000 -- NewDateTimeTypesToStrings
begin
set @newdatetimetypestostrings = 1
end
if (@schema_option & 0x0000002000000000) = 0x0000002000000000 -- HierarchyIdToVarBinaryMax
begin
set @hierarchyidtovarbinarymax = 1
end
if (@schema_option & 0x0000001000000000) = 0x0000001000000000 -- LargeUserDefinedTypesToVarBinaryMax
begin
set @largeuserdefinedtypestovarbinarymax = 1
end
if (@schema_option & 0x0000008000000000) = 0x0000008000000000 -- SpatialTypesToVarBinaryMax
begin
set @spatialtypestovarbinarymax = 1
end
-- get publication info
select @updsub_check = (ISNULL(allow_sync_tran, 0) |ISNULL(allow_queued_tran, 0))
,@repl_freq = repl_freq
,@publication = name
from syspublications
where pubid = @pubid
-- Do not support snapshot publications
if (@repl_freq = 1)
begin
raiserror(21515, 16, -1, @publication)
return 1
end
-- do we have the right call format
select @commandprefix = case
when (@format = @callformat) then N'CALL'
when (@format = @xcallformat) then N'XCALL'
when (@format = @vcallformat) then N'VCALL'
else null end
if ( 1 != charindex( @commandprefix, upper(@del_cmd collate SQL_Latin1_General_CP1_CS_AS) ) ) or @del_cmd is null
begin
raiserror (14156, 16, 1, N'delete', @commandprefix)
return 1
end
-- get destination proc name, the format stored inside sysarticles: XXXX [dbo].[xxxxxxxx]
select @dest_proc = case when (@format = @callformat)
then substring( @del_cmd, 6, len( @del_cmd ) - 4 )
else substring( @del_cmd, 7, len( @del_cmd ) - 5 ) end
--in ccs reconcile mode we can no longer use ;2 as proc name, append something to the end to make it *unique*
select @dest_proc = case @mode when @regularmode then @dest_proc
else substring(@dest_proc, 1, len( @dest_proc ) -1 ) + N'_msrepl_ccs]'
end
select @unquoted_proc_name = parsename(@dest_proc, 1)
-- script the dropping and creation of the proc
select @cmd = N'if object_id(N''' + sys.fn_replreplacesinglequote(@dest_proc) collate database_default + N''', ''P'') > 0
drop proc ' + @dest_proc
insert into #proctext(procedure_text) values( @cmd )
--only keep custom procs in MSreplication_objects for easier cleanup, ccs rec procs are cleaned up by logreader so no need for this
if (@mode = @regularmode)
begin
insert into #proctext(procedure_text) values( N'go' )
select @cmd = N'if object_id(N''dbo.MSreplication_objects'') is not null
delete from dbo.MSreplication_objects where object_name = N''' + sys.fn_replreplacesinglequote(@unquoted_proc_name) collate database_default + N''''
insert into #proctext(procedure_text) values( @cmd )
end
--droponly is used by ccs to drop reconciliation proc at the time sync_done token is posted
if (@mode = @droponly)
goto DROPONLY
insert into #proctext(procedure_text) values( N'go' )
insert into #proctext( procedure_text ) values ( N'create procedure ' + @dest_proc + N' ')
-- construct parameter list
select @art_col = 1
,@spacer = N' '
,@cmd = N' '
if (@publishertype = @pubtypemssqlserver)
begin
declare hccolid cursor local fast_forward for
select column_id, name from sys.columns where object_id = @src_objid order by column_id asc
end
else
begin
declare hccolid cursor local fast_forward for
select colid, name from IHsyscolumns where id = @src_objid order by colorder asc
end
open hccolid
fetch hccolid into @this_col, @colname
while (@@fetch_status != -1)
begin
if (@publishertype = @pubtypemssqlserver)
select @isvalidcolumn = case when exists (select name from sys.columns where object_id=@src_objid and @this_col=column_id and is_computed != 1) then 1 else 0 end
else
select @isvalidcolumn = case when exists (select name from IHsyscolumns where id=@src_objid and @this_col=colid) then 1 else 0 end
-- process each column based on format
if (@format in (@callformat, @vcallformat))
begin
-- processing CALL/VCALL formats
-- For VCALL we are looking for PK non computed column
exec @isset = sys.sp_MSiscolpk @src_objid, @this_col, @publishertype
if @isset = 1
begin
-- processing PK column
select @typestring = sys.fn_gettypestring (@src_objid, @this_col, @publishertype, @userdefinedtypestobasetypes, @xmltontext, @maxtypestomatchingnonmaxtypes, @maptimestamp, @newdatetimetypestostrings, @hierarchyidtovarbinarymax, @largeuserd
efinedtypestovarbinarymax, @spatialtypestovarbinarymax)
select @cmd = @cmd + @spacer + N'@pkc' + convert( nvarchar(10), @art_col ) + N' ' + @typestring
select @art_col = @art_col + 1
,@spacer = N','
end -- PK column
end -- CALL/VCALL format
else
begin
-- processing XCALL format
-- If this is a computed column, it is only valid if it is part of the primary key
if @isvalidcolumn = 0
exec @isset = sys.sp_MSiscolpk @src_objid, @this_col, @publishertype
else
exec @isset = sys.sp_isarticlecolbitset @this_col, @artid, @publishertype
if @isset = 1
begin
-- processing column marked for replication that is not computed
select @typestring = sys.fn_gettypestring (@src_objid, @this_col, @publishertype, @userdefinedtypestobasetypes, @xmltontext, @maxtypestomatchingnonmaxtypes, @maptimestamp, @newdatetimetypestostrings, @hierarchyidtovarbinarymax, @largeuserd
efinedtypestovarbinarymax, @spatialtypestovarbinarymax)
select @cmd = @cmd + @spacer + N'@c' + convert( nvarchar(10), @art_col ) + N' ' + @typestring
-- processing for updating subscribers:if this is the row version column : need to add to where clause
if (@updsub_check = 1 and @colname = N'msrepl_tran_version')
select @qwhere_string = N' and msrepl_tran_version = @c' + convert( nvarchar, @art_col )
select @art_col = @art_col + 1
,@spacer = N','
end -- end column replicated and not computed
end -- XCALL format
-- flush command as necessary
if len( @cmd ) > 3000
begin
insert into #proctext(procedure_text) values( @cmd )
select @cmd = N' '
end
fetch hccolid into @this_col, @colname
end -- while cursor block
close hccolid
deallocate hccolid
insert into #proctext(procedure_text) values( @cmd )
-- VCALL format : add version parameter for the regular scripting mode
if (@format = @vcallformat) and (@mode = @regularmode)
begin
insert into #proctext(procedure_text) values( N',@msrepl_tran_version uniqueidentifier ')
end
insert into #proctext(procedure_text) values( N'as ')
insert into #proctext(procedure_text) values( N'begin ')
-- script the delete statetment
insert into #proctext(procedure_text) values( N'delete ' + @dest_owner + quotename(@dest_tabname) )
-- script the where clause
if (@format in (@callformat, @vcallformat))
begin
-- processing CALL/VCALL formats
exec sys.sp_scriptpkwhereclause @src_objid = @src_objid
,@artid = @artid
,@publishertype = @publishertype
end
else
begin
-- processing XCALL format
exec sys.sp_scriptpkwhereclause @src_objid = @src_objid
,@artid = @artid
,@prefix = N'@c'
,@artcolcounter = 1
,@publishertype = @publishertype
end
-- additional processing based on mode and format
if (@mode = @regularmode)
begin
-- regular mode processing
if (@updsub_check = 0)
begin
-- processing for SQL Server publisher
if (@publishertype = @pubtypemssqlserver)
begin
-- regular articles : add missing row check
exec sys.sp_MSscript_missing_row_check
end
end
else
begin
-- updating subscriber article
if (@format = @xcallformat)
insert into #proctext(procedure_text) values( @qwhere_string )
else if (@format = @vcallformat)
insert into #proctext(procedure_text) values( N' and msrepl_tran_version = @msrepl_tran_version ' )
end
end -- end regular mode
-- end the scripting
insert into #proctext(procedure_text) values( N'end ')
-- script the inserting of proc entry in MSreplication_objects now that proc is created
--only keep custom procs in MSreplication_objects for easier cleanup, ccs rec procs are cleaned up by logreader so no need for this
if (@mode = @regularmode)
begin
insert into #proctext(procedure_text) values( N'go' )
select @cmd = N'if columnproperty(object_id(N''dbo.MSreplication_objects''), N''article'', ''AllowsNull'') is not null '
insert into #proctext(procedure_text) values( @cmd )
select @cmd = N'insert dbo.MSreplication_objects (object_name, publisher, publisher_db, publication, article, object_type) values (
+ N''' + sys.fn_replreplacesinglequote(@unquoted_proc_name) collate database_default + N''' '
+ N', N''' + sys.fn_replreplacesinglequote(@publisher) collate database_default + N''' '
+ N', N''' + sys.fn_replreplacesinglequote(db_name()) collate database_default + N''' '
+ N', N''' + sys.fn_replreplacesinglequote(@publication) collate database_default + N''' '
+ N', N''' + sys.fn_replreplacesinglequote(@article) collate database_default + N''' '
+ N',''P'')'
select @cmd = 'exec (''' + sys.fn_replreplacesinglequote(@cmd) + ''')'
insert into #proctext(procedure_text) values( @cmd )
end
DROPONLY:
-- flush to client
select procedure_text from #proctext order by c1 asc
-- all done
return 0
end