Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSenumschemachange_80

  No additional text.


Syntax

create procedure sys.sp_MSenumschemachange_80(
    @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_80')
        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 dbo.sysmergeschemachange where pubid=@pubid and schemaversion > @schemaversion and schematype = @alter_table_type
            return (0)
        end

    if exists (select * from dbo.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 (@schemaversion > 0)
    begin
        -- Subscriber has already received the snapshot so filter out
        -- the pre/post-snapshot commands.

        -- 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.
        -- Note: Excluded list should include all snapshot header, trailer commands
        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 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)
           and schematype not in (71, 80, 81, 82, 83, 84, 85, 88, 89, 90, 91, 92, 93, 96, 208, 209, 210, 211, 212, 213, 17) -- Filter out Yukon specific schema types
           and schematype not in (105) --Filter out Katmai specific schema types
        order by schemaversion
        -- 71 Dynamic snapshot validation token
    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 dbo.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 dbo.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 dbo.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 dbo.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 dbo.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 dbo.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 dbo.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 dbo.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 dbo.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 dbo.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 dbo.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 dbo.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)
sp_MSenumschemachange_80sp3 (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