Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_replicationdboption

  No additional text.


Syntax
create procedure sys.sp_replicationdboption (
      @dbname    sysname,
      @optname   sysname,
      @value     sysname,
      @ignore_distributor bit = 0,
      @from_scripting bit = 0
    ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */
    DECLARE @retcode      int,
                @optbit       int,
                @optbit_value int,				-- Desired value with the optbit mask
                @proc         nvarchar(512),
                @category     int,
                @value_bit    bit,
                @command  nvarchar(4000),
                @backup_proc nvarchar(1000),
                @testStr nvarchar(300),
                @num_mergedb int,
                @flush_proc nvarchar(300),
                @done_proc  nvarchar(300),
                @clearcache_proc  nvarchar(300)


    select @optname = LOWER(@optname)
            ,@value   = LOWER(@value)
            ,@num_mergedb = null

    /*
    ** Security Check: require sysadmin
    */
    IF (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'),0) = 0)
    BEGIN
        RAISERROR(21089,16,-1)
        RETURN (1)
    END

    /*
    ** Parameter check
    ** @dbname
    */
    SELECT @category = category FROM master.dbo.sysdatabases WHERE
        name = @dbname collate database_default
    if @category is null
    BEGIN
        RAISERROR(15010, 16, -1, @dbname)
        RETURN(1)
    END

    /*
    ** Parameter check
    ** @type
    */
    IF @optname is null or @optname NOT IN (N'publish',N'merge publish',N'subscribe',N'sync with backup'
        )
    BEGIN
        RAISERROR(14138,16,-1,@optname)
        RETURN(1)
    END

    -- Verify that this SKU is allowed to be a publisher
    if @optname in (N'publish',N'merge publish') and @value = N'true'
    begin
        exec @retcode= sys.sp_MSsku_allows_replication
        if @@error<>0 return 1
        if @retcode <> 0
        begin
            raiserror(21106, 16, -1)
            return (1)
        end
    end

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

    /*
    **    If we're in a transaction, disallow this since it might make recovery
    **    impossible.
    **
    */
    IF @@trancount > 0
    BEGIN
        RAISERROR(15002,16,-1,'sp_replicationdboption')
        RETURN(1)
    END

    IF @optname = N'publish'
    BEGIN
        SELECT @optbit = 1
        SELECT @proc = QUOTENAME(@dbname) + N'.sys.sp_MSpublishdb'
    END
    ELSE IF @optname = N'merge publish'
    BEGIN
        SELECT @optbit = 4
        SELECT @proc = QUOTENAME(@dbname) + N'.sys.sp_MSmergepublishdb'
    END
    ELSE IF @optname = N'subscribe'
    BEGIN
        SELECT @optbit = 2
    END
    ELSE IF @optname = N'sync with backup'
    BEGIN
        SELECT @optbit = 32
    END

    IF @value = N'true'
    begin
        SELECT @optbit_value = @optbit
        select @value_bit = 1
    end
    ELSE
    begin
        select @value_bit = 0
        SELECT @optbit_value = 0
    end

    /*
    ** Check if the option is set as required already
    */
    if (@category & @optbit) = @optbit_value
    BEGIN
        if @value = N'true'
            RAISERROR (14035, 10, -1, @optname, @dbname)
        else
            RAISERROR (14037, 10, -1, @optname, @dbname)
        RETURN (1)
    END

    -- If turning on 'sync with backup', make sure 'publish' or 'dist' is turned on already.
    if  @optbit_value = 32 and (@category & 1 = 0 and @category & 16 = 0)
    begin
        raiserror(20019, 16, -1, 'sync with backup')
        return (1)
    end

    -- We do not allow turning on sync with backup mode at publishing db if the db is
    -- in simple recovery mode
    if  @optbit_value = 32 and @category & 1 <> 0 and
        databasepropertyex(@dbname, 'recovery') = N'SIMPLE'
    begin
        raiserror(20622, 16, -1, 'sync with backup')
        return (1)
    end

    -- If turning off 'publish', turn off 'sync with backup' as well if the database
    -- is not a distribution database.
    if @optbit = 1 and @optbit_value = 0 and @category & 32 <> 0 and @category & 16 = 0
    begin
        EXEC @retcode =  sys.sp_replicationdboption
            @dbname = @dbname,
            @optname = N'sync with backup',
            @value = N'false',
            @ignore_distributor = @ignore_distributor,
            @from_scripting = @from_scripting
        IF @@ERROR <> 0 or @retcode <> 0
        BEGIN
            GOTO UNDO
        END
    end

    -- if turning on 'sync with backup' a distribution database, initialize the backup lsns
    -- to nulls, this should be done before the category bit is set.
    if @optbit_value = 32 and @category & 16 <> 0
    begin
        SELECT @backup_proc = QUOTENAME(@dbname) + N'.dbo.sp_MSrepl_init_backup_lsns'
        exec @retcode = @backup_proc
        if @@error <> 0 or @retcode <> 0
            goto UNDO
    end

    /*
    ** Prepare the required option
    */
    if @proc is not null
    begin
        if (@optname IN (N'publish',N'merge publish' ) and (@value = N'false'))
        begin
            
            --check db state in try block, if failed, goto IGNORE so db can be unmarked (and dropped later)
            --avoid putting try block on sp_MSpublishdb as it may fail with other error within nested tran
            
            BEGIN TRY
                SELECT @testStr = N'use '  + QUOTENAME(@dbname) + ' begin tran save tran test_read_write commit tran'
                exec (@testStr)
            END TRY
            BEGIN CATCH
                declare @number int
                        ,@sev int
                        ,@state int
                        ,@msg nvarchar(max)
                select @number = ERROR_NUMBER(), @sev = ERROR_SEVERITY(), @state = ERROR_STATE(), @msg = ERROR_MESSAGE()
                raiserror(14166, 11, 1, @number, @sev, @state, @msg)
                goto IGNORE
            END CATCH
        end
        EXEC @retcode = @proc @value = @value,
                                @ignore_distributor = @ignore_distributor
        IF @@ERROR <> 0 or @retcode <> 0
        BEGIN
            GOTO UNDO
        END
    end

IGNORE:
    -- Consider: Lock database using EXEC %%CurrentDatabase().Lock()??
    -- rmak: Doesn't seem necessary nor is the right thing to do as
    -- %%CurrentDatabase().Lock locks only the current database (master)
    /*
    ** Preparation succeeded.
    ** Toggle the category bit in master.dbo.sysdatabases
    */
    IF @optname = N'publish'
    BEGIN
        -- clear dbtable fields
        -- no check for IsPublished here, as it is taken care of above
        if @value_bit = 0
			AND CONVERT(sysname,DATABASEPROPERTYEX(@dbname,'status'))  = N'READ_WRITE'
			AND HAS_DBACCESS(@dbname) = 1
			--if cdc is still enabled, don't call sp_repldone
			AND not exists(select * from sys.databases where db_id(@dbname) = database_id and is_cdc_enabled = 1)
        BEGIN
            SELECT @flush_proc = QUOTENAME(@dbname) + N'.sys.sp_replflush'
            SELECT @done_proc  = QUOTENAME(@dbname) + N'.sys.sp_repldone'

            exec @flush_proc
            exec @done_proc NULL, NULL, 0, 0, 1
            exec @flush_proc
        END
        EXEC %%DatabaseEx(Name = @dbname).SetPublished(Value = @value_bit)
    END
    ELSE IF @optname = N'merge publish'
        EXEC %%DatabaseEx(Name = @dbname).SetMergePublished(Value = @value_bit)
    ELSE IF @optname = N'subscribe'
        EXEC %%DatabaseEx(Name = @dbname).SetSubscribed(Value = @value_bit)
    ELSE IF @optname = N'sync with backup'
        EXEC %%DatabaseEx(Name = @dbname).SetSyncWithBackup(Value = @value_bit)

    IF ((@optname = N'merge publish') or (@optname = N'publish'))
    begin
       if (convert(sysname,DATABASEPROPERTYEX(@dbname,'status'))  = N'ONLINE')
        begin
			-- sysreplservers needs to be refreshed when:
			-- 1. 'publish' or 'merge publish' is being set to true
			-- 2. 'publish' or 'merge publish' is being set to false and db is no longer published for either
			if(@value_bit = 1
			or (@value_bit = 0
					and(DatabasePropertyEx(@dbname, 'IsPublished') = 0)
					and (DatabasePropertyEx(@dbname, 'IsMergePublished') = 0)))
			begin
				SELECT @proc = QUOTENAME(@dbname) + N'.sys.sp_refreshreplsysservers'
				EXEC @retcode = @proc
				IF @@ERROR <> 0 or @retcode <> 0
				    GOTO UNDO
			end
        end
    end

    if @optname = N'merge publish'
    begin
        if @value = N'true'
        begin
            -- Set the 'startup' option for sp_MScleanupmergepublisher if the
            -- database is enabled for merge replication.
            exec (N'use master
                    exec sys.sp_procoption N''sp_MScleanupmergepublisher'', N''startup'', N''true''')
        end
        else
        begin
            -- Reset the 'startup' option for sp_MScleanupmergepublisher if
            -- this is the last database that has its 'merge publish' option
            -- disabled
            select @num_mergedb = count(*) from master.dbo.sysdatabases
             where (category & 4) <> 0
            if @num_mergedb = 0
            begin
                exec (N'use master
                    exec sys.sp_procoption N''sp_MScleanupmergepublisher'', N''startup'', N''false''')
            end
        end
    end

    IF ((@optname = N'merge publish') or (@optname = N'publish')) and (@value = N'true')
    BEGIN
        -- Add expired subscription cleanup job and alerts
        EXEC @retcode = sys.sp_MSrepl_add_expired_sub_cleanup_job

        IF @@ERROR <> 0 or @retcode <> 0
            GOTO UNDO
    END

    IF ((@optname = N'merge publish') or (@optname = N'publish')) and (@value = N'false')
    BEGIN
        -- Drop expired subscription cleanup job and alerts
        EXEC @retcode = sys.sp_MSrepl_drop_expired_sub_cleanup_job
        IF (@@ERROR != 0 OR @retcode != 0)
        	RETURN (1)
    END

	-- drop symetric keys if the database is online
	-- note that here on a failure we will throw an error
    IF CONVERT(sysname, DATABASEPROPERTYEX(@dbname,'status')) = N'ONLINE'
	BEGIN
	  	SELECT @proc = QUOTENAME(@dbname) + N'.sys.sp_dropreplsymmetrickey'
	  	
		EXEC @retcode = @proc @check_replication = 1, @throw_error = 1
		IF (@@ERROR != 0 OR @retcode != 0)
        	RETURN (1)
	END
	
    /*
    **    ???
    ** CHECKPOINT the database that was changed. Make the change
    ** effective immediatly
    */
    CHECKPOINT
    IF @@ERROR <> 0
    BEGIN
        RETURN(1)
    END

     IF @optname = N'publish' and @value_bit = 0 and not exists(select * from sys.databases where db_id(@dbname) = database_id and is_cdc_enabled = 1)
     BEGIN
	SELECT @clearcache_proc = QUOTENAME(@dbname) + N'.sys.sp_replhelp'
	EXEC @clearcache_proc N'ClearDbArticleCache' --clear article cache for this database
	EXEC @clearcache_proc N'DisablePerDbHistoryCache' --clear DMV cache for this database
     END


    RETURN(0)

UNDO:
    -- Create system table is not allowed in a multi-statement transactions.
    -- Drop the tables here
    IF @value = N'true'
        EXEC sys.sp_replicationdboption
          @dbname     = @dbname,
          @optname     = @optname,
          @value     = N'false',
          @ignore_distributor = @ignore_distributor

    return(1)

 
Last revision 2008RTM
See also

  sp_dboption (Procedure)
sp_dropdistributor (Procedure)
sp_MSrepl_droppublication (Procedure)
sp_MSrepl_init_backup_lsns (Procedure)
sp_MSrestoredbreplication (Procedure)
sp_publishdb (Procedure)
sp_refreshreplsysservers (Procedure)
sp_vupgrade_express_edition (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