Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSchangemergeschemaarticle

  No additional text.


Syntax

create procedure sys.sp_MSchangemergeschemaarticle (
    @pubid uniqueidentifier,
    @artid uniqueidentifier,
    @property sysname,
    @value nvarchar(2000)
    ) AS

    set nocount on

    /*
    ** No need to bump the compatibility level
    ** here as the compatibility level for
    ** a publication with a schema-only article
    ** is already 90
    */
    declare @schema_option   binary(8)
    declare @precmdid        tinyint
    declare @creation_script nvarchar(255)
    declare @statusid        tinyint
    declare @type            tinyint
    declare @source_object   sysname
    declare @valid_schema_options int

    /*
    ** Parameter Check: @property
    ** Check to make sure that @property is a valid property in
    ** dbo.sysmergeschemaarticles.
    */
    select @property = lower(@property collate SQL_Latin1_General_CP1_CS_AS)
    if @property NOT IN ('description',
                         'pre_creation_command',
                         'creation_script',
                         'status',
                         'schema_option',
                         'destination_owner',
                         'destination_object',
                         'processing_order')
    begin
        raiserror(21224, 16, -1, @property)
        return (1)
    end

    select @type = type, @source_object = object_name(objid)
      from dbo.sysmergeextendedarticlesview
     where pubid = @pubid
       and artid = @artid

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

    if @property = N'description'
    begin
        update dbo.sysmergeschemaarticles
           set description = @value
         where pubid = @pubid
           and artid = @artid

        if @@error <> 0
            return 1
    end
    else if @property = N'pre_creation_command'
    begin
        /*
        ** The value for the pre_creation_command
        ** property must be either 'none' or 'drop'
        */
        select @value = lower(@value collate SQL_Latin1_General_CP1_CS_AS)
        if @value not in (N'none', N'drop')
        begin
            raiserror(21223, 16, -1)
            return (1)
        end

        if @value = N'none'
            select @precmdid = 0
        else if @value = N'drop'
            select @precmdid = 1

        update dbo.sysmergeschemaarticles
           set pre_creation_command = @precmdid
         where pubid = @pubid
           and artid = @artid

        if @@error <> 0
            return (1)

    end
    else if @property = N'creation_script'
    begin
        if @value is NULL or @value = N''
        begin
            /*
            ** Existing schema option must not be
            ** 0x0000000000000000 if the creation_script
            ** path has to be set to null
            */

            select @schema_option = schema_option from dbo.sysmergeschemaarticles
            where pubid = @pubid and artid = @artid
/*
            if @schema_option = 0x0000000000000000
            begin
                raiserror(21218, 16, -1)
                return (1)
            end
*/

        end

        update dbo.sysmergeschemaarticles
           set creation_script = @value
         where pubid = @pubid
           and artid = @artid

        if @@error <> 0
            return (1)

    end
    else if @property = N'status'
    begin
        select @value = lower(@value collate SQL_Latin1_General_CP1_CS_AS)

        if @value not in ('active', 'unsynced')
        begin
            raiserror(20075, 16, -1)
            return (1)
        end

        if @value = N'unsynced'
            select @statusid = 1
        else if @value = N'active'
            select @statusid = 2

        update dbo.sysmergeschemaarticles
           set status = @statusid
         where pubid = @pubid
           and artid = @artid

        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

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

        create table #tab_changeschemaarticle (value varbinary(8) NULL)

        if @@error <> 0
        begin
            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

        update dbo.sysmergeschemaarticles
			set schema_option = @schema_option where pubid = @pubid and artid = @artid
			
        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
        select @value = rtrim(@value)
        update dbo.sysmergeschemaarticles
           set destination_owner = @value
         where pubid = @pubid
           and artid = @artid

        if @@error <> 0
            return (1)
    end
    else if @property = N'destination_object'
    begin
        select @value = rtrim(@value)
        if @value is null or @value = ''
        begin
            select @value = @source_object
        end
        update dbo.sysmergeschemaarticles
           set destination_object = @value
         where pubid = @pubid
           and artid = @artid
    end
    else if @property = 'processing_order'
    begin

        declare @processing_order int
        select @processing_order = convert(int, @value)

        update dbo.sysmergeschemaarticles set processing_order = @processing_order
        where artid = @artid and pubid = @pubid
        if @@ERROR<>0
            return 1
    end

    return (0)

 
Last revision 2008RTM
See also

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