Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSgenerate_articlechangemembershipevaluation_proc

  No additional text.


Syntax

-- Requires Certificate signature for catalog access
create procedure sys.sp_MSgenerate_articlechangemembershipevaluation_proc (@pubid uniqueidentifier, @article_nickname int)
as
begin

    declare @partition_view_name nvarchar(270),
            @retcode int,
            @artid uniqueidentifier,
            @guidstr nvarchar(40),
            @pubidstr nvarchar(40),
            @membership_eval_proc_name nvarchar(130),
            @objid int,
            @rgcol nvarchar(270),
            @dbname nvarchar(130),
            @cmd nvarchar(max),
            @quoted_membership_eval_proc_name nvarchar(258)

    select @artid = artid, @objid = objid from dbo.sysmergearticles
    where nickname = @article_nickname and pubid = @pubid

    select @partition_view_name = quotename(object_name(partition_view_id)), @membership_eval_proc_name = membership_eval_proc_name
    from dbo.sysmergepartitioninfo
    where artid = @artid and pubid = @pubid

    if @partition_view_name is null
        return 0 -- too early. sp_MSpublicationview has not been called yet.

    if @membership_eval_proc_name is not null
    begin
        if object_id (@membership_eval_proc_name) is not null
        begin
            set @quoted_membership_eval_proc_name= quotename(@membership_eval_proc_name)
            exec ('drop procedure dbo.' + @quoted_membership_eval_proc_name)
        end

        update dbo.sysmergepartitioninfo set membership_eval_proc_name = NULL
        where artid = @artid and pubid = @pubid
    end

    select @rgcol = quotename(name) from sys.columns where object_id = @objid and is_rowguidcol = 1

    select @dbname = db_name()

    exec @retcode = dbo.sp_MSguidtostr @artid, @guidstr out
    if @@ERROR <>0 OR @retcode <>0 return (1)

    exec @retcode = dbo.sp_MSguidtostr @pubid, @pubidstr out
    if @@ERROR <>0 OR @retcode <>0 return (1)

    declare @publication_number smallint
    select top 1 @publication_number = publication_number from dbo.sysmergepublications where pubid = @pubid

    select @membership_eval_proc_name = 'MSmerge_evalmembership_sp_' + sys.fn_MSmerge_getartprocsuffix(@artid, @pubid)
    if object_id (@membership_eval_proc_name) is not null
    begin
        set @quoted_membership_eval_proc_name= quotename(@membership_eval_proc_name)
        exec ('drop procedure dbo.' + @quoted_membership_eval_proc_name)
        if @@error<>0 return 1
    end

    set @quoted_membership_eval_proc_name= quotename(@membership_eval_proc_name)

    select @cmd = N'create procedure dbo.' + @quoted_membership_eval_proc_name + ' @partition_id int = NULL, @rowguid uniqueidentifier = NULL, @marker uniqueidentifier = NULL as
    begin
        if ({ fn ISPALUSER(''' + convert(nvarchar(36),@pubid) + ''') } <> 1)
        begin
            RAISERROR (14126, 11, -1)
            return 1
        end
        if @partition_id is not null
        begin
            insert into dbo.MSmerge_current_partition_mappings (publication_number, tablenick, rowguid, partition_id)
            select distinct ' + convert(nvarchar, @publication_number) + ', mc.tablenick, mc.rowguid, v.partition_id
            from dbo.MSmerge_contents mc
            JOIN ' + @partition_view_name + ' v
            ON mc.tablenick = ' + convert(nvarchar(11), @article_nickname) + '
            and mc.rowguid = v.' + @rgcol + '
            and v.partition_id = @partition_id
            where not exists (select * from dbo.MSmerge_current_partition_mappings mcpm
                                where mcpm.publication_number = ' + convert(nvarchar, @publication_number) + '
                                and mcpm.tablenick = mc.tablenick
                                and mcpm.rowguid = mc.rowguid
                                and mcpm.partition_id = v.partition_id)
        end
        else if @marker is not null
        begin
			insert into dbo.MSmerge_current_partition_mappings (publication_number, tablenick, rowguid, partition_id)
                select distinct ' + convert(nvarchar, @publication_number) + ', mc.tablenick, mc.rowguid, v.partition_id
                from dbo.MSmerge_contents mc
                JOIN ' + @partition_view_name + ' v
                ON mc.tablenick = ' + convert(nvarchar(11), @article_nickname) + '
                and mc.rowguid = v.' + @rgcol + '
                and mc.marker = @marker
                where not exists (select * from dbo.MSmerge_current_partition_mappings mcpm
                                where mcpm.publication_number = ' + convert(nvarchar, @publication_number) + '
                                and mcpm.tablenick = mc.tablenick
                                and mcpm.rowguid = mc.rowguid
                                and mcpm.partition_id = v.partition_id)
        end
        else
        begin
            if @rowguid is null
                insert into dbo.MSmerge_current_partition_mappings (publication_number, tablenick, rowguid, partition_id)
                select distinct ' + convert(nvarchar, @publication_number) + ', mc.tablenick, mc.rowguid, v.partition_id
                from dbo.MSmerge_contents mc
                JOIN ' + @partition_view_name + ' v
                ON mc.tablenick = ' + convert(nvarchar(11), @article_nickname) + '
                and mc.rowguid = v.' + @rgcol + '
                where not exists (select * from dbo.MSmerge_current_partition_mappings mcpm
                                where mcpm.publication_number = ' + convert(nvarchar, @publication_number) + '
                                and mcpm.tablenick = mc.tablenick
                                and mcpm.rowguid = mc.rowguid
                                and mcpm.partition_id = v.partition_id)
            else
                insert into dbo.MSmerge_current_partition_mappings (publication_number, tablenick, rowguid, partition_id)
                select distinct ' + convert(nvarchar, @publication_number) + ', ' + convert(nvarchar(11), @article_nickname) + ', @rowguid, v.partition_id
                from ' + @partition_view_name + ' v
                where v.' + @rgcol + ' = @rowguid
                and not exists (select * from dbo.MSmerge_current_partition_mappings mcpm
                                where mcpm.publication_number = ' + convert(nvarchar, @publication_number) + '
                                and mcpm.tablenick = ' + convert(nvarchar(11), @article_nickname) + '
                                and mcpm.rowguid = @rowguid
                                and mcpm.partition_id = v.partition_id)
        end
    end'

    exec @retcode = sys.xp_execresultset @cmd, @dbname
    if @@ERROR<>0 OR @retcode <>0 return (1)

    exec @retcode = dbo.sp_MS_marksystemobject    @membership_eval_proc_name
    if @@ERROR<>0 OR @retcode <>0 return (1)

    exec ('grant exec on dbo.' + @quoted_membership_eval_proc_name + ' to public')
    if @@ERROR<>0 return (1)

    update dbo.sysmergepartitioninfo set membership_eval_proc_name = @membership_eval_proc_name
    where artid = @artid and pubid = @pubid

end

 
Last revision 2008RTM
See also

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