create procedure sys.sp_MSmaketrancftproc
(
@article sysname,
@publication sysname,
@is_debug bit=0,
@alter bit = 0 -- if 1 script alter, otherwise script create
)
as
BEGIN
declare @source_table nvarchar(1000)
,@owner nvarchar(1000)
,@procname nvarchar(1000)
,@qualprocname nvarchar(1000)
,@source_objid int
,@artid int
,@pubid int
,@conflict_tableid int
,@conflict_table nvarchar(1000)
,@conflict_proc_id int
,@indid int
,@indkey int
,@ind_col_name nvarchar(1000)
,@qualname nvarchar(1000)
,@destqualname nvarchar(1000)
,@destowner nvarchar(1000)
,@dbname nvarchar(1000)
,@retcode smallint
,@retain_varname int
declare @colid int
,@colname nvarchar(1000)
,@coltype nvarchar(1000)
,@ccoltype nvarchar(1000)
,@termlen int
,@splitlen int
declare @argtabempty bit
,@seltabempty bit
,@sel2tabempty bit
,@valtabempty bit
,@paramtabempty bit
,@where_clausetabempty bit
,@decltabempty bit
,@assigntabempty bit
,@compinsertabempty bit
declare @argterm nvarchar(4000)
,@selterm nvarchar(4000)
,@sel2term nvarchar(4000)
,@updterm nvarchar(4000)
,@valterm nvarchar(4000)
,@paramterm nvarchar(4000)
,@where_term nvarchar(4000)
,@declterm nvarchar(4000)
,@assignterm nvarchar(4000)
,@compinsterm nvarchar(4000)
declare @cmd nvarchar(4000)
set nocount on
-- prepare the proc name and get the other parameters
select @artid = a.artid, @pubid = a.pubid, @source_table = object_name(a.objid),
@source_objid = a.objid, @destowner = a.dest_owner
from sysarticles a, syspublications p
where a.name = @article and
p.name = @publication and
a.pubid = p.pubid
-- Get the schema owner name for the object
select @owner = schema_name(schema_id)
from sys.objects
where object_id= @source_objid
-- Prepare the proc name
-- The source table should be owner qualified
select @source_table = QUOTENAME(@owner) + N'.' + QUOTENAME(@source_table)
if @alter = 0
begin
exec @retcode = sys.sp_MSgettranconflictname @publication=@publication,
@source_object= @source_table,
@str_prefix='sp_MScft_',
@conflict_table=@procname OUTPUT
end
--in case of alter, proc should already exists
else
begin
select @procname = object_name(ins_conflict_proc) from sysarticleupdates
where artid = @artid and pubid = @pubid
end
set @qualprocname = QUOTENAME(@owner)+ N'.'+ QUOTENAME(@procname)
-- The conflict table should exist before we do any conflict procs
select @conflict_tableid = conflict_tableid,
@conflict_table = OBJECT_NAME(conflict_tableid)
from sysarticleupdates
where artid = @artid and pubid = @pubid
if ( @conflict_tableid is NULL)
return (1)
-- To check if specified object exists in current database
select @qualname = case when (@owner is null or @owner = ' ') then QUOTENAME(@conflict_table)
else QUOTENAME(@owner) + N'.' + QUOTENAME(@conflict_table) end
if (object_id(@qualname) is NULL)
return (1)
-- The source table should have an unique index
exec @indid = sys.sp_MStable_has_unique_index @source_objid
if (@indid = 0)
return (1)
-- Get all the columns participating in the index of the source table
create table #indcoltab ( colname sysname collate database_default )
select @indkey = 1;
while (@indkey <= 16)
begin
select @ind_col_name = index_col(@source_table, @indid, @indkey)
if (@ind_col_name is not NULL)
insert into #indcoltab(colname) values(@ind_col_name)
else
select @indkey = 16
select @indkey = @indkey + 1
end
-- prepare destination table name (required for decentralized conflict processing)
select @destqualname = case when (@destowner is null or @destowner = ' ')
then QUOTENAME(@conflict_table)
else QUOTENAME(@destowner) + N'.' + QUOTENAME(@conflict_table) end
-- build the lists
select @argtabempty = 1
,@valtabempty = 1
,@paramtabempty = 1
,@seltabempty = 1
,@sel2tabempty = 1
,@decltabempty = 1
,@assigntabempty = 1
,@where_clausetabempty = 1
,@compinsertabempty = 1
create table #argtab ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
create table #valtab ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
create table #paramtab ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
create table #seltab ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
create table #sel2tab ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
create table #decltab ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
create table #assigntab ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
create table #where_clausetab ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
create table #compinstab ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null, termlen int null)
-- some predefined declares and assignments
select @cmd = N'
declare @reinit_code int, @subwins_code int, @pubwins_code int, @qcfttabrowid uniqueidentifier
,@retcode smallint, @compcmd nvarchar(max), @centralized_conflicts bit'
insert into #decltab(procedure_text) values(@cmd)
select @decltabempty = 0
select @cmd = N'
select @reinit_code = 3
,@subwins_code = 2
,@pubwins_code = 1
,@qcfttabrowid = NEWID()'
insert into #assigntab(procedure_text) values(@cmd)
select @cmd = N'
select @centralized_conflicts = centralized_conflicts
from dbo.syspublications where pubid = ' + cast(@pubid as nvarchar)
insert into #assigntab(procedure_text) values(@cmd)
select @assigntabempty = 0
-- cursor for enumerating each column
-- to build the terms for various lists
declare #argcursor cursor local FAST_FORWARD FOR
select column_id, max_length
from sys.columns
where is_computed = 0 and object_id=@conflict_tableid
order by column_id
FOR READ ONLY
select @retain_varname = 0
open #argcursor
fetch #argcursor into @colid, @termlen
while (@@FETCH_STATUS = 0)
begin
-- Get the column name and column type
exec sys.sp_MSget_type @conflict_tableid, @colid, @colname output, @coltype OUTPUT
if (@@ERROR<>0 or @retcode<>0)
return (1)
-- skip this specific column or if type is not returned
if ((@coltype IS NULL) or (rtrim(@coltype) like N'timestamp') or (LOWER(@colname) = 'qcfttabrowid'))
begin
-- do the next fetch and continue
fetch #argcursor into @colid, @termlen
continue
end
exec sys.sp_MSget_colinfo @conflict_tableid, @colid, NULL, 0, NULL, @ccoltype output
if (@@ERROR<>0 or @retcode<>0)
return (1)
-- parameterize the vars that are the column values of the source
-- table. For the columns that are specific to the conflict table
-- retain specific names for the vars
if (LOWER(@colname collate SQL_Latin1_General_CP1_CS_AS) = 'origin_datasource')
select @retain_varname = @colid
select @argterm = case when (@retain_varname = 0)
then N'@param' + cast(@colid as nvarchar)
else N'@' + @colname end
select @valterm = quotename(@colname)
,@paramterm = @argterm
select @updterm = @valterm + N' = ' + @argterm
if (@retain_varname = 0)
begin
if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'xml')
begin
select @selterm = @paramterm + N' = convert(xml, ' + @valterm + ') '
,@sel2term = @paramterm + N' = case when ' + @paramterm +
N' is NULL then convert(xml, ' + @valterm + N') else ' + @paramterm + N' end'
end
else
begin
select @selterm = @paramterm + N' = ' + @valterm
,@sel2term = @paramterm + N' = case when ' + @paramterm +
N' is NULL then ' + @valterm + N' else ' + @paramterm + N' end'
end
end
else
begin
select @selterm = NULL
,@sel2term = NULL
end
select @argterm = @argterm + N' ' + @coltype
-- Check if this is part of primary key / unique index
if (@colname in ( select colname from #indcoltab ) )
begin
-- this key assignment becomes part of where clause
select @where_term = @updterm
select @updterm = NULL
,@selterm = NULL
,@sel2term = NULL
end
else
select @where_term = NULL
-- special columns - process them as local var
if (LOWER(@colname collate SQL_Latin1_General_CP1_CS_AS) = 'insertdate' )
begin
select @declterm = N'
declare ' + @argterm
select @assignterm = N'
select ' + @paramterm + N' = GETDATE()'
select @argterm = NULL
end
else if (LOWER(@colname collate SQL_Latin1_General_CP1_CS_AS) = 'pubid' )
begin
select @declterm = N'
declare ' + @argterm
select @assignterm = N'
select ' + @paramterm + N' = ' + cast(@pubid as nvarchar)
select @argterm = NULL
end
else
begin
select @declterm = NULL
,@assignterm = NULL
end
-- build the term for compensating insert
if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'varchar')
begin
select @compinsterm = N' '''''' + sys.fn_replreplacesinglequote(' + @valterm + N') collate database_default + '''''''' '
,@termlen = case when (@termlen = -1) then 3500 else @termlen + 4 end
end
else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'nvarchar')
begin
select @compinsterm = N' N'''''' + sys.fn_replreplacesinglequote(' + @valterm + N') collate database_default + '''''''' '
,@termlen = case when (@termlen = -1) then 3500 else (@termlen * 2) + 4 end
end
else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'char')
begin
select @compinsterm = N' '''''' + sys.fn_replreplacesinglequote(CAST(RTRIM(' + @valterm + N') as nvarchar(4000))) collate database_default + '''''''' '
,@termlen = @termlen + 4
end
else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'nchar')
begin
select @compinsterm = N' N'''''' + sys.fn_replreplacesinglequote(CAST(RTRIM(' + @valterm + N') as nvarchar(4000))) collate database_default + '''''''' '
,@termlen = (@termlen * 2) + 4
end
else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('binary','varbinary'))
begin
select @compinsterm = N' '' + sys.fn_varbintohexstr(' + @valterm + N') collate database_default '
,@termlen = case when (@termlen = -1) then 3500 else (@termlen * 2) + 6 end
end
else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('bit','bigint','int','smallint','tinyint','decimal','numeric'))
begin
select @compinsterm = N' '' + CAST(' + @valterm + N' as nvarchar) '
,@termlen = 40
end
else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('float','real'))
begin
select @compinsterm = N' '' + CONVERT(nvarchar(60),' + @valterm + N', 2) '
,@termlen = 60
end
else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('money','smallmoney'))
begin
select @compinsterm = N' '' + CONVERT(nvarchar(40),' + @valterm + N', 2) '
,@termlen = 40
end
else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'uniqueidentifier')
begin
select @compinsterm = N' '''''' + CAST(' + @valterm + N' as nvarchar(40)) + '''''''' '
,@termlen = 40
end
else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('datetime','smalldatetime'))
begin
select @compinsterm = N' '''''' + CONVERT(nvarchar(40), ' + @valterm + N', 112) + N'' '' + CONVERT(nvarchar(40), ' + @valterm + N', 114) + '''''''' '
,@termlen = 40
end
else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('date','time','datetime2','datetimeoffset'))
begin
select @compinsterm = N' '''''' + CONVERT(nvarchar(40), ' + @valterm + N', 121) + '''''''' '
,@termlen = 40
end
else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'sql_variant')
begin
select @compinsterm = N' '' + sys.fn_sqlvarbasetostr(' + @valterm + N' ) collate database_default '
,@termlen = case when (@termlen = -1) then 3500 else @termlen + 4 end
end
else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'xml')
begin
select @compinsterm = N' N'''''' + CAST(' + @valterm + N' as nvarchar(max)) + '''''''' '
,@termlen = case when (@termlen = -1) then 3500 else (@termlen * 2) + 4 end
end
else
begin
select @compinsterm = N' '' + CAST(' + @valterm + N' as nvarchar) '
,@termlen = @termlen + 4
end
-- Now append to the various lists
if (@argterm is NOT NULL)
begin
if (@argtabempty = 1)
begin
select @argtabempty = 0
,@cmd = N'
' + @argterm
end
else
select @cmd = N',
' + @argterm
insert into #argtab(procedure_text) values(@cmd)
end
if (@valterm is NOT NULL)
begin
if (@valtabempty = 1)
begin
select @valtabempty = 0
,@cmd = @valterm
end
else
select @cmd = N', ' + @valterm
insert into #valtab(procedure_text) values(@cmd)
end
if (@paramterm is NOT NULL)
begin
if (@paramtabempty = 1)
begin
select @paramtabempty = 0
,@cmd = @paramterm
end
else
select @cmd = N', ' + @paramterm
insert into #paramtab(procedure_text) values(@cmd)
end
if (@selterm is NOT NULL)
begin
if (@seltabempty = 1)
begin
select @seltabempty = 0
,@cmd = N'
' + @selterm
end
else
select @cmd = N'
,' + @selterm
insert into #seltab(procedure_text) values(@cmd)
end
if (@sel2term is NOT NULL)
begin
if (@sel2tabempty = 1)
begin
select @sel2tabempty = 0
,@cmd = N'
' + @sel2term
end
else
select @cmd = N'
,' + @sel2term
insert into #sel2tab(procedure_text) values(@cmd)
end
if (@where_term is NOT NULL)
begin
if (@where_clausetabempty = 1)
begin
select @where_clausetabempty = 0
,@cmd = @where_term
end
else
select @cmd = N' AND
' + @where_term
insert into #where_clausetab(procedure_text) values(@cmd)
end
if (@declterm is NOT NULL)
begin
select @cmd = @declterm + N'
'
insert into #decltab(procedure_text) values(@cmd)
end
if (@assignterm is NOT NULL)
begin
select @cmd = @assignterm + N'
'
insert into #assigntab(procedure_text) values(@cmd)
end
if (@compinsterm is NOT NULL)
begin
if (@compinsertabempty = 1)
begin
select @compinsertabempty = 0
,@cmd = N' + ISNULL(''' + @compinsterm + N', ''null'')'
end
else
select @cmd = N' + ISNULL('',' + @compinsterm + N', '',null'')'
insert into #compinstab(procedure_text, termlen) values(@cmd, (@termlen + len(@cmd)))
end
-- do the next fetch of columns cursor
fetch #argcursor into @colid, @termlen
end -- while loop of columns cursor
close #argcursor
deallocate #argcursor
drop table #indcoltab
-- generation phase
BEGIN TRAN
save tran sp_MSmaketrancftproc
-- create temp table to select the command text out of
if exists (select * from sys.objects where name = 'tempcmd' and schema_id = schema_id('dbo'))
drop table dbo.tempcmd
create table dbo.tempcmd ( c1 int identity NOT NULL, cmdtext nvarchar(4000) NULL)
-- create header
insert into dbo.tempcmd(cmdtext)
values(case @alter when 0 then N'create' else N'alter' end + ' procedure '+@qualprocname + N'(
')
-- insert the arglist
insert into dbo.tempcmd(cmdtext) select procedure_text from #argtab order by c1
insert into dbo.tempcmd(cmdtext) values(N' ,@subcriber sysname = NULL, @subdb sysname = NULL )
as
begin
')
-- insert the declare list
insert into dbo.tempcmd(cmdtext) select procedure_text from #decltab order by c1
insert into dbo.tempcmd(cmdtext) values(N'
')
-- script out security check
insert into dbo.tempcmd(cmdtext) values(N'
' + N'
' + N'-- Check for security
' + N'
exec @retcode = sys.sp_MSreplcheck_pull @publication = N'''+ sys.fn_replreplacesinglequote(@publication) collate database_default + N'''
if (@retcode != 0 or @@error != 0)
return -1
')
-- insert the assignment list (for declared vars)
insert into dbo.tempcmd(cmdtext) select procedure_text from #assigntab order by c1
-- do the select for the case where we need to retain values of publisher
insert into dbo.tempcmd(cmdtext) values(N'
if (@reason_code = @subwins_code)
begin
select ')
insert into dbo.tempcmd(cmdtext) select procedure_text from #seltab order by c1
insert into dbo.tempcmd(cmdtext) values(N'
from ' + @source_table + N' where ')
insert into dbo.tempcmd(cmdtext) select procedure_text from #where_clausetab order by c1
insert into dbo.tempcmd(cmdtext) values(N'
end')
insert into dbo.tempcmd(cmdtext) values(N'
else
begin
select ')
insert into dbo.tempcmd(cmdtext) select procedure_text from #sel2tab order by c1
insert into dbo.tempcmd(cmdtext) values(N'
from ' + @source_table + N' where ')
insert into dbo.tempcmd(cmdtext) select procedure_text from #where_clausetab order by c1
insert into dbo.tempcmd(cmdtext) values(N'
end
')
-- insert the conflict row in the publisher cft table
insert into dbo.tempcmd(cmdtext) values(N'
insert into ' + @qualname + N'(')
insert into dbo.tempcmd(cmdtext) select procedure_text from #valtab order by c1
insert into dbo.tempcmd(cmdtext) values(N',[qcfttabrowid])
values (')
insert into dbo.tempcmd(cmdtext) select procedure_text from #paramtab order by c1
insert into dbo.tempcmd(cmdtext) values(N',@qcfttabrowid)
')
-- generate compensating command decentralized logging
-- depending on the number of columns, we will split the compensating
-- command into several compensating commands
select @compinsertabempty = 1
,@splitlen = 0
select @cmd = N'
if (@centralized_conflicts = 0)
begin
select @compcmd = N''insert into ' + sys.fn_replreplacesinglequote(@destqualname) collate database_default + N' ( '
insert into dbo.tempcmd(cmdtext) values(@cmd)
select @splitlen = @splitlen + len(@cmd)
-- build the first part of compensating command for each column using the values list
declare #htempcur cursor local for
select sys.fn_replreplacesinglequote(procedure_text) from #valtab order by c1
for read only
open #htempcur
fetch #htempcur into @compinsterm
while (@@fetch_status = 0)
begin
insert into dbo.tempcmd(cmdtext) select @compinsterm
select @splitlen = @splitlen + len(@cmd)
-- if we have processed enough terms then split the command
if (@splitlen > 3500)
begin
-- script where clause
select @cmd = N'''
from ' + @qualname + N' where qcfttabrowid = @qcfttabrowid and tranid = @tranid'
insert into dbo.tempcmd(cmdtext) values(@cmd)
-- script the compensating command to log
select @cmd = N'
exec @retcode = sys.sp_MSadd_compensating_cmd @subcriber, @subdb, @compcmd, '
+ CAST(@artid as nvarchar(10)) + N', ' + CAST(@pubid as nvarchar(10)) + N',1,0,'
+ CAST(@compinsertabempty as nvarchar(4)) + N'
if (@@error != 0 or @retcode != 0)
return 1
select @compcmd = N'''
insert into dbo.tempcmd(cmdtext) values(@cmd)
-- reinitialize the loop params
select @compinsertabempty = 0
,@splitlen = 0
end
-- fetch next value
fetch #htempcur into @compinsterm
end
close #htempcur
deallocate #htempcur
-- script the values part of the insert
insert into dbo.tempcmd(cmdtext) values(N', [qcfttabrowid] ) values ('' ')
select @splitlen = @splitlen + len(@cmd)
,@termlen = 0
-- build the last part of compensating command for each column using the compinstab list
declare #htempcur cursor local for
select procedure_text, termlen from #compinstab order by c1
for read only
open #htempcur
fetch #htempcur into @compinsterm, @termlen
while (@@fetch_status = 0)
begin
-- if we have processed enough terms then split the command
if (@splitlen + @termlen < 3500)
begin
insert into dbo.tempcmd(cmdtext) select @compinsterm
select @splitlen = @splitlen + @termlen
end
else
begin
-- script where clause
select @cmd = N'
from ' + @qualname + N' where qcfttabrowid = @qcfttabrowid and tranid = @tranid'
insert into dbo.tempcmd(cmdtext) values(@cmd)
-- script the compensating command to log
select @cmd = N'
exec @retcode = sys.sp_MSadd_compensating_cmd @subcriber, @subdb, @compcmd, '
+ CAST(@artid as nvarchar(10)) + N', ' + CAST(@pubid as nvarchar(10)) + N',1,0,'
+ CAST(@compinsertabempty as nvarchar(4)) + N'
if (@@error != 0 or @retcode != 0)
return 1
select @compcmd = N'' '''
insert into dbo.tempcmd(cmdtext) values(@cmd)
-- reinitialize the loop params
insert into dbo.tempcmd(cmdtext) select @compinsterm
select @splitlen = @termlen, @compinsertabempty = 0
end
-- fetch next compinsert term
fetch #htempcur into @compinsterm, @termlen
end
close #htempcur
deallocate #htempcur
-- script the final compensating command
select @cmd = N' + '', '''''' + CAST([qcfttabrowid] as nvarchar(40)) + '''''''' + N'' ) ''
from ' + @qualname + N' where qcfttabrowid = @qcfttabrowid and tranid = @tranid'
insert into dbo.tempcmd(cmdtext) values(@cmd)
select @cmd = N'
exec @retcode = sys.sp_MSadd_compensating_cmd @subcriber, @subdb, @compcmd, '
+ CAST(@artid as nvarchar(10)) + N', ' + CAST(@pubid as nvarchar(10)) + N',0,0,'
+ CAST(@compinsertabempty as nvarchar(4)) + N'
if (@@error != 0 or @retcode != 0)
return 1 '
insert into dbo.tempcmd(cmdtext) values(@cmd)
insert into dbo.tempcmd(cmdtext) values(N'
end
end')
-- process based on debug flag
if (@is_debug = 0)
begin
-- Now we select out the command text pieces in proper order so that our caller,
-- xp_execresultset will execute the command that creates the stored procedure.
select @dbname = db_name()
,@cmd = N'select cmdtext from dbo.tempcmd order by c1'
exec @retcode = sys.xp_execresultset @cmd, @dbname
if (@@error != 0 or @retcode != 0)
begin
-- roll back the tran
rollback tran sp_MSmaketrancftproc
return (1)
end
-- Check if we create the proc and update sysarticleupdates
select @conflict_proc_id = object_id from sys.objects where name = @procname and type = 'P '
if (@conflict_proc_id is NULL or @conflict_proc_id = 0)
begin
-- roll back the tran
rollback tran sp_MSmaketrancftproc
return (1)
end
else
begin
update dbo.sysarticleupdates set ins_conflict_proc = @conflict_proc_id
where artid = @artid and pubid = @pubid
if @@error <> 0
begin
-- roll back the tran
rollback tran sp_MSmaketrancftproc
return (1)
end
-- mark the proc as system object
if (@owner in ('dbo','INFORMATION_SCHEMA'))
begin
exec @retcode = dbo.sp_MS_marksystemobject @qualprocname
if (@@error != 0 or @retcode != 0)
begin
-- roll back the tran
rollback tran sp_MSmaketrancftproc
return (1)
end
end
end
end
else
begin
-- debug mode - just return the script as resultset
select cmdtext from dbo.tempcmd order by c1
end
-- commit tran
COMMIT TRAN
-- drop the temp tables
drop table dbo.tempcmd
drop table #argtab
drop table #valtab
drop table #paramtab
drop table #seltab
drop table #sel2tab
drop table #decltab
drop table #assigntab
drop table #where_clausetab
drop table #compinstab
-- all done
return (0)
END
