Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSenumschemachange_80sp3

  No additional text.


Syntax

CREATE PROCEDURE sys.sp_MSenumschemachange_80sp3(
        @pubid                         uniqueidentifier,
        @AlterTableOnly                                 int,
        @schemaversion         int
        )
as
    set nocount on
        /*
        ** To public
        */
        declare @alter_table_type               int
        declare @reinit_all_type                int
        declare @reinit_all_upload_type int
        declare @schemaversion_of_snapshottrailer int

        select @reinit_all_type = 12
        select @alter_table_type = 11
        select @reinit_all_upload_type = 14

        if (@schemaversion is null)
                begin
                RAISERROR(14043, 16, -1, '@schemaversion', 'sp_MSenumschemachange_80sp3')
                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
                        select pubid, artid, schemaversion, schemaguid, schematype, convert(nvarchar(2000), schematext) as schematext
                                from sysmergeschemachange where pubid=@pubid and schemaversion > @schemaversion and schematype = @alter_table_type
                        return (0)
                end

        if exists (select * from sysmergeschemachange where
                pubid=@pubid and ((schemaversion > @schemaversion and schematype = @reinit_all_type)
                or (schemaversion > @schemaversion and schematype = @reinit_all_upload_type)))
                begin
                        select @schemaversion=0
                end

        -- If subscriber missed a preparecleanup and a completecleanup they must be reinitialized

        if exists (select * from sysmergeschemachange where
                pubid=@pubid and schemaversion > @schemaversion and schematype = 17) and
            exists (select * from sysmergeschemachange where
                pubid=@pubid and schemaversion > @schemaversion and schematype = 19)
                begin
                        set @schemaversion=0
                end

    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

        select @min_incremental_schemaversion = min(schemaversion),
               @max_incremental_schemaversion = max(schemaversion)
          from dbo.sysmergeschemachange where schemaversion > @schemaversion
           and pubid = @pubid
           -- normal snapshot commands excluding pre/post snapshot commands
           -- and system tables commands
           and schematype in (2, 3, 4, 61, 62, 63, 64, 131, 132, 10, 15, 65)

        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

            declare @incremental_schemachanges table
            (
                pubid           uniqueidentifier  NOT NULL,
                artid           uniqueidentifier  NULL,
                schemaversion   int               NOT NULL,
                schemaguid      uniqueidentifier  NOT NULL,
                schematype      int               NOT NULL,
                schematext      nvarchar(2000)    collate database_default not null,
                seqno           int identity      NOT NULL
            )
            if @@error <> 0 return 1

            -- Insert every thing before @min_incremental_schemaversion but
            -- after @schemaversion
            -- Note: Excluded list should include all snapshot header, trailer commands

            insert into @incremental_schemachanges
            select pubid, artid, schemaversion, schemaguid, schematype, convert(nvarchar(2000), schematext) as schematext
              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, 208, 209, 210, 211, 212, 213)
            order by schemaversion
            if @@error <> 0 return 1

            -- Incremental snapshot header
            -- Header begins
            insert into @incremental_schemachanges
            select pubid, artid, @min_incremental_schemaversion, schemaguid, schematype | @incremental_schematype_bit, convert(nvarchar(2000), schematext) as schematext
              from dbo.sysmergeschemachange
             where pubid = @pubid
               and schematype = 50
            if @@error <> 0 return 1

            -- Header content
            insert into @incremental_schemachanges
            select pubid, artid, @min_incremental_schemaversion, schemaguid, schematype | @incremental_schematype_bit, convert(nvarchar(2000), schematext) as schematext
              from dbo.sysmergeschemachange
             where pubid = @pubid
               and schematype in (25, 53, 54, 55, 56, 57, 58)
            order by schemaversion
            if @@error <> 0 return 1

            -- Header ends
            insert into @incremental_schemachanges
            select pubid, artid, @min_incremental_schemaversion, schemaguid, schematype | @incremental_schematype_bit, convert(nvarchar(2000), schematext) as schematext
             from dbo.sysmergeschemachange
            where pubid = @pubid
              and schematype = 51
            if @@error <> 0 return 1

            -- 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
            insert into @incremental_schemachanges
            select pubid, artid, schemaversion, schemaguid, schematype, convert(nvarchar(2000), schematext) as schematext
              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, 80, 81, 82, 83, 84, 85, 208, 209, 210, 211, 212, 213)
            order by schemaversion
            if @@error <> 0 return 1

            -- Incremental snapshot trailer
            insert into @incremental_schemachanges
            select pubid, artid, @max_incremental_schemaversion, @max_incremental_schemaguid, schematype | @incremental_schematype_bit, convert(nvarchar(2000), schematext) as schematext
              from dbo.sysmergeschemachange
             where pubid = @pubid
               and schematype = 52
            if @@error <> 0 return 1

            -- Add everything after the incremental article commands
            -- Note: Excluded list should include all snapshot header, trailer commands
            insert into @incremental_schemachanges
            select pubid, artid, schemaversion, schemaguid, schematype, convert(nvarchar(2000), schematext) as schematext
              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, 208, 209, 210, 211, 212, 213)
            order by schemaversion

            -- Remove Yukon specific schematypes
            -- 71 Dynamic snapshot validation token
                        -- SCHEMA_TYPE_USER_DEFINED_DATA_TYPE           = 88,
                        -- SCHEMA_TYPE_CLR_USER_DEFINED_DATA_TYPE       = 89,
                        -- SCHEMA_TYPE_ASSEMBLY                                 = 90,
                        -- SCHEMA_TYPE_PARTITIONSCHEME                          = 91,
                        -- SCHEMA_TYPE_PARTITIONFUNCTION                        = 92,
                        -- SCHEMA_TYPE_XMLSCHEMANAMESPACE                       = 93,
                        -- SCHEMA_TYPE_FULLTEXTCATALOG                          = 96,
                        -- SCHEMA_TYPE_USER_SCHEMA                              = 31
		-- Remove Katmai specific schematypes
                        -- SCHEMA_TYPE_USER_DEFINED_TABLE_TYPE		    = 105
            delete @incremental_schemachanges where schematype in (71, 80, 81, 82, 83, 84, 85, 88, 89, 90, 91, 92, 93, 96, 31, 208, 209, 210, 211, 212, 213, 17, 105)

            -- Some schematypes that are distinct in Yukon used to have wrong number pre-Shiloh.
            -- Map them back to the old values.
            update @incremental_schemachanges set schematype=8 where schematype in (21,23)
            update @incremental_schemachanges set schematype=9 where schematype = 18

            -- Return the end result
            select pubid, artid, schemaversion, schemaguid, schematype, convert(nvarchar(2000), schematext) as schematext
              from @incremental_schemachanges order by seqno asc
        end
        else
        begin
            -- case ii)
            -- Also filter out the schematypes 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.
            -- added 17 so we dont send SCHEMA_TYPE_UPDATELASTSENTGEN which is a yukon specific schema type.
            select pubid, artid, schemaversion, schemaguid,
                   case schematype -- Pre-Yukon had some overloaded schematypes
                        when 18 then 9
                        when 21 then 8
                        when 23 then 8
                        else schematype
                   end,
                   convert(nvarchar(2000), schematext) as schematext
              from sysmergeschemachange where schemaversion > @schemaversion
                       and pubid = @pubid
               and schematype not in (17, 40, 45, 50, 51, 52, 53, 54, 55, 56, 57, 58, 25, 5, 6, 71, 80, 81, 82, 83, 84, 85, 88, 89, 90, 91, 92, 93, 96, 31, 208, 209, 210, 211, 212, 213, 105)
            order by schemaversion
        end
    end
    Else
    begin
        -- Subscriber requires a snapshot, so carefully sequence the
        -- pre/post-snapshot commands around the snapshot boundary

        create table #schemachanges
        (
            pubid           uniqueidentifier  NOT NULL,
            artid           uniqueidentifier  NULL,
            schemaversion   int               NOT NULL,
            schemaguid      uniqueidentifier  NOT NULL,
            schematype      int               NOT NULL,
            schematext      nvarchar(2000)    collate database_default not null,
            seqno           int identity      NOT NULL
        )

        truncate table #schemachanges
        -- Insert snapshot header

        -- Header begins
        insert into #schemachanges
            select pubid, artid, schemaversion, schemaguid, schematype, convert(nvarchar(2000), schematext) as schematext
                  from sysmergeschemachange where schemaversion > @schemaversion
                   and pubid = @pubid
           and schematype = 50

        -- Header content
        insert into #schemachanges
            select pubid, artid, schemaversion, schemaguid, schematype, convert(nvarchar(2000), schematext) as schematext
                  from sysmergeschemachange where schemaversion > @schemaversion
                   and pubid = @pubid
           and schematype in (25, 53, 54, 55, 56, 57, 58)
        order by schemaversion

        -- Header ends
        insert into #schemachanges
            select pubid, artid, schemaversion, schemaguid, schematype, convert(nvarchar(2000), schematext) as schematext
                  from sysmergeschemachange where schemaversion > @schemaversion
                   and pubid = @pubid
           and schematype = 51

        -- End of snapshot header
        -- Insert pre command
        insert into #schemachanges
            select pubid, artid, schemaversion, schemaguid, schematype, convert(nvarchar(2000), schematext) as schematext
                  from sysmergeschemachange where schemaversion > @schemaversion
                   and pubid = @pubid
           and schematype = 40

        -- Exclude pre-post, but include snapshot only commands
        insert into #schemachanges
            select pubid, artid, schemaversion, schemaguid, schematype, convert(nvarchar(2000), schematext) as schematext
                  from sysmergeschemachange where schemaversion > @schemaversion
                   and pubid = @pubid
           and schematype in (2, 3, 4, 20, 7, 60, 61, 62, 63, 64)
                 order by schemaversion

        -- Dynamic BCP commands
        insert into #schemachanges
            select pubid, artid, schemaversion, schemaguid, schematype, convert(nvarchar(2000), schematext) as schematext
                  from sysmergeschemachange where schemaversion > @schemaversion
                   and pubid = @pubid
           and schematype in (131, 132)
                 order by schemaversion

        -- DRI/Trg/XPROP
        insert into #schemachanges
            select pubid, artid, schemaversion, schemaguid, schematype, convert(nvarchar(2000), schematext) as schematext
                  from sysmergeschemachange where schemaversion > @schemaversion
                   and pubid = @pubid
           and schematype in (10, 15, 65)
                 order by schemaversion

        -- Insert post command
        insert into #schemachanges
            select pubid, artid, schemaversion, schemaguid, schematype, convert(nvarchar(2000), schematext) as schematext
                  from sysmergeschemachange where schemaversion > @schemaversion
                   and pubid = @pubid
           and schematype = 45

        -- Insert snapshot trailer
     insert into #schemachanges
            select pubid, artid, schemaversion, schemaguid, schematype, convert(nvarchar(2000), schematext) as schematext
                  from sysmergeschemachange where schemaversion > @schemaversion
                   and pubid = @pubid
           and schematype = 52

        -- Insert other schema changes
        insert into #schemachanges
            select pubid, artid, schemaversion, schemaguid, schematype, convert(nvarchar(2000), schematext) as schematext
                  from sysmergeschemachange where schemaversion > @schemaversion
           and schematype not in (2, 3, 4, 10, 15, 20, 7, 40, 45, 60)
           and schematype not in (61, 62, 63, 64, 65)
           and schematype not in (25, 50, 51, 52, 53, 54, 55, 56, 57, 58)
           and schematype not in (131, 132) -- dynamic bcp commands
                   and schematype not in (46, 11, 13) -- 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 pubid = @pubid
                 order by schemaversion

                -- get the schemaversion of the snapshot trailer row
                select @schemaversion_of_snapshottrailer = schemaversion from sysmergeschemachange
                        where schemaversion > @schemaversion
                        and pubid = @pubid
                        and schematype = 52

                if (@schemaversion_of_snapshottrailer is not null)
                begin
                        -- insert schema changes for on-demand script and schema replication commands which have schemaversion greater than
                        -- schemaversion of the snapshot trailer row
                        insert into #schemachanges
                        select pubid, artid, schemaversion, schemaguid, schematype, convert(nvarchar(2000), schematext) as schematext
                        from sysmergeschemachange where schemaversion > @schemaversion_of_snapshottrailer
                        and schematype in (46, 11, 13) -- The on-demand script and schema replication commands only.
                        and pubid = @pubid
                        order by schemaversion
        end

                declare @endofsnapshot int
        select @endofsnapshot = min(seqno) from #schemachanges
        where schematype = 52
        delete #schemachanges where seqno > @endofsnapshot
            and (schematype in (2, 3, 4, 10, 15, 20, 7, 40, 45, 65) or
            schematype in (25, 50, 51, 52, 53, 54, 55, 56, 57, 58))

                --only list the last reinitall command
                delete schemachanges1 from #schemachanges schemachanges1,#schemachanges schemachanges2
                        where schemachanges1.pubid=@pubid and schemachanges1.schematype=12 and
                                  schemachanges2.pubid=@pubid and schemachanges2.schematype=12 and
                                  schemachanges1.schemaversion
 
Last revision 2008RTM
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