create procedure sys.sp_MSsetup_partition_groups_table (@pubid uniqueidentifier, @upgrade bit = 0) as
begin
set nocount on
-- Find the functions used in this publication's filters.
declare @prev_index int,
@cur_index int,
@datalength int,
@function nvarchar(500),
@dynamic_filters_function_list nvarchar(500),
@retcode int,
@partition_id_eval_clause nvarchar(2000),
@column_list nvarchar(2000),
@function_list nvarchar(2000),
@use_partition_groups smallint,
@dynamic_filters bit,
@publication sysname,
@quoted_function nvarchar(517),
@function_schema sysname,
@function_name sysname
declare @publication_number smallint
select top 1 @publication_number = publication_number, @publication = name from dbo.sysmergepublications where pubid = @pubid
select @partition_id_eval_clause = NULL
select @dynamic_filters = 0
select @dynamic_filters_function_list = dynamic_filters_function_list,
@use_partition_groups = use_partition_groups,
@dynamic_filters = dynamic_filters
from dbo.sysmergepublications
where pubid = @pubid
and dynamic_filters = 1
if (@dynamic_filters = 1 and (@dynamic_filters_function_list is null or @dynamic_filters_function_list = ' '))
exec sp_MSget_dynamic_filters_function_list @publication, @dynamic_filters_function_list OUTPUT
if @dynamic_filters_function_list is not null and @dynamic_filters_function_list <> ' '
begin
select @datalength = datalength(@dynamic_filters_function_list)/2,
@prev_index = 1,
@cur_index = charindex (';', @dynamic_filters_function_list, @prev_index)
while (@cur_index <> 0)
begin
select @function = substring(@dynamic_filters_function_list, @prev_index, @cur_index - @prev_index)
select @function = REPLACE(REPLACE(REPLACE(REPLACE(@function collate SQL_Latin1_General_CP1_CS_AS, char(0x20),''), char(0x09),''), char(0x0D),''), char(0x0A),'')
if (ltrim(rtrim(@function)) <> ' ')
begin
select @function = REPLACE(REPLACE(@function collate SQL_Latin1_General_CP1_CS_AS, '(', ''), ')', '')
-- maximum individual function size of 125 characters
-- ( max sysname - len('_FN') )
if datalength(@function)/2 > 125
return(1)
-- whitelist alphabetical characters and underscores as built-ins (i.e. SUSER_SNAME and HOST_NAME) cannot be quoted
if patindex('%[A-Za-z0-9_]%', @function collate Latin1_General_BIN) > 0
begin
select @function_name = parsename(@function, 1)
select @function_schema = parsename(@function, 2)
select @quoted_function = N''
if @function_schema is not null
select @quoted_function = quotename(@function_schema) + N'.'
if @function_name is not null
select @quoted_function = @quoted_function + quotename(@function_name)
else
select @quoted_function = quotename(@function)
end
else
begin
-- function only contains A-Z and _ no need for quoting
select @quoted_function = @function
end
if @partition_id_eval_clause is NULL
begin
select @partition_id_eval_clause = quotename(@function + '_FN') + ' = ' + @quoted_function + '() '
select @column_list = quotename(@function + '_FN')
select @function_list = @quoted_function + '()'
end
else
begin
select @partition_id_eval_clause = @partition_id_eval_clause + 'AND ' + quotename(@function + '_FN') + ' = ' + @quoted_function + '() '
select @column_list = @column_list + ',' + quotename(@function + '_FN')
select @function_list = @function_list + ',' + @quoted_function + '()'
end
-- only add the partition column if it is not upgrade (called by sp_vupgrade_merge_objects)
if (@upgrade=0)
exec @retcode = sp_MSadd_merge_partition_column @function
end
select @prev_index = @cur_index+1
select @cur_index = 0
select @cur_index = charindex (';', @dynamic_filters_function_list, @prev_index)
end
-- still need to get the last one
if (@datalength > @prev_index)
begin
select @function = substring(@dynamic_filters_function_list, @prev_index, @datalength - @prev_index + 1)
select @function = REPLACE(REPLACE(REPLACE(REPLACE(@function collate SQL_Latin1_General_CP1_CS_AS, char(0x20),''), char(0x09),''), char(0x0D),''), char(0x0A),'')
if (ltrim(rtrim(@function)) <> ' ')
begin
select @function = REPLACE(REPLACE(@function collate SQL_Latin1_General_CP1_CS_AS, '(', ''), ')', '')
-- maximum individual function size of 125 characters
-- ( max sysname - len('_FN') )
if datalength(@function)/2 > 125
return(1)
-- whitelist alphabetical characters and underscores as built-ins (i.e. SUSER_SNAME and HOST_NAME) cannot be quoted
if patindex('%[A-Za-z0-9_]%', @function collate Latin1_General_BIN) > 0
begin
select @function_name = parsename(@function, 1)
select @function_schema = parsename(@function, 2)
select @quoted_function = N''
if @function_schema is not null
select @quoted_function = quotename(@function_schema) + N'.'
if @function_name is not null
select @quoted_function = @quoted_function + quotename(@function_name)
else
select @quoted_function = quotename(@function)
end
else
begin
-- function only contains A-Z and _ no need for quoting
select @quoted_function = @function
end
if @partition_id_eval_clause is NULL
begin
select @partition_id_eval_clause = quotename(@function + '_FN') + ' = ' + @quoted_function + '() '
select @column_list = quotename(@function + '_FN')
select @function_list = @quoted_function + '()'
end
else
begin
select @partition_id_eval_clause = @partition_id_eval_clause + 'AND ' + quotename(@function + '_FN') + ' = ' + @quoted_function + '() '
select @column_list = @column_list + ',' + quotename(@function + '_FN')
select @function_list = @function_list + ',' + @quoted_function + '()'
end
-- only add the partition column if it is not upgrade (called by sp_vupgrade_merge_objects)
if(@upgrade=0)
exec @retcode = sp_MSadd_merge_partition_column @function
end
end
end
declare @dbname sysname
declare @cmdtemp nvarchar(max)
declare @partition_id_eval_proc nvarchar(258)
declare @pubidstr sysname
select @dbname = db_name()
select @partition_id_eval_proc = quotename(partition_id_eval_proc) from dbo.sysmergepublications
where pubid = @pubid
if @partition_id_eval_proc is not null
begin
if object_id (@partition_id_eval_proc) is not null
exec ('drop procedure dbo.' + @partition_id_eval_proc)
update dbo.sysmergepublications set partition_id_eval_proc = NULL where pubid = @pubid
end
exec @retcode = dbo.sp_MSguidtostr @pubid, @pubidstr out
if @@ERROR <>0 OR @retcode <>0 return (1)
select @partition_id_eval_proc = 'MSmerge_evalpartid_sp_' + substring(@pubidstr, 1, 16)
if object_id(@partition_id_eval_proc) is not NULL
begin
exec ('drop procedure dbo.' + @partition_id_eval_proc)
if @@error<>0 return 1
end
select @cmdtemp = 'exec sys.sp_MSget_partitionid_eval_proc @partition_id_eval_proc = ' + @partition_id_eval_proc +
', @pubid = ''' + convert(nvarchar(36), @pubid) + ''', @publication_number = ' + convert(nvarchar, @publication_number)
if @column_list is not null and @column_list <> ''
select @cmdtemp = @cmdtemp + ', @column_list = ''' + replace(@column_list, '''', '''''') + ''''
if @function_list is not null and @function_list <> ''
select @cmdtemp = @cmdtemp + ', @function_list = ''' + replace(@function_list, '''', '''''') + ''''
if @partition_id_eval_clause is not null and @partition_id_eval_clause <> ''
select @cmdtemp = @cmdtemp + ', @partition_id_eval_clause = ''' + replace(@partition_id_eval_clause, '''', '''''') + ''''
if @use_partition_groups = 1
select @cmdtemp = @cmdtemp + ', @use_partition_groups = ' + convert(nvarchar, @use_partition_groups)
exec @retcode = sys.xp_execresultset @cmdtemp, @dbname
if @@ERROR<>0 OR @retcode <>0 return (1)
exec @retcode = dbo.sp_MS_marksystemobject @partition_id_eval_proc
if @@ERROR<>0 OR @retcode <>0 return (1)
exec ('grant exec on dbo.' + @partition_id_eval_proc + ' to public')
if @@ERROR<>0 return (1)
update dbo.sysmergepublications set partition_id_eval_proc = @partition_id_eval_proc where pubid = @pubid
return 0
end
