Home Microsoft SQL Server DigiMailing iRN Contact


  No additional text.


-- Name:
--		sp_validatecache

-- Description:
--		Validate the distributor side Oracle meta data (Oracle specific)

-- Inputs:
--		@publisher		== name of Oracle publisher
--		@publication		== publication name (default '%')
--		@article		== table name (defulat '%')

-- Returns:
--		Return code (0 for success, 1 for failure)

--		Result set

--		column				description

--		publication			publication name
--		article				article name
--		column				column name
--		index_or_constraint index or constraint name
--		columnordinal		column ordinal
--		type				column, index, or constraint type
--		length				length
--		prec				precision
--		scale				scale
--		isnullable			is nullable
--      validate            primary key validated
--      consstatus          constraint status
--      idxstatus           index status
--		description			'Column dropped'
--							'Column added'
--							'Column changed from'
--							'Column changed to'
--							'Index dropped'
--							'Index added'
--							'Index changed from'
--							'Index changed to'
--							'Constraint dropped'
--							'Constraint added'
--							'Constraint changed from'
--							'Constraint changed to'
--							'Index column dropped'
--							'Index column added'
--							'Constraint column dropped'
--							'Constraint column added'

-- Security:
--		public -- caller must be sysadmin

-- Notes:
--		This stored procedure is provided so that the administrator of Oracle
--		publishing can determine whether the current meta data for published
--		Oracle tables that is cached at the distributor, is still valid.  It is
--		intended to assist in diagnosing problems resulting from a meta data
--		mismatch between the Oracle database and the distributor meta data cache.

--		If the @publication parameter is not specified, all publications for the
--		Oracle publisher are examined.  If the @article parameter is not specified,
--		all articles for the given publication are examined.  If @article is non NULL,
--		and @publication is NULL, articles of the specified name from all publications
--		are examined.

--		If an empty rowset is returned, the distributor meta data is valid.
--		If a non-empty rowset is returned, the distributor meta data has been invalidated
--		by meta data changes at the Oracle publisher.

CREATE PROCEDURE sys.sp_validatecache
	@publisher		sysname,
	@publication	sysname = '%',
	@article		sysname = '%'

	DECLARE @cmd			nvarchar(4000)
	DECLARE @retcode		int
	DECLARE @publisher_type	sysname

	-- Security Check: requires sysadmin, done in sp_MSrepl_getpublisherinfo

	SET @retcode = 0

	EXEC @retcode = sys.sp_MSrepl_getpublisherinfo	@publisher      = @publisher,
													@rpcheader      = @cmd OUTPUT,
													@publisher_type	= @publisher_type OUTPUT,
													@hreplOnly      = 1							
	IF @retcode <> 0
		RETURN (@retcode)

	-- Error if the publisher is not an Oracle publisher
	IF @publisher_type NOT LIKE 'ORACLE%'
		RAISERROR (21687, 16, -1, @publisher, @publisher_type)
    	RETURN (1)

	SET @publisher = UPPER(@publisher) COLLATE DATABASE_DEFAULT
	set @cmd = @cmd + N'sys.sp_MSrepl_validatecache'
	EXEC @retcode = @cmd	@publisher,
	RETURN (@retcode)

Last revision 2008RTM
See also

  sp_MSdrop_repltran (Procedure)


  Query a named instance
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
Zoekmachine optimalisatie
SQL Servers hidden objects
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
sIFR; de combinatie tussen HTML en Flash