Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSdrop_peertopeer_tables

  No additional text.


Syntax
-- Name:	
--		sp_MSdrop_peertopeer_tables
--			
-- Description:
--		Drops peer to peer meta-data tables
--	
-- Security:
--		NONE... the procedure is not public

-- Returns: 	
--		0 : success
--		1 : failure 		

CREATE PROCEDURE sys.sp_MSdrop_peertopeer_tables
(
    @from_backup bit = 0
)
AS
BEGIN
	DECLARE @retcode bit

	BEGIN TRANSACTION tran_sp_MSdrop_peer_tables
	SAVE TRANSACTION tran_sp_MSdrop_peer_tables
	
	IF OBJECT_ID(N'MSpeer_lsns', 'U') IS NOT NULL
    BEGIN
    	IF NOT EXISTS(SELECT *
    				FROM dbo.MSpeer_lsns)
    	BEGIN
			if @from_backup = 0
			begin
				DROP TABLE dbo.MSpeer_lsns
				IF @@ERROR <> 0
				    GOTO UNDO

				--if we are dropping MSpeer_lsns, no need to keep p2p alerts around anymore
			    declare @alert_name nvarchar(max)
			    			,@alert_id int = 22815
				IF EXISTS
				(
					SELECT	*
					FROM	msdb.dbo.sysalerts
					WHERE	message_id = @alert_id
				)
				BEGIN
				    select @alert_name=name from msdb.dbo.sysalerts where message_id=@alert_id
			
				    EXEC @retcode = msdb.dbo.sp_delete_alert	@name = @alert_name

				    IF (@@ERROR != 0 OR @retcode != 0)
				    BEGIN
				         goto UNDO
				    END
				END
            end
		END
    END

    IF OBJECT_ID(N'MSpeer_request', 'U') IS NOT NULL
    BEGIN
    	IF NOT EXISTS(SELECT *
    				FROM dbo.MSpeer_request)
    	BEGIN
			DROP TABLE dbo.MSpeer_request
			IF @@ERROR <> 0
				GOTO UNDO
		END
    END

    IF OBJECT_ID(N'MSpeer_response', 'U') IS NOT NULL
    BEGIN
    	IF NOT EXISTS(SELECT *
    				FROM dbo.MSpeer_response)
    	BEGIN
			DROP TABLE dbo.MSpeer_response
			IF @@ERROR <> 0
				GOTO UNDO
		END
    END

    IF OBJECT_ID(N'MSpeer_topologyrequest', 'U') IS NOT NULL
    BEGIN
    	IF NOT EXISTS(SELECT *
    				FROM dbo.MSpeer_topologyrequest)
    	BEGIN
			DROP TABLE dbo.MSpeer_topologyrequest
			IF @@ERROR <> 0
				GOTO UNDO
		END
    END

    IF OBJECT_ID(N'MSpeer_topologyresponse', 'U') IS NOT NULL
    BEGIN
    	IF NOT EXISTS(SELECT *
    				FROM dbo.MSpeer_topologyresponse)
    	BEGIN
			DROP TABLE dbo.MSpeer_topologyresponse
			IF @@ERROR <> 0
				GOTO UNDO
		END
    END

    IF OBJECT_ID(N'MSpeer_originatorid_history', 'U') IS NOT NULL
    BEGIN
    	IF NOT EXISTS(SELECT *
    				FROM dbo.MSpeer_originatorid_history)
    	BEGIN
    		if @from_backup = 0
    		begin
             		DROP TABLE dbo.MSpeer_originatorid_history
		  	IF @@ERROR <> 0
			    GOTO UNDO
	        end
	END
    END

    IF OBJECT_ID(N'MSpeer_conflictdetectionconfigresponse', 'U') IS NOT NULL
    BEGIN
    	IF NOT EXISTS(SELECT *
    				FROM dbo.MSpeer_conflictdetectionconfigresponse)
    	   and
    	   NOT EXISTS(SELECT *
    				FROM dbo.MSpeer_conflictdetectionconfigrequest)
    	BEGIN
			DROP TABLE dbo.MSpeer_conflictdetectionconfigresponse
			IF @@ERROR <> 0
				GOTO UNDO
	END
    END

    IF OBJECT_ID(N'MSpeer_conflictdetectionconfigrequest', 'U') IS NOT NULL
    BEGIN
    	IF NOT EXISTS(SELECT *
    				FROM dbo.MSpeer_conflictdetectionconfigrequest)
    	BEGIN
			DROP TABLE dbo.MSpeer_conflictdetectionconfigrequest
			IF @@ERROR <> 0
				GOTO UNDO
	END
    END

	COMMIT TRANSACTION tran_sp_MSdrop_peer_tables

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

 
Last revision 2008RTM
See also

  sp_MSdrop_pub_tables (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