Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_ORAverifypublisher

  No additional text.


Syntax


-- Name:
--		sp_ORAverifypublisher

-- Description:
--		Oracle-specific verification procedure

-- Returns:
--		0 == Valid
--		1 == Error in publisher state

-- Security:
--		Internal

-- Notes:
--		Verifies following classes of objects:
--			SYNONYM
--			SEQUENCE
--			TABLES
--			PACKAGE
--			PACKAGE BODY
--			PROCEDURES

--		Check is simple existance check.
--		Additional data oriented checks may be added.


CREATE PROCEDURE sys.sp_ORAverifypublisher
(
	@publisher			sysname
)
AS
BEGIN
	DECLARE @retcode			int
	DECLARE @owner				sysname
	DECLARE @publisher_dbms		sysname
    DECLARE @publisher_version	sysname
	DECLARE	@InsColumnList		nvarchar(2000)
	DECLARE	@SelectColumnList	nvarchar(2000)
	DECLARE @objcount			int

	SET NOCOUNT ON

	-- Get HREPL owner for specified publisher
	SELECT	@owner = login
	FROM	msdb.dbo.MSdistpublishers
	WHERE	name = @publisher
	
	-- Set publisher DBMS and version
	SELECT	@publisher_dbms		= 'ORACLE'

	EXEC @retcode = sys.sp_IHgetversion	@publisher	= @publisher,
										@version	= @publisher_version OUTPUT

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

	-- Define sp_ORAremotequery support table
	create table #hquery
	(
		seq	int identity(2,1),
		cmd	nvarchar(4000)
	)

	-- Define output table
	create table #hreplinfo
	(
		type	sysname,
		owner	sysname,
		name	sysname
	)

    -- Get hrepl metadata
	SELECT @InsColumnList    = 'type, owner, name'
	SELECT @SelectColumnList = 'OBJECT_TYPE, OWNER, OBJECT_NAME'

	-- Get objects
	INSERT INTO #hquery(cmd) VALUES('SELECT OBJECT_TYPE, OWNER, OBJECT_NAME ')
	INSERT INTO #hquery(cmd) VALUES('FROM ALL_OBJECTS ')
	-- Admin user objects
	INSERT INTO #hquery(cmd) VALUES('WHERE OWNER = ' + QUOTENAME(@owner, '''') + ' ')
	-- Public synonyms
	INSERT INTO #hquery(cmd) VALUES('UNION ')
	INSERT INTO #hquery(cmd) VALUES('SELECT ''SYNONYM'' AS OBJECT_TYPE, OWNER, SYNONYM_NAME AS OBJECT_NAME ')
	INSERT INTO #hquery(cmd) VALUES('FROM ALL_SYNONYMS ')
	INSERT INTO #hquery(cmd) VALUES('WHERE OWNER = ''PUBLIC'' AND SYNONYM_NAME LIKE ''MSSQL%''')

	EXEC @retcode = sp_ORAremotequery
					@Server				= @publisher,
					@SelectColumnList	= @SelectColumnList,
					@InsTable			= '#hreplinfo',
					@InsColumnList		= @InsColumnList

	IF @@ERROR <> 0 OR @retcode <> 0
	BEGIN
		RAISERROR (21781, 16, -1)
		RETURN (@retcode)
	END

	-- Verify public synonym
	IF NOT EXISTS
	(
		SELECT	name
		FROM	#hreplinfo
		WHERE	type  = 'SYNONYM'
		  AND	name  = 'MSSQLSERVERDISTRIBUTOR'
	)
	BEGIN
		RAISERROR(21780, 10, -1, 'synonym')
		SET @retcode = 1
	END
	
	-- Verify tables
	SELECT	@objcount = COUNT(*)
	FROM	#hreplinfo
	WHERE	type  = 'TABLE'
	  AND	name IN
	  		(
				'HREPL_CHANGES',
				'HREPL_DISTRIBUTOR',
				'HREPL_EVENT',
				'HREPL_MUTEX',
				'HREPL_POLL',
				'HREPL_PUBLISHEDTABLES',
				'HREPL_PUBLISHER',
				'HREPL_SCHEMAFILTER',
				'HREPL_XACTSETCREATETIMES',
				'HREPL_XACTSETJOB'
			)
	
	IF 	@objcount != 10
	BEGIN
		RAISERROR(21780, 10, -1, 'table')
		SET @retcode = 1
	END
	
	-- Verify sequences
	SELECT	@objcount = COUNT(*)
	FROM	#hreplinfo
	WHERE	type = 'SEQUENCE'
	  AND	name IN
	  		(
				'HREPL_POLLID',
				'HREPL_SEQ',
				'HREPL_STMT'
			)
	
	IF 	@objcount != 3
	BEGIN
		RAISERROR(21780, 10, -1, 'sequence')
		SET @retcode = 1
	END

	-- Verify Procedures
	SELECT	@objcount = COUNT(*)
	FROM	#hreplinfo
	WHERE	type = 'PROCEDURE'
	  AND	name IN
	  		(
				'HREPL_DROPPUBLISHER',
				'HREPL_EXECUTECOMMAND'
			)
	
	IF 	@objcount != 2
	BEGIN
		RAISERROR(21780, 10, -1, 'procedure')
		SET @retcode = 1
	END

	-- Verify Packages
	SELECT	@objcount = COUNT(*)
	FROM	#hreplinfo
	WHERE	type = 'PACKAGE'
	  AND	name = 'HREPL'
	
	IF 	@objcount != 1
	BEGIN
		RAISERROR(21780, 10, -1, 'package')
		SET @retcode = 1
	END

	-- Verify Package bodies
	SELECT	@objcount = COUNT(*)
	FROM	#hreplinfo
	WHERE	type = 'PACKAGE BODY'
	  AND	name = 'HREPL'
	
	IF 	@objcount != 1
	BEGIN
		RAISERROR(21780, 10, -1, 'package body')
		SET @retcode = 1
	END

    RETURN (@retcode)
END

 
Last revision 2008RTM
See also

  sp_MSdrop_replcom (Procedure)
sp_verifypublisher (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