Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MScleanup_peer_metadata

  No additional text.


Syntax
CREATE PROCEDURE sys.sp_MScleanup_peer_metadata
(
	@type 			int,	-- 0-Drop Publication(pubside) 1-Cleanup Subscription(subside)
	@publication	sysname,
    @from_backup    bit = 0 -- 0-Cleanup all, 1 = From restore database, so we leave the MSpeer_lsns table
)
AS
BEGIN
	DECLARE @retcode int
	
	IF OBJECT_ID(N'MSpeer_lsns', 'U') IS NULL
    BEGIN
		RETURN 0
	END
	
	BEGIN TRANSACTION tr_del_peer_meta
	SAVE TRANSACTION tr_del_peer_meta

	/*
	 * Publisher side call for drop publication
	 */
	IF @type = 0
	BEGIN
		-- if a p2p subscription is found we must exit without cleaning up the
		-- metadata since the subscription will need it for replicating data
		IF OBJECT_ID('MSreplication_subscriptions', 'U') IS NOT NULL
		BEGIN
			IF EXISTS(SELECT *
						FROM MSpeer_lsns mspl
							JOIN MSreplication_subscriptions msrs
								ON msrs.publication = mspl.originator_publication
						WHERE msrs.publication = @publication
							OR LOWER(LTRIM(RTRIM(@publication))) = N'all')
			BEGIN
				GOTO COMMIT_TRAN
			END
		END
	END
	/*
	 * Subscriber side call for cleanup subscription
	 */
	ELSE IF @type = 1
	BEGIN
		-- cleanup PeerToPeer Metadata only if we find
		-- a peer lsn entry for the given publication
		IF NOT EXISTS(SELECT *
						FROM MSpeer_lsns mspl
						WHERE mspl.originator_publication = @publication
							OR LOWER(LTRIM(RTRIM(@publication))) = N'all')
		BEGIN
			GOTO COMMIT_TRAN
		END

		-- Cleanup the MSsubscription_articles table
		IF OBJECT_ID('MSsubscription_articles', 'U') IS NOT NULL
			AND OBJECT_ID('MSsubscription_agents', 'U') IS NOT NULL
		BEGIN
			DELETE MSsubscription_articles
				FROM MSsubscription_agents mssag
				WHERE agent_id = mssag.id
					AND (mssag.publication = @publication
						OR LOWER(LTRIM(RTRIM(@publication))) = N'all')
			IF @@ERROR <> 0 goto UNDO
		END

		-- if this database is a republisher and we still
		-- have peer_lsn meta-data then exit since we know
		-- that it is still being used by a publication...
		IF sys.fn_MSrepl_istranpublished(DB_NAME(),0) = 1
			AND OBJECT_ID('syspublications', 'U') IS NOT NULL
		BEGIN
			IF EXISTS(SELECT *
						FROM MSpeer_lsns mspl
							JOIN syspublications sp
								ON sp.name = mspl.originator_publication
						WHERE sp.name = @publication
							OR LOWER(LTRIM(RTRIM(@publication))) = N'all')
			BEGIN
				GOTO COMMIT_TRAN
			END
		END
	END

	-- Cleanup p2p meta-data
	IF OBJECT_ID('MSpeer_lsns', 'U') IS NOT NULL
	BEGIN
        if @from_backup = 0
        begin
		    DELETE MSpeer_lsns
			    WHERE originator_publication = @publication
				    OR LOWER(LTRIM(RTRIM(@publication))) = N'all'
		    IF @@ERROR <> 0 goto UNDO
        end
	END

	IF OBJECT_ID('MSpeer_response', 'U') IS NOT NULL
	BEGIN
		DELETE MSpeer_response
			FROM dbo.MSpeer_request
			WHERE (publication = @publication
					OR LOWER(LTRIM(RTRIM(@publication))) = N'all')
				AND request_id = id
		IF @@ERROR <> 0 goto UNDO
	END

	IF OBJECT_ID('MSpeer_request', 'U') IS NOT NULL
	BEGIN
		DELETE MSpeer_request
			WHERE publication = @publication
				OR LOWER(LTRIM(RTRIM(@publication))) = N'all'
	IF @@ERROR <> 0 goto UNDO
	END

    	IF OBJECT_ID('MSpeer_topologyresponse', 'U') IS NOT NULL
	BEGIN
		DELETE MSpeer_topologyresponse
			FROM dbo.MSpeer_topologyrequest
			WHERE (publication = @publication
					OR LOWER(LTRIM(RTRIM(@publication))) = N'all')
				AND request_id = id
		IF @@ERROR <> 0 goto UNDO
	END

	IF OBJECT_ID('MSpeer_topologyrequest', 'U') IS NOT NULL
	BEGIN
		DELETE MSpeer_topologyrequest
			WHERE publication = @publication
				OR LOWER(LTRIM(RTRIM(@publication))) = N'all'
		IF @@ERROR <> 0 goto UNDO
	END


    	IF OBJECT_ID('MSpeer_originatorid_history', 'U') IS NOT NULL
	BEGIN
	   if @from_backup = 0
	   begin
		    DELETE MSpeer_originatorid_history
			    WHERE originator_publication = @publication
				    OR LOWER(LTRIM(RTRIM(@publication))) = N'all'
		    IF @@ERROR <> 0 goto UNDO
           end
	END

	IF OBJECT_ID('MSpeer_conflictdetectionconfigresponse', 'U') IS NOT NULL
	BEGIN
		DELETE MSpeer_conflictdetectionconfigresponse
			FROM dbo.MSpeer_conflictdetectionconfigrequest
			WHERE (publication = @publication
					OR LOWER(LTRIM(RTRIM(@publication))) = N'all')
				AND request_id = id
		IF @@ERROR <> 0 goto UNDO
	END

	IF OBJECT_ID('MSpeer_conflictdetectionconfigrequest', 'U') IS NOT NULL
	BEGIN
		DELETE MSpeer_conflictdetectionconfigrequest
			WHERE publication = @publication
				OR LOWER(LTRIM(RTRIM(@publication))) = N'all'
		IF @@ERROR <> 0 goto UNDO
	END

COMMIT_TRAN:
	COMMIT TRANSACTION tr_del_peer_meta

	RETURN 0
UNDO:
	ROLLBACK TRANSACTION tr_del_peer_meta
	COMMIT TRANSACTION
	
	RETURN 1
END

 
Last revision 2008RTM
See also

  sp_MSdrop_repltran (Procedure)
sp_MSrepl_droppublication (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