Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSmakeorcheck_joinfilter_using_dri

  No additional text.


Syntax
create procedure sys.sp_MSmakeorcheck_joinfilter_using_dri
    @publication    sysname,
    @article        sysname,
    @base_objid        int,
    @join_objid        int,
    @join_unique    int,
    @check_filter    bit=0,
    @join_filterclause nvarchar(1000)=NULL
    AS

    /* Declare additional variables */
    declare    @table_name nvarchar(140)
    declare    @join_table nvarchar(140)
    declare    @join_article sysname
    declare    @filt_name    sysname
    declare    @basecol    sysname
    declare    @joincol    sysname
    declare    @basecolid    int
    declare    @joincolid    int
    declare    @keycnt        int
    declare    @base_columns    varbinary(128)
    declare    @join_columns    varbinary(128)
    declare    @keyindex    int
    declare    @filtclause nvarchar(3000)
    declare    @filtpiece    nvarchar(500)
    declare    @qual_jointable nvarchar(270)
    declare    @retcode    smallint
    declare    @pubid        uniqueidentifier
    declare    @first_piece bit

    declare    @dri_rowcount int
    declare    @constid int
    declare    @base_nick int
    declare    @join_nick int

    if @check_filter = 1 and (@join_filterclause is null or @join_filterclause = N' ')
    begin
        raiserror (14043, 11, -1, '@join_filterclause', 'sp_MSmakeorcheck_joinfilter_using_dri')
        return (1)
    end

    select @pubid = pubid from dbo.sysmergepublications where name = @publication and UPPER(publisher)=UPPER(publishingservername()) and publisher_db=db_name()

    if @check_filter = 0
    begin
        -- making the filters, so consider only columns that are in vertical partition.
        select @base_columns=0x00 -- so that no base column name will be returned if following query does not find a match
        select @join_columns=0x00 -- same as above
        select @base_columns=columns from dbo.sysmergearticles where pubid=@pubid and objid=@base_objid
        select @join_columns=columns from dbo.sysmergearticles where pubid=@pubid and objid=@join_objid
    end
    else
    begin
        -- checking the filters, so check all columns. if dri happens to be on a column that has been excluded,
        -- then return error (can't have logical record relationship for that case).
        select @base_columns=NULL
        select @join_columns=NULL
    end

    /* Are we adding join filter on referencing table (@join_unique = 1) or on unique key table ? */
    if @join_unique = 1
        select @constid = object_id,
               @keycnt = (select count(*) from sys.foreign_key_columns k where k.constraint_object_id = f.object_id),
               @filt_name = object_name(object_id)
            from sys.foreign_keys f where f.parent_object_id = @base_objid and f.referenced_object_id = @join_objid
    else
        select @constid = object_id,
               @keycnt = (select count(*) from sys.foreign_key_columns k where k.constraint_object_id = f.object_id),
               @filt_name = object_name(object_id)
            from sys.foreign_keys f where f.referenced_object_id = @base_objid and f.parent_object_id = @join_objid

    select @dri_rowcount = @@rowcount

    /* Set up object names - we use them as correlation values */
    set @table_name = QUOTENAME(object_name(@base_objid))
    set @join_table = QUOTENAME(object_name(@join_objid))

    select @join_article = name, @join_nick = nickname from dbo.sysmergearticles
    where objid = @join_objid and pubid=@pubid

    select @base_nick = nickname from dbo.sysmergearticles where pubid = @pubid and objid = @base_objid

    if @check_filter = 1
    begin
        if @dri_rowcount = 0
        begin
            raiserror(21570, 16, -1, @table_name, @join_table)
            return 1
        end

        if ObjectProperty(@constid, 'CnstIsDisabled') = 1
        begin
            raiserror(21572, 16, -1, @filt_name, @table_name)
            return 1
        end

        if ObjectProperty(@constid, 'CnstIsNotRepl') = 1
        begin
            --raiserror(21573, 16, -1, @filt_name, @table_name)
            -- for better usability of feature, reset NFR instead of raising error
            exec @retcode = sys.sp_MSsetreset_NFR_FK @fkid = @constid, @set=0
            if @@error <> 0 or @retcode <> 0
                return 1
        end

        if exists    (
                        select * from sys.columns sc
                        where sc.object_id = @base_objid and --(sys.fn_IsTypeBlob(system_type_id,max_length)= 1)
                        (sc.system_type_id in (type_id('image'), type_id('text'), type_id('ntext'), type_id('xml'))
                              or sc.max_length = -1)
                        and
                        (exists (select * from dbo.sysmergearticles where nickname = @base_nick
                                and sys.fn_MSisfilteredcolumn(subset_filterclause, sc.name, @base_objid) = 1)
                            or exists (select * from dbo.sysmergesubsetfilters where (art_nickname = @base_nick or join_nickname = @base_nick)
                                and sys.fn_MSisfilteredcolumn(join_filterclause, sc.name, @base_objid) = 1 )
                        )
                    )
        or exists   (
                        select * from sys.columns sc
                        where sc.object_id = @join_objid and --(sys.fn_IsTypeBlob(system_type_id,max_length)= 1)
                        (sc.system_type_id in (type_id('image'), type_id('text'), type_id('ntext'), type_id('xml'))
                              or sc.max_length = -1)
                        and
                        (    exists (select * from dbo.sysmergearticles where nickname = @join_nick
                                and sys.fn_MSisfilteredcolumn(subset_filterclause, sc.name, @join_objid) = 1 )
                            or exists (select * from dbo.sysmergesubsetfilters where (art_nickname = @join_nick or join_nickname = @join_nick)
                                and sys.fn_MSisfilteredcolumn(join_filterclause, sc.name, @join_objid) = 1 )
                        )
                    )
        begin
            raiserror(22519, 16, -1, @table_name, @join_table)
            return 1
        end

    end

    -- set @qual_jointable = @join_owner + '.' + @join_table

    -- Loop over keys, building up our join filter clause
    set @keyindex = 1
    set @first_piece = 0
    while @keyindex <= @keycnt
    begin
        /* Get the column names */

        /* also pass in vertical partitioning binary to excluded columns that are not in the current partition */
        /*
        exec sys.sp_MSindexcolfrombin @base_objid, @keyindex, @basekeys, @basecol output, @base_columns
        if @@ERROR<>0 return (1)
        exec sys.sp_MSindexcolfrombin @join_objid, @keyindex, @joinkeys, @joincol output, @join_columns
        if @@ERROR<>0 return (1)
        */
        if @join_unique = 1
            select @basecolid = parent_column_id, @joincolid = referenced_column_id
                from sys.foreign_keys f, sys.foreign_key_columns k
                where f.parent_object_id = @base_objid and
                      f.referenced_object_id = @join_objid and
                      k.constraint_object_id = f.object_id and
                      k.constraint_column_id = @keyindex
        else
            select @basecolid = referenced_column_id, @joincolid = parent_column_id
                from sys.foreign_keys f, sys.foreign_key_columns k
                where f.parent_object_id = @join_objid and
                      f.referenced_object_id = @base_objid and
                      k.constraint_object_id = f.object_id and
                      k.constraint_column_id = @keyindex

        if @base_columns is not NULL
        begin
            exec @retcode = sys.sp_MStestbit @bm=@base_columns, @coltotest=@basecolid
            if @retcode=0
                select @basecol = NULL
            else
                select @basecol = name from sys.columns where object_id = @base_objid and column_id = @basecolid
        end
        if @join_columns is not NULL
        begin
            exec @retcode = sys.sp_MStestbit @bm=@join_columns, @coltotest=@joincolid
            if @retcode=0
                select @joincol = NULL
            else
                select @joincol = name from sys.columns where object_id = @join_objid and column_id = @joincolid
        end

        if @basecol is not NULL and @joincol is not NULL
        begin
            if @check_filter = 0
            begin
                select @basecol = quotename(@basecol), @joincol = quotename(@joincol)
                -- Make filter
                /* Make the piece of predicate pertaining to this key column */
                set @filtpiece = @table_name + '.' + @basecol + ' = ' + @join_table + '.' + @joincol

                /* If first time through, initialize clause, else add to it */
                if @first_piece=0
                begin
                    set @first_piece=1
                    set @filtclause = @filtpiece
                end
                else
                    set @filtclause = @filtclause + ' and ' + @filtpiece
            end
            else
            begin
                -- Check filter. Filter should contain the

                if sys.fn_MSisfilteredcolumn(@join_filterclause, @basecol, @base_objid) = 0
                begin
                    raiserror(21537, 16, -1, @basecol, @table_name, @join_table)
                    return 1
                end

                if sys.fn_MSisfilteredcolumn(@join_filterclause, @joincol, @join_objid) = 0
                begin
                    raiserror(21537, 16, -1, @joincol, @join_table, @table_name)
                    return 1
                end

                if exists (select * from sys.columns where object_id = object_id(@table_name)
                            and name = @basecol and is_nullable = 1)
                begin
                    raiserror(21576, 16, -1, @table_name, @join_table, @basecol, @table_name)
                    return 1
                end

                if exists (select * from sys.columns where object_id = object_id(@join_table)
                            and name = @joincol and is_nullable = 1)
                begin
                    raiserror(21576, 16, -1, @table_name, @join_table, @joincol, @join_table)
                    return 1
                end

            end
        end
        /* move on to the next key */
        set @keyindex = @keyindex + 1
    end

    /* no filter generated due to vertical partitioning */
    if @first_piece>0 and @check_filter = 0
    begin
        /* Add the join filter */

        exec @retcode = sys.sp_addmergefilter @publication, @article, @filt_name, @join_article, @filtclause, @join_unique
        if @@ERROR<>0 or @retcode<>0 return (1)

        -- If the parent is well-partitioned, and the join_unique_key is 1, and there is only one
        -- join filter on this base article, then the base article can be well-partitioned as well.
        if @join_unique = 1
            and exists (select * from dbo.sysmergepartitioninfoview
                where pubid = @pubid
                and objid = @join_objid
                and partition_options = 3)
            and exists (select * from dbo.sysmergesubsetfilters
                            where pubid = @pubid
                            and art_nickname = @base_nick
                  group by art_nickname having count(*) = 1)
        begin
            exec @retcode = sys.sp_changemergearticle @publication, @article, 'partition_options', '3'
            if @@ERROR<>0 or @retcode<>0 return (1)
        end
        else if @join_unique = 1
            and exists (select * from dbo.sysmergepartitioninfoview
                where pubid = @pubid
                and objid = @join_objid
                and partition_options = 2)
            and exists (select * from dbo.sysmergesubsetfilters
                            where pubid = @pubid
                            and art_nickname = @base_nick
                            group by art_nickname having count(*) = 1)
        begin
            exec @retcode = sys.sp_changemergearticle @publication, @article, 'partition_options', '2'
            if @@ERROR<>0 or @retcode<>0 return (1)
        end
    end

    return (0)

 
Last revision 2008RTM
See also

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