-- Name:
-- sp_MSrepl_validalateache
-- Description:
-- Validate the distributor side Oracle meta data (Oracle specific)
-- Inputs:
-- @publisher == name of Oracle publisher
-- @publication == publication name
-- @article == article name
-- Returns:
-- Return code (0 for success, 1 for failure)
-- Security:
-- internal
-- Requires Certificate signature for catalog access
CREATE PROCEDURE sys.sp_MSrepl_validatecache
(
@publisher sysname,
@publication sysname,
@article sysname
)
AS
BEGIN
set nocount on
declare @publication_id int
declare @pubname sysname
declare @artname sysname
declare @tableowner sysname
declare @tablename sysname
declare @retcode int
declare @fetch_status int
-- Verify @publication is valid, if specified
if @publication <> '%'
begin
if not exists
(select p.name
from syspublications p,
MSpublications b,
master.dbo.sysservers s
where p.pubid = b.publication_id
and s.srvid = b.publisher_id
and UPPER(s.srvname collate database_default) = UPPER(@publisher) collate database_default
and p.name = @publication)
begin
raiserror(21073, 16, -1)
return 1
end
end
-- Verify @article is valid, if specified
if @article <> '%'
begin
if not exists
(select a.name
from sysarticles a,
MSarticles m,
syspublications p,
MSpublications b,
master.dbo.sysservers s
where a.pubid = p.pubid
and a.artid = m.article_id
and a.pubid = m.publication_id
and s.srvid = m.publisher_id
and p.pubid = b.publication_id
and s.srvid = b.publisher_id
and UPPER(s.srvname collate database_default) = UPPER(@publisher) collate database_default
and a.name = @article
and ((@publication = N'%') or (p.name = @publication)))
begin
raiserror(20046, 16, -1)
return 1
end
end
CREATE TABLE #RESULTSET (publication sysname, article sysname, tableowner sysname, tablename sysname, columnname nvarchar(30), index_or_constraint nvarchar(30), columnordinal int, type nvarchar(30), length int, prec int, scale int, isnullable bit, val
idate nvarchar(13), consstatus nvarchar(8), idxstatus nvarchar(8), description NVARCHAR(100))
declare hC CURSOR LOCAL FAST_FORWARD for
select p.name, a.name, m.source_owner, m.source_object
from sysarticles a,
MSarticles m,
syspublications p,
MSpublications b,
master.dbo.sysservers s
where a.pubid = p.pubid
and a.artid = m.article_id
and a.pubid = m.publication_id
and s.srvid = m.publisher_id
and p.pubid = b.publication_id
and s.srvid = b.publisher_id
and UPPER(s.srvname collate database_default) = UPPER(@publisher) collate database_default
and ((@article = N'%') or (a.name = @article))
and ((@publication = N'%') or (p.name = @publication))
order by p.name, a.name
open hC
fetch hC into @pubname, @artname, @tableowner, @tablename
while @@fetch_status <> -1
begin
-- sp_ORAvalidatecache will populate RESULTSET
exec @retcode = sys.sp_ORAvalidatecache @publisher,
@pubname,
@artname,
@tableowner,
@tablename
if @retcode <> 0 or @@error <> 0
begin
close hC
deallocate hC
return 1
end
fetch hC into @pubname, @artname, @tableowner, @tablename
end
close hC
deallocate hC
-- Return result set
select * from #RESULTSET order by publication, article, tableowner, tablename, columnname, description
return 0
end