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_MSget_partitionid_eval_proc(nvarchar @partition_id_eval_proc, uniqueidentifier @pubid
, smallint @publication_number
, nvarchar @column_list
, nvarchar @function_list
, nvarchar @partition_id_eval_clause
, smallint @use_partition_groups)
MetaData:
create procedure sys.sp_MSget_partitionid_eval_proc (
@partition_id_eval_proc sysname,
@pubid uniqueidentifier,
@publication_number smallint,
@column_list nvarchar(2000) = null,
@function_list nvarchar(2000) = null,
@partition_id_eval_clause nvarchar(2000) = null,
@use_partition_groups smallint = 0
) as
begin
declare @cmdtemp nvarchar(max),
@retcode int
-- Security check
exec @retcode= sys.sp_MSreplcheck_publish
if @@error <> 0 or @retcode <> 0 return (1)
create table #tmpevalproccmd (phase int NOT NULL, cmdtext nvarchar(max) collate database_default null)
select @cmdtemp = N'create procedure dbo.' + sys.fn_replreplacesinglequote(quotename(@partition_id_eval_proc)) collate database_default + N' @partition_id int OUTPUT, @maxgen_whenadded bigint OUTPUT, @host_name_override sysname = NULL, @suser_sname_override sysname = NULL as
begin
declare @retcode int
declare @function_values_overridden bit
declare @overridden_function_list nvarchar(2000)
declare @temp_overridden_function_list nvarchar(2001)
declare @overridden_eval_clause nvarchar(2000)
declare @command nvarchar(max)
declare @dbname sysname
declare @using_partition_groups bit
declare @is_partition_active bit
declare @app_lock_resource nvarchar(255)
if ({ fn ISPALUSER(N''' + convert(nvarchar(36),@pubid) + ''') } <> 1)
begin
RAISERROR (14126, 11, -1)
return (1)
end
select @dbname = quotename(db_name())
select @host_name_override = replace(@host_name_override, '''''''', '''''''''''')
select @suser_sname_override = replace(@suser_sname_override, '''''''', '''''''''''')
'
insert into #tmpevalproccmd (phase, cmdtext) values(1, @cmdtemp)
if @partition_id_eval_clause is null
select @cmdtemp = N'
select @partition_id = -1'
else
begin
select @cmdtemp = N'
select @partition_id = NULL
select @function_values_overridden = 0
select @overridden_function_list = N''' + sys.fn_replreplacesinglequote(@function_list) + N'''
select @overridden_eval_clause = N''' + sys.fn_replreplacesinglequote(@partition_id_eval_clause) + N'''
if @host_name_override is not null and @host_name_override != ''''
begin
select @temp_overridden_function_list = N'','' + @overridden_function_list
select @temp_overridden_function_list = replace(@temp_overridden_function_list collate SQL_Latin1_General_CP1_CI_AS, '',host_name()'' collate SQL_Latin1_General_CP1_CI_AS, '','''''' + @host_name_override + '''''''')
select @overridden_function_list = substring(@temp_overridden_function_list, 2, len(@temp_overridden_function_list)-1)
select @overridden_eval_clause = replace(@overridden_eval_clause collate SQL_Latin1_General_CP1_CI_AS, ''= host_name()'' collate SQL_Latin1_General_CP1_CI_AS, ''= '''''' + @host_name_override + '''''''')
select @function_values_overridden = 1
end
if @suser_sname_override is not null and @suser_sname_override != ''''
begin
select @temp_overridden_function_list = N'','' + @overridden_function_list
select @temp_overridden_function_list = replace(@temp_overridden_function_list collate SQL_Latin1_General_CP1_CI_AS, '',suser_sname()'' collate SQL_Latin1_General_CP1_CI_AS, '','''''' + @suser_sname_override + '''''''')
select @overridden_function_list = substring(@temp_overridden_function_list, 2, len(@temp_overridden_function_list)-1)
select @overridden_eval_clause = replace(@overridden_eval_clause collate SQL_Latin1_General_CP1_CI_AS, ''= suser_sname()'' collate SQL_Latin1_General_CP1_CI_AS, ''= '''''' + @suser_sname_override + '''''''')
select @function_values_overridden = 1
end
if @function_values_overridden = 0
select @partition_id = partition_id, @maxgen_whenadded = maxgen_whenadded, @using_partition_groups = using_partition_groups, @is_partition_active = is_partition_active
from dbo.MSmerge_partition_groups
where ' + sys.fn_replreplacesinglequote(@partition_id_eval_clause) + N'
and publication_number = ' + convert(nvarchar, @publication_number) + N'
else
begin
select @command = N''select @partition_id = partition_id, @maxgen_whenadded = maxgen_whenadded, @using_partition_groups = using_partition_groups, @is_partition_active = is_partition_active
from '' + @dbname + N''.[dbo].[MSmerge_partition_groups]
where '' + @overridden_eval_clause + N''
and publication_number = ' + convert(nvarchar, @publication_number) + N'''
exec sys.sp_executesql @command, N''@partition_id int output, @maxgen_whenadded int output, @using_partition_groups bit output, @is_partition_active bit output'', @partition_id=@partition_id output, @maxgen_whenadded=@maxgen_whenadded output, @using_partition_groups = @using_partition_groups output, @is_partition_active = @is_partition_active output
end
if @partition_id is null '
insert into #tmpevalproccmd (phase, cmdtext) values(2, @cmdtemp)
if @use_partition_groups > 0
select @cmdtemp = N' or @is_partition_active = 0 or @using_partition_groups = 0 '
else
select @cmdtemp = N' '
select @cmdtemp = @cmdtemp + N'
begin
begin tran
select @maxgen_whenadded = max(generation) from dbo.MSmerge_genhistory with (updlock) '
if @use_partition_groups > 0
begin
select @cmdtemp = @cmdtemp + N'
if @partition_id is null
begin
if @function_values_overridden = 0
insert into dbo.MSmerge_partition_groups( ' + sys.fn_replreplacesinglequote(@column_list) + N', publication_number, maxgen_whenadded, using_partition_groups, is_partition_active) values (' + sys.fn_replreplacesinglequote(@function_list) + N', ' + convert(nvarchar, @publication_number) + N', @maxgen_whenadded, 1, 0)
else
begin
select @command = N''insert into dbo.MSmerge_partition_groups( ' + sys.fn_replreplacesinglequote(@column_list) + N', publication_number, maxgen_whenadded, using_partition_groups, is_partition_active) values ('' + @overridden_function_list + '', ' + convert(nvarchar, @publication_number) + N', '' + convert(nvarchar, @maxgen_whenadded) + '', 1, 0)''
exec (@command)
end
end
else
begin
update dbo.MSmerge_partition_groups set maxgen_whenadded = @maxgen_whenadded, using_partition_groups = 1, is_partition_active = 0
where partition_id = @partition_id
and publication_number = ' + convert(nvarchar, @publication_number) + N'
end '
end -- if @use_partition_groups > 0
else
begin
select @cmdtemp = @cmdtemp + N'
if @function_values_overridden = 0
insert into dbo.MSmerge_partition_groups( ' + sys.fn_replreplacesinglequote(@column_list) + N', publication_number, maxgen_whenadded, using_partition_groups, is_partition_active) values (' + sys.fn_replreplacesinglequote(@function_list) + N', ' + convert(nvarchar, @publication_number) + N', @maxgen_whenadded, 0, 1)
else
begin
select @command = N''insert into dbo.MSmerge_partition_groups( ' + sys.fn_replreplacesinglequote(@column_list) + N', publication_number, maxgen_whenadded, using_partition_groups, is_partition_active) values ('' + @overridden_function_list + '', ' + convert(nvarchar, @publication_number) + N', '' + convert(nvarchar, @maxgen_whenadded) + '', 0, 1)''
exec (@command)
end '
end -- else block
select @cmdtemp = @cmdtemp + N'
commit tran
if @partition_id is null
select @partition_id = @@identity '
insert into #tmpevalproccmd (phase, cmdtext) values(3, @cmdtemp)
select @cmdtemp = ''
if @use_partition_groups > 0
begin
-- First we look for the entry for this clause that has the lowest partition_id and delete all that are greater than this lowest partition_id.
-- This is to protect against multiple subscribers with the same filter clause inserting new rows in MSmerge_partition_groups simultaneously.
-- We then work with this lowest partition_id
select @cmdtemp = @cmdtemp + N'
if @function_values_overridden = 0
begin
select top 1 @partition_id = partition_id, @maxgen_whenadded = maxgen_whenadded, @using_partition_groups = using_partition_groups, @is_partition_active = is_partition_active
from dbo.MSmerge_partition_groups
where ' + sys.fn_replreplacesinglequote(@partition_id_eval_clause) + N'
and publication_number = ' + convert(nvarchar, @publication_number) + N' order by partition_id asc
delete from dbo.MSmerge_partition_groups where ' + sys.fn_replreplacesinglequote(@partition_id_eval_clause) + N'
and partition_id > @partition_id and publication_number = ' + convert(nvarchar, @publication_number) + N'
end
else
begin
select @command = N''select top 1 @partition_id = partition_id, @maxgen_whenadded = maxgen_whenadded, @using_partition_groups = using_partition_groups, @is_partition_active = is_partition_active
from '' + @dbname + N''.[dbo].[MSmerge_partition_groups]
where '' + @overridden_eval_clause + N''
and publication_number = ' + convert(nvarchar, @publication_number) + N' order by partition_id asc''
exec sys.sp_executesql @command, N''@partition_id int output, @maxgen_whenadded int output, @using_partition_groups bit output, @is_partition_active bit output'', @partition_id=@partition_id output, @maxgen_whenadded=@maxgen_whenadded output, @using_partition_groups = @using_partition_groups output, @is_partition_active = @is_partition_active output
select @command = N''delete from '' + @dbname + N''.[dbo].[MSmerge_partition_groups] where '' + @overridden_eval_clause + N''
and partition_id > @partition_id and publication_number = ' + convert(nvarchar, @publication_number) + N'''
exec sys.sp_executesql @command, N''@partition_id int'', @partition_id = @partition_id
end'
-- Now take an exlusive applock for this lowest partition_id and do all the eval setup work (making sure that partition is still not active)
insert into #tmpevalproccmd (phase, cmdtext) values(4, @cmdtemp)
select @cmdtemp = '
select @app_lock_resource = N''MSmerge_evalpartid_' + convert(nvarchar, @publication_number) + N'_'' + convert(nvarchar, @partition_id)
exec @retcode = sp_getapplock @Resource = @app_lock_resource,
@LockMode = N''Exclusive'',
@LockOwner = N''Session'',
@LockTimeout = -1,
@DbPrincipal = N''MSmerge_PAL_role''
if @@error<>0 or @retcode<0
return 1
select @is_partition_active = is_partition_active from dbo.MSmerge_partition_groups where
partition_id = @partition_id and publication_number = ' + convert(nvarchar, @publication_number) + N'
if (@is_partition_active = 0)
begin
-- the following needs to be done only when using partition ids
-- create a dummy generation if using partition groups. This is needed because with recent makegeneration changes
-- we may have no open generation in genhistory. Now if we dont create a dummy generation that is higher than
-- @maxgen_whenadded our watermark may end up being maxgen_whenadded. Then even for the next merge since maxgen_whenadded
-- changes could potentially be enumerated we will use setupbelongs instead of partition groups.
exec @retcode = sys.sp_MScreatedummygeneration @pubid = ''' + convert(nvarchar(36), @pubid) + ''', @maxgen_whenadded = @maxgen_whenadded
if @retcode <> 0 or @@error <> 0
begin
select @retcode = 1
goto error
end'
insert into #tmpevalproccmd (phase, cmdtext) values(5, @cmdtemp)
select @cmdtemp = '
-- only do that following if there are some rows in contents
if exists (select * from dbo.MSmerge_contents)
begin
exec @retcode = sys.sp_MSevaluate_change_membership_for_pubid @pubid = ''' + convert(nvarchar(36), @pubid) + ''', @partition_id = @partition_id
if @retcode <> 0 or @@error <> 0
begin
select @retcode = 1
goto error
end
end
exec @retcode = sys.sp_MSmap_partitionid_to_generations @partition_id = @partition_id
if @retcode <> 0 or @@error <> 0
begin
select @retcode = 1
goto error
end
update dbo.MSmerge_partition_groups set is_partition_active = 1
where partition_id = @partition_id and publication_number = ' + convert(nvarchar, @publication_number) + N'
end
error:
exec sp_releaseapplock @Resource = @app_lock_resource, @LockOwner = N''Session'', @DbPrincipal = N''MSmerge_PAL_role''
if (@retcode = 1) -- If we got here because of an earlier error then bail
return 1'
end
select @cmdtemp = @cmdtemp + N'
end '
end
select @cmdtemp = @cmdtemp + N'
return 0
end
'
insert into #tmpevalproccmd (phase, cmdtext) values(6, @cmdtemp)
select cmdtext from #tmpevalproccmd order by phase
return 0
end
No comments:
Post a Comment