Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_helparticle

  No additional text.


Syntax

create procedure sys.sp_MSrepl_helparticle
(
    @publication	sysname,
    @article		sysname,
    @returnfilter	bit,
    @publisher		sysname,
    @found			int  OUTPUT,
    @rmo			bit = 0
)
as
begin
    SET NOCOUNT ON
    set DEADLOCK_PRIORITY LOW

    /*
    ** Declarations.
    */

    DECLARE @pubid int
                ,@retcode int
                ,@subscriber_bit smallint
                ,@publish_bit int
                ,@source_object  sysname
                ,@source_owner   sysname
                ,@username sysname
                ,@objid int
                ,@artid int
                ,@identitycolname sysname
                ,@identityrangemanagementoption int
                ,@OPT_ENABLED_FOR_P2P int

    SELECT @publish_bit = 1
                ,@subscriber_bit = 4
                ,@username = suser_sname()
                ,@OPT_ENABLED_FOR_P2P = 0x1
                ,@found = 0

    /*
    ** Parameter Check:  @publication.
    ** Check to make sure that publication exists.
    */

    IF @publication IS NULL
    BEGIN
        RAISERROR (14043, 16, -1, '@publication', 'sp_MSrepl_helparticle')
        RETURN (1)
    END

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

    /*
    ** Security Check. Restrict to 'sysadmin', DBO of publishing database, PAL
    */
    IF is_member(N'db_owner') <> 1
    BEGIN
        exec @retcode = sys.sp_MSreplcheck_pull
            @publication = @publication,
            @given_login = @username
        IF @retcode <> 0 OR @@error <> 0
            RETURN (1)
    END

    /*
    ** Check if the database is published.
    */
    -- Error Message not raised due to possible information disclosure vuln.
    IF NOT EXISTS (SELECT * FROM master.dbo.sysdatabases
        WHERE name = db_name() collate database_default
        AND (category & @publish_bit) = @publish_bit)
	RETURN(0)

	/*
	**	Publication parameter check
	*/
    IF NOT EXISTS (SELECT * FROM syspublications WHERE name = @publication)
    BEGIN
        RAISERROR (20026, 11, -1, @publication)
        RETURN (1)
    END
    /*
    ** Initializations.
    */
    IF @publication IS NOT NULL
        SELECT @pubid = pubid FROM syspublications WHERE name = @publication

    /*
    ** Create a temporary table to hold all information.
    */

    CREATE TABLE #tab1
    (
        artid               			int             NOT NULL,
        creation_script     			nvarchar(255)   collate database_default null,
        del_cmd             			nvarchar(255)   collate database_default null,
        description         			nvarchar(255)   collate database_default null,
        dest_table          			sysname         collate database_default null,
        old_filter          			int             NULL,
        ins_cmd             			nvarchar(255)   collate database_default null,
        name                			sysname         collate database_default not null,
        objid               			int             NOT NULL,
        pubid               			int             NOT NULL,
        status              			int         	NOT NULL,
        sync_objid          			int             NULL,
        type                			smallint        NOT NULL,
        upd_cmd             			nvarchar(255)   collate database_default null,
        source_table        			nvarchar(300)   collate database_default null,      /* converted from objid */
        filter              			nvarchar(300)   collate database_default null,      /* converted from old_filter */
        sync_object         			nvarchar(300)   collate database_default null,      /* converted from sync_objid */
        vpartition          			bit             NULL,      /* computed */
        pre_creation_cmd    			tinyint     	NOT NULL,
        filter_clause       			ntext           NULL,
        schema_option       			binary(8)       NULL,
        dest_owner          			sysname         collate database_default null,
        source_owner        			sysname         collate database_default null,   /* these two columns are for 7.0 use only */
        unqua_source_object 			sysname         collate database_default null,   /* column source_table stays due to backward compatibility */
        sync_object_owner   			sysname         collate database_default null,
        unqua_sync_object   			sysname         collate database_default null,
        filter_owner        			sysname         collate database_default null,
        unqua_filter        			sysname         collate database_default null,
        identityrangemanagementoption	int				null,
        IsArtSchemaBound				bit				null,
        fire_triggers_on_snapshot		bit       		not null
    )

    CREATE UNIQUE INDEX idx1 ON #tab1 (name, pubid)

    /*
    ** Parameter Check:  @article.
    ** Check to make sure that the article exists, that it conforms
    ** to the rules for identifiers, and that it isn't NULL.
    */

    IF @article IS NULL
    BEGIN
        RAISERROR (14043, 16, -1, '@article', 'sp_MSrepl_helparticle')
        RETURN (1)
    END

    IF @article <> '%'
    BEGIN
        /*
        EXECUTE @retcode = sys.sp_validname @article

        IF @retcode <> 0
        RETURN (1)
        */

        IF NOT EXISTS
        (
        	SELECT	*
			FROM	sysextendedarticlesview
			WHERE	name = @article
			  AND	pubid IN
			  		(
			  			SELECT	pubid
			          	FROM	syspublications
			         	WHERE	name = @publication
			         )
		)
        BEGIN
            RAISERROR (20027, 11, -1, @article)
            RETURN (1)
        END
    END

    -- Only attempt to get an application lock if the caller is
    -- db_owner as the intention of the lock is for coordinating between
    -- the snapshot agent (db_owner) and ddl replication (requires db_owner
    -- via sp_MSprep_exclusive)
    IF is_member(N'db_owner') = 1
    BEGIN
        EXEC sys.sp_getapplock @Resource = @publication,
                @LockMode = N'Shared',
                @LockOwner = N'Session',
                @DbPrincipal = N'db_owner'
    END

    IF @returnfilter = 1
    BEGIN
        INSERT INTO #tab1 (artid, creation_script, del_cmd,
                           description, dest_table, old_filter,
                           ins_cmd, name, objid, pubid, status,
                           sync_objid, type, upd_cmd, source_table,
                           filter, vpartition, pre_creation_cmd,
               filter_clause, schema_option, dest_owner, source_owner, unqua_source_object,
               sync_object_owner, unqua_sync_object, filter_owner, unqua_filter, fire_triggers_on_snapshot)

         (SELECT artid, creation_script, del_cmd, a.description,
                 dest_table, filter, ins_cmd, a.name, objid, a.pubid,
                 a.status, sync_objid, a.type, upd_cmd, NULL, NULL, 0,
                a.pre_creation_cmd, a.filter_clause, a.schema_option, a.dest_owner,
                schema_name(o.schema_id), o.name,
                schema_name(sync.schema_id), sync.name,
                schema_name(fltr.schema_id), fltr.name,
                a.fire_triggers_on_snapshot
            FROM syspublications b,
                 sys.objects o,
                 sysextendedarticlesview a
          LEFT JOIN sys.objects sync on a.sync_objid = sync.object_id
          LEFT JOIN sys.objects fltr on a.filter = fltr.object_id
           WHERE ((@article = N'%') or (a.name = @article))
             AND a.objid = o.object_id
             AND a.pubid = b.pubid
             AND b.name = @publication)
    END
    ELSE
    BEGIN
        INSERT INTO #tab1 (artid, creation_script, del_cmd,
                           description, dest_table, old_filter,
                           ins_cmd, name, objid, pubid, status,
                           sync_objid, type, upd_cmd, source_table,
                           filter, vpartition, pre_creation_cmd,
               filter_clause, schema_option, dest_owner, source_owner, unqua_source_object,
               sync_object_owner, unqua_sync_object, filter_owner, unqua_filter, fire_triggers_on_snapshot)
         (SELECT artid, creation_script, del_cmd, a.description,
                 dest_table, filter, ins_cmd, a.name, objid, a.pubid,
                 a.status, sync_objid, a.type, upd_cmd, NULL, NULL, 0,
                 a.pre_creation_cmd, NULL, schema_option, dest_owner,
                 schema_name(o.schema_id), o.name,
                 schema_name(sync.schema_id), sync.name,
                 schema_name(fltr.schema_id), fltr.name,
                 a.fire_triggers_on_snapshot
           FROM syspublications b,
                sys.objects o,
                sysextendedarticlesview a
           LEFT JOIN sys.objects fltr on a.filter = fltr.object_id
           LEFT JOIN sys.objects sync on a.sync_objid = sync.object_id
           WHERE  ((@article = N'%') or (a.name = @article))
             AND a.objid = o.object_id
             AND a.pubid = b.pubid
             AND b.name = @publication)
    END

    IF is_member(N'db_owner') = 1
    BEGIN
        EXEC sys.sp_releaseapplock @Resource = @publication, @LockOwner = N'Session', @DbPrincipal = N'db_owner'
    END
    
    -- update the qualified names with existing data
    
    UPDATE #tab1
        SET source_table = QUOTENAME(source_owner) + '.' + QUOTENAME(unqua_source_object)
        ,sync_object = QUOTENAME(sync_object_owner) + '.' + QUOTENAME(unqua_sync_object)
        ,filter = QUOTENAME(filter_owner) + '.' + QUOTENAME(unqua_filter)
        ,IsArtSchemaBound = objectproperty(objid, 'IsSchemaBound')

    
    -- identity specific processing
    
    DECLARE #hC  CURSOR LOCAL FAST_FORWARD FOR
        SELECT artid, name, pubid, objid FROM #tab1
    OPEN #hC
    FETCH #hC INTO @artid, @article, @pubid, @objid
    WHILE (@@fetch_status <> -1)
    BEGIN
        IF EXISTS (SELECT *
            FROM (sysextendedarticlesview a join sys.columns b
                on a.objid = b.object_id)
        WHERE a.name = @article
            AND a.pubid = @pubid
            and b.column_id not in  (select colid from sysarticlecolumns where artid = a.artid))
        begin
            UPDATE #tab1
            SET vpartition = 1
            WHERE name = @article
                AND pubid = @pubid
        end
        
        -- determine the identityrangemanagementoption value
        -- initialize variables
        
        select @identityrangemanagementoption = null
                ,@identitycolname = null
        -- check if we have an identity column
        select @identitycolname = name
        from sys.columns
        where object_id = @objid and ColumnProperty(object_id, name, 'IsIdentity') = 1
        if (@identitycolname is null)
        begin
            
            -- We have no identity column
            -- Check if P2P is enabled for this publication
            
            if exists (select * from syspublications
                            where name = @publication
                                and (options & @OPT_ENABLED_FOR_P2P) = @OPT_ENABLED_FOR_P2P)
            begin
                -- P2P enable publication - return manual - eases scripting
                select @identityrangemanagementoption = 2 -- manual
            end
            else
            begin
                -- regular publication
                select @identityrangemanagementoption = 0 -- none
            end
        end
        else
        begin
            
            -- We have an identity column
            -- check if it is NFR enabled
            
            if (ColumnProperty(@objid, @identitycolname, 'IsIdNotForRepl') = 1)
            begin
                
 -- NFR enabled
                -- check if sysarticlesupdates has range information
                
                if exists (select * from dbo.sysarticleupdates
                                where artid = @artid and pubid = @pubid
                                    and identity_support = 1)
                begin
                    -- range option is set
                    select @identityrangemanagementoption = 1 -- auto
                end
                else
                begin
                    -- range option is not set
                    select @identityrangemanagementoption = 2 -- manual
                end
            end
            else
            begin
                
                -- NFR not enabled
                
                select @identityrangemanagementoption = 0 -- none
            end
        end
        
        -- update identitymanagementoption column
        
        UPDATE #tab1
        SET identityrangemanagementoption = @identityrangemanagementoption
        WHERE name = @article
            AND pubid = @pubid
        
        -- fetch next article entry
        
        FETCH #hC INTO @artid, @article, @pubid, @objid
    END
    CLOSE #hC
    DEALLOCATE #hC

	IF (@rmo = 0)
	BEGIN
		-- Normal usage
	    SELECT	'article id'                    = art.artid,
				'article name'                  = name,
				'base object'                   = source_table,
				'destination object'            = dest_table,
				'synchronization object'        = sync_object,
				'type'                          = case
				                                    when art.IsArtSchemaBound = 1 and type <> 0x80 then 0x0100 | convert(smallint, type)
				                                    else type
				                                  end,
				'status'                        = status & ~32,
				'filter'                        = filter,
				'description'                   = description,
				'insert_command'                = ins_cmd,
				'update_command'                = upd_cmd,
				'delete_command'                = del_cmd,
				'creation script path'          = creation_script,
				'vertical partition'            = vpartition,
				'pre_creation_cmd'              = pre_creation_cmd,
				-- filter_clause is null when @return_filter is 0
				'filter_clause'                 = filter_clause,
				'schema_option'                 = schema_option,
				'dest_owner'                    = dest_owner,
				'source_owner'                  = source_owner,
				'unqua_source_object'           = unqua_source_object,
				'sync_object_owner'             = sync_object_owner,
				'unqualified_sync_object'       = unqua_sync_object,
				'filter_owner'                  = filter_owner,
				'unqua_filter'                  = unqua_filter,
				'auto_identity_range'           = isnull(artupd.identity_support, 0),
				'publisher_identity_range'      = abs(iden.pub_range),
				'identity_range'                = abs(iden.range),
				'threshold'                     = iden.threshold,
				'identityrangemanagementoption' = identityrangemanagementoption,
				'fire_triggers_on_snapshot'     = fire_triggers_on_snapshot
	    FROM #tab1 art
	        left join sysarticleupdates artupd on art.artid = artupd.artid
	        left join MSpub_identity_range iden on art.objid = iden.objid
	    ORDER BY 2
	END
	ELSE
	BEGIN
		-- Extended RMO version
	    SELECT	'article id'                    = art.artid,
				'article name'                  = name,
				'base object'                   = source_table,
				'destination object'            = dest_table,
				'synchronization object'        = sync_object,
				'type'                          = case
				                                    when art.IsArtSchemaBound = 1 and type <> 0x80 then 0x0100 | convert(smallint, type)
				                                    else type
				               end,
				'status'                        = status & ~32,
				'filter'                        = filter,
				'description'                   = description,
				'insert_command'                = ins_cmd,
				'update_command'                = upd_cmd,
				'delete_command'                = del_cmd,
				'creation script path'          = creation_script,
				'vertical partition'            = vpartition,
				'pre_creation_cmd'              = pre_creation_cmd,
				-- filter_clause is null when @return_filter is 0
				'filter_clause'                 = filter_clause,
				'schema_option'                 = schema_option,
				'dest_owner'                    = dest_owner,
				'source_owner'                  = source_owner,
				'unqua_source_object'           = unqua_source_object,
				'sync_object_owner'             = sync_object_owner,
				'unqualified_sync_object'       = unqua_sync_object,
				'filter_owner'                  = filter_owner,
				'unqua_filter'                  = unqua_filter,
				'auto_identity_range'           = isnull(artupd.identity_support, 0),
				'publisher_identity_range'      = abs(iden.pub_range),
				'identity_range'                = abs(iden.range),
				'threshold'                     = iden.threshold,
				'identityrangemanagementoption' = identityrangemanagementoption,
				'fire_triggers_on_snapshot'     = fire_triggers_on_snapshot,
				'tablespace'					= NULL,
				'use_default_datatypes'			= 1
	    FROM #tab1 art
	        left join sysarticleupdates artupd on art.artid = artupd.artid
	        left join MSpub_identity_range iden on art.objid = iden.objid
	    ORDER BY 2
	END

	if @@rowcount > 0
		SELECT @found=1

    RETURN (0)
end

 
Last revision 2008RTM
See also

  sp_helparticle (Procedure)
sp_helparticlecolumns (Procedure)
sp_MSdrop_repltran (Procedure)
sp_MSrepl_helparticlecolumns (Procedure)
sp_MSrepl_helparticlermo (Procedure)
sp_MSrepl_snapshot_helparticlecolumns (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