Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSenumschemachange_100

  No additional text.


Syntax
create procedure sys.sp_MSenumschemachange_100(
    @pubid                 uniqueidentifier,
    @AlterTableOnly        int,
    @schemaversion         int,
    @invalidateupload_schemachanges_for_ssce bit = 0,
    @filter_skipped_schemachanges bit = 0 -- do not return schema changes with schemastatus = 2
    )
as
begin
    set nocount on
    /*
    ** To public
    */
    declare @SCHEMA_TYPE_ALTERTABLE                     int
    declare @SCHEMA_TYPE_REINITALL                      int
    declare @SCHEMA_TYPE_NONSQLALTERTABLE       int
    declare @SCHEMA_TYPE_REINITWITHUPLOAD       int
    declare @SCHEMA_TYPE_DDL_ACTIONS            int
    declare @SCHEMA_TYPE_DDL_RELATED_COLUMN_FIXUP int
    declare @SCHEMA_TYPE_ARTICLEPROCESSINGORDER     int
    declare @SCHEMA_TYPE_DROPARTICLE        int
    declare @SCHEMA_TYPE_USER_DEFINED_DATA_TYPE     int
    declare @SCHEMA_TYPE_CLR_USER_DEFINED_DATA_TYPE int
    declare @SCHEMA_TYPE_ASSEMBLY               int
    declare @SCHEMA_TYPE_PARTITIONSCHEME    int
    declare @SCHEMA_TYPE_PARTITIONFUNCTION  int
    declare @SCHEMA_TYPE_XMLSCHEMANAMESPACE int
    declare @SCHEMA_TYPE_FULLTEXTCATALOG        int
    declare @SCHEMA_TYPE_SYNONYM            int
    declare @SCHEMA_TYPE_USER_SCHEMA            int
    declare @SCHEMA_TYPE_USER_DEFINED_TABLE_TYPE     int
    declare @schemaversion_of_snapshottrailer int
    declare @desired_schema_status tinyint

    set @SCHEMA_TYPE_ALTERTABLE                 = 11
    set @SCHEMA_TYPE_REINITALL                  = 12
    set @SCHEMA_TYPE_NONSQLALTERTABLE           = 13
    set @SCHEMA_TYPE_REINITWITHUPLOAD           = 14
    set @SCHEMA_TYPE_ARTICLEPROCESSINGORDER     = 22
    set @SCHEMA_TYPE_DROPARTICLE                = 28
    set @SCHEMA_TYPE_USER_DEFINED_DATA_TYPE     = 88
    set @SCHEMA_TYPE_CLR_USER_DEFINED_DATA_TYPE = 89
    set @SCHEMA_TYPE_ASSEMBLY                   = 90
    set @SCHEMA_TYPE_PARTITIONSCHEME            = 91
    set @SCHEMA_TYPE_PARTITIONFUNCTION          = 92
    set @SCHEMA_TYPE_XMLSCHEMANAMESPACE         = 93
    set @SCHEMA_TYPE_FULLTEXTCATALOG            = 96
    set @SCHEMA_TYPE_SYNONYM                    = 100
    set @SCHEMA_TYPE_DDL_ACTIONS                = 300
    set @SCHEMA_TYPE_DDL_RELATED_COLUMN_FIXUP   = 301
    set @SCHEMA_TYPE_USER_SCHEMA                = 31
    set @SCHEMA_TYPE_USER_DEFINED_TABLE_TYPE             = 105

    if (@schemaversion is null)
    begin
        RAISERROR(14043, 16, -1, '@schemaversion', 'sp_MSenumschemachange_90')
        return (1)
    end

        --  @schema_needed = 0 - only send back reinitall command, if any
        --  @schema_needed = 1 - normal enumeration
        --  @schema_needed = 2 - only send back alter-table command, if any
        --  @schema_needed = 3 - only send back reinitall-with-upload command, if any.

    if (@AlterTableOnly = 1)
    begin
        if 0=@invalidateupload_schemachanges_for_ssce
        begin
            select pubid, artid, schemaversion, schemaguid, schematype, schematext
                from dbo.sysmergeschemachange
                where pubid=@pubid and
                      schemaversion > @schemaversion and
                      schematype in (
                                        @SCHEMA_TYPE_ALTERTABLE,
                                        @SCHEMA_TYPE_NONSQLALTERTABLE,
                                        @SCHEMA_TYPE_DDL_ACTIONS,
                                        @SCHEMA_TYPE_DDL_RELATED_COLUMN_FIXUP,
                                        @SCHEMA_TYPE_DROPARTICLE
                                    )
                       and schemastatus < case @filter_skipped_schemachanges when 1 then 2 else 3 end
        end
        else
        begin
            select pubid, artid, schemaversion, schemaguid, schematype, schematext
                from dbo.sysmergeschemachange
                where pubid=@pubid and
                      schemaversion > @schemaversion and
                      schematype in (
                                        @SCHEMA_TYPE_ALTERTABLE,
                                        @SCHEMA_TYPE_NONSQLALTERTABLE,
                                        @SCHEMA_TYPE_DDL_ACTIONS,
                                        @SCHEMA_TYPE_DDL_RELATED_COLUMN_FIXUP,
                                        @SCHEMA_TYPE_ARTICLEPROCESSINGORDER,

                                        -- SCHEMA_TYPE_DROPARTICLE is here because an article might have
                                        -- been dropped, DDL on the article happened, then article was
                                        -- readded. Thus, SSCE upload needs to be rejected, as otherwise
                                        -- it might contain DML based on old schema.
                                        @SCHEMA_TYPE_DROPARTICLE
                                    )
                       and schemastatus < case @filter_skipped_schemachanges when 1 then 2 else 3 end
        end

        return (0)
    end

    if exists (select * from dbo.sysmergeschemachange where
        pubid=@pubid and ((schemaversion > @schemaversion and schematype = @SCHEMA_TYPE_REINITALL)
        or (schemaversion > @schemaversion and schematype = @SCHEMA_TYPE_REINITWITHUPLOAD)))
    begin
        select @schemaversion=0
    end


    if @filter_skipped_schemachanges = 1
        select @desired_schema_status = 2
    else
        select @desired_schema_status = 3

    if (@schemaversion > 0)
    begin
        -- Subscriber has already received the snapshot so filter out
        -- the pre/post-snapshot commands.
        -- If @schemaversion > 0, there are two main cases that we need to
        -- consider:
        -- i) There are incremental article commands such as bcp/schema
        --    commands that need to be applied within a "fake" snapshot
        --    boundary
        -- ii) There is no incremental article commands, schema changes can be
        --     applied "normally"
        declare @min_incremental_schemaversion int
        declare @max_incremental_schemaversion int

        select @min_incremental_schemaversion = null

        -- min and max should be different because with min we don't look at schema type 48 (*.prc files)
        -- whereas with max we should include that.
            -- normal snapshot commands excluding pre/post snapshot commands
            -- and system tables commands
            -- the incremental add article commands will have a non null artid. The below clause is to avoid incorrect
            -- application of snapshot on upgrade since yukon has new 90 schemachange commands.
        select @min_incremental_schemaversion = min(schemaversion)
          from dbo.sysmergeschemachange where schemaversion > @schemaversion
           and pubid = @pubid
           and schematype in (2, 3, 4, 61, 62, 63, 64, 131, 132, 10, 15, 65, 88, 89, 90, 91, 92, 93, 96, 100, 31, 105)
           and (artid is NULL or artid in (select artid from dbo.sysmergearticles where pubid = @pubid)
                                     or artid in (select artid from dbo.sysmergeschemaarticles where pubid = @pubid))

        select @max_incremental_schemaversion = max(schemaversion)
          from dbo.sysmergeschemachange where schemaversion > @schemaversion
           and pubid = @pubid
           and schematype in (2, 3, 4, 61, 62, 63, 64, 131, 132, 10, 15, 65, 88, 89, 90, 91, 92, 93, 96, 100, 31, 48, 105)
           and (artid is NULL or artid in (select artid from dbo.sysmergearticles where pubid = @pubid)
                                    or artid in (select artid from dbo.sysmergeschemaarticles where pubid = @pubid))

        if @min_incremental_schemaversion is not null
        begin

            -- case i), we have incrementally added articles

            -- Need to save off the schemaguid of the max incremental change
            -- for labelling the trailer. This is to prevent the schema
            -- validation logic from raising false alarm saying that the
            -- publisher is restored from a backup
            declare @max_incremental_schemaguid uniqueidentifier
            select @max_incremental_schemaguid = schemaguid
              from dbo.sysmergeschemachange
             where schemaversion = @max_incremental_schemaversion
               and pubid = @pubid

            declare @incremental_schematype_bit int
            select @incremental_schematype_bit = 1024


            select pubid, artid, schemaversion, schemaguid, schematype,  schematext, schemastatus
            from
            (
            -- Insert every thing before @min_incremental_schemaversion but
            -- after @schemaversion
            -- Note: Excluded list should include all snapshot header, trailer commands
            select 10 as schema_type_group, pubid, artid, schemaversion, schemaguid, schematype,  schematext, schemastatus
              from dbo.sysmergeschemachange where schemaversion > @schemaversion
               and schemaversion < @min_incremental_schemaversion
               and pubid = @pubid
               and schematype not in (40, 45, 50, 51, 52, 53, 54, 55, 56, 57, 58, 25, 5, 6, 71, 80, 81, 82, 83, 84, 85)
               and schemastatus < @desired_schema_status
            -- Incremental snapshot header
            -- Header begins
            union all
            select 20 as schema_type_group, pubid, artid, @min_incremental_schemaversion, schemaguid, schematype | @incremental_schematype_bit,  schematext, schemastatus
              from dbo.sysmergeschemachange
             where pubid = @pubid
               and schematype = 50
               and schemastatus < @desired_schema_status

            -- Header content
            union all
            select 30 as schema_type_group, pubid, artid, @min_incremental_schemaversion, schemaguid, schematype | @incremental_schematype_bit,  schematext, schemastatus
              from dbo.sysmergeschemachange
             where pubid = @pubid
               and schematype in (25, 53, 54, 55, 56, 57, 58)
               and schemastatus < @desired_schema_status

            -- Header ends
            union all
            select 40 as schema_type_group, pubid, artid, @min_incremental_schemaversion, schemaguid, schematype | @incremental_schematype_bit,  schematext, schemastatus
             from dbo.sysmergeschemachange
            where pubid = @pubid
              and schematype = 51
               and schemastatus < @desired_schema_status

            -- Add everything between max and min and maintain ordering
            union all
            select 50 as schema_type_group, pubid, artid, schemaversion, schemaguid, schematype,  schematext, schemastatus
              from dbo.sysmergeschemachange where schemaversion >= @min_incremental_schemaversion
               and schemaversion <= @max_incremental_schemaversion
               and pubid = @pubid
               and schematype in (@SCHEMA_TYPE_USER_SCHEMA)
               and schemastatus < @desired_schema_status

            -- Add everything between max and min and maintain ordering
            union all
            select 55 as schema_type_group, pubid, artid, schemaversion, schemaguid, schematype,  schematext, schemastatus
              from dbo.sysmergeschemachange where schemaversion >= @min_incremental_schemaversion
               and schemaversion <= @max_incremental_schemaversion
               and pubid = @pubid
               and schematype in (@SCHEMA_TYPE_PARTITIONFUNCTION)
               and schemastatus < @desired_schema_status

            -- Add everything between max and min and maintain ordering
            union all
            select 60 as schema_type_group, pubid, artid, schemaversion, schemaguid, schematype,  schematext, schemastatus
              from dbo.sysmergeschemachange where schemaversion >= @min_incremental_schemaversion
               and schemaversion <= @max_incremental_schemaversion
               and pubid = @pubid
               and schematype in (@SCHEMA_TYPE_ASSEMBLY, @SCHEMA_TYPE_PARTITIONSCHEME)
               and schemastatus < @desired_schema_status

            -- Add everything between max and min and maintain ordering
            union all
            select 70 as schema_type_group, pubid, artid, schemaversion, schemaguid, schematype,  schematext, schemastatus
              from dbo.sysmergeschemachange where schemaversion >= @min_incremental_schemaversion
               and schemaversion <= @max_incremental_schemaversion
               and pubid = @pubid
               and schematype in (@SCHEMA_TYPE_USER_DEFINED_DATA_TYPE, @SCHEMA_TYPE_CLR_USER_DEFINED_DATA_TYPE, @SCHEMA_TYPE_XMLSCHEMANAMESPACE, @SCHEMA_TYPE_FULLTEXTCATALOG)
               and schemastatus < @desired_schema_status

		 -- Add everything between max and min and maintain ordering
		 -- UDTableType can contain udt or clr udt or xml datatype. Hence it should be enumerated after all of the above.
            union all
            select 75 as schema_type_group, pubid, artid, schemaversion, schemaguid, schematype,  schematext, schemastatus
              from dbo.sysmergeschemachange where schemaversion >= @min_incremental_schemaversion
               and schemaversion <= @max_incremental_schemaversion
               and pubid = @pubid
               and schematype in (@SCHEMA_TYPE_USER_DEFINED_TABLE_TYPE)
               and schemastatus < @desired_schema_status


            -- Add everything between max and min and filter out things
            -- we didn't want before in the normal case
            -- Note: Excluded list should include all snapshot header, trailer commands
            union all
            select 80 as schema_type_group, pubid, artid, schemaversion, schemaguid, schematype,  schematext, schemastatus
              from dbo.sysmergeschemachange where schemaversion >= @min_incremental_schemaversion
               and schemaversion <= @max_incremental_schemaversion
               and pubid = @pubid
               and schematype not in (40, 45, 50, 51, 52, 53, 54, 55, 56, 57, 58, 25, 5, 6, 71)
               and schemastatus < @desired_schema_status

            -- Incremental snapshot trailer
            union all
            select 90 as schema_type_group, pubid, artid, @max_incremental_schemaversion, @max_incremental_schemaguid, schematype | @incremental_schematype_bit,  schematext, schemastatus
              from dbo.sysmergeschemachange
             where pubid = @pubid
               and schematype = 52
               and schemastatus < @desired_schema_status

            -- Add everything after the incremental article commands
            -- Note: Excluded list should include all snapshot header, trailer commands
            union all
            select 100 as schema_type_group, pubid, artid, schemaversion, schemaguid, schematype,  schematext, schemastatus
              from dbo.sysmergeschemachange where schemaversion > @max_incremental_schemaversion
               and pubid = @pubid
               and schematype not in (40, 45, 50, 51, 52, 53, 54, 55, 56, 57, 58, 25, 5, 6, 71, 80, 81, 82, 83, 84, 85)
               and schemastatus < @desired_schema_status

            ) as incremental_schema_changes
            order by schema_type_group, schemaversion
        end
        else
        begin
            -- case ii)
            -- Also filter out the schemtypes for the setlastsentgen (5) and setlastrecgen (6)
            -- This ensures that the subscriber does not apply these schema changes when
    -- it applies incremental schema - ie the perf optimization that is implemented
            -- by setting last sent/rec generation should be done only for brand new subscriptions.
            -- Need to remove dynamic snapshot validation command too.
            -- Note: Excluded list should include all snapshot header, trailer commands
            select pubid, artid, schemaversion, schemaguid, schematype, schematext
              from dbo.sysmergeschemachange where schemaversion > @schemaversion
               and pubid = @pubid
               and schematype not in (40, 45, 50, 51, 52, 53, 54, 55, 56, 57, 58, 25, 5, 6, 71, 80, 81, 82, 83, 84, 85)
               -- filter out article or system table bcp commands. These can only be present if an incrementally
               -- added article was present. In that case it should have entered the above if block and should not get to
               -- this else block. The following is there to take care of the upgrade scenario.
               -- 17 is compute rec gen command which also be present only when applying the entire snapshot
               -- 48 is the file that contains the merge stored procedures, do not enumerate it
               and schematype not in (3, 4, 131, 132, 10, 15, 17, 48)
               and schemastatus < @desired_schema_status
           order by schemaversion
        end
    end
    Else
    begin
        declare @snapshot_trailer_schemaversion int

        select @schemaversion_of_snapshottrailer = min(schemaversion) from dbo.sysmergeschemachange
            where schemaversion > @schemaversion
            and pubid = @pubid
            and schematype = 52

        -- Subscriber requires a snapshot, so carefully sequence the
        -- pre/post-snapshot commands around the snapshot boundary

        select pubid, artid, schemaversion, schemaguid, schematype,  schematext
        from
        (
        -- select snapshot header
        -- Header begins
        select 10 as schema_type_group, pubid, artid, schemaversion, schemaguid, schematype,  schematext, schemastatus, 0 as processing_order
          from dbo.sysmergeschemachange where schemaversion > @schemaversion
           and pubid = @pubid
           and schematype = 50
           and schemaversion < @schemaversion_of_snapshottrailer
           and schemastatus < @desired_schema_status

        -- Header content
        union all
        select 20 as schema_type_group, pubid, artid, schemaversion, schemaguid, schematype,  schematext, schemastatus, 0 as processing_order
          from dbo.sysmergeschemachange where schemaversion > @schemaversion
           and pubid = @pubid
           and schematype in (25, 53, 54, 55, 56, 57, 58)
           and schemaversion < @schemaversion_of_snapshottrailer
           and schemastatus < @desired_schema_status

        -- Header ends
        union all
        select 30 as schema_type_group, pubid, artid, schemaversion, schemaguid, schematype,  schematext, schemastatus, 0 as processing_order
          from dbo.sysmergeschemachange where schemaversion > @schemaversion
           and pubid = @pubid
           and schematype = 51
           and schemaversion < @schemaversion_of_snapshottrailer
           and schemastatus < @desired_schema_status

        -- End of snapshot header
        -- Insert pre command
        union all
        select 40 as schema_type_group, pubid, artid, schemaversion, schemaguid, schematype,  schematext, schemastatus, 0 as processing_order
          from dbo.sysmergeschemachange where schemaversion > @schemaversion
           and pubid = @pubid
           and schematype = 40
           and schemaversion < @schemaversion_of_snapshottrailer
           and schemastatus < @desired_schema_status

        -- Dynamic snapshot validation token processing command, if there is
        -- one
        union all
        select 50 as schema_type_group, pubid, artid, schemaversion, schemaguid, schematype,  schematext, schemastatus, 0 as processing_order
          from dbo.sysmergeschemachange where schemaversion > @schemaversion
           and pubid = @pubid
           and schematype = 71
           and schemaversion < @schemaversion_of_snapshottrailer
           and schemastatus < @desired_schema_status

        -- insert the presnapshot script command
        union all
        select 60 as schema_type_group, pubid, artid, schemaversion, schemaguid, schematype,  schematext, schemastatus, 0 as processing_order
          from dbo.sysmergeschemachange where schemaversion > @schemaversion
           and pubid = @pubid
           and schematype in (60, 67)
           and schemastatus < @desired_schema_status

        -- insert the drop mergearticle command
        union all
        select 70 as schema_type_group, pubid, artid, schemaversion, schemaguid, schematype,  schematext, schemastatus, 0 as processing_order
          from dbo.sysmergeschemachange where schemaversion > @schemaversion
           and pubid = @pubid
           and schematype = @SCHEMA_TYPE_DROPARTICLE
           and schemastatus < @desired_schema_status

        -- Exclude pre-post, but include snapshot only commands
        union all
        select 80 as schema_type_group, pubid, artid, schemaversion, schemaguid, schematype,  schematext, schemastatus, 0 as processing_order
          from dbo.sysmergeschemachange where schemaversion > @schemaversion
           and pubid = @pubid
           and schematype in (@SCHEMA_TYPE_USER_SCHEMA)
           and schemaversion < @schemaversion_of_snapshottrailer
           and schemastatus < @desired_schema_status

        -- Exclude pre-post, but include snapshot only commands
        union all
        select 85 as schema_type_group, pubid, artid, schemaversion, schemaguid, schematype,  schematext, schemastatus, 0 as processing_order
          from dbo.sysmergeschemachange where schemaversion > @schemaversion
           and pubid = @pubid
           and schematype in (@SCHEMA_TYPE_PARTITIONFUNCTION)
           and schemaversion < @schemaversion_of_snapshottrailer
           and schemastatus < @desired_schema_status

        -- Exclude pre-post, but include snapshot only commands
        union all
        select 90 as schema_type_group, pubid, artid, schemaversion, schemaguid, schematype,  schematext, schemastatus, 0 as processing_order
          from dbo.sysmergeschemachange where schemaversion > @schemaversion
           and pubid = @pubid
           and schematype in (@SCHEMA_TYPE_ASSEMBLY, @SCHEMA_TYPE_PARTITIONSCHEME)
           and schemaversion < @schemaversion_of_snapshottrailer
           and schemastatus < @desired_schema_status

        -- Exclude pre-post, but include snapshot only commands
        union all
        select 100 as schema_type_group, pubid, artid, schemaversion, schemaguid, schematype,  schematext, schemastatus, 0 as processing_order
          from dbo.sysmergeschemachange where schemaversion > @schemaversion
           and pubid = @pubid
           and schematype in (@SCHEMA_TYPE_USER_DEFINED_DATA_TYPE, @SCHEMA_TYPE_CLR_USER_DEFINED_DATA_TYPE, @SCHEMA_TYPE_XMLSCHEMANAMESPACE, @SCHEMA_TYPE_FULLTEXTCATALOG)
           and schemaversion < @schemaversion_of_snapshottrailer
           and schemastatus < @desired_schema_status

        -- Exclude pre-post, but include snapshot only commands
	-- UDTableType can contain udt or clr udt or xml datatype. Hence it should be enumerated after all of the above.
        union all
        select 105 as schema_type_group, pubid, artid, schemaversion, schemaguid, schematype,  schematext, schemastatus, 0 as processing_order
          from dbo.sysmergeschemachange where schemaversion > @schemaversion
           and pubid = @pubid
           and schematype in (@SCHEMA_TYPE_USER_DEFINED_TABLE_TYPE)
           and schemaversion < @schemaversion_of_snapshottrailer
           and schemastatus < @desired_schema_status


        -- DIRECTORY
        union all
        select 110 as schema_type_group, pubid, artid, schemaversion, schemaguid, schematype,  schematext, schemastatus, 0 as processing_order
          from dbo.sysmergeschemachange where schemaversion > @schemaversion
           and pubid = @pubid
           and schematype in (7)
           and schemaversion < @schemaversion_of_snapshottrailer
           and schemastatus < @desired_schema_status

        -- object creation schema types (sch files)
        union all
        select 112 as schema_type_group, pubid, artid, schemaversion, schemaguid, schematype,  schematext, schemastatus, processing_order
          from (
                -- get processing order from sysmergearticles where artid=artid
                select sms.pubid, sms.artid, sms.schemaversion, sms.schemaguid, sms.schematype, sms.schematext, sms.schemastatus, sma.processing_order
                from dbo.sysmergeschemachange sms JOIN dbo.sysmergearticles sma ON sms.artid = sma.artid and sms.pubid = sma.pubid
                where sms.schemaversion > @schemaversion
                   and sms.pubid = @pubid
                   and sms.schematype in (2, 61, 62, 63, 64, 100)
                   and sms.schemaversion < @schemaversion_of_snapshottrailer
                   and sms.schemastatus < @desired_schema_status

                -- get processing order from sysmergearticles where artid=munged artid
                union all
                select sms.pubid, sms.artid, sms.schemaversion, sms.schemaguid, sms.schematype, sms.schematext, sms.schemastatus, sma.processing_order
                from dbo.sysmergeschemachange sms JOIN dbo.sysmergearticles sma
                    ON substring(convert(binary(16), sms.artid),5,4) = convert(binary(4), sma.objid) and
                       substring(convert(binary(16), sms.artid),0,5) = 0x00000000 and sms.pubid = sma.pubid
                where sms.schemaversion > @schemaversion
                   and sms.pubid = @pubid
                   and sms.schematype in (2, 61, 62, 63, 64, 100)
                   and sms.schemaversion < @schemaversion_of_snapshottrailer
                   and sms.schemastatus < @desired_schema_status

                -- get processing order from sysmergeschemaarticles where artid= artid
                union all
                select sms.pubid, sms.artid, sms.schemaversion, sms.schemaguid, sms.schematype, sms.schematext, sms.schemastatus, smsa.processing_order
                from dbo.sysmergeschemachange sms JOIN dbo.sysmergeschemaarticles smsa ON sms.artid = smsa.artid and sms.pubid = smsa.pubid
                where sms.schemaversion > @schemaversion
                   and sms.pubid = @pubid
                   and sms.schematype in (2, 61, 62, 63, 64, 100)
                   and sms.schemaversion < @schemaversion_of_snapshottrailer
                   and sms.schemastatus < @desired_schema_status

                -- get processing order from sysmergeschemaarticles where artid= munged artid
                union all
                select sms.pubid, sms.artid, sms.schemaversion, sms.schemaguid, sms.schematype, sms.schematext, sms.schemastatus, smsa.processing_order
                from dbo.sysmergeschemachange sms JOIN dbo.sysmergeschemaarticles smsa
                    ON substring(convert(binary(16), sms.artid),5,4) = convert(binary(4), smsa.objid) and
                       substring(convert(binary(16), sms.artid),0,5) = 0x00000000 and sms.pubid = smsa.pubid
                where sms.schemaversion > @schemaversion
                   and sms.pubid = @pubid
                   and sms.schematype in (2, 61, 62, 63, 64, 100)
                   and sms.schemaversion < @schemaversion_of_snapshottrailer
                   and sms.schemastatus < @desired_schema_status
                   ) as processing_order_specific_schemas


        -- BCP commands
        union all
        select 114 as schema_type_group, pubid, artid, schemaversion, schemaguid, schematype,  schematext, schemastatus, 0 as processing_order
          from dbo.sysmergeschemachange where schemaversion > @schemaversion
           and pubid = @pubid
           and schematype in (3, 4, 80, 81, 82, 83, 84, 85)
           and schemaversion < @schemaversion_of_snapshottrailer
           and schemastatus < @desired_schema_status

        -- Dynamic BCP commands
        union all
        select 120 as schema_type_group, pubid, artid, schemaversion, schemaguid, schematype,  schematext, schemastatus, 0 as processing_order
          from dbo.sysmergeschemachange where schemaversion > @schemaversion
           and pubid = @pubid
           and schematype in (131, 132, 208, 209, 210, 211, 212, 213)
           and schemaversion < @schemaversion_of_snapshottrailer
           and schemastatus < @desired_schema_status

        -- Trg/XPROP/DRI/MERGE Procs
        union all
        select 130 as schema_type_group, pubid, artid, schemaversion, schemaguid, schematype,  schematext, schemastatus, 0 as processing_order
          from dbo.sysmergeschemachange where schemaversion > @schemaversion
           and pubid = @pubid
           and schematype in (10,15,65,48)
           and schemaversion < @schemaversion_of_snapshottrailer
           and schemastatus < @desired_schema_status

        -- Insert post command
        union all
        select 140 as schema_type_group, pubid, artid, schemaversion, schemaguid, schematype,  schematext, schemastatus, 0 as processing_order
          from dbo.sysmergeschemachange where schemaversion > @schemaversion
           and pubid = @pubid
           and schematype = 45
           and schemaversion < @schemaversion_of_snapshottrailer
           and schemastatus < @desired_schema_status

        -- Insert snapshot trailer
        union all
        select 150 as schema_type_group, pubid, artid, schemaversion, schemaguid, schematype,  schematext, schemastatus, 0 as processing_order
          from dbo.sysmergeschemachange where schemaversion > @schemaversion
           and pubid = @pubid
           and schematype = 52
           and schemaversion = @schemaversion_of_snapshottrailer
           and schemastatus < @desired_schema_status

        -- Insert other schema changes
        union all
        select 160 as schema_type_group, pubid, artid, schemaversion, schemaguid, schematype,  schematext, schemastatus, 0 as processing_order
          from dbo.sysmergeschemachange where schemaversion > @schemaversion
           and schematype not in (2, 3, 4, 10, 15, 7, 40, 45, 60, 80, 81, 82, 83, 84, 85, 88, 89, 90, 91, 92, 93, 96, 31, 48, 105)
           and schematype not in (61, 62, 63, 64, 65, 71, 100)
           and schematype not in (25, 50, 51, 52, 53, 54, 55, 56, 57, 58)
           and schematype not in (131, 132, 208, 209, 210, 211, 212, 213) -- dynamic bcp commands
           and schematype not in (46, 11, 13, 300, 301) -- The on-demand script and schema replication commands should not be enumerated unless
                                                        -- we have made sure that it was posted after the current snapshot.
           and schematype not in (@SCHEMA_TYPE_DROPARTICLE)
           and schematype not in (12, 14) -- we are skipping the reinit type commands here and we will insert it seperately. This is so that we only select the reinit commands with the highest schemaversion
           and pubid = @pubid
           and schemastatus < @desired_schema_status

        -- reinit type commands
        union all
        select top 1 170 as schema_type_group, pubid, artid, schemaversion, schemaguid, schematype,  schematext, schemastatus, 0 as processing_order
          from dbo.sysmergeschemachange where schemaversion > @schemaversion
           and pubid = @pubid
           and schematype = 12
           and schemastatus < @desired_schema_status
           order by schemaversion desc

        -- reinit with upload type commands
        union all
        select top 1 170 as schema_type_group, pubid, artid, schemaversion, schemaguid, schematype,  schematext, schemastatus, 0 as processing_order
          from dbo.sysmergeschemachange where schemaversion > @schemaversion
           and pubid = @pubid
           and schematype = 14
           and schemastatus < @desired_schema_status
           order by schemaversion desc

        -- The on-demand script and schema replication commands only that are greater than the snapshot trailer if one exists.
        union all
        select 190 as schema_type_group, pubid, artid, schemaversion, schemaguid, schematype, schematext, schemastatus, 0 as processing_order
          from dbo.sysmergeschemachange
            where schemaversion > @schemaversion_of_snapshottrailer
            and schematype in (46, 11, 13, 300, 301)
            and pubid = @pubid
           and schemastatus < @desired_schema_status

         ) as schema_changes
         order by schema_type_group, processing_order, schemaversion
    end

    return (0)

end

 
Last revision SQL2008SP2
See also

  sp_MSdrop_rlrecon (Procedure)
sp_MSenumschemachange (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