Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_articleview

  No additional text.


Syntax
create procedure sys.sp_MSrepl_articleview
(
    @publication                sysname,            /* Publication name */
    @article                    sysname,            /* Article name */
    @view_name                    nvarchar (386),        /* View name */
    @filter_clause                ntext,                /* Article's filter clause */
    @change_active                int,
    @force_invalidate_snapshot    bit,                /* Force invalidate existing snapshot */
    @force_reinit_subscription    bit,                /* Force reinit subscription */
    @refreshsynctranprocs         bit,                 -- 1 = regenerate synctran procedures inside this SP for filter change
    @internal                    bit
)
as
BEGIN
    declare @pubid smallint,
                @table_name sysname,
                @user_name sysname,
                @view_user_name sysname,
                @name sysname,
                @retcode int,
                @view_id int,
                @type tinyint,
                @table_id int,
                @previous_view sysname,
                @quoted_prev_view nvarchar(258),
                @colid int,
                @object sysname,
                @quoted_object nvarchar(512),
                @artid int,
                @active tinyint,
                @allow_sync_tran bit,
                @allow_queued_tran bit,
                @autogen_sync_procs_id bit,
                @filter_id int,
                @article_view nvarchar(255),
                @log_view nvarchar(255)
                ,@status tinyint
                ,@objid int
                ,@include_timestamps bit
                ,@options int
                ,@viewname nvarchar(255)
                --,@guid varbinary(16)
                ,@cmd nvarchar(max)
                ,@dbname sysname
                ,@OPT_ENABLED_FOR_P2P int
                ,@h_filter nvarchar(4000)

    select @active = 2
            ,@OPT_ENABLED_FOR_P2P = 0x1
            ,@dbname = db_name()

    -- 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, N'@publication', N'sp_MSrepl_articleview')
        RETURN (1)
    END
    EXECUTE @retcode = sys.sp_validname @publication
    IF @retcode <> 0
        RETURN (1)
    
    -- Get publication metadata
    
    SELECT  @pubid = pubid
                ,@allow_sync_tran = allow_sync_tran
                ,@allow_queued_tran = allow_queued_tran
                ,@autogen_sync_procs_id = autogen_sync_procs
                ,@options = options
    FROM dbo.syspublications
    WHERE name = @publication

    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, N'@article', N'sp_MSrepl_articleview')
        RETURN (1)
    END

    /*
        Enforce PeerToPeer restrictions
    */
    IF (@options & @OPT_ENABLED_FOR_P2P) = @OPT_ENABLED_FOR_P2P
        AND @internal = 0
    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 metadata.
    
    SELECT @artid = art.artid,
                @table_name    = so.name,
                @user_name    = schema_name(so.schema_id),
                @type        = art.type,
                @status        = art.status,
                @objid        = art.objid,
              @view_id    = art.sync_objid,
                @table_id    = art.objid,
                @filter_id    = art.filter
    FROM dbo.sysarticles as art
        JOIN sys.objects as so
            ON art.objid = so.object_id
    WHERE  art.pubid    = @pubid
      AND    art.name    = @article
    
    -- 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    dbo.sysarticles
        WHERE    artid = @artid
            AND    pubid = @pubid
            AND    (type & 1) = 1
    )
    BEGIN
        RAISERROR (14112, 11, -1 )
        RETURN (1)
    END
    
    -- Special handling for timestamp column.
    -- Although timestamp values are not needed, they have to be presented in bcp files. Otherwise
    -- bcp in will fail with data file format error.
    
    select @include_timestamps = case when (@status & 32 <> 0) then 1 else 0 end
    
    --Break out the specified view name and get the non-ownerqual'd name, then validate that.
    
    select @object = PARSENAME( @view_name, 1 )
    if @object IS NULL
    begin
        -- generate view name
        --set @guid = CONVERT(varbinary(16), LEFT(NEWID(),8))
        --exec @retcode = sys.xp_varbintohexstr @guid, @viewname OUTPUT
        --if @@ERROR <> 0 OR @retcode <> 0
        --    return (1)
        select @viewname = N'syncobj_'
            + sys.fn_varbintohexstr(CONVERT(varbinary(16), LEFT(NEWID(),8)))
        select @object = @viewname
    end
    SELECT @quoted_object = QUOTENAME(@object)
    EXECUTE @retcode = sys.sp_validname @object
    IF @retcode <> 0
        RETURN (1)

    -- 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 invalidate existing snapshot */
                        @force_reinit_subscription = @force_reinit_subscription,    /* Force reinit subscription */
                        @check_only = 1

    IF @@ERROR <> 0 OR @retcode <> 0
        RETURN 1

    -- ==============================================
    -- CREATE VIEW
    -- For SQL Server: Generate create view statement
    -- For HREPL:      Generate ordinal bit mask
    -- ==============================================
    BEGIN TRAN
    SAVE TRANSACTION articleview
    
    -- If the article has a generated view (not manually created), then
    -- drop the current view before creating the new one.
    
    if ((@type & 0x5) <> 0x5) and @view_id <> 0
            and @view_id <> @table_id
    begin
        select @previous_view = object_name (@view_id)
        if @previous_view is not null and
                exists (select * from sys.objects with( readpast ) where name = @previous_view
                and type = N'V')
        begin
            select @quoted_prev_view = QUOTENAME(@previous_view)
            exec (N'drop view ' + @quoted_prev_view)
            if @@error <> 0
                goto UNDO
        end
    end
    
    -- Construct and execute the view creation command.
    
    -- Drop the existing view.
    -- Note: upgrade needs this logic
    if datalength(@filter_clause) > 0 and
        exists (select * from sys.objects with( readpast ) where name = @object
    and type = 'V')
    begin
        exec (N'drop view ' + @quoted_object)
        if @@error <> 0
            goto UNDO
    end
    
    --  Update article definition
    --  Set @filter_clause value
    
    IF DATALENGTH(@filter_clause) > 0
    BEGIN
        UPDATE    dbo.sysarticles
        SET        filter_clause = @filter_clause
        WHERE    pubid = @pubid
          AND    artid = @artid

        IF @@error <> 0
            GOTO UNDO
    END
    ELSE
    BEGIN
        UPDATE    dbo.sysarticles
        SET        filter_clause = NULL
        WHERE    pubid = @pubid
          AND    artid = @artid

        IF @@error <> 0
            GOTO UNDO
    END
    
    -- create the view
    

    select @cmd = N'exec sys.sp_MSscript_article_view ' + cast(@artid as nvarchar(20))
                + N',N' + quotename(@object,'''') +
                + N',' + cast(@include_timestamps as nvarchar(1))

	begin try
		exec @retcode  = sys.sp_execresultset @cmd
	end try
	begin catch
		declare @number int
			,@sev int
			,@state int
			,@error nvarchar(max)
			,@msg nvarchar(max)
		select @number = ERROR_NUMBER(), @sev = ERROR_SEVERITY(), @state = ERROR_STATE(), @error = ERROR_MESSAGE()
		select @msg = N'Msg ' + cast(@number as nvarchar(50)) +
			N', Level ' + cast(@sev as nvarchar(50)) +
			N', State ' + cast(@state as nvarchar(50)) +
			N': ' + @error
		RAISERROR(@msg, 0, -1)
	        -- Filter view or procedure generation failed. Check and ensure that the filter clause is valid.
		if(@change_active = 0)
			RAISERROR (21745, 16, -1)
		else
			RAISERROR (14090, 16, -1, @article)
		set @retcode = 1
	end catch
   if @@error <> 0 or @retcode <> 0
   begin
    	goto UNDO
	end
    
    -- No need to grant select permission on sync view to
    -- public since sp_article_validate dynamically includes
    -- filtering criteria on the fly instead of using the
    -- sync view.
    
    
    -- Update the article's sync_objid with the new view id
    
    select @view_id = object_id from sys.objects with( readpast ) where name = @object and type = 'V'
    if @view_id is null or @view_id = 0
    begin
        RAISERROR (15001, 11, -1, @object)
        goto UNDO
    end
    else
    begin
        exec dbo.sp_MS_marksystemobject @object
    end
    
    --  Update article definition
    --  Set new sync_objid
    
    UPDATE    sysarticles
    SET        sync_objid = @view_id
    WHERE    pubid = @pubid
    AND    artid = @artid
    IF @@error <> 0
        GOTO UNDO
    -- sp_repldropcolumn used @change_active = 2 to prepare, don't invalidate or reinitialize
    IF @change_active <> 2
    BEGIN
        -- 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 invalidate existing snapshot */
                            @force_reinit_subscription = @force_reinit_subscription        /* Force reinit subscription */

        IF @@ERROR <> 0 OR @retcode <> 0
            GOTO UNDO
    END
    
    -- Force the article cache to be refreshed with the new definition.
    
    EXECUTE sys.sp_replflush
    COMMIT TRANSACTION
    
    -- Validate that the new view does not exclude the horizontal filter
    
    IF ((@allow_sync_tran = 1 or @allow_queued_tran = 1) and
        (DATALENGTH(@filter_clause) > 0))
    BEGIN
        SELECT @h_filter = cast(@filter_clause as nvarchar(4000))
        SELECT @view_user_name = schema_name(schema_id) FROM sys.objects WHERE object_id=OBJECT_ID(@quoted_object, 'V')
        SELECT @cmd = N'select top 0 * into #dummy from ' +
                quotename(@view_user_name) + N'.' + @quoted_object +
                N' where ' + @h_filter

        EXEC (@cmd)
        IF (@@error != 0)
        BEGIN
            
            -- The row filter does not work for the current view
            
            RAISERROR(21392, 16, 1, @h_filter, @object, @article, @publication)
            
            -- drop the filter if not manually generated
            
            IF ((@type & 0x3) <> 0x3) and @filter_id <> 0
            BEGIN
                IF EXISTS
                (
                    SELECT    *
                    FROM    sys.objects
                    WHERE    object_id = @filter_id
                    AND    type = 'RF'
                )
                BEGIN
                    DECLARE @filter_proc nvarchar(517)
                    EXEC sys.sp_MSget_qualified_name @filter_id, @filter_proc output
                    EXEC (N'drop procedure ' + @filter_proc)
                    UPDATE  dbo.sysarticles
                        SET   filter = 0,
                                filter_clause = NULL
                    WHERE   pubid = @pubid
                        AND   artid = @artid

                    RAISERROR(21393, 16, 1, @h_filter, @article, @publication)
                END
            END
            RETURN 1
        END
        
        -- Regenerate the synctran procedures for horizontal filtering
        -- as we now use the view object in the synctran procedures
        -- If the flag is not set that means we will be explicitly regenerating
        -- synctran procedures after this SP is executed - so will skip the
        -- implicit regeneration here
        
        if (@refreshsynctranprocs = 1)
        begin
            exec @retcode = sys.sp_articlesynctranprocs @publication, @article, @autogen_sync_procs_id, 0
            -- UNDO change to 1 when nvarchar(max) support on xml data is in
            -- exec @retcode = sys.sp_articlesynctranprocs @publication, @article, @autogen_sync_procs_id, 1
            IF @@ERROR <> 0 OR @retcode <> 0
                return 1
        end
    END
    
    -- all done
    
    RETURN 0

UNDO:
    IF @@trancount > 0
    BEGIN
        ROLLBACK TRANSACTION articleview
        COMMIT TRAN
    END
    RETURN (1)
END

 
Last revision 2008RTM
See also

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