Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_ORAvalidatecache

  No additional text.


Syntax


-- Name:
--		sp_ORAvalidatecache

-- Description:
--		Validate distributor side meta data against current Oracle meta data
--		for a given published table.

-- Inputs:
--		@publisher		== name of Oracle publisher
--		@publication	== name of publication
--		@article		== name of article
--		@owner			== table owner
--		@tablename		== table name

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

--		Result set

--		column		description

--		publication	publication name
--		article		article name
--		tableowner  table owner
--		tablename	table name
--		columnname	column name
--		index		index or constraint name
--		column_ordinal	column ordinal
--		type		column, index, or constrinat type
--		length		length
--		prec		precision
--		scale		scale
--		isnullable	is nullable
--      validated   is 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'

-- Security:
--		internal stored procedure
-- Requires Certificate signature for catalog access


CREATE PROCEDURE sys.sp_ORAvalidatecache
(
	@publisher		sysname,
	@publication	sysname = NULL,
	@article		sysname = NULL,
	@owner			sysname = NULL,
	@tablename		sysname = NULL
)
AS
BEGIN
	DECLARE @retcode	int
	DECLARE @pubid		int
	DECLARE @table_id	int
	DECLARE @cntcache	int
	DECLARE @cntmatch	int
	DECLARE	@cntactual	int

	-- Get publisher ID
	SELECT @pubid = srvid
	FROM	master.dbo.sysservers
	WHERE	UPPER(srvname collate database_default) = UPPER(@publisher) collate database_default
	
	IF @pubid IS NULL
	BEGIN
		RAISERROR(21600, 16, -1, @publisher)
		RETURN (1)
	END

	-- Table name and owner must both be non-NULL
	IF @tablename IS NULL OR @owner IS NULL
	BEGIN
		-- Both table name and owner must be NON NULL
		RAISERROR('Both table name and table owner must be NON NULL.', 16, -1)
		RETURN (1)
	END

	-- Verify that table is currently published
	-- and determine table ID
	SELECT distinct @table_id = iha.table_id
	FROM	IHarticles iha, MSarticles msa
	WHERE	msa.source_owner 	= @owner
	AND	msa.source_object 	= @tablename
	AND	msa.publisher_id 	= @pubid
	AND	msa.publisher_id 	= iha.publisher_id
	AND	msa.article_id 		= iha.article_id
	AND	msa.publication_id 	= iha.publication_id
	
	IF @table_id IS NULL
	BEGIN
		-- Table is not currently published
		RAISERROR('Table is not currently published.', 16, -1)
		RETURN (1)
	END

	-- Create the temporary meta data tables
	CREATE TABLE #publishercolumns
	(
        name            sysname collate database_default,
		column_ordinal	int,
        type            sysname collate database_default,
		length			int,
		prec			int,
		scale			int,
		isnullable		bit,
		iscaptured		bit
	)

	CREATE TABLE #indexes
	(
		index_name		nvarchar(30),
		index_owner		nvarchar(30),
		uniqueness		nvarchar(11),
		column_name		nvarchar(30),
		column_position	int,
		index_status	nvarchar(8)
	)

	CREATE TABLE #constraints
	(
		constraint_name	nvarchar(30),
		constraint_type	nchar(1),
		table_owner		nvarchar(30),
		table_name		nvarchar(30),
		index_owner		nvarchar(30),
		index_name		nvarchar(30),
		column_name		nvarchar(30),
		column_position	int,
		validated		nvarchar(13),
		cons_status		nvarchar(8),
		idx_status		nvarchar(8)
	)
    CREATE TABLE #MATCHES (name NVARCHAR(30))
	CREATE TABLE #MATCHES1(name NVARCHAR(100), column_name NVARCHAR(30), index_name NVARCHAR(30))
	CREATE TABLE #MATCHES2(name NVARCHAR(100), column_name NVARCHAR(30), index_name NVARCHAR(30))

    -- Populate the temporary metadata tables
    EXEC @retcode =	sys.sp_ORAgetcolumndata
					@owner		= @owner,
					@tablename	= @tablename,
					@publisher	= @publisher,
					@fordiagnosis = 1

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

	-- Determine the number of cached columns in the published table
	SELECT @cntcache = COUNT(*)
	FROM 	IHpublishercolumns
	WHERE	table_id = @table_id
	AND	publisher_id = @pubid

	-- Determine the number of actual columns in the published table
	SELECT	@cntactual = COUNT(*)
	FROM	#publishercolumns

	-- Compare the data in the temporary tables with that in the meta data cache
	INSERT INTO #MATCHES
		SELECT	ihpc.name
		FROM	IHpublishercolumns ihpc, #publishercolumns tpc
		WHERE	ihpc.table_id = @table_id
		AND	ihpc.publisher_id = @pubid
		AND	ihpc.name = tpc.name
		AND	ihpc.column_ordinal = tpc.column_ordinal
		AND	ihpc.type = tpc.type
		AND	(ihpc.length = tpc.length OR (ihpc.length IS NULL and tpc.length IS NULL))
		AND	(ihpc.prec = tpc.prec OR (ihpc.prec IS NULL and tpc.prec IS NULL))
		AND	(ihpc.scale = tpc.scale OR (ihpc.scale IS NULL and tpc.scale IS NULL))
		AND	ihpc.isnullable = tpc.isnullable
	
	-- Determine the number of matched columns in the table
	SELECT @cntmatch = COUNT(*)
	FROM #MATCHES

	-- If the counts don't match, populate result table
	if @cntcache <> @cntmatch OR @cntcache <> @cntactual
	BEGIN
		-- Determine which entries in IHpublishercolumns do not
		-- have matching entries in #publishercolumns
		INSERT INTO #RESULTSET
		SELECT @publication, @article, @owner, @tablename, name, NULL, column_ordinal, type, length, prec, scale, isnullable, NULL, NULL, NULL, N'Column dropped'
		FROM IHpublishercolumns
		WHERE	table_id = @table_id
		AND	publisher_id = @pubid
 		AND 	name NOT IN (SELECT * from #MATCHES)
		AND 	name NOT IN (SELECT name from #publishercolumns)

		-- Determine which entries in #publishercolumns do not
		-- have matching entries in IHpublishercolumns
		INSERT INTO #RESULTSET
		SELECT @publication, @article, @owner, @tablename, name, NULL, column_ordinal, type, length, prec, scale, isnullable, NULL, NULL, NULL, N'Column added'
		FROM #publishercolumns
		WHERE name NOT IN (SELECT * from #MATCHES)
		AND name NOT IN (SELECT name from IHpublishercolumns
				 WHERE	table_id = @table_id
				 AND	publisher_id = @pubid)

		-- Determine which entries in IHpublishercolumns have
		-- changed entries in #publishercolumns
		INSERT INTO #RESULTSET
		SELECT @publication, @article, @owner, @tablename, name, NULL, column_ordinal, type, length, prec, scale, isnullable, NULL, NULL, NULL, N'Column changed from'
		FROM IHpublishercolumns
		WHERE	table_id = @table_id
		AND	publisher_id = @pubid
		AND	name NOT IN (SELECT * from #MATCHES)
		AND	name IN (SELECT name from #publishercolumns)
		AND	name IN (SELECT name from IHpublishercolumns)

		INSERT INTO #RESULTSET
		SELECT @publication, @article, @owner, @tablename, name, NULL, column_ordinal, type, length, prec, scale, isnullable, NULL, NULL, NULL, N'Column changed to'
		FROM #publishercolumns
		WHERE name NOT IN (SELECT * from #MATCHES)
		AND name IN (SELECT name from #publishercolumns)
		AND name IN (SELECT name from IHpublishercolumns
				 WHERE	table_id = @table_id
				 AND	publisher_id = @pubid)
	END

	-- Compare #indexes with data in IHpublisherindexes
	SELECT @cntcache = COUNT(*)
	FROM 	IHpublisherindexes
	WHERE	table_id = @table_id
	AND	publisher_id = @pubid

	-- Determine the number of actual indexes for the published table
	SELECT	@cntactual = COUNT(*)
	FROM	#indexes

	-- Compare the data in the temporary tables with that in the meta data cache
	DELETE #MATCHES
	INSERT INTO #MATCHES
		SELECT	distinct ihpi.name
		FROM	IHpublisherindexes ihpi, #indexes tpi
		WHERE	ihpi.table_id = @table_id
		AND	ihpi.publisher_id = @pubid
		AND	ihpi.name = tpi.index_name
		AND	ihpi.type = tpi.uniqueness
		AND     'VALID' = tpi.index_status
	
	-- Determine the number of matched columns in the table
	SELECT @cntmatch = COUNT(*)
	FROM #MATCHES

	-- If the counts don't match, populate result table
	if @cntcache <> @cntmatch OR @cntcache <> @cntactual
	BEGIN
		-- Determine which entries in IHpublisherindexes do not
		-- have matching entries in #indexes
		INSERT INTO #RESULTSET
		SELECT @publication, @article, @owner, @tablename, NULL, name, NULL, type, NULL, NULL, NULL, NULL, NULL, NULL, 'VALID', N'Index dropped'
		FROM IHpublisherindexes
		WHERE	table_id = @table_id
		AND	    publisher_id = @pubid
		AND		name NOT IN (SELECT * from #MATCHES)
		AND		name NOT IN (SELECT index_name from #indexes)

		-- Determine which entries in #indexes do not
		-- have matching entries in IHpublisherindexes
		INSERT INTO #RESULTSET
		SELECT @publication, @article, @owner, @tablename, NULL, index_name, NULL, uniqueness, NULL, NULL, NULL, NULL, NULL, NULL, index_status, N'Index added'
		FROM #indexes
		WHERE index_name NOT IN (SELECT * from #MATCHES)
		AND index_name NOT IN
			(SELECT name from IHpublisherindexes
			 WHERE	table_id = @table_id
			 AND	publisher_id = @pubid)

		-- Determine which entries in IHpublisherindexes have
		-- changed entries in #indexes
		INSERT INTO #RESULTSET
		SELECT @publication, @article, @owner, @tablename, NULL, name, NULL, type, NULL, NULL, NULL, NULL, NULL, NULL, 'VALID', N'Index changed from'
		FROM IHpublisherindexes
		WHERE name NOT IN (SELECT * from #MATCHES)
		AND name IN (SELECT index_name from #indexes)
		AND name IN
			(SELECT name from IHpublisherindexes
			 WHERE	table_id = @table_id
			 AND	publisher_id = @pubid)

		INSERT INTO #RESULTSET
		SELECT @publication, @article, @owner, @tablename, NULL, index_name, NULL, uniqueness, NULL, NULL, NULL, NULL, NULL, NULL, index_status, N'Index changed to'
		FROM #indexes
		WHERE index_name NOT IN (SELECT * from #MATCHES)
		AND index_name IN (SELECT index_name from #indexes)
		AND index_name IN
			(SELECT name from IHpublisherindexes
			 WHERE	table_id = @table_id
			 AND	publisher_id = @pubid)

	END

	-- Compare #constraints with data in IHpublisherconstraints
	SELECT @cntcache = COUNT(*)
	FROM 	IHpublisherconstraints
	WHERE	table_id = @table_id
	AND	publisher_id = @pubid

	-- Determine the number of actual indexes for the published table
	SELECT	@cntactual = COUNT(*)
	FROM	#constraints

	-- Compare the data in the temporary tables with that in the meta data cache
	DELETE #MATCHES
	INSERT INTO #MATCHES
		SELECT	distinct ihpc.name
		FROM	IHpublisherconstraints ihpc, #constraints tpc
		WHERE	ihpc.table_id = @table_id
		AND	ihpc.publisher_id = @pubid
		AND	ihpc.name = tpc.constraint_name
		AND	'VALIDATED' = tpc.validated
		AND 'ENABLED' = tpc.cons_status
		--  For Oracle 8.05, we may not be able to identify an index
		--  for the primary key constraint, so status may be NULL
		AND     (tpc.idx_status is NULL OR 'VALID' = tpc.idx_status)
	
	-- Determine the number of matched columns in the table
	SELECT @cntmatch = COUNT(*)
	FROM #MATCHES

	-- If the counts don't match, populate result table
	if @cntcache <> @cntmatch OR @cntcache <> @cntactual
	BEGIN
		-- Determine which entries in IHpublisherconstraints do not
		-- have matching entries in #constraints
		INSERT INTO #RESULTSET
		SELECT @publication, @article, @owner, @tablename, NULL, name, NULL, type, NULL, NULL, NULL, NULL, 'VALIDATED', 'ENABLED', 'VALID', N'Constraint dropped'
		FROM IHpublisherconstraints
		WHERE	table_id = @table_id
		AND		publisher_id = @pubid
		AND		name NOT IN (SELECT * from #MATCHES)
		AND		name NOT IN (SELECT constraint_name from #constraints)

		-- Determine which entries in #constraints do not
		-- have matching entries in IHpublisherconstraints
		INSERT INTO #RESULTSET
		SELECT @publication, @article, @owner, @tablename, NULL, constraint_name, NULL, 'PRIMARYKEY', NULL, NULL, NULL, NULL, validated, cons_status, idx_status, N'Constraint Added'
		FROM #constraints
		WHERE constraint_name NOT IN (SELECT * from #MATCHES)
		AND constraint_name NOT IN
			(SELECT name from IHpublisherconstraints
			 WHERE	table_id = @table_id
			 AND	publisher_id = @pubid)
			
		-- Determine which entries in IHpublisherconstraints have
		-- changed entries in #constraints
		INSERT INTO #RESULTSET
		SELECT @publication, @article, @owner, @tablename, NULL, name, NULL, type, NULL, NULL, NULL, NULL, 'VALIDATED', 'ENABLED', 'VALID', N'Constraint changed from'
		FROM IHpublisherconstraints
		WHERE name NOT IN (SELECT * from #MATCHES)
		AND name IN (SELECT constraint_name from #constraints)
		AND name IN
			(SELECT name from IHpublisherconstraints
			 WHERE	table_id = @table_id
			 AND	publisher_id = @pubid)

		INSERT INTO #RESULTSET
		SELECT @publication, @article, @owner, @tablename, NULL, constraint_name, NULL, 'PRIMARYKEY', NULL, NULL, NULL, NULL, validated, cons_status, idx_status, N'Constraint changed to'
		FROM #constraints
		WHERE constraint_name NOT IN (SELECT * from #MATCHES)
		AND constraint_name IN (SELECT constraint_name from #constraints)
		AND constraint_name IN
			(SELECT name from IHpublisherconstraints
			 WHERE	table_id = @table_id
			 AND	publisher_id = @pubid)
	END

	-- Get the current column names and their associated constraint names
	INSERT INTO #MATCHES1
		SELECT	c.column_name + '"' + c.constraint_name, c.column_name, c.constraint_name
		FROM	#constraints c

	-- Get the cached column names and their associated constraint names
	INSERT INTO #MATCHES2
		SELECT	ihpc.name + '"' + ihpi.name, ihpc.name, ihpi.name
		FROM	IHpublisherconstraints ihpi,
			IHpublishercolumns ihpc,
			IHpublishercolumnconstraints ihpci
		WHERE	ihpi.table_id = @table_id
		AND	ihpi.publisher_id = @pubid
		AND	ihpi.publisherconstraint_id = ihpci.publisherconstraint_id
		AND	ihpc.publishercolumn_id = ihpci.publishercolumn_id
		AND	ihpc.table_id = ihpi.table_id
		AND	ihpc.publisher_id = ihpi.publisher_id
	
	-- Determine the number of matched columns in the table
	SELECT @cntactual = COUNT(*)
	FROM #MATCHES1

	SELECT @cntcache = COUNT(*)
	FROM #MATCHES2

	SELECT @cntmatch = COUNT(*)
	FROM #MATCHES1 M1, #MATCHES2 M2
	WHERE M1.name = M2.name
	AND M1.column_name = M2.column_name
	AND M1.index_name = M2.index_name

	-- If the counts don't match, populate result table
	if @cntcache <> @cntmatch OR @cntcache <> @cntactual
	BEGIN
		-- Determine which index columns have been dropped
		INSERT INTO #RESULTSET
		SELECT @publication, @article, @owner, @tablename, column_name, index_name, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'Constraint column dropped'
		FROM #MATCHES2
		WHERE name NOT IN (SELECT name from #MATCHES1)

		-- Determine which index columns have been added
		INSERT INTO #RESULTSET
		SELECT @publication, @article, @owner, @tablename, column_name, index_name, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'Constraint column added'
		FROM #MATCHES1
		WHERE name NOT IN (SELECT name from #MATCHES2)
	END

	-- Get the current column names and their associated index names
	DELETE #MATCHES1
	INSERT INTO #MATCHES1
		SELECT	c.column_name + '"' + c.index_name, c.column_name, c.index_name
		FROM	#indexes c

	-- Get the cached column names and their associated index names
	DELETE #MATCHES2
	INSERT INTO #MATCHES2
		SELECT	ihpc.name + '"' + ihpi.name, ihpc.name, ihpi.name
		FROM	IHpublisherindexes ihpi,
			IHpublishercolumns ihpc,
			IHpublishercolumnindexes ihpci
		WHERE	ihpi.table_id = @table_id
		AND	ihpi.publisher_id = @pubid
		AND	ihpi.publisherindex_id = ihpci.publisherindex_id
		AND	ihpc.publishercolumn_id = ihpci.publishercolumn_id
		AND	ihpc.table_id = ihpi.table_id
		AND	ihpc.publisher_id = ihpi.publisher_id
	
	-- Determine the number of matched columns in the table
	SELECT @cntactual = COUNT(*)
	FROM #MATCHES1

	SELECT @cntcache = COUNT(*)
	FROM #MATCHES2

	SELECT @cntmatch = COUNT(*)
	FROM #MATCHES1 M1, #MATCHES2 M2
	WHERE M1.name = M2.name
	AND M1.column_name = M2.column_name
	AND M1.index_name = M2.index_name

	-- If the counts don't match, populate result table
	if @cntcache <> @cntmatch OR @cntcache <> @cntactual
	BEGIN
		-- Determine which index columns have been dropped
		INSERT INTO #RESULTSET
		SELECT @publication, @article, @owner, @tablename, column_name, index_name, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'Index column dropped'
		FROM #MATCHES2
		WHERE name NOT IN (SELECT name from #MATCHES1)

		-- Determine which index columns have been added
		INSERT INTO #RESULTSET
		SELECT @publication, @article, @owner, @tablename, column_name, index_name, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'Index column added'
		FROM #MATCHES1
		WHERE name NOT IN (SELECT name from #MATCHES2)
	END

	RETURN (0)
END

 
Last revision 2008RTM
See also

  sp_MSdrop_repltran (Procedure)
sp_MSrepl_validatecache (Procedure)
sp_ORAgetcolumndata (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