Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSremovedbreplication

  No additional text.


Syntax


-- Name: sp_MSremovedbreplication

-- Descriptions:

-- Parameters: as defined in create statement

-- Returns: 0 - success
--          1 - Otherwise

-- Security:
-- Requires Certificate signature for catalog access

create procedure sys.sp_MSremovedbreplication
(
	@type				nvarchar(5) = 'both',	-- 'merge' or 'tran' or 'both' to cleanup.
	@ignore_distributor	bit = 0,
    @from_backup        bit = 0
)
AS
begin
    
    --  Note: There is zero error handling in this procedure, as it is considered a
    --         best effort cleanup. Failed procedures should still report their errors.
    --         See SQL BU #387130 for more information.
    

    SET NOCOUNT ON
	DECLARE @ErrorMessage NVARCHAR(4000)

	
    /*
    ** Declarations.
    */

    DECLARE @retcode int
                ,@pubid uniqueidentifier
                ,@whattodrop int
                ,@drop_jobs int
                ,@job_id varbinary(18)
                ,@job_step_uid uniqueidentifier
                ,@agent_name sysname
                ,@publisher sysname
                ,@publisher_db sysname
    			,@distributor_rpc sysname
    			,@distribution_db sysname
    			,@procedure	nvarchar(4000)
                ,@REPLICA_STATUS_BeforeRestore tinyint
                ,@SUBSCRIPTION_TYPE_LIGHTWEIGHT tinyint
                ,@regkey nvarchar(2000)
                ,@subscription_name nvarchar(2000)
                ,@publisher_db_prefix nvarchar(514)
                ,@local_regkey nvarchar(2000)

    declare @keyexist table (KeyExist int)
	

    select @whattodrop = 0
    		,@publisher = publishingservername()
    		,@publisher_db = DB_NAME()
            ,@REPLICA_STATUS_BeforeRestore= 7
            ,@SUBSCRIPTION_TYPE_LIGHTWEIGHT= 3

    -- If the attach info table exists, we need to restore the subscriptions, not
    -- deleting anything. Do nothing.
    if exists (select * from sys.objects where
                name = 'MSreplication_restore_stage')
        return 0



    
	-- cleanup any distributor side jobs if we are not ignoring distrib
	
	
    SELECT @drop_jobs = 0

	IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) IN ('tran', 'both')
    	AND DatabasePropertyEx(@publisher_db, 'IsPublished') = 1
	BEGIN
    	SELECT @drop_jobs = 1
    END

	IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) IN ('merge', 'both')
		AND DatabasePropertyEx(@publisher_db, 'IsMergePublished') = 1
    BEGIN
		SELECT @drop_jobs = 1
    END

    IF @drop_jobs = 1
    	AND @ignore_distributor = 0
    BEGIN					
		-- retrieve the distribution database name			
		EXEC @retcode = sys.sp_helpdistributor @rpcsrvname = @distributor_rpc OUTPUT,
												@distribdb = @distribution_db OUTPUT,
												@publisher = @publisher
		IF @@ERROR <> 0 OR @retcode <> 0 OR @distribution_db is NULL
		BEGIN
			-- "The Distributor has not been installed correctly."
			RAISERROR(20036, 10, -1)		
		END
								
		SELECT @procedure =  QUOTENAME(RTRIM(@distributor_rpc)) + '.' + QUOTENAME(RTRIM(@distribution_db)) + '.sys.sp_MSforce_drop_distribution_jobs'
		EXEC @retcode = @procedure @publisher = @publisher,
									@publisher_db = @publisher_db
									,@type = @type        		
    END

    
	-- Drop publisher side jobs
	

	-- This job is required if at least one published db exists
	SELECT @agent_name = FORMATMESSAGE(20569)
		
	IF EXISTS(SELECT *
				FROM msdb.dbo.sysjobs_view
				WHERE name = @agent_name collate database_default
					AND UPPER(originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))
	                AND master_server = 0)
	    AND NOT EXISTS(SELECT name
	    				FROM master.dbo.sysdatabases
	    				WHERE category & 4 = 4
	    					AND name != @publisher_db)
		AND NOT EXISTS(SELECT name
						FROM master.dbo.sysdatabases
						WHERE category & 1 =1
							AND name != @publisher_db)
	BEGIN
		EXEC @retcode = sys.sp_MSdrop_repl_job @job_name = @agent_name
	END
	
    
    -- clean up transactional
    
    IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) IN ('tran', 'both')
    BEGIN    	
	    	
			-- Drop subscriber side jobs
			
			IF OBJECT_ID('MSreplication_subscriptions', 'U') IS NOT NULL
				AND OBJECT_ID('MSsubscription_properties', 'U') IS NOT NULL
			BEGIN
				DECLARE cursorSubAgents CURSOR LOCAL FAST_FORWARD FOR
					SELECT msrs.agent_id,
							mssp.job_step_uid
				    	FROM MSreplication_subscriptions msrs
				    		LEFT JOIN MSsubscription_properties mssp
				    			ON UPPER(msrs.publisher) = UPPER(mssp.publisher)
							       AND msrs.publisher_db = mssp.publisher_db
							       AND msrs.publication = mssp.publication
							       AND mssp.publication_type in (0, 1)
				FOR READ ONLY

				OPEN cursorSubAgents

				FETCH cursorSubAgents INTO @job_id, @job_step_uid
				WHILE @@FETCH_STATUS != -1
				BEGIN
					IF EXISTS (SELECT *
								FROM msdb.dbo.sysjobs_view
								WHERE job_id = @job_id)
					BEGIN		
						EXEC @retcode = sys.sp_MSdrop_repl_job @job_id = @job_id,
																@job_step_uid = @job_step_uid
					END
					
					FETCH cursorSubAgents INTO @job_id, @job_step_uid
				END

				CLOSE cursorSubAgents
				DEALLOCATE cursorSubAgents
			END
	
		
	    if object_id(N'sysarticles', N'U') is not null
	    begin
	        if not exists (select * from master.dbo.MSreplication_options
	                        where optname = 'transactional')
	        begin
	            RAISERROR(21027, 10, -1, 'transactional')	
	        end
		
            exec sys.sp_MSdrop_synctran_objects

            -- Attempt to drop all publisher tables
		    exec sys.sp_MSdrop_pub_tables
        end
    END

    IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) IN ('merge', 'both')
	BEGIN
    	
	    
		-- Drop subscriber side jobs
		
		IF OBJECT_ID('MSmerge_replinfo', 'U') IS NOT NULL
			AND OBJECT_ID('sysmergepublications', 'U') IS NOT NULL
			AND OBJECT_ID('sysmergesubscriptions', 'U') IS NOT NULL
			AND OBJECT_ID('MSsubscription_properties', 'U') IS NOT NULL
		BEGIN
			DECLARE cursorSubAgents CURSOR LOCAL FAST_FORWARD FOR
				SELECT msmr.merge_jobid,
						mssp.job_step_uid
				    FROM sysmergepublications smp
				    	JOIN sysmergesubscriptions sms
				    		ON smp.pubid = sms.pubid
				    	JOIN MSmerge_replinfo msmr
				    		ON sms.subid = msmr.repid
				    	LEFT JOIN MSsubscription_properties mssp
				    		ON UPPER(smp.publisher) = UPPER(mssp.publisher)
							   AND smp.publisher_db = mssp.publisher_db
							   AND smp.name = mssp.publication
			FOR READ ONLY
		
			OPEN cursorSubAgents

			FETCH cursorSubAgents INTO @job_id, @job_step_uid
			WHILE @@FETCH_STATUS != -1
			BEGIN
				IF EXISTS (SELECT *
							FROM msdb.dbo.sysjobs_view
							WHERE job_id = @job_id)
				BEGIN
					EXEC @retcode = sys.sp_MSdrop_repl_job @job_id = @job_id,
														@job_step_uid = @job_step_uid		
				END
				
				FETCH cursorSubAgents INTO @job_id, @job_step_uid
			END

			CLOSE cursorSubAgents
			DEALLOCATE cursorSubAgents
		END


		-- merge cleanup. Drop the ddl triggers first because we don't want the alters that are done
		-- because of the cleanup below to be tracked in the ddl triggers anyway.
		execute @retcode=sys.sp_MSrepl_ddl_triggers @type='merge', @mode='drop'	
									
		    	
	    -- Cleanup for hws.
	    -- can not use sp_MSmergepublishdb or sp_dropmergepullsubscriptions
	    -- since they depend on serverid and dbname
	    
	    if object_id(N'sysmergearticles', N'U') is not null
	    begin
	        declare @artid 			uniqueidentifier
    		declare @objid          int
		    declare @ownername      sysname
		    declare @objectname     sysname
                declare @partition_id_eval_proc nvarchar(255)
 declare @membership_eval_proc_name sysname
                declare @expand_proc_name nvarchar(255)
                declare @expand_proc        sysname
                declare @tmp_procname nvarchar(517)
                declare @pubidstr           nvarchar(32)
                declare @artidstr               nvarchar(32)
                declare @art_proc_suffix nvarchar(40)

	        declare #hC CURSOR LOCAL FAST_FORWARD FOR
	            select DISTINCT pubid, artid
	            FROM dbo.sysmergearticles
	            where lightweight=0				
	        FOR READ ONLY

	        OPEN #hC
	        FETCH #hC INTO @pubid, @artid
	        WHILE (@@fetch_status <> -1)
	        begin
						
                EXEC @retcode = sys.sp_MSarticlecleanup
		        				@pubid, @artid, 1 -- set ignore_merge_metadata to 1 so that we drop the objects but not the metadata
			-- If article cleanup fails and force is set we should unmark the article and move to next article.
                IF (@@ERROR <> 0 or @retcode <> 0)
                begin														
                    select @objid = max(objid) from dbo.sysmergearticles where artid = @artid
                    if @objid is not NULL
                    begin
			    -- get owner name, and table name
                        select @objectname = name, @ownername = schema_name(schema_id) from sys.objects where object_id = @objid
                        exec @retcode=sys.sp_MSunmarkreplinfo @object=@objectname, @owner=@ownername
                    end				
                end			
                else
		  begin
                    --bug 428665
                    exec @retcode = dbo.sp_MSguidtostr @pubid, @pubidstr out
                    if @@ERROR <>0 OR @retcode <>0 return (1)

                    select @partition_id_eval_proc = 'MSmerge_evalpartid_sp_' + substring(@pubidstr, 1, 16)
                    if object_id(@partition_id_eval_proc) is not NULL
                    begin
                        select @ownername = schema_name(schema_id) from sys.objects where name=@partition_id_eval_proc
                        select @tmp_procname = QUOTENAME(@ownername) + '.' + QUOTENAME(@partition_id_eval_proc)
                        exec ('drop proc ' + @tmp_procname)
                        if @@error<>0 return 1
                    end

                        --drop the expand procs for the filters. we need to drop them before the system table
                        -- sysmergesubsetfilters is dropped
                        if (object_id('dbo.sysmergesubsetfilters') is not null)
                        begin
                            select @expand_proc_name = expand_proc from dbo.sysmergesubsetfilters where pubid = @pubid and artid = @artid
                            if (@expand_proc_name is not NULL) and (@expand_proc_name <> '') and (object_id( @expand_proc_name) is not null)
                            begin
                                select @ownername = schema_name(schema_id) from sys.objects where name = @expand_proc_name
                                select @tmp_procname = QUOTENAME(@ownername) + '.' + QUOTENAME(@expand_proc_name)
                                exec ('drop proc ' + @tmp_procname)
                                if @@ERROR <>0 return 1
                            end
                        end

                        exec @art_proc_suffix = sys.fn_MSmerge_getartprocsuffix @artid, @pubid
                        select @membership_eval_proc_name = 'MSmerge_evalmembership_sp_' + @art_proc_suffix
                        if object_id (@membership_eval_proc_name) is not null
                        begin
                            select @ownername = schema_name(schema_id) from sys.objects where name=@membership_eval_proc_name
                            select @tmp_procname = QUOTENAME(@ownername) + '.' + QUOTENAME(@membership_eval_proc_name)
                            exec ('drop proc ' + @tmp_procname)
                            if @@ERROR<>0 return (1)
                        end

                        exec @retcode=sys.sp_MSguidtostr @artid, @artidstr out
                        if @retcode<>0 or @@ERROR<>0
                            return (1)

                        select @expand_proc = 'MSmerge_expand_sp_' + @artidstr
                        if object_id(@expand_proc) is not null
                        begin
                            select @ownername = schema_name(schema_id) from sys.objects where name=@expand_proc
                            select @tmp_procname = QUOTENAME(@ownername) + '.' + QUOTENAME(@expand_proc)
                            exec ('drop proc ' + @tmp_procname)
                            if @@ERROR<>0 return (1)
                        end
		end

	            FETCH #hC INTO @pubid, @artid
	        end
	        close #hC
	        deallocate #hC
	        select @whattodrop= 1
	    end
	    
	    -- Cleanup for lws.
	    
	    if object_id('dbo.sysmergesubscriptions', 'U') is not null
	    begin
	        declare #hC CURSOR LOCAL FAST_FORWARD FOR
	            select DISTINCT pubid
	            FROM dbo.sysmergesubscriptions
	            where subscription_type = @SUBSCRIPTION_TYPE_LIGHTWEIGHT and
	                status <> @REPLICA_STATUS_BeforeRestore and
	                lower(subscriber_server) collate database_default = lower(@@servername) collate database_default and
	                db_name = db_name()
	        FOR READ ONLY

	        OPEN #hC
	        FETCH #hC INTO @pubid
	        WHILE (@@fetch_status <> -1)
	        BEGIN
	            exec @retcode= sys.sp_MSdeletelightweightsubscription
	                                        @publication_id = @pubid
	
	            FETCH #hC INTO @pubid
	        END

	        close #hC
	        deallocate #hC
	        select @whattodrop= @whattodrop + 2
	    end
	    
	    -- cleanup for roles
	    --	
		if object_id('dbo.sysmergepublications', 'U') is not NULL
		begin
			exec @retcode = sys.sp_MSdropmergepalrole
		
			
			-- drop the database pal role
			
			declare @role sysname
			select @role = 'MSmerge_PAL_role'
			if exists (select * from sys.database_principals where name=@role and type = 'R')
			begin
				declare @member sysname
				
				-- there could be roles of stale publications which are still members of the
				-- database wide pal role. The code below takes care of dropping all members
				-- of the database wide pal role before dropping it.
				declare role_members cursor local fast_forward
				for select u.name as MemberName
	        			from sys.database_principals u, sys.database_principals g, sys.database_role_members m
	        			where g.name = @role
	        				and g.principal_id = m.role_principal_id
	        				and g.type = 'R'
	        				and u.principal_id = m.member_principal_id
	        				
				open role_members
				fetch role_members into @member
				while @@fetch_status <> -1
				begin						
					exec @retcode = sys.sp_droprolemember @role, @member					
					fetch role_members into @member
				end
				close role_members
				deallocate role_members
				
				exec @retcode = sys.sp_droprole @role					
			end
		end
		
	    
	    -- misc merge cleanup
	    --	
		if 0 <> @whattodrop
		begin
			execute @retcode = sys.sp_MSdrop_mergesystables @whattodrop=@whattodrop		
		end
	
    END

    
    -- clean up tran sub
    
    IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) IN ('tran', 'both')
    BEGIN
	    IF OBJECT_ID('MSreplication_subscriptions', 'U') IS NOT NULL
	    BEGIN
	        
	        -- drop pull subscription
	        --		
			EXEC @retcode = sys.sp_droppullsubscription
										@publisher = N'all',
										@publisher_db = N'all',
										@publication = N'all',
                                        @from_backup = @from_backup
									
				
			
	        
	        -- drop push subscription
	        --		
			EXEC @retcode = sys.sp_subscription_cleanup
										@publisher = N'all',
										@publisher_db = N'all',
										@publication = N'all',
										@reserved = 'drop_all',
                                        @from_backup = @from_backup
		
			

	    END
	END
    
    -- this is needed here because in the merge case the above if would not
    -- have been executed.
    
	IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) IN ('merge', 'both')
    BEGIN
		IF OBJECT_ID('MSsubscription_properties', 'U') IS NOT NULL
	    BEGIN			
			EXEC @retcode = sys.sp_MSsub_cleanup_prop_table
			EXEC @retcode = sys.sp_resetsnapshotdeliveryprogress @drop_table = N'true'
		END
	END
	
    -- cleanup the objects that are still marked by replication bits
    
	IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) IN ('tran', 'both')
    begin
	    exec @retcode = sys.sp_MScleandbobjectsforreplication	
	end

    
    -- Clean up syncmgr Registry Keys
    
    -- This is a brute force cleanup for this particular database. This is required as, for performance reasons,
    --  we are no longer calling sp_MSpublishdb above, which previously called sp_dropsubscription, which cleaned up
    --  these keys.
    SET @regkey = sys.fn_replgetsqlserverregkey() collate database_default + 'Replication\Subscriptions\'
    insert into @keyexist exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', @regkey

    if exists (select KeyExist from @keyexist where KeyExist = 1)
    begin
        create table #syncmgr_subscriptions (subscription_name nvarchar(2000) collate database_default)
        SET @publisher_db_prefix = publishingservername() + ':' + db_name()

        insert into #syncmgr_subscriptions execute master.dbo.xp_regenumkeys 'HKEY_LOCAL_MACHINE', @regkey

        -- Filter for only those relating to this database as the registry could
        --  contain keys for other databases on this server
        declare #subscriptioncursor cursor LOCAL FAST_FORWARD FOR
            select distinct subscription_name
                    from #syncmgr_subscriptions
                        where left(subscription_name, len(@publisher_db_prefix)) = @publisher_db_prefix
                        for read only
        open #subscriptioncursor
        fetch next from #subscriptioncursor into @subscription_name
        while (@@fetch_status <> -1)
        begin
            set @local_regkey = @regkey + @subscription_name

            exec master.dbo.xp_regdeletekey 'HKEY_LOCAL_MACHINE', @local_regkey

            fetch next from #subscriptioncursor into @subscription_name
        end
        close #subscriptioncursor
        deallocate #subscriptioncursor

        drop table #syncmgr_subscriptions
    end
    
    -- all done
    
    return 0
end

 
Last revision 2008RTM
See also

  sp_attach_single_file_db (Procedure)
sp_MSremovedbreplication_internal (Procedure)
sp_MSrestoredbreplication (Procedure)
sp_removedbreplication (Procedure)
sp_removesrvreplication (Procedure)
sp_vupgrade_mergetables (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