Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSaddexecarticle

  No additional text.


Syntax
create procedure sys.sp_MSaddexecarticle
    @publication sysname,                    /* publication name */
    @article     sysname,                    /* article name */
    @source_proc nvarchar (92),              /* table name */
    @destination_proc sysname = NULL,        /* destination table name */
    @type sysname = NULL,                    /* article type */
    @creation_script nvarchar (255) = NULL,  /* article schema script */
    @description nvarchar (255) = NULL,      /* article description */
    @pre_creation_cmd nvarchar(10) = 'drop', /* 'none', 'drop', 'delete', 'truncate' */
    @schema_option binary(8) = NULL,         /* script out stored procedure */
    @destination_owner sysname,
    @article_id int OUTPUT

    AS


    SET NOCOUNT ON

    /* variables for SP_NAMECRACK */

    DECLARE @site sysname
    DECLARE @db sysname
    DECLARE @owner sysname
    DECLARE @object sysname

    DECLARE @retcode   int

    DECLARE @procid    int
    DECLARE @procnum   smallint
    DECLARE @pubid     int
    DECLARE @precmdid  int

    DECLARE @typeid      smallint
    DECLARE @publish_bit smallint
    DECLARE @unexpected_serializable_setting bit

    DECLARE @in_tran bit
    DECLARE @qualname nvarchar(517)
    DECLARE @allow_initialize_from_backup bit

    SELECT  @typeid      = 24


    SELECT @publish_bit = 1
    SELECT @retcode = 0
    SELECT @in_tran = 0
    SELECT @allow_initialize_from_backup = 0

    /*
    ** Parameter Check: @article.
    ** The @article name cannot be NULL and must conform to the rules
    ** for identifiers.
    */

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

    /*
    EXECUTE @retcode = sys.sp_validname @article

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

    if LOWER(@article) = 'all'
        BEGIN
            RAISERROR (14032, 16, -1, '@article')
            RETURN (1)
        END

    /*
    ** Parameter Check: @publication.
    ** The @publication name cannot be NULL and must conform to the rules
    ** for identifiers.
    */

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

    EXECUTE @retcode = sys.sp_validname @publication

    IF @retcode <> 0
    RETURN (1)

    /*
    ** Parameter Check: @source_proc.
    ** Check to see that the @source_proc is local, that it conforms
    ** to the rules for identifiers, and that it is a procedure
    */

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

   select @object = PARSENAME( @source_proc, 1 )
   select @owner  = PARSENAME( @source_proc, 2 )
   select @db     = PARSENAME( @source_proc, 3 )
   select @site   = PARSENAME( @source_proc, 4 )

   if @object IS NULL
         return 1


    -- LIKE N'%.%.%' doesn't guarantee that the object name
    -- is a 3-part name as . can be embedded in a quoted identifier.
    IF @source_proc LIKE '%.%.%' AND ISNULL(@db, DB_NAME()) <> DB_NAME()
    BEGIN
        RAISERROR (14004, 16, -1, @source_proc)
        RETURN (1)
    END


    /*
    **  Get the id of the @source_proc
    */

    SELECT @procid = object_id
      FROM sys.objects
     WHERE object_id = OBJECT_ID(@source_proc)
     AND   type in ('P', 'PC')

    IF @procid IS NULL
    BEGIN
        RAISERROR (14027, 11, -1, @source_proc)
        RETURN (1)
    END

    /*
    ** Parameter Check:  @destination_proc.
    ** If the destination proc is not specified, assume it's the same
    ** as the source.
    */

    IF @destination_proc IS NULL
    BEGIN
        -- Perform parsing only if the destination_proc parameter is not provided
        SELECT @destination_proc = @source_proc

        select @object = PARSENAME( @destination_proc, 1 )
        select @owner  = PARSENAME( @destination_proc, 2 )
        select @db     = PARSENAME( @destination_proc, 3 )
        select @site   = PARSENAME( @destination_proc, 4 )

        if @object IS NULL
             return 1
    END

    /*
    ** Get the pubid.
    */

    SELECT @pubid = pubid,
           @allow_initialize_from_backup = allow_initialize_from_backup
      FROM dbo.syspublications
     WHERE name = @publication

    IF @pubid IS NULL
        BEGIN
            RAISERROR (14027, 11, -1, @publication)
            RETURN (1)
        END


    /*
    ** Parameter Check:  @article, @publication.
    ** Check if the article already exists in this publication.
    */

    IF EXISTS (SELECT *
                 FROM sysextendedarticlesview
                WHERE pubid = @pubid
                  AND name = @article)
        BEGIN
            RAISERROR (14030, 16, -1, @article, @publication)
            RETURN (1)
        END

    IF EXISTS (SELECT *
                 FROM sys.parameters
                WHERE object_id = @procid
                  AND system_type_id = 243)
        BEGIN
            -- Stored procedures containing table-value parameters cannot be published as '[serializable] proc exec' articles.
            RAISERROR (25023, 16, -1)
            RETURN (1)
        END


    /*
    ** Set the precmdid.  The default type is 'drop'.
    **
    **      @precmdid   pre_creation_cmd
    **      =========   ================
    **            0     none
    **          1     drop
    */
    IF LOWER(@pre_creation_cmd collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('none', 'drop')
       BEGIN
          RAISERROR (14111, 16, -1)
          RETURN (1)
       END

    /*
    ** Determine the integer value for the pre_creation_cmd.
    */

    IF LOWER(@pre_creation_cmd collate SQL_Latin1_General_CP1_CS_AS) = 'none'
       SELECT @precmdid = 0
    ELSE IF LOWER(@pre_creation_cmd collate SQL_Latin1_General_CP1_CS_AS) = 'drop'
       SELECT @precmdid = 1

    /*  Determine 'type' value for article.
    **
    **            8     proc exec
    **           24     serializable proc exec
    */

    IF @type IS NULL
    BEGIN
        SELECT @type = 'serializable proc exec'
    END
    ELSE IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('proc exec', 'serializable proc exec')
    BEGIN
            RAISERROR (14118, 16, -1)
            RETURN (1)
    END

    IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) = 'proc exec'
    BEGIN
       SELECT @typeid = 8
       SELECT @unexpected_serializable_setting = 1
    END
    ELSE IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) = 'serializable proc exec'
    BEGIN
       SELECT @typeid = 24
       SELECT @unexpected_serializable_setting = 0
    END

    -- make sure we haven't already created an article of a different type
    -- on this proc

    IF EXISTS ( SELECT * FROM sys.procedures
                 WHERE object_id = @procid
                   AND is_execution_replicated = 1 -- This bit is set for both 'proc exec' and 'serializable proc exec'
                   AND is_repl_serializable_only = @unexpected_serializable_setting )
    BEGIN
       RAISERROR (21024, 16, -1, @source_proc )
       RETURN(1)
    END

    /*
    ** Parameter Check:  @creation_script and @schema_option
    ** @schema_option is null, set the default value
    */
    IF @schema_option IS NULL
    BEGIN
        SELECT @schema_option = 1
    /*
        RAISERROR (14043, 16, -1, '@schema_option', 'sp_MSaddexecarticle')
        RETURN (1)
        */
    END

    SET @schema_option = sys.fn_replprepadbinary8(@schema_option)
    DECLARE @valid_schema_options int
    SET @valid_schema_options = 0xFAC03021
    DECLARE @schema_option_lodword int
    SET @schema_option_lodword = fn_replgetbinary8lodword(@schema_option)

    IF (@schema_option & ~@valid_schema_options) <> 0
    BEGIN
        RAISERROR (20014, 10, -1)
        RETURN (1)
    END

    /*
    ** If the publication is enabled for autonosync, we need to impose
    ** a number of additional restrictions.
    */
    IF @allow_initialize_from_backup = 1
    BEGIN
        -- If destination owner is null or empty, explicitly set it to be the
        -- same as the source owner.
        IF @destination_owner IS NULL or RTRIM(@destination_owner) = N''
        BEGIN
            SELECT @destination_owner = schema_name(OBJECTPROPERTY(@procid, 'SchemaId'))
        END

        -- If destination object (proc) name is null or empty, explicitly set it
        -- to be the same as the source object (proc) name
        IF @destination_proc IS NULL or RTRIM(@destination_proc) = N''
        BEGIN
            SELECT @destination_proc = OBJECT_NAME(@procid)
        END

        -- Destination owner/object name cannot differ from source object/owner
        -- name (Restriction temporarily lifted)
--        IF @destination_proc <> OBJECT_NAME(@procid) or
--           @destination_owner <> schema_name(OBJECTPROPERTY(@procid, 'SchemaId'))
--        BEGIN
--            RAISERROR (18791, 16, -1)
--            RETURN (1)
--        END
        -- Article type must be serializable proc exec
        -- Restriction (temporarily lifted)
--        IF @typeid <> 24
--        BEGIN
--            RAISERROR (18793, 16, -1)
--            RETURN (1)
--        END
    END

    /*
    **  Add article to sysarticles and update sys.objects category bit.
    */

    begin tran
    save TRAN sp_MSaddexecarticle
        INSERT sysarticles (creation_script, del_cmd, description,
                            dest_table, filter, filter_clause, ins_cmd, name,
                objid, pre_creation_cmd, pubid,
                            status, sync_objid, type, upd_cmd, schema_option,
                            dest_owner)
        VALUES (@creation_script, NULL, @description,
                @destination_proc, 0, '', NULL, @article,
                @procid, @precmdid, @pubid,
                case when @allow_initialize_from_backup = 1 then 1 else 0 end
                , @procid, @typeid, NULL, @schema_option,
                @destination_owner)

        IF @@ERROR <> 0
        BEGIN
            SELECT @retcode = 1
            GOTO FAILURE
        END

        SELECT @article_id = @@IDENTITY

        EXEC sys.sp_MSget_qualified_name @procid, @qualname OUTPUT
        IF @qualname IS NULL
        BEGIN
            SELECT @retcode = 1
            GOTO FAILURE
        END

        EXEC %%Object(MultiName = @qualname).LockMatchID(ID = @procid, Exclusive = 1, BindInternal = 0)
        IF @@ERROR <> 0
        BEGIN
            SELECT @retcode = 1
            GOTO FAILURE
        END

        EXEC %%Object(ID = @procid).SetPublished(Value = 1)
        IF @@ERROR <> 0
        BEGIN
            SELECT @retcode = 1
            GOTO FAILURE
        END

        -- If the publication is enabled for autonosync, turn on the
        -- 'replicated' bit of the stored procedure now. We will also
        -- need to increment the publication's min. subscription backup
        -- lsn.
        IF @allow_initialize_from_backup = 1
        BEGIN

            -- The following @typeid checks are not really needed if we
            -- consider the fact that only 'serializable proc exec' type
            -- articles are allowed for backup sub. publications
            -- (see preceding check), but the presence of the following
            -- checks will make the code more resilient to change of
            -- requirements. (Now that the check is temporarily removed,
            -- the preceding statement is not true anymore.)

            IF (@typeid &  8) = 8
                EXEC %%Module(ID = @procid).SetProcReplicated(Value = 1)
            IF (@typeid &  16) = 16
                EXEC %%Module(ID = @procid).SetProcReplSerialOnly(Value = 1)

            -- Activate the article immediately and flush the article cache,
            -- This way, all executions of this procedure starting from
            -- now will be tracked and replicated to the distribution database
            UPDATE dbo.sysarticles
               SET status = status | 1
             WHERE artid = @article_id
            IF @@ERROR <> 0 OR @retcode <> 0 BEGIN SELECT @retcode = 1 GOTO FAILURE END

            EXEC @retcode = sys.sp_replflush
            IF @@ERROR <> 0 OR @retcode <> 0 BEGIN SELECT @retcode = 1 GOTO FAILURE END

            -- Increment the min subscription autonosync lsn of the publication
            EXEC @retcode = sys.sp_MSincrementpublicationminautonosynclsn
                    @publication = @publication
            IF @@ERROR <> 0 OR @retcode <> 0 BEGIN SELECT @retcode = 1 GOTO FAILURE END

        END


    COMMIT TRAN

FAILURE:

    IF @in_tran <> 0 AND @retcode <> 0
    BEGIN
        ROLLBACK TRAN sp_MSaddexecarticle
        COMMIT TRAN
    END

    RETURN @retcode

 
Last revision 2008RTM
See also

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