Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSaddmergeschemaarticle

  No additional text.


Syntax

create procedure sys.sp_MSaddmergeschemaarticle
    @pubid                              uniqueidentifier,
    @article                            sysname,
    @source_object                      sysname,
    @type                               tinyint,
    @description                        nvarchar(255),
    @status                             nvarchar(10),
    @pre_creation_command               int,
    @creation_script                    nvarchar(255),
    @source_owner                       sysname,
    @destination_owner                  sysname,
    @schema_option                      binary(8),
    @destination_object                 sysname,
    @qualified_name                     nvarchar(270),
    @publication                        sysname,
    @snapshot_ready                     int,
    @force_invalidate_snapshot          bit,
    @processing_order                   int
AS
    SET NOCOUNT ON

    DECLARE @retcode              int
    DECLARE @objid                int
    DECLARE @artid                uniqueidentifier
    DECLARE @bInTran              bit
    DECLARE @valid_schema_options int
    DECLARE @bump_to_80           bit
    DECLARE @qualname             nvarchar(517)
    declare @backward_comp_level  int

    SELECT @bInTran = 0
    SELECT @objid = OBJECT_ID(@qualified_name)
    -- We are going to bump the compatibility level anyway
    -- since we are adding schema only articles
    -- check if we need to bump up first
    select @backward_comp_level = backward_comp_level from dbo.sysmergepublications where pubid = @pubid
    if @backward_comp_level < 40
    begin
        SELECT @bump_to_80 = 1
        RAISERROR(21391, 10, -1, @publication)
    end

    /*
    ** Parameter check: Source object type must match
    ** specified article type.
    **
    */
    -- Type has to be one of 0x20, 0x40, 0xA0 or 0x80 by the time
    -- this sp is called
    IF @type = 0x20
    BEGIN
        IF NOT EXISTS (SELECT *
                         FROM sys.objects
                        WHERE object_id = @objid
                          AND type IN ('P','PC'))
        BEGIN
            RAISERROR(21219, 16, -1)
            RETURN (1)
        END

    END
    ELSE IF @type = 0x40
    BEGIN
        IF NOT EXISTS (SELECT *
                         FROM sys.objects
                        WHERE object_id = @objid
                          AND type = 'V ')
        BEGIN
            RAISERROR(21221, 16, -1)
            RETURN (1)
        END
    END
    ELSE IF @type = 0x80
    BEGIN
        IF NOT EXISTS (SELECT *
                         FROM sys.objects
                        WHERE object_id = @objid
                          AND type IN ('FN','TF','IF','FS'))
        BEGIN
            RAISERROR(21228, 16, -1)
            RETURN (1)
        END
    END
    ELSE IF @type = 0xA0
    BEGIN
        IF NOT EXISTS (SELECT *
                         FROM sys.objects
                        WHERE object_id = @objid
                          AND type = 'SN')
        BEGIN
            RAISERROR(20812, 16, -1)
            RETURN (1)
        END
    END

    -- 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 by now.
    DECLARE @schema_option_lodword int
    SELECT @schema_option_lodword = fn_replgetbinary8lodword(@schema_option)
    IF @type = 0x40
    BEGIN
        SELECT @valid_schema_options = 0xC9F43151
        IF (@schema_option_lodword & ~@valid_schema_options) <> 0
        BEGIN
            RAISERROR (21229, 16, -1)
            RETURN (1)
        END
    END
    ELSE
    BEGIN
        SELECT @valid_schema_options = 0xFAC03001
        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)
    */
    IF @pre_creation_command NOT IN (0, 1)
    BEGIN
        RAISERROR(21223, 16, -1)
        RETURN (1)
    END

    -- Reuse @artid from articles publishing the same object so we can
    -- link these articles together during the reinit/republishing scenario

    SELECT @artid = NULL
    SELECT @artid = artid
      FROM dbo.sysmergeschemaarticles
     WHERE objid = @objid

    IF @artid IS NULL
    BEGIN
        SELECT @artid = newid()
    END

    BEGIN TRAN
    SAVE TRANSACTION sp_MSaddmergeschemaarticle
    SELECT @bInTran = 1

    IF @snapshot_ready > 0
    BEGIN
        IF @force_invalidate_snapshot = 0 and @snapshot_ready = 1
        BEGIN
            RAISERROR(21364, 16, -1, @article)
            GOTO FAILURE
        END
        if @backward_comp_level < 40
        begin
            RAISERROR(21360, 10, -1, @publication)
            SELECT @bump_to_80 = 1
        end
        UPDATE dbo.sysmergepublications
           SET snapshot_ready=2
         WHERE pubid=@pubid
        IF @@ERROR<>0
            GOTO FAILURE
    END

    INSERT dbo.sysmergeschemaarticles (name, type, objid, artid, description,
        pre_creation_command, pubid, status, creation_script, schema_option,
        destination_object, destination_owner, processing_order)
    VALUES (@article, @type, @objid, @artid, @description, @pre_creation_command,
        @pubid, 1, @creation_script, @schema_option, @destination_object,
        @destination_owner, @processing_order)

    IF @@ERROR <> 0
        GOTO FAILURE

    EXEC sys.sp_MSget_qualified_name @objid, @qualname OUTPUT
    IF @qualname IS NULL
        GOTO FAILURE

    EXEC %%Object(MultiName = @qualname).LockMatchID(ID = @objid, Exclusive = 1, BindInternal = 0)
    --EXEC %%Object(MultiName = @qualname).LockExclusiveMatchID(ID = @objid)
    IF @@ERROR <> 0
        GOTO FAILURE

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

    IF @bump_to_80=1
    BEGIN
        EXEC @retcode = sys.sp_MSBumpupCompLevel @pubid, 40
        IF @@ERROR<>0 or @retcode<>0
            GOTO FAILURE
    END

    COMMIT TRANSACTION

    RETURN (0)

FAILURE:

    IF @bInTran = 1
    BEGIN
        ROLLBACK TRANSACTION sp_MSaddmergeschemaarticle
        COMMIT TRANSACTION
    END
    RETURN (1)

 
Last revision 2008RTM
See also

  sp_addmergearticle (Procedure)
sp_MSdrop_rladmin (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