Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_IHarticleview

  No additional text.


Syntax
CREATE PROCEDURE sys.sp_IHarticleview
(
	@publication				sysname,
	@article					sysname,
	@view_name					nvarchar (386),
	@filter_clause				ntext,
	@change_active				int,
	@force_invalidate_snapshot	bit,
	@force_reinit_subscription	bit,
	@publisher					sysname,
	@publisher_type				sysname
)
as
BEGIN
    declare @table_name sysname,
			@user_name sysname,
			@name sysname,
			@retcode int,
			@type tinyint,
			@table_id int,
			@colid int,
			@artid int,
			@active tinyint,
			@repl_freq int,
			@publication_id int,
			@publisher_id int,
			@status tinyint,
			@article_view nvarchar(255),
			@article_view_owner nvarchar(255),
			@columns binary(128),
			@publishedcolumns binary(128),
			@upd_cmd nvarchar(255),
			@del_cmd nvarchar(255),
			@distributortimestamp datetime,
			@instance_id int

    SET NOCOUNT ON

    select @active = 2

	-- 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_IHarticleview')
		RETURN (1)
    END

    EXECUTE @retcode = sys.sp_validname @publication
    IF @retcode <> 0
		RETURN (1)

    select @publication_id = pubid,
		@repl_freq = repl_freq,
		@publisher_id = s.srvid
    from syspublications p,
		master..sysservers s,
		MSpublications m
    where 	p.name = @publication
	and UPPER(s.srvname collate database_default) = UPPER(@publisher) collate database_default
	and	m.publisher_id = s.srvid
	and	m.publication_id = p.pubid

    IF @publication_id 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_IHarticleview')
		RETURN (1)
    END

    -- Get the article information.
    SELECT	@artid		= iha.article_id,
			@table_name	= ihpt.name,
			@user_name	= ihpt.owner,
			@type		= iha.type,
			@status		= iha.status,
			@table_id	= iha.table_id,
			@article_view	= iha.article_view,
			@article_view_owner = msd.login,
			@upd_cmd	= iha.upd_cmd,
			@del_cmd	= iha.del_cmd,
			@instance_id = iha.instance_id
    FROM	IHarticles iha,
    		IHpublishertables ihpt,
    		IHpublishers ihp,
    		msdb..MSdistpublishers msd,
    		master..sysservers s
    WHERE	iha.publication_id	= @publication_id
      AND	UPPER(msd.name collate database_default)		= UPPER(@publisher)
      AND	UPPER(msd.name collate database_default)		= UPPER(s.srvname collate database_default)
      AND	s.srvid				= ihp.publisher_id
      AND	iha.name			= @article
      AND	iha.table_id		= ihpt.table_id
      AND	ihpt.publisher_id	= @publisher_id
      AND	ihpt.publisher_id   = ihp.publisher_id	
	
    -- Fail if there is no article information.
    IF @artid IS NULL
    BEGIN
        RAISERROR (20027, 11, -1, @article)
        RETURN (1)
    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					= @publisher,
						@publisher_type				= @publisher_type
						
    IF @@ERROR <> 0 OR @retcode <> 0
        RETURN 1

    -- Generate bitmap of ordinal columns
    EXECUTE @columns = sys.fn_IHarticle_columns
	@artid,
	@table_id,
	@publisher_id
    IF @@error <> 0
    BEGIN
        RAISERROR (21775, 11, -1, @article)
        RETURN (1)
    END

    IF (@publisher_type = N'ORACLE') AND (@repl_freq = 0)
    BEGIN
        -- If this is an ORACLE publisher and the article belongs to a transactional publication,
        -- Generate the bitmap of published columns to be used to generate the trigger and article
        -- log table
    	EXECUTE @publishedcolumns = sys.fn_IHpublished_columns
		@table_id,
		@publisher_id
    	IF @@error <> 0
    	BEGIN
        	RAISERROR (21776, 11, -1, @article)
        	RETURN (1)
    	END
    END
    ELSE
    BEGIN
        -- If this is an ORACLE GATEWAY publisher or the article belongs to a snapshot publication,
		-- set the publish column bitmask to the article columns bitmask.
		SELECT @publishedcolumns = @columns
    END

    -- Generate view name
    EXECUTE @article_view = sys.fn_IHview_name
	@artid,
	@table_id
    IF @@error <> 0
    BEGIN
        RAISERROR (21777, 11, -1, @article)
        RETURN (1)
    END

    BEGIN TRANSACTION

    -- Update distributor timestamp to reflect change in meta data	
    SET @distributortimestamp = GETDATE()
    UPDATE	dbo.IHpublishers
    SET		flush_request_time = @distributortimestamp
    WHERE	publisher_id = @publisher_id
    IF @@error <> 0
    BEGIN
    	GOTO UNDO
    END

    -- Increment the instance id used to mark new isntances
    -- of the log view.
    SET @instance_id = @instance_id + 1;	

    -- Update article definition
    -- Set filter clause and view names
    IF DATALENGTH(@filter_clause) > 0
    BEGIN
		UPDATE	dbo.IHarticles
		SET	filter_clause = @filter_clause,
			article_view = @article_view,
			article_view_owner = @article_view_owner,
			instance_id = @instance_id,
			publisher_status = 1
		WHERE	publication_id	= @publication_id
      	AND	article_id		= @artid
      	AND	publisher_id	= @publisher_id	

		IF @@error <> 0
		BEGIN
			GOTO UNDO
		END	
    END
    ELSE
    BEGIN
		UPDATE	dbo.IHarticles
		SET	filter_clause = NULL,
			article_view = @article_view,				
			article_view_owner = @article_view_owner,
			instance_id = @instance_id,
			publisher_status = 1
		WHERE	publication_id	= @publication_id
      	AND	article_id		= @artid
      	AND	publisher_id	= @publisher_id	
      	
      	IF @@error <> 0
      	BEGIN
			GOTO UNDO
		END	
    END

    -- 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_reinit_subscription	= @force_reinit_subscription,
							@publisher					= @publisher,
							@publisher_type				= @publisher_type

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

    COMMIT TRANSACTION

    Declare @OptimizeTrigger bit
	Declare @RecreateTriggers bit
    Declare @XCALL bit
	Declare @artcnt int

		
	set @OptimizeTrigger = 0
	set @RecreateTriggers = 0
	set @XCALL = 0

	IF @repl_freq = 0
	BEGIN
		-- If publisher type is ORACLE GATEWAY, for transactional publications,
		-- always regenerate triggers, even if they already exist, and generate
		-- with optimal performance
		IF @publisher_type = N'ORACLE GATEWAY'
		BEGIN
    			set @OptimizeTrigger = 1
    			set @RecreateTriggers = 1
			IF upper(@upd_cmd) like 'XCALL%' OR
			   upper(@del_cmd) like 'XCALL%' OR
			   -- all filtered columns must be captured in the trigger and placed
			   -- in the article log if a filter clause is specified for the article
			   DATALENGTH(@filter_clause) > 0	
				set @XCALL = 1
     		END	
	END	

    -- Create the publisher objects for the article
    EXECUTE @retcode = sys.sp_ORAaddarticle
    	@owner             = @user_name
       ,@tablename         = @table_name
       ,@publisher         = @publisher
       ,@table_id          = @table_id
       ,@OptimizeTrigger   = @OptimizeTrigger
       ,@RecreateTriggers  = @RecreateTriggers
       ,@XCALL             = @XCALL				
       ,@article_view      = @article_view
       ,@columns           = @columns
       ,@publishedcolumns  = @publishedcolumns
       ,@repl_freq         = @repl_freq
       ,@filter_clause     = @filter_clause
       ,@distributortimestamp = @distributortimestamp	
       ,@instance_id	   = @instance_id	

    IF @@ERROR <> 0 OR @retcode <> 0
    BEGIN
      	RAISERROR (21778, 16, -1, @article)

       	-- Set view and filter to NULL IHarticles
      	UPDATE	IHarticles
      	SET	filter_clause = NULL,
		article_view = NULL,
		article_view_owner = NULL
      	WHERE	publication_id	= @publication_id
      	AND	article_id		= @artid
      	AND	publisher_id	= @publisher_id	
      	
		RETURN(1)
    END

    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