May 21, 2012

sp_MSmakebatchupdateproc (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_MSmakebatchupdateproc(nvarchar @tablename
, nvarchar @ownername
, nvarchar @procname
, uniqueidentifier @pubid
, uniqueidentifier @artid
, bit @generate_subscriber_proc
, nvarchar @destination_owner)

MetaData:

 create procedure sys.sp_MSmakebatchupdateproc   
(@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)
declare @isidentitycolumn bit
declare @len smallint
declare @prec int
declare @scale int
declare @tablenick int
declare @tablenickstr nvarchar(12)
declare @cmdpiece nvarchar(4000)
declare @article_name sysname
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 @updatecolumnsstarted 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 @rgcolname nvarchar(140)
declare @unquoted_colname sysname
declare @is_filtering_column bit
declare @filtering_column_check_start_phase int
declare @unfiltered_column_found bit
declare @filtering_column_number int
declare @maxschemaguid uniqueidentifier
declare @command1 nvarchar(4000)
declare @command2 nvarchar(4000)
declare @command3 nvarchar(4000)
declare @command4 nvarchar(4000)
declare @max_colv_size_in_bytes int
declare @qualified_sync_view nvarchar(517)
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 @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))

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 @updatecolumnsstarted = 0

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 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 @id = @sync_objid
select @colcount = count(*) from sys.columns where object_id=@id and is_computed=0 and user_type_id<>type_id('timestamp') and is_identity=0
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') and co.is_identity=0

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

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

select @unfiltered_column_found = 0
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),
@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
while (@colname is not null)
begin
if (@isidentitycolumn = 1 or @iscomputed=1 OR type_name(@xtype)='timestamp')
goto Next_Column1

if @isrowguidcol = 1
goto Next_Column1

set @is_filtering_column = 0
-- does updating this column change membership in a partial replica?

if exists (select * from dbo.sysmergearticles
where objid = @id and pubid = @pubid and sys.fn_MSisfilteredcolumn(subset_filterclause, @unquoted_colname, @id) = 1)
set @is_filtering_column = 1
else if exists (select * from dbo.sysmergesubsetfilters
where art_nickname = @tablenick and pubid = @pubid and sys.fn_MSisfilteredcolumn(join_filterclause, @unquoted_colname, @id) = 1)
set @is_filtering_column = 1
else if exists (select * from dbo.sysmergesubsetfilters
where join_nickname = @tablenick and pubid = @pubid and sys.fn_MSisfilteredcolumn(join_filterclause, @unquoted_colname, @id) = 1)
set @is_filtering_column = 1
if @is_filtering_column = 0
select @unfiltered_column_found = 1


Next_Column1:
-- 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),
@len = C.max_length,
@schname=case when S.name<>'sys' and S.name<>'dbo'
then QUOTENAME(S.name)+'.' else '' end,
@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
end

-- the following is true if the only columns in the table are all filtering columns
if @unfiltered_column_found = 0
begin
-- there is no point trying to create a update batch proc.
-- just create an empty proc
select 'create procedure dbo.' + QUOTENAME(@procname) + '
as
-- do nothing
select 1
go'

return 0
end


-- 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_updated 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_updated 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 @publication_number smallint
declare @filtering_column_updated bit
declare @rows_updated int
declare @cont_rows_updated int
declare @rows_in_syncview int

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_updated is NULL or @rows_tobe_updated <=0
return 0

select @filtering_column_updated = 0
select @rows_updated = 0
select @cont_rows_updated = 0 '


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

-- phase 22 - check the max schema guid for article and error out if they do not match
if @atpublisher = 1
begin
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 (22, @cmdpiece)
end

--
-- Do not allow out of partition updates at the publisher.
-- Check to see if the rows being updated are in the partition
-- before updating them.
--
if @atpublisher = 1 AND @partition_options>0 and @qualified_sync_view is not NULL
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 (23, @cmdpiece)
-- phase 23 will be rowguids
insert into @tempcmd (phase, cmdtext) select phase, cmdtext from sys.fn_MSgetrowguids(23, @batchingfactor) order by step
select @cmdpiece = '
)

if @rows_in_syncview <> @rows_tobe_updated
begin
raiserror(20733, 16, -1, '
'' + sys.fn_replreplacesinglequote(@tablename) + ''', ' + convert(char(3), @partition_options) + ')
return 3
end'


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


set @cmdpiece= '
begin tran
save tran batchupdateproc '

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

-- phase 50 onwards will be all the checks to see if a filtering column has been
-- updated. If a filtering column has been updated we will get out of the batched
-- update proc. Merge agent should revert back to singleton updates

-- phase 40000 is where the update statement starts
select @cmdpiece = '
update '
+ @qualified_name + ' with (rowlock)
set '

insert into @tempcmd (phase, cmdtext) values (40000, @cmdpiece)
-- phase 40100 will contain all the sets c1=@p1 etc.
select @cmdpiece = '
from ('

insert into @tempcmd (phase, cmdtext) values (40200, @cmdpiece)
-- phase 40300 will have the virtual table definition

if 1 = @maintainsmetadata
begin
select @cmdpiece = ') as rows
inner join '
+ @qualified_name + ' t with (rowlock) on rows.rowguid = t.' + @rgcolname + '
and rows.rowguid is not null
left outer join dbo.MSmerge_contents cont with (rowlock) on rows.rowguid = cont.rowguid
and cont.tablenick = '
+ @tablenickstr + '
where ((rows.metadata_type = 2 and cont.rowguid is not NULL and cont.lineage = rows.lineage_old) or
(rows.metadata_type = 3 and cont.rowguid is NULL))
and rows.rowguid is not null

select @rowcount = @@rowcount, @error = @@error'

insert into @tempcmd (phase, cmdtext) values (40400, @cmdpiece)
end
else
begin
select @cmdpiece = ') as rows
inner join '
+ @qualified_name + ' t with (rowlock) on rows.rowguid = t.' + @rgcolname + '
and rows.rowguid is not NULL
option (force order, loop join)
select @rowcount = @@rowcount, @error = @@error'

insert into @tempcmd (phase, cmdtext) values (40400, @cmdpiece)
end
select @cmdpiece = '
select @rows_updated = @rowcount
if (@rows_updated <> @rows_tobe_updated) or (@error <> 0)
begin
raiserror(20695, 16, -1, @rows_updated, @rows_tobe_updated, '
'' + sys.fn_replreplacesinglequote(@tablename) + ''')
set @errcode= 3
goto Failure
end'

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

-- insert or update the contents entry
if 1 = @maintainsmetadata
begin
select @cmdpiece = '
update dbo.MSmerge_contents with (rowlock)
set generation = rows.generation,
lineage = rows.lineage_new,
colv1 = rows.colv
from ('

insert into @tempcmd (phase, cmdtext) values (41000, @cmdpiece)
-- phase 41100 is the virtual table containing just the metadata new columns
select @cmdpiece = '
) as rows
inner join dbo.MSmerge_contents cont with (rowlock)
on cont.rowguid = rows.rowguid and cont.tablenick = '
+ @tablenickstr + '
and rows.rowguid is not NULL
and rows.lineage_new is not NULL
option (force order, loop join)
select @cont_rows_updated = @@rowcount, @error = @@error
if @error<>0
begin
set @errcode= 3
goto Failure
end'

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

select @cmdpiece = '
if @cont_rows_updated <> @rows_tobe_updated
begin'

insert into @tempcmd (phase, cmdtext) values (48000, @cmdpiece)
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 (48000, @cmdpiece)
-- phase 48100 will be virtual table with the list of rowguids and lineage
select @cmdpiece = '
) as rows
left outer join dbo.MSmerge_contents cont with (rowlock)
on cont.rowguid = rows.rowguid and cont.tablenick = '
+ @tablenickstr + '
and rows.rowguid is not NULL
and rows.lineage_new is not null
where cont.rowguid is NULL
and rows.rowguid is not null
and rows.lineage_new is not null

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

insert into @tempcmd (phase, cmdtext) values (48200, @cmdpiece)
end
-- insert into contents
select @cmdpiece = '
insert into dbo.MSmerge_contents with (rowlock)
(tablenick, rowguid, lineage, colv1, generation)
select '
+ @tablenickstr + ', rows.rowguid, rows.lineage_new, rows.colv, rows.generation
from ('

insert into @tempcmd (phase, cmdtext) values (48300, @cmdpiece)
-- phase 48400 will be virtual table with the list of rowguids and metadata
select @cmdpiece = '
) as rows
left outer join dbo.MSmerge_contents cont with (rowlock)
on cont.rowguid = rows.rowguid and cont.tablenick = '
+ @tablenickstr + '
and rows.rowguid is not NULL
and rows.lineage_new is not NULL
where cont.rowguid is NULL
and rows.rowguid is not NULL
and rows.lineage_new is not NULL

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

insert into @tempcmd (phase, cmdtext) values (48500, @cmdpiece)
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 (49000, @cmdpiece)
end

select @cmdpiece = '
commit tran
return 1

Failure:
rollback tran batchupdateproc
commit tran
return 0
end
'

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

-- this loop constructs the formal parameter declarations, the update statement with actual column names and
-- the union clause which does a union over all rows.
-- phase 10 is the declarations
select @command1 = ''
select @command2 = ''
select @command3 = ''
select @command4 = ''
set @colordinal = 1
set @rownumber = 1
select @filtering_column_check_start_phase = 0
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,
@setbm'
+ @rownumberstr + ' varbinary(125) = NULL,
@metadata_type'
+ @rownumberstr + ' tinyint = NULL,
@lineage_old'
+ @rownumberstr + ' varbinary(311) = NULL,
@generation'
+ @rownumberstr + ' bigint = NULL,
@lineage_new'
+ @rownumberstr + ' varbinary(311) = NULL,
@colv'
+ @rownumberstr + ' varbinary(' + convert(nvarchar(13), @max_colv_size_in_bytes) + ') = NULL'
-- insert into @tempcmd (phase, cmdtext) values (10, @cmdpiece)

-- phase 40300 is for update of user table where we need rowguid, setbm, metadata_type and lineage_old
-- to the virtual table in this phase we will add the user table columns
if @rownumber = 1
select @command2 = @command2 + '
select @rowguid'
+ @rownumberstr + ' as rowguid, @setbm' + @rownumberstr + ' as setbm, @metadata_type' + @rownumberstr + ' as metadata_type, @lineage_old' + @rownumberstr + ' as lineage_old'
else
select @command2 = @command2 + ' union all
select @rowguid'
+ @rownumberstr + ' as rowguid, @setbm' + @rownumberstr + ' as setbm, @metadata_type' + @rownumberstr + ' as metadata_type, @lineage_old' + @rownumberstr + ' as lineage_old'
-- insert into @tempcmd (phase, cmdtext) values (40300, @cmdpiece)

-- phase 41100 is the virtual table containing just the metadata new columns, used to update contents
if @rownumber = 1
select @command3 = @command3 + '
select @rowguid'
+ @rownumberstr + ' as rowguid, @generation' + @rownumberstr + ' as generation, @lineage_new' + @rownumberstr + ' as lineage_new, @colv' + @rownumberstr + ' as colv'
else
select @command3 = @command3 + ' union all
select @rowguid'
+ @rownumberstr + ' as rowguid, @generation' + @rownumberstr + ' as generation, @lineage_new' + @rownumberstr + ' as lineage_new, @colv' + @rownumberstr + ' as colv'
if (datalength(@command3) > 7500) or (@rownumber = @batchingfactor)
begin
insert into @tempcmd (phase, cmdtext) values (41100, @command3)

-- phase 48400 is for the virtual table that inserts into contents
insert into @tempcmd (phase, cmdtext) values (48400, @command3)

select @command3 = ''
end

-- this will be used to insert into current partition mappings
if @partition_options > 1 and @atpublisher = 1
begin
if @rownumber = 1
select @command4 = @command4 + '
select @rowguid'
+ @rownumberstr + ' as rowguid, @lineage_new' + @rownumberstr + ' as lineage_new'
else
select @command4 = @command4 + ' union all
select @rowguid'
+ @rownumberstr + ' as rowguid, @lineage_new' + @rownumberstr + ' as lineage_new'
if (datalength(@command4) > 7500) or (@rownumber = @batchingfactor)
begin
insert into @tempcmd (phase, cmdtext) values (48100, @command4)
select @command4 = ''
end
end
end
else
begin
select @command1 = @command1 + ',
@rowguid'
+ @rownumberstr + ' uniqueidentifier = NULL,
@setbm'
+ @rownumberstr + ' varbinary(125) = NULL'
-- insert into @tempcmd (phase, cmdtext) values (10, @cmdpiece)

if @rownumber = 1
select @command2 = @command2 + '
select @rowguid'
+ @rownumberstr + ' as rowguid, @setbm' + @rownumberstr + ' as setbm'
else
select @command2 = @command2 + ' union all
select @rowguid'
+ @rownumberstr + ' as rowguid, @setbm' + @rownumberstr + ' as setbm'
-- insert into @tempcmd (phase, cmdtext) values (40300, @cmdpiece)
end


-- now loop over columns
select @colnumber = 1
select @filtering_column_number = 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),
@len = C.max_length,
@schname=case when S.name<>'sys' and S.name<>'dbo'
then QUOTENAME(S.name)+'.' else '' end,
@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 (@isidentitycolumn = 1 or @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))
select @colalias = 'c' + @colidstr

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

if @isrowguidcol = 1
goto Next_Column

set @is_filtering_column = 0
-- does updating this column change membership in a partial replica?

if exists (select * from dbo.sysmergearticles
where objid = @id and pubid = @pubid and sys.fn_MSisfilteredcolumn(subset_filterclause, @unquoted_colname, @id) = 1)
set @is_filtering_column = 1
else if exists (select * from dbo.sysmergesubsetfilters
where art_nickname = @tablenick and pubid = @pubid and sys.fn_MSisfilteredcolumn(join_filterclause, @unquoted_colname, @id) = 1)
set @is_filtering_column = 1
else if exists (select * from dbo.sysmergesubsetfilters
where join_nickname = @tablenick and pubid = @pubid and sys.fn_MSisfilteredcolumn(join_filterclause, @unquoted_colname, @id) = 1)
set @is_filtering_column = 1
if @is_filtering_column = 1
begin
declare @start_phase int

select @start_phase = @filtering_column_check_start_phase + @filtering_column_number*50
-- we need to check if any partitioning/filtering column is being updated in the beginning of the
-- batched update proc. Add the code for doing that here.
-- if so abort the batched update proc and the merge agent should revert to singleton updates.
if @rownumber = 1
begin
select @cmdpiece = '
select @filtering_column_updated = 0

-- case 1 of setting the filtering column where we are setting it to NULL and the table has a non NULL value for this column
select @filtering_column_updated = 1 from
('

insert into @tempcmd (phase, cmdtext) values (@start_phase, @cmdpiece)
-- phase 60 is the rows virtual table contains rowguid, filtering column and setbm
select @cmdpiece = '
select @rowguid'
+ @rownumberstr + ' as rowguid, ' + @argname + ' as ' + @colalias + ', @setbm' + @rownumberstr + ' as setbm'
insert into @tempcmd (phase, cmdtext) values (@start_phase+10, @cmdpiece)

select @cmdpiece = '
) as rows
inner join '
+ @qualified_name + ' t with (rowlock)
on t.'
+ @rgcolname + ' = rows.rowguid and rows.rowguid is not NULL
where rows.'
+ @colalias + ' is NULL and sys.fn_IsBitSetInBitmask(rows.setbm, ' + @colordstr + ') <> 0 and t.' + @colname + ' is not NULL

if @filtering_column_updated = 1
begin
raiserror(20694, 16, -1, '
'' + sys.fn_replreplacesinglequote(@tablename) + ''', ''' + sys.fn_replreplacesinglequote(@colname) + ''')
set @errcode=4
goto Failure
end

-- case 2 of setting the filtering column where we are setting it to a not null value and the value is not equal to the value in the table
select @filtering_column_updated = 1 from
('

insert into @tempcmd (phase, cmdtext) values (@start_phase+20, @cmdpiece)
-- following phase is the rows virtual table contains rowguid, filtering column and setbm
select @cmdpiece = '
select @rowguid'
+ @rownumberstr + ' as rowguid, ' + @argname + ' as ' + @colalias
insert into @tempcmd (phase, cmdtext) values (@start_phase+30, @cmdpiece)

select @cmdpiece = '
) as rows
inner join '
+ @qualified_name + ' t with (rowlock)
on t.'
+ @rgcolname + ' = rows.rowguid and rows.rowguid is not NULL
where rows.'
+ @colalias + ' is not NULL and (t.' + @colname + ' is NULL or t.' + @colname + ' <> rows.' + @colalias + ' )

if @filtering_column_updated = 1
begin
raiserror(20694, 16, -1, '
'' + sys.fn_replreplacesinglequote(@tablename) + ''', ''' + sys.fn_replreplacesinglequote(@colname) + ''')
set @errcode=4
goto Failure
end'

insert into @tempcmd (phase, cmdtext) values (@start_phase+40, @cmdpiece)
end
else
begin
select @cmdpiece = ' union all
select @rowguid'
+ @rownumberstr + ' as rowguid, ' + @argname + ' as ' + @colalias + ', @setbm' + @rownumberstr + ' as setbm'
insert into @tempcmd (phase, cmdtext) values (@start_phase+10, @cmdpiece)
select @cmdpiece = ' union all
select @rowguid'
+ @rownumberstr + ' as rowguid, ' + @argname + ' as ' + @colalias
insert into @tempcmd (phase, cmdtext) values (@start_phase+30, @cmdpiece)
end
select @filtering_column_number = @filtering_column_number + 1
goto Next_Column
end

-- only non filtering columns will be part of the update statement
-- peformance optimization to concatenate.
if (@colid%10) = 0
select @command2 = @command2 + ',
'

else
select @command2 = @command2 + ', '

select @command2 = @command2 + @argname + ' as ' + @colalias
-- phase 40300 should also contain the list of user columns and metadata columns.
if (datalength(@command2)>7500) or (@rownumber = @batchingfactor)
begin
insert into @tempcmd (phase, cmdtext) values (40300, @command2)
select @command2 = ''
end
if @rownumber = 1
begin
-- this is the actual update statement with set c1=rows.c1
if @updatecolumnsstarted = 0
begin
select @updatecolumnsstarted = 1
select @cmdpiece = ''
end
else
select @cmdpiece = ','
-- this is the select list from the virual table to be specified in the
-- select statement from which we insert
select @cmdpiece = @cmdpiece + '
'
+ @colname + ' = case when rows.' + @colalias + ' is NULL then (case when sys.fn_IsBitSetInBitmask(rows.setbm, ' + @colordstr + ') <> 0 then rows.' + @colalias + ' else t.' + @colname + ' end) else rows.' + @colalias + ' end '
insert into @tempcmd (phase, cmdtext) values (40100, @cmdpiece)
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 is_computed=0 and user_type_id<>type_id('timestamp') 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),
@len = C.max_length,
@schname=case when S.name<>'sys' and S.name<>'dbo'
then QUOTENAME(S.name)+'.' else '' end,
@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

-- 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