Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_ORAdroppublisher

  No additional text.


Syntax


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

 
Last revision 2008RTM
See also

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