Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_articlefilter

  No additional text.


Syntax
create procedure sys.sp_MSrepl_articlefilter
(
    @publication				sysname,
    @article					sysname,
    @filter_name				nvarchar (517),
    @filter_clause				ntext,
    @force_invalidate_snapshot	bit,
    @force_reinit_subscription	bit,
    @publisher					sysname,
    @publisher_type				sysname
)
AS
BEGIN
    declare @pubid smallint
            ,@table_name sysname
            ,@user_name sysname
            ,@qualified_table_name nvarchar (258)
            ,@filter_id int
            ,@type tinyint
            ,@previous_proc nvarchar(517)
            ,@retcode int
            ,@site sysname
            ,@db sysname
            ,@owner sysname
            ,@object nvarchar(517)
            ,@artid int
            ,@active tinyint
            ,@obid int
            ,@view_id int
            ,@cmd nvarchar(4000)
            ,@allow_sync_tran bit
            ,@allow_queued_tran bit
            ,@options int
            ,@loc_publisher sysname
            ,@objid int

    DECLARE @OPT_ENABLED_FOR_P2P int
	SELECT	@OPT_ENABLED_FOR_P2P = 0x1

    select @active = 2
            ,@loc_publisher = case when (@publisher_type = N'MSSQLSERVER') then NULL else @publisher end
    
    -- Security Check.
    
    exec @retcode = sys.sp_MSreplcheck_publish
    if @@ERROR <> 0 or @retcode <> 0
        return(1)

    
    -- Parameter Check:  @publication.
    -- Make sure that the publication exists and that it conforms to the
    -- rules for identifiers.
    
    if @publication is null
    begin
        RAISERROR (14043, 16, -1, '@publication', 'sp_MSrepl_articlefilter')
        return (1)
    END
    execute @retcode = sys.sp_validname @publication
    if @retcode <> 0
        RETURN (1)

	-- Get publication id
	SELECT @pubid = sys.fn_MSrepl_getpubid(@publication, @publisher, @publisher_type)

    SELECT	@allow_sync_tran = allow_sync_tran,
    		@allow_queued_tran = allow_queued_tran,
    		@options = options
    FROM	syspublications
    WHERE	pubid = @pubid

    IF @pubid IS NULL
    BEGIN
        RAISERROR (20026, 11, -1, @publication)
        RETURN (1)
    END
    
    -- Parameter Check:  @article.
    -- Check to make sure that the article exists in the publication.
    
    if @article is null
    begin
        RAISERROR (14043, 16, -1, '@article', 'sp_MSrepl_articlefilter')
        return (1)
    end
    /*
    execute @retcode = sys.sp_validname @article
    if @retcode <> 0
    return (1)
    */

    /*
    	Enforce PeerToPeer restrictions
    */
    IF (@options & @OPT_ENABLED_FOR_P2P) = @OPT_ENABLED_FOR_P2P
    BEGIN
    	-- Peer-To-Peer publications do not support filtering. Please change the '@publication' parameter value.
		RAISERROR(20646, 16, -1, 'filtering', '@publication')
		RETURN (1)
    END

    
    -- Get the article information.
    
    IF @publisher_type = N'MSSQLSERVER'
    BEGIN
        SELECT	@artid = art.artid,
        		@table_name = so.name,
        		@type = art.type,
        		@filter_id = art.filter,
        		@user_name = schema_name(so.schema_id),
        		@view_id = ISNULL(art.sync_objid, art.objid),
        		@objid = art.objid
        FROM	sysarticles art, sys.objects so
        WHERE	art.pubid = @pubid
          AND	art.name = @article
          AND	art.objid = so.object_id
    END
    ELSE
    BEGIN
        SELECT	@artid = iha.article_id,
        		@table_name = ihpt.name,
        		@type = iha.type,
        		@filter_id = iha.filter,
        		@user_name = ihpt.owner,
        		@view_id = ISNULL(iha.sync_objid, iha.objid),
        		@objid = iha.table_id
        FROM	IHarticles iha, IHpublishertables ihpt
        WHERE	iha.publication_id = @pubid
          AND	iha.name = @article
          AND	iha.table_id = ihpt.table_id
    END
    /*
    ** Fail if there is no article information.
    */
    if @artid is null
    begin
        RAISERROR (20027, 11, -1, @article)
        return (1)
    end
    /*
    ** Error out if this is a not a table based article
    */
    IF NOT EXISTS ( SELECT * FROM sysarticles WHERE artid = @artid
            AND pubid = @pubid
            AND (type & 1) = 1 )
    BEGIN
        RAISERROR (14112, 11, -1 )
        RETURN (1)
    END
    /*
    ** Make sure a valid @filter_name was provided and it is
    ** a valid name.
    */
    if datalength(@filter_clause) > 0
    begin
        /*
        ** Make sure a valid @filter_name was provided and it is
        ** a valid name.
        */
        if @filter_name is null
        begin
           RAISERROR (14043, 16, -1, '@filter_name', 'sp_MSrepl_articlefilter')
           return (1)
        end
        select @object = PARSENAME( @filter_name, 1 )
                ,@owner  = PARSENAME( @filter_name, 2 )
                ,@db     = PARSENAME( @filter_name, 3 )
                ,@site   = PARSENAME( @filter_name, 4 )
        if @object IS NULL
        begin
            raiserror (21344, 16, -1, '@filter_name')
            return 1
        end
        if @owner is NULL
            select @owner = schema_name()
        select @object = quotename(@owner) + N'.' + quotename(@object)
    end
    
    -- Check if there are snapshot or subscriptions and raiserror if needed.
    
    EXECUTE @retcode  = sys.sp_MSreinit_article
						@publication				= @publication,
						@article					= @article,
						@need_new_snapshot			= 1,
						@need_reinit_subscription	= 1,
						@force_invalidate_snapshot	= @force_invalidate_snapshot,
						@force_reinit_subscription	= @force_reinit_subscription,
						@check_only					= 1,
						@publisher					= @loc_publisher,
						@publisher_type				= @publisher_type
    IF @@ERROR <> 0 OR @retcode <> 0
        return 1

    begin tran
    save TRANSACTION articlefilter

    /*
    ** If the article has a generated filter (not manually created), then
    ** drop the current filter before creating the new one.
    */
    if ((@type & 0x3) <> 0x3) and @filter_id <> 0
    begin
        if exists (select * from sys.objects where object_id = @filter_id
            and type = 'RF')
        begin
            exec sys.sp_MSget_qualified_name @filter_id, @previous_proc output
            exec ('drop procedure ' + @previous_proc)
            if @@error <> 0
                goto UNDO
        end
    end
    
    -- make an owner qualified table name for these operations name
    
    select @qualified_table_name = quotename(@user_name) + '.' + quotename(@table_name)
    IF @publisher_type = N'MSSQLSERVER'
    BEGIN
        -- Drop replication filter if it exists.
        -- Note: upgrade needs this logic
        if datalength(@filter_clause) > 0
                and exists (select * from sys.objects where object_id = object_id(@object)
                and type = 'RF')
        begin
            exec ('drop procedure ' + @object)
            if @@error <> 0
                goto UNDO
        end
    END
    /*
    ** If there is a @filter_clause, create the new filter and
    ** update the article filter id and filter_clause.
    **/
    if datalength(@filter_clause) > 0
    begin
        declare @subst_clause nvarchar(max)
        				,@setops nvarchar(1024)
        				,@filter_proc_cmd nvarchar(max)
        select @subst_clause = @filter_clause


        exec @retcode = sys.sp_MSsubst_filter_names @user_name, @table_name, @subst_clause output
        if @retcode <> 0 or @@error <> 0
            goto UNDO


		if exists (select 1 from sys.views where object_id = @objid)
		begin
			set @setops = N'set ansi_nulls on ' +
									N'set quoted_identifier on ' +
									N'set concat_null_yields_null on ' +
									N'set ansi_warnings on ' +
									N'set ansi_padding on ' +
									N'set arithabort on ' +
									N'set numeric_roundabort off '
		end
		else
			set @setops = N''

        set @filter_proc_cmd = @setops +
        										N'exec('' create procedure ' +
        											sys.fn_replreplacesinglequote(@object) +
        										N' for replication as ' +
        										N'if exists (select * from ' +
        											sys.fn_replreplacesinglequote(@qualified_table_name) +
								            	N' where ' +
								            		sys.fn_replreplacesinglequote(@subst_clause) +
            									N') return 1 else return 0'')'
			exec(@filter_proc_cmd)
       if @@error <> 0
		begin
	    	-- Filter view or procedure generation failed. Check and ensure that the filter clause is valid.
	    	RAISERROR (21745, 16, -1)
	    	goto UNDO
	    end
	
        if (@user_name in ('dbo','INFORMATION_SCHEMA'))
        begin
            exec @retcode = dbo.sp_MS_marksystemobject @object
            if @@error <> 0
                goto UNDO
        end

        select @filter_id = object_id  from sys.objects where object_id = object_id(@object)
            and type = 'RF'
        if @filter_id is null or @filter_id = 0
           begin
              RAISERROR (15001, 11, -1, @object)
              goto UNDO
           end
        /*
        ** Update article
        */
        update sysarticles set filter = @filter_id,
           filter_clause = @filter_clause
           where pubid = @pubid
              and name = @article
        if @@error <> 0
            goto UNDO

        IF @publisher_type = N'MSSQLSERVER'
        BEGIN
            
            -- SQL SERVER 7.0 ONLY: update sys.objects, set parent id = underlying
            -- object id
            
            select @obid = object_id( @qualified_table_name )
            EXEC @retcode = sys.sp_MSsetfilterparent @object, @obid
            if @retcode <> 0 or @@error <> 0
                goto UNDO
            EXEC @retcode = sys.sp_MSsetfilteredstatus @obid
            if @retcode <> 0 or @@error <> 0
                goto UNDO
        END
    end
    else
    begin
        /*
        ** Clear the filter id and filter_clause.
        */
        update sysarticles set filter = 0,
           filter_clause = NULL
           where pubid = @pubid
              and name = @article
        if @@error <> 0
            goto UNDO

        if @publisher_type = N'MSSQLSERVER'
        begin
            
            -- SQL SERVER 7.0 ONLY:  remove parent_id from filter proc
            
            select @obid = object_id( @qualified_table_name )
            if exists ( select * from sys.objects where object_id = object_id(@object)
                and type = 'RF')
            begin
                EXEC @retcode = sys.sp_MSsetfilterparent @object, 0
                if @retcode <> 0 or @@error <> 0
                    goto UNDO
            end
            EXEC @retcode = sys.sp_MSsetfilteredstatus @obid
            if @retcode <> 0 or @@error <> 0
                goto UNDO
        end
    end
    
    -- Have to call this stored procedure to invalidate existing snapshot or reint
    -- subscriptions if needed
    
    EXECUTE @retcode  = sys.sp_MSreinit_article
						@publication				= @publication,
						@article					= @article,
						@need_new_snapshot			= 1,
						@need_reinit_subscription	= 1,
						@force_invalidate_snapshot	= @force_invalidate_snapshot,
						@force_reinit_subscription	= @force_reinit_subscription,
						@publisher					= @loc_publisher,
						@publisher_type				= @publisher_type

    IF @@ERROR <> 0 OR @retcode <> 0
        GOTO UNDO

    IF @publisher_type = N'MSSQLSERVER'
    BEGIN
        /*
        ** Force the article cache to be refreshed with the new definition.
        */
        EXECUTE sys.sp_replflush
    END
	
    COMMIT TRANSACTION
    return 0
UNDO:
    if @@trancount > 0
    begin
        ROLLBACK TRANSACTION articlefilter
        commit tran
    end
    RETURN (1)
END

 
Last revision 2008RTM
See also

  sp_articlefilter (Procedure)
sp_MSdrop_repltran (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