May 21, 2012

sp_MSmakebatchinsertproc (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSmakebatchinsertproc(nvarchar @tablename
, nvarchar @ownername
, nvarchar @procname
, uniqueidentifier @pubid
, uniqueidentifier @artid
, bit @generate_subscriber_proc
, nvarchar @destination_owner)

MetaData:

 create procedure sys.sp_MSmakebatchinsertproc   
(@tablename sysname, @ownername sysname, @procname sysname, @pubid uniqueidentifier, @artid uniqueidentifier,
@generate_subscriber_proc bit = 0, @destination_owner sysname = NULL)
as
declare @argname sysname
declare @id int
declare @sync_objid int
declare @qualified_name nvarchar(270)
declare @idstr nvarchar(100)
declare @iscomputed tinyint
declare @xtype int
declare @is_identitynotforreplication bit
declare @retcode int
declare @colname nvarchar(140)
declare @typename nvarchar(258)
declare @base_typename nvarchar(140)
declare @schname nvarchar(140) -- track the typename owner if it is CLR UDT
declare @isidentitycolumn bit
declare @len smallint
declare @prec int
declare @scale int
declare @tablenick int
declare @tablenickstr nvarchar(12)
declare @cmdpiece nvarchar(4000)
declare @maintainsmetadata bit
declare @colid int -- index in sys.columns, used to iterate through sys.columns
declare @colordinal int -- index in @setbm, used to interate actual data sent across
declare @colordstr nvarchar(5) -- @colordinal stringification
declare @colcount int
declare @maxparams int
declare @batchingfactor int
declare @rownumber int
declare @rownumberstr nvarchar(5)
declare @insertcolumnsstarted bit
declare @colnumber int
declare @colidstr nvarchar(5)
declare @colalias nvarchar(100)
declare @isrowguidcol tinyint
declare @partition_options tinyint
declare @atpublisher bit
declare @publication_number smallint
declare @qualified_sync_view nvarchar(517)
declare @rgcolname nvarchar(140)
declare @before_image_objid int
declare @qualified_before_image_table nvarchar(517)
declare @col_in_bitable bit
declare @bitable_colnumber int
declare @unquoted_colname sysname
declare @maxschemaguid uniqueidentifier
declare @phaseid int
declare @command1 nvarchar(4000)
declare @command2 nvarchar(4000)
declare @command3 nvarchar(4000)
declare @max_colv_size_in_bytes int
declare @schema_option varbinary(8)

set nocount on

-- Check for subscribing permission
exec @retcode=sys.sp_MSreplcheck_subscribe
if @retcode<>0 or @@ERROR<>0 return (0)

if @ownername is NULL or @ownername=''
select @qualified_name = QUOTENAME(@tablename)
else
select @qualified_name = QUOTENAME(@ownername) + '.' + QUOTENAME(@tablename)

select @id = object_id(@qualified_name)
if @id is NULL
return (1)

if exists (select * from dbo.sysmergearticles where objid = @id and column_tracking = 1)
begin
declare @current_col_count int, @missing_col_count int
select @current_col_count = count(*)
from sys.columns where object_id = @id
select @missing_col_count = coalesce((select max(missing_col_count)
from dbo.sysmergearticles where objid = @id), 0)
-- 12 bytes per column in table + 1 trailing byte for colv.
select @max_colv_size_in_bytes = 12 * (@current_col_count + @missing_col_count) + 1
if @max_colv_size_in_bytes > 2953
select @max_colv_size_in_bytes = 2953
end
else
select @max_colv_size_in_bytes = 1

select @rgcolname = QUOTENAME(name) from sys.columns where object_id = @id and is_rowguidcol = 1
if @rgcolname is null
set @rgcolname = 'rowguid'

select @sync_objid = sync_objid,
@partition_options = partition_options, @schema_option=schema_option, @tablenick = nickname
from dbo.sysmergepartitioninfoview where artid = @artid and pubid=@pubid
if @tablenick is NULL
return (1)

set @tablenickstr = rtrim(convert(nchar, @tablenick))

if exists (select * from dbo.sysmergearticles where pubid = @pubid and nickname = @tablenick
and len(subset_filterclause) > 0)
or exists (select * from dbo.sysmergesubsetfilters where pubid = @pubid and
(art_nickname = @tablenick or join_nickname = @tablenick))
begin
exec @retcode = sys.sp_MSget_qualified_name @sync_objid, @qualified_sync_view output
if @sync_objid is not NULL and @qualified_sync_view is NULL
return 1
end
else
begin
select @qualified_sync_view = NULL
end

select @before_image_objid = max(before_image_objid) from dbo.sysmergearticles where objid = @id and
before_image_objid is not null
exec @retcode = sys.sp_MSget_qualified_name @before_image_objid, @qualified_before_image_table output
if @before_image_objid is not NULL and @qualified_before_image_table is NULL
return 1

if @partition_options = 2
begin
-- if this is a republisher of this article, and we are currently
-- downloading from the top-level publisher, then pretend that this is
-- not a well-partitioned article. This is done such that the partition evaluation
-- and setrowmetadata is done appropriately.
if sys.fn_MSmerge_islocalpubid(@pubid) = 0
and exists (select * from dbo.sysmergearticles
where artid = @artid
and sys.fn_MSmerge_islocalpubid(pubid) = 1)
select @partition_options = 0
end

set @idstr = rtrim(convert(nchar, @id))

select @insertcolumnsstarted = 0


if @generate_subscriber_proc = 1
begin
select @atpublisher = 0
select @qualified_name = quotename(@destination_owner) + '.' + quotename(@tablename)
if exists (select 1 from dbo.sysmergearticles where artid=@artid and pubid=@pubid and upload_options in (1,2))
select @maintainsmetadata = 0
else
select @maintainsmetadata = 1
end
else
begin
set @maintainsmetadata= sys.fn_MSarticle_allows_DML_at_this_replica(@artid, default)
select @atpublisher = sys.fn_MSmerge_islocalpubid(@pubid)
select @maxschemaguid = sys.fn_GetArticleSchemaVersionGuid(@artid, @pubid)
end


select top 1 @publication_number = publication_number
from dbo.sysmergepublications
where pubid = @pubid

-- compute batching factor. For each row we need
-- max params is 1024-2 for the first 2 parameters which is number of rows to be inserted, partition id
select @maxparams=1024-2

-- subtract one more since we have a @maxschemaguidforarticle parameter on the publisher
if @atpublisher = 1
select @maxparams = @maxparams - 1

if @sync_objid = @id
select @colcount = count(*) from sys.columns where object_id = @id and is_computed=0 and user_type_id<>type_id('timestamp')
else
select @colcount = count(*)
from sys.columns cs inner join sys.columns co on co.object_id=@id and co.name = cs.name
where cs.object_id = @sync_objid and
co.is_computed=0 and co.user_type_id<>type_id('timestamp')

-- for regular articles for each row we need rowguid, generation, lineage and colv in addition
-- to the list of user table columns. For download only articles we only need rowguid
if 1 = @maintainsmetadata
select @colcount = @colcount + 4
else
select @colcount = @colcount + 1

select @batchingfactor = @maxparams/@colcount
if @batchingfactor > 100
select @batchingfactor = 100


-- create temp table to select the command text out of
declare @tempcmd table (phase int NOT NULL, step int identity NOT NULL, cmdtext nvarchar(4000) collate database_default null)

-- phase 0 : create procedure and fixed part of argument list

if @atpublisher = 1
begin
set @cmdpiece = 'create procedure dbo.' + QUOTENAME(@procname) + ' (
@maxschemaguidforarticle uniqueidentifier,
@rows_tobe_inserted int,
@partition_id int = null '

insert into @tempcmd (phase, cmdtext) values (0, @cmdpiece)
end
else
begin
set @cmdpiece = 'create procedure dbo.' + QUOTENAME(@procname) + ' (
@rows_tobe_inserted int,
@partition_id int = null '

insert into @tempcmd (phase, cmdtext) values (0, @cmdpiece)
end

-- phase 10 is rest of the arguments built based on batching factor etc
select @cmdpiece = '
) as
begin
declare @errcode int
declare @retcode int
declare @rowcount int
declare @error int
declare @rows_in_contents int
declare @rows_inserted_into_contents int
declare @publication_number smallint
declare @gen_cur bigint
declare @rows_in_tomb bit
declare @rows_in_syncview int
declare @marker uniqueidentifier

set nocount on

set @errcode= 0
set @publication_number = '
+ convert(nvarchar(10), @publication_number) + '

if ({ fn ISPALUSER('
'' + convert(nvarchar(36),@pubid) + ''') } <> 1)
begin
RAISERROR (14126, 11, -1)
return 4
end

if @rows_tobe_inserted is NULL or @rows_tobe_inserted <=0
return 0

'


insert into @tempcmd (phase, cmdtext) values (20, @cmdpiece)

if @atpublisher = 1
begin
-- phase 5 - check the max schema guid for article and error out if they do not match
set @cmdpiece= '
declare @maxschemaguid uniqueidentifier
select @maxschemaguid = '
'' + convert(nvarchar(36),@maxschemaguid) + '''
if ((@maxschemaguidforarticle IS NOT NULL) and (@maxschemaguid <> @maxschemaguidforarticle))
begin
RAISERROR (25007, 11, -1)
return 5
end '

insert into @tempcmd (phase, cmdtext) values (20, @cmdpiece)
end

select @cmdpiece = '
begin tran
save tran batchinsertproc

exec @retcode = sys.sp_MSmerge_getgencur_public '
+ @tablenickstr + ', @rows_tobe_inserted, @gen_cur output
if @retcode<>0 or @@error<>0
return 4

'

insert into @tempcmd (phase, cmdtext) values (20, @cmdpiece)

-- we need to insert the row metadata first so that the insert trigger
-- does not insert the contents row.
if 1 = @maintainsmetadata
begin
-- if any of the rows exist in tombstone we will abort the batched insert
select @cmdpiece = '
select @rows_in_tomb = 0
select @rows_in_tomb = 1 from ('

insert into @tempcmd (phase, cmdtext) values (22, @cmdpiece)
-- list of rowguids will be in phase 25
insert into @tempcmd (phase, cmdtext) select phase, cmdtext from sys.fn_MSgetrowguidvirtualtable(25, @batchingfactor) order by step
select @cmdpiece = '
) as rows
inner join dbo.MSmerge_tombstone tomb with (rowlock)
on tomb.rowguid = rows.rowguid
and tomb.tablenick = '
+ @tablenickstr + '
and rows.rowguid is not NULL

if @rows_in_tomb = 1
begin
raiserror(20692, 16, -1, '
'' + sys.fn_replreplacesinglequote(@tablename) + ''')
set @errcode=3
goto Failure
end'

insert into @tempcmd (phase, cmdtext) values (27, @cmdpiece)

select @cmdpiece = '

select @marker = newid()
insert into dbo.MSmerge_contents with (rowlock)
(rowguid, tablenick, generation, partchangegen, lineage, colv1, marker)
select rows.rowguid, '
+ @tablenickstr + ', rows.generation, (-rows.generation), rows.lineage, rows.colv, @marker
from ('

insert into @tempcmd (phase, cmdtext) values (30, @cmdpiece)
-- phase 50 will be virtual table with the row metadata
select @cmdpiece = '
) as rows
where rows.rowguid is not NULL '

insert into @tempcmd (phase, cmdtext) values (60, @cmdpiece)

if @partition_options > 1
begin
select @cmdpiece = '
and rows.lineage is not null '

insert into @tempcmd (phase, cmdtext) values (61, @cmdpiece)
end

select @cmdpiece = '
select @rows_inserted_into_contents = @@rowcount, @error = @@error
if @error<>0
begin
set @errcode=3
goto Failure
end'

insert into @tempcmd (phase, cmdtext) values (62, @cmdpiece)

if @partition_options < 2
begin
select @cmdpiece = '
if (@rows_inserted_into_contents <> @rows_tobe_inserted)
begin
raiserror(20693, 16, -1, '
'' + sys.fn_replreplacesinglequote(@tablename) + ''')
set @errcode=4
goto Failure
end'

insert into @tempcmd (phase, cmdtext) values (63, @cmdpiece)
end

if @partition_options > 1 and @atpublisher = 1
begin
select @cmdpiece = '
if @partition_id is not null
begin
insert into dbo.MSmerge_current_partition_mappings with (rowlock)
(tablenick, rowguid, publication_number, partition_id)
select distinct '
+ @tablenickstr + ', rows.rowguid, @publication_number, @partition_id
from ('

insert into @tempcmd (phase, cmdtext) values (70, @cmdpiece)
-- phase 80 will be virtual table with the row metadata
select @cmdpiece = '
) as rows
where rows.rowguid is not NULL
and rows.lineage is not null
select @error = @@error
if @error<>0
begin
set @errcode= 3
goto Failure
end
end'

insert into @tempcmd (phase, cmdtext) values (90, @cmdpiece)
end
end

-- phase 100 is the insert statement
select @cmdpiece = '
insert into '
+ @qualified_name + ' with (rowlock) ('
insert into @tempcmd (phase, cmdtext) values (100, @cmdpiece)
-- phase 120 will have all the column names
-- now for completing insert and the select from the vtable
select @cmdpiece = ')
select '

insert into @tempcmd (phase, cmdtext) values (150, @cmdpiece)
-- phase 170 will contain column names selected from the virtual table
-- complete the select
select @cmdpiece = '
from ('

insert into @tempcmd (phase, cmdtext) values (180, @cmdpiece)
-- phase 200 will have the virtual table declaration for all the column values
select @cmdpiece = '
) as rows
where rows.rowguid is not NULL
select @rowcount = @@rowcount, @error = @@error'

insert into @tempcmd (phase, cmdtext) values (250, @cmdpiece)

-- phase 250 will also contain the optional identity insert off

select @cmdpiece = '
if (@rowcount <> @rows_tobe_inserted) or (@error <> 0)
begin
set @errcode= 3
goto Failure
end
'

insert into @tempcmd (phase, cmdtext) values (300, @cmdpiece)

-- if @qualified_sync_view is not NULL and partition_id is not null check if
-- the row is present in sync_view. If NOT do the following:
-- 1. insert a past partition mapping for the row
-- 2. update generation and partchangegen to 0
-- 3. if there is a before image table, insert the row into the before image table
if @atpublisher = 1 and @qualified_sync_view is not NULL
begin
if @partition_options>0
begin
-- for partition_options > 0 we will not allow out of partition inserts
select @cmdpiece = '
select @rows_in_syncview = count (*) from '
+ @qualified_sync_view + ' syncview with (READPAST)
where syncview.'
+ @rgcolname + ' in
('

insert into @tempcmd (phase, cmdtext) values (320, @cmdpiece)
-- phase 330 will be rowguids
insert into @tempcmd (phase, cmdtext) select phase, cmdtext from sys.fn_MSgetrowguids(330, @batchingfactor) order by step
select @cmdpiece = '
)

if @rows_in_syncview <> @rows_tobe_inserted
begin
raiserror(20703, 16, -1, '
'' + sys.fn_replreplacesinglequote(@tablename) + ''', ' + convert(char(3), @partition_options) + ')
set @errcode= 3
goto Failure
end'

insert into @tempcmd (phase, cmdtext) values (340, @cmdpiece)
end
else
begin
select @cmdpiece = '
if @partition_id is not NULL
begin
insert into dbo.MSmerge_past_partition_mappings with (rowlock)
(publication_number, tablenick, rowguid, partition_id, generation, reason)
select @publication_number, '
+ @tablenickstr + ', rows.rowguid, @partition_id, @gen_cur, 0
from ('

insert into @tempcmd (phase, cmdtext) values (320, @cmdpiece)
-- phase 330 will be virtual table with the rowguids
insert into @tempcmd (phase, cmdtext) select phase, cmdtext from sys.fn_MSgetrowguidvirtualtable(330, @batchingfactor) order by step
select @cmdpiece = '
) as rows
where not exists (select syncview.'
+ @rgcolname + ' from ' + @qualified_sync_view + ' syncview with (READPAST) where
syncview.'
+ @rgcolname + ' = rows.rowguid)
and rows.rowguid is not NULL
if @@error<>0
begin
set @errcode= 3
goto Failure
end
end'

insert into @tempcmd (phase, cmdtext) values (340, @cmdpiece)

select @cmdpiece = '
update dbo.MSmerge_contents with (rowlock)
set generation = @gen_cur, partchangegen = @gen_cur
from ('

insert into @tempcmd (phase, cmdtext) values (350, @cmdpiece)
-- phase 360 will be virtual table with the rowguids
insert into @tempcmd (phase, cmdtext) select phase, cmdtext from sys.fn_MSgetrowguidvirtualtable(360, @batchingfactor) order by step
select @cmdpiece = '
) as rows
inner join dbo.MSmerge_contents cont with (rowlock, READPAST)
on cont.rowguid = rows.rowguid and cont.tablenick = '
+ @tablenickstr + '
and rows.rowguid is not null
left outer join '
+ @qualified_sync_view + ' syncview with (READPAST)
on syncview.'
+ @rgcolname + ' = rows.rowguid
where syncview.'
+ @rgcolname + ' is NULL
and rows.rowguid is not null

if @@error<>0
begin
set @errcode= 3
goto Failure
end'

insert into @tempcmd (phase, cmdtext) values (370, @cmdpiece)

if @qualified_before_image_table is not NULL
begin
select @cmdpiece = '
insert into '
+ @qualified_before_image_table + ' with (rowlock)
(generation, system_delete'

insert into @tempcmd (phase, cmdtext) values (380, @cmdpiece)
-- the list of columns in the before image table are filtering columns, generation, system_delete
-- phase 390 will be the list of filtering columns and rowguid column
select @cmdpiece = ')
select @gen_cur, 1'

insert into @tempcmd (phase, cmdtext) values (400, @cmdpiece)
-- phase 410 will be the filtering column and rowguid selection from the virtual table
select @cmdpiece = '
from ( '

insert into @tempcmd (phase, cmdtext) values (420, @cmdpiece)
-- phase 430 will be the virtual table definition
select @cmdpiece = '
) as rows
left outer join '
+ @qualified_sync_view + ' syncview with (READPAST)
on syncview.'
+ @rgcolname + ' = rows.rowguid
and rows.rowguid is not null
where syncview.'
+ @rgcolname + ' is NULL
and rows.rowguid is not null

if @@error<>0
begin
set @errcode= 3
goto Failure
end'

insert into @tempcmd (phase, cmdtext) values (440, @cmdpiece)
end
end
end


if 1 = @maintainsmetadata
begin
select @cmdpiece = '
exec @retcode = sys.sp_MSdeletemetadataactionrequest '
'' + convert(nvarchar(36),@pubid) + ''', ' + @tablenickstr
set @rownumber = 1
while @rownumber <= @batchingfactor
begin
select @cmdpiece = @cmdpiece + ',
@rowguid'
+ convert(nvarchar(3), @rownumber)
select @rownumber = @rownumber + 1
end
select @cmdpiece = @cmdpiece + '
if @retcode<>0 or @@error<>0
goto Failure
'

insert into @tempcmd (phase, cmdtext) values (10200, @cmdpiece)
end

select @cmdpiece = '
commit tran
return 1

Failure:
rollback tran batchinsertproc
commit tran
return 0
end
'

insert into @tempcmd (phase, cmdtext) values (15000, @cmdpiece)

-- this loop constructs the formal parameter declarations, the insert statement with actual column names and
-- the union clause which does a union over all rows.
-- phase 10 is the declarations, the rowsunion clause will be inserted into phases 200,
-- the metadata union clause will be inserted into phase 400.

-- using these instead of directly inserting in the temp table is a performance optimization
select @command1 = ''
select @command2 = ''
select @command3 = ''
set @colordinal = 1
set @rownumber = 1
while @rownumber <= @batchingfactor
begin
-- insert the metadada parameters for this row.
set @rownumberstr = convert(nvarchar(5), @rownumber)

if 1 = @maintainsmetadata
begin
select @command1 = @command1 + ',
@rowguid'
+ @rownumberstr + ' uniqueidentifier = NULL,
@generation'
+ @rownumberstr + ' bigint = NULL,
@lineage'
+ @rownumberstr + ' varbinary(311) = NULL,
@colv'
+ @rownumberstr + ' varbinary(' + convert(nvarchar(13), @max_colv_size_in_bytes) + ') = NULL'
-- insert into @tempcmd (phase, cmdtext) values (10, @cmdpiece)

if @rownumber = 1
select @command2 = @command2 + '
select @rowguid'
+ @rownumberstr + ' as rowguid, @generation' + @rownumberstr + ' as generation, @lineage' + @rownumberstr + ' as lineage, @colv' + @rownumberstr + ' as colv'
else
select @command2 = @command2 + ' union all
select @rowguid'
+ @rownumberstr + ' as rowguid, @generation' + @rownumberstr + ' as generation, @lineage' + @rownumberstr + ' as lineage, @colv' + @rownumberstr + ' as colv'

if (datalength(@command2) > 7500) or (@rownumber = @batchingfactor)
begin
insert into @tempcmd (phase, cmdtext) values (50, @command2)

if @partition_options > 1 and @atpublisher = 1
insert into @tempcmd (phase, cmdtext) values (80, @command2)

select @command2 = ''
end
end
else
begin
select @command1 = @command1 + ',
@rowguid'
+ @rownumberstr + ' uniqueidentifier = NULL'
-- insert into @tempcmd (phase, cmdtext) values (10, @cmdpiece)
end

-- now loop over columns
select @colnumber = 1
select @bitable_colnumber = 1
if @sync_objid = @id
select @colid = min (column_id) from sys.columns where object_id = @id and is_computed=0 and user_type_id<>type_id('timestamp')
else
select @colid = min(cs.column_id)
from sys.columns cs inner join sys.columns co on co.object_id=@id and co.name = cs.name
where cs.object_id = @sync_objid and
co.is_computed=0 and co.user_type_id<>type_id('timestamp')

select top 1 @colname = QUOTENAME(C.name),
@unquoted_colname = C.name,
@typename = type_name(C.user_type_id),
@base_typename = type_name(C.system_type_id),
@schname=case when S.name<>'sys' and S.name<>'dbo'
then QUOTENAME(S.name)+'.' else '' end,
@len = C.max_length,
@prec = C.precision,
@scale = C.scale
from sys.columns C
INNER JOIN sys.types T ON C.user_type_id = T.user_type_id
INNER JOIN sys.schemas S ON T.schema_id = S.schema_id
where C.object_id = @sync_objid and C.column_id = @colid

select @isidentitycolumn = is_identity,
@iscomputed=is_computed,
@xtype=user_type_id,
@isrowguidcol = is_rowguidcol,
@is_identitynotforreplication = convert(bit, isnull(columnproperty(@id, name, 'IsIdNotForRepl'), 0))
from sys.columns
where object_id = @id and QUOTENAME(name) = @colname
if @base_typename='nvarchar' or @base_typename='nchar' -- a unit of nchar takes 2 bytes
select @len = @len/2

while (@colname is not null)
begin
if (@iscomputed=1 OR type_name(@xtype)='timestamp')
goto Next_Column

set @colordstr = convert(nvarchar(4), @colordinal)
set @colidstr = convert(nvarchar(4), @colid)

if @generate_subscriber_proc = 0
exec @retcode = sys.sp_MSmaptype @typename out, @len, @prec, @scale
else
exec @retcode = sys.sp_MSmap_subscriber_type @xtype, @len, @prec, @scale, @schema_option, @typename out, @schname out
if @@error<>0 OR @retcode <>0 return (1)

select @argname = '@p' + rtrim(convert(nchar, @colordinal))
if @isrowguidcol = 1
select @colalias = 'rowguid'
else
select @colalias = 'c' + @colidstr

-- add to argument list (phase 1)
set @command1 = @command1 + ',
'
+ @argname + ' ' + @schname + @typename + ' = NULL'
if (datalength(@command1) > 7500) or (@rownumber = @batchingfactor)
begin
insert into @tempcmd (phase, cmdtext) values (10, @command1)
select @command1 = ''
end

if @colnumber = 1
begin
if @rownumber = 1
select @command3 = @command3 + '
select '
+ @argname + ' as ' + @colalias
else
select @command3 = @command3 + ' union all
select '
+ @argname + ' as ' + @colalias
end
else
begin
if (@colnumber%10) = 0
select @command3 = @command3 + ',
'

else
select @command3 = @command3 + ', '

select @command3 = @command3 + @argname + ' as ' + @colalias
end

if (datalength(@command3) > 7500) or (@rownumber = @batchingfactor)
begin
insert into @tempcmd (phase, cmdtext) values (200, @command3)
select @command3 = ''
end

set @col_in_bitable = 0
if @atpublisher = 1 and @partition_options=0 and @qualified_sync_view is not NULL and @qualified_before_image_table is not NULL
begin
-- only filtering or rowguid columns are present in the before image table

if @isrowguidcol=1
begin
set @col_in_bitable = 1
end
else
begin
-- does updating this column change membership in a partial replica?
if exists (select * from dbo.sysmergearticles
where objid = @id and sys.fn_MSisfilteredcolumn(subset_filterclause, @unquoted_colname, @id) = 1)
set @col_in_bitable = 1
else if exists (select * from dbo.sysmergesubsetfilters
where art_nickname = @tablenick and sys.fn_MSisfilteredcolumn(join_filterclause, @unquoted_colname, @id) = 1)
set @col_in_bitable = 1
else if exists (select * from dbo.sysmergesubsetfilters
where join_nickname = @tablenick and sys.fn_MSisfilteredcolumn(join_filterclause, @unquoted_colname, @id) = 1)
set @col_in_bitable = 1
end
end

-- insert the column in the virtual table that is used to insert into the bi table
-- add the row in the virtual table that is used to insert into the before image table
if @col_in_bitable = 1
begin
if @bitable_colnumber = 1
begin
if @rownumber = 1
select @cmdpiece = '
select '
+ @argname + ' as ' + @colalias
else
select @cmdpiece = ' union all
select '
+ @argname + ' as ' + @colalias
end
else
select @cmdpiece = ', ' + @argname + ' as ' + @colalias
select @bitable_colnumber = @bitable_colnumber + 1
insert into @tempcmd (phase, cmdtext) values (430, @cmdpiece)
end

if @rownumber = 1
begin
-- this is the actual column names to be specified in the insert
if @insertcolumnsstarted = 0
begin
select @insertcolumnsstarted = 1
-- this is the select list from the virual table to be specified in the
-- select statement from which we insert
select @cmdpiece = @colalias
insert into @tempcmd (phase, cmdtext) values (170, @cmdpiece)

-- this is the actual column name
select @cmdpiece = @colname
insert into @tempcmd (phase, cmdtext) values (120, @cmdpiece)
end
else
begin
-- this is the select list from the virual table to be specified in the
-- select statement from which we insert
select @cmdpiece = ',
'
+ @colalias
insert into @tempcmd (phase, cmdtext) values (170, @cmdpiece)

-- this is the actual column name
select @cmdpiece = ',
'
+ @colname
insert into @tempcmd (phase, cmdtext) values (120, @cmdpiece)
end

if @col_in_bitable = 1
begin
-- add the column in the column list for insert into the before image table
select @cmdpiece = ',
'
+ @colname
insert into @tempcmd (phase, cmdtext) values (390, @cmdpiece)

-- add the column in the select list for insert into the before image table
select @cmdpiece = ',
'
+ ' rows.' + @colalias
insert into @tempcmd (phase, cmdtext) values (410, @cmdpiece)
end

-- is this an identity column without 'not for replication' marking?
if (@isidentitycolumn = 1) and
@is_identitynotforreplication = 0
begin
-- turning identity insert on is phase 7
set @cmdpiece = '
set identity_insert '
+ @qualified_name + ' on'
insert into @tempcmd (phase, cmdtext) values (20, @cmdpiece)

-- turning identity insert on is phase 13
set @cmdpiece = '
set identity_insert '
+ @qualified_name + ' off'
insert into @tempcmd (phase, cmdtext) values (250, @cmdpiece)
end
end

Next_Column:
-- now set up to repeat the loop with the next column
select @colid = min (column_id) from sys.columns where object_id = @sync_objid and column_id > @colid

set @colname = NULL
if @colid is not null
select top 1 @colname = QUOTENAME(C.name),
@unquoted_colname = C.name,
@isidentitycolumn = C.is_identity,
@typename = type_name(C.user_type_id),
@base_typename = type_name(C.system_type_id),
@schname=case when S.name<>'sys' and S.name<>'dbo'
then QUOTENAME(S.name)+'.' else '' end,
@len = C.max_length,
@prec = C.precision,
@scale = C.scale
from sys.columns C
INNER JOIN sys.types T ON C.user_type_id = T.user_type_id
INNER JOIN sys.schemas S ON T.schema_id = S.schema_id
where C.object_id = @sync_objid and C.column_id = @colid

select @isidentitycolumn = is_identity,
@iscomputed=is_computed,
@xtype=user_type_id,
@isrowguidcol = is_rowguidcol,
@is_identitynotforreplication = convert(bit, isnull(columnproperty(@id, name, 'IsIdNotForRepl'), 0))
from sys.columns
where object_id = @id and QUOTENAME(name) = @colname

if @base_typename='nvarchar' or @base_typename='nchar' -- a unit of nchar takes 2 bytes
select @len = @len/2
set @colordinal = @colordinal + 1
set @colnumber = @colnumber + 1
end
select @rownumber = @rownumber + 1
end

if @maintainsmetadata = 1 and @partition_options = 0 and @atpublisher = 1
and exists (
select * from dbo.sysmergearticles
where nickname = @tablenick
and pubid in
(
select pubid from dbo.sysmergepublications where use_partition_groups <= 0
)
)
begin
-- now for each article for which this article is the parent see if inserting the rows that we
-- inserted in this proc causes some child rows to qualify. If so update the generation and partchange gen
-- for the parent row (row in this article) to 0.
declare @joinnick int
declare @qualified_jointable nvarchar(517)
declare @unqualified_jointable nvarchar(300)
declare @filter_clause nvarchar(2000)
declare @tablealias sysname
declare @join_tables_check_phase int

select @join_tables_check_phase = 1000
declare f_c CURSOR LOCAL FAST_FORWARD for select art_nickname, join_filterclause
from dbo.sysmergesubsetfilters where join_nickname = @tablenick and pubid = @pubid and (filter_type & 1) = 1
FOR READ ONLY
open f_c
fetch next from f_c into @joinnick, @filter_clause
while (@@fetch_status <> -1)
begin
exec @retcode= sys.sp_MStablenamefromnick @joinnick, @qualified_jointable out, NULL, @unqualified_jointable out
if @@error<>0 or @retcode<>0
begin
close f_c
deallocate f_c
goto Failure
end

select @tablealias = quotename(name) from sys.objects where object_id=@id

select @cmdpiece = '
update dbo.MSmerge_contents with (rowlock)
set generation = @gen_cur, partchangegen = @gen_cur
from ('

insert into @tempcmd (phase, cmdtext) values (@join_tables_check_phase, @cmdpiece)

-- list of rowguids will be in phase @join_tables_check_phase+10
select @phaseid = @join_tables_check_phase+10
insert into @tempcmd (phase, cmdtext) select phase, cmdtext from sys.fn_MSgetrowguidvirtualtable(@phaseid, @batchingfactor) order by step

select @cmdpiece = '
) as rows
inner join dbo.MSmerge_contents cont with (rowlock, READPAST)
on cont.rowguid = rows.rowguid
and cont.tablenick = '
+ @tablenickstr + '
where rows.rowguid is not NULL and
rows.rowguid in
(select rows.rowguid
from ('

insert into @tempcmd (phase, cmdtext) values (@join_tables_check_phase+20, @cmdpiece)

-- list of rowguids
select @phaseid = @join_tables_check_phase+30
insert into @tempcmd (phase, cmdtext) select phase, cmdtext from sys.fn_MSgetrowguidvirtualtable(@phaseid, @batchingfactor) order by step
select @cmdpiece = '
) as rows,
'
+ @qualified_name + ' ' + @tablealias + ' with (rowlock, READPAST) ,
'
+ @qualified_jointable + ' ' + @unqualified_jointable + ' with (rowlock)
where '
+ @tablealias + '.' + @rgcolname + ' = rows.rowguid
and rows.rowguid is not null
and '
+ @filter_clause + '
)
if @@error<>0
begin
set @errcode= 3
goto Failure
end'

insert into @tempcmd (phase, cmdtext) values (@join_tables_check_phase+40, @cmdpiece)

select @join_tables_check_phase = @join_tables_check_phase+50
fetch next from f_c into @joinnick, @filter_clause
end
close f_c
deallocate f_c
end

-- 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 cmdtext from @tempcmd order by phase, step

-- drop table @tempcmd

return(0)


Failure:
-- drop table @tempcmd
return(1)

No comments:

Post a Comment

Total Pageviews