Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSmergepublishdb

  No additional text.


Syntax

create procedure sys.sp_MSmergepublishdb(
      @value     sysname,
      @ignore_distributor bit = 0
    ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */
    declare @command        nvarchar(255)
    declare @description    nvarchar(500)
    declare @cmptlevel      tinyint
    declare @db_name        sysname
    declare @retcode        int
    declare @distributor    sysname
    declare @distribdb      sysname
    declare @category_name  sysname
    declare @agentname      sysname
    declare @working_directory nvarchar(255)
    declare @alter_table_trigger nvarchar(255)
    declare @role sysname
					,@db_tranbit int

    -- database wide pal role
    select @role = 'MSmerge_PAL_role'
    /*
    ** Initialization
    */

    select @db_name = DB_NAME()
    			,@db_tranbit = 1


    /*
    ** Parameter check
    ** @value
    */
    IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true','false')
    BEGIN
      RAISERROR(14137,16,-1)
      RETURN(1)
    END

    /*
    ** if @ignore_distributor = 1, we are in bruteforce cleanup mode, don't do RPC.
    */
    if @ignore_distributor = 0
    begin
        /*
        ** Test to see if the distributor is installed and online.
        */
        EXECUTE @retcode = sys.sp_helpdistributor @rpcsrvname = @distributor OUTPUT, @directory=@working_directory OUTPUT,
           @distribdb    = @distribdb OUTPUT

        IF @@ERROR <> 0 or @retcode <> 0 or @distributor IS NULL or @distribdb IS NULL
        BEGIN
            IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'
                RAISERROR (20028, 16, -1)
            ELSE
                RAISERROR (20029, 16, -1)
            RETURN (1)
        END
    end

    /*
    ** Enable the database for publishing.
    */
    IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'
    BEGIN
        select @cmptlevel = cmptlevel from master.dbo.sysdatabases where name=db_name() collate database_default
        if @cmptlevel<70 OR @cmptlevel is NULL
        begin
            RAISERROR(20061, 16, -1)
            goto FAILURE
        end

        -- Lightweight replicas do not publish themselves
        execute @retcode = sys.sp_MScreate_mergesystables @whattocreate=1
        if @@ERROR <> 0 or @retcode <> 0 goto FAILURE

        execute @retcode= sys.sp_MSrepl_ddl_triggers @type='merge', @mode='add'
        if @@ERROR <> 0 or @retcode <> 0
            goto FAILURE

        -- create the databased pal role - a role to which every other pal role will belong
        if not exists (select * from sys.database_principals where name=@role and type = 'R')
        begin
            exec @retcode = sys.sp_addrole @role
            if @@ERROR <> 0 or @retcode <> 0
                goto FAILURE
        end
    END

    ELSE    /* Disable the database for publishing. */
    BEGIN
        -- if the current db is not a republisher we will ignore the merge metadata in
        -- all the drop procs. That will help us to not delete from contents and tombstones
        -- we will subsequently drop these tables anyway
        declare @ignore_merge_metadata bit

        if not exists (select * from dbo.sysmergesubscriptions
                        where UPPER(subscriber_server) = UPPER(publishingservername()) and db_name = db_name() and subid <> pubid)
        begin
            select @ignore_merge_metadata = 1
        end

        /*
        ** Remove all the registration entries for subscriptions
        */
        if not exists(select * from sys.objects where name = 'sysmergesubscriptions')
            goto FAILURE
        exec @retcode = sys.sp_dropmergesubscription @publication = 'all',
                                        @subscriber = 'all',
                                        @subscriber_db = 'all',
                                        @subscription_type = 'both',
                                        @ignore_distributor = @ignore_distributor
        IF @@ERROR <> 0 or @retcode <> 0
        begin
            goto FAILURE
        end

        /*
        ** Remove all publications and articles in the database.
        */
        EXEC @retcode = sys.sp_dropmergepublication @publication = 'all',
            @ignore_distributor = @ignore_distributor, @ignore_merge_metadata = @ignore_merge_metadata
        IF @@ERROR <> 0 or @retcode <> 0
        begin
            -- sp_dropmergepublication will raiserror
            goto FAILURE
        end

        If NOT EXISTS (select * from dbo.sysmergepublications)
        BEGIN
            execute @retcode= sys.sp_MSdrop_mergesystables @whattodrop=3
            if @@ERROR <> 0 or @retcode <> 0 goto FAILURE

            -- drop the database wide pal role
            if exists (select * from sys.database_principals where name=@role and type = 'R')
            begin
                exec @retcode = sys.sp_droprole @role
                if @@ERROR <> 0 or @retcode <> 0
                    goto FAILURE
            end
        END

        -- we may not have cleaned the merge system tables above if we know of other republishers.
        -- however now by now we have cleaned up all our publications and all
        -- subscriptions to those publications. So if the current database is
        -- not subscribed to any other publication we can remove all merge system tables here
        if exists (select * from sys.objects where name = 'sysmergesubscriptions')
        begin
            if not exists (select * from dbo.sysmergesubscriptions
                            where UPPER(subscriber_server)=UPPER(publishingservername()) and db_name=db_name() and subid <> pubid)
            begin
                execute @retcode= sys.sp_MSdrop_mergesystables @whattodrop=3
                if @@ERROR <> 0 or @retcode <> 0 goto FAILURE
            end
        end

        execute @retcode=sys.sp_MSrepl_ddl_triggers @type='merge', @mode='drop'
        if @@ERROR <> 0 or @retcode <> 0 goto FAILURE

        /*
        set @db_name = sys.fn_getvalidname(@db_name)
        select @alter_table_trigger = 'alter_table_' + @db_name
        if exists (select * from sys.objects where name=@alter_table_trigger)
        begin
            set @alter_table_trigger= quotename(@alter_table_trigger)
            exec('drop trigger ' + @alter_table_trigger + ' on database ')
        end
        */
    END


        return 0
FAILURE:

    return (1)


 
Last revision 2008RTM
See also

  sp_MSdrop_rladmin (Procedure)
sp_MSremovedbreplication (Procedure)
sp_replicationdboption (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