Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSget_partitionid_eval_proc

  No additional text.


Syntax
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_ov
erride 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_l
ist + '', ' + 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 outp
ut, @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

 
Last revision 2008RTM
See also

  sp_MSdrop_rladmin (Procedure)
sp_MSsetup_partition_groups_table (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