Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_changemergefilter

  No additional text.


Syntax
create procedure sys.sp_changemergefilter(
    @publication            sysname,
    @article                sysname,
    @filtername                sysname,
    @property                sysname,
    @value                    nvarchar(1000),
    @force_invalidate_snapshot bit = 0, /* Force invalidate existing snapshot */
    @force_reinit_subscription bit = 0    /* Force reinit subscription */
    )AS

    set nocount on

    declare @pre_command        int
    declare @qual_object        nvarchar(258)
    declare @qual_join_object    nvarchar(258)
    declare @owner				nvarchar(258)
    declare @join_object_owner	nvarchar(258)
    declare @join_articlename    nvarchar(4000)
    declare @db_name            sysname
    declare @pubid                uniqueidentifier
    declare @artid                uniqueidentifier
    declare @objid                int
    declare @object                sysname
    declare @join_object        sysname
    declare @retcode            int
    declare @join_filterid        int
    declare @join_objid            int
    declare @join_nickname        int
    declare @art_nickname        int
    declare @snapshot_ready        int
    declare @filter_type        tinyint
    declare @join_unique_key    int
    declare @join_filterclause    nvarchar(1000)
    declare @automatic_reinitialization_policy bit
    declare @regenerate_triggers bit
    declare @got_merge_admin_applock bit

    select @got_merge_admin_applock = 0

    /*
    ** Security Check.
    ** Only the System Administrator (SA) or the Database Owner (dbo) can
    ** call this procedure
    */
    exec @retcode = sys.sp_MSreplcheck_publish
    if @@ERROR <> 0 or @retcode <> 0
        return(1)

    select @regenerate_triggers = 0
    /*
    ** Parameter Check: @join_articlename.
    ** The join_articlename cannot be NULL
    */
    if @filtername is NULL
    begin
        raiserror (14043, 11, -1, '@filtername', 'sp_changemergefilter')
        return (1)
    end

    if @value is NULL or @value = ''
    begin
        raiserror (14043, 11, -1, '@value', 'sp_changemergefilter')
        return (1)
    end

    /*
    ** Parameter Check: @publication.
    ** The @publication id cannot be NULL and must conform to the rules
    ** for identifiers.
    */
    if @publication is NULL
    begin
        raiserror (14043, 11, -1, '@publication', 'sp_changemergefilter')
        return (1)
    end
    /*
    ** Get the pubid and make sure the publication exists
    */
    select @pubid = pubid,
           @snapshot_ready=snapshot_ready,
           @automatic_reinitialization_policy = automatic_reinitialization_policy
       from dbo.sysmergepublications where
       name = @publication and UPPER(publisher)=UPPER(publishingservername()) and publisher_db=db_name()
    if @pubid is NULL
    begin
        raiserror (20026, 16, -1, @publication)
        return (1)
    end

    select @db_name = db_name from dbo.sysmergesubscriptions
        where (pubid=@pubid) and (subid=@pubid)
    IF @db_name <> db_name()
    BEGIN
        RAISERROR (20047, 16, -1)
        RETURN (1)
    END

    /*
    ** Parameter Check: @article.
    ** Check to see that the @article is valid and does exist
    */
    if @article is NULL
    begin
        raiserror (20045, 16, -1)
        return (1)
    end

    select    @artid = artid, @object = object_name(objid), @objid = objid,
            @pre_command = pre_creation_command, @art_nickname = nickname
    from dbo.sysmergearticles
    where name = @article and pubid = @pubid
    if @artid is NULL
        begin
            raiserror (20046, 16, -1)
            return (1)
        end

    select @join_filterid=join_filterid, @join_articlename=join_articlename, @join_nickname = join_nickname,
            @join_unique_key = join_unique_key, @join_filterclause = join_filterclause
    from dbo.sysmergesubsetfilters
    where pubid=@pubid and artid=@artid and filtername=@filtername
    if @join_filterid is null
        begin
            raiserror (21362, 16, -1, @filtername)
            return (1)
        end

    select @join_object = object_name(objid), @join_objid = objid from dbo.sysmergearticles
        where nickname = @join_nickname and pubid=@pubid

    IF @property IS NULL
    BEGIN
        CREATE TABLE #temp (properties sysname collate database_default)
        INSERT INTO #temp VALUES ('filtername')
        INSERT INTO #temp VALUES ('join_filterclause')
        INSERT INTO #temp VALUES ('join_articlename')
        INSERT INTO #temp VALUES ('join_unique_key')
        INSERT INTO #temp VALUES ('filter_type')
        select * FROM #tab1
        RETURN (0)
    END


    if @value is null
    begin
        RAISERROR (14043, 16, -1, @property, 'sp_changemergefilter')
        return (1)
    end

    begin TRAN
    save TRAN change_filter

    exec @retcode = sys.sp_MSgetmergeadminapplock @timeout = 0, -- no wait
                                                  @lockowner = N'Transaction'
    if @retcode<>0 or @@error<>0
    begin
        raiserror(20713, 16, -1, 'sp_changemergefilter', @publication)
        goto FAILURE
    end

    select @got_merge_admin_applock = 1

    if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) in
            ('join_filterclause','join_articlename','join_unique_key', 'filter_type')
        and @snapshot_ready>0
    begin
            -- 1 means'drop': which is the only option that support reintialization
            if @pre_command<>1
                begin
                    raiserror(21419, 16, -1, @filtername, @article)
                    goto FAILURE
                end
            /*
            ** make sure we know we really want to do this.
            */
            if @force_invalidate_snapshot = 0 and @snapshot_ready = 1
                begin
                    raiserror(20607, 16, -1)
                    goto FAILURE
                end
            if @force_reinit_subscription = 0
                begin
                    raiserror(20608, 16, -1)
                    goto FAILURE
                end

            select @regenerate_triggers = 1

            update dbo.sysmergepublications
                set snapshot_ready=2,
                use_partition_groups = case when use_partition_groups = 1 then 2 else use_partition_groups end
            where pubid=@pubid
            if @@ERROR<>0 goto FAILURE

            exec @retcode = sys.sp_MSreinitmergepublication
                                    @publication = @publication,
                                    @upload_first = @automatic_reinitialization_policy
            if @retcode<>0 or @@ERROR<>0 goto FAILURE

    end

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS)='join_filterclause'
    BEGIN

        -- check if the join_filterclause contains any column of type that is not supported in
        --  a join filter.

        if exists    (
                        select * from sys.columns
                        where object_id = @objid and
                        (
                            --(sys.fn_IsTypeBlob(sc.system_type_id,sc.max_length) = 1) -- Blob type text,ntext,xml
                              (system_type_id in (type_id('image'), type_id('text'), type_id('ntext'), type_id('xml')))
                              or max_length = -1
                              or system_type_id = 240    -- CLR-UDTs
                        )
                        and sys.fn_MSisfilteredcolumn(@value, name, @objid) = 1
                    )
        begin
                raiserror(22518, 16, -1, @object)
                goto FAILURE
        end

        update dbo.sysmergesubsetfilters set join_filterclause=@value
        where join_filterid=@join_filterid
        execute @retcode = sys.sp_MSsubsetpublication @publication
        if @@ERROR <> 0 or @retcode<>0
            goto FAILURE
    END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS)='join_unique_key'
    BEGIN
        IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true','false')
        BEGIN
             RAISERROR(14137,16,-1)
             goto FAILURE
        END
        if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'
            update dbo.sysmergesubsetfilters set join_unique_key=1 where join_filterid=@join_filterid
        else
            update dbo.sysmergesubsetfilters set join_unique_key=0 where join_filterid=@join_filterid
        if @@ERROR <> 0 or @retcode<>0
            goto FAILURE
    END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS)='filtername'
    BEGIN
        update dbo.sysmergesubsetfilters set filtername=@value
            where join_filterid=@join_filterid
        if @@ERROR<>0
            goto FAILURE
    END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS)='join_articlename'
    BEGIN

        -- consistency check join_articlename should not be the same as @article
        if @article = @value
        begin
            raiserror(21599, 16, -1)
            goto FAILURE
        end

        select @join_objid = objid from dbo.sysmergearticles where name = @value and pubid = @pubid

        IF @join_objid is NULL
        BEGIN
            raiserror (14027, 11, -1, @value)
            goto FAILURE
        END

        select @join_nickname = nickname from dbo.sysmergearticles
        where pubid = @pubid AND objid = @join_objid
        if @join_nickname is NULL
        begin
            raiserror (20001, 11, -1, @article, @publication)
            goto FAILURE
        end

        update dbo.sysmergesubsetfilters set join_articlename=@value, join_nickname=@join_nickname
            where join_filterid=@join_filterid
        if @@ERROR<>0
            goto FAILURE
    END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS)='filter_type'
    BEGIN
        if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = '1'
            select @filter_type = 1
        else if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = '2'
            select @filter_type = 2
        else if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = '3'
            select @filter_type = 3 -- (1 | 2)=3
        else
        begin
            raiserror (21575, 16, -1)
            goto FAILURE
        end

        update dbo.sysmergesubsetfilters set filter_type = @filter_type
            where join_filterid=@join_filterid
        if @@ERROR<>0
            goto FAILURE
    END

    -- Do a final validation. This needs to be done irrespective of which property is being set.
    select @join_filterid=join_filterid, @join_articlename=join_articlename, @join_nickname = join_nickname,
            @join_unique_key = join_unique_key, @join_filterclause = join_filterclause, @filter_type = filter_type
    from dbo.sysmergesubsetfilters
    where pubid=@pubid and artid=@artid and filtername=@filtername

    select @join_object = object_name(objid), @join_objid = objid from dbo.sysmergearticles
        where nickname = @join_nickname and pubid=@pubid

    if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) in
            ('join_filterclause','join_articlename')
    begin
        select @qual_object=QUOTENAME(@object)
        select @qual_join_object=QUOTENAME(@join_object)

        select @owner = quotename(SCHEMA_NAME(schema_id)) from sys.objects where object_id = @objid
		select @join_object_owner = quotename(SCHEMA_NAME(schema_id)) from sys.objects where object_id = @join_objid

        exec ('declare @test int select @test=1 from ' + @owner + '.' + @qual_object + ' ' + @qual_object
						+ ', ' + @join_object_owner + '.' + @qual_join_object + ' ' + @qual_join_object + ' where ' + @join_filterclause)
        if @@ERROR<>0
        begin
            raiserror(21256, 16, -1, @join_filterclause, @object)
            goto FAILURE
        end
    end

    if (@filter_type & 2) = 2
    begin
        declare @pubname_lessthan90compat sysname
        declare @owner_qualified_art_name nvarchar(517)
        declare @owner_qualified_join_name nvarchar(517)

        select @pubname_lessthan90compat = NULL

        -- Only supported with publications that have 90 compatibility level.
        select top 1 @pubname_lessthan90compat = name from dbo.sysmergepublications
        where backward_comp_level < 90
        and pubid in
            (select pubid from dbo.sysmergearticles where nickname = @art_nickname)

        if @pubname_lessthan90compat is not null
        begin
            raiserror(21574, 16, -1, @article, @pubname_lessthan90compat)
            goto FAILURE
        end

        select @pubname_lessthan90compat = NULL

        -- Only supported with publications that have 90 compatibility level.
        select top 1 @pubname_lessthan90compat = name from dbo.sysmergepublications
        where backward_comp_level < 90
        and pubid in
            (select pubid from dbo.sysmergearticles where nickname = @join_nickname)

        if @pubname_lessthan90compat is not null
        begin
            raiserror(21574, 16, -1, @join_articlename, @pubname_lessthan90compat)
            goto FAILURE
        end

        if @join_unique_key = 0
        begin
            -- logical record relation only supported with join_unique_key=1
            raiserror(21539, 16, -1)
            goto FAILURE
        end

        -- Cannot use Logical records and BusinessLogicResolvers at the same time.
        if exists (select * from dbo.sysmergearticles where ((nickname = @art_nickname) or
                                                            (nickname = @join_nickname)) and
                                                            (resolver_clsid = '00000000-0000-0000-0000-000000000000'))
        begin
            raiserror(20708, 16, -1)
            goto FAILURE
        end

        if exists (select * from dbo.sysmergepublications where pubid = @pubid and allow_web_synchronization = 1)
        begin
            raiserror(22545, 16, -1, @publication)
            goto FAILURE
        end

        if exists (select * from dbo.sysmergepublications where pubid = @pubid and sync_mode = 1)
        begin
            raiserror(22541, 16, -1, @publication)
            goto FAILURE
        end

        if exists (select * from dbo.sysmergesubsetfilters
                                                where art_nickname = @art_nickname
                                                and join_nickname <> @join_nickname
                                                and (filter_type & 2) = 2)
        begin
            -- there cannot be two parents of a child in a logical record relationship
            raiserror (21538, 16, -1, @object, @join_object)
            goto FAILURE
        end

        if @filter_type & 1 = 1 and exists (select * from dbo.sysmergepublications
                                            where pubid = @pubid and use_partition_groups <= 0)
        begin
            raiserror(21571, 16, -1, @publication)
            goto FAILURE
        end

        -- We do not allow logical records to be used if the articles involved in a join filter
        -- have cascading constraints defined on them
        -- yiche
        if exists (select * from sys.foreign_keys fks
        where fks.parent_object_id in (@join_objid, @objid) and
        	(ObjectProperty(fks.object_id, 'CnstIsDeleteCascade') = 1  -- on delete cascade
        	or ObjectProperty(fks.object_id, 'CnstIsUpdateCascade') = 1 -- on update cascade
           	or fks.delete_referential_action = 2 -- on delete set null
		or fks.delete_referential_action = 3 -- on delete set default
		or fks.update_referential_action = 2  -- on update set null
		or fks.update_referential_action = 3 -- on update set default
        	))
        begin
            select @owner_qualified_art_name = quotename(SCHEMA_NAME(schema_id)) + '.' + quotename(name)
            from sys.objects
            where object_id = @objid

            select @owner_qualified_join_name = quotename(SCHEMA_NAME(schema_id)) + '.' + quotename(name)
            from sys.objects
            where object_id = @join_objid

            raiserror(25019, 16, -1, @owner_qualified_art_name, @owner_qualified_join_name)
            goto FAILURE
        end

        -- Enforce DRI relation
        exec @retcode = sys.sp_MScheck_joinfilter_using_dri @publication,
                                                            @article,
                                                            @objid,
                                                            @join_objid,
                                                            1,
                                                            @join_filterclause
        if @@error <> 0 or @retcode <> 0
            goto FAILURE

        -- based on usability feeback, we should set the allow_subscriber_initiated_snapshot
        -- option to 1 rather than raise an error.
        update dbo.sysmergepublications set allow_subscriber_initiated_snapshot = 1
            where pubid = @pubid
    end

    exec @retcode = sys.sp_MSvalidate_wellpartitioned_articles @publication
    if @@error <> 0 or @retcode <> 0
        goto FAILURE

    if (@regenerate_triggers = 1)
    begin
        exec @retcode = sys.sp_MSpublicationview @publication = @publication, @force_flag = 1
        if @@ERROR<>0 or @retcode <>0
            goto FAILURE

        exec @retcode = sp_MSregenerate_mergetriggers @publication = @publication
        if @@ERROR<>0 or @retcode<>0
            goto FAILURE
    end

    exec sys.sp_MSreleasemergeadminapplock @lockowner = N'Transaction'
    Commit TRAN
    return(0)

FAILURE:
    if @@TRANCOUNT > 0
    begin
        if @got_merge_admin_applock=1
            exec sys.sp_MSreleasemergeadminapplock @lockowner = N'Transaction'
        ROLLBACK TRANSACTION change_filter
        COMMIT TRANSACTION
    end
    RAISERROR (20038, 16, -1, @article, @publication)
    return(1)


 
Last revision 2008RTM
See also

  sp_MSdrop_rladmin (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