Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSchangeschemaarticle

  No additional text.


Syntax
create procedure sys.sp_MSchangeschemaarticle (
    @pubid int,
    @artid int,
    @property sysname,
    @value nvarchar(255)
    ) AS
    SET NOCOUNT ON
    DECLARE @retcode int
    DECLARE @pre_creation_cmdid tinyint
    DECLARE @statusid int
    DECLARE @schema_option_table_created bit
    DECLARE @creation_script nvarchar(255)
    DECLARE @type tinyint
    DECLARE @schema_option binary(8)
    DECLARE @valid_schema_options int

    SELECT @type = type
      FROM sysextendedarticlesview
     WHERE artid = @artid
       AND pubid = @pubid

    SELECT @schema_option_table_created = 0

    /*
    ** The pubid and artid passed into this procedure from sp_changearticle
    ** have to be valid by now.
    */

    /*
    ** Parameter check: @property
    */
    SELECT @property = LOWER(@property collate SQL_Latin1_General_CP1_CS_AS)
    IF @property NOT IN ('description',
                         'dest_object',
                         'creation_script',
                         'pre_creation_cmd',
                         'schema_option',
                         'destination_owner')
    BEGIN
        RAISERROR(21224, 16, -1, @property)
        RETURN (1)
    END

    -- Since all property changes will take the form of
    -- simple update stataments, no transaction will be
    -- started.

    IF @property = N'description'
    BEGIN
        UPDATE sysschemaarticles
           SET description = @value
         WHERE artid = @artid
           AND pubid = @pubid

        IF @@ERROR <> 0
            RETURN (1)
    END
    ELSE IF @property = N'dest_object'
    BEGIN
        UPDATE sysschemaarticles
           SET dest_object = @value
         WHERE artid = @artid
           AND pubid = @pubid

        IF @@ERROR <> 0
            RETURN (1)
    END
    ELSE IF @property = N'creation_script'
    BEGIN
        UPDATE sysschemaarticles
           SET creation_script = @value
         WHERE artid = @artid
           AND pubid = @pubid

        IF @@ERROR <> 0
            RETURN (1)
    END
    ELSE IF @property = N'pre_creation_cmd'
    BEGIN
        /*
        ** Validate the given value for
        ** the property. It has to be either
        ** 'none' or 'drop' case-insensitive.
        */
        SELECT @value = LOWER(@value collate SQL_Latin1_General_CP1_CS_AS)
        IF @value NOT IN ('none', 'drop')
        BEGIN
            RAISERROR(21223, 16, -1)
            RETURN (1)
        END

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

        UPDATE sysschemaarticles
           SET pre_creation_cmd = @pre_creation_cmdid
         WHERE artid = @artid
           AND pubid = @pubid

        IF @@ERROR <> 0
            RETURN (1)

    END
    ELSE IF @property = N'schema_option'
    BEGIN

        IF @value IS NULL
        BEGIN
            RAISERROR(14146, 16,1)
            RETURN (1)
        END

        CREATE TABLE #tab_changeschemaarticle (value varbinary(8) NULL)
        IF @@ERROR <> 0
        BEGIN
           RETURN (1)
        END

		-- make sure that the value we are about to
		-- build a dyn sql stmt with is a valid hexstr
		IF (SELECT sys.fn_isvalidhexstr(@value)) <> 1
		BEGIN
			RAISERROR(20014, 16, -1)
			RETURN (1)
		END
		
        EXEC ('insert #tab_changeschemaarticle values (' + @value + ')')
        IF @@ERROR <> 0
        BEGIN
            DROP TABLE #tab_changeschemaarticle
            RETURN (1)
        END

        SELECT @schema_option = fn_replprepadbinary8(value)
          FROM #tab_changeschemaarticle


        DECLARE @schema_option_lodword int
        SELECT @schema_option_lodword = sys.fn_replgetbinary8lodword(@schema_option)
        DECLARE @schema_option_hidword int
        SELECT @schema_option_hidword = sys.fn_replgetbinary8hidword(@schema_option)

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

        IF EXISTS (SELECT * FROM #tab_changeschemaarticle
                    WHERE value = 0x0000000000000000)
        BEGIN

            SELECT @creation_script = NULL
            SELECT @creation_script = creation_script
              FROM sysschemaarticles
             WHERE artid = @artid
               AND pubid = @pubid
/*
            IF @creation_script IS NULL OR
               @creation_script = N''
            BEGIN
                RAISERROR(21218, 16, -1)
                DROP TABLE #tab_changeschemaarticle
                RETURN (1)
            END
*/
        END

        UPDATE sysschemaarticles
           SET schema_option = @schema_option
          FROM #tab_changeschemaarticle tab
         WHERE artid = @artid
           AND pubid = @pubid

        IF @@ERROR <> 0
        BEGIN
            DROP TABLE #tab_changeschemaarticle
            RETURN (1)
        END

        DROP TABLE #tab_changeschemaarticle

        IF @@ERROR <> 0
            RETURN (1)
    END
    ELSE IF @property = N'destination_owner'
    BEGIN

        UPDATE sysschemaarticles
           SET dest_owner = @value
         WHERE artid = @artid
           AND pubid = @pubid

        IF @@ERROR <> 0
            RETURN (1)
    END

    RAISERROR (14025, 10, -1)
    RETURN (0)

 
Last revision 2008RTM
See also

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