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