Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSmerge_subscriptions_upgrade

  No additional text.


Syntax
create procedure sys.sp_MSmerge_subscriptions_upgrade
as
begin

    begin tran
    save tran MSmerge_subscriptions_upgrade

    IF object_id('MSmerge_subscriptions') is not NULL
    begin
        if NOT EXISTS (select * from sys.columns where name = 'description' and object_id=object_id('MSmerge_subscriptions'))
        BEGIN
            ALTER TABLE MSmerge_subscriptions ADD description nvarchar(255) NULL
            if @@error<>0 goto error
        END

        if NOT EXISTS (select * from sys.columns where name = 'subscriber' and object_id=object_id('MSmerge_subscriptions'))
        BEGIN
            if exists( select * from sysindexes where name = 'ucMSmerge_subscriptions' AND id = OBJECT_ID('MSmerge_subscriptions'))
            begin
                drop index dbo.MSmerge_subscriptions.ucMSmerge_subscriptions
                if @@error<>0 goto error
            end

            -- In Yukon, the subscriber_id column is optional since there is no reliance on sysservers entries.
            alter table MSmerge_subscriptions alter COLUMN subscriber_id smallint NULL
            if @@error<>0 goto error

            -- In Yukon, adding a subscriber servername column to MSmerge_subscriptions
            ALTER TABLE MSmerge_subscriptions ADD subscriber sysname NULL
            if @@error<>0 goto error

            -- In Yukon, adding a subscriber GUID column to MSmerge_subscriptions
            ALTER TABLE MSmerge_subscriptions ADD subid uniqueidentifier NULL
            if @@error<>0 goto error

            -- need to exec update in diff process space to avoid syntax error on deferred name resolution at time of proc exec
            exec( N'update dbo.MSmerge_subscriptions set subscriber =
                (select srvname from master.dbo.sysservers where srvid = MSmerge_subscriptions.subscriber_id)' )
            if @@error<>0 goto error

            exec( N'update dbo.MSmerge_subscriptions set subid = newid() where subid IS NULL')
            if @@error<>0 goto error

            -- In Yukon, the subid is a required GUID colum, make it non NULL and create index on it
            alter table MSmerge_subscriptions alter COLUMN subid uniqueidentifier NOT NULL
            if @@error<>0 goto error

            -- In Yukon, adding a publisher servername column to MSmerge_subscriptions
            ALTER TABLE MSmerge_subscriptions ADD publisher sysname NULL
            if @@error<>0 goto error

            exec( N'update dbo.MSmerge_subscriptions set publisher =
                (select srvname from master.dbo.sysservers where srvid = MSmerge_subscriptions.publisher_id)' )
            if @@error<>0 goto error

            if not exists(select * from sysindexes where name = 'ucMSmerge_subscriptions' AND id = OBJECT_ID('MSmerge_subscriptions'))
            begin
                CREATE UNIQUE CLUSTERED INDEX ucMSmerge_subscriptions ON dbo.MSmerge_subscriptions(subid)
                if @@error<>0 goto error

            end

            if not exists(select * from sysindexes where name = 'unc1MSmerge_subscriptions'
                AND id = OBJECT_ID('MSmerge_subscriptions'))
            begin
                CREATE UNIQUE INDEX unc1MSmerge_subscriptions ON dbo.MSmerge_subscriptions
                    (subscriber, subscriber_db, publisher_id, publisher_db, publication_id)
                if @@error<>0 goto error

            end
        END

        if NOT EXISTS (select * from sys.columns where name = 'subscriber_version' and object_id=object_id('MSmerge_subscriptions'))
        BEGIN
            ALTER TABLE MSmerge_subscriptions ADD subscriber_version int NULL
            if @@error<>0 goto error
        END
    end

    IF object_id('MSmerge_agents') is not NULL
    begin
        update dbo.MSmerge_agents set subscriber_name =
            (select srvname from master.dbo.sysservers where srvid = MSmerge_agents.subscriber_id)
        where subscriber_name is null
        if @@error<>0 goto error
    end

    IF object_id('dbo.MSmerge_sessions') is not NULL
    BEGIN
        if NOT EXISTS (select * from sys.columns where name = 'prepare_snapshot_time' and object_id=object_id('dbo.MSmerge_sessions'))
        begin
            ALTER TABLE dbo.MSmerge_sessions ADD prepare_snapshot_time int NOT NULL default 0
            if @@error<>0 goto error
        end

        if NOT EXISTS (select * from sys.columns where name = 'spid' and object_id=object_id('MSmerge_sessions'))
        BEGIN
            ALTER TABLE MSmerge_sessions ADD spid smallint NULL, spid_login_time datetime NULL
            if @@error<>0 goto error
        END
    END

    commit transaction
    return 0
error:
    rollback tran MSmerge_subscriptions_upgrade
    commit tran
    raiserror(25003, 16, -1)
    return 1
end

 
Last revision 2008RTM
See also

  sp_MScreate_dist_tables (Procedure)
sp_vupgrade_distdb (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash