Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_removedbreplication

 

This stored procedure is executed at the Publisher on the publication database or at the Subscriber on the subscription database. The procedure removes all replication objects from the database in which it is executed, but it does not remove objects from other databases, such as the distribution database.

Execution syntax:
sp_removedbreplication [ [ @dbname = ] 'dbname' ]
    [ , [ @type = ] type ]

@type can be merge, tran or both.

This is a procedure in the mssqlsystemresource database.




Syntax



/* Permission to sysadmin - Wrapper to include security check*/
create procedure sys.sp_removedbreplication (
      @dbname     sysname = NULL,
      @type		nvarchar(5) = 'both'	-- 'merge' or 'tran' or 'both' to cleanup.
    ) AS
    set nocount on
    declare @retcode int
    declare @proc  nvarchar(255)

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

    if @dbname is NULL
        select @dbname = db_name()

    IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('merge', 'tran', 'both')
    BEGIN
    	RAISERROR(22551, 16, -1, @type)
    	RETURN (1)
    END

    if (convert(sysname,DATABASEPROPERTYEX(@dbname,'status'))  = 'ONLINE')
    begin
        exec @retcode = sys.sp_MSremovedbreplication_internal @dbname = @dbname,@type = @type,@ignore_distributor = 1

	    -- does brute force clean up for merge
	    IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) IN ('merge', 'both')
	    begin
            SELECT @proc = quotename(@dbname) + N'.sys.sp_MSremovedb_merge_replication_brute_force'	
	   	    exec @retcode = @proc
	    end

		IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) = 'both'
		BEGIN
			-- drop replication symetrickey
			-- note that since this is brute force we will not confirm
			-- whether or not replication is still enabled in the db
			SELECT @proc = QUOTENAME(@dbname) + N'.sys.sp_dropreplsymmetrickey'
		    EXEC @retcode = @proc @check_replication = 0, @throw_error = 0
		END
		ELSE
		BEGIN
			-- drop replication symetrickey
			-- in this case since we are only dropping one merge/tran it's possibe
			-- that we still need the symetric key... so in this case we will check
			SELECT @proc = QUOTENAME(@dbname) + N'.sys.sp_dropreplsymmetrickey'
		    EXEC @retcode = @proc @check_replication = 1, @throw_error = 0
		END
    end
    else
    begin
        exec @retcode  = sys.sp_MSrepl_clean_replication_bit @dbname=@dbname,@type=@type	
    end


    if @@error <> 0 select @retcode = 1
    return @retcode


 
Last revision 2008RTM
See also

  sp_MSrestoredbreplication (Procedure)
sp_subscription_cleanup (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