Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSaddschemaarticle

  No additional text.


Syntax
create procedure sys.sp_MSaddschemaarticle
    @publication        sysname,        /* Name of the publciation */
    @article            sysname,        /* Name of the article */
    @source_object      nvarchar(517),  /* Qualified name of object to be replicated */
    @destination_object sysname,        /* Name of the object created on the subscriber */
    @type               tinyint,        /* Must be one of 0x20, 0x40, 060 or 0x80 */
    @creation_script    nvarchar(255),  /* custom creation script for the article */
    @description        nvarchar(255),  /* article description */
    @pre_creation_cmd   nvarchar(10),   /* must be 'none' or 'drop' */
    @schema_option      binary(8),
    @destination_owner  sysname,        /* owner of the article object on the subscriber */
    @status             tinyint,
    @artid              int OUTPUT
AS
    SET NOCOUNT ON
    DECLARE @retcode int

    DECLARE @source_owner           sysname
    DECLARE @object                 sysname
    DECLARE @pubid                  int
    DECLARE @source_objid           int
    DECLARE @pre_creation_cmdid     tinyint

    /* Variables for setting up RPC call to the Distributor */
    DECLARE @distproc               nvarchar(2000)
    DECLARE @distributor            sysname
    DECLARE @distributiondb         sysname
    DECLARE @dbname                 sysname
    DECLARE @valid_schema_options   int
    DECLARE @qualname               nvarchar(517)
    DECLARE @allow_initialize_from_backup bit

    SELECT @source_owner = PARSENAME(@source_object, 2)
    SELECT @object = PARSENAME(@source_object, 1)
    SELECT @allow_initialize_from_backup = 0

    /* Note that @article & @publication has been validated by sp_addarticle
       as non-null */

    /*
    ** Get the pubid of the publication
    */
    SELECT @pubid = pubid,
           @allow_initialize_from_backup = allow_initialize_from_backup
      FROM syspublications
     WHERE name = @publication

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

    /*
    **  Destination object name
    */
    IF @destination_object IS NULL
        SELECT @destination_object = @source_object

    /*
    ** Parameter check: @schema_option
    */
    -- Since only the lower 32 bits of @schema_option are
    -- currently used, the following check is sufficient.
    -- Note that @schema_option should have been padded out by now
    DECLARE @schema_option_lodword int
    SELECT @schema_option_lodword = fn_replgetbinary8lodword(@schema_option)
    IF @type = 0x40
    BEGIN

        SELECT @valid_schema_options = 0xC9F43171
        IF (@schema_option_lodword & ~@valid_schema_options) <> 0
        BEGIN
            RAISERROR (21229, 16, -1)
            RETURN (1)
        END
    END
    ELSE
    BEGIN
        SELECT @valid_schema_options = 0xFAC03021
        IF (@schema_option_lodword & ~@valid_schema_options) <> 0
        BEGIN
            RAISERROR (21222, 16, -1)
            RETURN (1)
        END
    END

    /*
    ** Parameter check: @pre_creation_command must be
    ** 'drop' (id = 1) or 'none' (id = 0)
    */
    SELECT @pre_creation_cmd = LOWER(@pre_creation_cmd collate SQL_Latin1_General_CP1_CS_AS)
    IF @pre_creation_cmd NOT IN (N'none', N'drop')
    BEGIN
        RAISERROR(21223, 16, -1)
        RETURN (1)
    END

    IF @pre_creation_cmd = N'none'
        SELECT @pre_creation_cmdid = 0
    ELSE IF @pre_creation_cmd = N'drop'
        SELECT @pre_creation_cmdid = 1


    /*
    ** 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

    SELECT @source_objid = OBJECT_ID(@source_object)
    /*
    ** If the publication allows 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(@source_objid, 'SchemaId'))
        END

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

        -- Destination owner/object name cannot differ from source object/owner
        -- name (Restriction temporarily lifted)
--        IF @destination_object <> OBJECT_NAME(@source_objid) or
--           @destination_owner <> schema_name(OBJECTPROPERTY(@source_objid, 'SchemaId'))
--        BEGIN
--            RAISERROR (18791, 16, -1)
--            RETURN (1)
--        END
    END

    BEGIN TRANSACTION
    SAVE TRANSACTION sp_MSaddschemaarticle

    /*
    ** Get the source object id
    */
    IF NOT (@source_objid IS NULL)
    BEGIN
        EXEC %%Object(MultiName = @source_object).LockMatchID(ID = @source_objid, Exclusive = 1, BindInternal = 0)
        IF @@ERROR <> 0
            SELECT @source_objid = NULL
    END

    IF @source_objid IS NULL
    BEGIN
        RAISERROR(15052, -1, -1, @source_object)
        GOTO Failure
    END

    /*
    ** Parameter check: @type
    ** @type must correspond to the object type of the source object
    **
    ** @type = 0x20 => source object type = 'P', 'PC'
    ** @type = 0x40 => source object type = 'V'
    ** @type = 0x60 => source object type = 'AF'
    ** @type = 0xA0 => source object type = 'SN'
    ** @type = 0x80 => source object type = 'FN' OR 'TF' OR 'IF' OR 'FC' OR 'FT'
    */
    IF @type = 0x20
    BEGIN
        IF NOT EXISTS (SELECT *
                         FROM sys.objects
                        WHERE object_id = @source_objid
                          AND type in ('P ', 'PC'))
        BEGIN
            RAISERROR(21219, 16, -1)
            GOTO Failure
        END
    END
    ELSE IF @type = 0x40
    BEGIN
        IF NOT EXISTS (SELECT *
                         FROM sys.objects
                        WHERE object_id = @source_objid
                          AND type = 'V ')
        BEGIN
            RAISERROR(21221, 16, -1)
            GOTO Failure
        END
    END
    ELSE IF @type = 0x60
    BEGIN
        IF NOT EXISTS (SELECT *
                         FROM sys.objects
                        WHERE object_id = @source_objid
                          AND type = 'AF')
        BEGIN
            RAISERROR(20810, 16, -1)
            GOTO Failure
        END
    END
    ELSE IF @type = 0xA0
    BEGIN
        IF NOT EXISTS (SELECT *
                         FROM sys.objects
                        WHERE object_id = @source_objid
                          AND type = 'SN')
        BEGIN
            RAISERROR(20812, 16, -1)
            GOTO Failure
        END
    END
    ELSE IF @type = 0x80
    BEGIN
        IF NOT EXISTS (SELECT *
                         FROM sys.objects
                        WHERE object_id = @source_objid
                          AND (type in ('FN','TF','IF', 'FS', 'FT')))
        BEGIN
            RAISERROR(21228, 16, -1)
            GOTO Failure
        END
    END


    -- Add a dummy record to sysarticles to reserve an artid
    INSERT sysarticles (dest_table, filter, name, objid, pubid,
        pre_creation_cmd, status, sync_objid, type)
        VALUES (@destination_object, N'', @article, @source_objid, @pubid,
                @pre_creation_cmdid, @status, @source_objid, @type)

    IF @@ERROR <> 0
        GOTO Failure

    SELECT @artid = @@IDENTITY

    -- Now that we have reserved an artid in sysarticles,
    -- we can remove the dummy record

    DELETE sysarticles WHERE artid = @artid AND pubid = @pubid

    IF @@ERROR <> 0
        GOTO Failure

    -- Insert a record into sysschemaarticles to represent this
    -- schema only article
    INSERT sysschemaarticles
        VALUES (@artid, @creation_script, @description, @destination_object,
                @article, @source_objid, @pubid, @pre_creation_cmdid, @status,
                @type, @schema_option, @destination_owner)

    IF @@ERROR <> 0
        GOTO Failure

    EXEC sys.sp_MSget_qualified_name @source_objid, @qualname OUTPUT
    IF @qualname IS NULL
        GOTO Failure

    EXEC %%Object(MultiName = @qualname).LockMatchID(ID = @source_objid, Exclusive = 1, BindInternal = 0)
    IF @@ERROR <> 0
        GOTO Failure

    -- Make a bit in replinfo to prevent the source object from
    -- being dropped
    EXEC %%Object(ID = @source_objid).SetSchemaPublished(Value = 1)
    IF @@ERROR <> 0
        GOTO Failure

    -- Increment publication's min. autonosync lsn
    IF @allow_initialize_from_backup = 1
    BEGIN
        EXEC @retcode = sys.sp_MSincrementpublicationminautonosynclsn
                @publication = @publication
        IF @@ERROR <> 0 OR @retcode <> 0 BEGIN SELECT @retcode = 1 GOTO Failure END
    END

    COMMIT TRANSACTION

    RETURN (0)

Failure:
    ROLLBACK TRANSACTION sp_MSaddschemaarticle
    COMMIT TRANSACTION
    RETURN (1)

 
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