Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSmerge_metadataupgrade

  No additional text.


Syntax
create procedure sys.sp_MSmerge_metadataupgrade
as
    declare @retcode integer
    declare @table_name sysname
    declare @default_nicknames    varbinary(255)
    declare @replnick   binary(6)
    begin tran
    save tran merge_metadata_upgrade

    -- MSmerge_genhistory
    SELECT @table_name = N'MSmerge_genhistory'
    IF object_id('MSmerge_genhistory') is not NULL
    BEGIN
        if not exists (select * from sys.columns where object_id = object_id('MSmerge_genhistory') and
                        name = 'changecount')
        begin
            alter table dbo.MSmerge_genhistory add changecount int NOT NULL default 0
            if @@error<>0 goto error
        end

        if exists (select * from sys.columns where name = 'guidlocal' and object_id = object_id('dbo.MSmerge_genhistory'))
        begin
            if not exists (select * from sys.columns where name = 'genstatus' and object_id = object_id('dbo.MSmerge_genhistory'))
            begin
                alter table dbo.MSmerge_genhistory add genstatus tinyint NOT NULL default 0
                if @@error<>0 goto error
            end

            exec('update dbo.MSmerge_genhistory set genstatus = 0 where guidlocal = ''00000000-0000-0000-0000-000000000000''')
            if @@error<>0 goto error

            exec('update dbo.MSmerge_genhistory set genstatus = 1 where guidlocal <> ''00000000-0000-0000-0000-000000000000'' and guidlocal = guidsrc')
            if @@error<>0 goto error

            exec('update dbo.MSmerge_genhistory set genstatus = 2 where guidlocal <> ''00000000-0000-0000-0000-000000000000'' and guidlocal <> guidsrc')
            if @@error<>0 goto error

            -- drop the index before dropping the column.
            if exists (select * from sys.indexes where name = 'nc2MSmerge_genhistory' and object_id = object_id('dbo.MSmerge_genhistory'))
            begin
                drop index dbo.MSmerge_genhistory.nc2MSmerge_genhistory
            end

            alter table dbo.MSmerge_genhistory drop column guidlocal
            if @@error<>0 goto error
        end

	if not exists (select * from sys.columns where object_id = object_id('MSmerge_genhistory') and
				name = 'subscriber_number')
	begin
		alter table dbo.MSmerge_genhistory add subscriber_number int NOT NULL default 0
		if @@error<>0 goto error
	end
        -- nicknames is varbinary(1000) in Shiloh and before, varbinary(1001) in Yukon and after.
        -- in addition, the nicknames contained therein changed from 4 bytes to 6 bytes.
        if 1001 > col_length('MSmerge_genhistory', 'nicknames')
        begin
            begin tran
            save tran tran_genhistnicks80to90
                alter table dbo.MSmerge_genhistory alter column nicknames varbinary(1001) not null
                if @@error<>0 goto err_genhistnicks80to90

                exec @retcode= sys.sp_MSgetreplnick @replnick = @replnick out
                if @retcode<>0 or @@error<>0
                    goto err_genhistnicks80to90
                -- add a guard byte
                set @default_nicknames= @replnick + 0xFF

                update dbo.MSmerge_genhistory set nicknames= ISNULL({fn REPLNICKARRAY_80_TO_90(nicknames)},@default_nicknames)
                if @@error<>0 goto err_genhistnicks80to90
            commit tran
            goto after_genhistnicks80to90

            err_genhistnicks80to90:
            rollback tran tran_genhistnicks80to90
            commit tran
            goto error
        end

        after_genhistnicks80to90:

        -- generation is int in Shiloh and before, bigint in Yukon and after
        -- drop the index on generation before changing it to bigint
        exec @retcode = sys.sp_MSmerge_dropindex @table_name = 'MSmerge_genhistory', @index_column_name = 'generation'
        if @@error <> 0 or @retcode <> 0
            goto error

        if 56 = (select system_type_id from sys.columns where
                        object_id = object_id('MSmerge_genhistory') and name = 'generation')
        begin
            alter table dbo.MSmerge_genhistory alter column generation bigint not null
            if @@error<>0 goto error
        end

        if columnproperty(object_id('dbo.MSmerge_genhistory'), 'generation', 'IsIdentity') = 1
        begin
            -- an index has been added on genhistory(genstatus)
            if not exists (select * from sysindexes where name = 'nc2MSmerge_genhistory')
            begin
                create  index nc2MSmerge_genhistory on MSmerge_genhistory(genstatus, art_nick,changecount)
                if @@error<>0 goto error
            end
            -- Only create the index index if guidsrc values are unique. Before SP1 it was possible
            -- for this table to contain dup guidsrc. This happened when the genhistory table was empty to start
            -- and we insert an initial row (for no article) and then try to insert a row for the first
            -- article without generating a new guid.
            IF EXISTS ( SELECT guidsrc, pubid
                FROM dbo.MSmerge_genhistory
                GROUP BY guidsrc, pubid
                HAVING COUNT(*) > 1 )
                RAISERROR (21203, 10, 5, @table_name)
            ELSE
            begin
                IF EXISTS ( SELECT * FROM sys.indexes WHERE name = 'uc1MSmerge_genhistory' AND
                    object_id = OBJECT_ID('MSmerge_genhistory') )
                begin
                    drop index MSmerge_genhistory.uc1MSmerge_genhistory
                    if @@error<>0 goto error
                    -- used to be clustered in previous versions, now it's nonclustered, hence the name change
                    create unique index unc1MSmerge_genhistory on MSmerge_genhistory(guidsrc, pubid)
                    if @@error<>0 goto error
                end
            end

            IF EXISTS ( SELECT * FROM sys.indexes WHERE name = 'nc3MSmerge_genhistory' AND
                            object_id = OBJECT_ID('MSmerge_genhistory') )
            begin
                DROP INDEX MSmerge_genhistory.nc3MSmerge_genhistory
                if @@error<>0 goto error
            end

            IF NOT EXISTS ( SELECT * FROM sys.indexes WHERE name = 'nc4MSmerge_genhistory' AND
                            object_id = OBJECT_ID('MSmerge_genhistory') )
            begin
                CREATE INDEX nc4MSmerge_genhistory ON MSmerge_genhistory(coldate)
                if @@error<>0 goto error
            end

            -- we will drop this index so that we can create a new clustered one as well
            -- as allow us to perform the alter column statement following this drop
            IF EXISTS ( SELECT * FROM sysindexes WHERE name = 'nc1MSmerge_genhistory' AND
                        id = OBJECT_ID('MSmerge_genhistory') )
            begin
                drop index MSmerge_genhistory.nc1MSmerge_genhistory
                if @@error<>0 goto error
            end

            if not exists (select * from sys.indexes where name = 'c1MSmerge_genhistory' and object_id = object_id('MSmerge_genhistory'))
            begin
                create clustered index c1MSmerge_genhistory on MSmerge_genhistory(generation)
                if @@error<>0 goto error
            end
        end
        else
        begin
            exec @retcode = sys.sp_MSmakegenerationidentity
            if @@error<>0 or @retcode <> 0
                goto error
        end
	
    END

    SELECT @table_name = N'MSmerge_tombstone'
    IF object_id('MSmerge_tombstone') is not NULL
    BEGIN

        if exists (select * from sys.columns where name = 'reason' and object_id = object_id('MSmerge_tombstone'))
        begin
            alter table dbo.MSmerge_tombstone drop column reason
            if @@error<>0 goto error
        end

        -- lineage is varbinary(249) in Shiloh and before, varbinary(311) in Yukon and after
        -- in addition, the format has changed from 4 to 6 byte nicknames, and there are new mergenicks
        if 311 > col_length('MSmerge_tombstone', 'lineage')
        begin
            begin tran
            save tran tran_tomblin80to90
                alter table dbo.MSmerge_tombstone alter column lineage varbinary(311) not null
                if @@error<>0 goto err_tomblin80to90
                update dbo.MSmerge_tombstone set lineage= {fn LINEAGE_80_TO_90(lineage)}
                if @@error<>0 goto err_tomblin80to90
            commit tran
            goto after_tomblin80to90

            err_tomblin80to90:
            rollback tran tran_tomblin80to90
            commit tran
            goto error
        end

        after_tomblin80to90:

        -- generation is int in Shiloh and before, bigint in Yukon and after
        if 56 = (select system_type_id from sys.columns where
                        object_id = object_id('MSmerge_tombstone') and name = 'generation')
        begin
            IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'nc2MSmerge_tombstone' AND object_id = OBJECT_ID('MSmerge_tombstone'))
                drop index MSmerge_tombstone.nc2MSmerge_tombstone
            if @@error<>0 goto error
            alter table dbo.MSmerge_tombstone alter column generation bigint not null
            if @@error<>0 goto error
            create index nc2MSmerge_tombstone on MSmerge_tombstone(generation)
            if @@error<>0 goto error
        end

        IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'unc3MSmerge_tombstone' AND object_id = OBJECT_ID('MSmerge_tombstone'))
        begin
            drop index MSmerge_tombstone.unc3MSmerge_tombstone
            if @@error<>0 goto error
        end

        if not exists (select * from sys.columns where object_id = object_id('MSmerge_tombstone') and
                        name = 'logical_record_parent_rowguid')
        begin
            alter table dbo.MSmerge_tombstone add logical_record_parent_rowguid    uniqueidentifier    NULL
            if @@error<>0 goto error
        end

        if not exists (select * from sys.columns where object_id = object_id('MSmerge_tombstone') and
                        name = 'logical_record_lineage')
        begin
            alter table dbo.MSmerge_tombstone add logical_record_lineage    varbinary(311)    NULL
            if @@error<>0 goto error
        end
    END

    SELECT @table_name = N'MSmerge_contents'
    IF object_id('MSmerge_contents') is not NULL
    BEGIN
        IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'nc2MSmerge_contents' AND object_id = OBJECT_ID('MSmerge_contents'))
            drop index MSmerge_contents.nc2MSmerge_contents

        IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'nc3MSmerge_contents' AND object_id = OBJECT_ID('MSmerge_contents'))
            drop index MSmerge_contents.nc3MSmerge_contents

        IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'nc4MSmerge_contents' AND object_id = OBJECT_ID('MSmerge_contents'))
            drop index MSmerge_contents.nc4MSmerge_contents

        IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'nc5MSmerge_contents' AND object_id = OBJECT_ID('MSmerge_contents'))
            drop index MSmerge_contents.nc5MSmerge_contents

        IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'nc6MSmerge_contents' AND object_id = OBJECT_ID('MSmerge_contents'))
            drop index MSmerge_contents.nc6MSmerge_contents

        IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'unc3SycContents' AND object_id = OBJECT_ID('MSmerge_contents'))
            drop index MSmerge_contents.unc3SycContents

        --IF EXISTS (SELECT * FROM sysindexes WHERE name = 'uc1SycContents' AND id = OBJECT_ID('MSmerge_contents'))
        --  exec sys.sp_rename 'uc1SycContents', 'uc1MSmerge_contents', 'index'

        -- lineage is varbinary(249) in Shiloh and before, varbinary(311) in Yukon and after
        -- in addition, the format has changed from 4 to 6 byte nicknames, and there are new mergenicks
        if 311 > col_length('MSmerge_contents', 'lineage')
        begin
            begin tran
            save tran tran_contlin80to90
                alter table dbo.MSmerge_contents alter column lineage varbinary(311) not null
                if @@error<>0 goto err_contlin80to90
                update dbo.MSmerge_contents set lineage= {fn LINEAGE_80_TO_90(lineage)}
                if @@error<>0 goto err_contlin80to90
            commit tran
            goto after_contlin80to90

            err_contlin80to90:
            rollback tran tran_contlin80to90
            commit tran
            goto error
        end
        after_contlin80to90:

        -- colv is varbinary(2048) in Shiloh and before, varbinary(2953) in Yukon and after
        -- in addition, the format has changed from 4 to 6 byte nicknames, and colv is compressed
        -- 2953 is got from 12 bytes of metadata per column * max 246 columns + 1 trailing byte
        if 2953 > col_length('MSmerge_contents', 'colv1')
        begin
            begin tran
            save tran tran_colv80to90
                alter table dbo.MSmerge_contents alter column colv1 varbinary(2953) null
                if @@error<>0 goto err_colv80to90
                begin try
                    update dbo.MSmerge_contents set colv1= {fn COLV_80_TO_90(colv1)}
                end try
                begin catch
                    update dbo.MSmerge_contents set colv1= {fn COLV_80_TO_90(sys.fn_MSmerge_Fix80Colv(colv1))}
                end catch
                if @@error<>0 goto err_colv80to90
            commit tran
            goto after_colv80to90

            err_colv80to90:
            rollback tran tran_colv80to90
            commit tran
            goto error
        end

        after_colv80to90:

        -- generation is int in Shiloh and before, bigint in Yukon and after
        if 56 = (select system_type_id from sys.columns where
                        object_id = object_id('MSmerge_contents') and name = 'generation')
        begin
            alter table dbo.MSmerge_contents alter column generation bigint not null
            if @@error<>0 goto error
        end

        -- partchangegen is int in Shiloh and before, bigint in Yukon and after
        if 56 = (select system_type_id from sys.columns where
                        object_id = object_id('MSmerge_contents') and name = 'partchangegen')
        begin
            alter table dbo.MSmerge_contents alter column partchangegen bigint null
            if @@error<>0 goto error
        end

        if not exists (select * from sys.columns where object_id = object_id('MSmerge_contents') and
                        name = 'marker')
        begin
            alter table dbo.MSmerge_contents add marker    uniqueidentifier NULL
            if @@error<>0 goto error
        end

        if not exists (select * from sys.columns where object_id = object_id('MSmerge_contents') and
                        name = 'logical_record_parent_rowguid')
        begin
            alter table dbo.MSmerge_contents add logical_record_parent_rowguid    uniqueidentifier    NULL
            if @@error<>0 goto error
        end

        if not exists (select * from sys.columns where object_id = object_id('MSmerge_contents') and
                        name = 'logical_record_lineage')
        begin
            alter table dbo.MSmerge_contents add logical_record_lineage    varbinary(311)    NULL
            if @@error<>0 goto error
        end

        if exists (select * from sys.columns where name = 'joinchangegen' and object_id = object_id('MSmerge_contents'))
        begin
            exec('update dbo.MSmerge_contents
                  set partchangegen = case when partchangegen is null
                                      then (case when joinchangegen=generation then (-generation) else joinchangegen end)
                                      else (case when partchangegen < joinchangegen then joinchangegen else partchangegen end)
                                      end')
            if @@error<>0 goto error

            alter table dbo.MSmerge_contents drop column joinchangegen
            if @@error<>0 goto error
        end

        create index nc2MSmerge_contents on MSmerge_contents(generation)
        if @@error<>0 goto error

        create index nc3MSmerge_contents on MSmerge_contents(partchangegen)
        if @@error<>0 goto error

		create index nc4MSmerge_contents on MSmerge_contents(rowguid)
        if @@error <> 0 goto error

        --create index nc6MSmerge_contents on MSmerge_contents(logical_record_parent_rowguid)
        --if @@error<>0 goto error
    END


    -- remove orphaned rows in MSmerge_contents
    if (
            object_id('sysmergearticles') is not NULL  and
            object_id('sysmergepublications') is not NULL  and
            object_id('MSmerge_genhistory') is not NULL  and
            object_id('MSmerge_contents') is not NULL  and
            object_id('MSmerge_tombstone') is not NULL
        )
    begin
        exec @retcode= sp_MSpurgecontentsorphans
        if @retcode<>0 or @@error<>0
            goto error
    end

    commit tran
    return 0

error:
    rollback tran merge_metadata_upgrade
    commit tran
    raiserror(21090, 16, -1)
    return 1

 
Last revision 2008RTM
See also

  sp_helpmergepublication (Procedure)
sp_MSmerge_upgrade_subscriber (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