Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSsetup_partition_groups_table

  No additional text.


Syntax
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

 
Last revision 2008RTM
See also

  sp_MSdrop_rladmin (Procedure)
sp_MSsetup_use_partition_groups (Procedure)
sp_vupgrade_mergeobjects (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash