create procedure sys.sp_scriptinsproccore
(
@artid int -- id of the article we are processing
,@format tinyint -- 1 = CALL, 5 = SCALL
,@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
,@ins_cmd nvarchar(255)
,@dest_proc nvarchar(524)
,@this_col int
,@art_col int
,@isset int
,@isvalidcolumn tinyint
,@pubid int
,@identity_insert bit
,@fscriptidentity bit
,@rc int
,@colname sysname
,@typestring nvarchar(255)
,@spacer nvarchar(1)
,@updsub_check bit
,@isallreplcolpk int
,@repl_freq tinyint
,@publication sysname
,@userdefinedtypestobasetypes bit
,@xmltontext bit
,@maxtypestomatchingnonmaxtypes bit
,@schema_option bigint
,@newdatetimetypestostrings bit
,@hierarchyidtovarbinarymax bit
,@largeuserdefinedtypestovarbinarymax bit
,@spatialtypestovarbinarymax bit
,@maptimestamp bit
,@article sysname
,@unquoted_proc_name sysname
,@commandprefix sysname
-- constants
,@regularmode tinyint
,@reconciliationmode tinyint
,@droponly tinyint
,@pubtypemssqlserver tinyint
,@pubtypeheterogeneous tinyint
,@callformat tinyint
,@scallformat tinyint
-- initialize
set nocount on
select @regularmode = 1
,@reconciliationmode = 2
,@droponly = 3
,@pubtypemssqlserver = 1
,@pubtypeheterogeneous = 2
,@userdefinedtypestobasetypes = 0
,@xmltontext = 0
,@maxtypestomatchingnonmaxtypes = 0
,@hierarchyidtovarbinarymax = 0
,@largeuserdefinedtypestovarbinarymax = 0
,@spatialtypestovarbinarymax = 0
,@maptimestamp = 1
,@newdatetimetypestostrings = 0
,@article = NULL
,@commandprefix = NULL
,@callformat = 1
,@scallformat = 5
-- 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, @scallformat))
begin
raiserror(21402, 16, 11, '@format')
return (1)
end
-- validate @mode
if (@mode not in (@regularmode, @reconciliationmode, @droponly))
begin
raiserror(21402, 16, 6, '@mode')
return (1)
end
-- validate @publishertype
if (@publishertype not in (@pubtypemssqlserver, @pubtypeheterogeneous))
begin
raiserror(21402, 16, 7, '@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
-- Validate @mode
if ((@publishertype = @pubtypeheterogeneous) and (@mode = 2))
begin
raiserror(21634, 16, 7, N'@mode', N'snapshot reconciliation scripting (2)', N'regular scripting (1)')
return (1)
end
if (@publishertype = @pubtypemssqlserver)
begin
select @publisher = publishingservername()
end
-- create temp table for command fragments and insert column list
create table #proctext ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
create table #collisttab ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null, collist bit)
-- 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
,@ins_cmd = ins_cmd
,@pubid = pubid
,@schema_option = convert(bigint,schema_option)
,@fscriptidentity = case when (@publishertype = @pubtypemssqlserver and (cast(schema_option as int) & 0x4) != 0 ) then 1 else 0 end
,@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
-- do we have the right call format
select @commandprefix = case @format
when @callformat then N'CALL'
when @scallformat then N'SCALL'
else null
end
if (1 != charindex( @commandprefix, upper(@ins_cmd collate SQL_Latin1_General_CP1_CS_AS) ))
or @ins_cmd is null
begin
raiserror (14156, 16, 1, N'insert', @commandprefix)
return 1
end
-- Check to see if identity insert must be turned on
-- i.e. Does the table has identity that are included in the partition?
if (@publishertype = @pubtypemssqlserver)
begin
exec sys.sp_MSis_identity_insert @publication = null
,@article = null
,@identity_insert = @identity_insert output
,@artid = @artid
,@mode = 2
end
else
select @identity_insert = 0
-- get publication information
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
-- get destination proc name, the format stored inside sysarticles: XXXX [dbo].[xxxxxxxx]
select @dest_proc = case @format
when @callformat then substring( @ins_cmd, 6, len( @ins_cmd ) - 4 )
when @scallformat then substring( @ins_cmd, 7, len( @ins_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' '
-- prepare cursor based on publisher type
if (@publishertype = @pubtypemssqlserver)
begin
declare hccolid cursor local fast_forward for
select column_id 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 from IHsyscolumns where id = @src_objid order by colorder asc
end
open hccolid
fetch hccolid into @this_col
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
-- is the column marked for replication
-- is the column valid
if @isvalidcolumn = 0
begin
if @mode = @reconciliationmode
exec @isset = sys.sp_MSiscolpk @src_objid, @this_col, @publishertype
else
set @isset = 0
end
else
begin
exec @isset = sys.sp_isarticlecolbitset @this_col, @artid, @publishertype
end
if @isset = 1
begin
-- processing a column marked for replication and it is not computed
select @typestring = sys.fn_gettypestring (@src_objid, @this_col, @publishertype, @userdefinedtypestobasetypes, @xmltontext, @maxtypestomatchingnonmaxtypes, @maptimestamp, @newdatetimetypestostrings, @hierarchyidtovarbinarymax, @largeuserdefin
edtypestovarbinarymax, @spatialtypestovarbinarymax)
select @cmd = @cmd + @spacer + N'@c' + convert( nvarchar(10), @art_col ) + N' ' + @typestring
if @format = @scallformat
begin
select @cmd = @cmd + ' = null'
end
select @art_col = @art_col + 1
,@spacer = N','
-- flush command as necessary
if len( @cmd ) > 3000
begin
insert into #proctext(procedure_text) values( @cmd )
select @cmd = N' '
end
end -- column marked for replication and not computed
fetch hccolid into @this_col
end -- while cursor block
close hccolid
deallocate hccolid
insert into #proctext(procedure_text) values( @cmd )
insert into #proctext(procedure_text) values( N'as ')
insert into #proctext(procedure_text) values( N'begin ')
-- Regular Mode
-- The scripting is simple for general tran article (insert statement).
-- For updating subscriber article - there is a possibility that the row
-- already exists - we will add a row_does_not_exist check :
-- if not exists (select * from [table] where [column1] = @c1)
-- begin
-- insert statement
-- end
-- Snapshot Reconciliation Mode
-- The scripting is done as follows
-- if exists (select * from [table] where [column1] = @c1)
-- begin
-- update statement
-- end
-- else
-- begin
-- insert statement
-- end
-- we will script an update if the row already exists.
-- If we are replicating only PK columns, then the update is a noop.
-- continue processing based on the mode
if (@mode = @regularmode)
begin
-- regular mode
if (@updsub_check = 1)
begin
-- updating subscriber article
-- script out the if not row exists clause
insert into #proctext(procedure_text) values (N'if not exists (select * from ' + @dest_owner + quotename(@dest_tabname) + N' ')
exec @rc = sp_replscriptuniquekeywhereclause @tabid = @src_objid
,@artid = @artid
,@prefix = '@c'
,@mode = 1
,@skipindexesonudtcolumns = @userdefinedtypestobasetypes
if (@@error != 0 or @rc != 0)
return 1
insert into #proctext(procedure_text) values( N')' )
insert into #proctext(procedure_text) values (N'begin')
end -- end (@updsub_check = 1)
end -- end regular mode
else
begin
-- reconciliation mode
-- scripting for if the row already exists, apply as update
insert into #proctext(procedure_text)
values( N'if exists ( select * from ' + @dest_owner + quotename(@dest_tabname) )
exec sys.sp_scriptreconwhereclause @src_objid, @artid, @publishertype
insert into #proctext(procedure_text) values( N')' )
insert into #proctext(procedure_text) values (N'begin')
-- Check if we are replicating PK columns only
exec @isallreplcolpk = sys.sp_MSisallreplcolpk @artid, @publishertype
if @isallreplcolpk = 0
begin
-- replicating non PK columns as well
-- construct update
select @art_col = 1
,@spacer = N' '
insert into #proctext(procedure_text) values( N'update ' + @dest_owner + quotename(@dest_tabname) + N' set ' )
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
-- is the column marked for replication
-- is the column valid
exec @isset = sys.sp_isarticlecolbitset @this_col, @artid, @publishertype
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
if @isset = 1 and @isvalidcolumn = 1
begin
-- processing a column marked for replication and it is not computed
exec @isset = sys.sp_MSiscolpk @src_objid, @this_col, @publishertype
if @isset = 0
begin
-- not a PK column - check if we have a identity column that is being scripted as identity
-- we have to skip these identity columns
if not (@fscriptidentity = 1 and @publishertype = @pubtypemssqlserver and
columnproperty(@src_objid, @colname, 'IsIdentity') = 1)
begin
-- not a PK column and not a column scripted as identity
insert into #proctext(procedure_text) values(@spacer + quotename(@colname) + N' = @c' + convert( nvarchar(10), @art_col ))
select @spacer = N','
end
end -- end not a PK column
select @art_col = @art_col + 1
end -- column marked for replication and not computed
else if @isset = 1 and @isvalidcolumn = 0
begin
exec @isset = sys.sp_MSiscolpk @src_objid, @this_col, @publishertype
if @isset = 1
begin
select @art_col = @art_col + 1
end
end
fetch hccolid into @this_col, @colname
end -- end while cursor block
close hccolid
deallocate hccolid
exec sys.sp_scriptreconwhereclause @src_objid, @artid, @publishertype
end -- replicating non PK columns as well
else
begin
-- all article columns are included in the PK, & PK already exists, do nothing
insert into #proctext(procedure_text ) values( N'return' )
end -- replicating only PK columns
-- continue scripting
insert into #proctext(procedure_text) values (N'end')
insert into #proctext(procedure_text) values (N'else')
insert into #proctext(procedure_text) values (N'begin')
end -- end reconciliation mode
-- More preparation for insert statement
-- Generate strings for col names and variables
select @art_col = 0
,@spacer = 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
-- is the column marked for replication
-- is the column valid
exec @isset = sys.sp_isarticlecolbitset @this_col, @artid, @publishertype
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
if @isset = 1 and @isvalidcolumn = 1
begin
-- column marked for replication and it is not computed
select @art_col = @art_col + 1
insert into #collisttab(procedure_text, collist) values( @spacer + quotename(@colname), 1 )
insert into #collisttab(procedure_text, collist) values( @spacer + N'@c' + cast(@art_col as nvarchar(10)), 0 )
select @spacer = N','
end -- column marked for replication and not computed
else if @isset = 1 and @isvalidcolumn = 0 and @mode = @reconciliationmode
begin
-- Increment the column\parameter counter if this is a computed
-- primary key column
exec @isset = sys.sp_MSiscolpk @src_objid, @this_col, @publishertype
if @isset = 1
set @art_col = @art_col + 1
end
fetch hccolid into @this_col, @colname
end -- end while cursor block
close hccolid
deallocate hccolid
-- set identity_insert on
if @identity_insert = 1
begin
insert into #proctext(procedure_text) values( N'set identity_insert ' + @dest_owner + quotename(@dest_tabname) + ' on' )
end
-- script the insert statement now
insert into #proctext(procedure_text) values(N'insert into ' + @dest_owner + quotename(@dest_tabname) + N'( ' )
insert into #proctext(procedure_text)
select procedure_text from #collisttab where collist = 1 order by c1 asc
insert into #proctext(procedure_text) values( N' )' )
-- script the values() clause
if (@art_col > 0)
begin
insert into #proctext(procedure_text) values( N'values ( ' )
insert into #proctext(procedure_text)
select procedure_text from #collisttab where collist = 0 order by c1 asc
insert into #proctext(procedure_text) values( N' ) ')
end
drop table #collisttab
-- set identity_insert off
if @identity_insert = 1
begin
insert into #proctext(procedure_text) values( N'set identity_insert ' + @dest_owner + quotename(@dest_tabname) + ' off' )
end
-- continue scripting
if (@mode = @regularmode)
begin
-- regular scripting
if (@updsub_check = 1)
insert into #proctext(procedure_text) values( 'end')
end
else
begin
-- reconciliation scripting
insert into #proctext(procedure_text) values (N'end')
end
-- end 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:
-- send fragments to client
select procedure_text from #proctext order by c1 asc
-- all done
return 0
end
