-- Name:
--          sp_ORAdroppublisher

-- Description:
--          Drop Oracle publisher bits (Oracle side)

-- Security:
--          Internal

-- Returns:
--          Success/failure

-- Owner:
--          

CREATE PROCEDURE sys.sp_ORAdroppublisher
(
	@publisher	sysname
)
AS
BEGIN
	DECLARE @retcode int
	DECLARE @result  int
	DECLARE @owner	 sysname
	
	-- Use @result for intermediate actions for each sub query
	-- If any one of them fails, return an overall failure code
	-- @retcode to indicate that it did not completely succeed.
	-- However, try to run each step irregardless of if any of
	-- the other steps fail.
	SET @retcode = 0

	SET NOCOUNT ON

	-- Define sp_IHquery support table
	create table #hquery
	(
		seq	int identity(2,1),
		cmd	nvarchar(4000)
	)
	
	-- Define object check table
	create table #objcheck (obj sysname)

	-- Get publisher owner
	SELECT	@owner = login
	FROM	msdb.dbo.MSdistpublishers
	WHERE	name = UPPER(@publisher) COLLATE DATABASE_DEFAULT

	-- Verify MSSQLServerDistributor synonym exists
	INSERT INTO #hquery(cmd) VALUES ('SELECT SYNONYM_NAME FROM ALL_SYNONYMS ')
	INSERT INTO #hquery(cmd) VALUES ('WHERE OWNER = ''PUBLIC'' AND  SYNONYM_NAME = ''MSSQLSERVERDISTRIBUTOR''')

	EXEC @retcode = sys.sp_ORAremotequery	@Server				= @publisher,
											@SelectColumnList	= N'SYNONYM_NAME',
											@InsTable			= N'#objcheck',
											@InsColumnList		= N'obj'

	IF (@retcode != 0) OR (@@ERROR != 0)
	BEGIN
		RETURN (1)
	END

	IF EXISTS (SELECT obj FROM #objcheck)
	BEGIN
		--  Drop the public synonym for HREPL_Distributor - ignore the error
		TRUNCATE TABLE #hquery
		INSERT INTO #hquery (cmd) VALUES (N'DROP PUBLIC SYNONYM MSSQLSERVERDISTRIBUTOR')
		EXEC @result = sys.sp_IHquery @publisher
		IF (@result != 0)
		BEGIN
			SET @retcode = 1
		END
	END

	-- Verify MSSQLServerSetSQLOriginator synonym exists
	TRUNCATE TABLE #objcheck
	TRUNCATE TABLE #hquery
	INSERT INTO #hquery(cmd) VALUES ('SELECT SYNONYM_NAME FROM ALL_SYNONYMS ')
	INSERT INTO #hquery(cmd) VALUES ('WHERE OWNER = ''PUBLIC'' AND  SYNONYM_NAME = ''MSSQLSERVERSETSQLORIGINATOR''')

	EXEC @retcode = sys.sp_ORAremotequery	@Server				= @publisher,
											@SelectColumnList	= N'SYNONYM_NAME',
											@InsTable			= N'#objcheck',
											@InsColumnList		= N'obj'

	IF (@retcode != 0) OR (@@ERROR != 0)
	BEGIN
		RETURN (1)
	END

	IF EXISTS (SELECT obj FROM #objcheck)
	BEGIN
		--  Drop the public synonym for HREPL.SetSQLOriginator - ignore the error
		TRUNCATE TABLE #hquery
		INSERT INTO #hquery (cmd) VALUES (N'DROP PUBLIC SYNONYM MSSQLSERVERSETSQLORIGINATOR')
		EXEC @result = sys.sp_IHquery @publisher
		IF (@result != 0)
		BEGIN
			SET @retcode = 1
		END
	END
	
	-- Verify HREPL_DropPublisher procedure exists
	TRUNCATE TABLE #objcheck
	TRUNCATE TABLE #hquery
	INSERT INTO #hquery(cmd) VALUES ('SELECT OBJECT_NAME FROM ALL_OBJECTS ')
	INSERT INTO #hquery(cmd) VALUES ('WHERE OWNER = ' + QUOTENAME(@owner, '''') + ' ')
	INSERT INTO #hquery(cmd) VALUES ('AND OBJECT_NAME = ''HREPL_DROPPUBLISHER'' ')
	INSERT INTO #hquery(cmd) VALUES ('AND OBJECT_TYPE = ''PROCEDURE''')

	EXEC @retcode = sys.sp_ORAremotequery	@Server				= @publisher,
											@SelectColumnList	= N'OBJECT_NAME',
											@InsTable			= N'#objcheck',
											@InsColumnList		= N'obj'

	IF (@retcode != 0) OR (@@ERROR != 0)
	BEGIN
		RETURN (1)
	END

	IF EXISTS (SELECT obj FROM #objcheck)
	BEGIN
		--  Call the remote routine to drop the publisher - ignore the error
		TRUNCATE TABLE #hquery
		INSERT INTO #hquery (cmd) VALUES (N'{call HREPL_DROPPUBLISHER}')
		EXEC @result = sys.sp_IHquery @publisher
		IF (@result != 0)
		BEGIN
			SET @retcode = 1
		END
	
		-- Drop the helper proc HREPL_DropPublisher
		TRUNCATE TABLE #hquery
		INSERT INTO #hquery (cmd) VALUES (N'DROP PROCEDURE HREPL_DROPPUBLISHER')
		EXEC @result = sys.sp_IHquery @publisher
		IF (@result != 0)
		BEGIN
			SET @retcode = 1
		END
	END
	
	-- Verify HREPL_ExecuteCommand procedure exists
	TRUNCATE TABLE #objcheck
	TRUNCATE TABLE #hquery
	INSERT INTO #hquery(cmd) VALUES ('SELECT OBJECT_NAME FROM ALL_OBJECTS ')
	INSERT INTO #hquery(cmd) VALUES ('WHERE OWNER = ' + QUOTENAME(@owner, '''') + ' ')
	INSERT INTO #hquery(cmd) VALUES ('AND OBJECT_NAME = ''HREPL_EXECUTECOMMAND''')
	INSERT INTO #hquery(cmd) VALUES ('AND OBJECT_TYPE = ''PROCEDURE''')

	EXEC @retcode = sys.sp_ORAremotequery	@Server				= @publisher,
											@SelectColumnList	= N'OBJECT_NAME',
											@InsTable			= N'#objcheck',
											@InsColumnList		= N'obj'

	IF (@retcode != 0) OR (@@ERROR != 0)
	BEGIN
		RETURN (1)
	END

	IF EXISTS (SELECT obj FROM #objcheck)
	BEGIN
		-- Drop helper proc HREPL_ExecuteCommand
		TRUNCATE TABLE #hquery
		INSERT INTO #hquery (cmd) VALUES (N'DROP PROCEDURE HREPL_EXECUTECOMMAND')
		EXEC @result = sys.sp_IHquery @publisher
		IF (@result != 0)
		BEGIN
			SET @retcode = 1
		END
	END

	RETURN(@retcode)
END