Enter 2001 April/EnterCD4.iso/Update/SQL Server SP3/sql70sp3i.exe/INSTALL/sp3_repl.sql

Text File | 2000-10-04 | 115.2 KB | 3,272 lines

/*------------------------------------------------------------------------------

sp3_repl.sql

THIS SCRIPT UPDATES REPLICATION SYSTEM STORED PROCEDURES FROM 7.0 SP2 to SP3.

Changes in this file are organized as follows (please maintain):
    Common system objects (replsys.sql)
    Common repl objects (replcom.sql)
    Tran repl objects (repltran.sql)
    Merge repl objects (rladmin.sql, rlrecon.sql, rlcore.sql)

Notes:
+ Catalog-updates and sp_MS_upd_sysobj_category are enabled for the entire
    file.  Do not disable or re-enable them.  Please do not change set options.
+ grep for "--.
------------------------------------------------------------------------------*/


--------------------------------------------------------------------------------
-- VERIFY Server is started in single-user-mode (catalog-updates enabled), and
--    start marking of system-objects.
--------------------------------------------------------------------------------
use master
go

dump tran master with no_log
go

exec dbo.sp_configure 'allow updates',1
go
reconfigure with override
go

set ANSI_NULLS off

exec sp_MS_upd_sysobj_category 1
go

--------------------------------------------------------------------------------
--.    Common system objects (replsys.sql)
--------------------------------------------------------------------------------
dump tran master with no_log
go

--------------------------------------------------------------------------------
--.    Common repl objects (replcom.sql)
--------------------------------------------------------------------------------
dump tran master with no_log
go

--------------------------------------------------------------------------------
--.    Tran repl objects (repltran.sql)
--------------------------------------------------------------------------------
dump tran master with no_log
go

--------------------------------------------------------------------------------
--.    Merge repl objects (rladmin.sql)
--------------------------------------------------------------------------------
dump tran master with no_log
go

--------------------------------------------------------------------------------
--.    Merge repl objects (rlcore.sql)
--------------------------------------------------------------------------------
dump tran master with no_log
go

--------------------------------------------------------------------------------
--.    Merge repl objects (rlrecon.sql)
--------------------------------------------------------------------------------
dump tran master with no_log
go

--------------------------------------------------------------------------------
--. sp_MSadd_mergereplcommand
--------------------------------------------------------------------------------
if exists (select * from sysobjects    where type = 'P'
    and name = 'sp_MSadd_mergereplcommand')
    drop procedure sp_MSadd_mergereplcommand
go

raiserror('Creating procedure sp_MSadd_mergereplcommand', 0,1)
GO

CREATE PROCEDURE sp_MSadd_mergereplcommand (
    @publication        sysname,
    @article            sysname = NULL,
    @schematype            int,
    @schematext            nvarchar(2000)
    ) AS
    declare @schemaguid            uniqueidentifier
    declare @schemaversion         int
    declare @retcode             int
    declare @pubid                uniqueidentifier
    declare @artid                uniqueidentifier

    /*
    ** Publish permission check
    */
    exec @retcode=sp_MSreplcheck_publish
    if @retcode<>0 or @@ERROR<>0 return (1)

    if @publication IS NULL
        BEGIN
            RAISERROR (14003, 16, -1)
            RETURN (1)
        END

    select @pubid = pubid FROM sysmergepublications WHERE name = @publication and UPPER(publisher)=UPPER(@@SERVERNAME) and publisher_db=db_name()
    select @artid = artid FROM sysmergearticles WHERE name = @article and pubid = @pubid
    /*
    ** For certain system tables that are bcped out such as MSmerge_contents
    ** and MSmerge_tombstone use the article name as sysobjects.name and get
    ** sysobjects.id as the artid
    */
    if (@artid IS NULL) AND (@schematype <> 7)
        begin
            declare @binguid binary(16)
            set @binguid = OBJECT_ID(@article)
            set @artid = convert(uniqueidentifier, @binguid)
        end

    if exists (select *    from sysmergeschemachange
            where pubid = @pubid
            AND artid = @artid
            AND (schematype = @schematype or @schematype in (3,4) and schematype in (3,4)) )
        begin
            /* Select the existing schema guid */
            select @schemaversion = schemaversion, @schemaguid = schemaguid from sysmergeschemachange
                where pubid = @pubid
                AND artid = @artid
                AND (schematype = @schematype or @schematype in (3,4) and schematype in (3,4))


            /* For directory commands, delete the previous directory before the update */
            if (@schematype = 7)
                begin
                    declare @dir             nvarchar(255)
                    declare @local_path        nvarchar(255)
                    declare @delcmd         nvarchar(255)
                    declare @distributor     sysname
                    declare @distproc         nvarchar(255)

                    /*
                    ** Get distribution server information for remote RPC call.
                    */
                    EXEC @retcode = dbo.sp_helpdistributor @rpcsrvname = @distributor OUTPUT
                    IF @@ERROR <> 0 OR  @retcode <> 0
                        BEGIN
                            RAISERROR (14071, 16, -1)
                            RETURN (1)
                        END

                    select @dir = schematext from sysmergeschemachange
                        where schemaversion = @schemaversion
                    /*
                    ** We have to convert UNC to drive, otherwise will get 'Access denied' error in xp_cmdshell
                    */
                    EXEC @retcode = master.dbo.sp_MSunc_to_drive @unc_path = @dir,
                        @local_server = @distributor, @local_path = @local_path OUTPUT
                    if @retcode<>0 or @@ERROR<>0 return (1)
                    /*
                    ** Delete publication directory in the distributor machine.
                    */
                    SET @distproc = RTRIM(@distributor) + '.master..xp_cmdshell'
                    SET @delcmd = 'rmdir /S /Q "' + @local_path + '"'

                    -- Put outter quotes if running on NT
                    if ((platform() & 0x1) = 0x1)
                        select @delcmd = '" ' + @delcmd + ' "'

                    EXECUTE @distproc @delcmd, NO_OUTPUT
                    if @@ERROR<>0 return (1)
                end

            /*
            ** Update the schema change version
            */
            exec @retcode = dbo.sp_MSupdateschemachange @pubid, @artid, @schemaversion,
                @schemaguid, @schematype, @schematext
            if @@error <> 0    or @retcode <> 0
                begin
                RAISERROR(20054 , 16, -1)
                return (1)
            end
        end
    else
        begin
            /* Insert the schema change */
            select @schemaversion = schemaversion from sysmergeschemachange
            if (@schemaversion is NULL)
                set @schemaversion = 1
            else
                select @schemaversion = 1 + max(schemaversion) from sysmergeschemachange

            /* generate a new schema guid */
            set @schemaguid = newid()
            exec @retcode = dbo.sp_MSinsertschemachange @pubid, @artid, @schemaversion,
                @schemaguid, @schematype, @schematext
            if @@error <> 0    or @retcode <> 0
                begin
                    RAISERROR(20054 , 16, -1)
                    return (1)
                end
        end

        /*
        ** change last_validate column of sysmergesubscription entry that represents the publication
        */
        if (@schematype = 7)
        begin
            update sysmergesubscriptions set last_validated=getdate() where pubid=@pubid and subid=@pubid
            IF @@ERROR <> 0
            begin
                RAISERROR(20054 , 16, -1)
                RETURN (1)
            end
        end

    return (0)
go
exec dbo.sp_MS_marksystemobject sp_MSadd_mergereplcommand
go
grant exec on dbo.sp_MSadd_mergereplcommand to public
go

--------------------------------------------------------------------------------
--. sp_MSdummyupdate
--------------------------------------------------------------------------------
if exists (select * from sysobjects    where type = 'P'
    and name = 'sp_MSdummyupdate')
    drop procedure sp_MSdummyupdate
go

raiserror('Creating procedure sp_MSdummyupdate', 0,1)
go

CREATE PROCEDURE sp_MSdummyupdate
    (@rowguid uniqueidentifier, @tablenick int, @metatype tinyint, @pubid uniqueidentifier = NULL, @uplineage tinyint = 1, @inlineage varbinary(255) = NULL, @incolv varbinary(2048) = NULL)
as
    declare @retcode int
    declare @lineage varbinary(255)
    declare @mynickname int
    declare @objid int
    declare @col_tracking int
    declare @ccols int
    declare @missing_count int
    declare @colv varbinary(2048)

    /*
    ** Check to see if current publication has permission
    */
    exec @retcode=sp_MSreplcheck_connection
        @tablenick = @tablenick
    if @retcode<>0 or @@ERROR<>0 return (1)

    /* Parameter checks */
    if (@rowguid is null)
        begin
        RAISERROR(14043, 16, -1, '@rowguid')
        return (1)
        end
    if (@tablenick is null)
        begin
        RAISERROR(14043, 16, -1, '@tablenick')
        return (1)
        end
    if (@metatype is null)
        begin
        RAISERROR(14043, 16, -1, '@metatype')
        return (1)
        end

    /* Check if we have a merge publication by whether system table is there */
    if (not exists(select * from sysobjects where name = 'MSmerge_contents'))
        begin
        RAISERROR(20054 , 16, -1)
        return (1)
        end

    exec dbo.sp_MSgetreplnick @nickname = @mynickname out
    if (@@error <> 0) or @mynickname IS NULL
        begin
        RAISERROR (14055, 11, -1)
        RETURN(1)
        end

    if (@metatype = 0) or @@ERROR<>0
        begin
        declare @reason nvarchar(255)
        /* We don't have the row.  Putting in a system delete tombstone should cause a delete and
        ** eventual convergence.  We are already logging the row as a conflict / error.
        */
        set @lineage = { fn UPDATELINEAGE(0x0, @mynickname) }
        select @reason = formatmessage(20562)

        insert into MSmerge_tombstone (rowguid, tablenick, type, lineage, generation, reason)
            values (@rowguid, @tablenick, 6, @lineage, 0, @reason)

        end
    else if (@metatype = 1)
        begin
        /* Update systombstone */
        select @lineage = lineage from MSmerge_tombstone (UPDLOCK ROWLOCK index = 1) where tablenick = @tablenick and
            rowguid = @rowguid
        if (@uplineage = 1)
        begin
            set @lineage = { fn UPDATELINEAGE(@lineage, @mynickname) }
        end
        if @inlineage is not null
        begin
            set @lineage = @inlineage
            set @lineage = { fn UPDATELINEAGE(@lineage, @mynickname) }
        end

        update MSmerge_tombstone set generation = 0, lineage = @lineage where
            tablenick = @tablenick and rowguid = @rowguid
        end
    else if (@metatype = 2)
        begin
        /* Update MSmerge_contents */
        select @lineage = lineage, @colv = colv1 from MSmerge_contents (UPDLOCK ROWLOCK index = 1) where tablenick = @tablenick and
            rowguid = @rowguid
        if (@uplineage = 1)
        begin
            set @lineage = { fn UPDATELINEAGE(@lineage, @mynickname) }
            if @pubid is NULL
                select @objid = objid, @col_tracking = column_tracking, @missing_count = missing_col_count
                 from sysmergearticles where nickname = @tablenick
            else
                select @objid = objid, @col_tracking = column_tracking, @missing_count = missing_col_count
                     from sysmergearticles where nickname = @tablenick and pubid = @pubid
            if (@col_tracking = 0 or @colv is NULL)
                set @colv = NULL
            else
                set @colv = { fn UPDATECOLVBM(@colv, @mynickname, 0x01, 0x00) }

        end
        if @inlineage is not null
        begin
            set @lineage = @inlineage
            set @lineage = { fn UPDATELINEAGE(@lineage, @mynickname) }
        end
        if @incolv is not null
        begin
            set @colv = @incolv
        end
        update MSmerge_contents set generation = 0, lineage = @lineage, colv1 = @colv where
            tablenick = @tablenick and rowguid = @rowguid
        end
    else if (@metatype = 3)
        begin
        set @lineage = { fn UPDATELINEAGE(0x0, @mynickname) }
        -- call this again, because reconciler assumes that destination always knows about version 1 of source.
        -- thus, without 2nd call, dummy update would be ignored.
        set @lineage = { fn UPDATELINEAGE(@lineage, @mynickname) }
        if @pubid is NULL
            select @objid = objid, @col_tracking = column_tracking, @missing_count = missing_col_count
                 from sysmergearticles where nickname = @tablenick
        else
            select @objid = objid, @col_tracking = column_tracking, @missing_count = missing_col_count
                 from sysmergearticles where nickname = @tablenick and pubid = @pubid
        if (@col_tracking = 0)
            set @colv = NULL
        else
        begin
            select @ccols= count(*) from syscolumns where id = @objid
            set @ccols = @ccols + @missing_count
            set @colv = { fn INITCOLVS(@ccols, @mynickname ) }
        end

        if @inlineage is not null
        begin
            set @lineage = @inlineage
            set @lineage = { fn UPDATELINEAGE(@lineage, @mynickname) }
        end
        if @incolv is not null
        begin
            set @colv = @incolv
        end
        insert into MSmerge_contents (tablenick, rowguid, lineage, generation, colv1)
            values (@tablenick, @rowguid, @lineage, 0, @colv)

        end
    return (0)

go
exec dbo.sp_MS_marksystemobject sp_MSdummyupdate
go
grant exec on dbo.sp_MSdummyupdate to public
go

--------------------------------------------------------------------------------
--. sp_MSenumschemachange
--------------------------------------------------------------------------------
if exists (select * from sysobjects    where type = 'P'
    and name = 'sp_MSenumschemachange')
    drop procedure sp_MSenumschemachange
go

raiserror('Creating procedure sp_MSenumschemachange', 0,1)
GO

CREATE PROCEDURE sp_MSenumschemachange(
    @pubid             uniqueidentifier,
    @schemaversion     int,
    @compatibility_level int = 7000000 -- for 70 RTM and 70 SP1; SP2 and up will pass in a bigger value
    )
as

    /*
    ** To public
    */

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

    if (not exists(select * from sysobjects where name = 'MSmerge_contents'))
        begin
        RAISERROR(20054 , 16, -1)
        return (1)
        end

    /*
    ** For 70 RTM and SP1, we want to filter out type 9 (retention propagation) and 16 (metadata cleanup)
    ** and 5 ( last rec generation ) and 6 (last sent generation)
    ** which they do not support. SP2 subscriber will be passing in 7000200, which gets all schema types
    ** SP2 publishers have to offer
    ** Also filter out the schematypes for the setlastsentgen (5) and setlastrecgen (6) if schemaversion > 0
    ** 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.
    */
    select pubid, artid, schemaversion, schemaguid, schematype, schematext
         from sysmergeschemachange where schemaversion > @schemaversion
             and pubid = @pubid
            and ((@compatibility_level > 7000000 and @schemaversion <= 0)
            or  (@compatibility_level > 7000000 and @schemaversion > 0 and schematype not in (5, 6))
            or  (schematype not in (5, 6, 9, 16)))
                 order by schemaversion
    return (0)
go
exec dbo.sp_MS_marksystemobject sp_MSenumschemachange
go
grant exec on dbo.sp_MSenumschemachange to public
go

--------------------------------------------------------------------------------
--. sp_MSmakectsview
--------------------------------------------------------------------------------
if exists (select * from sysobjects    where type = 'P'
    and name = 'sp_MSmakectsview')
    drop procedure sp_MSmakectsview
go

raiserror('Creating procedure sp_MSmakectsview', 0,1)
GO

create procedure sp_MSmakectsview
    @publication sysname,
    @ctsview     sysname
AS
    set nocount on
    declare @pubid          uniqueidentifier
    declare @artid          uniqueidentifier
    declare @pubidstr        nvarchar(40)
    declare @artidstr        nvarchar(40)
    declare @objid        int
    declare @tablenick    int
    declare @tablenickstr nvarchar(12)
    declare @command_piece nvarchar(2000)
    declare @rowguidcolname sysname
    declare @view_type    int
    declare    @view_name  sysname
    declare @or_after_first nvarchar(100)
    declare @generate_per_article bit
    declare @retcode int

    /* By default the @generate_per_article is OFF */
      set @generate_per_article = 0
    if @ctsview IS NULL
        set @generate_per_article = 1

    set @or_after_first = ''

    select @pubid = pubid from sysmergepublications where name = @publication and UPPER(publisher)=UPPER(@@SERVERNAME) and publisher_db=db_name()
    exec @retcode = dbo.sp_MSguidtostr @pubid, @pubidstr out
    if @@ERROR <>0 OR @retcode <> 0
        return (1)

    /* Check for the no filtering cases */
    if not exists (select * from sysmergesubsetfilters where pubid = @pubid) and
       not exists (select * from sysmergearticles where pubid = @pubid and
                       len(subset_filterclause) > 0)
           begin
            if @generate_per_article = 1
                begin
                    set @ctsview = 'ctsv_' + @pubidstr
                    select @command_piece = N'if object_id(''' + @ctsview + ''') is NOT NULL drop view ' + @ctsview
                    exec ( @command_piece )
                    if @@ERROR <>0
                        return (1)
                    exec ( @command_piece )
                end
            set @command_piece = 'create view ' + @ctsview + ' as select * from dbo.MSmerge_contents '

            /* Are there any published tables not in this publication? */
            if exists (select * from sysmergearticles where pubid <> @pubid and
                       nickname not in (select nickname from sysmergearticles where pubid = @pubid))
                begin
                    /* pubidstr is needed in GUID format */
                    set @pubidstr = '''' + convert(nchar(36), @pubid) + ''''
                    set @command_piece = @command_piece + ' where tablenick in
                        (select nickname from sysmergearticles where pubid = ' + @pubidstr + ')'
                end
            if @generate_per_article = 1
                begin
                    exec ( @command_piece )
                    if @@ERROR <>0
                        return (1)
                    select @ctsview
                end
            else
                begin
                    /* Now select our simple view syntax and we are done with this simple unfiltered case */
                    select @command_piece
                end
            return 0
           end

    /* create temp table to insert into and select commands out of */
    create table #tempcmd (phase int NOT NULL, step int identity NOT NULL, cmdtext nvarchar(4000) NULL)

    if @generate_per_article = 0
        begin
            set @command_piece = 'create view ' + @ctsview + ' as select * from dbo.MSmerge_contents where '
            insert into #tempcmd (phase, cmdtext) values (1, @command_piece)
        end

    /* Initialize for loop over articles in this publication */
    select @tablenick = min(nickname) from sysmergearticles where pubid = @pubid
    while @tablenick is not null
        begin
        select @objid = objid, @view_type = view_type, @view_name = object_name(sync_objid) from
            sysmergearticles where pubid = @pubid and nickname = @tablenick
        select @rowguidcolname = name from syscolumns where id = @objid and
                columnproperty(id, name, 'isrowguidcol')=1

        set @rowguidcolname = QUOTENAME(@rowguidcolname)
        set @view_name = QUOTENAME(@view_name)
        set @tablenickstr = convert(nchar(12), @tablenick)

        if @view_type <> 0
            begin
                if @generate_per_article = 0
                    begin
                        set @command_piece = @or_after_first + '
                        (tablenick = ' + @tablenickstr + ' and rowguid in
                            (select ' + @rowguidcolname + ' from ' + @view_name + '))'
                    end
                else
                    begin
                        select @artid = artid from sysmergearticles where nickname = @tablenick and pubid = @pubid
                        set @artidstr = '''' + convert(nchar(36), @artid) + ''''
                        exec @retcode = dbo.sp_MSguidtostr @artid, @artidstr out
                        if @@ERROR <>0 OR @retcode <> 0
                            return (1)
                        set @ctsview = 'ctsv_' + @pubidstr + @artidstr
                        select @command_piece = N'if object_id(''' + @ctsview + ''') is NOT NULL drop view ' + @ctsview
                        exec ( @command_piece )
                        if @@ERROR <>0
                            return (1)

                        set @command_piece = 'create view dbo.' + @ctsview +
                            ' as select * from dbo.MSmerge_contents where (tablenick = ' + @tablenickstr + ' and rowguid in
                            (select ' + @rowguidcolname + ' from ' + @view_name + ')) '
                        exec ( @command_piece )
                        if @@ERROR <>0
                            return (1)
                    end
            end
        else
            set @command_piece = @or_after_first + '
                (tablenick = ' + @tablenickstr + ')'

        if @generate_per_article = 0
            begin
                insert into #tempcmd (phase, cmdtext) values (2, @command_piece)
            end
        else
            begin
                insert into #tempcmd (phase, cmdtext) values (2, @ctsview)
            end

        /* Advance to next article and repeat the loop */
        select @tablenick = min(nickname) from sysmergearticles where
            pubid = @pubid and nickname > @tablenick

        /* make it so that any subsequent selects in the view are preceded by the word OR */
        set @or_after_first = ' OR '
        end

    /* final steps: select out the text and drop the temp table */
    if @generate_per_article = 0
        begin
            select cmdtext from #tempcmd order by phase, step
        end
    else
        /* Select the view names so that the caller can query them so they can be BCP out and dropped later */
        begin
            select cmdtext from #tempcmd where phase = 2 order by step
        end

    drop table #tempcmd

go
exec dbo.sp_MS_marksystemobject sp_MSmakectsview
go
grant exec on dbo.sp_MSmakectsview to public
go

--------------------------------------------------------------------------------
--. sp_MSdrop_expired_mergesubscription
--------------------------------------------------------------------------------
if exists (select * from sysobjects    where type = 'P'
    and name = 'sp_MSdrop_expired_mergesubscription')
    drop procedure sp_MSdrop_expired_mergesubscription
go

raiserror('Creating procedure sp_MSdrop_expired_mergesubscription', 0,1)
GO

create procedure sp_MSdrop_expired_mergesubscription
AS
/*
** This stored procedure is to periodically check the status of all the subscriptions
** of every merge publication. If any of them is out-of-date, i.e., has lost contact
** with publisher for a certain length of time, we can declare the death of that replica
** and cleanup their traces at the publisher side
*/
declare @subscription_type  int
declare @sub_type           nvarchar(5)
declare @publication        sysname
declare @pubid              uniqueidentifier
declare @subid              uniqueidentifier
declare @status             tinyint
declare @publisher_id       int
declare @subscriber         sysname
declare @subscriber_id      int
declare @subscriber_db      sysname
declare @publisher_db       sysname
declare @distributor        sysname
declare @distribdb          sysname
declare @retention          int  -- in days
declare @retcode            smallint
declare @distproc           nvarchar(255)
declare @localproc          nvarchar(255)
declare @msg                nvarchar(255)
declare @recgen             int
declare @sentgen            int
declare @max_distretention  int
declare @recent_merge       datetime
declare @minus_retention2   datetime
declare @minus_retention    datetime
declare @send_ts            datetime
declare @receive_ts         datetime

/*
** Security Check
*/
    EXEC @retcode = dbo.sp_MSreplcheck_publish
    IF @@ERROR <> 0 or @retcode <> 0
        return (1)



    declare PC CURSOR LOCAL FAST_FORWARD for select DISTINCT p.name, p.pubid, p.retention from sysmergepublications p, sysmergesubscriptions s
        where s.subid=p.pubid and s.pubid=p.pubid for read only
    open PC
    fetch PC into @publication, @pubid, @retention

    WHILE (@@fetch_status <> -1)
        BEGIN
            /* Compute the retention period cutoff dates per publication */
            select @minus_retention2 = dateadd(day, -@retention * 2, getdate())
            select @minus_retention = dateadd(day, -@retention, getdate())

            if @retention is not NULL and @retention > 0
            begin
                    declare SC CURSOR LOCAL FAST_FORWARD for select srvid, db_name, subid, status, subscription_type from sysmergesubscriptions where
                    pubid = @pubid and pubid<>subid for read only
                   open SC
                fetch SC into @subscriber_id, @subscriber_db, @subid, @status, @subscription_type
                WHILE (@@fetch_status <> -1)
                    BEGIN
                        select @subscriber=srvname from master..sysservers where srvid=@subscriber_id
                        if @subscription_type = 0 select @sub_type = 'push' else select @sub_type = 'pull'
                        select @receive_ts = coldate from MSmerge_genhistory where guidsrc = (select recguid from MSmerge_replinfo
                                where repid = @subid)
                        select @sentgen=sentgen from MSmerge_replinfo where repid=@subid
                        select @send_ts = coldate from MSmerge_genhistory where generation=@sentgen
                        if @receive_ts>@send_ts select @recent_merge = @receive_ts
                            else select @recent_merge = @send_ts

                        if @status <> 2 and @recent_merge<@minus_retention
                        begin
                            exec @retcode = dbo.sp_dropmergesubscription
                                    @publication = @publication,
                                    @subscriber = @subscriber,
                                    @subscriber_db = @subscriber_db,
                                    @subscription_type = @sub_type
                               if @retcode <>0 or @@ERROR<>0
                                goto FAILURE
                        end
                        if @recent_merge<@minus_retention2
                        begin
                            delete from sysmergesubscriptions where subid = @subid --delete the row in sysmergesubscription
                               if @@ERROR<>0
                                goto FAILURE
                            delete from MSmerge_replinfo where repid = @subid
                            if @@ERROR<>0
                                goto FAILURE
                        end
                        fetch SC into @subscriber_id, @subscriber_db, @subid, @status, @subscription_type
                    END
                CLOSE SC
                DEALLOCATE SC
            end
            fetch PC into @publication, @pubid, @retention
        END
    CLOSE PC
    DEALLOCATE PC
    return (0)

FAILURE:
    close SC
    deallocate SC
    close PC
    deallocate PC
    return (1)

GO
exec dbo.sp_MS_marksystemobject sp_MSdrop_expired_mergesubscription
go

--------------------------------------------------------------------------------
--. sp_MScleanup_metadata
--------------------------------------------------------------------------------
if exists (select * from sysobjects    where type = 'P'
    and name = 'sp_MScleanup_metadata')
    drop procedure sp_MScleanup_metadata
go

raiserror('Creating procedure sp_MScleanup_metadata', 0,1)
GO

create procedure sp_MScleanup_metadata
@pubid            uniqueidentifier
AS

    declare @retcode                 int
    declare @truncate_flag          int
    declare @artid                  uniqueidentifier
    declare @rcount                    int

    /*
    ** Security Check
    */
    EXEC @retcode = dbo.sp_MSreplcheck_publish
    IF @@ERROR <> 0 or @retcode <> 0
        return (1)

    -- Figure out whether there are any publications which prevent us from truncating the tables
    if @pubid is null
        begin
        if exists (select * from sysmergepublications where
            LOWER(publisher)<> LOWER(@@SERVERNAME) or publisher_db<>db_name())
            set @truncate_flag = 0
        else
            set @truncate_flag = 1
        end
    else
        begin
        if exists (select * from sysmergepublications p where p.pubid <> @pubid and
            (LOWER(publisher)<> LOWER(@@SERVERNAME) or publisher_db<>db_name() or
            not exists (select * from sysmergearticles a1, sysmergearticles a2 where
                a1.pubid = p.pubid and a2.pubid = @pubid and a1.objid = a2.objid)))
            set @truncate_flag = 0
        else
            set @truncate_flag = 1
        end

    if @pubid is not null
        begin
            /* If there are any inprocess generations inform user that queisce did not     succeed - reinit required  */
            if exists (select top 1 guidsrc from dbo.MSmerge_genhistory h
                where    guidlocal='00000000-0000-0000-0000-000000000000'  -- incomplete gen
                    and generation not in (select gen_cur from sysmergearticles)  -- not a     local incomplete gen
                    and coldate in (select login_time from master..sysprocesses)  -- not a     gen that currently receives replica updates from another db
                    and h.art_nick in (select nickname from sysmergearticles where pubid = @pubid)) -- generations relevant to current publication
                begin
                    declare @dbname sysname
                    set @dbname = db_name()
                    RAISERROR(21504, 16, -1, @@servername, @dbname)
                    return (1)
                end
        end
    if @truncate_flag = 1
        begin
        truncate table MSmerge_contents
        truncate table MSmerge_tombstone
        truncate table MSmerge_genhistory
        update sysmergearticles set gen_cur = NULL
        update MSmerge_replinfo set recgen = NULL, recguid = NULL, sentgen = NULL,
    sentguid = NULL
        end
    else
        begin
        -- set up temp table of article nicknames that we delete for
        create table #artnicks (nickname int)
        if @pubid is null
            begin
            -- insert pubids for all local publications
            insert into #artnicks select distinct nickname from sysmergearticles where
                pubid in (select pubid from sysmergepublications where
                    LOWER(publisher)=LOWER(@@SERVERNAME) and publisher_db=db_name())
            end
        else
            begin
            insert into #artnicks select distinct nickname from sysmergearticles where
                pubid = @pubid
            end


        -- do deletions in batches of 5000 to avoid excessive log growth

        set rowcount 5000
        set @rcount = 1
        while @rcount > 0
            begin
            set @rcount = 0

            -- update our cumulative count of rows deleted in this pass with each table
            delete MSmerge_contents WITH (PAGLOCK) from MSmerge_contents where tablenick in (select nickname from #artnicks)
            set @rcount = @@rowcount + @rcount
            delete MSmerge_tombstone WITH (PAGLOCK) from MSmerge_tombstone where tablenick in (select nickname from #artnicks)
            set @rcount = @@rowcount + @rcount
            delete MSmerge_genhistory WITH (PAGLOCK) from MSmerge_genhistory where art_nick in (select nickname from #artnicks)
            set @rcount = @@rowcount + @rcount
            end

        update sysmergearticles set gen_cur = NULL where nickname in (select nickname from #artnicks)

        if @pubid is not null
            update MSmerge_replinfo set recgen = NULL, recguid = NULL, sentgen = NULL, sentguid = NULL
                where repid in ( select subid from sysmergesubscriptions where pubid = @pubid)
        else
            update MSmerge_replinfo set recgen = NULL, recguid = NULL, sentgen = NULL, sentguid = NULL
                where repid in ( select subid from sysmergesubscriptions s, sysmergepublications p
                    where s.pubid = p.pubid and LOWER(p.publisher)=LOWER(@@SERVERNAME) and p.publisher_db=db_name())

        drop table #artnicks
        dbcc dbreindex ( MSmerge_contents )
        end

    execute @retcode = dbo.sp_MSmakegeneration
    if @@ERROR <> 0 or @retcode <> 0
        return (1)
    return 0

GO
exec dbo.sp_MS_marksystemobject sp_MScleanup_metadata
go
grant execute on dbo.sp_MScleanup_metadata to public
go


--------------------------------------------------------------------------------
--. sp_mergecleanupmetadata
--------------------------------------------------------------------------------
if exists (select * from sysobjects    where type = 'P'
    and name = 'sp_mergecleanupmetadata')
    drop procedure sp_mergecleanupmetadata
go

raiserror('Creating procedure sp_mergecleanupmetadata', 0,1)
GO

create procedure sp_mergecleanupmetadata
    @publication                sysname = '%',
    @reinitialize_subscriber    nvarchar(5) = 'true'
AS
declare @pubid            uniqueidentifier
declare @retcode        int
declare @pubname          sysname
declare @snapshot_ready int

declare @artid                  uniqueidentifier
declare @schematype             int
declare @schemaversion          int
declare @schemaguid             uniqueidentifier
declare @schematext             nvarchar(2000)
declare @make_generation         int

set @make_generation = 0

/*
** Security Check
*/
EXEC @retcode = dbo.sp_MSreplcheck_publish
IF @@ERROR <> 0 or @retcode <> 0
    return (1)

if not exists (select * from sysobjects where name='sysmergearticles')
    return (1)

begin TRAN
declare #per_publication CURSOR LOCAL FAST_FORWARD for select pubid
    from sysmergepublications where LOWER(publisher)=LOWER(@@SERVERNAME) and publisher_db=db_name() and name like @publication
open #per_publication
fetch #per_publication into @pubid
while @@fetch_status<>-1
begin
    select @pubname=name, @snapshot_ready=snapshot_ready from sysmergepublications where pubid=@pubid
    if @publication <> '%' and exists (select * from sysmergearticles where pubid<>@pubid and objid in
        (select objid from sysmergearticles where pubid=@pubid))
    begin
        raiserror(21287, 16, -1, @pubname)
        goto FAILURE
    end

    /* Only do this when snapshot has been ran before this operation */
    if @snapshot_ready>0
    begin
        if LOWER(@reinitialize_subscriber)='true'
        begin
            exec @retcode = sp_reinitmergesubscription @publication=@pubname
            if @@ERROR<>0 or @retcode<>0
            begin
                close #per_publication
                deallocate #per_publication
                goto FAILURE
            end
            update sysmergepublications set snapshot_ready=2 where pubid=@pubid
            if @@ERROR<>0
            begin
                close #per_publication
                deallocate #per_publication
                goto FAILURE
            end
        end
        else
        begin
            select @schemaversion = schemaversion from sysmergeschemachange
            if (@schemaversion is NULL)
            set @schemaversion = 1
            else
                select @schemaversion = 1 + max(schemaversion) from sysmergeschemachange
            set @schemaguid = newid()
            set @artid = newid()
            set @schematype = 16 /* metadata cleanup */
            select @schematext = 'exec dbo.sp_MScleanup_metadata '+ '''' + convert(nchar(36),@pubid) + ''''
            exec @retcode=sp_MSinsertschemachange @pubid, @artid, @schemaversion, @schemaguid, @schematype, @schematext
            if @@ERROR<>0 or @retcode<>0
            begin
                close #per_publication
                deallocate #per_publication
                goto FAILURE
            end
        end
    end

    set @make_generation = 1
    fetch next from #per_publication into @pubid
end
close #per_publication
deallocate #per_publication
COMMIT TRAN

if @publication = '%'
    begin
        set @pubid = NULL
    end
else
    begin
        select @pubid = pubid from sysmergepublications
            where LOWER(publisher)=LOWER(@@SERVERNAME) and publisher_db=db_name() and name = @publication
    end

exec @retcode = dbo.sp_MScleanup_metadata @pubid
if @@ERROR <> 0 or @retcode <> 0
        goto FAILURE

if @make_generation = 1
    begin
        execute @retcode = dbo.sp_MSmakegeneration
        if @@ERROR <> 0 or @retcode <> 0
               goto FAILURE
    end
return (0)
FAILURE:
if @@TRANCOUNT = 1
    ROLLBACK TRANSACTION
else
    COMMIT TRANSACTION
return (1)

GO
exec dbo.sp_MS_marksystemobject sp_mergecleanupmetadata
go
grant execute on dbo.sp_mergecleanupmetadata to public
go

--------------------------------------------------------------------------------
--. sp_enumcustomresolvers
--------------------------------------------------------------------------------
if exists (select * from sysobjects    where type = 'P'
    and name = 'sp_enumcustomresolvers')
    drop procedure sp_enumcustomresolvers
go

raiserror('Creating procedure sp_enumcustomresolvers', 0,1)
GO

CREATE PROCEDURE sp_enumcustomresolvers
--    @distributor parameter will be removed in the next version.
    @distributor     sysname = NULL
    AS
    SET NOCOUNT ON

    declare @distributor_rpc sysname
    declare @return_status int
    declare @distproc nvarchar(100)
    declare @retcode int

    select @return_status = 0

    /*
    ** Get the distributor
    */
    EXEC @return_status = dbo.sp_helpdistributor @rpcsrvname = @distributor_rpc OUTPUT
    IF @@error <> 0 OR @return_status <> 0 OR @distributor_rpc IS NULL
    BEGIN
        RAISERROR (20036, 16, -1)
        RETURN (1)
    END


    /*
    ** For a local distributor, query the registry directly
    */
    if @distributor_rpc = @@servername
       begin
        declare @key_exists int
        select @key_exists = 0
        create table #keyexists (keyexists int)
        select @distproc = RTRIM(@distributor_rpc) + '.master..xp_regread'
        insert into #keyexists exec @distproc 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\Replication\ArticleResolver'
        select @key_exists = keyexists from #keyexists
        if (@key_exists = 1)
            begin
                select @distproc = RTRIM(@distributor_rpc) + '.master..xp_regenumvalues'
                exec @distproc 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\Replication\ArticleResolver'
                if @@ERROR<>0 return (1)
            end
        drop table #keyexists
    end
    /*
    ** Since a downlevel publisher can connect to a remote distributor with a higher
    ** version, it is better to return the resolver list using the RPC as opposed
    ** to reading regkeys directly since they might change from version to version.
    */
    else
    begin

        select @distproc = RTRIM(@distributor_rpc) + '.master..sp_enumcustomresolvers'
        exec @distproc
        if @@ERROR<> 0
            return (1)
    end
    RETURN (0)

GO
exec dbo.sp_MS_marksystemobject sp_enumcustomresolvers
go

grant execute on dbo.sp_enumcustomresolvers to public
go

--------------------------------------------------------------------------------
--. sp_MSexpandsubsnb
--------------------------------------------------------------------------------
if exists (select * from sysobjects    where type = 'P'
    and name = 'sp_MSexpandsubsnb')
    drop procedure sp_MSexpandsubsnb
go

raiserror('Creating procedure sp_MSexpandsubsnb', 0,1)
GO

create PROCEDURE sp_MSexpandsubsnb (@pubid uniqueidentifier)
AS
    declare @filterid int
    declare @base_nick int
    declare @join_nick int
    declare @basetable nvarchar(258)
    declare @jointable nvarchar(258)
    declare @join_clause nvarchar(4000)
    declare @retcode int
    declare @base_nickstr nvarchar(10)
    declare @join_unique_key int

    /* get first filter to expand on */
    select @filterid = min(join_filterid) from sysmergesubsetfilters f, #notbelong nb
        where pubid = @pubid and nb.tablenick = f.join_nickname and f.join_filterid > nb.flag

    while @filterid is not null
        begin
        /* get join clause and tables for this filter */
        select @join_nick = join_nickname, @join_clause = join_filterclause,
            @base_nick = art_nickname, @join_unique_key = join_unique_key from sysmergesubsetfilters where
                pubid = @pubid and join_filterid = @filterid
        exec @retcode = dbo.sp_MStablenamefromnick @join_nick, @jointable out, @pubid
        exec @retcode = dbo.sp_MStablenamefromnick @base_nick, @basetable out, @pubid
        set @base_nickstr = convert(nchar(10), @base_nick)

        /* Mark rows so that we know we've expanded those rows for this filter */
        update #notbelong set flag = @filterid where flag < @filterid

        /* exec an insert/select query to expand #notbelong */
        exec ('insert into #notbelong (tablenick, rowguid, flag) select ' +
                @base_nickstr + ', ' + @basetable + '.RowGuidCol, 0 from ' + @basetable +
                ', ' + @jointable + ' where ( ' + @jointable + '.RowGuidCol in
                    (select rowguid from #notbelong) ) and (' + @join_clause + ')')

        /*
        ** if any rows inserted, try to contract the #notbelong table.
        ** Only join filters that are non unique need to contract the
        ** NOTBELONGS table -
        */
        if @@rowcount <> 0 and @join_unique_key = 0
            exec @retcode = dbo.sp_MScontractsubsnb @pubid, @base_nick, @basetable

        /* get next filter to expand with */
        select @filterid = min(join_filterid) from sysmergesubsetfilters f, #notbelong nb
            where pubid = @pubid and nb.tablenick = f.join_nickname and f.join_filterid > nb.flag
        end
go

exec dbo.sp_MS_marksystemobject sp_MSexpandsubsnb
go
grant exec on dbo.sp_MSexpandsubsnb to public
go

--------------------------------------------------------------------------------
--. sp_addmergearticle
--------------------------------------------------------------------------------
if exists (select * from sysobjects    where type = 'P'
    and name = 'sp_addmergearticle')
    drop procedure sp_addmergearticle
go

raiserror('Creating procedure sp_addmergearticle', 0,1)
GO

create procedure sp_addmergearticle
    @publication            sysname,                            /* publication name */
    @article                sysname,                            /* article name */
    @source_object          sysname,                            /* source object name */
    @type                   sysname = 'table',                  /* article type */
    @description            nvarchar(255)= NULL,                /* article description */
    @column_tracking        nvarchar(10) = 'false',             /* column level tracking */
    @status                 nvarchar(10) = 'unsynced',          /* unsynced, active */
    @pre_creation_cmd       nvarchar(10) = 'drop',              /* 'none', 'drop', 'delete', 'truncate' */
    @creation_script        nvarchar(255)= NULL,                /* article schema script */
    @schema_option          binary(8)   = 0x00000000000000f1,   /* article schema creation options */
    @subset_filterclause    nvarchar(2000) = '',                /* filter clause */
    @article_resolver       nvarchar(255)= NULL,                 /* custom resolver for article */
    @resolver_info          nvarchar(255) = NULL,                     /* custom resolver info */
    @source_owner           sysname = NULL
    AS

    set nocount on

    /*
    ** Declarations.
    */
    declare @resolver_info_len        int
    declare @sp_resolver            sysname
    declare @num_columns            smallint
    declare @pubid                  uniqueidentifier                /* Publication id */
    declare @db                     sysname
    declare @object                 sysname
    declare @owner                  sysname
    declare @destination_object     sysname
    declare @retcode                int
    declare @objid                  int
    declare @sync_objid             int
    declare @typeid                 smallint
    declare @nickname               int
    declare @merge_pub_object_bit int
    declare @column_tracking_id     int
    declare @cmd                    nvarchar(255)
    declare @statusid               tinyint
    declare @precmdid               int
    declare @resolver_clsid         nvarchar(50)
    declare @resolver_clsid_old     nvarchar(50)
    declare @tablenick              int
    declare @artid                  uniqueidentifier
    declare @distributor            sysname
    declare @distribdb              sysname
    declare @distproc               nvarchar(255)
    declare @dbname                 sysname
    declare @replinfo               int
    declare @db_name                sysname
    declare @subset                 int
    declare @row_size               int
    declare @sp_name                sysname
    declare @sp_owner                sysname
    declare @qualified_name         nvarchar(257)

    -- PARSENAME VARS
    declare     @UnqualName      nvarchar(258)  --rightmost name node
               ,@QualName1       nvarchar(258)
               ,@QualName2       nvarchar(258)
    -- END PARSENAME VARS

    /*
    ** Initializations
    */
    select @statusid        = 0
    select @resolver_clsid  = NULL
    select @subset          = 1     /* Const: publication type 'subset' */
    select @merge_pub_object_bit    = 128

    select @sp_resolver = 'Microsoft SQLServer Stored Procedure Resolver'

    if @source_owner is NULL
        begin
            select @source_owner = user_name(uid) from sysobjects where id = object_id(@source_object)
            if @source_owner is NULL
                begin
                    raiserror (14027, 11, -1, @source_object)
                    return (1)
                end
        end
    select @qualified_name = QUOTENAME(@source_owner) + '.' + QUOTENAME(@source_object)

    /*
    ** Security Check
    */
    EXEC @retcode = dbo.sp_MSreplcheck_publish
    IF @@ERROR <> 0 or @retcode <> 0
        return (1)

    /*
    ** Parameter Check: @publication.
    ** The @publication id cannot be NULL and must conform to the rules
    ** for identifiers.
    */

    if @publication is NULL
        begin
            raiserror (14043, 16, -1, '@publication')
            return (1)
        end

    select @pubid = pubid from sysmergepublications
        where name = @publication and UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name()
    if @pubid is NULL
        begin
            raiserror (14027, 11, -1, @publication)
            return (1)
        end
    /*
    ** Only publisher can call sp_addmergearticle
    */
    EXEC @retcode = dbo.sp_MScheckatpublisher @pubid
    IF @@ERROR <> 0 or @retcode <>  0
        BEGIN
            RAISERROR (20073, 16, -1)
            RETURN (1)
        END

    select @sync_objid = id from sysobjects where id = OBJECT_ID(@qualified_name)
    if @sync_objid is NULL
        begin
            raiserror (14027, 11, -1, @qualified_name)
            return (1)
        end

    select @row_size=sum(length) from syscolumns where id=OBJECT_ID(@qualified_name)
    if @row_size>6000
        begin
            RAISERROR (21062, 16, -1, @qualified_name)
            -- RETURN (1)
        end

    select @num_columns=count(*) from syscolumns where id = object_id(@qualified_name)
    if @num_columns > 246
        begin
            RAISERROR (20068, 16, -1, @qualified_name, 246)
            RETURN (1)
        end

    /*
    ** Parameter Check: @article.
    ** Check to see that the @article is local, that it conforms
    ** to the rules for identifiers, and that it is a table, and not
    ** a view or another database object.
    */

    if @article is NULL
        begin
            raiserror (20045, 16, -1)
            return (1)
        end

    exec @retcode = dbo.sp_MSreplcheck_name @article
    if @@ERROR <> 0 or @retcode <> 0
        return(1)

    /*
    ** Merge does not really support destination object. It has the same value as source
    */
    select @destination_object = @source_object

    /*
    **  Get the id of the @qualified_name
    */
    select @objid = id, @replinfo = replinfo from sysobjects where id = OBJECT_ID(@qualified_name)
    if @objid is NULL
        begin
            raiserror (14027, 11, -1, @qualified_name)
            return (1)
        end

    /*
    ** If current publication contains a non-sync subscription, all articles to be added in it
    ** has to contain a rowguidcol.
    */
    if exists (select * from sysmergesubscriptions where pubid = @pubid and sync_type = 2)
        begin
            if not exists (select * from syscolumns c
                where c.id=@objid and ColumnProperty(c.id, c.name, 'isrowguidcol') = 1)
                begin
                    raiserror(20085 , 16, -1, @article, @publication)
                    return (1)
                end
        end

    /*
    ** Make sure that the table name specified is a table and not a view.
    */

    if NOT exists (select * from sysobjects
        where id = (select OBJECT_ID(@qualified_name)) AND type = 'U')
        begin
            raiserror (20074, 16, -1)
            return (1)
        end

    /*
    ** Check that the underlying table has no timestamp columns.  If it does,
    ** return an appropriate error.
    */

    if EXISTS (SELECT * FROM syscolumns c WHERE c.id = @sync_objid
                AND type_name(c.xtype) = 'timestamp')
        BEGIN
            RAISERROR (20055, 16, -1, @qualified_name)
            RETURN (1)
        END

    /*
    ** Parameter Check:  @creation_script and @schema_option
    ** @schema_option cannot be null
    ** If @schema_option is 0, there have to be @creation_script defined.
    */
    IF @schema_option IS NULL
    BEGIN
        select @schema_option  = 0x00000000000000f1
    END

    IF @schema_option = 0x0000000000000000
    BEGIN
        RAISERROR (3217, 16, -1, '@schema_option')
        RETURN (1)
    END

    /*
    ** Set the typeid.  The default type is table.  Anything else is
    ** currently undefined (reserved for future use).
    **
    **      @typeid     type
    **      =======     ========
    **          1       table
    ** UNDONE - message
    */

    IF LOWER(@type) NOT IN ('table')
       BEGIN
            RAISERROR (20074, 16, -1)
            RETURN (1)
       END

    IF LOWER(@type) = 'table'
       SET @typeid = 0x0a

    /*
    ** Validate the column tracking
    */
    if @column_tracking IS NULL OR LOWER(@column_tracking) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@column_tracking')
            RETURN (1)
        END
    if LOWER(@column_tracking) = 'true'
        SET @column_tracking_id = 1
    else
        SET @column_tracking_id = 0

    /*
    ** Get the pubid.
    */
    SELECT @pubid = pubid FROM sysmergepublications
        WHERE name = @publication and UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name()
    if @pubid is NULL
        begin
            raiserror (14027, 11, -1, @publication)
            return (1)
        end

    /*
    ** Parameter Check:  @article, @publication.
    ** Check if the article already exists in this publication.
    */

    IF EXISTS (SELECT *
                 FROM sysmergearticles
                WHERE pubid = @pubid
                  AND name = @article)
        BEGIN
            RAISERROR (14030, 16, -1, @article, @publication)
            RETURN (1)
        END

    execute @retcode = dbo.sp_MSgetreplnick @pubid = @pubid, @nickname = @nickname output
    if (@@error <> 0) or @retcode <> 0 or @nickname IS NULL
        begin
        RAISERROR (14055, 11, -1)
        RETURN(1)
        end

    /*
    ** Set the precmdid.  The default type is 'drop'.
    **
    **      @precmdid   pre_creation_cmd
    **      =========   ================
    **            0     none
    **          1     drop
    **          2     delete
    **          3     truncate
    */
    IF LOWER(@pre_creation_cmd) NOT IN ('none', 'drop', 'delete', 'truncate')
       BEGIN
          RAISERROR (14061, 16, -1)
          RETURN (1)
       END

    /*
    ** Determine the integer value for the pre_creation_cmd.
    */
    IF LOWER(@pre_creation_cmd) = 'none'
       select @precmdid = 0
    ELSE IF LOWER(@pre_creation_cmd) = 'drop'
       select @precmdid = 1
    ELSE IF LOWER(@pre_creation_cmd) = 'delete'
       select @precmdid = 2
    ELSE IF LOWER(@pre_creation_cmd) = 'truncate'
       select @precmdid = 3

    /*
    ** Validate the article resolver
    */
    if @article_resolver IS NOT NULL
        begin
            if @article_resolver = 'default' OR @article_resolver = ''
                begin
                    select @article_resolver = NULL
                    select @resolver_clsid = NULL
                end
            else
                begin
                    /*
                    ** Get the distributor info
                    */
                    EXEC @retcode = dbo.sp_helpdistributor @rpcsrvname = @distributor OUTPUT
                    IF @@error <> 0 OR @retcode <> 0 OR @distributor IS NULL
                        BEGIN
                            RAISERROR (20036, 16, -1)
                            RETURN (1)
                    END
                    select @distproc = RTRIM(@distributor) + '.master.dbo.xp_regread'
                    EXECUTE @retcode = @distproc 'HKEY_LOCAL_MACHINE',
                                  'SOFTWARE\Microsoft\MSSQLServer\Replication\ArticleResolver',
                                  @article_resolver,
                                  @param = @resolver_clsid  OUTPUT

                    /* if xp_regread failed, is it because distributor server is running on Shiloh */
                    IF @retcode <> 0 or @resolver_clsid IS NULL
                        begin
                            EXECUTE @retcode = @distproc 'HKEY_LOCAL_MACHINE',
                                'SOFTWARE\Microsoft\Microsoft SQL Server\80\Replication\ArticleResolver',
                                @article_resolver,
                                @param = @resolver_clsid  OUTPUT
                            IF @retcode <> 0 or @resolver_clsid IS NULL
                                BEGIN
                                    select @distproc =  RTRIM(@distributor) + '.master.dbo.sp_MSread_resolver_clsid'
                                    exec @retcode=@distproc @article_resolver,@param = @resolver_clsid OUTPUT
                                    if @retcode<>0 or @@ERROR<>0 or @resolver_clsid is NULL
                                        begin
                                              RAISERROR (20020, 16, -1)
                                              RETURN (1)
                                          end
                                END
                        end
                end
        end

        /*
        ** If article resolver is 'SP resolver', make sure that resolver_info refers to an SP or XP;
        ** Also make sure it is stored with owner qualification
        */
        if  @article_resolver = @sp_resolver
            begin
                if not exists (select * from sysobjects where id = object_id(@resolver_info) and ( type = 'P' or type = 'X'))
                    begin
                        select @resolver_info_len = datalength(@resolver_info)
                        raiserror(2812, 16, -1, @resolver_info_len, @resolver_info)
                        return (1)
                    end

                select @sp_name = name, @sp_owner=user_name(uid) from sysobjects where id = object_id(@resolver_info)
                select @resolver_info = QUOTENAME(@sp_owner) + '.' + QUOTENAME(@sp_name)
            end
    /*
    ** Validate the resolver procedure for the article - should be either a stored proc or an extended procedure.
    */
    if @resolver_info IS NOT NULL
        begin
            /*
            ** Get the distributor info
            */
            EXEC @retcode = dbo.sp_helpdistributor @rpcsrvname = @distributor OUTPUT
            IF @@error <> 0 OR @retcode <> 0 OR @distributor IS NULL
                BEGIN
                    RAISERROR (20036, 16, -1)
                    RETURN (1)
            END
            select @distproc = RTRIM(@distributor) + '.master.dbo.xp_regread'
            /* Don't force different resolver than one that was passed in. */
        end


    /*
    **  Add article to sysmergearticles and update sysobjects category bit.
    */
    begin tran
    save TRAN sp_addmergearticle
        select @artid = artid from sysmergearticles where objid = OBJECT_ID(@qualified_name)
        select @statusid = 1  /*default status is inactive */
        if @artid is NULL
            begin
                set @artid = newid()
                if @@ERROR <> 0
                    goto FAILURE
                execute @retcode = dbo.sp_MSgentablenickname @tablenick output, @nickname, @objid
                if @@ERROR <> 0 OR @retcode <> 0
                    goto FAILURE
            end
        /* Clone the article properties if article has already been published (in a different pub) */
        else
            begin
            /*
            ** Parameter Check:  @article, @publication.
            ** Check if the table already exists in this publication.
            */
            if exists (select * from sysmergearticles
                where pubid = @pubid AND artid = @artid)
                begin
                    raiserror (14030, 16, -1, @article, @publication)
                    goto FAILURE
                end

            /* Make sure that coltracking option matches */
            if exists (select * from sysmergearticles where artid = @artid and
                         column_tracking <> @column_tracking_id)
                begin
                    raiserror (20030, 16, -1, @article)
                    goto FAILURE
                end

            /* Reuse the article nickname if article has already been published (in a different pub)*/
            select @tablenick = nickname from sysmergearticles where artid = @artid
            if @tablenick IS NULL
                goto FAILURE

            /* Make sure that @resolver_clsid matches the existing resolver_clsid */
            select @resolver_clsid_old = resolver_clsid from sysmergearticles where artid = @artid
            if ((@resolver_clsid IS NULL AND @resolver_clsid_old IS NOT NULL) OR
                (@resolver_clsid IS NOT NULL AND @resolver_clsid_old IS NULL) OR
                (@resolver_clsid IS NOT NULL AND @resolver_clsid_old IS NOT NULL AND @resolver_clsid_old <> @resolver_clsid))
                begin
                    raiserror (20037, 16, -1, @article)
                    goto FAILURE
                end

            /* Insert to articles, copying some stuff from other article row */
            set rowcount 1
            insert into sysmergearticles (name, type, objid, sync_objid, artid, description,
                    pre_creation_command, pubid, nickname, column_tracking, status,
                    conflict_table, creation_script, conflict_script, article_resolver,
                    resolver_clsid, ins_conflict_proc, schema_option,
                    destination_object, subset_filterclause, view_type, resolver_info, gen_cur)
                -- use top 1, distinct could return more than one matching row if status different on partitioned articles
                select top 1 @article, type, objid, @sync_objid, @artid, @description, @precmdid,
                    @pubid, nickname, column_tracking, 1, conflict_table, @creation_script,
                    conflict_script, article_resolver, resolver_clsid, ins_conflict_proc,
                    @schema_option, @destination_object, @subset_filterclause, 0, resolver_info, gen_cur
                    from sysmergearticles where artid = @artid
            set rowcount 0

            /* Jump to end of transaction  */
            goto DONE_TRAN
            end

        /* Add the specific GUID based replication columns to sys articles */
        insert sysmergearticles (name, objid, sync_objid, artid, type, description, pubid, nickname,
                column_tracking, status, schema_option, pre_creation_command, destination_object,
                article_resolver, resolver_clsid, subset_filterclause, view_type, resolver_info)
        values (@article, @objid, @sync_objid, @artid, @typeid, @description, @pubid, @tablenick,
                @column_tracking_id, @statusid, @schema_option, @precmdid, @destination_object,
                @article_resolver, @resolver_clsid, @subset_filterclause, 0, @resolver_info)
        if @@ERROR <> 0
            goto FAILURE

        exec @retcode = dbo.sp_replupdateschema @qualified_name
        if @@ERROR <> 0 or @retcode <> 0
            goto FAILURE
        update sysobjects set replinfo = (replinfo | @merge_pub_object_bit) where id = @objid
        if @@ERROR <> 0
            goto FAILURE
        /* Make a generation */
        execute @retcode = dbo.sp_MSmakegeneration
        if @@ERROR <> 0
            goto FAILURE

                /* If the article status is active then publish the user tables */
        if @status = 'active'
            begin
                /* Get a holdlock on the underlying table */
                select @cmd = 'select * into #tab1 from '
                select @cmd = @cmd + @qualified_name
                select @cmd = @cmd + '(TABLOCK HOLDLOCK) where 1 = 2 '
                execute(@cmd)

                /* Add the guid column to the user table */
                execute @retcode = dbo.sp_MSaddguidcolumn @source_owner, @source_object
                if @@ERROR <> 0 OR  @retcode <> 0  -- NOTE: new change
                    goto FAILURE

                /* Create an index on the rowguid column in the user table */
                execute @retcode = dbo.sp_MSaddguidindex @source_owner, @source_object
                if @@ERROR <> 0 OR @retcode <> 0
                    goto FAILURE

                /* Create the merge triggers on the base table */
                execute @retcode = dbo.sp_MSaddmergetriggers @qualified_name, @column_tracking_id
                if @@ERROR <> 0 OR @retcode <> 0
                    goto FAILURE

                /* Create the merge insert/update stored procedures for the base table */
                execute @retcode = dbo.sp_MSsetartprocs @publication, @article
                if @@ERROR <> 0 OR @retcode <> 0
                    goto FAILURE

                /* Set the article status to be active so that Snapshot does not do this again */
                select @statusid = 2 /* Active article */
                update sysmergearticles set status = @statusid where artid = @artid
                if @@ERROR <> 0
                    goto FAILURE
            end

DONE_TRAN:
        /*
        ** For articles with subset filter clause - set the pub type to subset
        */
        if len(@subset_filterclause) > 0
            begin
                execute @retcode = dbo.sp_MSsubsetpublication @publication
                if @@ERROR <> 0 or @retcode<>0
                    goto FAILURE
            end
        /*
        ** Get distribution server information for remote RPC call.
        */
        EXECUTE @retcode = dbo.sp_helpdistributor @rpcsrvname = @distributor OUTPUT,
           @distribdb   = @distribdb OUTPUT
        IF @@ERROR <> 0 or @retcode <> 0
            BEGIN
                goto FAILURE
            END

        SELECT @dbname =  DB_NAME()

        SELECT @distproc = RTRIM(@distributor) + '.' + @distribdb +
            '.dbo.sp_MSadd_article'
        EXECUTE @retcode = @distproc
            @publisher = @@SERVERNAME,
            @publisher_db = @dbname,
            @publication = @publication,
            @article = @article,
            @destination_object = @destination_object,
            @source_owner = @source_owner,
            @source_object = @source_object,
            @description = @description
            -- @article_id = NULL
        IF @@ERROR <> 0 or @retcode <> 0
            BEGIN
                goto FAILURE
            END

        COMMIT TRAN

        /* If the article status is active adding the merge triggers to the base table */

        return (0)
FAILURE:
        RAISERROR (20009, 16, -1, @article, @publication)
        if @@TRANCOUNT > 0
        begin
            ROLLBACK TRANSACTION sp_addmergearticle
            COMMIT TRANSACTION
        end
        return (1)
go

exec dbo.sp_MS_marksystemobject sp_addmergearticle
go

grant execute on dbo.sp_addmergearticle to public
go

--------------------------------------------------------------------------------
--. sp_MSaddinitialpublication
--------------------------------------------------------------------------------
if exists (select * from sysobjects    where type = 'P'
    and name = 'sp_MSaddinitialpublication')
    drop procedure sp_MSaddinitialpublication
go

raiserror('Creating procedure sp_MSaddinitialpublication', 0,1)
GO

CREATE PROCEDURE sp_MSaddinitialpublication(
    @publisher                sysname,
    @publisher_db             sysname,
    @publication            sysname,             /* Name of the publication */
    @description            nvarchar(255),         /* Description of the publication */
    @pubid                     uniqueidentifier,    /* Publication ID */
    @retention                int,                /* Retention period of the publication */
    @sync_mode                int,                /* Sync mode of the publication */
    @allow_push                int,                /* does publication allow push ? */
    @allow_pull                int,                /* does publication allow pull ? */
    @allow_anonymous        int,                /* does publication allow anonymous ? */
    @centralized_conflicts    int,                 /* publication does centralized conflicts ? */
    @status                    int,                 /* publication's status */
    @snapshot_ready            int,                 /* publication snapshto_ready flag ? */
    @enabled_for_internet    int,                 /* publication enabled_for_internet flag ? */
    @publication_type        int                    /* a full publication or a partial one */
    ) AS

    SET NOCOUNT ON
    declare @retcode int
    declare @publisher_srvid int
    /*
    ** NOTE -- WORKAROUND ODBC BUG WHICH have not been reproed.
    */
    select @publication = RTRIM(@publication)
    select @publisher_db = RTRIM(@publisher_db)

    select @publisher_srvid = srvid from master..sysservers where UPPER(srvname) = UPPER(@publisher)
        begin
        EXECUTE @retcode = dbo.sp_addserver @publisher, @duplicate_ok='duplicate_ok'

        IF @@error <> 0 OR @retcode <> 0
            BEGIN
                RAISERROR (14042, 16, -1)
                RETURN (1)
            END
        end

    select @publisher_srvid = srvid from master..sysservers where UPPER(srvname) = UPPER(@publisher)
    IF @publisher_srvid IS NULL
        BEGIN
            RAISERROR (14010, 16, -1)
            RETURN (1)
        END
    /*
    ** Check for subscribing permission
    */
    exec @retcode=sp_MSreplcheck_subscribe
    if @retcode<>0 or @@ERROR<>0 return (1)

    /*
    ** Populate the local copy of sysmergepublications
    */
    BEGIN TRAN
    save TRAN MSaddinitialpublication

    if exists (select * from sysmergepublications
            where name = @publication and UPPER(publisher)=UPPER(@publisher) and publisher_db=@publisher_db)
        begin
            declare @pubid_local uniqueidentifier
            select @pubid_local = pubid
                from sysmergepublications where name = @publication and UPPER(publisher)=UPPER(@publisher) and publisher_db=@publisher_db

            if exists (select * from sysmergesubscriptions
                where pubid = @pubid and srvid = @publisher_srvid and db_name = @publisher_db)
                    begin
                        delete from sysmergesubscriptions
                            where pubid = @pubid and srvid = @publisher_srvid and db_name = @publisher_db
                        IF @@ERROR <> 0
                            BEGIN
                                RAISERROR (14057, 16, -1)
                                goto FAILURE
                            END
                    end
            update sysmergesubscriptions    SET pubid = @pubid where pubid = @pubid_local
            IF @@ERROR <> 0
                BEGIN
                    RAISERROR (14057, 16, -1)
                    goto FAILURE
                END

            if @pubid <> @pubid_local
                delete from sysmergesubscriptions where subid = @pubid

            update sysmergesubscriptions    SET subid = @pubid where subid = @pubid_local
            IF @@ERROR <> 0
                BEGIN
                    RAISERROR (14057, 16, -1)
                    goto FAILURE
                END

            update sysmergesubscriptions
                SET partnerid = @pubid
                where partnerid = @pubid_local

            IF @@ERROR <> 0
                BEGIN
                    RAISERROR (14057, 16, -1)
                    goto FAILURE
                END

            update sysmergepublications
                SET pubid = @pubid,
                    name = @publication,
                    description = @description,
                    designmasterid = @pubid,
                    retention = @retention,
                    parentid = pubid,
                    sync_mode = sync_mode,
                    allow_push = @allow_push,
                    allow_pull = @allow_pull,
                    allow_anonymous = @allow_anonymous,
                    centralized_conflicts = @centralized_conflicts,
                    status = @status,
                    snapshot_ready = @snapshot_ready,
                    enabled_for_internet = @enabled_for_internet,
                    publication_type = @publication_type
                where name = @publication
                      and UPPER(publisher) = UPPER(@publisher)
                      and publisher_db = @publisher_db
        end
    else
        begin
            insert sysmergepublications(publisher, publisher_db,pubid, name, description, designmasterid,
                retention, parentid, sync_mode, allow_push, allow_pull, allow_anonymous,
                centralized_conflicts, status, snapshot_ready, enabled_for_internet, publication_type)
            values(@publisher, @publisher_db, @pubid, @publication, @description, @pubid,
                @retention, @pubid, @sync_mode, @allow_push, @allow_pull, @allow_anonymous,
                @centralized_conflicts, @status, @snapshot_ready, @enabled_for_internet, @publication_type)
        end
    IF @@ERROR <> 0
        BEGIN
            RAISERROR (14057, 16, -1)
            goto FAILURE
        END
    COMMIT TRAN

    RETURN (0)

FAILURE:
    /* UNDONE : This code is specific to 6.X nested transaction semantics */
    if @@TRANCOUNT > 0
    begin
        ROLLBACK TRANSACTION MSaddinitialpublication
        COMMIT TRANSACTION
    end
    RETURN (1)
go
exec dbo.sp_MS_marksystemobject sp_MSaddinitialpublication
go
grant exec on dbo.sp_MSaddinitialpublication to public
go

--------------------------------------------------------------------------------
--. sp_MSgetconflictinsertproc
--------------------------------------------------------------------------------
if exists (select * from sysobjects    where type = 'P'
    and name = 'sp_MSgetconflictinsertproc')
    drop procedure sp_MSgetconflictinsertproc
go

raiserror('Creating procedure sp_MSgetconflictinsertproc', 0,1)
GO

CREATE PROCEDURE sp_MSgetconflictinsertproc (
    @artid                 uniqueidentifier,
    @pubid                uniqueidentifier=NULL, --added for Sphinx SP3
    @output    int = 1
    ) AS
    declare @conflict_table sysname
    declare @dbname            sysname
    declare @conflict_proc    sysname
    declare @owner             sysname
    declare @object            sysname
    declare @retcode         int
    declare    @basetableid    int

    -- PARSENAME VARS
    declare @UnqualName     nvarchar(258)  --rightmost name node
    declare @QualName1      nvarchar(258)
    declare @command        nvarchar(4000)
    declare @QualName2      nvarchar(258)
    -- END PARSENAME VARS

    declare @guidstr varchar(40)
    exec @retcode=sp_MSguidtostr @artid, @guidstr out
    if @retcode<>0 or @@ERROR<>0 return (1)

    /*
    ** Check to see if current publication has permission
    */
    exec @retcode=sp_MSreplcheck_connection
        @artid = @artid
    if @retcode<>0 or @@ERROR<>0 return (1)

    select @conflict_table = conflict_table, @conflict_proc = ins_conflict_proc, @basetableid = objid from
        sysmergearticles where artid = @artid

    if @@ERROR <> 0
        return (1)

    -- Create an index on the conflict table if it doesn't have one
    if (OBJECT_ID(@conflict_table) is not null) and
        not exists (select * from sysindexes where id = OBJECT_ID(@conflict_table) and keys is not null)
        begin
        declare @rgcol sysname
        declare @indname sysname
        declare @quotedname sysname

        select @rgcol = QUOTENAME(name) from syscolumns where id = @basetableid and
                ColumnProperty(id, name, 'isrowguidcol') = 1
        select @indname = 'uc_' + @conflict_table
        set @indname = QUOTENAME(@indname)
        set @quotedname = QUOTENAME(@conflict_table)
        exec ('Create unique clustered index ' + @indname + ' on ' + @quotedname +
                ' (' + @rgcol + ', origin_datasource)' )
        if @@error <> 0
            return (1)
        end
    if ((OBJECT_ID(@conflict_proc) is null) and (OBJECT_ID(@conflict_table) is not null))
        begin

        select @UnqualName = PARSENAME(@conflict_table, 1)
        select @QualName1 = PARSENAME(@conflict_table, 2)
        if @UnqualName IS NULL
             return 1

        -- fixup for variable length differences.  remove when vars expanded
        -- to new SQL SERVER 7.0 lengths

        select @owner =  @QualName1
        select @object = @UnqualName

        -- first set up the procedure name variable
        select @conflict_proc = 'sp_cft_' + @guidstr
        exec @retcode=sp_MSuniqueobjectname @conflict_proc , @conflict_proc output
        if @@ERROR <> 0 OR @retcode <> 0
            return(1)
        set @dbname = db_name()
        if @owner is NULL
            set @command = 'sp_MSmakeconflictinsertproc ' + QUOTENAME(@conflict_table) + ' , NULL, ' + @conflict_proc  + ' , ' + convert(nvarchar,@basetableid)
        else
            set @command = 'sp_MSmakeconflictinsertproc ' + QUOTENAME(@conflict_table) + ' , ' + QUOTENAME(@owner) + ' , ' + @conflict_proc  + ' , ' + convert(nvarchar,@basetableid)
        exec @retcode = master..xp_execresultset @command, @dbname
        if @@ERROR<>0 OR @retcode<>0
        begin
            return (1)
        end
        exec @retcode = dbo.sp_MS_marksystemobject  @conflict_proc
        if @@ERROR<>0  return (1)
        exec ('grant exec on ' + @conflict_proc + ' to public')
        update sysmergearticles set ins_conflict_proc = @conflict_proc where artid = @artid
    end

    if @output = 1
        select @conflict_table, @conflict_proc
    if @@ERROR <> 0
        return (1)
    return (0)

go
exec dbo.sp_MS_marksystemobject sp_MSgetconflictinsertproc
go
grant exec on dbo.sp_MSgetconflictinsertproc to public
go

--------------------------------------------------------------------------------
--. sp_MShelpcreatebeforetable
--------------------------------------------------------------------------------
if exists (select * from sysobjects    where type = 'P'
    and name = 'sp_MShelpcreatebeforetable')
    drop procedure sp_MShelpcreatebeforetable
go

raiserror('Creating procedure sp_MShelpcreatebeforetable', 0,1)
GO
create procedure sp_MShelpcreatebeforetable
    @objid int,
    @newname sysname
AS
    declare @command nvarchar(1000)
    declare @retcode int
    declare @include int
    declare @tablenick int
    declare @colpat nvarchar(130)
    declare @colname sysname
    declare @typename sysname
    declare @colid smallint
    declare @colidstr nvarchar(3)
    declare @status tinyint
    declare @len smallint
    declare @prec smallint
    declare @scale int
    declare @cMaxIndexLength int
    declare @isnullable tinyint

    set nocount on

    set @cMaxIndexLength= 900
    select @tablenick = max(nickname) from sysmergearticles where objid = @objid
    if @tablenick is null return (1)

    -- create temp table to select the command text out of
    create table #tempcmd (phase int NOT NULL, step int identity NOT NULL, cmdtext nvarchar(4000) NULL)

    set @command = 'create table ' + @newname + '('
    insert into #tempcmd (phase, cmdtext) values (1, @command)

    -- Loop over the columns and see which ones we include
    declare col_cursor CURSOR LOCAL FAST_FORWARD for select name, status, type_name(xtype), length,
         prec, scale, isnullable, colid from syscolumns
    where id = @objid and iscomputed <> 1 and type_name(xtype) <> 'timestamp' order by colid
    FOR READ ONLY

    open col_cursor
    fetch next from col_cursor into @colname, @status, @typename, @len, @prec, @scale, @isnullable, @colid
    while (@@fetch_status <> -1)
        begin
        set @include = 0
        set @colpat = '%' + @colname + '%'
        if columnproperty( @objid, @colname , 'isrowguidcol')=1
            begin
            set @include = 1
            end
        else
            begin
            -- does updating this column change membership in a partial replica?
            if exists (select * from sysmergearticles
                where objid = @objid and subset_filterclause like @colpat)
                set @include = 1
            else if exists (select * from sysmergesubsetfilters
                where art_nickname = @tablenick and join_filterclause like @colpat)
                set @include = 1
            else if exists (select * from sysmergesubsetfilters
                 where join_nickname = @tablenick and join_filterclause like @colpat)
                set @include = 1
            end
        -- If we want this column, map its type and insert a row to temp table
        if @include = 1
            begin
            if @typename='nvarchar' or @typename='nchar' -- a unit of nchar takes 2 bytes
                set @len = @len/2
            exec @retcode = dbo.sp_MSmaptype @typename out, @len, @prec, @scale
            if @@ERROR<>0 or @retcode<>0 return (1)
            if @typename not in ('text', 'ntext','image')
                begin
                set @colname = QUOTENAME(@colname)
                if @isnullable = 1
                    set @command = @colname + ' ' + @typename + ' NULL, '
                else
                    set @command = @colname + ' ' + @typename + ' NOT NULL, '

                -- Insert the part of create table command for this column
                insert into #tempcmd (phase, cmdtext) values (1, @command)

                -- Insert a create index command
            if (@len <= @cMaxIndexLength)
                    begin
                    set @colidstr =convert(nvarchar(3), @colid)
                    set @command = 'create index ' + @newname + '_' + @colidstr + ' on ' + @newname +
                        ' (' + @colname + ')
                            '
                    insert into #tempcmd (phase, cmdtext) values (2, @command)
                    end
                end
            end
        /* Repeat the loop with next column */
        fetch next from col_cursor into @colname, @status, @typename, @len, @prec, @scale, @isnullable, @colid
        end
    close col_cursor
    deallocate col_cursor

    -- Insert last column, generation
    set @command = 'generation int NOT NULL)
        '
    insert into #tempcmd (phase, cmdtext) values (1, @command)
    set @command = 'create index ' + @newname + '_gen on ' + @newname + '(generation)
        '
    insert into #tempcmd (phase, cmdtext) values (2, @command)

    select cmdtext from #tempcmd order by phase, step
go
exec dbo.sp_MS_marksystemobject sp_MShelpcreatebeforetable
go

--------------------------------------------------------------------------------
--. sp_MShelpalterbeforetable
--------------------------------------------------------------------------------
if exists (select * from sysobjects    where type = 'P'
    and name = 'sp_MShelpalterbeforetable')
    drop procedure sp_MShelpalterbeforetable
go

raiserror('Creating procedure sp_MShelpalterbeforetable', 0,1)
GO
create procedure sp_MShelpalterbeforetable
    @objid int,
    @biname sysname
AS
    declare @command nvarchar(1000)
    declare @retcode int
    declare @include int
    declare @tablenick int
    declare @colpat nvarchar(130)
    declare @colname sysname
    declare @typename sysname
    declare @colid smallint
    declare @colidstr nvarchar(3)
    declare @status tinyint
    declare @len smallint
    declare @prec smallint
    declare @scale int
    declare @cMaxIndexLength int
    declare @isnullable tinyint
    declare @bi_objid int
    set nocount on

    select @tablenick = max(nickname) from sysmergearticles where objid = @objid
    if @tablenick is null
        return (1)

    select @bi_objid = OBJECT_ID(@biname)
    set @cMaxIndexLength= 900

    -- Loop over the columns and see which ones we include
    declare col_cursor CURSOR LOCAL FAST_FORWARD for select name, status, type_name(xtype), length,
         prec, scale, isnullable, colid from syscolumns
    where id = @objid and iscomputed <> 1 and type_name(xtype) <> 'timestamp' order by colid
    FOR READ ONLY

    open col_cursor
    fetch next from col_cursor into @colname, @status, @typename, @len, @prec, @scale, @isnullable, @colid
    while (@@fetch_status <> -1)
        begin
        set @include = 0
        set @colpat = '%' + @colname + '%'

        -- Is this column already in the before image table?
        if exists (select * from syscolumns where id = @bi_objid and name = @colname)
            begin
            goto fetchnext
            end

        -- does updating this column change membership in a partial replica?
        if exists (select * from sysmergearticles
            where objid = @objid and subset_filterclause like @colpat)
            set @include = 1
        else if exists (select * from sysmergesubsetfilters
            where art_nickname = @tablenick and join_filterclause like @colpat)
            set @include = 1
        else if exists (select * from sysmergesubsetfilters
            where join_nickname = @tablenick and join_filterclause like @colpat)
            set @include = 1

        -- If we want this column, map its type and insert a row to temp table
        if @include <> 1
            begin
            goto fetchnext
            end
        if @typename='nvarchar' or @typename='nchar' -- a unit of nchar takes 2 bytes
            set @len = @len/2
        exec @retcode = dbo.sp_MSmaptype @typename out, @len, @prec, @scale
        if @@ERROR<>0 or @retcode<>0 goto errlabel
        if @typename not in ('text', 'ntext','image')
        begin
            set @colname = QUOTENAME(@colname)

            -- Always make columns nullable when we add them because we might have
            -- existing rows in the before image table.
            set @command = 'alter table ' + @biname + ' ADD ' + @colname + ' ' + @typename + ' NULL '
            exec (@command)
                if @@error <> 0 goto errlabel

            -- Insert a create index command if column is not too long
            if (@len <= @cMaxIndexLength)
            begin
                set @colidstr =convert(nvarchar(3), @colid)
                 set @command = 'create index ' + @biname + '_' + @colidstr + ' on ' + @biname + ' (' + @colname + ')'
                execute ( @command )
                if @@ERROR<>0 goto errlabel
            end
        end
fetchnext:
        /* Repeat the loop with next column */
        fetch next from col_cursor into @colname, @status, @typename, @len, @prec, @scale, @isnullable, @colid
        end
    close col_cursor
    deallocate col_cursor
    return 0
errlabel:
    close col_cursor
    deallocate col_cursor
    return 1

go
exec dbo.sp_MS_marksystemobject sp_MShelpalterbeforetable
go

--------------------------------------------------------------------------------
--. sp_MSread_resolver_clsid
--------------------------------------------------------------------------------
if exists (select * from sysobjects    where type = 'P'
    and name = 'sp_MSread_resolver_clsid')
    drop procedure sp_MSread_resolver_clsid
go

raiserror('Creating procedure sp_MSread_resolver_clsid', 0,1)
GO


create procedure sp_MSread_resolver_clsid (
@article_resolver sysname,
@resolver_clsid nvarchar(50)
)
AS
    RAISERROR (20020, 16, -1)
    RETURN (1)
GO
exec dbo.sp_MS_marksystemobject sp_MSread_resolver_clsid
go


--------------------------------------------------------------------------------
--. sp_MSsetupbelongs
--------------------------------------------------------------------------------
if exists (select * from sysobjects    where type = 'P'
    and name = 'sp_MSsetupbelongs')
    drop procedure sp_MSsetupbelongs
go

raiserror('Creating procedure sp_MSsetupbelongs', 0,1)
GO

-- Modify temp table. No security check needed.
create procedure sp_MSsetupbelongs
    @publisher            sysname,
    @publisher_db        sysname,
    @publication         sysname,
    @genlist             varchar(1000),
    @commongen            int,
    @subissql            int

AS
    declare @pubid uniqueidentifier
    declare @temp_id int
    declare @retval int
    declare @tablenick int
    declare @tnstr nvarchar(12)
    declare @rowguid uniqueidentifier
    declare @rowguidstr nvarchar(40)
    declare @belongsname sysname
    declare @belongs_uniquename sysname
    declare @notbelongsname sysname
    declare @notbelongs_uniquename sysname
    declare @artnick    int
    declare @artnickstr nvarchar(10)
    declare @artviewobjid int
    declare @before_view_objid int
    declare @before_view_name sysname
    declare @procname sysname
    declare @sync_objid int
-- DEBUG declare @belongcnt int
-- DEBUG declare @nb_cnt int
    declare @artbaseobjid int
    declare @artviewname sysname
    declare @artviewowner sysname

    declare @commongenstr nvarchar(12)
    declare @partchangegen int
    declare @joinchangegen int
    declare @rgcol sysname
    declare @maxfilterid int
    declare @view_type int
    declare @temp_view int
    declare @retcode smallint
    declare @dynamic_join_cnt int

    /*
    ** To public
    */

    set @temp_view = 2
-- DEBUG insert into master.dbo.debuglog2 (publisher, pubdb, hostname, twhen, commongen, belong_cnt, nb_cnt, genlist) values
-- DEBUG (@publisher, @publisher_db, host_name(), getdate(), @commongen, NULL, NULL, @genlist)

    select @pubid = pubid from sysmergepublications where name = @publication and UPPER(publisher)=UPPER(@publisher) and publisher_db=@publisher_db
    set @commongenstr = convert(nchar(12), @commongen)
    select @dynamic_join_cnt = count(*) from sysmergesubsetfilters where pubid = @pubid and
        (UPPER(join_filterclause) like '%USER_%NAME%' or
         UPPER(join_filterclause) like '%USER_%ID%' or
         UPPER(join_filterclause) like '%SESSION_USER%' or
         UPPER(join_filterclause) like '%SYSTEM_USER%')

    /* Set up the ##belong and ##notbelong tables; return names as rowset */
    /* step 1 make temptable names, create tables */
    set @rowguid = newid()
    exec @retcode=sp_MSguidtostr @rowguid, @rowguidstr out
    if @retcode<>0 or @@ERROR<>0 return (1)

    set @belongsname = '##belong' + @rowguidstr
    set @belongs_uniquename = 'bunique_' + @rowguidstr
    exec @retcode = dbo.sp_MSuniquetempname @belongsname, @belongsname out
    IF @@ERROR<>0 OR @retcode<>0 return (1)
    create table #belong (tablenick int NOT NULL, rowguid uniqueidentifier NOT NULL, flag int NOT NULL,
        partchangegen int null, joinchangegen int null, skippedexpand int null)
    create index #indbelong on #belong (rowguid, tablenick)

    exec ('create table ' + @belongsname + ' (tablenick int NOT NULL, rowguid uniqueidentifier NOT NULL,
        generation int NULL, lineage varbinary(255) NULL, colv varbinary(2048) NULL) ')
    if @@ERROR <>0 return (1)
    create table #notbelong (tablenick int NOT NULL, rowguid uniqueidentifier NOT NULL,
        flag int NOT NULL, partchangegen int null, joinchangegen int null, type int default 5)
    create index #indnbelong on #notbelong (tablenick, rowguid)
    create table #genlist (generation int)
    exec ('insert into #genlist select distinct generation from MSmerge_genhistory where
            generation in (' + @genlist + ')')

    set @notbelongsname = '##notbelong' + @rowguidstr
    set @notbelongs_uniquename = 'nbunique_' + @rowguidstr
    exec @retcode = dbo.sp_MSuniquetempname @notbelongsname, @notbelongsname out
    IF @@ERROR<>0 OR @retcode<>0 return (1)
    exec ('create table ' + @notbelongsname + ' (bookmark int identity  primary key NOT NULL, tablenick int NOT NULL, rowguid uniqueidentifier NOT NULL,
        generation int NULL, lineage varbinary(255) NULL, type tinyint NOT NULL) ')
    if @@ERROR <>0 return (1)
    create table #temp_cont (temp_id int identity NOT NULL, tablenick int NOT NULL, rowguid uniqueidentifier NOT NULL,
            partchangegen int null, joinchangegen int null)

    /* step 2 setup pass through MSmerge_contents */
    /* article with permanent views can be handled with bulk inserts */
    set @artnick = 0
    set rowcount 0

    /* Get first article, go into loop */
    select @artnick = min(nickname) from sysmergearticles where pubid = @pubid and
         nickname > @artnick

    if @@ERROR <>0 return (1)


    while (@artnick is not null)
        begin
        set @artnickstr = convert(nchar(10), @artnick)
        select @artviewobjid = sync_objid, @artbaseobjid = objid, @view_type = view_type,
            @procname = view_sel_proc, @sync_objid=sync_objid, @before_view_objid = before_view_objid from sysmergearticles where
            pubid = @pubid and nickname = @artnick
        /* Get name of rowguidcol. Aliasing doesn't work through a view. */
        select @rgcol = name from syscolumns where id = @artbaseobjid and ColumnProperty(@artbaseobjid, name, 'isrowguidcol') = 1

        /* UNDONE get view type from sysmergearticle to see if permanent view to use */
        set @artviewname = NULL
        if @view_type <> @temp_view
            select @artviewname = name, @artviewowner = user_name(uid) from sysobjects where
                id = @artviewobjid
        if (@procname is not null)
            begin
            exec @retcode = @procname @artnick
            if @@ERROR <>0 or @retcode <> 0 return (1)
            end
        else
            begin
            delete from #temp_cont
            exec ('insert into #temp_cont (tablenick, rowguid, partchangegen, joinchangegen)
                        select tablenick, rowguid, partchangegen, joinchangegen from MSmerge_contents where
                        tablenick  = ' + @artnickstr + ' and generation in (' +
                    @genlist + ') ')
            if @@ERROR <>0 return (1)
            set @temp_id = 0
            select @temp_id = min(temp_id) from #temp_cont where temp_id > @temp_id
            while (@temp_id is not null)
                begin
                select @tablenick = tablenick, @rowguid = rowguid,
                   @partchangegen = partchangegen, @joinchangegen = joinchangegen
                    from #temp_cont where temp_id = @temp_id
                set @rowguidstr = '''' + convert(nchar(36), @rowguid) + ''''
                exec @retcode = dbo.sp_MSbelongs @publisher, @publisher_db,    @publication, @tablenick, @rowguid, @retval    output, 0
                if @@ERROR <>0 or @retcode <> 0 return (1)
                if @retval = 1
                    begin
                    insert into #belong (tablenick, rowguid, flag, partchangegen, joinchangegen) values
                            (@artnick, @rowguid, 0, @partchangegen, @joinchangegen)
                    if @@ERROR <>0 return (1)
                    end

                else
                    begin
                    /* Checking partchangegen will tell us if we need to insert this */
                    if @partchangegen > @commongen
                        insert into #notbelong (tablenick, rowguid, flag) values
                            (@artnick, @rowguid, 0)
                    end

                select @temp_id = min(temp_id) from #temp_cont where temp_id > @temp_id
                end

            end
        /* Put changes in #notbelong  that aren't in #belong and have a relevant partchangegen    */
        -- If publication has before image tables, we should screen changes using the before image tables
        if @before_view_objid is not null
            begin
            set @before_view_name = OBJECT_NAME(@before_view_objid)
            execute ('insert into #notbelong (tablenick, rowguid, flag, partchangegen, joinchangegen)
                select tablenick, c.rowguid, 0, partchangegen, joinchangegen
                    from MSmerge_contents c, #genlist g where c.generation = g.generation
                    and partchangegen > ' + @commongenstr + ' and tablenick = ' + @artnickstr + ' and
                    rowguid in (select ' + @rgcol + ' from ' + @before_view_name + ') and
                    rowguid not in (select rowguid from #belong)')
            if @@ERROR <>0 return (1)

            /* Add tombstones to ##notbelong */
            execute ('insert into #notbelong (tablenick, rowguid, flag, partchangegen, joinchangegen, type)
                select tablenick, rowguid,  0, generation, generation, type from
                MSmerge_tombstone where tablenick = ' + @artnickstr + ' and
                    rowguid in (select ' + @rgcol + ' from ' + @before_view_name + ') and generation in (' + @genlist + ' ) ')
            if @@ERROR <>0 return (1)

            end
        else
            begin
            insert into #notbelong (tablenick, rowguid, flag, partchangegen, joinchangegen)
                select tablenick, c.rowguid, 0, partchangegen, joinchangegen
                    from MSmerge_contents c, #genlist g where c.generation = g.generation
                    and partchangegen > @commongen and tablenick = @artnick and
                    rowguid not in (select rowguid from #belong)
            if @@ERROR <>0 return (1)
            execute ('insert into #notbelong (tablenick, rowguid, flag, partchangegen, joinchangegen, type)
                select tablenick, rowguid,  0, generation, generation, type from
                MSmerge_tombstone where tablenick = ' + @artnickstr + ' and generation in (' + @genlist + ' ) ')
            if @@ERROR <>0 return (1)
            end
        /* Move on to next article, repeat while loop */
        select @artnick = min(nickname) from sysmergearticles where pubid = @pubid and
             nickname > @artnick
        end

    drop table #temp_cont

    /* Optimization: If joinchangegen and partchange are both null or < common gen,
    ** it is not necessary to expand #belong for that particular row.
    */

    select @maxfilterid = max(join_filterid) from sysmergesubsetfilters

    if @maxfilterid is not null
        begin
        update #belong set flag = @maxfilterid, skippedexpand = 1 where
            ((joinchangegen is null or joinchangegen <= @commongen) and
             (partchangegen is null or partchangegen <= @commongen))

        /* Expand the #belong temptable */
        exec @retcode = dbo.sp_MSexpandbelongs @pubid
        if @@ERROR<>0 OR @retcode<>0 return (1)
        end

    /* transfer rows from local temp to global temp */
    exec ('insert into ' + @belongsname + ' (tablenick, rowguid, generation, lineage, colv)
        select distinct b.tablenick, b.rowguid, c.generation, c.lineage, c.colv1 from
        #belong b left outer join MSmerge_contents c
        on  c.tablenick = b.tablenick and c.rowguid = b.rowguid order by b.tablenick, b.rowguid')
    if @@ERROR <>0 return (1)

    /* If subscriber is sql server, we don't have to expand belongs */
    if @subissql = 0 or @dynamic_join_cnt > 0
        begin
        /* Expand the #notbelong temptable */
        exec dbo.sp_MSexpandnotbelongs @pubid
        end

    /* transfer rows from local temp to global temp */
    exec ('insert into ' + @notbelongsname + ' (tablenick, rowguid, generation, lineage, type)
        select distinct b.tablenick, b.rowguid, coalesce (c.generation, t.generation), coalesce(c.lineage, t.lineage), b.type from
        #notbelong b left outer join MSmerge_contents c
         on  c.tablenick = b.tablenick and c.rowguid = b.rowguid
        left outer join MSmerge_tombstone t on t.tablenick = b.tablenick and t.rowguid = b.rowguid
            order by b.tablenick DESC, b.rowguid ASC')
    if @@ERROR <>0 return (1)

    -- this index will be useful in sp_MSenumpartialchanges
    exec ('create index nc1belongstable on ' + @belongsname + ' (tablenick, rowguid) with FILLFACTOR = 100')
    if @@ERROR <>0
        return (1)

    -- this index will be useful in sp_MSenumpartialdeletes
    exec ('create index nc1notbelongstable on ' + @notbelongsname + ' (tablenick, rowguid) with FILLFACTOR = 100')
    if @@ERROR <>0
        return (1)


-- DEBUG select @belongcnt = count(*) from #belong
-- DEBUG select @nb_cnt = count(*) from #notbelong
    drop table #notbelong

-- DEBUG insert into master.dbo.debuglog2 (publisher, pubdb, hostname, twhen, commongen, belong_cnt, nb_cnt, genlist) values
-- DEBUG (@publisher, @publisher_db, host_name(), getdate(), @commongen, @belongcnt, @nb_cnt, @genlist)

    if not exists (select * from #belong)
        select @belongsname, @notbelongsname, -1
    else
        select distinct @belongsname, @notbelongsname, tablenick from #belong
    drop table #belong
    return (0)
go

exec dbo.sp_MS_marksystemobject sp_MSsetupbelongs
go
grant exec on dbo.sp_MSsetupbelongs to public
go

--------------------------------------------------------------------------------
--. sp_MSmakesystableviews
--------------------------------------------------------------------------------
if exists (select * from sysobjects    where type = 'P'
    and name = 'sp_MSmakesystableviews')
    drop procedure sp_MSmakesystableviews
go

raiserror('Creating procedure sp_MSmakesystableviews', 0,1)
GO


create procedure sp_MSmakesystableviews
    (@publication sysname)
AS
    declare @guidstr         nvarchar(40)
    declare @pubid          uniqueidentifier
    declare @contentsview     sysname
    declare @tombstoneview     sysname
    declare @genhistoryview    sysname
    declare @filtersview    sysname
    declare @retcode smallint
    declare @dbname            sysname
    declare    @command        nvarchar(500)

    /*
    ** Check to see if current publication has permission
    */
    exec @retcode=sp_MSreplcheck_publish
    if @retcode<>0 or @@ERROR<>0 return (1)

    select @pubid = pubid from sysmergepublications where name = @publication and UPPER(publisher)=UPPER(@@SERVERNAME) and publisher_db=db_name()
    if @pubid is null
        BEGIN
            RAISERROR (20026, 16, -1, @publication)
            RETURN (1)
        END

    exec @retcode = dbo.sp_MSguidtostr @pubid, @guidstr out
    if @@ERROR<>0 OR @retcode<>0 return (1)
    select @contentsview = 'cont' + @guidstr
    select @tombstoneview = 'ts' + @guidstr
    select @genhistoryview = 'gh' + @guidstr
    select @filtersview = 'filt' + @guidstr

    set @guidstr = '''' + convert(nchar(36), @pubid) + ''''

    exec @retcode = dbo.sp_MSuniqueobjectname @tombstoneview, @tombstoneview out
    if @@ERROR<>0 OR @retcode<>0 return (1)
    exec @retcode = dbo.sp_MSuniqueobjectname @contentsview, @contentsview out
    if @@ERROR<>0 OR @retcode<>0 return (1)
    exec @retcode = dbo.sp_MSuniqueobjectname @genhistoryview, @genhistoryview out
    if @@ERROR<>0 OR @retcode<>0 return (1)
    exec @retcode = dbo.sp_MSuniqueobjectname @filtersview, @filtersview out
    if @@ERROR<>0 OR @retcode<>0 return (1)

    /* generate view for MSmerge_contents qualified by the pubid */
    set @command = 'sp_MSmakectsview ' + QUOTENAME(@publication) + ' , ' + @contentsview
    set @dbname = db_name()

    exec @retcode = master..xp_execresultset @command, @dbname
    if @@ERROR<>0 OR @retcode <>0 return (1)

    /*
    ** generate the view for MSmerge_tombstone. In SP2, the change was made to make the view
    ** return 0 rows since it is unnecessary and expensive to propagate the tombstones.
    ** In order to leave all the other moving parts unchanged, we decided to let the view
    ** return 0 rows.
    */
    exec ('create view ' + @tombstoneview + ' as select * from MSmerge_tombstone where 1 = 2 and
            tablenick in (select nickname from sysmergearticles where convert(nchar(36), pubid) = ' +
            @guidstr + ')')
    if @@ERROR <>0
        begin
            return (1)
        end

    /* take care of the potential dup-key problem on (guidsrc-pubid) */
    if exists (select * from MSmerge_genhistory gen1, MSmerge_genhistory gen2 where
        gen1.guidsrc=gen2.guidsrc and gen1.generation=gen2.generation and
            gen1.pubid is not null and gen2.pubid is not null and gen1.pubid<>gen2.pubid)
        begin
            delete gen1 from MSmerge_genhistory gen1, MSmerge_genhistory gen2
                where gen1.guidsrc=gen2.guidsrc and gen1.generation=gen2.generation
                    and gen1.pubid is not null and gen2.pubid is not null and gen1.pubid>gen2.pubid
            if @@ERROR<>0
                return (1)
        end

    exec ('create view ' + @genhistoryview + '(guidsrc, guidlocal, pubid, generation,
            art_nick, nicknames, coldate) as select DISTINCT guidsrc, guidlocal, CONVERT(uniqueidentifier, '
            + @guidstr + '), generation, art_nick, nicknames, coldate  from MSmerge_genhistory
            where guidlocal <> ''00000000-0000-0000-0000-000000000000'' and (art_nick = 0 or art_nick is NULL or
                    art_nick in (select nickname from sysmergearticles where convert(nchar(36), pubid) = ' +
            @guidstr + ')) ')
    if @@ERROR <>0
        begin
            return (1)
        end

    exec ('create view ' + @filtersview + ' as select * from sysmergesubsetfilters where convert(nchar(36), pubid) = ' +
            @guidstr)
    if @@ERROR <>0
        begin
            return (1)
        end

    set nocount on
    select @contentsview, @tombstoneview, @genhistoryview, @filtersview
    return (0)
go
exec dbo.sp_MS_marksystemobject sp_MSmakesystableviews
go
grant exec on dbo.sp_MSmakesystableviews to public
go


--------------------------------------------------------------------------------
--. sp_MSmakeviewproc
--------------------------------------------------------------------------------
if exists (select * from sysobjects    where type = 'P'
    and name = 'sp_MSmakeviewproc')
    drop procedure sp_MSmakeviewproc
go


raiserror('Creating procedure sp_MSmakeviewproc', 0,1)
GO

create procedure sp_MSmakeviewproc
    (@viewname sysname, @ownername sysname, @procname nvarchar(290), @rgcol sysname, @source_id int)
as
declare @retcode             smallint
declare @varname            nvarchar(10)
declare @cmdpiece            nvarchar(4000)
declare @column_list        nvarchar(4000)
declare @col_name            sysname

set nocount on

if EXISTS (select name from syscolumns where id=@source_id and iscomputed=1)
BEGIN
    select @column_list = ''
    DECLARE column_cursor CURSOR LOCAL FAST_FORWARD FOR
        select name from syscolumns where id=@source_id and iscomputed<>1
    FOR READ ONLY
    open column_cursor
    fetch next from column_cursor into @col_name
    WHILE (@@fetch_status <> -1)
    BEGIN
            if LOWER(@col_name)='rowguid'
                select @col_name = 'v.rowguid' --Use alias to avoid conflict with #cont table
            else
                set @col_name = QUOTENAME(@col_name)
            if @column_list=''
                select @column_list = @col_name
            else
                select @column_list = @column_list + ', ' + @col_name
            fetch next from column_cursor into @col_name
    END
    close column_cursor
    deallocate column_cursor

    if @column_list=''
    begin
        RAISERROR(21125, 16, -1)
        return (1)
    end
END
else
    select @column_list = '*'

select @procname=QUOTENAME(@procname)

set @cmdpiece = 'create procedure dbo.' + @procname + ' (@tablenick int, @max_rows int = NULL,
    @guidlast uniqueidentifier = NULL)
    AS

    set nocount on
    set rowcount 0
    if  @max_rows is not null
        begin
        -- used to select data for initial pop. of subscriber for dynamic filtered publication
        set rowcount @max_rows
        declare @lin varbinary (255)
        declare @cv varbinary (2048)
        declare @replnick int
        declare @objid int
        declare @ccols int

        select @objid = objid from sysmergearticles where nickname = @tablenick
        select @ccols = max(colid) from syscolumns where id = @objid

        exec dbo.sp_MSgetreplnick @nickname = @replnick out
        if (@@error <> 0) or @replnick IS NULL
                begin
            RAISERROR (14055, 11, -1)
            RETURN(1)
            end
        set @lin = { fn UPDATELINEAGE(0x0, @replnick) }
        set @cv = { fn INITCOLVS(@ccols, @replnick) }

        select @tablenick, v.' + @rgcol + ', coalesce (c.generation,1),
            coalesce (c.lineage, @lin), coalesce (c.colv1, @cv), ' + @column_list + ' from ' +
                 QUOTENAME(@ownername) + '.' +
                    QUOTENAME(@viewname) + ' v left outer join  MSmerge_contents c on
                      v.' + @rgcol + ' = c.rowguid and c.tablenick = @tablenick where v.' + @rgcol + ' > @guidlast
                     order by v.' + @rgcol + '
        return (1)
        end
    insert into #belong (tablenick, rowguid, flag, partchangegen, joinchangegen)
        select ct.tablenick, ct.rowguid, 0, ct.partchangegen, ct.joinchangegen
                    from  MSmerge_contents ct, #genlist g, ' + QUOTENAME(@ownername) + '.' +
                    QUOTENAME(@viewname) + ' v where ct.tablenick = @tablenick
                    and ct.generation = g.generation
                    and ct.rowguid = v.' + @rgcol + '

        UNION

        select ct.tablenick, ct.rowguid, 0, ct.partchangegen, ct.joinchangegen
                    from  MSmerge_contents ct, #genlist g, ' + QUOTENAME(@ownername) + '.' +
                    QUOTENAME(@viewname) + ' v where ct.tablenick = @tablenick
                    and ct.partchangegen = g.generation
                    and ct.rowguid = v.' + @rgcol + '

    if @@ERROR <> 0
        begin
        RAISERROR(''Error selecting from view'' , 16, -1)
        return (1)
        end'


exec (@cmdpiece)
exec dbo.sp_MS_marksystemobject @procname
exec ('grant exec on ' + @procname + ' to public')
go

exec dbo.sp_MS_marksystemobject sp_MSmakeviewproc
go
grant exec on dbo.sp_MSmakeviewproc to public
go

--------------------------------------------------------------------------------
--. sp_MSmakeviewproc
--------------------------------------------------------------------------------
if exists (select * from sysobjects    where type = 'P'
    and name = 'sp_MSdelsubrows')
    drop procedure sp_MSdelsubrows
go

raiserror('Creating procedure sp_MSdelsubrows', 0,1)
GO

create PROCEDURE sp_MSdelsubrows
    (@rowguid         uniqueidentifier,
    @tablenick     int,
    @metadata_type tinyint, /* 0 - Missing, 1 - Tombstone, 2 - Contents, 3 - ContentsDeferred */
    @lineage_old varbinary(255),
    @generation int,
    @lineage_new varbinary(255),
    @pubid uniqueidentifier = NULL,
    @rowsdeleted INT = NULL OUTPUT)

as
    set nocount on
    declare @success int
    declare @tablename         nvarchar(258)
    declare @rowguidstr        nvarchar(40)
    declare @match             int
    declare @new_metatype    tinyint
    declare @retcode         smallint
    declare @reason         nvarchar(255)
    declare @procname         nvarchar(258)
    declare @ownername        sysname
    /* By default this sp should delete exactly one row */
    set    @rowsdeleted = 1

    /*
    ** Check to see if current publication has permission
    */
    exec @retcode=sp_MSreplcheck_connection
        @tablenick = @tablenick
    if @retcode<>0 or @@ERROR<>0 return (1)

    select @success = 0
    /* Parameter validation */
    if (@rowguid is null)
        begin
        RAISERROR(14043, 16, -1, '@rowguid')
        return (0)
        end
    if (@tablenick is null)
        begin
        RAISERROR(14043, 16, -1, '@tablenick')
        return (0)
        end
    if @pubid is NULL
        exec @retcode = dbo.sp_MStablenamefromnick    @tablenick, @tablename output
    else
        exec @retcode = dbo.sp_MStablenamefromnick    @tablenick, @tablename output, @pubid
    if @@ERROR<>0 return (0)

    if (@tablename is null)
        begin
        RAISERROR(14043, 16, -1, '@tablename')
        return (0)
        end
    if (@lineage_new is null)
        begin
        RAISERROR(14043, 16, -1, '@lineage_new')
        return (0)
        end

    if (not exists(select * from sysobjects where name = 'MSmerge_contents'))
        begin
        RAISERROR(20054 , 16, -1)
        return (0)
        end
    set @rowguidstr = '''' + convert(nchar(36), @rowguid) + ''''

-- DEBUG    insert into master.dbo.debuglog(tablenick, rowguid, type, twhen, comment) values
-- DEBUG    (@tablenick, @rowguid, @metadata_type, getdate(), 'sp_MSdelsubrows')

    -- Are we just changing the type of a tombstone?
    if (@metadata_type = 5 and exists (select * from MSmerge_tombstone where
        rowguid = @rowguid and tablenick = @tablenick))
        begin
        set @success = 1
        return @success
        end

    -- Are we just changing the type of a tombstone?
    if (@metadata_type = 1 and exists (select * from MSmerge_tombstone where
        rowguid = @rowguid and tablenick = @tablenick))
        begin
        set @reason = formatmessage (20562) -- User delete
        update MSmerge_tombstone set type = @metadata_type, reason = @reason where
            rowguid = @rowguid and tablenick = @tablenick
        set @success = 1
        return @success
        end


    -- Are we just changing the type of a tombstone?
    if (@metadata_type = 6 and exists (select * from MSmerge_tombstone where
        rowguid = @rowguid and tablenick = @tablenick))
        begin
        set @reason = formatmessage (20564) -- System deleted
        update MSmerge_tombstone set type = @metadata_type, reason = @reason where
            rowguid = @rowguid and tablenick = @tablenick
        set @success = 1
        return @success
        end

    -- begin transaction and lock row that we plan to delete
    begin transaction

        select @ownername = user_name(uid) from sysobjects where id = object_id(@tablename)
        select @procname = select_proc from sysmergearticles where objid = object_id(@tablename)  and pubid = @pubid
        exec @retcode = @procname @type =8, @rowguid=@rowguid

        select @success = 2

    if @metadata_type = 5
        begin
        set @match = 1
        set @new_metatype = 5
        end
    else if @metadata_type = 6
        begin
        set @match = 1
        set @new_metatype = 6
        end
    else
        begin
        exec @retcode=sp_MScheckmetadatamatch @metadata_type, @rowguid, @tablenick, @lineage_old, @match output

        set @new_metatype = 1
        end
    if (@match = 1)
        begin

        /* If there are any joinfilters with this as the join table, try to expand to deleting
        ** a set of related rows.
        */
        if (exists (select * from sysmergesubsetfilters where pubid = @pubid and
                        join_nickname = @tablenick))
            begin
            declare @tn int
            declare @table_name sysname

            set @reason = formatmessage (20563) -- Moved out of partial range
            /* create temp and put in our tablenick, rowguid */
            create table #notbelong (tablenick int NOT NULL,
                rowguid uniqueidentifier NOT NULL, flag int NOT NULL)
            create index #indnbelong on #notbelong (rowguid)
            insert into #notbelong (tablenick, rowguid, flag) values
                    (@tablenick, @rowguid, 0)

            /* call expand proc */
            exec @retcode = dbo.sp_MSexpandsubsnb @pubid

            select @rowsdeleted = count(*) from #notbelong

            select @tn = max(tablenick) from #notbelong where flag > -1
            while @tn is not null
                begin
                exec @retcode = dbo.sp_MStablenamefromnick @tn, @table_name out, @pubid
                /* delete all rows indicated by the temp table */
                exec ('delete from ' + @table_name + ' where RowGuidCol in
                    (select rowguid from #notbelong)' )
                /* change tombstone type for those rows */
                update MSmerge_tombstone set type = 5, reason = @reason where tablenick = @tn and
                    rowguid in (select rowguid from #notbelong)

                /* move on to next nickname - decreasing makes delete order correct */
                update #notbelong set flag = -1 where tablenick = @tn
                select @tn = max(tablenick) from #notbelong where flag > -1
                end

            /* drop temp, set success */
            drop table #notbelong
            exec dbo.sp_MSsetrowmetadata @tablenick, @rowguid, @generation, @lineage_new, NULL, @new_metatype
            select @success = 1
            end
        else
            begin

            /*
            ** select_proc makes a delete with @type = 5, despite its name.
            */
            exec @retcode = @procname @type =5, @rowguid=@rowguid

            if (@@error = 0 and @@rowcount = 1)
                begin
                exec dbo.sp_MSsetrowmetadata @tablenick, @rowguid, @generation, @lineage_new, NULL, @new_metatype
                select @success = 1
                end
            else
                select @success = 3
            end
        end
    commit
    return (@success)
go
exec dbo.sp_MS_marksystemobject sp_MSdelsubrows
go
grant exec on dbo.sp_MSdelsubrows to public
go


--------------------------------------------------------------------------------
--. sp_vupgrade_mergetables
--------------------------------------------------------------------------------
if exists (select * from sysobjects    where type = 'P'
    and name = 'sp_vupgrade_mergetables')
    drop procedure sp_vupgrade_mergetables
go

raiserror('Creating procedure sp_vupgrade_mergetables', 0,1)
GO
create procedure sp_vupgrade_mergetables
as

    -- this raiserror makes sp_vupgrade_replication.out file easier to read
    raiserror('sp_vupgrade_mergetables', 0,1)

    DECLARE @table_name sysname

    if (exists (select * from sysobjects where name = 'sysmergearticles'))
        begin
        -- Columns have been added to the sysmergearticles table
        if not exists (select * from syscolumns where id = object_id('sysmergearticles') and
                        name = 'gen_cur')
            begin
            alter table sysmergearticles add gen_cur int null
            if @@ERROR <> 0
                return 1
            end
        if not exists (select * from syscolumns where id = object_id('sysmergearticles') and
                            name = 'before_image_objid')
            begin
            alter table sysmergearticles add before_image_objid    int    NULL
            if @@ERROR <> 0
                return 1
            end
        if not exists (select * from syscolumns where id = object_id('sysmergearticles') and
                            name = 'before_view_objid')
            begin
            alter table sysmergearticles add before_view_objid    int    NULL
            if @@ERROR <> 0
                return 1
            end
        end
    --  MSmerge_contents
    SELECT @table_name = N'MSmerge_contents'
    IF EXISTS ( SELECT * FROM sysobjects WHERE name = 'MSmerge_contents' )
    BEGIN
        IF NOT EXISTS ( SELECT * FROM sysindexes WHERE name = 'nc3MSmerge_contents' AND
        id = OBJECT_ID('MSmerge_contents') )
                create index nc3MSmerge_contents on MSmerge_contents(tablenick, partchangegen)

        IF NOT EXISTS ( SELECT * FROM sysindexes WHERE name = 'nc4MSmerge_contents' AND
        id = OBJECT_ID('MSmerge_contents') )
                create index nc4MSmerge_contents on MSmerge_contents(generation, partchangegen)
    END

    --    sysmergepublications
    SELECT @table_name = N'sysmergepublications'
    IF EXISTS ( SELECT * FROM sysobjects WHERE name = 'sysmergepublications' )
    BEGIN
        if not exists (select * from syscolumns where id = object_id('sysmergepublications')
            and name = 'keep_before_values')
        begin
        alter table sysmergepublications add keep_before_values int null
        if @@ERROR <> 0
            return 1
        end

        IF EXISTS ( SELECT pubid
            FROM sysmergepublications
            GROUP BY pubid
            HAVING COUNT(*) > 1 )
            RAISERROR (21144, 10, 4, @table_name)
        ELSE
            IF NOT EXISTS ( SELECT * FROM sysindexes WHERE name = 'nc1sysmergepublications' AND
                id = OBJECT_ID('sysmergepublications') )
            CREATE UNIQUE NONCLUSTERED INDEX nc1sysmergepublications
                ON sysmergepublications(pubid)
    END

    --    MSmerge_genhistory
    SELECT @table_name = N'MSmerge_genhistory'
    IF EXISTS ( SELECT * FROM sysobjects WHERE name = 'MSmerge_genhistory' )
    BEGIN
    -- 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 MSmerge_genhistory
            GROUP BY guidsrc, pubid
            HAVING COUNT(*) > 1 )
            RAISERROR (21144, 10, 5, @table_name)
        ELSE
            IF NOT EXISTS ( SELECT * FROM sysindexes WHERE name = 'nc3MSmerge_genhistory' AND
                id = OBJECT_ID('MSmerge_genhistory') )
            CREATE UNIQUE NONCLUSTERED INDEX nc3MSmerge_genhistory ON MSmerge_genhistory(guidsrc, pubid)

            IF NOT EXISTS ( SELECT * FROM sysindexes WHERE name = 'nc4MSmerge_genhistory' AND
                id = OBJECT_ID('MSmerge_genhistory') )
            create  index nc4MSmerge_genhistory on MSmerge_genhistory(coldate)

    END


    --    MSmerge_delete_conflicts
    SELECT @table_name = N'MSmerge_delete_conflicts'
    IF EXISTS ( SELECT * FROM sysobjects WHERE name = 'MSmerge_delete_conflicts' )
    BEGIN
        IF EXISTS ( SELECT tablenick, rowguid, origin_datasource
            FROM MSmerge_delete_conflicts
            GROUP BY tablenick, rowguid, origin_datasource
            HAVING COUNT(*) > 1 )
            RAISERROR (21144, 10, 6, @table_name)
        ELSE
            IF NOT EXISTS ( SELECT * FROM sysindexes WHERE name = 'nc1MSmerge_delete_conflicts' AND
                id = OBJECT_ID('MSmerge_delete_conflicts') )
                CREATE UNIQUE NONCLUSTERED INDEX nc1MSmerge_delete_conflicts
                    ON MSmerge_delete_conflicts(tablenick, rowguid, origin_datasource)
    END

    --    sysmergeschemachange
    SELECT @table_name = N'sysmergeschemachange'
    IF EXISTS ( SELECT * FROM sysobjects WHERE name = 'sysmergeschemachange' )
    BEGIN
        IF EXISTS ( SELECT schemaversion, pubid
            FROM sysmergeschemachange
            GROUP BY schemaversion, pubid
            HAVING COUNT(*) > 1 )
            RAISERROR (21144, 10, 7, @table_name)
        ELSE
            BEGIN
                IF EXISTS ( SELECT * FROM sysindexes WHERE name = 'schemachangeversion' AND
                    id = OBJECT_ID('sysmergeschemachange') )
                    DROP INDEX sysmergeschemachange.schemachangeversion

                -- Recreate this index as unique clustered with one more field in index key.
                CREATE UNIQUE CLUSTERED INDEX schemachangeversion ON sysmergeschemachange(schemaversion, pubid)
            END
    END


    --    sysmergesubsetfilters
    SELECT @table_name = N'sysmergesubsetfilters'
    IF EXISTS ( SELECT * FROM sysobjects WHERE name = 'sysmergesubsetfilters' )
    BEGIN
        IF EXISTS ( SELECT join_filterid
            FROM sysmergesubsetfilters
            GROUP BY join_filterid
            HAVING COUNT(*) > 1 )
            RAISERROR (21144, 10, 8, @table_name)
        ELSE
            IF NOT EXISTS ( SELECT * FROM sysindexes WHERE name = 'nc1sysmergesubsetfilters' AND
                id = OBJECT_ID('sysmergesubsetfilters') )
            CREATE UNIQUE NONCLUSTERED INDEX nc1sysmergesubsetfilters ON sysmergesubsetfilters(join_filterid, pubid)
    END


    /*
     * Views, procs and triggers supporting merge articles need regenerated with new
     * DDL to take advantage of some fixes. If sysmergearticles, assume a merge pub or sub db
    */
    if (exists (select * from sysobjects where name = 'sysmergearticles'))
    begin

        declare @artnick        int
        declare @source_owner    sysname
        declare @source_object    sysname
        declare @qualified_name nvarchar(257)
        declare @artid            uniqueidentifier
        declare @pubid            uniqueidentifier
        declare @objid            int
        declare @pubname        sysname
        declare @article        sysname
        declare @snapshot_ready    int
        declare @retcode        integer

        -- only process those articles that have had a snapshot generated already
        select @artnick = min(a.nickname) from sysmergearticles a inner join sysmergepublications p on p.pubid = a.pubid where p.snapshot_ready =1
        while @artnick is not null
        begin
            -- find base table to compute number of columns
            select @objid = objid from sysmergearticles where nickname = @artnick

            -- regenerate the triggers
            select @source_owner = user_name(uid), @source_object = name from sysobjects where id = @objid
            select @qualified_name = QUOTENAME(@source_owner) + '.' + QUOTENAME(@source_object)
            exec dbo.sp_MSaddmergetriggers @qualified_name

            /* Loop through all articles that this table is involved in and regenerate the article procs */
            declare hcArtCursor CURSOR LOCAL FAST_FORWARD FOR select artid, pubid from sysmergearticles where nickname = @artnick order by artid, pubid

            OPEN hcArtCursor
            FETCH hcArtCursor INTO @artid, @pubid
            WHILE (@@fetch_status <> -1)
            BEGIN
                select @pubname = name, @snapshot_ready = snapshot_ready from sysmergepublications where pubid = @pubid
                -- regenerate procs, triggersonly for articles with snapshot ready
                if @snapshot_ready>0
                begin
                    select @article = name from sysmergearticles where artid = @artid and pubid = @pubid
                    exec @retcode = dbo.sp_MSsetartprocs @publication = @pubname,   @article = @article, @force_flag = 1
                    if @@ERROR <>0 OR @retcode <>0
                        return (1)
                END
                FETCH hcArtCursor INTO @artid, @pubid
            END
            CLOSE hcArtCursor
            DEALLOCATE hcArtCursor

            -- find next article
            select @artnick = min(a.nickname) from sysmergearticles a inner join sysmergepublications p on p.pubid = a.pubid where p.snapshot_ready > 0 and a.nickname > @artnick
        end -- end article proc and trigger re-gen

        -- Loop over publications and recreate the views, skipping publications where snapshot is not ready
        select @pubname = min(name) from sysmergepublications where UPPER(publisher)=UPPER(@@SERVERNAME) and publisher_db=db_name() and snapshot_ready > 0
        while @pubname is not null
        begin
            -- remake the publication views
            exec dbo.sp_MSpublicationview @pubname, 1
            select @pubname = min(name) from sysmergepublications where name > @pubname and UPPER(publisher)=UPPER(@@SERVERNAME) and publisher_db=db_name() and snapshot_ready > 0

        end
    end
GO
exec dbo.sp_MS_marksystemobject sp_vupgrade_mergetables
go

--------------------------------------------------------------------------------
-- END OF FILE: Turn off marking of system objects.
--    DO NOT ADD ANYTHING AFTER THIS POINT
--------------------------------------------------------------------------------
exec sp_MS_upd_sysobj_category 2
go

exec sp_configure 'allow updates',0
go

reconfigure with override
go